添加 maven 依赖
<dependency><groupId>commons-fileupload</groupId><artifactId>commons-fileupload</artifactId><version>1.3.1</version></dependency><dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.4</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.0</version></dependency>
编写工具类
- 可以实现
- 1.导出Excel 到本地 / 浏览器
- 2.导出包含多个 Sheet 的 Excel 到本地 / 浏览器
package com.geotmt.billingcenter.common.utils;import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;/*** @Descriptinon: Excel 导出类* @Author: yanghanwei* @Mail: yanghanwei@geotmt.com* @CreateDate: 2018/12/3 17:15* @Version: v1*/
public class ExportExcelUtils {private static final String ATTR_ARR_STR = "attrArr";private static final String DATA_LIST_STR = "dataList";private static final String TITLE_ARR_STR = "titleArr";private static final String SHEET_NAME = "Sheet";private static final String SHEET_NAME_STR = "sheetName";/*** 默认单元格宽度*/private static final Integer DEFAULT_CELL_WIDTH = 3000;private static FileOutputStream fout = null;private static OutputStream out = null;private static OutputStream setResponseHeaderFileName(HttpServletResponse response, String filename) throws IOException {response.setContentType("application/octet-stream;charset=utf-8");response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename+".xls","UTF-8"));OutputStream out = new BufferedOutputStream(response.getOutputStream());return out;}private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);/*** 导出 exccel到本地 工具类（多个sheet导出）* @param mapList* @param filePath 本地路径* sheetName： sheet名字* titleArr： 表头中文名数组* attrArr： 数据对应的实体类属性数组（与表头一一对应）* dataList： 到处的数据list*/public static void buildExcelTemplate( List<Map<String, Object>> mapList,String filePath, Integer cellWidth) {HSSFWorkbook wb = new HSSFWorkbook();FileOutputStream fout = null;try{// 将文件存到指定位置fout = new FileOutputStream(filePath);if (!mapList.isEmpty()) {for (int a = 0; a < mapList.size(); a++) {String sheetName = "Sheet1";if(!StringUtils.isEmpty(mapList.get(a).get(SHEET_NAME_STR))){sheetName = String.valueOf(mapList.get(a).get(SHEET_NAME_STR));}String[] titleArr = (String[]) mapList.get(a).get("titleArr");String[] attrArr = (String[]) mapList.get(a).get("attrArr");Object dataList = mapList.get(a).get("dataList");// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheetHSSFSheet sheetMonitor = wb.createSheet(sheetName);// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制shortHSSFRow rowMonitor = sheetMonitor.createRow(0);// 第四步，创建单元格，并设置值表头 设置表头居中HSSFCellStyle style = ExportExcelUtils.getColumnTopStyle(wb);HSSFCell cellMonitor = rowMonitor.createCell(0);for (int i = 0; i < titleArr.length; i++) {cellMonitor.setCellValue(titleArr[i]);cellMonitor.setCellStyle(style);cellMonitor = rowMonitor.createCell((i + 1));}String jsonString = JSONObject.toJSONString(dataList);JSONArray jsonArray = JSONObject.parseArray(jsonString);if (jsonArray != null && !jsonArray.isEmpty()) {for (int i = 0; i < jsonArray.size(); i++) {sheetMonitor.setColumnWidth(i, null == cellWidth ? DEFAULT_CELL_WIDTH : cellWidth);Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(jsonArray.get(i)), Map.class);if(null != map){for (int j = 0; j < titleArr.length; j++) {rowMonitor = sheetMonitor.createRow(i + 1);// 第五步，写入实体数据 实际应用中这些数据从数据库得到，for (int k = 0; k < attrArr.length; k++) {if (map.containsKey(attrArr[k]) && !StringUtils.isEmpty(map.get(attrArr[k]))) {// 第四步，创建单元格，并设置值if(!StringUtils.isEmpty(map.get(attrArr[k]))){String value = String.valueOf(map.get(attrArr[k]));rowMonitor.createCell(k).setCellValue(value);}else{rowMonitor.createCell(k).setCellValue("");}}}}}}}}}wb.write(fout);}catch (Exception e){logger.error("导出Excel失败,Exception：" + e);logger.info("导出Excel失败，Exception:{0}", e);}finally {try {if(null != fout){fout.close();}}catch (Exception e){logger.error("关闭流异常：[{}]",e);}}}/*** 导出 exccel 到浏览器工具类（1个sheet导出）* @param dataListForMap* sheetName： sheet名字* titleArr： 表头中文名数组* attrArr： 数据对应的实体类属性数组（与表头一一对应）* dataListForMap： 导出的数据list*/public static void buildExcelTemplate(Map<String, Object> dataListForMap, HttpServletResponse response, String fileName, Integer cellWidth) {HSSFWorkbook wb = new HSSFWorkbook();// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheetObject sheetName = dataListForMap.get("sheetName");HSSFSheet sheetMonitor = wb.createSheet(!StringUtils.isEmpty(sheetName) ? sheetName + "" : SHEET_NAME);// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制shortHSSFRow rowMonitor = sheetMonitor.createRow(0);try{out = setResponseHeaderFileName(response,fileName);//创建excel标题,设置列宽createTitle(wb, rowMonitor, dataListForMap, sheetMonitor, cellWidth);//写入数据writeData(sheetMonitor,1,dataListForMap);wb.write(out);}catch (Exception e){logger.info("导出Excel失败,Exception:{}",e);}finally {try {out.close();}catch (Exception e){logger.error("关闭流异常：[{}]",e);}}}/*** 导出多个 sheet 到浏览器* @param mapList* * sheetName： sheet名字* * titleArr： 表头中文名数组* * attrArr： 数据对应的实体类属性数组（与表头一一对应）* * dataList： 到处的数据list* @param response* @param fileName 文件名称*/public static void buildExcelTemplate(List<Map<String, Object>> mapList, HttpServletResponse response, String fileName, Integer cellWidth) {HSSFWorkbook wb = new HSSFWorkbook();if(null != mapList && !mapList.isEmpty()){try{for (int i=0; i<mapList.size(); i++) {Map<String, Object> dataListForMap = mapList.get(i);// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheetObject sheetName = dataListForMap.get(SHEET_NAME_STR);String sheetNameRt = !StringUtils.isEmpty(sheetName) ? sheetName + "" : SHEET_NAME + (i+1);HSSFSheet sheetMonitor = wb.createSheet(sheetNameRt);// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制shortHSSFRow rowMonitor = sheetMonitor.createRow(0);out = setResponseHeaderFileName(response,fileName);//创建excel标题createTitle(wb, rowMonitor, dataListForMap, sheetMonitor, cellWidth);//写入数据writeData(sheetMonitor,1,dataListForMap);}wb.write(out);}catch (IOException e){e.printStackTrace();logger.info("导出Excel失败,Exception:{}",e);}catch (Exception e) {logger.info("导出Excel失败,Exception:{}",e);}finally {try {out.close();}catch (Exception e){logger.error("关闭流异常：[{}]",e);}}}}/*** 创建excel标题* @param dataListForMap*/private static void createTitle(HSSFWorkbook wb, HSSFRow rowMonitor, Map<String, Object> dataListForMap, HSSFSheet sheetMonitor, Integer cellWidth){String[] titleArr = (String[]) dataListForMap.get(TITLE_ARR_STR);//居中样式HSSFCellStyle centerStyle = ExportExcelUtils.getColumnTopStyle(wb);for (int i = 0; i < titleArr.length; i++) {// 设置表格宽度（自适应）sheetMonitor.setColumnWidth(i, null == cellWidth ? DEFAULT_CELL_WIDTH : cellWidth);HSSFCell cellMonitor = rowMonitor.createCell(i);cellMonitor.setCellStyle(centerStyle);cellMonitor.setCellValue(titleArr[i]);}}/*** 写入数据* @param sheetMonitor* @param startRow* @param dataListForMap*/public static void writeData(HSSFSheet sheetMonitor, Integer startRow, Map<String, Object> dataListForMap){String[] titleArr = (String[]) dataListForMap.get(TITLE_ARR_STR);String[] attrArr = (String[]) dataListForMap.get(ATTR_ARR_STR);Object dataList = dataListForMap.get(DATA_LIST_STR);String jsonString = JSONObject.toJSONString(dataList);JSONArray jsonArray = JSONObject.parseArray(jsonString);if (jsonArray != null && !jsonArray.isEmpty()) {for (int i = 0; i < jsonArray.size(); i++) {Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(jsonArray.get(i)), Map.class);for (int j = 0; j < titleArr.length; j++) {HSSFRow rowMonitor = sheetMonitor.createRow( i + startRow);// 第五步，写入实体数据 实际应用中这些数据从数据库得到，for (int k = 0; k < attrArr.length; k++) {if (map != null && attrArr[k] != null && map.containsKey(attrArr[k]) && !StringUtils.isEmpty(map.get(attrArr[k]))) {// 第四步，创建单元格，并设置值if(!StringUtils.isEmpty(map.get(attrArr[k]))){String value = String.valueOf(map.get(attrArr[k]));rowMonitor.createCell(k).setCellValue(value);}else{rowMonitor.createCell(k).setCellValue("");}}}}}}}/*** 列头单元格样式* @param workbook* @return*/private static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {// 设置字体HSSFFont font = workbook.createFont();//设置字体大小font.setFontHeightInPoints((short)12);//字体加粗font.setBold(true);//设置字体名字font.setFontName("Courier New");//设置样式;HSSFCellStyle style = workbook.createCellStyle();//设置底边框;style.setBorderBottom(BorderStyle.THIN);//设置底边框颜色;style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());//设置左边框;style.setBorderLeft(BorderStyle.THIN);//设置左边框颜色;style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());//设置右边框;style.setBorderRight(BorderStyle.THIN);//设置右边框颜色;style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());//设置顶边框;style.setBorderTop(BorderStyle.THIN);//设置顶边框颜色;style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());//在样式用应用设置的字体;style.setFont(font);//设置自动换行;style.setWrapText(false);//设置水平对齐的样式为居中对齐;style.setAlignment(HorizontalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;style.setVerticalAlignment(VerticalAlignment.CENTER);return style;}/*** 列数据信息单元格样式* @param workbook* @return*/private HSSFCellStyle getStyle(HSSFWorkbook workbook) {// 设置字体HSSFFont font = workbook.createFont();//设置字体大小font.setFontHeightInPoints((short)12);font.setFontName("Courier New");//设置样式;HSSFCellStyle style = workbook.createCellStyle();//设置底边框;style.setBorderBottom(BorderStyle.THIN);//设置底边框颜色;style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());//设置左边框;style.setBorderLeft(BorderStyle.THIN);//设置左边框颜色;style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());//设置右边框;style.setBorderRight(BorderStyle.THIN);//设置右边框颜色;style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());//设置顶边框;style.setBorderTop(BorderStyle.THIN);//设置顶边框颜色;style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());//在样式用应用设置的字体;style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BRIGHT_GREEN.getIndex());style.setFont(font);//设置自动换行;style.setWrapText(false);//设置水平对齐的样式为居中对齐;style.setAlignment(HorizontalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;style.setVerticalAlignment(VerticalAlignment.CENTER);return style;}/*** 判断是否是整数* @param str* @return*/private static Pattern pattern = Pattern.compile("^\\d+$");public static boolean isIntNum(String str) {Matcher isIntNum = pattern.matcher(str);if (!isIntNum.matches()) {return false;}return true;}/*** 判断是否是浮点数* @param str* @return*/private static Pattern pattern2 = Pattern.compile("\\d+\\.\\d+$|-\\d+\\.\\d+$");public static boolean isDoubleNum(String str) {Matcher isDoubleNum = pattern2.matcher(str);if (!isDoubleNum.matches()) {return false;}return true;}
}
简单使用
@ApiOperation(value = "导出成本统计")@RequestMapping(value = "/exportList",method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_UTF8_VALUE)public void ExportConsumeStatis(@RequestBody ParamsCost obj,HttpServletResponse response){logger.info("导出成本统计,ParamsConsume:{}",obj);try{if(null == obj.getEffectStartTime() || null == obj.getEffectEndTime()){throw new MyException("开始时间和结束时间不能为空！", HttpServletResponse.SC_INTERNAL_SERVER_ERROR);}String startStr = GeoDateUtils.getDateStr(obj.getEffectStartTime(), GeoDateUtils.DATE_FORMAT2);String endStr = GeoDateUtils.getDateStr(obj.getEffectEndTime(), GeoDateUtils.DATE_FORMAT2);String fileName = startStr + "-" + endStr + "(成本统计)";List<CostStatisVo> consumeStatisVos = costStatisService.costStatisList(obj);if (null != consumeStatisVos) {consumeStatisVos.add(costStatisService.costStatisTotal(obj));}// 拼接 Excel 数据Map<String,Object> map = new HashMap<>(3);String[] titleArr = {"日期","产品","客户","用户","用户账号","用户ID","运营商","内部接口","外部接口","计费数","金额"};String[] attrArr = {"dayFlag","productName","companyName","cuserName","account","cuserId","isp","interfaceName","outInterfaceName","countCharging","cost"};map.put("titleArr",titleArr);map.put("attrArr",attrArr);map.put("dataList", consumeStatisVos);ExportExcelUtils.buildExcelTemplate(map, response, fileName,4000);}catch (Exception e) {e.printStackTrace();logger.error("导出消耗统计,Exception:",e);}}