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

﹏ヽ暗。殇╰゛Y 2022-05-09 14:28 254阅读 0赞

前言：网上对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中加入

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.6</version>
            </dependency>
    
     
            <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][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设为接口，并将页面的查询参数也传进去，可以做到导出页面经过查询条件过滤后的数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpYW56aGFuZzg2MQ_size_16_color_FFFFFF_t_70][]

<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"></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"></i> 录入</span>
    			<%--<span class="btn btn-primary radius"><i class="Hui-iconfont yanjing"></i> 查看</span>--%>
    			<span>
    			<%@include file="/common/jsp/func.jsp" %>
    			<a href="javascript:;" onclick="exportExcel()" class="btn btn-primary radius">
    				<i class="Hui-iconfont"></i>导出Excel
    			</a>
    			<%--<a href="javascript:;" onclick="delItems()" class="btn btn-primary radius">
    				<i class="Hui-iconfont"></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中的表格基本一致

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpYW56aGFuZzg2MQ_size_16_color_FFFFFF_t_70 1][]

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

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

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

[https://blog.csdn.net/u012662357/article/details/58593020][https_blog.csdn.net_u012662357_article_details_58593020]

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

### 4.1 html中传excel文件 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpYW56aGFuZzg2MQ_size_27_color_FFFFFF_t_70][]

html：

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpYW56aGFuZzg2MQ_size_27_color_FFFFFF_t_70 1][]

返回的数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpYW56aGFuZzg2MQ_size_27_color_FFFFFF_t_70 2][]

[https_blog.csdn.net_qq_35192741_article_details_77645018]: https://blog.csdn.net/qq_35192741/article/details/77645018
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpYW56aGFuZzg2MQ_size_16_color_FFFFFF_t_70]: /images/20220506/5a5e209a15814cae954a5d1a214a4521.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpYW56aGFuZzg2MQ_size_16_color_FFFFFF_t_70 1]: /images/20220506/b0f6f370013a49f7b9ce8cdad8efd738.png
[https_blog.csdn.net_u012662357_article_details_58593020]: https://blog.csdn.net/u012662357/article/details/58593020
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpYW56aGFuZzg2MQ_size_27_color_FFFFFF_t_70]: /images/20220506/edaa4db9a84548e0aee0bf7d0c48cfd0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpYW56aGFuZzg2MQ_size_27_color_FFFFFF_t_70 1]: /images/20220506/13e8ccde07b34207ab6065d50c9a19fc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpYW56aGFuZzg2MQ_size_27_color_FFFFFF_t_70 2]: /images/20220506/ad81a84ce08444909b55958af4e8f698.png