前言
日常開發中，Excel的匯出、匯入可以說是最常見的功能模組之一，一個通用的、健壯的的工具類可以節省大量開發時間，讓我們把更多精力放在業務處理上中
之前我們也寫了一個Excel的簡單匯出，甚至可以不依賴poi，還擴充套件了純前端匯出Excel！詳情請戳：《POI匯出Excel 》，遺憾的是這些匯出並不支援複雜表頭
HExcel，一個簡單通用的匯入匯出Excel工具類
1、支援匯出複雜表頭（支援表頭單元格水平合併、垂直合併，支援表頭單元格個性化樣式）
2、支援匯入讀取sheet資料（只需要提供title與key的關係，不需要管列的順序）
程式碼思路都在程式碼註釋裡，感興趣的自己看註釋
PS：依賴 poi 以及 hutool
<!-- POI --> <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> <!-- hutool --> <dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <version>5.7.4</version> </dependency>
先睹為快
表頭目前支援以下屬性，可自行擴充套件：
title 標題 key key width 寬度 align 對齊方式 background-color 背景顏色（POI的IndexedColors） color 字型顏色（POI的IndexedColors） children 子級表頭
匯出
程式碼
//獲取HExcel例項 HExcel hExcel1 = HExcel.newInstance(); //資料，一般是查資料庫，經過資料處理生成 List<Map<String, Object>> dataList = new ArrayList<>(); HashMap<String, Object> date1 = new HashMap<>(); date1.put("user_name","張三"); date1.put("sex","男"); date1.put("age",20); date1.put("yu_wen",90); date1.put("ying_yu",0); date1.put("shu_xue",85); date1.put("wu_li",80); date1.put("total",255); dataList.add(date1); HashMap<String, Object> date2 = new HashMap<>(); date2.put("user_name","李四"); date2.put("sex","女"); date2.put("age",18); date2.put("yu_wen",81); date2.put("ying_yu",0); date2.put("shu_xue",90); date2.put("wu_li",70); date2.put("total",241); dataList.add(date2); //如果是固定表頭資料，可以在專案資原始檔夾下面新建個json資料夾，用來儲存表頭json資料，方便讀、寫 //JSONArray header = JSONUtil.parseArray(ResourceUtil.readUtf8Str("json/header.json")); //如果是動態表頭資料，直接把json字串寫在程式碼裡，方便動態生成表頭資料 //表頭 String sheetName = "學生成績單"; JSONArray headers = JSONUtil.parseArray("" + "[\n" + " {\n" + " \"title\":\""+sheetName+"\",\n" + " \"children\":[\n" + " {\n" + " \"title\":\"日期："+DateUtil.today()+"\",\n" + " \"align\":\"right\",\n" + " \"children\":[\n" + " {\n" + " \"title\":\"姓名\",\n" + " \"key\":\"user_name\",\n" + " },\n" + " {\n" + " \"title\":\"語文\",\n" + " \"key\":\"yu_wen\",\n" + " },\n" + " {\n" + " \"title\":\"數學\",\n" + " \"key\":\"shu_xue\",\n" + " },\n" + " {\n" + " \"title\":\"總分\",\n" + " \"key\":\"total\",\n" + " \"background-color\":17,\n" + " \"color\":10,\n" + " \"width\":30,\n" + " },\n" + " ]\n" + " },\n" + " ]\n" + " },\n" + "]" + ""); //生成sheet hExcel1.buildSheet(sheetName, headers, dataList); //儲存成File檔案 hExcel1.toFile("C:\\Users\\XFT User\\Desktop\\學生成績單複雜表頭匯出測試.xls"); //關閉物件 hExcel1.close();
效果
匯入
需要匯入的Excel檔案
程式碼
//需要設定title與key的關係 JSONObject headerTitleKey = new JSONObject("" + "{\n" + " \"姓名\":\"user_name\",\n" + " \"語文\":\"yu_wen\",\n" + " \"數學\":\"shu_xue\",\n" + " \"總分\":\"total\",\n" + "}" + ""); //根據Excel檔案，獲取HExcel例項 HExcel hExcel2 = HExcel.newInstance(new File("C:\\Users\\XFT User\\Desktop\\學生成績單複雜表頭匯出測試.xls")); //根據title-key關係，讀取指定位置的sheet資料 List<Map<String, Object>> sheetList = hExcel2.readSheet(2, 3, headerTitleKey); //列印sheetList資料 for (Map<String, Object> map : sheetList) { System.out.println(map.toString()); } //關閉物件 hExcel2.close();
效果
{user_name=張三, yu_wen=90, shu_xue=85, total=255}
{user_name=李四, yu_wen=81, shu_xue=90, total=241}
完整程式碼
package cn.huanzi.qch.util; import cn.hutool.json.JSONArray; import cn.hutool.json.JSONObject; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.net.URLEncoder; import java.util.*; /** * HExcel，一個簡單通用的匯入匯出Excel工具類 * 1、支援複雜表頭匯出（支援表頭單元格水平合併、垂直合併，支援表頭單元格個性化樣式） * 2、支援匯入讀取sheet資料（只需要提供title與key的關係，不需要管列的順序） * * PS：依賴 poi 以及 hutool * * 詳情請戳：https://www.cnblogs.com/huanzi-qch/p/17797355.html */ public class HExcel { /** * 獲取一個HExcel例項，並初始化空Workbook物件 */ public static HExcel newInstance(){ HExcel hExcelUtil = new HExcel(); hExcelUtil.hSSFWorkbook = new HSSFWorkbook(); return hExcelUtil; } /** * 獲取一個HExcel例項，並根據excelFile初始化Workbook物件 */ public static HExcel newInstance(File excelFile){ HExcel hExcelUtil = new HExcel(); try { hExcelUtil.hSSFWorkbook = new HSSFWorkbook(new FileInputStream(excelFile)); } catch (IOException e) { throw new RuntimeException("【HExcel】 根據excelFile初始化Workbook物件異常",e); } return hExcelUtil; } /** * 匯入並讀取Excel * * @param sheetIndex 需要讀取的sheet下標 * @param firstDataRow 資料起始行 * @param headerTitleKey title與key的關係json物件 * @return 返回資料集合 */ public List<Map<String, Object>> readSheet(int sheetIndex,int firstDataRow,JSONObject headerTitleKey){ //最終返回的資料集合 ArrayList<Map<String, Object>> list = new ArrayList<>(); //獲取sheet HSSFSheet sheet = this.hSSFWorkbook.getSheetAt(sheetIndex); //獲取title與col的對應關係 HashMap<Integer, String> headerMap = new HashMap<>(); int lastCellNum = sheet.getRow(0).getLastCellNum(); for (int i = 0; i < lastCellNum; i++) { for (int j = firstDataRow-1; j >=0 ; j--) { HSSFCell cell = sheet.getRow(j).getCell(i); if(cell != null && !"".equals(cell.getStringCellValue())){ String title = cell.getStringCellValue(); headerMap.put(i,title); break; } } } //獲取資料 for (int i = firstDataRow; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); LinkedHashMap<String, Object> dateMap = new LinkedHashMap<>(); for (int j = 0; j < lastCellNum; j++) { String title = headerMap.get(j); String key = headerTitleKey.getStr(title); if(key != null && !"".equals(key)){ String value = row.getCell(j).getStringCellValue(); dateMap.put(key,value); } } list.add(dateMap); } return list; } /** * 構造一個sheet，以及生成複雜表頭、表資料 * * @param sheetName sheet名稱 * @param headers 複雜表頭json陣列物件 * @param dataLists 表資料集合 * @return HExcel */ public HExcel buildSheet(String sheetName, JSONArray headers, List<Map<String, Object>> dataLists) { //建立新的sheet物件 HSSFSheet sheet = this.hSSFWorkbook.createSheet(sheetName);//設定表單名 //生成複雜表頭 int row = 0;//當前行 int col = 0;//當前列 HashMap<String, Object> hashMap = createHeader(sheet,row,col,headers); ArrayList<String> headerList = (ArrayList<String>) hashMap.get("keyList"); row = (int) hashMap.get("maxRow"); //取出水平合併區域資料 List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions(); //垂直合併，單元格為空，且不屬於水平合併區域 //這裡row-1是因為，生成所有表頭結束後，maxRow比最大行+1， for (int i = 0; i < headerList.size(); i++) { for (int j = 0; j <= row-1; j++) { boolean flag = true; //單元格不為空 HSSFCell cell = sheet.getRow(j).getCell(i); if(cell != null){ continue; } //檢查合併區域 for (CellRangeAddress cellAddresses : cellRangeAddressList) { int OldFirstRow = cellAddresses.getFirstRow(); int OldLastRow = cellAddresses.getLastRow(); int OldFirstCol = cellAddresses.getFirstColumn(); int OldLastCol = cellAddresses.getLastColumn(); //與合併區域重疊 if ((OldFirstRow >= j && OldLastRow <= j) && (OldFirstCol >= i && OldLastCol <= i)) { flag = false; break; } } //滿足條件，將上一個單元格與最後一個單元格合併 if(flag){ mergedCell(sheet,j-1,row-1,i,i); break; } } } //開始填充資料 HSSFCellStyle dataStyle = createDataStyle(sheet); for (Map<String, Object> map : dataLists) { //建立內容行 HSSFRow dataHSSFRow = sheet.createRow(row); for (int i = 0; i < headerList.size(); i++) { String key = headerList.get(i); Object val = map.get(key); createCell(dataHSSFRow, i, dataStyle, val == null ? "" : String.valueOf(val)); } row++; } return this; } /** * 儲存成File檔案 * * @param path 完整檔案路徑+檔名 */ public void toFile(String path) { //try-catch語法糖 try (FileOutputStream out = new FileOutputStream(path);){ this.hSSFWorkbook.write(out); }catch (IOException e){ throw new RuntimeException("【HExcel】 Workbook物件檔案流寫入File異常",e); } } /** * 儲存到HttpServletResponse * * @param fileName 檔名 * @param response HttpServletResponse物件 */ public void toHttpServletResponse(String fileName, HttpServletResponse response) { //try-catch語法糖 try (ServletOutputStream outputStream = response.getOutputStream();){ response.setHeader("Accept-Ranges", "bytes"); response.setHeader("Content-disposition", "attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\""); response.setContentType("application/octet-stream"); this.hSSFWorkbook.write(outputStream); }catch (Exception e){ throw new RuntimeException("【HExcel】 Workbook物件檔案流寫入Response異常",e); } } /** * 關閉Workbook */ public void close(){ try{ //關閉Workbook this.hSSFWorkbook.close(); } catch (Exception e) { throw new RuntimeException("【HExcel】 關閉Workbook異常",e); } } /* 已下設定私有，對外隱藏實現細節 */ /** * Workbook物件 */ private HSSFWorkbook hSSFWorkbook; /** * 構造表頭 * * @param sheet sheet * @param row 當前操作行 * @param col 當前操作列 * @param headers 表頭資料 * @return 返回一個map物件，供上級表頭獲取最新當前操作行、列、key集合 */ private HashMap<String,Object> createHeader(HSSFSheet sheet, int row, int col, JSONArray headers){ //最終返回物件 HashMap<String, Object> hashMap = new HashMap<>(); //key集合 ArrayList<String> keyList = new ArrayList<>(); HSSFWorkbook wb = sheet.getWorkbook(); HSSFRow headerHSSFRow = sheet.getRow(row); if(headerHSSFRow == null){ headerHSSFRow = sheet.createRow(row); } for (Object object : headers) { JSONObject header = (JSONObject) object; String title = (String) header.get("title"); String key = (String) header.get("key"); Object width = header.get("width"); Object align = header.get("align"); Object backgroundColor = header.get("background-color"); Object color = header.get("color"); Object children = header.get("children"); //單元格樣式 HSSFCellStyle headerStyle = createHeaderStyle(sheet); //自定義單元格背景色 if(backgroundColor != null){ headerStyle.setFillForegroundColor(Short.parseShort(backgroundColor+"")); } //自定義單元格字型顏色 if(color != null){ headerStyle.getFont(wb).setColor(Short.parseShort(color+"")); } //預設單元格寬度，20 sheet.setColumnWidth(col, 20 * 256); if(width != null){ //自定義單元格寬度 sheet.setColumnWidth(col, (int) width * 256); } //預設水平對齊方式（水平居中） if(align != null){ //自定義水平對齊方式 HorizontalAlignment alignment; switch (String.valueOf(align).toUpperCase()){ case "LEFT": alignment = HorizontalAlignment.LEFT; break; case "RIGHT": alignment = HorizontalAlignment.RIGHT; break; default: alignment = HorizontalAlignment.CENTER; break; } headerStyle.setAlignment(alignment); } //System.out.println(title + " " + key + " " + row + " " + col); //生成單元格同時設定內容 createCell(headerHSSFRow, col, headerStyle, title); //無子級表頭 if(children == null){ //保留順序，方便後面設定資料 keyList.add(key); //當前列+1 col++; } //有子級表頭 else{ //遞迴生成子級表頭前，儲存父級表頭col，用於水平合併 int firstCol = col; //遞迴呼叫 HashMap<String, Object> hashMap1 = createHeader(sheet, row + 1, col, (JSONArray) children); //獲取最新col、key集合 col = (int) hashMap1.get("col"); hashMap.put("maxRow",hashMap1.get("maxRow")); keyList.addAll((ArrayList<String>) hashMap1.get("keyList")); //水平合併，這裡col-1是因為，生成子級表頭結束後，col比最後一個下級表頭+1， if(!(firstCol == col-1)){ mergedCell(sheet,row,row,firstCol,col-1); } } } //將資料設定到物件中，返回上一層 hashMap.put("maxRow",(hashMap.get("maxRow") != null ? Integer.parseInt(hashMap.get("maxRow")+"") : 0) + 1);//最大行 hashMap.put("row",row);//當前操作行 hashMap.put("col",col);//當前操作列 hashMap.put("keyList",keyList);//key集合 return hashMap; } /** * 建立一個單元格 * * @param hSSFRow 當前行物件 * @param col 當前列 * @param cellStyle 單元格樣式物件 * @param text 單元格內容，目前只支援字串，如需支援更多格式可自行擴充套件 */ private void createCell(HSSFRow hSSFRow, int col, HSSFCellStyle cellStyle, String text) { HSSFCell cell = hSSFRow.createCell(col); // 建立單元格 cell.setCellStyle(cellStyle); // 設定單元格樣式 cell.setCellValue(text); // 設定值 } /** * 構造表頭、資料樣式 * * @param sheet sheet * @return 返回一個單元格樣式物件 */ private HSSFCellStyle createHeaderStyle(HSSFSheet sheet){ HSSFWorkbook wb = sheet.getWorkbook(); //表頭的樣式 HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中 headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 //列名的字型 HSSFFont dataFont = wb.createFont(); dataFont.setFontHeightInPoints((short) 12); dataFont.setFontName("新宋體"); headerStyle.setFont(dataFont);// 把字型 應用到當前樣式 headerStyle.setWrapText(true);//自動換行 //填充樣式，前景色、天空藍 headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex()); // 設定邊框 headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setBorderRight(BorderStyle.THIN); headerStyle.setBorderTop(BorderStyle.THIN); return headerStyle; } private HSSFCellStyle createDataStyle(HSSFSheet sheet){ HSSFWorkbook wb = sheet.getWorkbook(); //內容的樣式 HSSFCellStyle dataStyle = wb.createCellStyle(); dataStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中 dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 //內容的字型 HSSFFont font3 = wb.createFont(); font3.setFontHeightInPoints((short) 12); font3.setFontName("新宋體"); dataStyle.setFont(font3);// 把字型 應用到當前樣式 dataStyle.setWrapText(true);//自動換行 //預設無填充 dataStyle.setFillPattern(FillPatternType.NO_FILL); // 設定邊框 dataStyle.setBorderBottom(BorderStyle.THIN); dataStyle.setBorderLeft(BorderStyle.THIN); dataStyle.setBorderRight(BorderStyle.THIN); dataStyle.setBorderTop(BorderStyle.THIN); return dataStyle; } /** * 合併單元格 * * @param sheet sheet * @param firstRow 起始行 * @param lastRow 結束行 * @param firstCol 起始列 * @param lastCol 結束列 */ private void mergedCell(HSSFSheet sheet,int firstRow, int lastRow, int firstCol, int lastCol){ //一個單元格無需合併，例如：[0,0,0,0] if(firstRow == lastRow && firstCol == lastCol){ return; } //先取出合併前的單元格樣式 HSSFCellStyle cellStyle = sheet.getRow(firstRow).getCell(firstCol).getCellStyle(); //合併 sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol)); //解決合併後的邊框等樣式問題 int first; int end; //垂直合併 if(firstCol == lastCol){ first = firstRow; end = lastRow+1; for (int i = first; i < end; i++) { HSSFRow row = sheet.getRow(i); if(row == null){ row = sheet.createRow(i); } HSSFCell cell = row.getCell(firstCol); if(cell == null){ cell = row.createCell(firstCol); } cell.setCellStyle(cellStyle); } } //水平合併 else{ first = firstCol; end = lastCol+1; for (int i = first; i < end; i++) { HSSFRow row = sheet.getRow(firstRow); if(row == null){ row = sheet.createRow(firstRow); } HSSFCell cell = row.getCell(i); if(cell == null){ cell = row.createCell(i); } cell.setCellStyle(cellStyle); } } } }
完整main測試
public static void main(String[] args) { //獲取HExcel例項 HExcel hExcel1 = HExcel.newInstance(); //資料，一般是查資料庫，經過資料處理生成 List<Map<String, Object>> dataList = new ArrayList<>(); HashMap<String, Object> date1 = new HashMap<>(); date1.put("user_name","張三"); date1.put("sex","男"); date1.put("age",20); date1.put("yu_wen",90); date1.put("ying_yu",0); date1.put("shu_xue",85); date1.put("wu_li",80); date1.put("total",255); dataList.add(date1); HashMap<String, Object> date2 = new HashMap<>(); date2.put("user_name","李四"); date2.put("sex","女"); date2.put("age",18); date2.put("yu_wen",81); date2.put("ying_yu",0); date2.put("shu_xue",90); date2.put("wu_li",70); date2.put("total",241); dataList.add(date2); //如果是固定表頭資料，可以在專案資原始檔夾下面新建個json資料夾，用來儲存表頭json資料，方便讀、寫 //JSONArray header = JSONUtil.parseArray(ResourceUtil.readUtf8Str("json/header.json")); //如果是動態表頭資料，直接把json字串寫在程式碼裡，方便動態生成表頭資料 //表頭 String sheetName = "學生成績單"; JSONArray headers = JSONUtil.parseArray("" + "[\n" + " {\n" + " \"title\":\""+sheetName+"\",\n" + " \"children\":[\n" + " {\n" + " \"title\":\"日期："+DateUtil.today()+"\",\n" + " \"align\":\"right\",\n" + " \"children\":[\n" + " {\n" + " \"title\":\"姓名\",\n" + " \"key\":\"user_name\",\n" + " },\n" + " {\n" + " \"title\":\"語文\",\n" + " \"key\":\"yu_wen\",\n" + " },\n" + " {\n" + " \"title\":\"數學\",\n" + " \"key\":\"shu_xue\",\n" + " },\n" + " {\n" + " \"title\":\"總分\",\n" + " \"key\":\"total\",\n" + " \"background-color\":17,\n" + " \"color\":10,\n" + " \"width\":30,\n" + " },\n" + " ]\n" + " },\n" + " ]\n" + " },\n" + "]" + ""); //生成sheet hExcel1.buildSheet(sheetName, headers, dataList); //表頭 JSONArray headers2 = JSONUtil.parseArray("" + "[\n" + " {\n" + " \"title\":\"姓名\",\n" + " \"key\":\"user_name\",\n" + " },\n" + " {\n" + " \"title\":\"學科成績\",\n" + " \"children\":[\n" + " {\n" + " \"title\":\"語文\",\n" + " \"key\":\"yu_wen\",\n" + " },\n" + " {\n" + " \"title\":\"數學\",\n" + " \"key\":\"shu_xue\",\n" + " },\n" + " ]\n" + " },\n" + " {\n" + " \"title\":\"總分\",\n" + " \"key\":\"total\",\n" + " \"align\":\"right\",\n" + " \"background-color\":17,\n" + " \"color\":10,\n" + " \"width\":30\n," + " },\n" + "]" + ""); //生成sheet hExcel1.buildSheet("學生成績單2", headers2, dataList); //表頭 JSONArray headers3 = JSONUtil.parseArray("" + "[\n" + " {\n" + " \"title\":\"姓名\",\n" + " \"key\":\"user_name\"\n" + " },\n" + " {\n" + " \"title\":\"性別\",\n" + " \"key\":\"sex\"\n" + " },\n" + " {\n" + " \"title\":\"年齡\",\n" + " \"key\":\"age\"\n" + " },\n" + " {\n" + " \"title\":\"學科成績\",\n" + " \"children\":[\n" + " {\n" + " \"title\":\"語言類\",\n" + " \"children\":[\n" + " {\n" + " \"title\":\"語文\",\n" + " \"key\":\"yu_wen\",\n" + " \"background-color\":7,\n" + " \"color\":5,\n" + " },\n" + " ]\n" + " },\n" + " {\n" + " \"title\":\"科學類\",\n" + " \"background-color\":10,\n" + " \"children\":[\n" + " {\n" + " \"title\":\"數學\",\n" + " \"key\":\"shu_xue\"\n" + " },\n" + " {\n" + " \"title\":\"物理\",\n" + " \"key\":\"wu_li\"\n" + " }\n" + " ]\n" + " },\n" + " ]\n" + " },\n" + " {\n" + " \"title\":\"總分\",\n" + " \"key\":\"total\",\n" + " \"align\":\"right\",\n" + " \"background-color\":17,\n" + " \"color\":10,\n" + " \"width\":30\n," + " },\n" + "]"+ ""); //生成sheet hExcel1.buildSheet("學生成績單3", headers3, dataList); //表頭 JSONArray headers4 = JSONUtil.parseArray("" + "[\n" + " {\n" + " \"title\":\"姓名\",\n" + " \"key\":\"user_name\"\n" + " },\n" + " {\n" + " \"title\":\"性別\",\n" + " \"key\":\"sex\"\n" + " },\n" + " {\n" + " \"title\":\"年齡\",\n" + " \"key\":\"age\"\n" + " },\n" + " {\n" + " \"title\":\"學科成績\",\n" + " \"children\":[\n" + " {\n" + " \"title\":\"語文\",\n" + " \"key\":\"yu_wen\",\n" + " },\n" + " {\n" + " \"title\":\"科學類\",\n" + " \"background-color\":10,\n" + " \"children\":[\n" + " {\n" + " \"title\":\"數學\",\n" + " \"key\":\"shu_xue\"\n" + " },\n" + " {\n" + " \"title\":\"物理\",\n" + " \"key\":\"wu_li\"\n" + " }\n" + " ]\n" + " },\n" + " {\n" + " \"title\":\"英語\",\n" + " \"key\":\"ying_yu\",\n" + " },\n" + " ]\n" + " },\n" + " {\n" + " \"title\":\"總分\",\n" + " \"key\":\"total\",\n" + " \"align\":\"right\",\n" + " \"background-color\":17,\n" + " \"color\":10,\n" + " \"width\":30\n" + " \n" + " }\n" + "]"+ ""); //生成sheet hExcel1.buildSheet("學生成績單4", headers4, dataList); //儲存成File檔案 hExcel1.toFile("C:\\Users\\XFT User\\Desktop\\學生成績單複雜表頭匯出測試.xls"); System.out.println("匯出完成！\n"); //關閉物件 hExcel1.close(); //匯入 //需要設定title與key的關係 JSONObject headerTitleKey = new JSONObject("" + "{\n" + " \"姓名\":\"user_name\",\n" + " \"語文\":\"yu_wen\",\n" + " \"數學\":\"shu_xue\",\n" + " \"總分\":\"total\",\n" + "}" + ""); //根據Excel檔案，獲取HExcel例項 HExcel hExcel2 = HExcel.newInstance(new File("C:\\Users\\XFT User\\Desktop\\學生成績單複雜表頭匯出測試.xls")); //根據title-key關係，讀取指定位置的sheet資料 List<Map<String, Object>> sheetList = hExcel2.readSheet(2, 3, headerTitleKey); //列印sheetList資料 System.out.println("匯入完成！"); for (Map<String, Object> map : sheetList) { System.out.println(map.toString()); } //關閉物件 hExcel2.close(); }
後記
一個簡單通用的匯入匯出Excel工具類暫時先記錄到這，後續再進行補充