JAVA 使用 Apache POI 导出 Excel 详解
POI全称PoorObfuscation Implementation ，是Apache组件的一个开源项目，可以对微软的Office一系列办公软件进行读写操作。
实现方式有三种（建议SXSSF）：
1，HSSF是POI工程对Excel 97(-2007)文件操作的纯Java实现
2，XSSF是POI工程对Excel 2007 OOXML （.xlsx）文件操作的纯Java实现
3，从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的API----SXSSF
SXSSF通过一个滑动窗口来限制访问Row的数量从而达到低内存占用的目录，XSSF可以访问所有行。旧的行数据不再出现在滑动窗口中并变得无法访问，与此同时写到磁盘上。
在自动刷新的模式下，可以指定窗口中访问Row的数量，从而在内存中保持一定数量的Row。当达到这一数量时，在窗口中产生新的Row数据，并将低索引的数据从窗口中移动到磁盘中。
或者，滑动窗口的行数可以设定成自动增长的。它可以根据需要周期的根据一次明确的flushRow(int keepRows)调用来进行修改。
1，使用的POM包
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>4.0.1</version> </dependency>
2，导出案例
/** * 票据汇总 * * @param in: * @param response: * @return: com.jfa.common.utils.R * @author: */ @PostMapping("/billSummary") public R billSummary(@RequestBody ExportExcelIn in, HttpServletResponse response) { exportExcelService.billSummary(in, response); return R.ok(); } /** * 票据汇总 excel报表 * * @param in: * @param response: * @return: void * @author: */ public void billSummary(ExportExcelIn in, HttpServletResponse response) { //如果参数为空，设置实际为当前年份 if (in == null || StringUtils.isBlank(in.getYear())) { in.setYear(DUtils.fmtDateY(new Date())); } ExcelUtils excelUtils = new ExcelUtils(); excelUtils.setResponse(response); //设置文件名称 excelUtils.setFileName(StringUtils.isNotBlank(in.getFileName()) ? in.getFileName() : "综合分析".concat(Constant.HG).concat(String.valueOf(System.currentTimeMillis()))); //文档内超链接跳转 excelUtils.setLinkType(CommonsUtils.DOCUMENT); /** 根据时间段，查询票据数据 */ List<BillPollEntity> billPollList = billPollService.queryByBillByParams(in); Optional.ofNullable(billPollList).filter(s -> s.size() > 0).orElseThrow(() -> new RRException("当前时间数据为空，请重新选择再导出")); if (billPollList != null && billPollList.size() > 0) { //根据出票日期、到期日、出票人、收款人 分组 TreeMap<String, List<BillPollEntity>> billPollData = billPollList.stream().collect(Collectors.groupingBy(s -> DUtils.dateFmtYYMMDD(s.getDrawTime()).concat(Constant.HG).concat(DUtils.dateFmtYYMMDD(s.getDueTime())).concat(Constant.HG).concat(s.getDrawName().concat(s.getPayeeName())), TreeMap::new, Collectors.toList())); //组装各Sheet数据 List<List<Object[]>> retDataList = new ArrayList<>(); //设置sheet名称; 名称规则:出票日+到期日+总票额（万元）+总条数 List<Object> sheetNameList = new ArrayList<>(); sheetNameList.add("汇总"); //存放sheet数据对象 List<Object[]> sheetList = new ArrayList<>(); /** ============== 汇总 数据组装开始 ============== */ //票据回款信息集合 Map<String, BillBackInfoEntity> billBackDataMap = billBackInfoService.getAllBillBackList(ShiroUtils.getUserMark()); /** 汇总 设置列头 */ sheetList.add(ExcelHeadUtil.getBillSummaryHead()); /** 汇总 设置总计 */ List<Object> sumValueList = new ArrayList<>(); ExcelTools.setEmpty(sumValueList, 5); sumValueList.add(ExcelTools.setFormula(5, 3, (billPollData.size() + 2))); sumValueList.add(ExcelTools.setFormula(6, 3, (billPollData.size() + 2))); sumValueList.add(ExcelTools.setFormula(7, 3, (billPollData.size() + 2))); sheetList.add(ExcelTools.list2ObjArr(sumValueList)); for (Map.Entry<String, List<BillPollEntity>> m : billPollData.entrySet()) { String key = m.getKey(); // 键 List<BillPollEntity> dataList = m.getValue(); // 值 String sheetName = key.substring(0, key.lastIndexOf(Constant.HG)); //总票面额 BigDecimal totalBillAmount = dataList.stream().map(BillPollEntity::getBillAmount).reduce(BigDecimal.ZERO, BigDecimal::add); sheetName = sheetName.concat(Constant.L_KH + Constant.fmtAMT(totalBillAmount) + "万" + Constant.R_KH).concat(dataList.size() + "条"); //添加sheet名称 sheetNameList.add(sheetName); //总贴现金额 BigDecimal totalBackAmount = dataList.stream().map(t -> billBackDataMap.get(t.getBillNo()) != null ? billBackDataMap.get(t.getBillNo()).getBackAmount() : BigDecimal.ZERO).reduce(BigDecimal.ZERO, BigDecimal::add); //总利息 BigDecimal totalInterest = dataList.stream().map(t -> billBackDataMap.get(t.getBillNo()) != null ? billBackDataMap.get(t.getBillNo()).getInterest() : BigDecimal.ZERO).reduce(BigDecimal.ZERO, BigDecimal::add); /** 汇总 设置内容 */ List<Object> valueList = new ArrayList<>(); valueList.add(sheetName); valueList.add(DUtils.dateFmtYMD(dataList.get(0).getDrawTime())); valueList.add(DUtils.dateFmtYMD(dataList.get(0).getDueTime())); valueList.add(dataList.get(0).getDrawName()); valueList.add(dataList.get(0).getPayeeName()); valueList.add(totalBillAmount); valueList.add(totalBackAmount); valueList.add(totalInterest); sheetList.add(ExcelTools.list2ObjArr(valueList)); } retDataList.add(sheetList); /** ============== 汇总 数据组装结束 ============== */ //设置各Sheet名称 excelUtils.setSheetName(ExcelTools.list2StrArr(sheetNameList)); if (Constant.WHETHERE_DETAIL.YES.equals(in.getWhethereDetail())) { /** 设置汇总分类的详细数据 */ setBillSummaryData(billPollData, retDataList, billBackDataMap); } //设置Sheet数据 excelUtils.setDataLists(retDataList); } List<List<Object[]>> dataList = excelUtils.getDataLists(); Optional.ofNullable(dataList).filter(s -> s.size() > 0).ifPresent(s -> { //固定列头、表头 HashMap paneMap = new HashMap(); ArrayList<Integer[]> paneList = new ArrayList<>(); paneList.add(new Integer[]{3, 2});//冻结的列数、冻结的行数 paneMap.put(1, paneList); //设置筛选 HashMap filterMap = new HashMap(); filterMap.put(1, setDataFilter(ExcelHeadUtil.getBillSummaryHead().length)); for (int i = 1; i < s.size(); i++) { ArrayList<Integer[]> pList = new ArrayList<>(); pList.add(new Integer[]{1, 2}); paneMap.put(i + 1, pList); filterMap.put(i + 1, setDataFilter(ExcelHeadUtil.getBillSummaryChildHead().length)); } excelUtils.setPaneMap(paneMap); excelUtils.setFilterMap(filterMap); }); //设置数字精度 excelUtils.setPrecision(2); //开始导出 excelUtils.exportForExcelsOptimize(); } /** * 设置 票据汇总 excel报表数据 * * @param billPollMap: 数据源 * @param retDataList: Sheet数据 * @return: java.util.List<java.util.List < java.lang.String [ ]>> * @author: */ private List<List<Object[]>> setBillSummaryData(TreeMap<String, List<BillPollEntity>> billPollMap, List<List<Object[]>> retDataList, Map<String, BillBackInfoEntity> billBackDataMap) { for (Map.Entry<String, List<BillPollEntity>> m : billPollMap.entrySet()) { //值 List<BillPollEntity> dataList = m.getValue(); //存放sheet数据对象 List<Object[]> sheetList = new ArrayList<>(); //设置列头 sheetList.add(ExcelHeadUtil.getBillSummaryChildHead()); //票据种类 Map<String, String> kindMap = billKindConfigService.getCtNMap(); //设置头部统计 List<Object> totalList = new ArrayList<>(); ExcelTools.setEmpty(totalList, 6); totalList.add(ExcelTools.setFormula(6, 3, (dataList.size() + 2))); ExcelTools.setEmpty(totalList, 3); totalList.add(ExcelTools.setFormula(10, 3, (dataList.size() + 2))); totalList.add(ExcelTools.setFormula(11, 3, (dataList.size() + 2))); ExcelTools.setEmpty(totalList, 1); sheetList.add(ExcelTools.list2ObjArr(totalList)); dataList.forEach(d -> { //设置内容 List<Object> valueList = new ArrayList<>(); valueList.add(d.getBillNo()); valueList.add(kindMap.get(d.getBillKind())); valueList.add(d.getDrawName()); valueList.add(d.getPayeeName()); valueList.add(DUtils.dateFmtYMD(d.getDrawTime())); valueList.add(DUtils.dateFmtYMD(d.getDueTime())); valueList.add(d.getBillAmount()); valueList.add(BillStatusEnum.getName(d.getBillStatus())); BillBackInfoEntity billBackInfoEntity = billBackDataMap.get(d.getBillNo()); if (billBackInfoEntity != null) { valueList.add(DUtils.dateFmtYMD(billBackInfoEntity.getBackTime())); valueList.add(String.valueOf(billBackInfoEntity.getInterestDays())); valueList.add(billBackInfoEntity.getInterest()); valueList.add(billBackInfoEntity.getBackAmount()); valueList.add(String.valueOf(billBackInfoEntity.getInterestRate().multiply(new BigDecimal(100)).setScale(2)).concat("%")); } else { ExcelTools.setEmpty(valueList, 5); } sheetList.add(ExcelTools.list2ObjArr(valueList)); }); retDataList.add(sheetList); } return retDataList; }
3，导出工具类
ExcelUtils：
package com.xxx.common.excel; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.net.URLEncoder; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.*; import static com.jfa.common.excel.CommonsUtils.*; /** * @ProjectName: git-dev * @Package: com.jfa.modules.peak.controller * @ClassName: ExcelUtils * @Author * @Description: Excel 导入导出操作相关工具类 */ @Slf4j public class ExcelUtils { private static Logger log = LoggerFactory.getLogger(ExcelUtils.class); private static final ThreadLocal<SimpleDateFormat> fmt = new ThreadLocal<>(); private static final ThreadLocal<DecimalFormat> df = new ThreadLocal<>(); private static final ThreadLocal<ExcelUtils> UTILS_THREAD_LOCAL = new ThreadLocal<>(); private static final Integer DATE_LENGTH = 10; // private static final String MESSAGE_FORMAT_df = "#.######"; // private static final String MESSAGE_FORMAT = "yyyy-MM-dd"; private SimpleDateFormat getDateFormat() { SimpleDateFormat format = fmt.get(); if (format == null) { //默认格式日期： "yyyy-MM-dd" format = new SimpleDateFormat(expectDateFormatStr, Locale.getDefault()); fmt.set(format); } return format; } public DecimalFormat getDecimalFormat() { DecimalFormat format = df.get(); if (format == null) { //默认数字格式： "#.######" 六位小数点 format = new DecimalFormat(numeralFormat); df.set(format); } return format; } public static final ExcelUtils initialization() { ExcelUtils excelUtils = UTILS_THREAD_LOCAL.get(); if (excelUtils == null) { excelUtils = new ExcelUtils(); UTILS_THREAD_LOCAL.set(excelUtils); } return excelUtils; } public ExcelUtils() { filePath = this.getFilePath(); dataLists = this.getDataLists(); response = this.getResponse(); regionMap = this.getRegionMap(); mapColumnWidth = this.getMapColumnWidth(); styles = this.getStyles(); paneMap = this.getPaneMap(); fileName = this.getFileName(); sheetName = this.getSheetName(); labelName = this.getLabelName(); rowStyles = this.getRowStyles(); columnStyles = this.getColumnStyles(); dropDownMap = this.getDropDownMap(); numeralFormat = this.getNumeralFormat(); dateFormatStr = this.getDateFormatStr(); expectDateFormatStr = this.getExpectDateFormatStr(); defaultColumnWidth = this.getDefaultColumnWidth(); fontSize = this.getFontSize(); precision = this.getPrecision(); linkType = this.getLinkType(); filterMap = this.getFilterMap(); } /** * web 响应（response） * Excel导出：有样式（行、列、单元格样式）、自适应列宽 * 功能描述: excel 数据导出、导出模板 * 更新日志: * 1.response.reset();注释掉reset，否在会出现跨域错误。[2018-05-18] * 2.新增导出多个单元。[2018-08-08] * 3.poi官方建议大数据量解决方案：SXSSFWorkbook。[2018-08-08] * 4.自定义下拉列表：对每个单元格自定义下拉列表。[2018-08-08] * 5.数据遍历方式换成数组(效率较高)。[2018-08-08] * 6.可提供模板下载。[2018-08-08] * 7.每个表格的大标题[2018-09-14] * 8.自定义列宽：对每个单元格自定义列宽[2018-09-18] * 9.自定义样式：对每个单元格自定义样式[2018-10-22] * 10.自定义单元格合并：对每个单元格合并[2018-10-22] * 11.固定表头[2018-10-23] * 12.自定义样式：单元格自定义某一列或者某一行样式[2018-11-12] * 13.忽略边框(默认是有边框)[2018-11-15] * 14.函数式编程换成面向对象编程[2018-12-06-5] * 15.单表百万数据量导出时样式设置过多，导致速度慢（行、列、单元格样式暂时控制10万行、超过无样式）[2019-01-30] * 版 本: * 1.apache poi 3.17 * 2.apache poi-ooxml 3.17 * * @return */ public Boolean exportForExcelsOptimize() { long startTime = System.currentTimeMillis(); log.info("=== === === :Excel tool class export start run!"); SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1000); OutputStream outputStream = null; SXSSFRow sxssfRow = null; try { // 设置数据 setDataList(sxssfWorkbook, sxssfRow, dataLists, regionMap, mapColumnWidth, styles, paneMap, sheetName, labelName, rowStyles, columnStyles, dropDownMap, defaultColumnWidth, fontSize,linkType,slashMap,precision,filterMap,rowStyleMap,rowList); // io 响应 setIo(sxssfWorkbook, outputStream, fileName, sheetName, response); } catch (Exception e) { e.printStackTrace(); } log.info("=== === === :Excel tool class export run time:" + (System.currentTimeMillis() - startTime) + " ms!"); return true; } /** * Excel导出：无样式（行、列、单元格样式）、自适应列宽 * web 响应（response） * * @return */ public Boolean exportForExcelsNoStyle() { long startTime = System.currentTimeMillis(); log.info("=== === === :Excel tool class export start run!"); SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1000); OutputStream outputStream = null; SXSSFRow sxssfRow = null; try { setDataListNoStyle(sxssfWorkbook, sxssfRow, dataLists, regionMap, mapColumnWidth, paneMap, sheetName, labelName, dropDownMap, defaultColumnWidth, fontSize,linkType,precision); setIo(sxssfWorkbook, outputStream, fileName, sheetName, response); } catch (Exception e) { e.printStackTrace(); } log.info("=== === === :Excel tool class export run time:" + (System.currentTimeMillis() - startTime) + " ms!"); return true; } /** * 功能描述: * 1.excel 模板数据导入。 * <p> * 更新日志: * 1.共用获取Excel表格数据。 * 2.多单元数据获取。 * 3.多单元从第几行开始获取数据[2018-09-20] * 4.多单元根据那些列为空来忽略行数据[2018-10-22] * <p> * 版 本: * 1.apache poi 3.17 * 2.apache poi-ooxml 3.17 * * @param book Workbook对象（不可为空） * @param sheetName 多单元数据获取（不可为空） * @param indexMap 多单元从第几行开始获取数据，默认从第二行开始获取（可为空，如 hashMapIndex.put(1,3); 第一个表格从第三行开始获取） * @param continueRowMap 多单元根据那些列为空来忽略行数据（可为空，如 mapContinueRow.put(1,new Integer[]{1, 3}); 第一个表格从1、3列为空就忽略） * @return */ @SuppressWarnings({"deprecation", "rawtypes"}) public static List<List<LinkedHashMap<String, String>>> importForExcelData(Workbook book, String[] sheetName, HashMap indexMap, HashMap continueRowMap) { long startTime = System.currentTimeMillis(); log.info("=== === === :Excel tool class export start run!"); try { List<List<LinkedHashMap<String, String>>> returnDataList = new ArrayList<>(); for (int k = 0; k <= sheetName.length - 1; k++) { // 得到第K个工作表对象、得到第K个工作表中的总行数。 Sheet sheet = book.getSheetAt(k); int rowCount = sheet.getLastRowNum() + 1; Row valueRow = null; List<LinkedHashMap<String, String>> rowListValue = new ArrayList<>(); LinkedHashMap<String, String> cellHashMap = null; int irow = 1; // 第n个工作表:从开始获取数据、默认第一行开始获取。 if (indexMap != null && indexMap.get(k + 1) != null) { irow = Integer.valueOf(indexMap.get(k + 1).toString()) - 1; } // 第n个工作表:数据获取。 for (int i = irow; i < rowCount; i++) { valueRow = sheet.getRow(i); if (valueRow == null) { continue; } // 第n个工作表:从开始列忽略数据、为空就跳过。 if (continueRowMap != null && continueRowMap.get(k + 1) != null) { int continueRowCount = 0; Integer[] continueRow = (Integer[]) continueRowMap.get(k + 1); for (int w = 0; w <= continueRow.length - 1; w++) { Cell valueRowCell = valueRow.getCell(continueRow[w] - 1); if (valueRowCell == null || isBlank(valueRowCell.toString())) { continueRowCount = continueRowCount + 1; } } if (continueRowCount == continueRow.length) { continue; } } cellHashMap = new LinkedHashMap<>(); // 第n个工作表:获取列数据。 for (int j = 0; j < valueRow.getLastCellNum(); j++) { cellHashMap.put(Integer.toString(j), getCellVal(valueRow.getCell(j))); } if (cellHashMap.size() > 0) { rowListValue.add(cellHashMap); } } returnDataList.add(rowListValue); } log.info("=== === === :Excel tool class export run time:" + (System.currentTimeMillis() - startTime) + " ms!"); return returnDataList; } catch (Exception e) { log.debug("=== === === :Andyczy ExcelUtils Exception Message：Excel tool class export exception !"); e.printStackTrace(); return null; } } /** * response 响应 * * @param sxssfWorkbook * @param outputStream * @param fileName * @param sheetName * @param response * @throws Exception */ private static void setIo(SXSSFWorkbook sxssfWorkbook, OutputStream outputStream, String fileName, String[] sheetName, HttpServletResponse response) throws Exception { try { if (response != null) { response.setCharacterEncoding("utf-8"); response.setHeader("Charset", "UTF-8"); response.setHeader("Content-Type", "application/force-download"); response.setHeader("Content-Type", "application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName == null ? sheetName[0] : fileName, "utf8") + ".xlsx"); response.flushBuffer(); outputStream = response.getOutputStream(); } writeAndColse(sxssfWorkbook, outputStream); } catch (Exception e) { e.getSuppressed(); } } /** * 功能描述: 获取Excel单元格中的值并且转换java类型格式 * * @param cell * @return */ private static String getCellVal(Cell cell) { String val = null; if (cell != null) { CellType cellType = cell.getCellType(); switch (cellType) { case NUMERIC: if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { val = ExcelUtils.initialization().getDateFormat().format(cell.getDateCellValue()); } else { val = ExcelUtils.initialization().getDecimalFormat().format(cell.getNumericCellValue()); } break; case STRING: if (cell.getStringCellValue().trim().length() >= DATE_LENGTH && verificationDate(cell.getStringCellValue(), ExcelUtils.initialization().dateFormatStr)) { val = strToDateFormat(cell.getStringCellValue(), ExcelUtils.initialization().dateFormatStr, ExcelUtils.initialization().expectDateFormatStr); } else { val = cell.getStringCellValue(); } break; case BOOLEAN: val = String.valueOf(cell.getBooleanCellValue()); break; case BLANK: val = cell.getStringCellValue(); break; case ERROR: val = "错误"; break; case FORMULA: try { val = String.valueOf(cell.getStringCellValue()); } catch (IllegalStateException e) { val = String.valueOf(cell.getNumericCellValue()); } break; default: val = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString(); } } else { val = ""; } return val; } /** * 导出数据必填 */ private List<List<Object[]>> dataLists; /** * sheet名称必填 */ private String[] sheetName; /** * 每个表格的大标题 */ private String[] labelName; /** * 页面响应 */ private HttpServletResponse response; /** * 自定义：单元格合并 */ private HashMap regionMap; /** * 自定义：对每个单元格自定义列宽 */ private HashMap mapColumnWidth; /** * 自定义：每一个单元格样式 */ private HashMap styles; /** * 自定义：固定表头 */ private HashMap paneMap; /** * 自定义：设置斜线 */ private HashMap slashMap; /** * 自定义：某一行样式 */ private HashMap rowStyles; /** * 自定义：某一列样式 */ private HashMap columnStyles; /** * 自定义：对每个单元格自定义下拉列表 */ private HashMap dropDownMap; /** * 文件名称 */ private String fileName; /** * 导出本地路径 */ private String filePath; /** * 导出数字格式化：默认是保留六位小数点 */ private String numeralFormat; /** * 导出日期格式化：默认是"yyyy-MM-dd"格式 */ private String dateFormatStr; /** * 期望转换后的日期格式：默认是 dateFormatStr */ private String expectDateFormatStr; /** * 默认列宽大小：默认16 */ private Integer defaultColumnWidth; /** * 默认字体大小：默认12号字体 */ private Integer fontSize; /** * 超链接类型;DOCUMENT = 文档 */ private String linkType; /** * 自定义数字精度 */ private Integer precision = 0; /** * 自定义过滤列 */ private HashMap filterMap; /** * 自定义行 样式 */ private HashMap rowStyleMap; /** * 自定义行 样式 */ private List<Integer> rowList; public Integer getFontSize() { if (fontSize == null) { fontSize = 12; } return fontSize; } public void setFontSize(Integer fontSize) { this.fontSize = fontSize; } public Integer getDefaultColumnWidth() { if (defaultColumnWidth == null) { defaultColumnWidth = 30; } return defaultColumnWidth; } public void setDefaultColumnWidth(Integer defaultColumnWidth) { this.defaultColumnWidth = defaultColumnWidth; } public void setDateFormatStr(String dateFormatStr) { if (dateFormatStr == null) { dateFormatStr = "yyyy-MM-dd"; } this.dateFormatStr = dateFormatStr; } public String getDateFormatStr() { if (dateFormatStr == null) { dateFormatStr = "yyyy-MM-dd"; } return dateFormatStr; } public String getExpectDateFormatStr() { if (expectDateFormatStr == null) { expectDateFormatStr = dateFormatStr; } return expectDateFormatStr; } public void setExpectDateFormatStr(String expectDateFormatStr) { if (expectDateFormatStr == null) { expectDateFormatStr = dateFormatStr; } this.expectDateFormatStr = expectDateFormatStr; } public void setNumeralFormat(String numeralFormat) { if (numeralFormat == null) { numeralFormat = "#.######"; } this.numeralFormat = numeralFormat; } public String getNumeralFormat() { if (numeralFormat == null) { numeralFormat = "#.######"; } return numeralFormat; } public List<List<Object[]>> getDataLists() { return dataLists; } public void setDataLists(List<List<Object[]>> dataLists) { this.dataLists = dataLists; } public String[] getSheetName() { return sheetName; } public void setSheetName(String[] sheetName) { this.sheetName = sheetName; } public String[] getLabelName() { return labelName; } public void setLabelName(String[] labelName) { this.labelName = labelName; } public HttpServletResponse getResponse() { return response; } public void setResponse(HttpServletResponse response) { this.response = response; } public HashMap getRegionMap() { return regionMap; } public void setRegionMap(HashMap regionMap) { this.regionMap = regionMap; } public HashMap getMapColumnWidth() { return mapColumnWidth; } public void setMapColumnWidth(HashMap mapColumnWidth) { this.mapColumnWidth = mapColumnWidth; } public HashMap getStyles() { return styles; } public void setStyles(HashMap styles) { this.styles = styles; } public HashMap getPaneMap() { return paneMap; } public void setPaneMap(HashMap paneMap) { this.paneMap = paneMap; } public HashMap getRowStyles() { return rowStyles; } public void setRowStyles(HashMap rowStyles) { this.rowStyles = rowStyles; } public HashMap getColumnStyles() { return columnStyles; } public void setColumnStyles(HashMap columnStyles) { this.columnStyles = columnStyles; } public HashMap getDropDownMap() { return dropDownMap; } public void setDropDownMap(HashMap dropDownMap) { this.dropDownMap = dropDownMap; } public String getFileName() { return fileName; } public void setFileName(String fileName) { this.fileName = fileName; } public String getFilePath() { return filePath; } public void setFilePath(String filePath) { this.filePath = filePath; } public String getLinkType() { return linkType; } public void setLinkType(String linkType) { this.linkType = linkType; } public HashMap getSlashMap() { return slashMap; } public void setSlashMap(HashMap slashMap) { this.slashMap = slashMap; } public Integer getPrecision() { return precision; } public void setPrecision(Integer precision) { this.precision = precision; } public HashMap getFilterMap() { return filterMap; } public void setFilterMap(HashMap filterMap) { this.filterMap = filterMap; } public HashMap getRowStyleMap() { return rowStyleMap; } public void setRowStyleMap(HashMap rowStyleMap) { this.rowStyleMap = rowStyleMap; } public List<Integer> getRowList() { return rowList; } public void setRowList(List<Integer> rowList) { this.rowList = rowList; } }
CommonsUtils：
package com.xxx.common.excel; import org.apache.commons.lang.StringUtils; import org.apache.poi.common.usermodel.HyperlinkType; import org.apache.poi.hssf.usermodel.HSSFPalette; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.xssf.streaming.SXSSFDrawing; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.swing.text.AttributeSet; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.math.BigDecimal; import java.net.HttpURLConnection; import java.net.URL; import java.text.SimpleDateFormat; import java.util.*; import java.util.regex.Matcher; import java.util.regex.Pattern; import static org.apache.poi.ss.usermodel.ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE; import static org.apache.poi.ss.util.CellUtil.createCell; /** * @ProjectName: git-dev * @Package: com.jfa.common.excel * @ClassName: CommonsUtils * @Author: * @Description: */ public class CommonsUtils { private static Logger log = LoggerFactory.getLogger(CommonsUtils.class); private static final String DataValidationError1 = "Excel表格提醒："; private static final String DataValidationError2 = "数据不规范，请选择表格下拉列表中的数据！"; public static final Integer MAX_ROWSUM = 1048570; public static final Integer MAX_ROWSYTLE = 100000; public static final String BIGDECIMAL_CLASS = "class java.math.BigDecimal"; /** * 超链接类型;DOCUMENT = 文档 */ public static final String DOCUMENT = "DOCUMENT"; /** * 超链接类型;DOCUMENT = 文档 */ public static final String GS = "公式:"; /** * 设置数据：有样式（行、列、单元格样式） * * @param wb * @param sxssfRow * @param dataLists * @param regionMap * @param columnMap * @param styles * @param paneMap * @param sheetName * @param labelName * @param rowStyles * @param columnStyles * @param dropDownMap * @throws Exception */ public static void setDataList(SXSSFWorkbook wb, SXSSFRow sxssfRow, List<List<Object[]>> dataLists, HashMap regionMap, HashMap columnMap, HashMap styles, HashMap paneMap, String[] sheetName, String[] labelName, HashMap rowStyles, HashMap columnStyles, HashMap dropDownMap, Integer defaultColumnWidth, Integer fontSize, String linkType, HashMap slashMap, Integer precision, HashMap filterMap, HashMap rowStyleMap, List<Integer> rowList) throws Exception { if (dataLists == null) { log.debug("=== === === :Andyczy ExcelUtils Exception Message：Export data(type:List<List<String[]>>) cannot be empty!"); } if (sheetName == null) { log.debug("=== === === :Andyczy ExcelUtils Exception Message：Export sheet(type:String[]) name cannot be empty!"); } int k = 0; for (List<Object[]> listRow : dataLists) { SXSSFSheet sxssfSheet = wb.createSheet(); sxssfSheet.setForceFormulaRecalculation(true); // sxssfSheet.setDefaultColumnWidth(defaultColumnWidth); wb.setSheetName(k, sheetName[k]); CellStyle cellStyle = wb.createCellStyle(); XSSFFont font = (XSSFFont) wb.createFont(); SXSSFDrawing sxssfDrawing = sxssfSheet.createDrawingPatriarch(); int jRow = 0; // 自定义：大标题（看该方法说明）。 jRow = setLabelName(jRow, k, wb, labelName, sxssfRow, sxssfSheet, listRow); // 自定义：每个表格固定表头（看该方法说明）。 if (paneMap != null && paneMap.get(k + 1) != null) { createFreezePane(sxssfSheet, (ArrayList<Integer[]>) paneMap.get(k + 1)); } // 自定义：设置斜线 if (slashMap != null && slashMap.get(k + 1) != null) { createSlash(wb, sxssfSheet, (ArrayList<Integer[]>) slashMap.get(k + 1)); } // 自定义：过滤数据 if (filterMap != null && filterMap.get(k + 1) != null) { createFilter(sxssfSheet, filterMap, k + 1); } // 自定义：每个单元格自定义合并单元格：对每个单元格自定义合并单元格（看该方法说明）。 if (regionMap != null) { setMergedRegion(sxssfSheet, (ArrayList<Integer[]>) regionMap.get(k + 1)); } // 自定义：每个单元格自定义下拉列表：对每个单元格自定义下拉列表（看该方法说明）。 if (dropDownMap != null) { setDataValidation(sxssfSheet, (List<String[]>) dropDownMap.get(k + 1), listRow.size()); } // 自定义：每个表格自定义列宽：对每个单元格自定义列宽（看该方法说明）。 if (columnMap != null) { setColumnWidth(sxssfSheet, (HashMap) columnMap.get(k + 1)); } // 默认样式。 setStyle(cellStyle, font, fontSize); CellStyle cell_style = null; CellStyle row_style = null; CellStyle column_style = null; // 写入小标题与数据。 Integer SIZE = listRow.size() < MAX_ROWSUM ? listRow.size() : MAX_ROWSUM; Integer MAXSYTLE = listRow.size() < MAX_ROWSYTLE ? listRow.size() : MAX_ROWSYTLE; Map<Integer, Integer> sizeMap = new HashMap<>(); sxssfSheet.setForceFormulaRecalculation(true); for (int i = 0; i < SIZE; i++) { sxssfRow = sxssfSheet.createRow(jRow); for (int j = 0; j < listRow.get(i).length; j++) { // 样式过多会导致GC内存溢出！ try { Cell cell = null; if (patternIsImg(String.valueOf(listRow.get(i)[j]))) { cell = createCell(sxssfRow, j, " "); drawPicture(wb, sxssfDrawing, String.valueOf(listRow.get(i)[j]), j, jRow); } else { cell = createCell(sxssfRow, j, String.valueOf(listRow.get(i)[j])); } /** ========== 单元格类型；设置对应格式 ========start== */ //BigDecimal 类型 if (BIGDECIMAL_CLASS.equals(listRow.get(i)[j].getClass().toString())) { cell.setCellValue(Double.valueOf(String.valueOf(listRow.get(i)[j]))); XSSFDataFormat format = wb.getXSSFWorkbook().createDataFormat(); String defualtFmt = "_ * #,##0 ;_ * -#,##0 ;_ * \"-\"??_ ;_ @_ "; switch (precision) { case 1: defualtFmt = "_ * #,##0.0 ;_ * -#,##0.0 ;_ * \"-\"??_ ;_ @_ "; break; case 2: defualtFmt = "_ * #,##0.00 ;_ * -#,##0.00 ;_ * \"-\"??_ ;_ @_ "; break; case 3: defualtFmt = "_ * #,##0.000 ;_ * -#,##0.000 ;_ * \"-\"??_ ;_ @_ "; break; case 4: defualtFmt = "_ * #,##0.0000 ;_ * -#,##0.0000 ;_ * \"-\"??_ ;_ @_ "; break; } cellStyle.setDataFormat(format.getFormat(defualtFmt)); } /** ========== 单元格类型；设置对应格式 ========end== */ //设置单元格样式 cell.setCellStyle(cellStyle); /** ========== 设置公式 ========start== */ if (String.valueOf(listRow.get(i)[j]).indexOf(GS) != -1) { String rowCell = String.valueOf(listRow.get(i)[j]).replace(GS, ""); cell.setCellFormula(rowCell); } /** ========== 设置公式 ========end== */ /** ========== 超链接设置 ========start== */ if (DOCUMENT.equals(linkType)) { //设置超链接 if (Arrays.asList(sheetName).contains(listRow.get(i)[j])) { for (int n = 1; n < sheetName.length; n++) { if (sheetName[n].equals(listRow.get(i)[j])) { CreationHelper createHelper = wb.getCreationHelper(); XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.DOCUMENT); link.setAddress("#'" + sheetName[n] + "'!A1"); //设置超链接样式 XSSFCellStyle linkStyle = wb.getXSSFWorkbook().createCellStyle(); XSSFFont cellFont = wb.getXSSFWorkbook().createFont(); cellFont.setUnderline((byte) 1); cellFont.setColor(IndexedColors.fromInt(4).index); cellFont.setFontName("Arial"); cellFont.setFontHeight(12); linkStyle.setAlignment(HorizontalAlignment.CENTER); linkStyle.setVerticalAlignment(VerticalAlignment.CENTER); linkStyle.setFont(cellFont); cell.setCellStyle(linkStyle); //超链接 setBorder(linkStyle, true); cell.setHyperlink(link); break; } } } } /** ========== 超链接设置 ========start== */ // 自定义：每个表格每一列的样式（看该方法说明）。 // if (columnStyles != null && jRow >= pane && i <= MAXSYTLE) { if (columnStyles != null && i <= MAXSYTLE) { setExcelRowStyles(cell, wb, sxssfRow, (List) columnStyles.get(k + 1), j); } // 自定义：每个表格每一行的样式（看该方法说明）。 if (rowStyles != null && i <= MAXSYTLE) { setExcelRowStyles(cell, wb, sxssfRow, (List) rowStyles.get(k + 1), jRow); } if (Arrays.asList(listRow.get(i)).contains("小计") || Arrays.asList(listRow.get(i)).contains("总计")) { setTotalStyles(wb, cell); } // 自定义标题样式 if (rowList != null && rowList.size() > 0) { for (int rowListIndex = 0; rowListIndex < rowList.size(); rowListIndex++) { if (rowList.get(rowListIndex) == i) { setSubTitleStyles(wb, cell, sxssfRow, slashMap); } } } else { if (i == 0) { setSubTitleStyles(wb, cell, sxssfRow, slashMap); } } // 自定义：每一个单元格样式（看该方法说明）。 if (styles != null && i <= MAXSYTLE) { setExcelStyles(cell, wb, sxssfRow, (List<List<Object[]>>) styles.get(k + 1), j, i); } //筛选单元格最大长度 if (sizeMap.get(j) != null && !CellType.FORMULA.equals(cell.getCellType())) { // BigDecimal b = ((new BigDecimal(sizeMap.get(j)).add(new BigDecimal(listRow.get(i)[j].getBytes().length))).divide(new BigDecimal(2)).setScale(0, RoundingMode.HALF_UP)); // sizeMap.put(j, Integer.valueOf(String.valueOf(b))); sizeMap.put(j, Math.max(sizeMap.get(j), String.valueOf(listRow.get(i)[j]).getBytes().length)); } else { sizeMap.put(j, String.valueOf(listRow.get(i)[j]).getBytes().length); } } catch (Exception e) { log.debug("=== === === :Andyczy ExcelUtils Exception Message：The maximum number of cell styles was exceeded. You can define up to 4000 styles!"); } } jRow++; sxssfSheet.setForceFormulaRecalculation(true); } //设置单元格长度, 这里要乘上256 for (Integer i : sizeMap.keySet()) { sxssfSheet.setColumnWidth(i, (sizeMap.get(i) + 9) * 256); } k++; sxssfSheet.setForceFormulaRecalculation(true); } } private static boolean hasDouble(String s) { boolean bol = true; try { Double.valueOf(s); } catch (Exception e) { bol = false; } return bol; } /** * @param cell Cell对象。 * @param wb SXSSFWorkbook对象。 * @param fontSize 字体大小。 * @param bold 是否加粗。 * @param center 是否左右上下居中。 * @param isBorder 是否忽略边框 * @param leftBoolean 左对齐 * @param rightBoolean 右对齐 * @param height 行高 */ public static void setExcelStyles(Cell cell, SXSSFWorkbook wb, SXSSFRow sxssfRow, Integer fontSize, Boolean bold, Boolean center, Boolean isBorder, Boolean leftBoolean, Boolean rightBoolean, Integer fontColor, Integer height) { CellStyle cellStyle = cell.getRow().getSheet().getWorkbook().createCellStyle(); //保证了既可以新建一个CellStyle，又可以不丢失原来的CellStyle 的样式 cellStyle.cloneStyleFrom(cell.getCellStyle()); //左右居中、上下居中 if (center != null && center) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); } //右对齐 if (rightBoolean != null && rightBoolean) { cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.RIGHT); } //左对齐 if (leftBoolean != null && leftBoolean) { cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.LEFT); } //是否忽略边框 if (isBorder != null && isBorder) { setBorderColor(cellStyle, isBorder); } //设置单元格字体样式 XSSFFont font = (XSSFFont) wb.createFont(); if (bold != null && bold) { font.setBold(bold); } //行高 if (height != null) { sxssfRow.setHeight((short) (height * 2)); } font.setFontName("Arial"); font.setFontHeight(fontSize == null ? 12 : fontSize); cellStyle.setFont(font); // 点击可查看颜色对应的值： BLACK(8), WHITE(9), RED(10), font.setColor(IndexedColors.fromInt(fontColor == null ? 8 : fontColor).index); //金额右对齐 if (BIGDECIMAL_CLASS.equals(sxssfRow.getClass().toString())) { cellStyle.setAlignment(HorizontalAlignment.RIGHT); } cell.setCellStyle(cellStyle); } public static void setExcelTitleStyles(Cell cell, SXSSFWorkbook wb, SXSSFRow sxssfRow, List<Object[]> rowstyleList, int rowIndex) { if (rowstyleList != null && rowstyleList.size() > 0) { Integer[] rowstyle = (Integer[]) rowstyleList.get(1); for (int i = 0; i < rowstyle.length; i++) { if (rowIndex == rowstyle[i]) { Boolean[] bool = (Boolean[]) rowstyleList.get(0); Integer fontColor = null; Integer fontSize = null; Integer height = null; //当有设置颜色值 、字体大小、行高才获取值 if (rowstyleList.size() >= 3) { int leng = rowstyleList.get(2).length; if (leng >= 1) { fontColor = (Integer) rowstyleList.get(2)[0]; } if (leng >= 2) { fontSize = (Integer) rowstyleList.get(2)[1]; } if (leng >= 3) { height = (Integer) rowstyleList.get(2)[2]; } } setExcelStyles(cell, wb, sxssfRow, fontSize, Boolean.valueOf(bool[3]), Boolean.valueOf(bool[0]), Boolean.valueOf(bool[4]), Boolean.valueOf(bool[2]), Boolean.valueOf(bool[1]), fontColor, height); } } } } public static void setExcelRowStyles(Cell cell, SXSSFWorkbook wb, SXSSFRow sxssfRow, List<Object[]> rowstyleList, int rowIndex) { if (rowstyleList != null && rowstyleList.size() > 0) { Integer[] rowstyle = (Integer[]) rowstyleList.get(1); for (int i = 0; i < rowstyle.length; i++) { if (rowIndex == rowstyle[i]) { Boolean[] bool = (Boolean[]) rowstyleList.get(0); Integer fontColor = null; Integer fontSize = null; Integer height = null; //当有设置颜色值 、字体大小、行高才获取值 if (rowstyleList.size() >= 3) { int leng = rowstyleList.get(2).length; if (leng >= 1) { fontColor = (Integer) rowstyleList.get(2)[0]; } if (leng >= 2) { fontSize = (Integer) rowstyleList.get(2)[1]; } if (leng >= 3) { height = (Integer) rowstyleList.get(2)[2]; } } setExcelStyles(cell, wb, sxssfRow, fontSize, Boolean.valueOf(bool[3]), Boolean.valueOf(bool[0]), Boolean.valueOf(bool[4]), Boolean.valueOf(bool[2]), Boolean.valueOf(bool[1]), fontColor, height); } } } } /** * 画图片 * @param wb * @param sxssfSheet * @param pictureUrl * @param rowIndex */ /** * 画图片 * * @param wb * @param sxssfDrawing * @param pictureUrl * @param rowIndex */ private static void drawPicture(SXSSFWorkbook wb, SXSSFDrawing sxssfDrawing, String pictureUrl, int colIndex, int rowIndex) { //rowIndex代表当前行 try { if (pictureUrl != null) { URL url = new URL(pictureUrl); //打开链接 HttpURLConnection conn = (HttpURLConnection) url.openConnection(); conn.setRequestMethod("GET"); conn.setConnectTimeout(5 * 1000); InputStream inStream = conn.getInputStream(); byte[] data = readInputStream(inStream); //设置图片大小， XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 50, 50, colIndex, rowIndex, colIndex + 1, rowIndex + 1); anchor.setAnchorType(DONT_MOVE_AND_RESIZE); sxssfDrawing.createPicture(anchor, wb.addPicture(data, XSSFWorkbook.PICTURE_TYPE_JPEG)); } } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } /** * 是否是图片 * * @param str * @return */ public static Boolean patternIsImg(String str) { String reg = ".+(.JPEG|.jpeg|.JPG|.jpg|.png|.gif)$"; Pattern pattern = Pattern.compile(reg); Matcher matcher = pattern.matcher(str); Boolean temp = matcher.find(); return temp; } /** * @param inStream * @return * @throws Exception */ private static byte[] readInputStream(InputStream inStream) throws Exception { ByteArrayOutputStream outStream = new ByteArrayOutputStream(); //创建一个Buffer字符串 byte[] buffer = new byte[1024]; //每次读取的字符串长度，如果为-1，代表全部读取完毕 int len = 0; //使用一个输入流从buffer里把数据读取出来 while ((len = inStream.read(buffer)) != -1) { //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度 outStream.write(buffer, 0, len); } //关闭输入流 inStream.close(); //把outStream里的数据写入内存 return outStream.toByteArray(); } /** * 自定义：大标题 * * @param jRow * @param k * @param wb * @param labelName * @param sxssfRow * @param sxssfSheet * @param listRow * @return */ private static int setLabelName(Integer jRow, Integer k, SXSSFWorkbook wb, String[] labelName, SXSSFRow sxssfRow, SXSSFSheet sxssfSheet, List<Object[]> listRow) { if (labelName != null) { // 自定义：大标题和样式。参数说明：new String[]{"表格数据一", "表格数据二", "表格数据三"} sxssfRow = sxssfSheet.createRow(0); Cell cell = createCell(sxssfRow, 0, labelName[k]); setMergedRegion(sxssfSheet, 0, 0, 0, listRow.get(0).length - 1); setLabelStyles(wb, cell, sxssfRow); jRow = 1; } return jRow; } /** * 设置数据：无样式（行、列、单元格样式） * * @param wb * @param sxssfRow * @param dataLists * @param regionMap * @param columnMap * @param paneMap * @param sheetName * @param labelName * @param dropDownMap * @throws Exception */ public static void setDataListNoStyle(SXSSFWorkbook wb, SXSSFRow sxssfRow, List<List<Object[]>> dataLists, HashMap regionMap, HashMap columnMap, HashMap paneMap, String[] sheetName, String[] labelName, HashMap dropDownMap, Integer defaultColumnWidth, Integer fontSize, String linkType, Integer precision) throws Exception { if (dataLists == null) { log.debug("=== === === :Andyczy ExcelUtils Exception Message：Export data(type:List<List<String[]>>) cannot be empty!"); } if (sheetName == null) { log.debug("=== === === :Andyczy ExcelUtils Exception Message：Export sheet(type:String[]) name cannot be empty!"); } int k = 0; for (List<Object[]> listRow : dataLists) { SXSSFSheet sxssfSheet = wb.createSheet(); sxssfSheet.setDefaultColumnWidth(defaultColumnWidth); wb.setSheetName(k, sheetName[k]); CellStyle cellStyle = wb.createCellStyle(); XSSFFont font = (XSSFFont) wb.createFont(); int jRow = 0; // 自定义：大标题（看该方法说明）。 jRow = setLabelName(jRow, k, wb, labelName, sxssfRow, sxssfSheet, listRow); // 自定义：每个表格固定表头（看该方法说明）。 if (paneMap != null && paneMap.get(k + 1) != null) { createFreezePane(sxssfSheet, (ArrayList<Integer[]>) paneMap.get(k + 1)); } // 自定义：每个单元格自定义合并单元格：对每个单元格自定义合并单元格（看该方法说明）。 if (regionMap != null) { setMergedRegion(sxssfSheet, (ArrayList<Integer[]>) regionMap.get(k + 1)); } // 自定义：每个单元格自定义下拉列表：对每个单元格自定义下拉列表（看该方法说明）。 if (dropDownMap != null) { setDataValidation(sxssfSheet, (List<String[]>) dropDownMap.get(k + 1), listRow.size()); } // 自定义：每个表格自定义列宽：对每个单元格自定义列宽（看该方法说明）。 if (columnMap != null) { setColumnWidth(sxssfSheet, (HashMap) columnMap.get(k + 1)); } // 默认样式。 setStyle(cellStyle, font, fontSize); // 写入小标题与数据。 Integer SIZE = listRow.size() < MAX_ROWSUM ? listRow.size() : MAX_ROWSUM; for (int i = 0; i < SIZE; i++) { sxssfRow = sxssfSheet.createRow(jRow); for (int j = 0; j < listRow.get(i).length; j++) { Cell cell = createCell(sxssfRow, j, String.valueOf(listRow.get(i)[j])); cell.setCellStyle(cellStyle); } jRow++; } k++; } } public static void writeAndColse(SXSSFWorkbook sxssfWorkbook, OutputStream outputStream) throws Exception { try { if (outputStream != null) { sxssfWorkbook.setForceFormulaRecalculation(true);// 执行公式 sxssfWorkbook.write(outputStream); sxssfWorkbook.dispose(); outputStream.flush(); outputStream.close(); } } catch (Exception e) { System.out.println(" Andyczy ExcelUtils Exception Message：Output stream is not empty !"); e.getSuppressed(); } } /** * 功能描述：所有自定义单元格样式 * * @param cell * @param wb * @param styles */ public static void setExcelStyles(Cell cell, SXSSFWorkbook wb, SXSSFRow sxssfRow, List<List<Object[]>> styles, int cellIndex, int rowIndex) { if (styles != null) { for (int z = 0; z < styles.size(); z++) { List<Object[]> stylesList = styles.get(z); if (stylesList != null) { //样式 Boolean[] bool = (Boolean[]) stylesList.get(0); //颜色和字体 Integer fontColor = null; Integer fontSize = null; Integer height = null; //当有设置颜色值 、字体大小、行高才获取值 if (stylesList.size() >= 2) { int leng = stylesList.get(1).length; if (leng >= 1) { fontColor = (Integer) stylesList.get(1)[0]; } if (leng >= 2) { fontSize = (Integer) stylesList.get(1)[1]; } if (leng >= 3) { height = (Integer) stylesList.get(1)[2]; } } //第几行第几列 for (int m = 2; m < stylesList.size(); m++) { Integer[] str = (Integer[]) stylesList.get(m); if (cellIndex + 1 == (str[1]) && rowIndex + 1 == (str[0])) { setExcelStyles(cell, wb, sxssfRow, fontSize, Boolean.valueOf(bool[3]), Boolean.valueOf(bool[0]), Boolean.valueOf(bool[4]), Boolean.valueOf(bool[2]), Boolean.valueOf(bool[1]), fontColor, height); } } } } } } /** * 大标题样式 * * @param wb * @param cell * @param sxssfRow */ public static void setLabelStyles(SXSSFWorkbook wb, Cell cell, SXSSFRow sxssfRow) { CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); sxssfRow.setHeight((short) (399 * 2)); XSSFFont font = (XSSFFont) wb.createFont(); font.setFontName("Arial"); font.setFontHeight(14); font.setBold(true); cellStyle.setFont(font); cell.setCellStyle(cellStyle); } /** * 合计/小计 样式 * * @param wb * @param cell */ public static void setTotalStyles(SXSSFWorkbook wb, Cell cell) { XSSFCellStyle cellStyle = wb.getXSSFWorkbook().createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); XSSFFont font = (XSSFFont) wb.createFont(); font.setFontName("Arial"); font.setFontHeight(12); // font.setColor(new XSSFColor(new java.awt.Color(221,235,247))); font.setBold(true); cellStyle.setFont(font); cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(189, 235, 247))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); setBorder(cellStyle, true); cell.setCellStyle(cellStyle); } /** * 小标题样式 * * @param wb * @param cell */ public static void setSubTitleStyles(SXSSFWorkbook wb, Cell cell, SXSSFRow sxssfRow, HashMap slashMap) { XSSFCellStyle cellStyle = wb.getXSSFWorkbook().createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); if (slashMap != null) { sxssfRow.setHeight((short) (399 * 1.8)); } XSSFFont font = (XSSFFont) wb.createFont(); font.setFontName("Arial"); font.setFontHeight(12); font.setColor(IndexedColors.WHITE.index); font.setBold(true); cellStyle.setFont(font); cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(47, 117, 181))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); setBorder(cellStyle, true); cell.setCellStyle(cellStyle); } /** * 默认样式 * * @param cellStyle * @param font * @return * @Parm */ public static void setStyle(CellStyle cellStyle, XSSFFont font, Integer fontSize) { cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); font.setFontName("Arial"); cellStyle.setFont(font); font.setFontHeight(fontSize); setBorder(cellStyle, true); } /** * 判断字符串是否为空 * * @param str * @return */ public static boolean isBlank(String str) { int strLen; if (str != null && (strLen = str.length()) != 0) { for (int i = 0; i < strLen; ++i) { if (!Character.isWhitespace(str.charAt(i))) { return false; } } return true; } else { return true; } } /** * 功能描述: 锁定行（固定表头） * * @param sxssfSheet * @param row */ public static void createFreezePane(SXSSFSheet sxssfSheet, Integer row) { if (row != null && row > 0) { sxssfSheet.createFreezePane(0, row, 0, 1); } } /** * 功能描述: 锁定行/列（固定表头/列头） * * @param sxssfSheet * @param rowColList */ public static void createFreezePane(SXSSFSheet sxssfSheet, ArrayList<Integer[]> rowColList) { Optional.ofNullable(rowColList).filter(s -> s.size() > 0).ifPresent(s -> { for (int i = 0; i < rowColList.size(); i++) { Integer[] str = rowColList.get(i); if (str.length > 0 && str.length == 2) { int colSplit = str[0]; //冻结的列数 int rowSplit = str[1]; //冻结的行数 // int leftmostColumn = str[2]; //首列可见序号 // int topRow = str[3]; //首行可见序号 // colSplit=冻结的列数、rowSplit=冻结的行数、leftmostColumn=首列序号、leftmostColumn=首行序号 // sxssfSheet.createFreezePane(colSplit, rowSplit, leftmostColumn, leftmostColumn); sxssfSheet.createFreezePane(colSplit, rowSplit); } } }); } /** * 功能描述: 设置斜线 * * @param sxssfSheet * @param rowColList */ public static void createSlash(SXSSFWorkbook wb, SXSSFSheet sxssfSheet, ArrayList<Integer[]> rowColList) { Optional.ofNullable(rowColList).filter(s -> s.size() > 0).ifPresent(s -> { for (int i = 0; i < rowColList.size(); i++) { Integer[] str = rowColList.get(i); if (str.length > 0 && str.length == 4) { CreationHelper helper = wb.getCreationHelper(); XSSFDrawing drawing = sxssfSheet.getDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); // 设置斜线的开始位置 anchor.setCol1(str[0]); anchor.setRow1(str[1]); // 设置斜线的结束位置 anchor.setCol2(str[2]); anchor.setRow2(str[3]); XSSFSimpleShape shape = drawing.createSimpleShape((XSSFClientAnchor) anchor); // 设置形状类型为线型 shape.setShapeType(ShapeTypes.LINE); // 设置线宽 shape.setLineWidth(0.3); // 设置线的风格 shape.setLineStyle(0); // 设置线的颜色 shape.setLineStyleColor(00, 00, 00); } } }); } public static void createFilter(SXSSFSheet sxssfSheet, HashMap dataFilter, Integer k) { Optional.ofNullable(dataFilter).filter(s -> s.size() > 0).ifPresent(s -> { String value = String.valueOf(s.get(k)); if (StringUtils.isNotBlank(value)) { CellRangeAddress c = CellRangeAddress.valueOf(value); sxssfSheet.setAutoFilter(c); } }); } /** * 功能描述: 自定义列宽 * * @param sxssfSheet * @param map */ public static void setColumnWidth(SXSSFSheet sxssfSheet, HashMap map) { if (map != null) { Iterator iterator = map.entrySet().iterator(); while (iterator.hasNext()) { Map.Entry entry = (Map.Entry) iterator.next(); Object key = entry.getKey(); Object val = entry.getValue(); sxssfSheet.setColumnWidth((int) key, (int) val * 512); } } } /** * 功能描述: excel 合并单元格 * * @param sheet * @param rowColList */ public static void setMergedRegion(SXSSFSheet sheet, ArrayList<Integer[]> rowColList) { if (rowColList != null && rowColList.size() > 0) { for (int i = 0; i < rowColList.size(); i++) { Integer[] str = rowColList.get(i); if (str.length > 0 && str.length == 4) { Integer firstRow = str[0]; Integer lastRow = str[1]; Integer firstCol = str[2]; Integer lastCol = str[3]; setMergedRegion(sheet, firstRow, lastRow, firstCol, lastCol); } } } } /** * 功能描述: 合并单元格 * * @param sheet * @param firstRow * @param lastRow * @param firstCol * @param lastCol */ public static void setMergedRegion(SXSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) { sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol)); } /** * 功能描述:下拉列表 * * @param sheet * @param dropDownListData * @param dataListSize */ public static void setDataValidation(SXSSFSheet sheet, List<String[]> dropDownListData, int dataListSize) { if (dropDownListData.size() > 0) { for (int col = 0; col < dropDownListData.get(0).length; col++) { Integer colv = Integer.parseInt(dropDownListData.get(0)[col]); setDataValidation(sheet, dropDownListData.get(col + 1), 1, dataListSize < 100 ? 500 : dataListSize, colv, colv); } } } /** * 功能描述:下拉列表 * * @param xssfWsheet * @param list * @param firstRow * @param lastRow * @param firstCol * @param lastCol */ public static void setDataValidation(SXSSFSheet xssfWsheet, String[] list, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) { DataValidationHelper helper = xssfWsheet.getDataValidationHelper(); CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); DataValidationConstraint constraint = helper.createExplicitListConstraint(list); DataValidation dataValidation = helper.createValidation(constraint, addressList); dataValidation.createErrorBox(DataValidationError1, DataValidationError2); // 处理Excel兼容性问题 if (dataValidation instanceof XSSFDataValidation) { dataValidation.setSuppressDropDownArrow(true); dataValidation.setShowErrorBox(true); } else { dataValidation.setSuppressDropDownArrow(false); } xssfWsheet.addValidationData(dataValidation); } /** * 功能描述：设置边框 * * @param cellStyle * @param isBorder */ public static void setBorder(CellStyle cellStyle, Boolean isBorder) { if (isBorder) { cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); } else { //添加白色背景，统一设置边框后但不能选择性去掉，只能通过背景覆盖达到效果。 cellStyle.setBottomBorderColor(IndexedColors.WHITE.getIndex()); cellStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex()); cellStyle.setRightBorderColor(IndexedColors.WHITE.getIndex()); cellStyle.setTopBorderColor(IndexedColors.WHITE.getIndex()); } } public static void setBorderColor(CellStyle cellStyle, Boolean isBorder) { if (isBorder) { //添加白色背景，统一设置边框后但不能选择性去掉，只能通过背景覆盖达到效果。 cellStyle.setBottomBorderColor(IndexedColors.WHITE.getIndex()); cellStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex()); cellStyle.setRightBorderColor(IndexedColors.WHITE.getIndex()); cellStyle.setTopBorderColor(IndexedColors.WHITE.getIndex()); } } /** * 验证是否是日期 * * @param strDate * @return */ public static boolean verificationDate(String strDate, String style) { Date date = null; if (style == null) { style = "yyyy-MM-dd"; } SimpleDateFormat formatter = new SimpleDateFormat(style); try { formatter.parse(strDate); } catch (Exception e) { return false; } return true; } public static String strToDateFormat(String strDate, String style, String expectDateFormatStr) { Date date = null; if (style == null) { style = "yyyy-MM-dd"; } // 日期字符串转成date类型 SimpleDateFormat formatter = new SimpleDateFormat(style); try { date = formatter.parse(strDate); } catch (Exception e) { return null; } // 转成指定的日期格式 SimpleDateFormat sdf = new SimpleDateFormat(expectDateFormatStr == null ? style : expectDateFormatStr); String str = sdf.format(date); return str; } }
ExcelTools：
package com.xxx.common.excel; import org.apache.poi.ss.util.CellReference; import java.math.BigDecimal; import java.util.List; /** * @ProjectName: git-dev * @Package: com.jfa.common.utils * @ClassName: ExcelTools * @Author: * @Description: excel 工具类 */ public class ExcelTools { /** * 设置公式 * * @param col: 需转化成对应字母的列号 * @param startIndex: 开始计算行号/列号 * @param endIndex: 结束计算行号/列号 * @return: java.lang.String * @author: */ public static String setFormula(Integer col, Integer startIndex, Integer endIndex) { String colString = CellReference.convertNumToColString(col); String formula = CommonsUtils.GS.concat("=SUM(" + colString + startIndex + ":" + colString + endIndex + ")"); return formula; } /** * 设置公式 * * @param startCol: 需转化成对应字母的列号 * @param endCol: 需转化成对应字母的列号 * @param startIndex: 开始计算行号/列号 * @param endIndex: 结束计算行号/列号 * @return: java.lang.String * @author: */ public static String setFormula(Integer startCol, Integer endCol, Integer startIndex, Integer endIndex) { String startColString = CellReference.convertNumToColString(startCol); String endColString = CellReference.convertNumToColString(endCol); String formula = CommonsUtils.GS.concat("=SUM(" + startColString + startIndex + ":" + endColString + endIndex + ")"); return formula; } /** * 设置公式 * * @param col: 需转化成对应字母的列号 * @param indexList: 续统计的行号 * @return: java.lang.String * @author: */ public static String setFormula(Integer col, List<Integer> indexList) { String colString = CellReference.convertNumToColString(col); StringBuffer sb = new StringBuffer(); if (indexList != null && indexList.size() > 0) { for (int n = 0; n < indexList.size(); n++) { sb.append(colString.concat(indexList.get(n).toString())); if (n < indexList.size() - 1) { sb.append("+"); } } } String formula = CommonsUtils.GS.concat("=SUM(" + sb.toString() + ")"); return formula; } /** * 设置空字符串 * * @param n: * @return: java.lang.String[] * @author: */ public static void setEmpty(List<Object> list, int n) { for (int i = 0; i < n; i++) { list.add(""); } } /** * 设置横岗 * * @param n: * @return: java.lang.String[] * @author: */ public static void setBigDecimal(List<Object> list, int n) { for (int i = 0; i < n; i++) { list.add(BigDecimal.ZERO); } } /** * List转String[] * * @param list: * @return: java.lang.String[] * @author: */ public static Object[] list2ObjArr(List<Object> list) { Object[] strArr = new Object[list.size()]; for (int i = 0; i < list.size(); i++) { strArr[i] = list.get(i); } return strArr; } /** * List转String[] * * @param list: * @return: java.lang.String[] * @author: */ public static String[] list2StrArr(List<Object> list) { String[] strArr = new String[list.size()]; for (int i = 0; i < list.size(); i++) { strArr[i] = String.valueOf(list.get(i)); } return strArr; } /** * List转String[] * * @param list: * @return: java.lang.String[] * @author: */ public static String[] list2StrArr2(List<String> list) { String[] strArr = new String[list.size()]; for (int i = 0; i < list.size(); i++) { strArr[i] = String.valueOf(list.get(i)); } return strArr; } }
会持续更新，先发个粗糙版的
