概述
原图
合并之后图片
工具类ExportExcel
package com.daxiao.test.util;
/**
- Created by Bob on 2021/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();
// sheet1
XSSFSheet 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 = Pattern.compile("^[-\+]?[\d]*$");
return pattern.matcher(str).matches();
}
}
pojo
package com.daxiao.test.poJo;
/**
- Created by Bob on 2021/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);
}
}
因为我的数据是在内网，拿出来比较麻烦，实现的效果和图片内容一样
转自:http://www.bubuko.com/infodetail-3613922.html?cf_chl_jschl_tk=3e4633a1a7393b9add9b809609dc1b065902757a-1620785914-0-AetcD7IGJN3edlQCOR_qMh22GK4KhXnfMUkxGx4PvHgc331sqg7iiwI03_oHwrXVwL3YSjhyq0TYiPN0ojJ8RI6fgO9oFR350ZjMuxrcebr59hS_T8nCGwhLGvRf2Mp7alobIIclge8Yyj6POQO_XE2yW6G6t6c7nOTmhSDkchrbn0Ok6BjmtSh4DPndJBpshjmF6xGiPtw6iOC_IUgTM2YQtEvMyVwckrUkjSdVWa4x9WlqzjFSxHAbSHBbuxuSDEIErtZABHy0uOL6FUeaWe5BFgDRI-0m9b7yzp0TJXi8yMuXj235qltBHra_CawI66qSTmP22wb8Var92FiOuonfonrAogun1dNakrbcHxm5akGSzAVnXJP7295lz5NtHjr3PzGTOGS_q8VRuERAXJ8
最后
以上就是幸福棒棒糖为你收集整理的poi导出excel 并实现合并单元格的全部内容，希望文章能够帮你解决poi导出excel 并实现合并单元格所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复