当前位置：首页 > news >正文

Spring Boot导出EXCEL 文件

news 2025/8/26 5:21:39

主要功能:实现java导出excel到本地

JDK版本：openJDK 20.0.1

依赖pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>3.2.2</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.joker</groupId><artifactId>export</artifactId><version>0.0.1-SNAPSHOT</version><name>export</name><description>export</description><properties><java.version>17</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-devtools</artifactId><scope>runtime</scope><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.1.0</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>

主要核心代码

package com.joker.export.Controller;import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.*;
import java.util.Iterator;
import java.util.List;import java.util.Map;@RestController
public class ExportExcelController {@RequestMapping("/")public String home(){System.out.println("hello world");return "Hello World for Spring Boot by Joker！";}@RequestMapping("/Export2Excel")public String Export2Excel() throws IOException {// you should get the filename from the input dataString filename = "ExportDataByJoker"  +".xlsx";// Parse the JSON string into a JSON object.String jsonData = "[{\"ZHC_ZZ\":\"20001118\",\"ZHR_ID_JD\":\"20001118\",\"ZHC_ZZ_DESC\":\"\",\"ZHC_ZZ_LEVEL\":\"21\",\"ZHC_ZZ_LEVEL_DESC\":\"二级部门\",\"ZHC_PLAN\":0,\"ZHC_PLAN_ACTUAL\":0,\"ZHC_PLAN_AVAILABLE_AC\":0,\"ZHC_PLAN_RATE\":0.0000,\"ZHC_PLAN_RATE_CHAR\":\"\",\"ZHC_PLAN_LOCK\":0,\"ZHC_PLAN_RELEASE\":0,\"ZHC_PLAN_AVAILABLE_AD\":0,\"ZHC_FREEZE\":\"\",\"ZORG_FULLNAME\":\"\"},{\"ZHC_ZZ\":\"20001342\",\"ZHR_ID_JD\":\"20001342\",\"ZHC_ZZ_DESC\":\"测试组织20230915\",\"ZHC_ZZ_LEVEL\":\"21\",\"ZHC_ZZ_LEVEL_DESC\":\"二级部门\",\"ZHC_PLAN\":0,\"ZHC_PLAN_ACTUAL\":0,\"ZHC_PLAN_AVAILABLE_AC\":0,\"ZHC_PLAN_RATE\":0.0000,\"ZHC_PLAN_RATE_CHAR\":\"\",\"ZHC_PLAN_LOCK\":0,\"ZHC_PLAN_RELEASE\":0,\"ZHC_PLAN_AVAILABLE_AD\":0,\"ZHC_FREEZE\":\"\",\"ZORG_FULLNAME\":\"华中区\\/测试组织20230915\"},{\"ZHC_ZZ\":\"10000127\",\"ZHR_ID_JD\":\"01010603\",\"ZHC_ZZ_DESC\":\"长沙区\",\"ZHC_ZZ_LEVEL\":\"21\",\"ZHC_ZZ_LEVEL_DESC\":\"二级部门\",\"ZHC_PLAN\":0,\"ZHC_PLAN_ACTUAL\":374,\"ZHC_PLAN_AVAILABLE_AC\":-374,\"ZHC_PLAN_RATE\":0.0000,\"ZHC_PLAN_RATE_CHAR\":\"\",\"ZHC_PLAN_LOCK\":0,\"ZHC_PLAN_RELEASE\":2,\"ZHC_PLAN_AVAILABLE_AD\":-372,\"ZHC_FREEZE\":\"\",\"ZORG_FULLNAME\":\"华中区\\/长沙区\"},{\"ZHC_ZZ\":\"10006019\",\"ZHR_ID_JD\":\"0154\",\"ZHC_ZZ_DESC\":\"南昌区\",\"ZHC_ZZ_LEVEL\":\"21\",\"ZHC_ZZ_LEVEL_DESC\":\"二级部门\",\"ZHC_PLAN\":0,\"ZHC_PLAN_ACTUAL\":2,\"ZHC_PLAN_AVAILABLE_AC\":-2,\"ZHC_PLAN_RATE\":0.0000,\"ZHC_PLAN_RATE_CHAR\":\"\",\"ZHC_PLAN_LOCK\":0,\"ZHC_PLAN_RELEASE\":1,\"ZHC_PLAN_AVAILABLE_AD\":-1,\"ZHC_FREEZE\":\"\",\"ZORG_FULLNAME\":\"华中区\\/南昌区\"},{\"ZHC_ZZ\":\"10000009\",\"ZHR_ID_JD\":\"01010602\",\"ZHC_ZZ_DESC\":\"武汉区\",\"ZHC_ZZ_LEVEL\":\"21\",\"ZHC_ZZ_LEVEL_DESC\":\"二级部门\",\"ZHC_PLAN\":0,\"ZHC_PLAN_ACTUAL\":333,\"ZHC_PLAN_AVAILABLE_AC\":-333,\"ZHC_PLAN_RATE\":0.0000,\"ZHC_PLAN_RATE_CHAR\":\"\",\"ZHC_PLAN_LOCK\":0,\"ZHC_PLAN_RELEASE\":3,\"ZHC_PLAN_AVAILABLE_AD\":-330,\"ZHC_FREEZE\":\"\",\"ZORG_FULLNAME\":\"华中区\\/武汉区\"},{\"ZHC_ZZ\":\"20000365\",\"ZHR_ID_JD\":\"20000365\",\"ZHC_ZZ_DESC\":\"电商部\",\"ZHC_ZZ_LEVEL\":\"21\",\"ZHC_ZZ_LEVEL_DESC\":\"二级部门\",\"ZHC_PLAN\":0,\"ZHC_PLAN_ACTUAL\":36,\"ZHC_PLAN_AVAILABLE_AC\":-36,\"ZHC_PLAN_RATE\":0.0000,\"ZHC_PLAN_RATE_CHAR\":\"\",\"ZHC_PLAN_LOCK\":0,\"ZHC_PLAN_RELEASE\":0,\"ZHC_PLAN_AVAILABLE_AD\":-36,\"ZHC_FREEZE\":\"\",\"ZORG_FULLNAME\":\"华中区\\/电商部\"},{\"ZHC_ZZ\":\"20000366\",\"ZHR_ID_JD\":\"20000366\",\"ZHC_ZZ_DESC\":\"业务支持部\",\"ZHC_ZZ_LEVEL\":\"21\",\"ZHC_ZZ_LEVEL_DESC\":\"二级部门\",\"ZHC_PLAN\":0,\"ZHC_PLAN_ACTUAL\":28,\"ZHC_PLAN_AVAILABLE_AC\":-28,\"ZHC_PLAN_RATE\":0.0000,\"ZHC_PLAN_RATE_CHAR\":\"\",\"ZHC_PLAN_LOCK\":0,\"ZHC_PLAN_RELEASE\":1,\"ZHC_PLAN_AVAILABLE_AD\":-27,\"ZHC_FREEZE\":\"\",\"ZORG_FULLNAME\":\"华中区\\/业务支持部\"},{\"ZHC_ZZ\":\"20000367\",\"ZHR_ID_JD\":\"20000367\",\"ZHC_ZZ_DESC\":\"市场品牌部\",\"ZHC_ZZ_LEVEL\":\"21\",\"ZHC_ZZ_LEVEL_DESC\":\"二级部门\",\"ZHC_PLAN\":0,\"ZHC_PLAN_ACTUAL\":15,\"ZHC_PLAN_AVAILABLE_AC\":-15,\"ZHC_PLAN_RATE\":0.0000,\"ZHC_PLAN_RATE_CHAR\":\"\",\"ZHC_PLAN_LOCK\":0,\"ZHC_PLAN_RELEASE\":0,\"ZHC_PLAN_AVAILABLE_AD\":-15,\"ZHC_FREEZE\":\"\",\"ZORG_FULLNAME\":\"华中区\\/市场品牌部\"},{\"ZHC_ZZ\":\"20000559\",\"ZHR_ID_JD\":\"20000559\",\"ZHC_ZZ_DESC\":\"成本中心测试\",\"ZHC_ZZ_LEVEL\":\"21\",\"ZHC_ZZ_LEVEL_DESC\":\"二级部门\",\"ZHC_PLAN\":0,\"ZHC_PLAN_ACTUAL\":0,\"ZHC_PLAN_AVAILABLE_AC\":0,\"ZHC_PLAN_RATE\":0.0000,\"ZHC_PLAN_RATE_CHAR\":\"\",\"ZHC_PLAN_LOCK\":0,\"ZHC_PLAN_RELEASE\":0,\"ZHC_PLAN_AVAILABLE_AD\":0,\"ZHC_FREEZE\":\"\",\"ZORG_FULLNAME\":\"华中区\\/成本中心测试\"}]";// Convert JSON to a list of mapsObjectMapper mapper = new ObjectMapper();List<Map<String, Object>> dataList = mapper.readValue(jsonData, new TypeReference<List<Map<String, Object>>>() {});// Create an Excel workbookWorkbook workbook = new XSSFWorkbook();// Create a sheetSheet sheet = workbook.createSheet("Sheet1");// Create a header rowRow headerRow = sheet.createRow(0);// Get the column names from the first map entry,// get the column names from the input data is betterMap<String, Object> firstData = dataList.get(0);Iterator<Map.Entry<String, Object>> iterator = firstData.entrySet().iterator();int columnCount = 0;while (iterator.hasNext()) {Map.Entry<String, Object> entry = iterator.next();String columnName = entry.getKey();headerRow.createCell(columnCount).setCellValue(columnName);columnCount++;}// Create data rowsint rowIndex = 1;for (Map<String, Object> data : dataList) {Row dataRow = sheet.createRow(rowIndex);int dataColumnCount = 0;for (Map.Entry<String, Object> entry : data.entrySet()) {Object value = entry.getValue();Cell cell = dataRow.createCell(dataColumnCount);if (value instanceof String) {cell.setCellValue((String) value);} else if (value instanceof Number) {cell.setCellValue(((Number) value).doubleValue());} else {cell.setCellValue(value.toString());}dataColumnCount++;}rowIndex++;}// Auto-size the columnsfor (int i = 0; i < columnCount; i++) {sheet.autoSizeColumn(i);}// Write the workbook to a filetry (FileOutputStream out = new FileOutputStream(filename)) {workbook.write(out);}// change the return to file streamreturn "download file is ready";}
}