Java 使用SSH框架导出多Sheet复杂表头的Excel数据

以下是一个使用 Java 和 Apache POI 库来生成与你上传文件结构类似的 Excel 文件的示例代码。假设你想要生成包含三个工作表（分别对应 ' 费用合计 '、' 苹果汁 '、' 橙汁 '）且结构一致的文件。

前提条件

确保你的项目中已经引入了 Apache POI 相关的依赖。如果使用 Maven，在pom.xml中添加以下依赖：

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

示例代码

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;
import java.io.IOException;public class ExcelGenerator {public static void generateExcel() {// 创建一个新的Excel工作簿try (XSSFWorkbook workbook = new XSSFWorkbook()) {// 创建'费用合计'工作表XSSFSheet costSummarySheet = workbook.createSheet("费用合计");// 填充'费用合计'工作表的数据（根据之前输出的结构）String[][] costSummaryData = {{null, null, null, null, null, null, null, null},{null, null, null, null, null, null, null, null},{null, null, null, null, null, null, null, null},{null, null, null, null, null, null, null, null},{null, null, null, null, null, null, null, null},{null, null, null, null, null, null, null, null},{null, null, null, null, null, null, null, null},{null, "青岛港怡之航冷链物流有限公司物流费用表", null, null, null, null, null, null},{null, "序号", "费用名称", "品名", "公司名称", "储存条件", "月末净吨（吨）", "费用合计（元）"},{null, "1", "仓储费", "苹果汁", "运城田源", "冷藏", "1200.1", "37377.13"},{null, "2", "仓储费", "橙汁", "派森百", "冷冻", "52", "7374.93"},{null, "合计", null, null, null, null, "1252.1", "44752.06"}};for (int rowIndex = 0; rowIndex < costSummaryData.length; rowIndex++) {Row row = costSummarySheet.createRow(rowIndex);String[] rowData = costSummaryData[rowIndex];for (int colIndex = 0; colIndex < rowData.length; colIndex++) {Cell cell = row.createCell(colIndex);if (rowData[colIndex] != null) {cell.setCellValue(rowData[colIndex]);}}}// 创建'苹果汁'工作表XSSFSheet appleJuiceSheet = workbook.createSheet("苹果汁");// 填充'苹果汁'工作表的表头（根据之前输出的结构）String[][] appleJuiceHeader = {{"日期 ", "品名", "入库", null, null, null, "出库", null, null, null, "结存", null, null, null, "仓储费（元）", null, null, "入库包干费 9元/净重吨", "出库包干费9元/净重吨", "普通理货费 80元/柜", "加固费 20元/柜", "标签制作及粘贴费1元/张", "费用合计"},{null, null, "铁桶/木箱", null, null, null, "铁桶/木箱", null, null, null, "铁桶/吨箱", null, null, null, "数量(吨)", "单价", "金额", null, null, null, null, null, null},{null, null, "件数（桶）", "件数（箱）", "净重(吨)", "毛重（吨）", "件数（桶）", "件数（箱）", "净重(吨)", "毛重（吨）", "件数（桶）", "件数（箱）", "净重(吨)", "毛重（吨）", null, null, null, null, null, null, null, null, null}};for (int rowIndex = 0; rowIndex < appleJuiceHeader.length; rowIndex++) {Row row = appleJuiceSheet.createRow(rowIndex);String[] rowData = appleJuiceHeader[rowIndex];for (int colIndex = 0; colIndex < rowData.length; colIndex++) {Cell cell = row.createCell(colIndex);if (rowData[colIndex] != null) {cell.setCellValue(rowData[colIndex]);}}}// 填充'苹果汁'工作表的部分数据（这里只是示例，可以根据实际需求扩展）String[][] appleJuiceData = {{"2025-04-30 00:00:00", "苹果汁", null, null, null, null, null, null, null, null, "4996", null, "1373.9", "1448.84", "1373.9", "0.85", "1167.815", "0", "0", null, null, null, "1167.815"},{"2025-05-01 00:00:00", "苹果汁", "0", null, "0", "0", "0", null, "0", "0", "4996", "0", "1373.9", "1448.84", "1373.9", "0.85", "1167.815", "0", "0", null, null, null, "1167.815"}};int startRowIndex = 3;for (int rowIndex = 0; rowIndex < appleJuiceData.length; rowIndex++) {Row row = appleJuiceSheet.createRow(startRowIndex + rowIndex);String[] rowData = appleJuiceData[rowIndex];for (int colIndex = 0; colIndex < rowData.length; colIndex++) {Cell cell = row.createCell(colIndex);if (rowData[colIndex] != null) {cell.setCellValue(rowData[colIndex]);}}}// 创建'橙汁'工作表XSSFSheet orangeJuiceSheet = workbook.createSheet("橙汁");// 填充'橙汁'工作表的表头（根据之前输出的结构）String[][] orangeJuiceHeader = {{"日期 ", "品名", "入库", null, null, null, "出库", null, null, null, "结存", null, null, null, "仓储费（元）", null, null, "出入库及一次性降温18元/净重吨", "入库包干费 9元/净重吨", "出库包干费9元/净重吨", "普通理货费 80元/柜", "加固费 20元/柜", "标签制作及粘贴费1元/张", "费用合计"},{null, null, "铁桶/木箱", null, null, null, "铁桶/木箱", null, null, null, "铁桶/吨箱", null, null, null, "数量(吨)", "单价", "金额", null, null, null, null, null, null},{null, null, "件数（桶）", "件数（箱）", "净重(吨)", "毛重（吨）", "件数（桶）", "件数（箱）", "净重(吨)", "毛重（吨）", "件数（桶）", "件数（箱）", "净重(吨)", "毛重（吨）", null, null, null, null, null, null, null, null, null}};for (int rowIndex = 0; rowIndex < orangeJuiceHeader.length; rowIndex++) {Row row = orangeJuiceSheet.createRow(rowIndex);String[] rowData = orangeJuiceHeader[rowIndex];for (int colIndex = 0; colIndex < rowData.length; colIndex++) {Cell cell = row.createCell(colIndex);if (rowData[colIndex] != null) {cell.setCellValue(rowData[colIndex]);}}}// 填充'橙汁'工作表的部分数据（这里只是示例，可以根据实际需求扩展）String[][] orangeJuiceData = {{"2025-04-30 00:00:00", "橙汁", null, null, null, null, null, null, null, null, "722", null, "180.5", "194.73", "180.5", "1.2", "216.6", null, null, null, null, "0", "0", "216.6"},{"2025-05-01 00:00:00", "橙汁", "0", null, null, "0", null, null, null, null, "722", "0", "180.5", "194.73", "180.5", "1.2", "216.6", null, null, null, null, null, null, "216.6"}};startRowIndex = 3;for (int rowIndex = 0; rowIndex < orangeJuiceData.length; rowIndex++) {Row row = orangeJuiceSheet.createRow(startRowIndex + rowIndex);String[] rowData = orangeJuiceData[rowIndex];for (int colIndex = 0; colIndex < rowData.length; colIndex++) {Cell cell = row.createCell(colIndex);if (rowData[colIndex] != null) {cell.setCellValue(rowData[colIndex]);}}}// 保存Excel文件try (FileOutputStream outputStream = new FileOutputStream("/mnt/新生成的仓储费汇总文件.xlsx")) {workbook.write(outputStream);}} catch (IOException e) {e.printStackTrace();}}public static void main(String[] args) {generateExcel();}
}