Spring mvc+maven+java 导出Excel表格+读取Excel并输出list

最新推荐文章于 2024-02-19 22:38:26 发布

豆趣编程

最新推荐文章于 2024-02-19 22:38:26 发布

阅读量4.1k

点赞数

分类专栏：后端问题文章标签： java excel excel导出 excel读取

本文链接：https://blog.csdn.net/lianzhang861/article/details/83092835

版权

后端问题专栏收录该内容

14 篇文章 1 订阅

订阅专栏

前言：网上对java excel处理比较杂乱，我经过查阅资料并亲自试验，总结了一下java excel的导出和读取

.xls格式的excel文件需要HSSF支持，需要相应的poi.jar，.xlsx格式的excel文件需要XSSF支持，需要poi-ooxml.jar，

1.导jar包

引入poi-3.7-20101029.jar包和poi-ooxml-3.7-20101029.jar（我用的maven没有试验这种jar包引入）

我的是maven项目，则在pom中加入

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.6</version>
        </dependency>

 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.6</version>
        </dependency>

2.创建ExcelUtil

package com.equipment.util;

import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

	private final static String excel2003L = ".xls"; // 2003- 版本的excel
	private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel

	/**
	 * 导出Excel
	 * @param sheetName sheet名称
	 * @param title 标题
	 * @param values 内容
	 * @param wb HSSFWorkbook对象
	 * @return
	 */
	public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){

		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		if(wb == null){
			wb = new HSSFWorkbook();
		}

		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);

		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		HSSFRow row = sheet.createRow(0);

		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

		//声明列对象
		HSSFCell cell = null;

		//创建标题
		for(int i=0;i<title.length;i++){
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
			cell.setCellStyle(style);
		}

		//创建内容
		for(int i=0;i<values.length;i++){
			row = sheet.createRow(i + 1);
			if(values[i]!=null){
				for(int j=0;j<values[i].length;j++){
					//将内容按顺序赋给对应的列对象
					row.createCell(j).setCellValue(values[i][j]);
				}
			}else{
				continue;
			}

		}
		return wb;
	}


	/**
	 * 将流中的Excel数据转成List<Map>(读取Excel)
	 *
	 * @param in
	 *            输入流
	 * @param fileName
	 *            文件名（判断Excel版本）
	 * @param mapping
	 *            字段名称映射
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> readExcel(InputStream in, String fileName,
													   Map<String, String> mapping) throws Exception {
		// 根据文件名来创建Excel工作薄
		Workbook work = getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		// 返回数据
		List<Map<String, Object>> ls = new ArrayList<Map<String, Object>>();

		// 遍历Excel中所有的sheet
		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if (sheet == null)
				continue;

			// 取第一行标题
			row = sheet.getRow(0);
			String title[] = null;
			if (row != null) {
				title = new String[row.getLastCellNum()];

				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					cell = row.getCell(y);
					title[y] = (String) getCellValue(cell);
				}

			} else
				continue;

			// 遍历当前sheet中的所有行
			for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
				row = sheet.getRow(j);
				Map<String, Object> m = new HashMap<String, Object>();
				// 遍历所有的列
				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					cell = row.getCell(y);
					String key = title[y];
					// log.info(JSON.toJSONString(key));
					m.put(mapping.get(key), getCellValue(cell));
				}
				ls.add(m);
			}

		}
		//work.close();
		return ls;
	}

	/**
	 * 描述：根据文件后缀，自适应上传文件的版本
	 *
	 * @param inStr
	 *            ,fileName
	 * @return
	 * @throws Exception
	 */
	public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (excel2003L.equals(fileType)) {
			wb = new HSSFWorkbook(inStr); // 2003-
		} else if (excel2007U.equals(fileType)) {
			wb = new XSSFWorkbook(inStr); // 2007+
		} else {
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 *
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(Cell cell) {
		Object value = null;
		DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
		DecimalFormat df2 = new DecimalFormat("0"); // 格式化数字

		switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				value = cell.getRichStringCellValue().getString();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if ("General".equals(cell.getCellStyle().getDataFormatString())) {
					value = df.format(cell.getNumericCellValue());
				} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
					value = sdf.format(cell.getDateCellValue());
				} else {
					value = df2.format(cell.getNumericCellValue());
				}
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				value = cell.getBooleanCellValue();
				break;
			case Cell.CELL_TYPE_BLANK:
				value = "";
				break;
			default:
				break;
		}
		return value;
	}

	public static void main(String[] args) throws Exception {
		File file = new File("D:\\studn.xls");
		FileInputStream fis = new FileInputStream(file);
		Map<String, String> m = new HashMap<String, String>();
		m.put("id", "id");
		m.put("姓名", "name");
		m.put("年龄", "age");
		List<Map<String, Object>> ls = readExcel(fis, file.getName(), m);
		System.out.println(JSON.toJSONString(ls));
	}





}

注：其中HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER)

可以设置表格的样式，具体样式详情可以看这个：

https://blog.csdn.net/qq_35192741/article/details/77645018

3.导出表格

3.1 controller调用

/**
     * 导出报表
     * @return
     */
    @RequestMapping(value = "/exportRepairOrder")
    @ResponseBody
    public void exportRepairOrder(@RequestParam Map<String,String> params, HttpServletRequest req,HttpServletResponse response) throws Exception {
        
        //获取数据
        List<Map<String,Object>> list = repairManagerService.getRepairOrderList(params);
        //excel标题
        String[] title = {"地市","区县","营业厅ID","营业厅名称","渠道电话","设备类型","设备名称","报修日期"};

        //excel文件名
        String fileName = "维护单表"+System.currentTimeMillis()+".xls";

        //sheet名
        String sheetName = "维护单表";
        String [][] content = new String[list.size()][];
        for (int i = 0; i < list.size(); i++) {
            content[i] = new String[title.length];
            Map<String,Object> obj = list.get(i);
            content[i][0] = obj.get("REGION_GROUP_NAME")==null?"":obj.get("REGION_GROUP_NAME").toString();
            content[i][1] = obj.get("DISTRICT_GROUP_NAME")==null?"":obj.get("DISTRICT_GROUP_NAME").toString();
            content[i][2] = obj.get("GROUP_ID")==null?"":obj.get("GROUP_ID").toString();
            content[i][3] = obj.get("GROUP_NAME")==null?"":obj.get("GROUP_NAME").toString();
            content[i][4] = obj.get("CHANNEL_CONTACT")==null?"":obj.get("CHANNEL_CONTACT").toString();
            content[i][5] = obj.get("DEVICE_CATEGORY_NAME")==null?"":obj.get("DEVICE_CATEGORY_NAME").toString();
            content[i][6] = obj.get("DEVICE_NAME")==null?"":obj.get("DEVICE_NAME").toString();
            content[i][7] = obj.get("REPORT_DATE1")==null?"":obj.get("REPORT_DATE1").toString();
            
        }

        //创建HSSFWorkbook
        HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);

        //响应到客户端
        try {
            this.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //发送响应流方法
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

3.2在html中调用

点击导出按钮，则将页面url设为接口，并将页面的查询参数也传进去，可以做到导出页面经过查询条件过滤后的数据

<div class="mt-20">
			报修日期：
			<input type="text" onfocus="WdatePicker({  })"
				   id="datemin" name="datemin" class="input-text Wdate" style="width:100px;">
			-
			<input type="text" onfocus="WdatePicker({ minDate:'#F{$dp.$D(\'datemin\')}' })"
				   id="datemax" name="datemax" class="input-text Wdate" style="width:100px;">
			<span class="span-label">报修工号：</span><input type="text" class="input-text" style="width:100px" id="loginNo" name="loginNo">
			<span class="span-label">维修单状态：</span>
			<span class="select-box inline">
				<select name="" class="select" id="orderStatus" name="orderStatus">
					<option value="">请选择</option>
					<option value="0">未维修</option>
					<option value="1">已维修</option>
					<option value="2">已超时</option>
					<option value="3">已撤单</option>
				</select>
			</span>
			<button type="button" class="btn btn-success radius" style="margin-left:10px;"
				onclick="reloadTable();"><i class="Hui-iconfont">&#xe665;</i> 搜索</button>
		</div>
		<div class="cl pd-5 bg-1 bk-gray mt-20">
			<span class="btn btn-primary radius" onclick="member_add('录入','${ctx }/repairOrder/editRepairOrder','900','510')" ><i class="Hui-iconfont add">&#xe600;</i> 录入</span>
			<%--<span class="btn btn-primary radius"><i class="Hui-iconfont yanjing">&#xe725;</i> 查看</span>--%>
			<span>
			<%@include file="/common/jsp/func.jsp" %>
			<a href="javascript:;" onclick="exportExcel()" class="btn btn-primary radius">
				<i class="Hui-iconfont">&#xe644;</i>导出Excel
			</a>
			<%--<a href="javascript:;" onclick="delItems()" class="btn btn-primary radius">
				<i class="Hui-iconfont">&#xe609;</i>批量删除
			</a>--%>

			</span>
		</div>

3.3 js中的调用方法及传参数

/*导出Excel*/
function exportExcel(){
	var url = "${ctx }/repairOrder/exportRepairOrder?"
	var parameter = "loginNo="+ $("#loginNo").val()
	+"&orderStatus="+$('#orderStatus').val()
	+"&datemin="+$('#datemin').val()
	+ "&datemax=" + $("#datemax").val();
	window.location.href = url+parameter;
}

点击后即下载excel文件，打开文件后表格跟html中的表格基本一致

4.对excel的读取并返回List<Map<String,String>>

因为使用的spring mvc，所以文件取multipart格式比较容易，然后在手动转成inputstream

关于读取excel我是参考这边博客

https://blog.csdn.net/u012662357/article/details/58593020

此接口可以自动识别xls和xlsx

4.1 html中传excel文件

html：

<form action="" id="form1" method="post">
	<input type="file" id="file" style="display: none;" name="file">
</form>

<span>
	<a href="javascript:;" onclick="uploadExcel()" class="btn btn-primary radius">
		<i class="Hui-iconfont">&#xe644;</i>文件上传
	</a>
</span>

js：

//上传文件
	function uploadExcel(){
		$("#file").trigger("click");
		$("#file").change(function(){
			var formData = new FormData($("#form1")[0]);  //重点：要用这种方法接收表单的参数
			$.ajax({
				url : "${ctx }/statistics/uploadExcel",
				type : 'POST',
				data : formData,
				// 告诉jQuery不要去处理发送的数据
				processData : false,
				// 告诉jQuery不要去设置Content-Type请求头
				contentType : false,
				async : false,
				success : function(data) {
					if(data){
						console.log(data)
					}
				}
			});

		})
	}

4.2 controller中调用

@ResponseBody
@RequestMapping(value = "uploadExcel", produces = "text/plain;charset=UTF-8")
public String uploadExcel(@RequestParam Map<String,String> params,
                          @RequestParam(value = "file") MultipartFile file) {
    RetBase ret=new RetBase();
    List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
    try {
        String fileName = file.getOriginalFilename();
        //将multipartFile转为inputstream
        CommonsMultipartFile cFile = (CommonsMultipartFile) file;
        DiskFileItem fileItem = (DiskFileItem) cFile.getFileItem();
        InputStream inputStream = fileItem.getInputStream();
        Map<String,String> titleMap=new HashMap<>();
        titleMap.put("id", "id");
        titleMap.put("姓名", "name");
        titleMap.put("年龄", "age");
        list=ExcelUtil.readExcel(inputStream,fileName,titleMap);
        ret.setData(list);
        ret.setSuccess(true);
        ret.setMsg("成功");
    } catch (Exception e) {
        e.printStackTrace();
        ret.setMsg("失败");
        ret.setSuccess(false);
    }
    return JSON.toJSONString(ret, SerializerFeature.WriteMapNullValue);
}

上传的excel

返回的数据：

豆趣编程

关注

0
点赞
踩
24

收藏

觉得还不错? 一键收藏
打赏
2
评论
Spring mvc+maven+java 导出Excel表格+读取Excel并输出list

前言：网上对java excel处理比较杂乱，我经过查阅资料并亲自试验，总结了一下java excel的导出和读取.xls格式的excel文件需要HSSF支持，需要相应的poi.jar，.xlsx格式的excel文件需要XSSF支持，需要poi-ooxml.jar， 1.导jar包引入poi-3.7-20101029.jar包和poi-ooxml-3.7-20101029.jar（我用...
复制链接

扫一扫