【每日一点】1. Java如何实现导出Excel单表头或多表头
一、背景
在后台项目中，经常会遇到将呈现的内容导出到Excel的需求，通过都是导出单个表头的Excel文件，如果存在级联关系的情况下，也就需要导出多表头的场景。今天这篇文章就是分享导出Excel单表头或多表头的实现，目前实现方案仅支持2行表头场景。如有更复杂的3行表头、4行表头复杂需求可以自行实现。
二、实现思路
1. 借助POI包实现表头的写入。每个表头其实就是一行，如果是多个表头，无非就是将写多行表头，然后将需要合并的表头进行合并，借助POI的函数为addMergedRegion。
2. 将导出数据进行转化为一个集合，循环写入每行数据。
三、实现代码
3.1 pom引入
<properties> <java.version>1.8</java.version> <poi.version>3.17</poi.version> <mybatis.version>1.3.2</mybatis.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-validation</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>${mybatis.version}</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>${poi.version}</version> </dependency> </dependencies>
3.2 Java代码实现
@Getter @Setter public class ExcelHelper<T> { /** * 表格标题 */ private String title; /** * 单元格宽度 */ private int colWidth = 20; /** * 行高度 */ private int rowHeight = 20; private HSSFWorkbook workbook; /** * 表头样式 */ private HSSFCellStyle headStyle; /** * 主体样式 */ private HSSFCellStyle bodyStyle; /** * 日期格式化,默认yyyy-MM-dd HH:mm:ss */ private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); /** * Constructor * @param title */ public ExcelHelper(String title){ this.title = title; workbook = new HSSFWorkbook(); init(); } /** * Constructor * @param title * @param colWidth * @param rowHeight */ public ExcelHelper(String title, int colWidth, int rowHeight){ this.colWidth = colWidth; this.rowHeight = rowHeight; this.title = title; workbook = new HSSFWorkbook(); init(); } /** * Constructor * @param title * @param colWidth * @param rowHeight * @param dateFormat */ public ExcelHelper(String title, int colWidth, int rowHeight, String dateFormat) { this.title = title; this.colWidth = colWidth; this.rowHeight = rowHeight; workbook = new HSSFWorkbook(); sdf = new SimpleDateFormat(dateFormat); init(); } /** * 导出Excel,适用于web导出excel * * @param sheet * @param data */ private void writeSheet(HSSFSheet sheet, List<T> data,List<Column> headerList) { try { sheet.setDefaultColumnWidth(colWidth); sheet.setDefaultRowHeightInPoints(rowHeight); createHead(headerList, sheet); writeSheetContent(headerList, data, sheet); } catch (Exception e) { throw new RuntimeException(e); } } /** * 导出表格 * @param listColumn * @param datas * @return * @throws Exception */ public InputStream exportExcel(List<Column> listColumn,List<T> datas) throws Exception { splitDataToSheets(datas,listColumn); return save(workbook); } /** * 导出表格 支持2级表头或单表头的Excel导出 * @param headers * @param datas * @param filePath * @throws FileNotFoundException * @throws IOException * void */ public void exportExcel(List<Column> headers,List<T> datas,String filePath) throws IOException { splitDataToSheets(datas, headers); save(workbook, filePath); } /** * 把数据写入到单元格 * @param listColumn * @param datas * @param sheet * @throws Exception * void */ private void writeSheetContent(List<Column> listColumn,List<T> datas,HSSFSheet sheet) throws Exception { HSSFRow row; List<Column> listCol = getColumnList(listColumn); for (int i = 0, index = 2; i < datas.size(); i++, index++) { // 创建行 row = sheet.createRow(index); for (int j = 0; j < listCol.size(); j++) { Column c = listCol.get(j); createCol(row, c, datas.get(i), j); } } } /** * 创建表头 * @param listColumn 表头数组 * @return 返回表头总行数 */ public void createHead(List<Column> listColumn, HSSFSheet sheetCo){ HSSFRow row = sheetCo.createRow(0); HSSFRow row2 = sheetCo.createRow(1); for(short i = 0, n = 0; i < listColumn.size(); i++){ HSSFCell cell1 = row.createCell(n); cell1.setCellStyle(headStyle); HSSFRichTextString text; List<Column> columns = listColumn.get(i).getListColumn(); //双标题 if(CollectionUtils.isEmpty(columns)){ // 单标题 HSSFCell cell2 = row2.createCell(n); cell2.setCellStyle(headStyle); text = new HSSFRichTextString(listColumn.get(i).getContent()); sheetCo.addMergedRegion(new CellRangeAddress(0, n, 1, n)); n++; cell1.setCellValue(text); continue; } text = new HSSFRichTextString(listColumn.get(i).getContent()); cell1.setCellValue(text); // 创建第一行大标题 sheetCo.addMergedRegion(new CellRangeAddress(0, 0, n, (short) (n + columns.size() -1))); // 给标题赋值 for(int j = 0; j < columns.size(); j++){ HSSFCell cell2 = row2.createCell(n++); cell2.setCellStyle(headStyle); cell2.setCellValue(new HSSFRichTextString(columns.get(j).getContent())); } } } /** * 创建行 * @param row * @param column * @param v * @param rowIndex * @return * @throws Exception */ public int createRowVal(HSSFRow row, Column column,T v,int rowIndex) throws Exception{ // 遍历标题 if(column.getListColumn() != null && column.getListColumn().size() > 0){ for(int i = 0; i < column.getListColumn().size(); i++){ createRowVal(row,column.getListColumn().get(i),v,rowIndex); } }else{ createCol(row,column,v,rowIndex++); } return rowIndex; } /** * 创建单元格 * @param row * @param column * @param v * @param columnIndex * @throws Exception */ public void createCol(HSSFRow row,Column column,T v,int columnIndex) throws Exception{ // 创建单元格 HSSFCell cell = row.createCell(columnIndex); // 设置单元格样式 cell.setCellStyle(bodyStyle); Class cls = v.getClass(); Field field = cls.getDeclaredField(column.getFieldName()); // 设置些属性是可以访问的 field.setAccessible(true); if(field.get(v) != null){ Object value = field.get(v); if(value instanceof Date){ value = parseDate((Date)value); } HSSFRichTextString richString = new HSSFRichTextString(value.toString()); cell.setCellValue(richString); } } /** * init */ private void init(){ // 生成表头样式 headStyle = workbook.createCellStyle(); headStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.AQUA.getIndex()); headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headStyle.setBorderBottom(BorderStyle.THIN); headStyle.setBorderLeft(BorderStyle.THIN); headStyle.setBorderRight(BorderStyle.THIN); headStyle.setBorderTop(BorderStyle.THIN); headStyle.setAlignment(HorizontalAlignment.CENTER); headStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 生成一个字体 HSSFFont headFont = workbook.createFont(); headFont.setColor(HSSFColor.HSSFColorPredefined.VIOLET.getIndex()); headFont.setFontHeightInPoints((short) 12); headFont.setBold(true); // 把字体应用到当前的样式 headStyle.setFont(headFont); // 生成并设置另一个样式 bodyStyle = workbook.createCellStyle(); bodyStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex()); bodyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); bodyStyle.setBorderBottom(BorderStyle.THIN); bodyStyle.setBorderLeft(BorderStyle.THIN); bodyStyle.setBorderRight(BorderStyle.THIN); bodyStyle.setBorderTop(BorderStyle.THIN); bodyStyle.setAlignment(HorizontalAlignment.CENTER); bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 生成另一个字体 HSSFFont bodyFont = workbook.createFont(); bodyFont.setBold(false); // 把字体应用到当前的样式 bodyStyle.setFont(bodyFont); } /** * 时间转换 * @param date * @return * String */ private String parseDate(Date date){ String dateStr = ""; try{ dateStr = sdf.format(date); } catch (Exception e){ e.printStackTrace(); } return dateStr; } /** * 拆分sheet，因为每个sheet不能超过6526，否则会报异常 * @param data * @param listColumn * void */ private void splitDataToSheets(List<T> data,List<Column> listColumn) { int dataCount = data.size(); int maxColumn = 65535; int pieces = dataCount / maxColumn; for (int i = 1; i <= pieces; i++) { HSSFSheet sheet = workbook.createSheet(this.title + i); List<T> subList = data.subList((i - 1) * maxColumn, i * maxColumn); writeSheet(sheet, subList, listColumn); } HSSFSheet sheet = workbook.createSheet(this.title + (pieces + 1)); writeSheet(sheet, data.subList(pieces * maxColumn, dataCount), listColumn); } /** * 把column的columnList整理成一个list<column> * @param listColumn * @return * List<Column> */ private List<Column> getColumnList(List<Column> listColumn){ List<Column> listCol = new ArrayList<>(); for(int i = 0; i < listColumn.size(); i++){ List<Column> list = listColumn.get(i).getListColumn(); if(list.size() > 0){ for(Column c : list){ if(c.getFieldName() != null){ listCol.add(c); } } }else{ if(listColumn.get(i).getFieldName() != null){ listCol.add(listColumn.get(i)); } } } return listCol; } /** * 保存Excel到InputStream，此方法适合web导出excel * * @param workbook * @return */ private InputStream save(HSSFWorkbook workbook) { ByteArrayOutputStream bos = new ByteArrayOutputStream(); try { workbook.write(bos); InputStream bis = new ByteArrayInputStream(bos.toByteArray()); return bis; } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } } /** * 保存文件 * @param workbook * @param filePath * @throws IOException */ private void save(HSSFWorkbook workbook,String filePath) throws IOException { workbook.write(new FileOutputStream(filePath)); }
四、测试类
public class ExportTest { @Data @AllArgsConstructor public class ValueObj { private String value1; private String value2; } public static void main(String[] args) throws Exception { //用于存放第一行单元格 List<Column> listColumn = new ArrayList<>(); // 用于存放第一列第二行的单元格 List<Column> list2 = new ArrayList<>(); // 创建一列，value1 表示这一列需要导出字段的值 list2.add(new Column("标题1","value1")); list2.add(new Column("标题2","value1")); list2.add(new Column("标题3","value1")); // 用于存放第二列第二行的单元格 List<Column> list3 = new ArrayList<>(); list3.add(new Column("标题6","value2")); list3.add(new Column("标题7","value2")); //创建第一行大标题,大标题的fieldName 为 null Column c1 = new Column("标题1",null); c1.setListColumn(list2); Column c2 = new Column("标题2",null); c2.setListColumn(list3); listColumn.add(c1); listColumn.add(c2); //需要导出的数据 List<ValueObj> valueList = new ArrayList<>(); valueList.add(new ValueObj("1","11")); valueList.add(new ValueObj("2","22")); valueList.add(new ValueObj("3","33")); valueList.add(new ValueObj("4","44")); ExcelHelper<ValueObj> ta = new ExcelHelper<ValueObj>("表格",15,20); ta.exportExcel(listColumn, valueList,"D:\\outExcel.xls"); } }
五、实现效果
正文到此结束
