1200字范文 > Java：Excel写入“合并单元格“

Java：Excel写入“合并单元格“

时间：2021-11-16 23:59:43

相关推荐

Java：Excel写入“合并单元格“

1、 Maven仓库下载导入

2、写入

2.1 数据格式

2.2 代码

2.2 输出的Excel结果：

附录：

本文以Java示例展示Excel中的写入“合并单元格”的方法。

1、 Maven仓库下载导入

在pom.xml中配置maven路径，指定依赖，如下：

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>

2、写入

2.1 数据格式

要把每个单元格信息（包含：位置、值等）都一一列举清楚，

需要的字段：

位置：firstRow，lastRow，firstCol，lastCol；(从0开始)

值：value；

[{"firstRow":0,"lastRow":6,"lastCol":0,"firstCol":0,"value":"秦时明月汉时关"},{"firstRow":0,"lastRow":0,"lastCol":1,"firstCol":1,"value":"万"},{"firstRow":1,"lastRow":1,"lastCol":1,"firstCol":1,"value":"里"},{"firstRow":2,"lastRow":2,"lastCol":1,"firstCol":1,"value":"长"},{"firstRow":3,"lastRow":3,"lastCol":1,"firstCol":1,"value":"征"},{"firstRow":4,"lastRow":4,"lastCol":1,"firstCol":1,"value":"人"},{"firstRow":5,"lastRow":5,"lastCol":1,"firstCol":1,"value":"未"},{"firstRow":6,"lastRow":6,"lastCol":1,"firstCol":1,"value":"还"},{"firstRow":3,"lastRow":3,"lastCol":2,"firstCol":2,"value":"但使龙城飞将在"},{"firstRow":0,"lastRow":0,"lastCol":3,"firstCol":3,"value":"不"},{"firstRow":1,"lastRow":1,"lastCol":3,"firstCol":3,"value":"教"},{"firstRow":2,"lastRow":2,"lastCol":3,"firstCol":3,"value":"胡"},{"firstRow":3,"lastRow":3,"lastCol":3,"firstCol":3,"value":"马"},{"firstRow":4,"lastRow":4,"lastCol":3,"firstCol":3,"value":"度"},{"firstRow":5,"lastRow":5,"lastCol":3,"firstCol":3,"value":"阴"},{"firstRow":6,"lastRow":6,"lastCol":3,"firstCol":3,"value":"山"}]

2.2 代码

package com;import com.alibaba.fastjson.JSONObject;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;import java.io.IOException;import java.util.List;public class ExcelTest {/*** 写入excel数据** @param path excel保存路径* @param name excel名称* @param version excel格式，支持xls、xlsx* @param rows excel单元格信息（包含位置、值等信息,rows中每个Json必须包含位置和值的信息，*位置:firstRow，lastRow，firstCol，lastCol，值:value）*/public static void writeMergeExcel(String path, String name, String version,List<JSONObject> rows) {try {Workbook wb = produce(version);createSheet(wb, name, version, rows,null);String filePath = path + name;if (!filePath.endsWith(version)) {filePath = filePath +"."+ version;}FileOutputStream fileOut = new FileOutputStream(filePath);wb.write(fileOut);fileOut.close();} catch (Exception e) {e.printStackTrace();}}/*** 写入excel数据** @param path excel保存路径* @param name excel名称* @param version excel格式，支持xls、xlsx* @param rows excel单元格信息（包含位置、值等信息）* @param rowKeys excel单元格字段信息（rows中每个Json必须包含位置和值的信息，如果存储的字段与工具中的字段不一致，做个映射，工具中的字段：:firstRow，lastRow，firstCol，lastCol，值:value）*/public static void writeMergeExcel(String path, String name, String version,List<JSONObject> rows, JSONObject rowKeys) {try {Workbook wb = produce(version);createSheet(wb, name, version, rows, rowKeys);String filePath = path + name;if (!filePath.endsWith(version)) {filePath = filePath +"."+ version;}FileOutputStream fileOut = new FileOutputStream(filePath);wb.write(fileOut);fileOut.close();} catch (Exception e) {e.printStackTrace();}}private static void createSheet(Workbook wb, String name, String version,List<JSONObject> rows, JSONObject rowKeys) {Integer max = getMaxRowNumber(version);int sheetNum = 1;Sheet sheet = wb.createSheet(name + sheetNum);CellStyle style = wb.createCellStyle();style.setAlignment(HorizontalAlignment.LEFT);//设置水平对齐方式style.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐方式String firstRowKey = rowKeys != null ? rowKeys.getString("firstRow"):"firstRow";String lastRowKey = rowKeys != null ? rowKeys.getString("lastRow"):"lastRow";String firstColKey = rowKeys != null ? rowKeys.getString("firstCol"):"firstCol";String lastColKey = rowKeys != null ? rowKeys.getString("lastCol"):"lastCol";String valueKey = rowKeys != null ? rowKeys.getString("value"):"value";for (JSONObject rowJson : rows) {int lastRow = rowJson.getInteger(lastRowKey);if (lastRow > max){List<JSONObject> subRows = rows.subList(lastRow,rows.size());createSheet(wb, name, version, subRows, rowKeys);} else{int firstRow = rowJson.getInteger(firstRowKey);int firstCol = rowJson.getInteger(firstColKey);int lastCol = rowJson.getInteger(lastColKey);System.out.println(firstRow+"_"+lastRow +"_"+ firstCol +"_"+ lastCol);if (lastCol - firstCol > 0 || lastRow - firstRow > 0 ){CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);sheet.addMergedRegion(cellRangeAddress);}Row row ;if (sheet.getRow(firstRow)!=null){row = sheet.getRow(firstRow);} else {row = sheet.createRow(firstRow);}Cell cell = row.createCell(firstCol);cell.setCellValue(rowJson.getString(valueKey));cell.setCellStyle(style);}}}public static Workbook produce(String version) {switch (version) {case "xls":return new HSSFWorkbook();case "xlsx":return new XSSFWorkbook();case "sxlsx":return new SXSSFWorkbook();default:return null;}}private static int maxXls = 65024;private static int maxXlsx = 1048576;public static Integer getMaxRowNumber(String version) {switch (version) {case "xls":return maxXls;case "xlsx":return maxXlsx;case "sxlsx":return maxXlsx;default:return null;}}public static void main(String[] args) {try {List<JSONObject> list = (List<JSONObject>) JSONObject.parse("[{\"firstRow\":0,\"lastRow\":6,\"lastCol\":0,\"firstCol\":0,\"value\":\"秦时明月汉时关\"},{\"firstRow\":0,\"lastRow\":0,\"lastCol\":1,\"firstCol\":1,\"value\":\"万\"},{\"firstRow\":1,\"lastRow\":1,\"lastCol\":1,\"firstCol\":1,\"value\":\"里\"},{\"firstRow\":2,\"lastRow\":2,\"lastCol\":1,\"firstCol\":1,\"value\":\"长\"},{\"firstRow\":3,\"lastRow\":3,\"lastCol\":1,\"firstCol\":1,\"value\":\"征\"},{\"firstRow\":4,\"lastRow\":4,\"lastCol\":1,\"firstCol\":1,\"value\":\"人\"},{\"firstRow\":5,\"lastRow\":5,\"lastCol\":1,\"firstCol\":1,\"value\":\"未\"},{\"firstRow\":6,\"lastRow\":6,\"lastCol\":1,\"firstCol\":1,\"value\":\"还\"},{\"firstRow\":3,\"lastRow\":3,\"lastCol\":2,\"firstCol\":2,\"value\":\"但使龙城飞将在\"},{\"firstRow\":0,\"lastRow\":0,\"lastCol\":3,\"firstCol\":3,\"value\":\"不\"},{\"firstRow\":1,\"lastRow\":1,\"lastCol\":3,\"firstCol\":3,\"value\":\"教\"},{\"firstRow\":2,\"lastRow\":2,\"lastCol\":3,\"firstCol\":3,\"value\":\"胡\"},{\"firstRow\":3,\"lastRow\":3,\"lastCol\":3,\"firstCol\":3,\"value\":\"马\"},{\"firstRow\":4,\"lastRow\":4,\"lastCol\":3,\"firstCol\":3,\"value\":\"度\"},{\"firstRow\":5,\"lastRow\":5,\"lastCol\":3,\"firstCol\":3,\"value\":\"阴\"},{\"firstRow\":6,\"lastRow\":6,\"lastCol\":3,\"firstCol\":3,\"value\":\"山\"}]");writeMergeExcel("E:\\","mergeExcel", "xls" ,list);} catch (IOException e) {e.printStackTrace();}}}