- <!-- 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();
- //需要设置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();

- 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
- 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();
- }