Springboot+Poi实现Excel的导入导出
https://blog.csdn.net/typ1805/article/details/83279532
目录
代码:https://gitee.com/typ1805/springboot-master
POI操作Excel
一、poi简介
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
1、HSSF:HSSF 是Horrible SpreadSheet Format的缩写,通过HSSF,你可以用纯Java代码来读取、写入、修改Excel文件。HSSF 为读取操作提供了两类API:usermodel和eventusermodel,即“用户模型”和“事件-用户模型”。
2、POI EXCEL文档结构类
- HSSFWorkbook excel文档对象
- HSSFSheet excel的sheet HSSFRow excel的行
- HSSFCell excel的单元格 HSSFFont excel字体
- HSSFName 名称 HSSFDataFormat 日期格式
- HSSFHeader sheet头
- HSSFFooter sheet尾
- HSSFCellStyle cell样式
- HSSFDateUtil 日期
- HSSFPrintSetup 打印
- HSSFErrorConstants 错误信息表
3、导入Excel常用的方法:
- POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/test.xls"));
- HSSFWorkbook wb = new HSSFWorkbook(fs); //得到Excel工作簿对象
- HSSFSheet sheet = wb.getSheetAt(0); //得到Excel工作表对象
- HSSFRow row = sheet.getRow(i); //得到Excel工作表的行
- HSSFCell cell = row.getCell((short) j); //得到Excel工作表指定行的单元格
- cellStyle = cell.getCellStyle(); //得到单元格样式
4、导出Excel常用的方法:
- HSSFWorkbook wb = new HSSFWorkbook(); //创建Excel工作簿对象
- HSSFSheet sheet = wb.createSheet("new sheet"); //创建Excel工作表对象
- HSSFRow row = sheet.createRow((short)0); //创建Excel工作表的行
- cellStyle = wb.createCellStyle(); //创建单元格样式
- row.createCell((short)0).setCellStyle(cellStyle); //创建Excel工作表指定行的单元格
- row.createCell((short)0).setCellValue(1); //设置Excel工作表的值
二、springboot整合poi
主要是springboot+myBatis+poi+mysql的简单应用,从数据库查询到结果集导出excel到本地,从本地中的excel文件导入到数据库表中。
1、添加依赖
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>RELEASE</version>
- </dependency>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-ooxml</artifactId>
- <version>RELEASE</version>
- </dependency>
2、创建一个ExcelUtil类,这里的实现比较简单
- package com.example.demo.utils;
-
- import com.example.demo.entity.ExcelData;
- import com.example.demo.entity.User;
- import lombok.extern.slf4j.Slf4j;
- import org.apache.poi.hssf.usermodel.*;
- import org.apache.poi.ss.usermodel.*;
-
- import javax.servlet.http.HttpServletResponse;
- import java.io.BufferedOutputStream;
- import java.io.FileInputStream;
- import java.io.InputStream;
- import java.io.OutputStream;
- import java.util.ArrayList;
- import java.util.List;
-
- import static org.apache.poi.ss.usermodel.CellType.*;
-
- /**
- * 路径:com.example.demo.utils
- * 类名:
- * 功能:导入导出
- * 备注:
- * 创建人:typ
- * 创建时间:2018/10/19 11:21
- * 修改人:
- * 修改备注:
- * 修改时间:
- */
- @Slf4j
- public class ExcelUtil {
-
- /**
- * 方法名:exportExcel
- * 功能:导出Excel
- * 描述:
- * 创建人:typ
- * 创建时间:2018/10/19 16:00
- * 修改人:
- * 修改描述:
- * 修改时间:
- */
- public static void exportExcel(HttpServletResponse response, ExcelData data) {
- log.info("导出解析开始,fileName:{}",data.getFileName());
- try {
- //实例化HSSFWorkbook
- HSSFWorkbook workbook = new HSSFWorkbook();
- //创建一个Excel表单,参数为sheet的名字
- HSSFSheet sheet = workbook.createSheet("sheet");
- //设置表头
- setTitle(workbook, sheet, data.getHead());
- //设置单元格并赋值
- setData(sheet, data.getData());
- //设置浏览器下载
- setBrowser(response, workbook, data.getFileName());
- log.info("导出解析成功!");
- } catch (Exception e) {
- log.info("导出解析失败!");
- e.printStackTrace();
- }
- }
-
- /**
- * 方法名:setTitle
- * 功能:设置表头
- * 描述:
- * 创建人:typ
- * 创建时间:2018/10/19 10:20
- * 修改人:
- * 修改描述:
- * 修改时间:
- */
- private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
- try {
- HSSFRow row = sheet.createRow(0);
- //设置列宽,setColumnWidth的第二个参数要乘以256,这个参数的单位是1/256个字符宽度
- for (int i = 0; i <= str.length; i++) {
- sheet.setColumnWidth(i, 15 * 256);
- }
- //设置为居中加粗,格式化时间格式
- HSSFCellStyle style = workbook.createCellStyle();
- HSSFFont font = workbook.createFont();
- font.setBold(true);
- style.setFont(font);
- style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
- //创建表头名称
- HSSFCell cell;
- for (int j = 0; j < str.length; j++) {
- cell = row.createCell(j);
- cell.setCellValue(str[j]);
- cell.setCellStyle(style);
- }
- } catch (Exception e) {
- log.info("导出时设置表头失败!");
- e.printStackTrace();
- }
- }
-
- /**
- * 方法名:setData
- * 功能:表格赋值
- * 描述:
- * 创建人:typ
- * 创建时间:2018/10/19 16:11
- * 修改人:
- * 修改描述:
- * 修改时间:
- */
- private static void setData(HSSFSheet sheet, List<String[]> data) {
- try{
- int rowNum = 1;
- for (int i = 0; i < data.size(); i++) {
- HSSFRow row = sheet.createRow(rowNum);
- for (int j = 0; j < data.get(i).length; j++) {
- row.createCell(j).setCellValue(data.get(i)[j]);
- }
- rowNum++;
- }
- log.info("表格赋值成功!");
- }catch (Exception e){
- log.info("表格赋值失败!");
- e.printStackTrace();
- }
- }
-
- /**
- * 方法名:setBrowser
- * 功能:使用浏览器下载
- * 描述:
- * 创建人:typ
- * 创建时间:2018/10/19 16:20
- * 修改人:
- * 修改描述:
- * 修改时间:
- */
- private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
- try {
- //清空response
- response.reset();
- //设置response的Header
- response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
- OutputStream os = new BufferedOutputStream(response.getOutputStream());
- response.setContentType("application/vnd.ms-excel;charset=gb2312");
- //将excel写入到输出流中
- workbook.write(os);
- os.flush();
- os.close();
- log.info("设置浏览器下载成功!");
- } catch (Exception e) {
- log.info("设置浏览器下载失败!");
- e.printStackTrace();
- }
-
- }
-
-
- /**
- * 方法名:importExcel
- * 功能:导入
- * 描述:
- * 创建人:typ
- * 创建时间:2018/10/19 11:45
- * 修改人:
- * 修改描述:
- * 修改时间:
- */
- public static List<Object[]> importExcel(String fileName) {
- log.info("导入解析开始,fileName:{}",fileName);
- try {
- List<Object[]> list = new ArrayList<>();
- InputStream inputStream = new FileInputStream(fileName);
- Workbook workbook = WorkbookFactory.create(inputStream);
- Sheet sheet = workbook.getSheetAt(0);
- //获取sheet的行数
- int rows = sheet.getPhysicalNumberOfRows();
- for (int i = 0; i < rows; i++) {
- //过滤表头行
- if (i == 0) {
- continue;
- }
- //获取当前行的数据
- Row row = sheet.getRow(i);
- Object[] objects = new Object[row.getPhysicalNumberOfCells()];
- int index = 0;
- for (Cell cell : row) {
- if (cell.getCellType().equals(NUMERIC)) {
- objects[index] = (int) cell.getNumericCellValue();
- }
- if (cell.getCellType().equals(STRING)) {
- objects[index] = cell.getStringCellValue();
- }
- if (cell.getCellType().equals(BOOLEAN)) {
- objects[index] = cell.getBooleanCellValue();
- }
- if (cell.getCellType().equals(ERROR)) {
- objects[index] = cell.getErrorCellValue();
- }
- index++;
- }
- list.add(objects);
- }
- log.info("导入文件解析成功!");
- return list;
- }catch (Exception e){
- log.info("导入文件解析失败!");
- e.printStackTrace();
- }
- return null;
- }
-
- //测试导入
- public static void main(String[] args) {
- try {
- String fileName = "f:/test.xlsx";
- List<Object[]> list = importExcel(fileName);
- for (int i = 0; i < list.size(); i++) {
- User user = new User();
- user.setId((Integer) list.get(i)[0]);
- user.setUsername((String) list.get(i)[1]);
- user.setPassword((String) list.get(i)[2]);
- user.setEnable((Integer) list.get(i)[3]);
- System.out.println(user.toString());
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
-
- }
具体业务代码在此不赘述,完整代码:https://download.csdn.net/download/typ1805/10737617
三、poi API简述
1、设置sheet名称和单元格内容
- workbook.setSheetName(1, "工作表",HSSFCell.ENCODING_UTF_16);
- cell.setEncoding((short) 1);
- cell.setCellValue("单元格内容");
2、取得sheet的数目
workbook.getNumberOfSheets()
3、根据index取得sheet对象
HSSFSheet sheet = wb.getSheetAt(0);
4、取得有效的行数
int rowcount = sheet.getLastRowNum();
row.getLastCellNum();
6、单元格值类型读写
- cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置单元格为STRING类型
- cell.getNumericCellValue();//读取为数值类型的单元格内容
7、设置列宽、行高
- sheet.setColumnWidth((short)column,(short)width);
- row.setHeight((short)height);
8、添加区域,合并单元格
- // 合并从第rowFrom行columnFrom列
- Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo(short)columnTo);
- // 到rowTo行columnTo的区域
- sheet.addMergedRegion(region);
- // 得到所有区域
- sheet.getNumMergedRegions()
9、保存Excel文件
- FileOutputStream fileOut = new FileOutputStream(path);
- wb.write(fileOut);
10、根据单元格不同属性返回字符串数值
- public String getCellStringValue(HSSFCell cell) {
- String cellValue = "";
- switch (cell.getCellType()) {
- case HSSFCell.CELL_TYPE_STRING://字符串类型
- cellValue = cell.getStringCellValue();
- if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
- cellValue=" ";
- break;
- case HSSFCell.CELL_TYPE_NUMERIC: //数值类型
- cellValue = String.valueOf(cell.getNumericCellValue());
- break;
- case HSSFCell.CELL_TYPE_FORMULA: //公式
- cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
- cellValue = String.valueOf(cell.getNumericCellValue());
- break;
- case HSSFCell.CELL_TYPE_BLANK:
- cellValue=" ";
- break;
- case HSSFCell.CELL_TYPE_BOOLEAN:
- break;
- case HSSFCell.CELL_TYPE_ERROR:
- break;
- default:
- break;
- }
- return cellValue;
- }
11、常用单元格边框格式
- HSSFCellStyle style = wb.createCellStyle();
- style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框
- style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框
- style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
- style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
12、设置字体和内容位置
- HSSFFont f = wb.createFont();
- f.setFontHeightInPoints((short) 11);//字号
- f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
- style.setFont(f);
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
- style.setRotation(short rotation);//单元格内容的旋转的角度
- HSSFDataFormat df = wb.createDataFormat();
- style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式
- cell.setCellFormula(string);//给单元格设公式
- style.setRotation(short rotation);//单元格内容的旋转的角度
13、插入图片
- //先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray
- ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
- BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));
- ImageIO.write(bufferImg,"jpg",byteArrayOut);
- //读进一个excel模版
- FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");
- fs = new POIFSFileSystem(fos);
- //创建一个工作薄
- HSSFWorkbook wb = new HSSFWorkbook(fs);
- HSSFSheet sheet = wb.getSheetAt(0);
- HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
- HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);
- patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
14、调整工作表位置
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet("format sheet");
- HSSFPrintSetup ps = sheet.getPrintSetup();
- sheet.setAutobreaks(true);
- ps.setFitHeight((short)1);
- ps.setFitWidth((short)1);
15、设置打印区域
- HSSFSheet sheet = wordbook.createSheet("Sheet1");
- wordbook.setPrintArea(0, "$A$1:$C$2");
16、标注脚注
- HSSFSheet sheet = wordbook.createSheet("format sheet");
- HSSFFooter footer = sheet.getFooter()
- footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
17、在工作单中清空行数据,调整行位置
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet("row sheet");
- // Create various cells and rows for spreadsheet.
- // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
- sheet.shiftRows(5, 10, -5);
18、选中指定的工作表
- HSSFSheet sheet = wb.createSheet("row sheet");
- heet.setSelected(true);
19、工作表的放大缩小
- HSSFSheet sheet1 = wb.createSheet("new sheet");
- sheet1.setZoom(1,2); // 50 percent magnification
20、头注和脚注
- HSSFSheet sheet = wb.createSheet("new sheet");
- HSSFHeader header = sheet.getHeader();
- header.setCenter("Center Header");
- header.setLeft("Left Header");
- header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
- HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
21、自定义颜色
- HSSFCellStyle style = wb.createCellStyle();
- style.setFillForegroundColor(HSSFColor.LIME.index);
- style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- HSSFFont font = wb.createFont();
- font.setColor(HSSFColor.RED.index);
- style.setFont(font);
- cell.setCellStyle(style);
22、填充和颜色设置
- HSSFCellStyle style = wb.createCellStyle();
- style.setFillBackgroundColor(HSSFColor.AQUA.index);
- style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
- HSSFCell cell = row.createCell((short) 1);
- cell.setCellValue("X");
- style = wb.createCellStyle();
- style.setFillForegroundColor(HSSFColor.ORANGE.index);
- style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- cell.setCellStyle(style);
23、强行刷新单元格公式
- HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);
- private static void updateFormula(Workbook wb,Sheet s,int row){
- Row r=s.getRow(row);
- Cell c=null;
- FormulaEcaluator eval=null;
- if(wb instanceof HSSFWorkbook)
- eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);
- else if(wb instanceof XSSFWorkbook)
- eval=new XSSFFormulaEvaluator((XSSFWorkbook) wb);
- for(int i=r.getFirstCellNum();i
- c=r.getCell(i);
- if(c.getCellType()==Cell.CELL_TYPE_FORMULA)
- eval.evaluateFormulaCell(c);
- }
- }
24、设置不显示excel网格线
sheet.setDisplayGridlines(false);//其中sheet是Sheet对象
25、设置excel单元格中的内容换行
cellStyle.setWrapText(true);
注意:其中cellStyle是WorkBook创建的CellStyle对象,然后将cellStyle设置到要换行的Cell对象,最后在要换行的对象(一般为字符串)加入"/r/n"。例如:
topTile.append("/r/n" +"cellContent");
26、单元格的合并
sheet.addMergedRegion(new CellRangeAddress(0, 4, 0, 2));// 本示例为合并4行2列
27、设置页眉和页脚的页数
- HSSFHeader header = sheet.getHeader();
- header.setCenter("Center Header");
- header.setLeft("Left Header");
- header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
- HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
- HSSFFooter footer = (HSSFFooter )sheet.getFooter()
- footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
28、使得一个Sheet适合一页
sheet.setAutobreaks(true);
29、设置放大属性(Zoom被明确为一个分数,例如下面的75%使用3作为分子,4作为分母)
sheet.setZoom(3,4);
30、设置打印
- HSSFPrintSetup print = (HSSFPrintSetup) sheet.getPrintSetup();
- print.setLandscape(true);//设置横向打印
- print.setScale((short) 70);//设置打印缩放70%
- print.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);//设置为A4纸张
- print.setLeftToRight(true);//設置打印顺序先行后列,默认为先列行
- print.setFitHeight((short) 10);设置缩放调整为10页高
- print.setFitWidth((short) 10);设置缩放调整为宽高
-
- sheet.setAutobreaks(false);
- if (i != 0 && i % 30 == 0) {
- sheet.setRowBreak(i);//設置每30行分頁打印
- }
31、反复的行和列(设置打印标题)
- HSSFWorkbook wb = new HSSFWorkbook();
- wb.setRepeatingRowsAndColumns(0, 0, 12, 1, 6);//设置1到12列,行1到6每一页重复打印
32、调整单元格宽度
- sheet.setAutobreaks(true);
- sheet.setColumnWidth((short)i,colsWidth[i]); //设定单元格长度
- sheet.autoSizeColumn((short) i);//自动根据长度调整单元格长度
使用poi对excel的操作到此结束。。。
EasyPOI操作Excel
注:使用easypoi API导出excel相对于poi的API比较简单。
一、导出Excel
1、添加依赖
- <dependency>
- <groupId>cn.afterturn</groupId>
- <artifactId>easypoi-spring-boot-starter</artifactId>
- <version>3.3.0</version>
- </dependency>
2、映射实体注解
- /**
- * 路径:com.example.demo.entity
- * 类名:
- * 功能:使用easypoi导出excel
- * 备注:
- * 创建人:typ
- * 创建时间:2019/5/19 20:54
- * 修改人:
- * 修改备注:
- * 修改时间:
- */
- @Data
- public class BrandInfo implements Serializable{
-
- @Excel(name = "brandGuid", width = 25,orderNum = "0")
- private String brandGuid;
-
- @Excel(name = "brandName", width = 25,orderNum = "0")
- private String brandName;
-
- @Excel(name = "ytFullcode", width = 25,orderNum = "0")
- private String ytFullcode;
-
- @Excel(name = "formatGuid", width = 25,orderNum = "0")
- private String formatGuid;
-
- @Excel(name = "flag", width = 25,orderNum = "0")
- private String flag;
-
- @Excel(name = "customerid", width = 25,orderNum = "0")
- private String customerid;
-
- @Excel(name = "createDatetime",width = 20,exportFormat = "yyyy-MM-dd HH:mm:ss", orderNum = "1")
- private String createDatetime;
-
- @Excel(name = "updateDatetime",width = 20,exportFormat = "yyyy-MM-dd HH:mm:ss", orderNum = "1")
- private String updateDatetime;
-
- @Excel(name = "source", width = 25,orderNum = "0")
- private Integer source;
-
- }
3、查询数据
3.1、service接口:
- public interface ExcelService {
-
- List<BrandInfo> list();
- }
3.2、service实现类:
- @Service
- public class ExcelServiceImlp implements ExcelService {
-
- @Autowired
- private ExcelMapper excelMapper;
-
- @Override
- public List<BrandInfo> list() {
- return excelMapper.list();
- }
- }
3.3、mapper接口
- @Mapper
- public interface ExcelMapper {
-
- List<BrandInfo> list();
- }
3.4、mapper对应的xml
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
-
- <mapper namespace="com.example.demo.mapper.ExcelMapper">
-
- <select id="list" resultType="com.example.demo.entity.BrandInfo">
- select brand_guid,brand_name,yt_fullcode,format_guid,flag,customerid,create_datetime,update_datetime,source from brand_info
- </select>
-
- </mapper>
4、导出Controller
- package com.example.demo.controller;
-
- import cn.afterturn.easypoi.excel.ExcelExportUtil;
- import cn.afterturn.easypoi.excel.entity.ExportParams;
- import com.example.demo.entity.BrandInfo;
- import com.example.demo.service.ExcelService;
- import org.apache.poi.ss.usermodel.Workbook;
- import org.slf4j.Logger;
- import org.slf4j.LoggerFactory;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.web.bind.annotation.GetMapping;
- import org.springframework.web.bind.annotation.RequestMapping;
- import org.springframework.web.bind.annotation.RestController;
-
- import javax.servlet.http.HttpServletResponse;
- import java.io.BufferedOutputStream;
- import java.io.IOException;
- import java.io.OutputStream;
- import java.util.List;
-
- /**
- * 路径:com.example.demo.controller
- * 类名:EasyPoiExcelController
- * 功能:使用easypoi注解进行导入导出
- * 备注:
- * 创建人:typ
- * 创建时间:2019/5/19 20:00
- * 修改人:
- * 修改备注:
- * 修改时间:
- */
- @RestController
- @RequestMapping("/easypoi")
- public class EasyPoiExcelController {
-
- private static final Logger log = LoggerFactory.getLogger(EasyPoiExcelController.class);
-
- @Autowired
- public ExcelService excelService;
-
- /**
- * 方法名:exportExcel
- * 功能:导出
- * 描述:
- * 创建人:typ
- * 创建时间:2019/5/19 20:03
- * 修改人:
- * 修改描述:
- * 修改时间:
- */
- @GetMapping("/exportExcel")
- public void exportExcel(HttpServletResponse response){
- log.info("请求 exportExcel start ......");
-
- // 获取用户信息
- List<BrandInfo> list = excelService.list();
-
- try {
- // 设置响应输出的头类型及下载文件的默认名称
- String fileName = new String("demo信息表.xls".getBytes("utf-8"), "ISO-8859-1");
- response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
- response.setContentType("application/vnd.ms-excel;charset=gb2312");
-
- //导出
- Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), BrandInfo.class, list);
- workbook.write(response.getOutputStream());
- log.info("请求 exportExcel end ......");
- } catch (IOException e) {
- log.info("请求 exportExcel 异常:{}", e.getMessage());
- }
- }
- }
5、浏览器请求 http://localhost:8081/easypoi/exportExcel,导出结果如下:
二、导入Excel
这里只是简单的测试了一下,没有把数据往数据库存储:
- public static void main(String[] args) {
- try{
- // 没有使用实体类注解的形式,这里用的Map
- List<Map<String,Object>> list = ExcelImportUtil.importExcel(
- new File(PoiPublicUtil.getWebRootPath("check.xls")),
- Map.class,
- new ImportParams()
- );
- // 数据打印
- for (Map<String, Object> map : list) {
- System.out.println(JSON.toJSON(map));
- }
- } catch (Exception e){
- log.info(" Excel 导入异常:{}", e.getMessage());
- }
- }
Excel原数据如图:
导入结果如下:
- DEBUG 2019-05-31 14:54:06,466 cn.afterturn.easypoi.excel.imports.ExcelImportServer: Excel import start ,class is interface java.util.Map
- DEBUG 2019-05-31 14:54:06,811 cn.afterturn.easypoi.excel.imports.ExcelImportServer: start to read excel by is ,startTime is 1559285646811
- DEBUG 2019-05-31 14:54:06,812 cn.afterturn.easypoi.excel.imports.ExcelImportServer: end to read excel by is ,endTime is 1559285646811
- DEBUG 2019-05-31 14:54:06,837 cn.afterturn.easypoi.excel.imports.ExcelImportServer: end to read excel list by pos ,endTime is 1559285646837
- {"name":"zhangsan","password":123,"id":1,"sex":"男"}
- {"name":"lisi","password":123456,"id":2,"sex":"男"}
- {"name":"wangwu","password":10002,"id":3,"sex":"女"}
- {"name":"zhaoliu","password":1587,"id":4,"sex":"男"}
- {"name":"maqi","password":45987,"id":5,"sex":"女"}
- {"name":"houjiu","password":23143,"id":6,"sex":"男"}
- {"name":"jishi","password":4543645,"id":7,"sex":"男"}
解决不同浏览器导出excel中文名称乱码问题
在Windows上以上的导出都是正常,而在Max上导出时,文件名称包含中文时会乱码,只需添加一下代码就可以完美的解决名称乱码问题。
- // 各浏览器基本都支持ISO编码
- String userAgent = request.getHeader("User-Agent").toUpperCase();
- if(userAgent.contains("TRIDENT") || userAgent.contains("EDGE")){
- fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
- } else if(userAgent.contains("MSIE")) {
- fileName = new String(fileName.getBytes(), "ISO-8859-1");
- } else {
- fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
- }
- response.addHeader("Content-Disposition", String.format("attachment; filename=\"%s\"", fileName));
使用POI将HTML Table导出Excel
1、POI依赖
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>3.8</version>
- </dependency>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-ooxml</artifactId>
- <version>3.8</version>
- </dependency>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-ooxml-schemas</artifactId>
- <version>3.8</version>
- </dependency>
2、导出工具类
2.1、使用HSSFWorkbook
- package com.demo.utils;
-
- import com.demo.model.CrossRangeCellMeta;
- import org.apache.commons.lang3.StringUtils;
- import org.apache.commons.lang3.math.NumberUtils;
- import org.apache.poi.hssf.usermodel.*;
- import org.apache.poi.hssf.util.HSSFColor;
- import org.apache.poi.ss.util.CellRangeAddress;
- import org.dom4j.Document;
- import org.dom4j.DocumentException;
- import org.dom4j.DocumentHelper;
- import org.dom4j.Element;
-
- import java.io.File;
- import java.io.FileOutputStream;
- import java.util.ArrayList;
- import java.util.List;
-
-
- /**
- * 类名:Html2Excel.java
- * 路径:com.demo.utils.Html2Excel.java
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:00
- * 功能:将html table转成excel
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- public class Html2Excel {
-
- /**
- * 方法名:table2Excel
- * 功能:html表格转excel
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:00
- * 入参:html字符串:<table> ... </table>
- * 出参:excel
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- public static HSSFWorkbook table2Excel(String tableHtml) {
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet();
- HSSFCellStyle style = wb.createCellStyle();
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- List<CrossRangeCellMeta> crossRowEleMetaLs = new ArrayList<>();
- int rowIndex = 0;
- try {
- Document data = DocumentHelper.parseText(tableHtml);
- // 生成表头
- Element thead = data.getRootElement().element("thead");
- HSSFCellStyle titleStyle = getTitleStyle(wb);
- if (thead != null) {
- List<Element> trLs = thead.elements("tr");
- for (Element trEle : trLs) {
- HSSFRow row = sheet.createRow(rowIndex);
- List<Element> thLs = trEle.elements("th");
- makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);
- row.setHeightInPoints(17);
- rowIndex++;
- }
- }
- // 生成表体
- Element tbody = data.getRootElement().element("tbody");
- if (tbody != null) {
- HSSFCellStyle contentStyle = getContentStyle(wb);
- List<Element> trLs = tbody.elements("tr");
- for (Element trEle : trLs) {
- HSSFRow row = sheet.createRow(rowIndex);
- List<Element> thLs = trEle.elements("th");
- int cellIndex = makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);
- List<Element> tdLs = trEle.elements("td");
- makeRowCell(tdLs, rowIndex, row, cellIndex, contentStyle, crossRowEleMetaLs);
- row.setHeightInPoints(18);
- rowIndex++;
- }
- }
- // 合并表头
- for (CrossRangeCellMeta crcm : crossRowEleMetaLs) {
- sheet.addMergedRegion(new CellRangeAddress(crcm.getFirstRow(), crcm.getLastRow(), crcm.getFirstCol(), crcm.getLastCol()));
- }
- } catch (DocumentException e) {
- e.printStackTrace();
- }
- //自动调整列宽
- for (int i = 0; i < 15; i++) {
- sheet.autoSizeColumn((short) i);
- }
- return wb;
- }
-
- /**
- * 方法名:makeRowCell
- * 功能:生产行内容
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:01
- * 入参:
- * tdLs: th或者td集合;
- * rowIndex: 行号;
- * row: POI行对象;
- * startCellIndex;
- * cellStyle: 样式;
- * crossRowEleMetaLs: 跨行元数据集合
- * 出参:
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- private static int makeRowCell(List<Element> tdLs, int rowIndex, HSSFRow row, int startCellIndex, HSSFCellStyle cellStyle, List<CrossRangeCellMeta> crossRowEleMetaLs) {
- int i = startCellIndex;
- for (int eleIndex = 0; eleIndex < tdLs.size(); i++, eleIndex++) {
- int captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
- while (captureCellSize > 0) {
- // 当前行跨列处理(补单元格)
- for (int j = 0; j < captureCellSize; j++) {
- row.createCell(i);
- i++;
- }
- captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
- }
- Element thEle = tdLs.get(eleIndex);
- String val = thEle.getTextTrim();
- if (StringUtils.isBlank(val)) {
- Element e = thEle.element("a");
- if (e != null) {
- val = e.getTextTrim();
- }
- }
- HSSFCell c = row.createCell(i);
- if (NumberUtils.isNumber(val)) {
- c.setCellValue(Double.parseDouble(val));
- c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
- } else {
- c.setCellValue(val);
- }
- c.setCellStyle(cellStyle);
- int rowSpan = NumberUtils.toInt(thEle.attributeValue("rowspan"), 1);
- int colSpan = NumberUtils.toInt(thEle.attributeValue("colspan"), 1);
- // 存在跨行或跨列
- if (rowSpan > 1 || colSpan > 1) {
- crossRowEleMetaLs.add(new CrossRangeCellMeta(rowIndex, i, rowSpan, colSpan));
- }
- // 当前行跨列处理(补单元格)
- if (colSpan > 1) {
- for (int j = 1; j < colSpan; j++) {
- i++;
- row.createCell(i);
- }
- }
- }
- return i;
- }
-
- /**
- * 方法名:getCaptureCellSize
- * 功能:获得因rowSpan占据的单元格
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:03
- * 入参:
- * rowIndex:行号
- * colIndex:列号
- * crossRowEleMetaLs:跨行列元数据
- * 出参:当前行在某列需要占据单元格
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- private static int getCaptureCellSize(int rowIndex, int colIndex, List<CrossRangeCellMeta> crossRowEleMetaLs) {
- int captureCellSize = 0;
- for (CrossRangeCellMeta crossRangeCellMeta : crossRowEleMetaLs) {
- if (crossRangeCellMeta.getFirstRow() < rowIndex && crossRangeCellMeta.getLastRow() >= rowIndex) {
- if (crossRangeCellMeta.getFirstCol() <= colIndex && crossRangeCellMeta.getLastCol() >= colIndex) {
- captureCellSize = crossRangeCellMeta.getLastCol() - colIndex + 1;
- }
- }
- }
- return captureCellSize;
- }
-
- /**
- * 方法名:getTitleStyle
- * 功能:获得标题样式
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:04
- * 入参:workbook
- * 出参:
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- private static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
- short titlebackgroundcolor = HSSFColor.GREY_25_PERCENT.index;
- short fontSize = 12;
- String fontName = "宋体";
- HSSFCellStyle style = workbook.createCellStyle();
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- style.setBorderBottom((short) 1);
- style.setBorderTop((short) 1);
- style.setBorderLeft((short) 1);
- style.setBorderRight((short) 1);
- style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- // 背景色
- style.setFillForegroundColor(titlebackgroundcolor);
- HSSFFont font = workbook.createFont();
- font.setFontName(fontName);
- font.setFontHeightInPoints(fontSize);
- font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- style.setFont(font);
- return style;
- }
-
- /**
- * 方法名:getContentStyle
- * 功能:获得内容样式
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:05
- * 入参:HSSFWorkbook
- * 出参:
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- private static HSSFCellStyle getContentStyle(HSSFWorkbook wb) {
- short fontSize = 12;
- String fontName = "宋体";
- HSSFCellStyle style = wb.createCellStyle();
- style.setBorderBottom((short) 1);
- style.setBorderTop((short) 1);
- style.setBorderLeft((short) 1);
- style.setBorderRight((short) 1);
- HSSFFont font = wb.createFont();
- font.setFontName(fontName);
- font.setFontHeightInPoints(fontSize);
- style.setFont(font);
- return style;
- }
-
- }
异常:使用Html2Excel.java导出时列超过256时会报错:Invalid column index (256). Allowable column range for BIFF8 is (0..255) or ('A'..'IV')。
原因:使用HSSFWorkbook最多只能创建256列,超过就会报上述的错误。
解决方法:使用XSSFWorkbook创建,最多可以创建16384列。修改Html2Excel工具类为XHtml2Excel.java,如下:
2.2、使用XSSFWorkbook
- package com.demo.utils;
-
- import com.demo.model.CrossRangeCellMeta;
- import org.apache.commons.lang3.StringUtils;
- import org.apache.commons.lang3.math.NumberUtils;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- import org.apache.poi.hssf.usermodel.HSSFFont;
- import org.apache.poi.hssf.util.HSSFColor;
- import org.apache.poi.ss.util.CellRangeAddress;
- import org.apache.poi.xssf.usermodel.*;
- import org.dom4j.Document;
- import org.dom4j.DocumentException;
- import org.dom4j.DocumentHelper;
- import org.dom4j.Element;
-
- import java.io.File;
- import java.io.FileOutputStream;
- import java.util.ArrayList;
- import java.util.List;
-
-
- /**
- * 类名:XHtml2Excel.java
- * 路径:com.demo.utils.XHtml2Excel.java
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:00
- * 功能:将html table转成excel
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- public class XHtml2Excel {
-
- /**
- * 方法名:table2Excel
- * 功能:html表格转excel
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:00
- * 入参:html字符串:<table> ... </table>
- * 出参:excel
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- public static XSSFWorkbook table2Excel(String tableHtml) {
- XSSFWorkbook wb = new XSSFWorkbook();
- XSSFSheet sheet = wb.createSheet();
- XSSFCellStyle style = wb.createCellStyle();
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- List<CrossRangeCellMeta> crossRowEleMetaLs = new ArrayList<>();
- int rowIndex = 0;
- try {
- Document data = DocumentHelper.parseText(tableHtml);
- // 生成表头
- Element thead = data.getRootElement().element("thead");
- XSSFCellStyle titleStyle = getTitleStyle(wb);
- if (thead != null) {
- List<Element> trLs = thead.elements("tr");
- for (Element trEle : trLs) {
- XSSFRow row = sheet.createRow(rowIndex);
- List<Element> thLs = trEle.elements("th");
- makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);
- row.setHeightInPoints(17);
- rowIndex++;
- }
- }
- // 生成表体
- Element tbody = data.getRootElement().element("tbody");
- if (tbody != null) {
- XSSFCellStyle contentStyle = getContentStyle(wb);
- List<Element> trLs = tbody.elements("tr");
- for (Element trEle : trLs) {
- XSSFRow row = sheet.createRow(rowIndex);
- List<Element> thLs = trEle.elements("th");
- int cellIndex = makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);
- List<Element> tdLs = trEle.elements("td");
- makeRowCell(tdLs, rowIndex, row, cellIndex, contentStyle, crossRowEleMetaLs);
- row.setHeightInPoints(18);
- rowIndex++;
- }
- }
- // 合并表头
- for (CrossRangeCellMeta crcm : crossRowEleMetaLs) {
- sheet.addMergedRegion(new CellRangeAddress(crcm.getFirstRow(), crcm.getLastRow(), crcm.getFirstCol(), crcm.getLastCol()));
- }
- } catch (DocumentException e) {
- e.printStackTrace();
- }
- //自动调整列宽
- for (int i = 0; i < 15; i++) {
- sheet.autoSizeColumn((short) i);
- }
- return wb;
- }
-
- /**
- * 方法名:makeRowCell
- * 功能:生产行内容
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:01
- * 入参:
- * tdLs: th或者td集合;
- * rowIndex: 行号;
- * row: POI行对象;
- * startCellIndex;
- * cellStyle: 样式;
- * crossRowEleMetaLs: 跨行元数据集合
- * 出参:
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- private static int makeRowCell(List<Element> tdLs, int rowIndex, XSSFRow row, int startCellIndex, XSSFCellStyle cellStyle, List<CrossRangeCellMeta> crossRowEleMetaLs) {
- int i = startCellIndex;
- for (int eleIndex = 0; eleIndex < tdLs.size(); i++, eleIndex++) {
- int captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
- while (captureCellSize > 0) {
- // 当前行跨列处理(补单元格)
- for (int j = 0; j < captureCellSize; j++) {
- row.createCell(i);
- i++;
- }
- captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
- }
- Element thEle = tdLs.get(eleIndex);
- String val = thEle.getTextTrim();
- if (StringUtils.isBlank(val)) {
- Element e = thEle.element("a");
- if (e != null) {
- val = e.getTextTrim();
- }
- }
- XSSFCell c = row.createCell(i);
- if (NumberUtils.isNumber(val)) {
- c.setCellValue(Double.parseDouble(val));
- c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
- } else {
- c.setCellValue(val);
- }
- c.setCellStyle(cellStyle);
- int rowSpan = NumberUtils.toInt(thEle.attributeValue("rowspan"), 1);
- int colSpan = NumberUtils.toInt(thEle.attributeValue("colspan"), 1);
- // 存在跨行或跨列
- if (rowSpan > 1 || colSpan > 1) {
- crossRowEleMetaLs.add(new CrossRangeCellMeta(rowIndex, i, rowSpan, colSpan));
- }
- // 当前行跨列处理(补单元格)
- if (colSpan > 1) {
- for (int j = 1; j < colSpan; j++) {
- i++;
- row.createCell(i);
- }
- }
- }
- return i;
- }
-
- /**
- * 方法名:getCaptureCellSize
- * 功能:获得因rowSpan占据的单元格
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:03
- * 入参:
- * rowIndex:行号
- * colIndex:列号
- * crossRowEleMetaLs:跨行列元数据
- * 出参:当前行在某列需要占据单元格
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- private static int getCaptureCellSize(int rowIndex, int colIndex, List<CrossRangeCellMeta> crossRowEleMetaLs) {
- int captureCellSize = 0;
- for (CrossRangeCellMeta crossRangeCellMeta : crossRowEleMetaLs) {
- if (crossRangeCellMeta.getFirstRow() < rowIndex && crossRangeCellMeta.getLastRow() >= rowIndex) {
- if (crossRangeCellMeta.getFirstCol() <= colIndex && crossRangeCellMeta.getLastCol() >= colIndex) {
- captureCellSize = crossRangeCellMeta.getLastCol() - colIndex + 1;
- }
- }
- }
- return captureCellSize;
- }
-
- /**
- * 方法名:getTitleStyle
- * 功能:获得标题样式
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:04
- * 入参:workbook
- * 出参:
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- private static XSSFCellStyle getTitleStyle(XSSFWorkbook workbook) {
- short titlebackgroundcolor = HSSFColor.GREY_25_PERCENT.index;
- short fontSize = 12;
- String fontName = "宋体";
- XSSFCellStyle style = workbook.createCellStyle();
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- style.setBorderBottom((short) 1);
- style.setBorderTop((short) 1);
- style.setBorderLeft((short) 1);
- style.setBorderRight((short) 1);
- style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- // 背景色
- style.setFillForegroundColor(titlebackgroundcolor);
- XSSFFont font = workbook.createFont();
- font.setFontName(fontName);
- font.setFontHeightInPoints(fontSize);
- font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- style.setFont(font);
- return style;
- }
-
- /**
- * 方法名:getContentStyle
- * 功能:获得内容样式
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:05
- * 入参:HSSFWorkbook
- * 出参:
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- private static XSSFCellStyle getContentStyle(XSSFWorkbook wb) {
- short fontSize = 12;
- String fontName = "宋体";
- XSSFCellStyle style = wb.createCellStyle();
- style.setBorderBottom((short) 1);
- style.setBorderTop((short) 1);
- style.setBorderLeft((short) 1);
- style.setBorderRight((short) 1);
- XSSFFont font = wb.createFont();
- font.setFontName(fontName);
- font.setFontHeightInPoints(fontSize);
- style.setFont(font);
- return style;
- }
-
- }
3、跨行列实体类
- package com.demo.model;
-
- /**
- * 类名:CrossRangeCellMeta.java
- * 路径:com.winner.model.CrossRangeCellMeta.java
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:00
- * 功能:跨行元素元数据
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- public class CrossRangeCellMeta {
-
- public CrossRangeCellMeta(int firstRowIndex, int firstColIndex, int rowSpan, int colSpan) {
- super();
- this.firstRowIndex = firstRowIndex;
- this.firstColIndex = firstColIndex;
- this.rowSpan = rowSpan;
- this.colSpan = colSpan;
- }
-
- private int firstRowIndex;
- private int firstColIndex;
- // 跨越行数
- private int rowSpan;
- // 跨越列数
- private int colSpan;
-
- public int getFirstRow() {
- return firstRowIndex;
- }
-
- public int getLastRow() {
- return firstRowIndex + rowSpan - 1;
- }
-
- public int getFirstCol() {
- return firstColIndex;
- }
-
- public int getLastCol() {
- return firstColIndex + colSpan - 1;
- }
-
- public int getColSpan() {
- return colSpan;
- }
- }
4、controller调用
- /**
- * 类名:Html2ExcelController.java
- * 路径:com.demo.controller.Html2ExcelController.java
- * 创建人:tanyp
- * 创建时间:2019/9/19 10:00
- * 功能:HTML table导出excel
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- @Controller
- public class Html2ExcelController {
- private static final Logger log = LoggerFactory.getLogger(Html2ExcelController.class);
-
- /**
- * 方法名:exportExcel
- * 功能:导出
- * 创建人:tanyp
- * 创建时间:2019/9/19 11:07
- * 入参:dataTable
- * 出参:
- * 修改人:
- * 修改时间:
- * 修改备注:
- */
- @RequestMapping("exportExcel")
- public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
- try {
- String dataTable = request.getParameter("dataTable");
- String fileName = "测试"+System.currentTimeMillis()+".xls";
- // 各浏览器基本都支持ISO编码
- String userAgent = request.getHeader("User-Agent").toUpperCase();
- if (userAgent.contains("TRIDENT") || userAgent.contains("EDGE")) {
- fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
- } else if (userAgent.contains("MSIE")) {
- fileName = new String(fileName.getBytes(), "ISO-8859-1");
- } else {
- fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
- }
-
- response.addHeader("Content-Disposition", String.format("attachment; filename=\"%s\"", fileName));
- OutputStream os = new BufferedOutputStream(response.getOutputStream());
- response.setContentType("application/vnd.ms-excel;charset=gb2312");
- //将excel写入到输出流中
- HSSFWorkbook workbook = Html2Excel.table2Excel(dataTable);
- // XSSFWorkbook workbook = XHtml2Excel.table2Excel(dataTable);
- workbook.write(os);
- os.flush();
- os.close();
- } catch (Exception e) {
- e.printStackTrace();
- log.error("请求 exportExcel异常:{}", e.getMessage());
- }
- }
- }
备注:使用两个不同工具类时导包不同(HSSFWorkbook或XSSFWorkbook) 。
5、HTML代码
- <!DOCTYPE html>
- <html>
- <head>
- <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
- <script type="text/javascript" src="js/jquery.js"></script>
- </head>
- <body>
- <div onclick="download()">导出</div>
- <div id="DateTable" style="overflow-x:scroll">
- <table id="myTable" class="gridtable w2000"><thead><tr><th rowspan="2" style="min-width: 100px; max-width: 100px;width: 100px;">名称</th><th rowspan="2" style="min-width: 80px; max-width: 80px;width: 80px;">客流总量(人次)</th><th rowspan="2" style="min-width: 80px; max-width: 80px;width: 80px;">编码</th><th rowspan="2" style="min-width: 80px; max-width: 80px;width: 80px;">单客流总量(人次)</th><th colspan="3">时段客流(人次)</th></tr><tr><th style="min-width: 80px; max-width: 80px;width: 80px;">01:05</th><th style="min-width: 80px; max-width: 80px;width: 80px;">01:10</th><th style="min-width: 80px; max-width: 80px;width: 80px;">01:15</th></tr></thead><tbody id="pointBasedDateTbody"><tr><td rowspan="2">购物一号门</td><td rowspan="2">5006</td><td>C001</td><td>3006</td><td>0</td><td>0</td><td>0</td></tr><tr><td>C002</td><td>2000</td><td>0</td><td>0</td><td>0</td></tr><tr><td rowspan="2">购物二号门</td><td rowspan="2">2796</td><td>C001</td><td>1542</td><td>0</td><td>0</td><td>0</td></tr><tr><td>C002</td><td>1254</td><td>0</td><td>0</td><td>0</td></tr><tr><td rowspan="1">购物三号门</td><td rowspan="1">1654</td><td>C005</td><td>1654</td><td>0</td><td>0</td><td>0</td></tr><tr><td rowspan="1">购物四号门</td><td rowspan="1">54365</td><td>C001</td><td>54365</td><td>0</td><td>0</td><td>0</td></tr></tbody></table>
- </div>
- <form action="exportExcel" id="exportExcle" method="POST">
- <input type="hidden" name="dataTable" id="dataTable">
- </form>
- </body>
- <script>
- function download() {
- var html = $("#tables").html();
- $("#dataTable").val(html);
- $("#exportExcle").submit();
- };
- </script>
- </html>
6、效果图
欢迎关注