poi导出excel 并实现合并单元格

原图

合并之后图片

工具类ExportExcel

package com.daxiao.test.util;

/**

Created by Bob on /5/12.

*/

import java.io.FileOutputStream;

import java.io.IOException;

import java.text.DecimalFormat;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.regex.Pattern;

import com.daxiao.test.poJo.MergeModel;

import org.apache.poi.ss.usermodel.HorizontalAlignment;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExportExcel {

/**

* 创建excel文件

* @param objData 数据

* @param fileName 文件名

* @param sheetName sheet名

* @param columns 表头

* @param mergeIndex 需要合并的列号集合

* @return

*/

public static int exportToExcelForXlsx(List<List> objData,String fileName, String sheetName, List columns,List mergeIndex) {

int flag = 0;

// 创建工作薄XSSFWorkbook wb = new XSSFWorkbook();// sheet1XSSFSheet sheet1 = wb.createSheet(sheetName);//设置样式XSSFCellStyle style = wb.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);//水平对齐//表头sheet1.createFreezePane(0, 1);//冻结表头XSSFRow sheet1row1 = sheet1.createRow((short) 0);sheet1row1.setHeight((short) 480);//写入表头if (columns != null && columns.size() > 0) {for(int i=0;i<columns.size();i++) {String column = columns.get(i);//列XSSFCell cell = sheet1row1.createCell(i);cell.setCellValue(column);}}int dataSatrtIndex = 1;//数据开始行boolean isMerge = false;if(mergeIndex != null && mergeIndex.size() != 0) {isMerge = true;}//写入数据if (objData != null && objData.size() > 0) {Map<Integer,MergeModel> poiModels = new HashMap<Integer, MergeModel>();//循环写入表中数据int i = 0;for ( ; i < objData.size(); i++) {//数据行XSSFRow row = sheet1.createRow((short) (i+dataSatrtIndex));//行内循环，既单元格（列）List<Object> 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)) {//如果不同了，则将前面的数据合并写入XSSFRow lastRow = sheet1.getRow(poiModel.getRowIndex());XSSFCell lastCell = lastRow.createCell(poiModel.getCellIndex());//创建列lastCell.setCellValue(poiModel.getContent());//合并单元格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);}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());//合并单元格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);}FileOutputStream out = null;try {out = new FileOutputStream("E:\\" + fileName + ".xlsx");wb.write(out);} catch (Exception ex) {try {out.flush();out.close();}catch (IOException e){e.printStackTrace();}}return flag;}/***判断是不是数字* @param str* @return*/private static boolean isNumeric(String str){if(str == null || str.length() == 0) {return false;}Pattern pattern = pile("^[-\\+]?[\\d]*$");return pattern.matcher(str).matches();}

}

pojo

package com.daxiao.test.poJo;

/**

Created by Bob on /5/12.

*/

public class PoiModel {

private String content;

private String oldContent;private int rowIndex;private int cellIndex;public String getOldContent() {return oldContent;}public void setOldContent(String oldContent) {this.oldContent = oldContent;}public String getContent() {return content;}public void setContent(String content) {this.content = content;}public int getRowIndex() {return rowIndex;}public void setRowIndex(int rowIndex) {this.rowIndex = rowIndex;}public int getCellIndex() {return cellIndex;}public void setCellIndex(int cellIndex) {this.cellIndex = cellIndex;}

}

public class Startover {

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<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 = ExportExcel.exportToExcelForXlsx(exportData, fileName, sheetName, columns, mergeIndex);

System.out.println(flag);

}

}

因为我的数据是在内网，拿出来比较麻烦，实现的效果和图片内容一样

转自:/infodetail-3613922.html?cf_chl_jschl_tk=3e4633a1a7393b9add9b809609dc1b065902757a-1620785914-0-AetcD7IGJN3edlQCOR_qMh22GK4KhXnfMUkxGx4PvHgc331sqg7iiwI03_oHwrXVwL3YSjhyq0TYiPN0ojJ8RI6fgO9oFR350ZjMuxrcebr59hS_T8nCGwhLGvRf2Mp7alobIIclge8Yyj6POQO_XE2yW6G6t6c7nOTmhSDkchrbn0Ok6BjmtSh4DPndJBpshjmF6xGiPtw6iOC_IUgTM2YQtEvMyVwckrUkjSdVWa4x9WlqzjFSxHAbSHBbuxuSDEIErtZABHy0uOL6FUeaWe5BFgDRI-0m9b7yzp0TJXi8yMuXj235qltBHra_CawI66qSTmP22wb8Var92FiOuonfonrAogun1dNakrbcHxm5akGSzAVnXJP7295lz5NtHjr3PzGTOGS_q8VRuERAXJ8