使用POI导出excel并合并单元格可Tree |
您所在的位置：网站首页 › 文件名导出excel › 使用POI导出excel并合并单元格可Tree |
使用POI导出excel并合并单元格可Tree
|
概要
使用POI导出Excel，需合并单元格 预期一、简单合并单元格
二、tree结构合并单元格
工具类： /** * FileName: ExcelMergeUtil * Author: Administrator * Date: 2023/6/1 14:49 * Description: 复杂excel导出工具类 * History: * * 作者姓名 修改时间 版本号 描述 */ import com.fasterxml.jackson.annotation.JsonFormat; import com.gwos.common.utils.ExceptionUtil; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.*; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.text.DecimalFormat; import java.util.*; import java.util.regex.Pattern; public class ExcelMergeUtil { /** * 创建excel文件 * * @param objData 数据 * @param fileName 文件名 * @param sheetName sheet名 * @param columns 表头 * @param mergeIndex 需要合并的列号集合 * @return */ public static int exportToExcelForXlsx(List objData, String fileName, String sheetName, List columns, List mergeIndex, boolean isTree, HttpServletRequest request, HttpServletResponse response) { int flag = 0; Collections.sort(mergeIndex);//将列号排序 // 创建工作薄 XSSFWorkbook wb = new XSSFWorkbook(); // sheet1 XSSFSheet sheet1 = wb.createSheet(sheetName); //设置样式 XSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); // 设置水平对齐方式为居中对齐 style.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中对齐 //标题头 sheet1.createFreezePane(0, 1);//冻结表头 XSSFRow sheet1row1 = sheet1.createRow((short) 0); sheet1row1.setHeight((short) 480); XSSFCell title = sheet1row1.createCell(0); title.setCellValue("设备***数据统计"); // 设置第一个单元格的内容 XSSFCellStyle titleStyle = wb.createCellStyle(); titleStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平对齐方式为居中对齐 titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中对齐 XSSFFont cellFont = wb.createFont(); cellFont.setFontHeightInPoints((short)18); cellFont.setBold(true); titleStyle.setFont(cellFont); title.setCellStyle(titleStyle); // 合并 A1 到 F1 这 6 个单元格 CellRangeAddress region = new CellRangeAddress(0, 0, 0, 6); sheet1.addMergedRegion(region); //表头 sheet1.createFreezePane(0, 1);//冻结表头 XSSFRow sheet1row2 = sheet1.createRow((short) 1); sheet1row1.setHeight((short) 480); //写入表头 if (columns != null && columns.size() > 0) { for (int i = 0; i < columns.size(); i++) { String column = columns.get(i); //列 XSSFCell cell2 = sheet1row2.createCell(i); cell2.setCellValue(column); cell2.setCellStyle(style); } } int dataSatrtIndex = 1;//数据开始行 boolean isMerge = false; if (mergeIndex != null && mergeIndex.size() != 0) { isMerge = true; } //写入数据 if (objData != null && objData.size() > 0) { Map poiModels = new HashMap(); //循环写入表中数据 int i = 0; for (; i < objData.size(); i++) { //数据行 XSSFRow row = sheet1.createRow((short) (i + dataSatrtIndex)); //行内循环，既单元格（列） List list = objData.get(i); DecimalFormat decimalFormat = new DecimalFormat("0.00"); int j = 0; for (Object o : list) { //数据列 String content = ""; if (o != null) { if (o.toString().contains(".") && isNumeric(o.toString())) { content = decimalFormat.format(Float.valueOf(o.toString())); } else if (o.toString().contains("-") && o.toString().contains(":")) { content = String.valueOf(o).split("\\.")[0]; } else { content = String.valueOf(o); } } if (isMerge && mergeIndex.contains(j)) { //如果该列需要合并 MergeModel poiModel = poiModels.get(j); if (poiModel == null) { poiModel = new MergeModel(); poiModel.setContent(content); poiModel.setRowIndex(i + dataSatrtIndex); poiModel.setCellIndex(j); poiModels.put(j, poiModel); } else { if (!poiModel.getContent().equals(content)) { //如果不同了，则将前面的数据合并写入 if (isTree) { //此列向后的所有列都进行一次写入合并操作，并清空。 //树结构中存在这种情况，a目录和b目录为同级目录，a目录下最后一个子目录和b目录下的第一个子目录名称相同，防止本来不应该合并的单元格被合并 addMergedRegionValue(sheet1, poiModels, mergeIndex, i + dataSatrtIndex, poiModel.getCellIndex()); } else { XSSFRow lastRow = sheet1.getRow(poiModel.getRowIndex()); XSSFCell lastCell = lastRow.createCell(poiModel.getCellIndex());//创建列 lastCell.setCellValue(poiModel.getContent()); lastCell.setCellStyle(style); //合并单元格 if (poiModel.getRowIndex() != i + dataSatrtIndex - 1) { sheet1.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), i + dataSatrtIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex())); } } //将新数据存入 poiModel.setContent(content); poiModel.setRowIndex(i + dataSatrtIndex); poiModel.setCellIndex(j); poiModels.put(j, poiModel); } } row.createCell(j);//创建单元格 } else {//该列不需要合并 //数据列 XSSFCell cell = row.createCell(j); cell.setCellValue(content); cell.setCellStyle(style); } j++; } } //将最后一份存入 if (poiModels != null && poiModels.size() != 0) { for (Integer key : poiModels.keySet()) { MergeModel poiModel = poiModels.get(key); XSSFRow lastRow = sheet1.getRow(poiModel.getRowIndex()); XSSFCell lastCell = lastRow.getCell(poiModel.getCellIndex()); lastCell.setCellValue(poiModel.getContent()); lastCell.setCellStyle(style); //合并单元格 if (poiModel.getRowIndex() != i + dataSatrtIndex - 1) { sheet1.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), i + dataSatrtIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex())); } } } } else { flag = -1; } //设置固定列宽，poi的列宽设置有点操蛋，大概规律网上有不少版本自行百度 //这里大概是143像素 for (int i = 0; i < columns.size(); i++) { sheet1.setColumnWidth(i, 4550); } OutputStream os = null; try { // 创建一个普通输出流 os = response.getOutputStream(); fileName = "file.xls"; // 请求浏览器打开下载窗口 response.reset(); response.setCharacterEncoding("UTF-8"); // Content-disposition 告诉浏览器以下载的形式打开 // String header = request.getHeader("User-Agent").toUpperCase(); // if (header.contains("MSIE") || header.contains("TRIDENT") || header.contains("EDGE")) { // fileName = URLEncoder.encode(fileName, "utf-8"); // fileName = fileName.replace("+", "%20"); // IE下载文件名空格变+号问题 // } else { // fileName = new String(fileName.getBytes(), "ISO8859-1"); // } fileName = new String(fileName.getBytes(), "ISO8859-1"); response.setHeader("Content-Disposition", "attachment; filename=" + fileName);// 要保存的文件名 response.setContentType("application/octet-stream"); // 直接用数组缓冲输出流输出 wb.write(os); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { try { wb.close(); os.close(); } catch (IOException e) { e.printStackTrace(); } } /* 本地下载方式 FileOutputStream out = null; try { out = new FileOutputStream("E:\\" + fileName + ".xlsx"); wb.write(out); } catch (Exception ex) { try { out.flush(); out.close(); }catch (IOException e){ flag = 0; e.printStackTrace(); } }*/ return flag; } /** * 判断是不是数字 * * @param str * @return */ private static boolean isNumeric(String str) { if (str == null || str.length() == 0) { return false; } Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$"); return pattern.matcher(str).matches(); } private static void addMergedRegionValue(XSSFSheet sheet, Map poiModels, List mergeIndex, int nowRowIndex, int nowCellIndex) { if (poiModels != null && poiModels.size() != 0 && mergeIndex != null && mergeIndex.size() != 0) { for (Integer index : mergeIndex) { if (index >= nowCellIndex) { MergeModel poiModel = poiModels.remove(index);//删除并获取value if (poiModel != null) { XSSFRow lastRow = sheet.getRow(poiModel.getRowIndex()); XSSFCell lastCell = lastRow.createCell(poiModel.getCellIndex());//创建列 lastCell.setCellValue(poiModel.getContent()); //合并单元格 if (poiModel.getRowIndex() != nowRowIndex - 1) { sheet.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), nowRowIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex())); } } } } } } } 调用层： @GetMapping("/export") public void exportDeviceMeasureValues( HttpServletRequest request, HttpServletResponse response, @ApiParam(value = "项目编号") @RequestParam String projectNo, @ApiParam(value = "项目名称") @RequestParam String projectName, @ApiParam(value = "日期,格式:yyyy-MM-dd") @RequestParam String inspectDate, @ApiParam(value = "设备类别") @RequestParam String deviceCategory) { try { TowerMonitorVO monitorValues = monitorService.getDeviceMeasureValues(projectNo, formatter.parse(inspectDate),deviceCategory); monitorService.exportDeviceMeasureValues(projectName,monitorValues,deviceCategory,request,response); } catch (Exception e) { LOG.error("", e); } }tree结构导出 public static void main(String[] args) { List columns = new ArrayList();//标头 columns.add("目录"); columns.add("目录"); columns.add("目录"); columns.add("内容"); String fileName = "文件名字";//文件名字 String sheetName = "sheet名字";//sheet名字 //内容数据 List exportData = new ArrayList(); //行内的数据 List rowData = new ArrayList(); rowData.add("一级目录1"); rowData.add("二级目录1"); rowData.add("三级目录1"); rowData.add("内容1"); exportData.add(rowData); List rowData2 = new ArrayList(); rowData2.add("一级目录1"); rowData2.add("二级目录1"); rowData2.add("三级目录1"); rowData2.add("内容2"); exportData.add(rowData2); List rowData3 = new ArrayList(); rowData3.add("一级目录1"); rowData3.add("二级目录1"); rowData3.add("三级目录2"); rowData3.add("内容3"); exportData.add(rowData3); List rowData4 = new ArrayList(); rowData4.add("一级目录1"); rowData4.add("二级目录1"); rowData4.add("三级目录2"); rowData4.add("内容4"); exportData.add(rowData4); List rowData5 = new ArrayList(); rowData5.add("一级目录1"); rowData5.add("二级目录2"); rowData5.add("三级目录3"); rowData5.add("内容5"); exportData.add(rowData5); List rowData6 = new ArrayList(); rowData6.add("一级目录1"); rowData6.add("二级目录2"); rowData6.add("三级目录3"); rowData6.add("内容6"); exportData.add(rowData6); //需要合并的列号 List mergeIndex = new ArrayList(); mergeIndex.add(0); mergeIndex.add(1); mergeIndex.add(2); int flag = ExcelMergeUtil.exportToExcelForXlsx(exportData, fileName, sheetName, columns, mergeIndex, true, null, null); System.out.println(flag); }
小结
引用原文： https://www.cnblogs.com/wdk2020/p/13204162.html |
【本文地址】