HExcel，一个简单通用的导入导出Excel工具类
前言
日常开发中，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" +
"]" +
"");//生成sheethExcel1.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<>();//获取sheetHSSFSheet 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{//关闭Workbookthis.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+""));
}//默认单元格宽度，20sheet.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);//当前列+1col++;
}//有子级表头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);
}
}
}
}
View Code
完整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" +
"]" +
"");//生成sheethExcel1.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" +
"]"+
"");//生成sheethExcel1.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" +
"]"+
"");//生成sheethExcel1.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
工具类暂时先记录到这，后续再进行补充