前言
日常开发中，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);
}
}
}
}
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" +
"]" +
"");
//生成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工具类暂时先记录到这，后续再进行补充
标签：sheet,title,HExcel,Excel,表头,导入,key,put From： https://www.cnblogs.com/huanzi-qch/p/17797355.html