经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » Java相关 » Java » 查看文章
HExcel,一个简单通用的导入导出Excel工具类 - huanzi-qch
来源:cnblogs  作者:huanzi-qch  时间:2023/11/1 9:02:30  对本文有异议

  前言

  日常开发中,Excel的导出、导入可以说是最常见的功能模块之一,一个通用的、健壮的的工具类可以节省大量开发时间,让我们把更多精力放在业务处理上中

  之前我们也写了一个Excel的简单导出,甚至可以不依赖poi,还扩展了纯前端导出Excel!详情请戳:《POI导出Excel 》,遗憾的是这些导出并不支持复杂表头

  HExcel,一个简单通用的导入导出Excel工具类
    1、支持导出复杂表头(支持表头单元格水平合并、垂直合并,支持表头单元格个性化样式)
    2、支持导入读取sheet数据(只需要提供title与key的关系,不需要管列的顺序)

  代码思路都在代码注释里,感兴趣的自己看注释

 

  PS:依赖 poi 以及 hutool

  1. <!-- POI -->
  2. <dependency>
  3. <groupId>org.apache.poi</groupId>
  4. <artifactId>poi</artifactId>
  5. <version>5.2.3</version>
  6. </dependency>
  7. <dependency>
  8. <groupId>org.apache.poi</groupId>
  9. <artifactId>poi-ooxml</artifactId>
  10. <version>5.2.3</version>
  11. </dependency>
  12. <!-- hutool -->
  13. <dependency>
  14. <groupId>cn.hutool</groupId>
  15. <artifactId>hutool-all</artifactId>
  16. <version>5.7.4</version>
  17. </dependency>

 

  先睹为快

   表头目前支持以下属性,可自行扩展:

  1. title 标题
  2. key key
  3. width 宽度
  4. align 对齐方式
  5. background-color 背景颜色(POIIndexedColors
  6. color 字体颜色(POIIndexedColors
  7. children 子级表头

 

  导出

  代码

  1. //获取HExcel实例
  2. HExcel hExcel1 = HExcel.newInstance();
  3. //数据,一般是查数据库,经过数据处理生成
  4. List<Map<String, Object>> dataList = new ArrayList<>();
  5. HashMap<String, Object> date1 = new HashMap<>();
  6. date1.put("user_name","张三");
  7. date1.put("sex","男");
  8. date1.put("age",20);
  9. date1.put("yu_wen",90);
  10. date1.put("ying_yu",0);
  11. date1.put("shu_xue",85);
  12. date1.put("wu_li",80);
  13. date1.put("total",255);
  14. dataList.add(date1);
  15. HashMap<String, Object> date2 = new HashMap<>();
  16. date2.put("user_name","李四");
  17. date2.put("sex","女");
  18. date2.put("age",18);
  19. date2.put("yu_wen",81);
  20. date2.put("ying_yu",0);
  21. date2.put("shu_xue",90);
  22. date2.put("wu_li",70);
  23. date2.put("total",241);
  24. dataList.add(date2);
  25. //如果是固定表头数据,可以在项目资源文件夹下面新建个json文件夹,用来保存表头json数据,方便读、写
  26. //JSONArray header = JSONUtil.parseArray(ResourceUtil.readUtf8Str("json/header.json"));
  27. //如果是动态表头数据,直接把json字符串写在代码里,方便动态生成表头数据
  28. //表头
  29. String sheetName = "学生成绩单";
  30. JSONArray headers = JSONUtil.parseArray("" +
  31. "[\n" +
  32. " {\n" +
  33. " \"title\":\""+sheetName+"\",\n" +
  34. " \"children\":[\n" +
  35. " {\n" +
  36. " \"title\":\"日期:"+DateUtil.today()+"\",\n" +
  37. " \"align\":\"right\",\n" +
  38. " \"children\":[\n" +
  39. " {\n" +
  40. " \"title\":\"姓名\",\n" +
  41. " \"key\":\"user_name\",\n" +
  42. " },\n" +
  43. " {\n" +
  44. " \"title\":\"语文\",\n" +
  45. " \"key\":\"yu_wen\",\n" +
  46. " },\n" +
  47. " {\n" +
  48. " \"title\":\"数学\",\n" +
  49. " \"key\":\"shu_xue\",\n" +
  50. " },\n" +
  51. " {\n" +
  52. " \"title\":\"总分\",\n" +
  53. " \"key\":\"total\",\n" +
  54. " \"background-color\":17,\n" +
  55. " \"color\":10,\n" +
  56. " \"width\":30,\n" +
  57. " },\n" +
  58. " ]\n" +
  59. " },\n" +
  60. " ]\n" +
  61. " },\n" +
  62. "]" +
  63. "");
  64. //生成sheet
  65. hExcel1.buildSheet(sheetName, headers, dataList);
  66. //保存成File文件
  67. hExcel1.toFile("C:\\Users\\XFT User\\Desktop\\学生成绩单复杂表头导出测试.xls");
  68. //关闭对象
  69. hExcel1.close();

  效果

 

  导入

  需要导入的Excel文件

   代码

  1. //需要设置title与key的关系
  2. JSONObject headerTitleKey = new JSONObject("" +
  3. "{\n" +
  4. " \"姓名\":\"user_name\",\n" +
  5. " \"语文\":\"yu_wen\",\n" +
  6. " \"数学\":\"shu_xue\",\n" +
  7. " \"总分\":\"total\",\n" +
  8. "}" +
  9. "");
  10. //根据Excel文件,获取HExcel实例
  11. HExcel hExcel2 = HExcel.newInstance(new File("C:\\Users\\XFT User\\Desktop\\学生成绩单复杂表头导出测试.xls"));
  12. //根据title-key关系,读取指定位置的sheet数据
  13. List<Map<String, Object>> sheetList = hExcel2.readSheet(2, 3, headerTitleKey);
  14. //打印sheetList数据
  15. for (Map<String, Object> map : sheetList) {
  16. System.out.println(map.toString());
  17. }
  18. //关闭对象
  19. hExcel2.close();

  效果

  {user_name=张三, yu_wen=90, shu_xue=85, total=255}
  {user_name=李四, yu_wen=81, shu_xue=90, total=241}

 

  完整代码

  1. package cn.huanzi.qch.util;
  2. import cn.hutool.json.JSONArray;
  3. import cn.hutool.json.JSONObject;
  4. import org.apache.poi.hssf.usermodel.*;
  5. import org.apache.poi.ss.usermodel.*;
  6. import org.apache.poi.ss.util.CellRangeAddress;
  7. import javax.servlet.ServletOutputStream;
  8. import javax.servlet.http.HttpServletResponse;
  9. import java.io.*;
  10. import java.net.URLEncoder;
  11. import java.util.*;
  12. /**
  13. * HExcel,一个简单通用的导入导出Excel工具类
  14. * 1、支持复杂表头导出(支持表头单元格水平合并、垂直合并,支持表头单元格个性化样式)
  15. * 2、支持导入读取sheet数据(只需要提供title与key的关系,不需要管列的顺序)
  16. *
  17. * PS:依赖 poi 以及 hutool
  18. *
  19. * 详情请戳:https://www.cnblogs.com/huanzi-qch/p/17797355.html
  20. */
  21. public class HExcel {
  22. /**
  23. * 获取一个HExcel实例,并初始化空Workbook对象
  24. */
  25. public static HExcel newInstance(){
  26. HExcel hExcelUtil = new HExcel();
  27. hExcelUtil.hSSFWorkbook = new HSSFWorkbook();
  28. return hExcelUtil;
  29. }
  30. /**
  31. * 获取一个HExcel实例,并根据excelFile初始化Workbook对象
  32. */
  33. public static HExcel newInstance(File excelFile){
  34. HExcel hExcelUtil = new HExcel();
  35. try {
  36. hExcelUtil.hSSFWorkbook = new HSSFWorkbook(new FileInputStream(excelFile));
  37. } catch (IOException e) {
  38. throw new RuntimeException("【HExcel】 根据excelFile初始化Workbook对象异常",e);
  39. }
  40. return hExcelUtil;
  41. }
  42. /**
  43. * 导入并读取Excel
  44. *
  45. * @param sheetIndex 需要读取的sheet下标
  46. * @param firstDataRow 数据起始行
  47. * @param headerTitleKey title与key的关系json对象
  48. * @return 返回数据集合
  49. */
  50. public List<Map<String, Object>> readSheet(int sheetIndex,int firstDataRow,JSONObject headerTitleKey){
  51. //最终返回的数据集合
  52. ArrayList<Map<String, Object>> list = new ArrayList<>();
  53. //获取sheet
  54. HSSFSheet sheet = this.hSSFWorkbook.getSheetAt(sheetIndex);
  55. //获取title与col的对应关系
  56. HashMap<Integer, String> headerMap = new HashMap<>();
  57. int lastCellNum = sheet.getRow(0).getLastCellNum();
  58. for (int i = 0; i < lastCellNum; i++) {
  59. for (int j = firstDataRow-1; j >=0 ; j--) {
  60. HSSFCell cell = sheet.getRow(j).getCell(i);
  61. if(cell != null && !"".equals(cell.getStringCellValue())){
  62. String title = cell.getStringCellValue();
  63. headerMap.put(i,title);
  64. break;
  65. }
  66. }
  67. }
  68. //获取数据
  69. for (int i = firstDataRow; i <= sheet.getLastRowNum(); i++) {
  70. HSSFRow row = sheet.getRow(i);
  71. LinkedHashMap<String, Object> dateMap = new LinkedHashMap<>();
  72. for (int j = 0; j < lastCellNum; j++) {
  73. String title = headerMap.get(j);
  74. String key = headerTitleKey.getStr(title);
  75. if(key != null && !"".equals(key)){
  76. String value = row.getCell(j).getStringCellValue();
  77. dateMap.put(key,value);
  78. }
  79. }
  80. list.add(dateMap);
  81. }
  82. return list;
  83. }
  84. /**
  85. * 构造一个sheet,以及生成复杂表头、表数据
  86. *
  87. * @param sheetName sheet名称
  88. * @param headers 复杂表头json数组对象
  89. * @param dataLists 表数据集合
  90. * @return HExcel
  91. */
  92. public HExcel buildSheet(String sheetName, JSONArray headers, List<Map<String, Object>> dataLists) {
  93. //建立新的sheet对象
  94. HSSFSheet sheet = this.hSSFWorkbook.createSheet(sheetName);//设置表单名
  95. //生成复杂表头
  96. int row = 0;//当前行
  97. int col = 0;//当前列
  98. HashMap<String, Object> hashMap = createHeader(sheet,row,col,headers);
  99. ArrayList<String> headerList = (ArrayList<String>) hashMap.get("keyList");
  100. row = (int) hashMap.get("maxRow");
  101. //取出水平合并区域数据
  102. List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions();
  103. //垂直合并,单元格为空,且不属于水平合并区域
  104. //这里row-1是因为,生成所有表头结束后,maxRow比最大行+1,
  105. for (int i = 0; i < headerList.size(); i++) {
  106. for (int j = 0; j <= row-1; j++) {
  107. boolean flag = true;
  108. //单元格不为空
  109. HSSFCell cell = sheet.getRow(j).getCell(i);
  110. if(cell != null){
  111. continue;
  112. }
  113. //检查合并区域
  114. for (CellRangeAddress cellAddresses : cellRangeAddressList) {
  115. int OldFirstRow = cellAddresses.getFirstRow();
  116. int OldLastRow = cellAddresses.getLastRow();
  117. int OldFirstCol = cellAddresses.getFirstColumn();
  118. int OldLastCol = cellAddresses.getLastColumn();
  119. //与合并区域重叠
  120. if ((OldFirstRow >= j && OldLastRow <= j) && (OldFirstCol >= i && OldLastCol <= i)) {
  121. flag = false;
  122. break;
  123. }
  124. }
  125. //满足条件,将上一个单元格与最后一个单元格合并
  126. if(flag){
  127. mergedCell(sheet,j-1,row-1,i,i);
  128. break;
  129. }
  130. }
  131. }
  132. //开始填充数据
  133. HSSFCellStyle dataStyle = createDataStyle(sheet);
  134. for (Map<String, Object> map : dataLists) {
  135. //创建内容行
  136. HSSFRow dataHSSFRow = sheet.createRow(row);
  137. for (int i = 0; i < headerList.size(); i++) {
  138. String key = headerList.get(i);
  139. Object val = map.get(key);
  140. createCell(dataHSSFRow, i, dataStyle, val == null ? "" : String.valueOf(val));
  141. }
  142. row++;
  143. }
  144. return this;
  145. }
  146. /**
  147. * 保存成File文件
  148. *
  149. * @param path 完整文件路径+文件名
  150. */
  151. public void toFile(String path) {
  152. //try-catch语法糖
  153. try (FileOutputStream out = new FileOutputStream(path);){
  154. this.hSSFWorkbook.write(out);
  155. }catch (IOException e){
  156. throw new RuntimeException("【HExcel】 Workbook对象文件流写入File异常",e);
  157. }
  158. }
  159. /**
  160. * 保存到HttpServletResponse
  161. *
  162. * @param fileName 文件名
  163. * @param response HttpServletResponse对象
  164. */
  165. public void toHttpServletResponse(String fileName, HttpServletResponse response) {
  166. //try-catch语法糖
  167. try (ServletOutputStream outputStream = response.getOutputStream();){
  168. response.setHeader("Accept-Ranges", "bytes");
  169. response.setHeader("Content-disposition", "attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
  170. response.setContentType("application/octet-stream");
  171. this.hSSFWorkbook.write(outputStream);
  172. }catch (Exception e){
  173. throw new RuntimeException("【HExcel】 Workbook对象文件流写入Response异常",e);
  174. }
  175. }
  176. /**
  177. * 关闭Workbook
  178. */
  179. public void close(){
  180. try{
  181. //关闭Workbook
  182. this.hSSFWorkbook.close();
  183. } catch (Exception e) {
  184. throw new RuntimeException("【HExcel】 关闭Workbook异常",e);
  185. }
  186. }
  187. /* 已下设置私有,对外隐藏实现细节 */
  188.  
  189. /**
  190. * Workbook对象
  191. */
  192. private HSSFWorkbook hSSFWorkbook;
  193. /**
  194. * 构造表头
  195. *
  196. * @param sheet sheet
  197. * @param row 当前操作行
  198. * @param col 当前操作列
  199. * @param headers 表头数据
  200. * @return 返回一个map对象,供上级表头获取最新当前操作行、列、key集合
  201. */
  202. private HashMap<String,Object> createHeader(HSSFSheet sheet, int row, int col, JSONArray headers){
  203. //最终返回对象
  204. HashMap<String, Object> hashMap = new HashMap<>();
  205. //key集合
  206. ArrayList<String> keyList = new ArrayList<>();
  207. HSSFWorkbook wb = sheet.getWorkbook();
  208. HSSFRow headerHSSFRow = sheet.getRow(row);
  209. if(headerHSSFRow == null){
  210. headerHSSFRow = sheet.createRow(row);
  211. }
  212. for (Object object : headers) {
  213. JSONObject header = (JSONObject) object;
  214. String title = (String) header.get("title");
  215. String key = (String) header.get("key");
  216. Object width = header.get("width");
  217. Object align = header.get("align");
  218. Object backgroundColor = header.get("background-color");
  219. Object color = header.get("color");
  220. Object children = header.get("children");
  221. //单元格样式
  222. HSSFCellStyle headerStyle = createHeaderStyle(sheet);
  223. //自定义单元格背景色
  224. if(backgroundColor != null){
  225. headerStyle.setFillForegroundColor(Short.parseShort(backgroundColor+""));
  226. }
  227. //自定义单元格字体颜色
  228. if(color != null){
  229. headerStyle.getFont(wb).setColor(Short.parseShort(color+""));
  230. }
  231. //默认单元格宽度,20
  232. sheet.setColumnWidth(col, 20 * 256);
  233. if(width != null){
  234. //自定义单元格宽度
  235. sheet.setColumnWidth(col, (int) width * 256);
  236. }
  237. //默认水平对齐方式(水平居中)
  238. if(align != null){
  239. //自定义水平对齐方式
  240. HorizontalAlignment alignment;
  241. switch (String.valueOf(align).toUpperCase()){
  242. case "LEFT":
  243. alignment = HorizontalAlignment.LEFT;
  244. break;
  245. case "RIGHT":
  246. alignment = HorizontalAlignment.RIGHT;
  247. break;
  248. default:
  249. alignment = HorizontalAlignment.CENTER;
  250. break;
  251. }
  252. headerStyle.setAlignment(alignment);
  253. }
  254. //System.out.println(title + " " + key + " " + row + " " + col);
  255. //生成单元格同时设置内容
  256. createCell(headerHSSFRow, col, headerStyle, title);
  257. //无子级表头
  258. if(children == null){
  259. //保留顺序,方便后面设置数据
  260. keyList.add(key);
  261. //当前列+1
  262. col++;
  263. }
  264. //有子级表头
  265. else{
  266. //递归生成子级表头前,保存父级表头col,用于水平合并
  267. int firstCol = col;
  268. //递归调用
  269. HashMap<String, Object> hashMap1 = createHeader(sheet, row + 1, col, (JSONArray) children);
  270. //获取最新col、key集合
  271. col = (int) hashMap1.get("col");
  272. hashMap.put("maxRow",hashMap1.get("maxRow"));
  273. keyList.addAll((ArrayList<String>) hashMap1.get("keyList"));
  274. //水平合并,这里col-1是因为,生成子级表头结束后,col比最后一个下级表头+1,
  275. if(!(firstCol == col-1)){
  276. mergedCell(sheet,row,row,firstCol,col-1);
  277. }
  278. }
  279. }
  280. //将数据设置到对象中,返回上一层
  281. hashMap.put("maxRow",(hashMap.get("maxRow") != null ? Integer.parseInt(hashMap.get("maxRow")+"") : 0) + 1);//最大行
  282. hashMap.put("row",row);//当前操作行
  283. hashMap.put("col",col);//当前操作列
  284. hashMap.put("keyList",keyList);//key集合
  285.  
  286. return hashMap;
  287. }
  288. /**
  289. * 创建一个单元格
  290. *
  291. * @param hSSFRow 当前行对象
  292. * @param col 当前列
  293. * @param cellStyle 单元格样式对象
  294. * @param text 单元格内容,目前只支持字符串,如需支持更多格式可自行扩展
  295. */
  296. private void createCell(HSSFRow hSSFRow, int col, HSSFCellStyle cellStyle, String text) {
  297. HSSFCell cell = hSSFRow.createCell(col); // 创建单元格
  298. cell.setCellStyle(cellStyle); // 设置单元格样式
  299. cell.setCellValue(text); // 设置值
  300. }
  301. /**
  302. * 构造表头、数据样式
  303. *
  304. * @param sheet sheet
  305. * @return 返回一个单元格样式对象
  306. */
  307. private HSSFCellStyle createHeaderStyle(HSSFSheet sheet){
  308. HSSFWorkbook wb = sheet.getWorkbook();
  309. //表头的样式
  310. HSSFCellStyle headerStyle = wb.createCellStyle();
  311. headerStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
  312. headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
  313. //列名的字体
  314. HSSFFont dataFont = wb.createFont();
  315. dataFont.setFontHeightInPoints((short) 12);
  316. dataFont.setFontName("新宋体");
  317. headerStyle.setFont(dataFont);// 把字体 应用到当前样式
  318. headerStyle.setWrapText(true);//自动换行
  319. //填充样式,前景色、天空蓝
  320. headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  321. headerStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
  322. // 设置边框
  323. headerStyle.setBorderBottom(BorderStyle.THIN);
  324. headerStyle.setBorderLeft(BorderStyle.THIN);
  325. headerStyle.setBorderRight(BorderStyle.THIN);
  326. headerStyle.setBorderTop(BorderStyle.THIN);
  327. return headerStyle;
  328. }
  329. private HSSFCellStyle createDataStyle(HSSFSheet sheet){
  330. HSSFWorkbook wb = sheet.getWorkbook();
  331. //内容的样式
  332. HSSFCellStyle dataStyle = wb.createCellStyle();
  333. dataStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
  334. dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
  335. //内容的字体
  336. HSSFFont font3 = wb.createFont();
  337. font3.setFontHeightInPoints((short) 12);
  338. font3.setFontName("新宋体");
  339. dataStyle.setFont(font3);// 把字体 应用到当前样式
  340. dataStyle.setWrapText(true);//自动换行
  341. //默认无填充
  342. dataStyle.setFillPattern(FillPatternType.NO_FILL);
  343. // 设置边框
  344. dataStyle.setBorderBottom(BorderStyle.THIN);
  345. dataStyle.setBorderLeft(BorderStyle.THIN);
  346. dataStyle.setBorderRight(BorderStyle.THIN);
  347. dataStyle.setBorderTop(BorderStyle.THIN);
  348. return dataStyle;
  349. }
  350. /**
  351. * 合并单元格
  352. *
  353. * @param sheet sheet
  354. * @param firstRow 起始行
  355. * @param lastRow 结束行
  356. * @param firstCol 起始列
  357. * @param lastCol 结束列
  358. */
  359. private void mergedCell(HSSFSheet sheet,int firstRow, int lastRow, int firstCol, int lastCol){
  360. //一个单元格无需合并,例如:[0,0,0,0]
  361. if(firstRow == lastRow && firstCol == lastCol){
  362. return;
  363. }
  364. //先取出合并前的单元格样式
  365. HSSFCellStyle cellStyle = sheet.getRow(firstRow).getCell(firstCol).getCellStyle();
  366. //合并
  367. sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
  368. //解决合并后的边框等样式问题
  369. int first;
  370. int end;
  371. //垂直合并
  372. if(firstCol == lastCol){
  373. first = firstRow;
  374. end = lastRow+1;
  375. for (int i = first; i < end; i++) {
  376. HSSFRow row = sheet.getRow(i);
  377. if(row == null){
  378. row = sheet.createRow(i);
  379. }
  380. HSSFCell cell = row.getCell(firstCol);
  381. if(cell == null){
  382. cell = row.createCell(firstCol);
  383. }
  384. cell.setCellStyle(cellStyle);
  385. }
  386. }
  387. //水平合并
  388. else{
  389. first = firstCol;
  390. end = lastCol+1;
  391. for (int i = first; i < end; i++) {
  392. HSSFRow row = sheet.getRow(firstRow);
  393. if(row == null){
  394. row = sheet.createRow(firstRow);
  395. }
  396. HSSFCell cell = row.getCell(i);
  397. if(cell == null){
  398. cell = row.createCell(i);
  399. }
  400. cell.setCellStyle(cellStyle);
  401. }
  402. }
  403. }
  404. }
View Code

 

   完整main测试

 

  1. public static void main(String[] args) {
  2. //获取HExcel实例
  3. HExcel hExcel1 = HExcel.newInstance();
  4. //数据,一般是查数据库,经过数据处理生成
  5. List<Map<String, Object>> dataList = new ArrayList<>();
  6. HashMap<String, Object> date1 = new HashMap<>();
  7. date1.put("user_name","张三");
  8. date1.put("sex","男");
  9. date1.put("age",20);
  10. date1.put("yu_wen",90);
  11. date1.put("ying_yu",0);
  12. date1.put("shu_xue",85);
  13. date1.put("wu_li",80);
  14. date1.put("total",255);
  15. dataList.add(date1);
  16. HashMap<String, Object> date2 = new HashMap<>();
  17. date2.put("user_name","李四");
  18. date2.put("sex","女");
  19. date2.put("age",18);
  20. date2.put("yu_wen",81);
  21. date2.put("ying_yu",0);
  22. date2.put("shu_xue",90);
  23. date2.put("wu_li",70);
  24. date2.put("total",241);
  25. dataList.add(date2);
  26. //如果是固定表头数据,可以在项目资源文件夹下面新建个json文件夹,用来保存表头json数据,方便读、写
  27. //JSONArray header = JSONUtil.parseArray(ResourceUtil.readUtf8Str("json/header.json"));
  28. //如果是动态表头数据,直接把json字符串写在代码里,方便动态生成表头数据
  29. //表头
  30. String sheetName = "学生成绩单";
  31. JSONArray headers = JSONUtil.parseArray("" +
  32. "[\n" +
  33. " {\n" +
  34. " \"title\":\""+sheetName+"\",\n" +
  35. " \"children\":[\n" +
  36. " {\n" +
  37. " \"title\":\"日期:"+DateUtil.today()+"\",\n" +
  38. " \"align\":\"right\",\n" +
  39. " \"children\":[\n" +
  40. " {\n" +
  41. " \"title\":\"姓名\",\n" +
  42. " \"key\":\"user_name\",\n" +
  43. " },\n" +
  44. " {\n" +
  45. " \"title\":\"语文\",\n" +
  46. " \"key\":\"yu_wen\",\n" +
  47. " },\n" +
  48. " {\n" +
  49. " \"title\":\"数学\",\n" +
  50. " \"key\":\"shu_xue\",\n" +
  51. " },\n" +
  52. " {\n" +
  53. " \"title\":\"总分\",\n" +
  54. " \"key\":\"total\",\n" +
  55. " \"background-color\":17,\n" +
  56. " \"color\":10,\n" +
  57. " \"width\":30,\n" +
  58. " },\n" +
  59. " ]\n" +
  60. " },\n" +
  61. " ]\n" +
  62. " },\n" +
  63. "]" +
  64. "");
  65. //生成sheet
  66. hExcel1.buildSheet(sheetName, headers, dataList);
  67. //表头
  68. JSONArray headers2 = JSONUtil.parseArray("" +
  69. "[\n" +
  70. " {\n" +
  71. " \"title\":\"姓名\",\n" +
  72. " \"key\":\"user_name\",\n" +
  73. " },\n" +
  74. " {\n" +
  75. " \"title\":\"学科成绩\",\n" +
  76. " \"children\":[\n" +
  77. " {\n" +
  78. " \"title\":\"语文\",\n" +
  79. " \"key\":\"yu_wen\",\n" +
  80. " },\n" +
  81. " {\n" +
  82. " \"title\":\"数学\",\n" +
  83. " \"key\":\"shu_xue\",\n" +
  84. " },\n" +
  85. " ]\n" +
  86. " },\n" +
  87. " {\n" +
  88. " \"title\":\"总分\",\n" +
  89. " \"key\":\"total\",\n" +
  90. " \"align\":\"right\",\n" +
  91. " \"background-color\":17,\n" +
  92. " \"color\":10,\n" +
  93. " \"width\":30\n," +
  94. " },\n" +
  95. "]" +
  96. "");
  97. //生成sheet
  98. hExcel1.buildSheet("学生成绩单2", headers2, dataList);
  99. //表头
  100. JSONArray headers3 = JSONUtil.parseArray("" +
  101. "[\n" +
  102. " {\n" +
  103. " \"title\":\"姓名\",\n" +
  104. " \"key\":\"user_name\"\n" +
  105. " },\n" +
  106. " {\n" +
  107. " \"title\":\"性别\",\n" +
  108. " \"key\":\"sex\"\n" +
  109. " },\n" +
  110. " {\n" +
  111. " \"title\":\"年龄\",\n" +
  112. " \"key\":\"age\"\n" +
  113. " },\n" +
  114. " {\n" +
  115. " \"title\":\"学科成绩\",\n" +
  116. " \"children\":[\n" +
  117. " {\n" +
  118. " \"title\":\"语言类\",\n" +
  119. " \"children\":[\n" +
  120. " {\n" +
  121. " \"title\":\"语文\",\n" +
  122. " \"key\":\"yu_wen\",\n" +
  123. " \"background-color\":7,\n" +
  124. " \"color\":5,\n" +
  125. " },\n" +
  126. " ]\n" +
  127. " },\n" +
  128. " {\n" +
  129. " \"title\":\"科学类\",\n" +
  130. " \"background-color\":10,\n" +
  131. " \"children\":[\n" +
  132. " {\n" +
  133. " \"title\":\"数学\",\n" +
  134. " \"key\":\"shu_xue\"\n" +
  135. " },\n" +
  136. " {\n" +
  137. " \"title\":\"物理\",\n" +
  138. " \"key\":\"wu_li\"\n" +
  139. " }\n" +
  140. " ]\n" +
  141. " },\n" +
  142. " ]\n" +
  143. " },\n" +
  144. " {\n" +
  145. " \"title\":\"总分\",\n" +
  146. " \"key\":\"total\",\n" +
  147. " \"align\":\"right\",\n" +
  148. " \"background-color\":17,\n" +
  149. " \"color\":10,\n" +
  150. " \"width\":30\n," +
  151. " },\n" +
  152. "]"+
  153. "");
  154. //生成sheet
  155. hExcel1.buildSheet("学生成绩单3", headers3, dataList);
  156. //表头
  157. JSONArray headers4 = JSONUtil.parseArray("" +
  158. "[\n" +
  159. " {\n" +
  160. " \"title\":\"姓名\",\n" +
  161. " \"key\":\"user_name\"\n" +
  162. " },\n" +
  163. " {\n" +
  164. " \"title\":\"性别\",\n" +
  165. " \"key\":\"sex\"\n" +
  166. " },\n" +
  167. " {\n" +
  168. " \"title\":\"年龄\",\n" +
  169. " \"key\":\"age\"\n" +
  170. " },\n" +
  171. " {\n" +
  172. " \"title\":\"学科成绩\",\n" +
  173. " \"children\":[\n" +
  174. " {\n" +
  175. " \"title\":\"语文\",\n" +
  176. " \"key\":\"yu_wen\",\n" +
  177. " },\n" +
  178. " {\n" +
  179. " \"title\":\"科学类\",\n" +
  180. " \"background-color\":10,\n" +
  181. " \"children\":[\n" +
  182. " {\n" +
  183. " \"title\":\"数学\",\n" +
  184. " \"key\":\"shu_xue\"\n" +
  185. " },\n" +
  186. " {\n" +
  187. " \"title\":\"物理\",\n" +
  188. " \"key\":\"wu_li\"\n" +
  189. " }\n" +
  190. " ]\n" +
  191. " },\n" +
  192. " {\n" +
  193. " \"title\":\"英语\",\n" +
  194. " \"key\":\"ying_yu\",\n" +
  195. " },\n" +
  196. " ]\n" +
  197. " },\n" +
  198. " {\n" +
  199. " \"title\":\"总分\",\n" +
  200. " \"key\":\"total\",\n" +
  201. " \"align\":\"right\",\n" +
  202. " \"background-color\":17,\n" +
  203. " \"color\":10,\n" +
  204. " \"width\":30\n" +
  205. " \n" +
  206. " }\n" +
  207. "]"+
  208. "");
  209. //生成sheet
  210. hExcel1.buildSheet("学生成绩单4", headers4, dataList);
  211. //保存成File文件
  212. hExcel1.toFile("C:\\Users\\XFT User\\Desktop\\学生成绩单复杂表头导出测试.xls");
  213. System.out.println("导出完成!\n");
  214. //关闭对象
  215. hExcel1.close();
  216. //导入
  217. //需要设置title与key的关系
  218. JSONObject headerTitleKey = new JSONObject("" +
  219. "{\n" +
  220. " \"姓名\":\"user_name\",\n" +
  221. " \"语文\":\"yu_wen\",\n" +
  222. " \"数学\":\"shu_xue\",\n" +
  223. " \"总分\":\"total\",\n" +
  224. "}" +
  225. "");
  226. //根据Excel文件,获取HExcel实例
  227. HExcel hExcel2 = HExcel.newInstance(new File("C:\\Users\\XFT User\\Desktop\\学生成绩单复杂表头导出测试.xls"));
  228. //根据title-key关系,读取指定位置的sheet数据
  229. List<Map<String, Object>> sheetList = hExcel2.readSheet(2, 3, headerTitleKey);
  230. //打印sheetList数据
  231. System.out.println("导入完成!");
  232. for (Map<String, Object> map : sheetList) {
  233. System.out.println(map.toString());
  234. }
  235. //关闭对象
  236. hExcel2.close();
  237. }

 

  后记

  一个简单通用的导入导出Excel工具类暂时先记录到这,后续再进行补充

 

原文链接:https://www.cnblogs.com/huanzi-qch/p/17797355.html

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号