失眠网 > POI导出EXCEL合并单元格对象嵌套List数据

POI导出EXCEL合并单元格对象嵌套List数据

时间：2019-05-21 07:55:07

导出EXCEL

在实际的开发过程当中，我们会遇到一些比较复杂的导出需求，例如需要导出的实体类中需要嵌套集合对象等，正好最近碰到了所以分享出来，希望对大家有帮助

一、POI是什么

简单的说就是Apache软件基金会提供的一个库，这个库里有API，API赋予了Java程序更强大的读和写的能力

二、使用步骤

这里是结果

1、POM.XML

<dependencies><dependency><groupId>com.monitorjbl</groupId><artifactId>xlsx-streamer</artifactId><version>2.1.0</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.16.10</version></dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.6.0</version></dependency></dependencies>

2、常量

public static final Integer ZERO = 0;public static final Integer ONE = 1;public static final Integer UNQUALIFIED_INFO_INDEX = 24;public static final String[] TITLE_LIST = new String[]{"汉皇重色思倾国", "御宇多年求不得", "杨家有女初长成", "养在深闺人未识", "天生丽质难自弃", "一朝选在君王侧","回眸一笑百媚生", "六宫粉黛无颜色", "春寒赐浴华清池", "温泉水滑洗凝脂", "侍儿扶起娇无力", "始是新承恩泽时","云鬓花颜金步摇", "芙蓉帐暖度春宵", "春宵苦短日高起", "从此君王不早朝", "承欢侍宴无闲暇", "春从春游夜专夜","后宫佳丽三千人", "三千宠爱在一身", "金屋妆成娇侍夜", "玉楼宴罢醉和春", "姊妹弟兄皆列土", "可怜光彩生门户","花冠不整下堂来", "风吹仙袂飘飖举", "犹似霓裳羽衣舞", "在天愿作比翼鸟", "在地愿为连理枝", "天长地久有时尽","此恨绵绵无绝期", "九重城阙烟尘生", "千乘万骑西南行", "西出都门百余里"};

该处为标题

3、实体类

package cn.itsaxon.vo;import lombok.*;import java.io.Serializable;@Data@NoArgsConstructor@AllArgsConstructor@Builder@Getter@Setterpublic class ExportVO implements Serializable {private String orderId = "";private String customerProvince = "";private String customerArea = "";private String dealerName = "";private String customerName = "";private String customerTel = "";private String customerIdCard = "";private String inverterList = "";private String inverterNum = "";private String cooperationName = "";private String customerAddress = "";private String roofType = "";private String singlePower = "";private String actualNum = "";private String actualPower = "";private String onGridModel = "";private String installDate = "";private String taskType = "";private String startInspectionDate = "";private String endInspectionDate = "";private String taskOperatorName = "";private String auditor = "";private String taskStatus = "";private String acceptResult = "";private String overTimeStatus = "";private String proposalSubmitDate = "";private String resultSubmitDate = "";private String onGridPeriod= "";private String riskAssessment = "";private String applyDescribe = "";private String handlingOpinions = "";private String completedFlag = "";private String unqualifiedInfo = "";}

4、表格工具类

package cn.itsaxon.utils;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.xssf.usermodel.*;/*** Excel工具类** @author itsaxon* @date -03-17 16:39*/public class ExcelUtil {/*** 将Sheet列号变为列名** @param index 列号, 从0开始* @return 0->A; 1->B...26->AA*/public static String index2ColName(int index) {if (index < 0) {return null;}// A的Unicode码int num = 65;String colName = "";do {if (colName.length() > 0) {index--;}int remainder = index % 26;colName = ((char) (remainder + num)) + colName;index = (int) ((index - remainder) / 26);} while (index > 0);return colName;}/*** 根据表元的列名转换为列号** @param colName 列名, 从A开始* @return A1->0; B1->1...AA1->26*/public static int colName2Index(String colName) {int index = -1;// A的Unicode码int num = 65;int length = colName.length();for (int i = 0; i < length; i++) {char c = colName.charAt(i);// 确定指定的char值是否为数字if (Character.isDigit(c)) break;index = (index + 1) * 26 + (int) c - num;}return index;}public static XSSFFont getFont(XSSFWorkbook workbook, String fontName, int fontSize) {XSSFFont font = workbook.createFont();font.setFontName(fontName);font.setFontHeightInPoints((short) fontSize);return font;}public static XSSFFont getFont(XSSFWorkbook workbook, String fontName, int fontSize, int color) {XSSFFont font = getFont(workbook, fontName, fontSize);font.setColor((short) color);return font;}public static XSSFFont getFont(XSSFWorkbook workbook, String fontName, int fontSize, boolean bold) {XSSFFont font = getFont(workbook, fontName, fontSize);font.setBold(bold);return font;}public static XSSFFont getFont(XSSFWorkbook workbook, String fontName, int fontSize, boolean bold, int color) {XSSFFont font = getFont(workbook, fontName, fontSize);font.setBold(bold);font.setColor((short) color);return font;}public static XSSFFont getFont(XSSFWorkbook workbook, String fontName, int fontSize, boolean bold, byte[] color) {XSSFFont font = getFont(workbook, fontName, fontSize);font.setBold(bold);font.setColor(new XSSFColor(color, new DefaultIndexedColorMap()));return font;}public static XSSFCellStyle getStyle(XSSFWorkbook workbook, XSSFFont font, byte[] color) {XSSFCellStyle style = workbook.createCellStyle();style.setFont(font);if (null != color) {style.setFillForegroundColor(new XSSFColor(color, new DefaultIndexedColorMap()));style.setFillPattern(FillPatternType.SOLID_FOREGROUND);}return style;}public static XSSFCellStyle getStyle(XSSFWorkbook workbook, XSSFFont font, byte[] color, boolean isCenter) {XSSFCellStyle style = getStyle(workbook, font, color);if (isCenter) {style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);}return style;}public static XSSFCellStyle getStyle(XSSFWorkbook workbook, XSSFFont font, byte[] color, boolean isCenter, BorderStyle borderStyle) {XSSFCellStyle style = getStyle(workbook, font, color);if (isCenter) {style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);}if (null != borderStyle) {style.setBorderBottom(borderStyle);style.setBorderLeft(borderStyle);style.setBorderRight(borderStyle);style.setBorderTop(borderStyle);}return style;}public static XSSFCellStyle getStyle(XSSFWorkbook workbook, XSSFFont font, byte[] color, boolean isCenter, BorderStyle topStyle, BorderStyle bottomStyle) {XSSFCellStyle style = getStyle(workbook, font, color);if (isCenter) {style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);}if (null != topStyle) {style.setBorderTop(topStyle);}if (null != bottomStyle) {style.setBorderBottom(bottomStyle);}return style;}public static XSSFCellStyle getStyle(XSSFWorkbook workbook, XSSFFont font, byte[] color, boolean isCenter, BorderStyle topStyle, BorderStyle bottomStyle, BorderStyle lrStyle) {XSSFCellStyle style = getStyle(workbook, font, color);if (isCenter) {style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);}if (null != topStyle) {style.setBorderTop(topStyle);}if (null != bottomStyle) {style.setBorderBottom(bottomStyle);}if (null != lrStyle) {style.setBorderLeft(lrStyle);style.setBorderRight(lrStyle);}return style;}public static XSSFCellStyle getStyle(XSSFWorkbook workbook, XSSFFont font, byte[] color, boolean isCenter, BorderStyle topStyle, BorderStyle bottomStyle, BorderStyle leftStyle, BorderStyle rightStyle) {XSSFCellStyle style = getStyle(workbook, font, color);if (isCenter) {style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);}if (null != topStyle) {style.setBorderTop(topStyle);}if (null != bottomStyle) {style.setBorderBottom(bottomStyle);}if (null != leftStyle) {style.setBorderLeft(leftStyle);}if (null != rightStyle) {style.setBorderRight(rightStyle);}return style;}}

5、导出业务实现

代码如下：

package cn.itsaxon.service.serviceImpl;import cn.hutool.core.collection.CollectionUtil;import cn.hutool.core.util.StrUtil;import cn.itsaxon.contract.Constants;import cn.itsaxon.vo.ExportVO;import cn.itsaxon.utils.ExcelUtil;import mons.collections4.CollectionUtils;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.*;import java.util.*;public class ExportService {/*** 导出** @param exportVOList* @throws IOException*/public static void export(List<ExportVO> exportVOList) throws IOException {XSSFWorkbook workbook;// 创建工作簿workbook = new XSSFWorkbook();// 创建sheetSheet sheet = workbook.createSheet("Sheet0");sheet.autoSizeColumn(1);// 创建标题行Row titleRow = sheet.createRow(0);// 定义标题样式CellStyle titleStyle = ExcelUtil.getStyle(workbook, ExcelUtil.getFont(workbook, "宋体", 10, true), null, true);// 写入样式for (int i = 0; i < Constants.TITLE_LIST.length; i++) {Cell title = titleRow.createCell(i);title.setCellStyle(titleStyle);title.setCellValue(Constants.TITLE_LIST[i]);}if (CollectionUtils.isEmpty(exportVOList)) {return;}// 定义内容样式CellStyle style = ExcelUtil.getStyle(workbook, ExcelUtil.getFont(workbook, "宋体", 8, false, new byte[]{(byte) 0, (byte) 176, (byte) 80}), null, true);// 一条工单的开始行数int startCell = Constants.ONE;// 一条工单的结束行数int endCell = Constants.ONE;// 不合格项的索引int unqualifiedInfoIndex = Constants.UNQUALIFIED_INFO_INDEX;// 写入内容int m, x, y, z;for (m = 0; m < exportVOList.size(); m++) {Row row = sheet.createRow(startCell);// 不合格项之前的数据createCellInfoBefore(row, style, exportVOList, m);// 不合格项的数据Cell unqualifiedCell = row.createCell(unqualifiedInfoIndex);unqualifiedCell.setCellStyle(style);// 获取不合格项数组String[] temp = StrUtil.split(exportVOList.get(m).getUnqualifiedInfo(), ",");// 转集合List<String> unqualifiedInfoList = Arrays.asList(temp);// 不合格项填充合格项不处理（默认为空）if (CollectionUtil.isNotEmpty(unqualifiedInfoList)) {unqualifiedCell.setCellValue(unqualifiedInfoList.get(Constants.ZERO));}// 不合格项之后的数据createCellInfoAfter(row, style, exportVOList, m);// 空或一个不合格项if (CollectionUtil.isEmpty(unqualifiedInfoList) || unqualifiedInfoList.size() <= 1) {// 重置下一行的行数startCell++;// 记录结束行数if (m > 0) {endCell++;}continue;}// 多个不合格项for (x = 1; x < unqualifiedInfoList.size(); x++) {// 每进来一次说明多写一行endCell++;row = sheet.createRow(x + startCell);// 不合格项之前的数据createCellInfoBefore(row, style, exportVOList, m);// 不合格项数据Cell unqualifiedCellMore = row.createCell(unqualifiedInfoIndex);unqualifiedCellMore.setCellStyle(style);unqualifiedCellMore.setCellValue(unqualifiedInfoList.get(x));// 不合格项之后的数据createCellInfoAfter(row, style, exportVOList, m);}// 记录结束行数在外层先写一行所以自增if (m > 0) {endCell++;}// 合并单元格：起始行，结束行，起始列，结束列for (y = 0; y < unqualifiedInfoIndex; y++) {mergedRegion(startCell, endCell, y, y, sheet);}for (z = unqualifiedInfoIndex + 1; z < Constants.TITLE_LIST.length; z++) {mergedRegion(startCell, endCell, z, z, sheet);}// 记录下一行开始行数startCell = endCell + Constants.ONE;}File file = new File("F:\\呆猫.xlsx");FileOutputStream fout = new FileOutputStream(file);workbook.write(fout);fout.close();}/*** 合并单元格** @param firstRow* @param lastRow* @param firstCol* @param lastCol*/public static void mergedRegion(int firstRow, int lastRow, int firstCol, int lastCol, Sheet sheet) {CellRangeAddress cellTemp = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);sheet.addMergedRegion(cellTemp);}/*** 写入多数据项之前的记录** @param row* @param style* @param exportVOList* @param m*/public static void createCellInfoBefore(Row row, CellStyle style, List<ExportVO> exportVOList, int m) {Cell customerProvinceCell = row.createCell(0);customerProvinceCell.setCellStyle(style);customerProvinceCell.setCellValue(exportVOList.get(m).getCustomerProvince());Cell customerAreaCell = row.createCell(1);customerAreaCell.setCellStyle(style);customerAreaCell.setCellValue(exportVOList.get(m).getCustomerArea());Cell dealerNameCell = row.createCell(2);dealerNameCell.setCellStyle(style);dealerNameCell.setCellValue(exportVOList.get(m).getDealerName());Cell customerNameCell = row.createCell(3);customerNameCell.setCellStyle(style);customerNameCell.setCellValue(exportVOList.get(m).getCustomerName());Cell customerTelCell = row.createCell(4);customerTelCell.setCellStyle(style);customerTelCell.setCellValue(exportVOList.get(m).getCustomerTel());Cell customerIdCardCell = row.createCell(5);customerIdCardCell.setCellStyle(style);customerIdCardCell.setCellValue(exportVOList.get(m).getCustomerIdCard());Cell inverterListCell = row.createCell(6);inverterListCell.setCellStyle(style);inverterListCell.setCellValue(exportVOList.get(m).getInverterList());Cell inverterNumCell = row.createCell(7);inverterNumCell.setCellStyle(style);inverterNumCell.setCellValue(exportVOList.get(m).getInverterNum());Cell cooperationNameCell = row.createCell(8);cooperationNameCell.setCellStyle(style);cooperationNameCell.setCellValue(exportVOList.get(m).getCooperationName());Cell customerAddressCell = row.createCell(9);customerAddressCell.setCellStyle(style);customerAddressCell.setCellValue(exportVOList.get(m).getCustomerAddress());Cell roofTypeCell = row.createCell(10);roofTypeCell.setCellStyle(style);roofTypeCell.setCellValue(exportVOList.get(m).getRoofType());Cell singlePowerCell = row.createCell(11);singlePowerCell.setCellStyle(style);singlePowerCell.setCellValue(exportVOList.get(m).getSinglePower());Cell actualNumCell = row.createCell(12);actualNumCell.setCellStyle(style);actualNumCell.setCellValue(exportVOList.get(m).getActualNum());Cell actualPowerCell = row.createCell(13);actualPowerCell.setCellStyle(style);actualPowerCell.setCellValue(exportVOList.get(m).getActualPower());Cell onGridModelCell = row.createCell(14);onGridModelCell.setCellStyle(style);onGridModelCell.setCellValue(1);Cell installDateCell = row.createCell(15);installDateCell.setCellStyle(style);installDateCell.setCellValue(exportVOList.get(m).getInstallDate());Cell startInspectionDateCell = row.createCell(16);startInspectionDateCell.setCellStyle(style);startInspectionDateCell.setCellValue(exportVOList.get(m).getStartInspectionDate());Cell endInspectionDateCell = row.createCell(17);endInspectionDateCell.setCellStyle(style);endInspectionDateCell.setCellValue(exportVOList.get(m).getEndInspectionDate());Cell taskOperatorCell = row.createCell(18);taskOperatorCell.setCellStyle(style);taskOperatorCell.setCellValue(exportVOList.get(m).getTaskOperatorName());Cell startInspectionDateCell2 = row.createCell(19);startInspectionDateCell2.setCellStyle(style);startInspectionDateCell2.setCellValue(exportVOList.get(m).getStartInspectionDate());Cell endInspectionDateCell3 = row.createCell(20);endInspectionDateCell3.setCellStyle(style);endInspectionDateCell3.setCellValue(exportVOList.get(m).getEndInspectionDate());Cell taskOperatorCell2 = row.createCell(21);taskOperatorCell2.setCellStyle(style);taskOperatorCell2.setCellValue(exportVOList.get(m).getTaskOperatorName());Cell managerCell = row.createCell(22);managerCell.setCellStyle(style);managerCell.setCellValue(exportVOList.get(m).getAuditor());Cell taskStatusCell = row.createCell(23);taskStatusCell.setCellStyle(style);taskStatusCell.setCellValue(1);}/*** 写入多数据项之后的记录** @param row* @param style* @param exportVOList* @param m*/public static void createCellInfoAfter(Row row, CellStyle style, List<ExportVO> exportVOList, int m) {Cell acceptResultCell = row.createCell(25);acceptResultCell.setCellStyle(style);acceptResultCell.setCellValue(exportVOList.get(m).getAcceptResult());Cell overTimeStatusCell = row.createCell(26);overTimeStatusCell.setCellStyle(style);overTimeStatusCell.setCellValue(1);Cell proposalSubmitDateCell = row.createCell(27);proposalSubmitDateCell.setCellStyle(style);proposalSubmitDateCell.setCellValue(exportVOList.get(m).getProposalSubmitDate());Cell resultSubmitDateCell = row.createCell(28);resultSubmitDateCell.setCellStyle(style);resultSubmitDateCell.setCellValue(exportVOList.get(m).getResultSubmitDate());Cell onGridPeriodCell = row.createCell(29);onGridPeriodCell.setCellStyle(style);onGridPeriodCell.setCellValue(exportVOList.get(m).getOnGridPeriod());Cell riskAssessmentCell = row.createCell(30);riskAssessmentCell.setCellStyle(style);riskAssessmentCell.setCellValue(1);Cell applyDescribeCell = row.createCell(31);applyDescribeCell.setCellStyle(style);applyDescribeCell.setCellValue(exportVOList.get(m).getApplyDescribe());Cell handlingOpinions = row.createCell(32);handlingOpinions.setCellStyle(style);handlingOpinions.setCellValue(exportVOList.get(m).getHandlingOpinions());Cell completedFlag = row.createCell(33);completedFlag.setCellStyle(style);completedFlag.setCellValue(exportVOList.get(m).getCompletedFlag().equals(1));}public static void main(String[] args) throws IOException {List<ExportVO> list = new ArrayList<ExportVO>();ExportVO eo = ExportVO.builder().orderId("itsaxon").customerProvince("itsaxon").customerArea("itsaxon").dealerName("itsaxon").customerName("itsaxon").customerTel("itsaxon").customerIdCard("itsaxon").inverterList("itsaxon").inverterNum("itsaxon").cooperationName("itsaxon").customerAddress("itsaxon").roofType("itsaxon").singlePower("itsaxon").actualNum("itsaxon").actualPower("itsaxon").onGridModel("itsaxon").installDate("itsaxon").taskType("itsaxon").startInspectionDate("itsaxon").endInspectionDate("itsaxon").taskOperatorName("itsaxon").auditor("itsaxon").taskStatus("itsaxon").acceptResult("itsaxon").overTimeStatus("itsaxon").proposalSubmitDate("itsaxon").resultSubmitDate("itsaxon").onGridPeriod("itsaxon").riskAssessment("itsaxon").applyDescribe("itsaxon").handlingOpinions("itsaxon").completedFlag("itsaxon").unqualifiedInfo("1,2,3").build();//造数据省略export(list);}}