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

素颜马尾好姑娘i 2023-09-28 12:53 136阅读 0赞

**目录**

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();
 }
 }
 }

## 2.2 输出的Excel结果： ##

<table style="width:270.77pt;"> 
 <tbody> 
 <tr> 
 <td style="text-align:left;vertical-align:middle;width:84pt;">秦时明月汉时关</td> 
 <td style="text-align:left;vertical-align:middle;width:48pt;">万</td> 
 <td style="vertical-align:bottom;width:90.75pt;"></td> 
 <td style="text-align:left;vertical-align:middle;width:48pt;">不</td> 
 </tr> 
 <tr> 
 <td style="text-align:left;vertical-align:middle;">里</td> 
 <td style="vertical-align:bottom;"></td> 
 <td style="text-align:left;vertical-align:middle;">教</td> 
 </tr> 
 <tr> 
 <td style="text-align:left;vertical-align:middle;">长</td> 
 <td style="vertical-align:bottom;"></td> 
 <td style="text-align:left;vertical-align:middle;">胡</td> 
 </tr> 
 <tr> 
 <td style="text-align:left;vertical-align:middle;">征</td> 
 <td style="text-align:left;vertical-align:middle;">但使龙城飞将在</td> 
 <td style="text-align:left;vertical-align:middle;">马</td> 
 </tr> 
 <tr> 
 <td style="text-align:left;vertical-align:middle;">人</td> 
 <td style="vertical-align:bottom;"></td> 
 <td style="text-align:left;vertical-align:middle;">度</td> 
 </tr> 
 <tr> 
 <td style="text-align:left;vertical-align:middle;">未</td> 
 <td style="vertical-align:bottom;"></td> 
 <td style="text-align:left;vertical-align:middle;">阴</td> 
 </tr> 
 <tr> 
 <td style="text-align:left;vertical-align:middle;">还</td> 
 <td style="vertical-align:bottom;"></td> 
 <td style="text-align:left;vertical-align:middle;">山</td> 
 </tr> 
 </tbody> 
</table>

# 附录： #

合并单元格的读取见另一篇文章：

[Java读取Excel中的合并单元格![icon-default.png_t_M5H6][]https://blog.csdn.net/u012998680/article/details/124557925][Java_Excel_icon-default.png_t_M5H6_https_blog.csdn.net_u012998680_article_details_124557925]

[icon-default.png_t_M5H6]: https://csdnimg.cn/release/blog_editor_html/release2.1.4/ckeditor/plugins/CsdnLink/icons/icon-default.png?t=M5H6
[Java_Excel_icon-default.png_t_M5H6_https_blog.csdn.net_u012998680_article_details_124557925]: https://blog.csdn.net/u012998680/article/details/124557925