java导出excel文件_springboot中使用poi导出excel文件

1.POI简介

Jakarta POI 是一套用于访问微软格式文档的Java API.

组件HWPF用于操作Word的;

组件HSSF用于操作Excel格式文件.

2.常用组件

HSSFWorkbook -- excel的文档对象

HSSFSheet -- excel的表单

HSSFRow -- excel的行

HSSFCell -- excel的格子单元

HSSFHeader -- sheet头

HSSFFooter -- sheet尾(只有打印的时候才能看到效果)

HSSFDataFormat --日期格式

HSSFCellStyle -- cell样式

HSSFFont -- excel字体

HSSFColor --颜色

HSSFDateUtil --日期

HSSFPrintSetup -- 打印

HSSFErrorConstants -- 错误信息表

合并单元格,构造参数依次表示起始行,截止行,起始列,截止列

eg:sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));

设置单元格样式时,先创建样式,再指定到单元格。

样式可指定对齐方式、背景填充方式及颜色、上下左右边框样式及颜色

设置单元格的填充方式，以及前景颜色和背景颜色时注意：

a.如果需要前景颜色或背景颜色，一定要指定填充方式，两者顺序无所谓；

b.如果同时存在前景颜色和背景颜色，前景颜色的设置要写在前面；

c.前景颜色不是字体颜色。

3.结构说明

Excel

workbook

sheet

4.操作步骤

a、用HSSFWorkbook打开或者创建Excel文件对象

b、用HSSFWorkbook对象返回或者创建Sheet对象

c、用Sheet对象返回行对象，用行对象得到Cell对象

d、对Cell对象读写

5.实例

第一种方法固定导出字段导出excel;

第二种方法用配置的方式将导出字段存储数库中导出excel,可重用;

pom.xml

net.sf.json-lib

json-lib

2.4

jdk15

cn.afterturn

easypoi-base

3.0.3

cn.afterturn

easypoi-web

3.0.3

cn.afterturn

easypoi-annotation

3.0.3

org.apache.poi

poi-ooxml

3.9

commons-fileupload

1.3.1

commons-io

2.4

IExportExcleService.java 接口

packagecom.wulss.jakartapoi.hssf;importjava.util.List;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;public interfaceIExportExcelService {/*** 第一种直接导出excle

*@paramreq

*@paramresp

*@paramlist 要导出的数据*/

public void exportExcelWithSimple(HttpServletRequest req,HttpServletResponse resp,Listlist);/*** 第二种根据exportKey查询出要导出的字段,并匹配list每个类中字段来导出excel,只需维护数据库,即可实现该方法的重用

*@paramexportKey 数据库中存储的导出英文名

*@paramfileName 文件名

*@paramlist 要导出的数据

*@paramreq

*@paramresp*/

public void exportExcelWithDispose(String exportKey,String fileName,List>list,HttpServletRequest req,HttpServletResponse resp);

}

ExportExcleServiceImpl.java 实现类

packagecom.wulss.jakartapoi.hssf;importjava.text.SimpleDateFormat;importjava.util.List;importjava.util.UUID;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.util.CellRangeAddress;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Service;importnet.sf.json.JSONArray;importnet.sf.json.JSONObject;

@Servicepublic class ExportExcelServiceImpl extends ExportExcelBaseService implementsIExportExcelService{

@AutowiredprivateExportMapper exportMapper;

@Overridepublic void exportExcelWithSimple(HttpServletRequest req,HttpServletResponse resp,Listlist){

String fileName= "个人消费明细表" +UUID.randomUUID().toString();try{//工作空间

HSSFWorkbook workbook = newHSSFWorkbook();//第1张工作表

HSSFSheet sheet1 = workbook.createSheet("个人消费明细");

sheet1.setDefaultRowHeightInPoints(20);//行高

sheet1.setDefaultColumnWidth(20);//列宽//行标题

HSSFRow titleRow = sheet1.createRow(0);

titleRow.createCell(0).setCellValue("个人消费明细表");

sheet1.addMergedRegion(new CellRangeAddress(0,0,0,4));合并单元格//行表头

HSSFRow headRow = sheet1.createRow(sheet1.getLastRowNum() + 1);

headRow.createCell(0).setCellValue("序号");

headRow.createCell(headRow.getLastCellNum()).setCellValue("用户姓名");

headRow.createCell(headRow.getLastCellNum()).setCellValue("消费金额");

headRow.createCell(headRow.getLastCellNum()).setCellValue("消费时间");

headRow.createCell(headRow.getLastCellNum()).setCellValue("消费项目");//行表头单元格设置样式

for(int h = 0; h < headRow.getLastCellNum() ; h ++) {

headRow.getCell(h).setCellStyle(super.getCellStyle(workbook));

}//行数据体

int index = 1;

HSSFRow bodyRow= null;for(UserConsumeDetailRecord bean:list) {

bodyRow= sheet1.createRow(sheet1.getLastRowNum() + 1);

bodyRow.createCell(0).setCellValue(index ++);

bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(bean.getUserName());

bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(bean.getConsumeAmount());

bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(bean.getConsumeDate()));//.split("\\.")[0]

bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(bean.getConsumeTitle());

}//输出

super.outExcelStream(resp, workbook, fileName);//将生成的excel文件写到磁盘//FileOutputStream fos = new FileOutputStream(fileName + ".xls");//workbook.write(fos);//fos.close();//从磁盘删除删除文件//super.deleteFileDir(fileName + ".xls");

}catch(Exception e){

e.printStackTrace();

}

@Overridepublic void exportExcelWithDispose(String exportKey,String fileName,List>list,HttpServletRequest req,HttpServletResponse resp){//查询一表级联获取多表集合

List fieldBeanList =exportMapper.getExportByExportKey(exportKey).getFieldBeanList();try{//工作空间

HSSFWorkbook workbook = newHSSFWorkbook();//第1张工作表

HSSFSheet sheet1 = workbook.createSheet("个人消费明细");

sheet1.setDefaultRowHeightInPoints(20);//行高

sheet1.setDefaultColumnWidth(20);//列宽//行表头

HSSFRow headRow = sheet1.createRow(0);

headRow.createCell(0).setCellValue("序号");

headRow.getCell(0).setCellStyle(super.getCellStyle(workbook));//创建行表头单元格并设置样式

for(ExportFieldBean fieldBean:fieldBeanList) {

headRow.createCell(headRow.getLastCellNum()).setCellValue(fieldBean.getExportName());//赋值

headRow.getCell(headRow.getLastCellNum()).setCellStyle(super.getCellStyle(workbook));//样式

}//创建行数据体

int index = 1;

HSSFRow bodyRow= null;

JSONArray jsonArray= JSONArray.fromObject(list);//--赋值(先转json, 再赋值, 通用性高)

for(Object obj:jsonArray) {

bodyRow= sheet1.createRow(sheet1.getLastRowNum() + 1);

bodyRow.createCell(0).setCellValue(index ++);for(ExportFieldBean fieldBean:fieldBeanList) {

bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(((JSONObject)obj).get(fieldBean.getExportCode())+ "");//赋值//bodyRow.getCell(bodyRow.getLastCellNum()).setCellStyle(super.getCellStyle(workbook));//样式

}

}//输出

super.outExcelStream(resp, workbook, fileName);

}catch(Exception e){

e.printStackTrace();

}

ExportExcelBaseService.java 基础类

packagecom.wulss.jakartapoi.hssf;importjava.io.File;importjava.io.IOException;importjava.io.OutputStream;importjavax.servlet.http.HttpServletResponse;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.usermodel.HSSFDataFormat;importorg.apache.poi.hssf.usermodel.HSSFFont;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.hssf.util.HSSFColor;importorg.apache.poi.ss.usermodel.Workbook;importorg.springframework.stereotype.Component;

@Componentpublic classExportExcelBaseService {/*** 获取设置好的样式

*@paramworkbook 工作空间

*@return

*/

publicHSSFCellStyle getCellStyle(HSSFWorkbook workbook) {

HSSFCellStyle cellStyle=workbook.createCellStyle();

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//单元格-垂直居中

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//单元格-水平居中

cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS);//背景色-方块填充

cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);//前背景色-天蓝

cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);//后背景色-浅黄

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//底边框样式-倾斜断点

cellStyle.setBottomBorderColor(HSSFColor.DARK_RED.index);//底边框颜色-暗红

cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));//日期显示格式//headRowCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));

cellStyle.setFont(this.getFont(workbook));//设置字体

returncellStyle;

}/*** 获取设置好的字体

*@paramworkbook 工作空间

*@return

*/

publicHSSFFont getFont(HSSFWorkbook workbook) {

HSSFFont fontStyle=workbook.createFont();

fontStyle.setFontName("宋体");//名称-宋体

fontStyle.setFontHeightInPoints((short)13);//高度-13

fontStyle.setColor(HSSFColor.WHITE.index);//颜色-白色

fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗

fontStyle.setItalic(true);//斜体

fontStyle.setUnderline(HSSFFont.U_SINGLE);//下划线

returnfontStyle;

}/*** 通过流的方式输出excle到页面

*@paramresponse 响应

*@paramworkbook 工作空间

*@paramfileName 文件名*/

public voidoutExcelStream(HttpServletResponse response, Workbook workbook, String fileName){

OutputStream os= null;try{

os=response.getOutputStream();

response.setContentType("application/x-download");

response.setCharacterEncoding("UTF-8");

response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls");

workbook.write(os);

os.flush();

}catch(Exception e) {

e.printStackTrace();

}finally{if(os!=null){try{

os.close();

}catch(IOException e) {

e.printStackTrace();

}

}//删除单个文件夹

public voiddeleteFileDir(String fileName) {

File file= newFile(fileName);

DeleteAll(file);

}public voidDeleteAll(File dir) {if(dir.isFile()) {

dir.delete();return;

}else{

File[] files=dir.listFiles();for(File file : files) {

DeleteAll(file);

}

dir.delete();

}

ExportExcelController.java

packagecom.wulss.jakartapoi.hssf;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;importjava.util.UUID;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.web.bind.annotation.RequestMapping;importorg.springframework.web.bind.annotation.RequestMethod;importorg.springframework.web.bind.annotation.RestController;

@RestController

@RequestMapping("/exportExlce")public classExportExcelController {

@Autowired

IExportExcelService iExportExcelService;

@RequestMapping(value="/withSimple",method=RequestMethod.GET)publicString withSimple(HttpServletRequest req,HttpServletResponse resp) {

List list = new ArrayList<>();

UserConsumeDetailRecord record= null;for(int i=0;i<10;i++) {

record= newUserConsumeDetailRecord();

record.setUserName("奥雷里亚诺");

record.setConsumeAmount(6.66);

record.setConsumeDate(newDate());

record.setConsumeTitle("喝酒吃肉");

list.add(record);

}

iExportExcelService.exportExcelWithSimple(req, resp, list);return "success";

}

@RequestMapping(value="/withDispose",method=RequestMethod.GET)publicString WithDispose(HttpServletRequest req,HttpServletResponse resp) {

List list = new ArrayList<>();

UserConsumeDetailRecord record= null;for(int i=0;i<10;i++) {

record= newUserConsumeDetailRecord();

record.setUserName("奥雷里亚诺");

record.setConsumeAmount(6.66);

record.setConsumeDate(newDate());

record.setConsumeTitle("喝酒吃肉");

list.add(record);

}

iExportExcelService.exportExcelWithDispose("consume_detail", "个人消费明细表" +UUID.randomUUID().toString(), list, req, resp);return "success";

}

第二种方法涉及到的建表语句

CREATE TABLE`export` (

`id`int(32) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

`export_code`varchar(255) DEFAULT NULL COMMENT '导出主题英文名',

`export_name`varchar(255) DEFAULT NULL COMMENT '导出主题中文名'

PRIMARY KEY(`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='导出主题表';CREATE TABLE`export_field` (

`id`int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

`export_id`int(11) unsigned DEFAULT NULL COMMENT '导出主表ID',

`field_code`varchar(55) DEFAULT NULL COMMENT '字段英文名',

`field_name`varchar(64) DEFAULT NULL COMMENT '字段中文名',

`sort`int(11) unsigned DEFAULT '1' COMMENT '排序字段'

PRIMARY KEY(`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='导出字段表';

数据截图

表一

表二

涉及到的 UserConsumeDetailRecord.java 、ExportBean.java、ExportFieldBean.java、ExportMapper.java等，mybatis映射文件就不贴出来了

/***

* @Descript TODO (用户消费实体)

*@authorAdministrator

* @date 2019年5月24日

**/

public classUserConsumeDetailRecord {private intid;private intuserId;privateString userName;privateString userTel;privateString consumeTitle;privateDate consumeDate;privateDouble consumeAmount;public intgetId() {returnid;

}public void setId(intid) {this.id =id;

}public intgetUserId() {returnuserId;

}public void setUserId(intuserId) {this.userId =userId;

}publicString getUserName() {returnuserName;

}public voidsetUserName(String userName) {this.userName =userName;

}publicString getUserTel() {returnuserTel;

}public voidsetUserTel(String userTel) {this.userTel =userTel;

}publicString getConsumeTitle() {returnconsumeTitle;

}public voidsetConsumeTitle(String consumeTitle) {this.consumeTitle =consumeTitle;

}publicDate getConsumeDate() {returnconsumeDate;

}public voidsetConsumeDate(Date consumeDate) {this.consumeDate =consumeDate;

}publicDouble getConsumeAmount() {returnconsumeAmount;

}public voidsetConsumeAmount(Double consumeAmount) {this.consumeAmount =consumeAmount;

}

UserConsumeDetailRecord.java

/***

* @Descript TODO (导出主题表)

*@authorAdministrator

* @date 2019年5月27日

**/

public classExportBean {privateInteger id;privateString exportCode;privateString exportName;private ListfieldBeanList;publicInteger getId() {returnid;

}public voidsetId(Integer id) {this.id =id;

}publicString getExportCode() {returnexportCode;

}public voidsetExportCode(String exportCode) {this.exportCode =exportCode;

}publicString getExportName() {returnexportName;

}public voidsetExportName(String exportName) {this.exportName =exportName;

}public ListgetFieldBeanList() {returnfieldBeanList;

}public void setFieldBeanList(ListfieldBeanList) {this.fieldBeanList =fieldBeanList;

}

ExportBean.java

/***

* @Descript TODO (导出字段表)

*@authorAdministrator

* @date 2019年5月27日

**/

public classExportFieldBean {privateInteger id;privateInteger exportId;privateString exportCode;privateString exportName;privateInteger sort;privateExportBean exportBean;publicInteger getId() {returnid;

}public voidsetId(Integer id) {this.id =id;

}publicInteger getExportId() {returnexportId;

}public voidsetExportId(Integer exportId) {this.exportId =exportId;

}publicString getExportCode() {returnexportCode;

}public voidsetExportCode(String exportCode) {this.exportCode =exportCode;

}publicString getExportName() {returnexportName;

}public voidsetExportName(String exportName) {this.exportName =exportName;

}publicInteger getSort() {returnsort;

}public voidsetSort(Integer sort) {this.sort =sort;

}publicExportBean getExportBean() {returnexportBean;

}public voidsetExportBean(ExportBean exportBean) {this.exportBean =exportBean;

}