SpringBoot2.x使用POI实现导入数据到Excel

都嫣然

一、前言

最近做一个推送服务将系统某些报表，通过邮件附件的形式推送给指定的人，首先我们需要在服务端生成报表的excel文档。生成excel文档采用的是POI的方式。

二、pom引入相应的jar包

引入三个poi相关的jar包

1. <dependency>
2. <groupId>org.apache.poi</groupId>
3. <artifactId>poi</artifactId>
4. <version>4.1.2</version>
5. </dependency>
6. <dependency>
7. <groupId>org.apache.poi</groupId>
8. <artifactId>poi-ooxml</artifactId>
9. <version>4.1.2</version>
10. </dependency>
11. <dependency>
12. <groupId>org.apache.poi</groupId>
13. <artifactId>poi-ooxml-schemas</artifactId>
14. <version>4.1.2</version>
15. </dependency>

复制代码

注意：这三个jar包的版本号一定要保持一致。否则的话会引起异常，无法创建XSSFWorkbook

三、导出方法封装类

1. import lombok.extern.slf4j.Slf4j;
2. import org.apache.http.client.utils.DateUtils;
3. import org.apache.poi.ss.usermodel.Cell;
4. import org.apache.poi.ss.usermodel.Row;
5. import org.apache.poi.ss.usermodel.Sheet;
6. import org.apache.poi.ss.usermodel.Workbook;
7. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
9. import java.io.FileOutputStream;
10. import java.io.IOException;
11. import java.sql.Timestamp;
12. import java.util.*;
14. /**
15. * POI导出Excel封装
16. * @author lenny
17. * @date 20210715
18. */
19. @Slf4j
20. public class ExcelUtil {
21. /**
22. * @param fileName excel文件名
23. * @param headMap 表头map
24. * @param dataList 表格数据
25. */
26. public static String exportExcel(String fileName,Map<String, String> headMap, List<Map<String, Object>> dataList) {
27. String fullPath="";
28. Workbook workbook = exportXlsx(fileName, headMap, dataList);
29. FileOutputStream outputStream=null;
30. try {
31. //将生成的excel存储在系统根目录下excel文件夹中
32. String path=CommonUtil.getRunningDirectory()+"\\excel\";
33. fullPath=path+fileName;
34. outputStream = new FileOutputStream(path+fileName);
35. workbook.write(outputStream);
36. } catch (Exception e) {
37. e.printStackTrace();
38. } finally {
39. if (outputStream != null) {
40. try {
41. outputStream.close();
42. } catch (IOException e) {
43. e.printStackTrace();
44. }
45. }
46. }
47. //返回文件所在的目录
48. return fullPath;
49. }
51. /**
52. * 导出数据
53. *
54. * @param headMap 表头map
55. * @param dataList 导出的数据
56. */
57. public static Workbook exportXlsx(String sheetName, Map<String, String> headMap, List<Map<String, Object>> dataList) {
58. try {
59. XSSFWorkbook workbook = new XSSFWorkbook();
60. Sheet sheet = workbook.createSheet(sheetName);
61. //初始化行列索引
62. int rowIndex = 0, columnIndex = 0;
63. Set<String> keys = headMap.keySet();
64. //表头
65. Row row = sheet.createRow(rowIndex++);
66. for (String key : keys) {
67. Cell cell = row.createCell(columnIndex++);
68. cell.setCellValue(headMap.get(key));
69. }
70. //内容
71. if (dataList != null && !dataList.isEmpty()) {
72. for (Map<String, Object> map : dataList) {
73. row = sheet.createRow(rowIndex++);
74. columnIndex = 0;
75. for (String key : keys) {
76. Cell cell = row.createCell(columnIndex++);
77. setCellValue(cell, map.get(key));
78. }
79. }
80. }
81. return workbook;
82. }catch (Exception e){
83. log.error(e.toString());
84. return null;
85. }
86. }
88. /**
89. * 填充单元格内容
90. * @param cell
91. * @param obj
92. */
93. private static void setCellValue(Cell cell, Object obj) {
94. if (obj == null) {
95. return;
96. }
97. if (obj instanceof String) {
98. cell.setCellValue((String) obj);
99. } else if (obj instanceof Date) {
100. Date date = (Date) obj;
101. if (date != null) {
102. cell.setCellValue(DateUtils.formatDate(date));
103. }
104. } else if (obj instanceof Calendar) {
105. Calendar calendar = (Calendar) obj;
106. if (calendar != null) {
107. cell.setCellValue(DateUtils.formatDate(calendar.getTime()));
108. }
109. } else if (obj instanceof Timestamp) {
110. Timestamp timestamp = (Timestamp) obj;
111. if (timestamp != null) {
112. cell.setCellValue(DateUtils.formatDate(new Date(timestamp.getTime())));
113. }
114. } else if (obj instanceof Double) {
115. cell.setCellValue((Double) obj);
116. } else {
117. cell.setCellValue(obj.toString());
118. }
119. }
120. }

复制代码

其中使用到了封装的获取系统根目录的方法，方法实现如下：

1. /**
2. * 得到程序运行的根目录
3. * @return
4. */
5. public static String getRunningDirectory()
6. {
7. return System.getProperty("user.dir");
8. }

复制代码

四、调用方法执行生成excel操作

1. /**
2. * 导出Adas报警
3. * @param reportAdasList
4. * @param language
5. * @param timeDiff
6. * @return
7. */
8. public static String exportAdasReport(List<ReportAdasList> reportAdasList, int language, int timeDiff) {
9. //表头
10. Map<String, String> headNameMap = new LinkedHashMap<String, String>();
11. headNameMap.put("agentName", getHeadName("agentName",language));
12. headNameMap.put("vehicleName", getHeadName("vehicleName",language));
13. headNameMap.put("assetID", getHeadName("assetID",language));
14. headNameMap.put("alarmType", getHeadName("alarmType",language));
15. headNameMap.put("alarmLevel", getHeadName("alarmLevel",language));
16. headNameMap.put("startTime", getHeadName("startTime",language));
17. headNameMap.put("endTime", getHeadName("endTime",language));
18. headNameMap.put("driverName", getHeadName("driverName",language));
19. headNameMap.put("startAddress", getHeadName("startAddress",language));
20. headNameMap.put("endAddress", getHeadName("endAddress",language));
21. headNameMap.put("processStatus", getHeadName("processStatus",language));
22. headNameMap.put("processContext", getHeadName("processContext",language));
23. headNameMap.put("processTime", getHeadName("processTime",language));
24. //表格数据
25. List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
26. for(ReportAdasList item : reportAdasList){
27. Map<String, Object> map = new HashMap<String, Object>();
28. map.put("agentName", item.getAgentName());
29. map.put("vehicleName", item.getVehicleName());
30. map.put("assetID", item.getAssetID());
31. map.put("alarmType", language==1?item.getCnName():item.getEnName());
32. map.put("alarmLevel", getAlarmLevel(item.getAlarmLevel(),language));
33. map.put("startTime", CommonUtil.getLocalTime(item.getStartTime(),timeDiff));
34. map.put("endTime", CommonUtil.getLocalTime(item.getEndTime(),timeDiff));
35. map.put("driverName", item.getDriverName());
36. map.put("startAddress", CommonUtil.geoCoding(String.valueOf(item.getStartLatitude()),String.valueOf(item.getStartLongitude()),language));
37. map.put("endAddress", CommonUtil.geoCoding(String.valueOf(item.getEndLatitude()),String.valueOf(item.getEndLongitude()),language));
38. map.put("processStatus", getProcessStatus(item.getProcessStatus(),language));
39. map.put("processContext", item.getProcessContext());
40. map.put("processTime", CommonUtil.getLocalTime(item.getProcessTime(),timeDiff));
41. dataList.add(map);
42. }
43. String fileName = "";
44. if(language==1) {
45. fileName= CommonConstants.CN_REPORT_ADAS;
46. }else {
47. fileName=CommonConstants.EN_REPORT_ADAS;
48. }
49. fileName=fileName+".xlsx";
50. String fullPath = ExcelUtil.exportExcel(fileName,headNameMap,dataList);
51. return fullPath;
52. }

复制代码

这里大家可以根据自己的系统自行调整，最后封装成headMap（表头字段）与List>（数据列表）即可。

到此这篇关于SpringBoot2.x使用POI实现导入数据到Excel的文章就介绍到这了,更多相关SpringBoot POI导入数据到Excel内容请搜索晓枫资讯以前的文章或继续浏览下面的相关文章希望大家以后多多支持晓枫资讯！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

找名真难丝

感谢楼主，顶。