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)
workbook <-- 多 工作表(sheet)
sheet <-- 多 行(row) + 多列(cell)
4.操作步骤
a、用HSSFWorkbook打开或者创建Excel文件对象
b、用HSSFWorkbook对象返回或者创建Sheet对象
c、用Sheet对象返回行对象,用行对象得到Cell对象
d、对Cell对象读写
5.实例
第一种方法 固定导出字段导出excel;
第二种方法 用配置的方式将导出字段存储数库中导出excel,可重用;
pom.xml
<!-- json转换工具 -->
<dependency>
<groupId>net.sf.json-lib</groupId>
<artifactId>json-lib</artifactId>
<version>2.4</version>
<classifier>jdk15</classifier>
</dependency>
<!-- easypoi 导入导出插件-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
<!-- POI,excel导入需要的 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
IExportExcleService.java 接口
package com.wulss.jakartapoi.hssf;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public interface IExportExcelService {
/**
* 第一种 直接导出excle
* @param req
* @param resp
* @param list 要导出的数据
*/
public void exportExcelWithSimple(HttpServletRequest req,HttpServletResponse resp,List<UserConsumeDetailRecord> list);
/**
* 第二种 根据exportKey查询出要导出的字段,并匹配list每个类中字段来导出excel,只需维护数据库,即可实现该方法的重用
* @param exportKey 数据库中存储的导出英文名
* @param fileName 文件名
* @param list 要导出的数据
* @param req
* @param resp
*/
public void exportExcelWithDispose(String exportKey,String fileName,List<?> list,HttpServletRequest req,HttpServletResponse resp);
}
ExportExcleServiceImpl.java 实现类
package com.wulss.jakartapoi.hssf;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.UUID;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
@Service
public class ExportExcelServiceImpl extends ExportExcelBaseService implements IExportExcelService{
@Autowired
private ExportMapper exportMapper;
@Override
public void exportExcelWithSimple(HttpServletRequest req,HttpServletResponse resp,List<UserConsumeDetailRecord> list){
String fileName = "个人消费明细表" + UUID.randomUUID().toString();
try {
//工作空间
HSSFWorkbook workbook = new HSSFWorkbook();
//第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();
}
}
@Override
public void exportExcelWithDispose(String exportKey,String fileName,List<?> list,HttpServletRequest req,HttpServletResponse resp){
//查询一表 级联 获取多表集合
List<ExportFieldBean> fieldBeanList = exportMapper.getExportByExportKey(exportKey).getFieldBeanList();
try {
//工作空间
HSSFWorkbook workbook = new HSSFWorkbook();
//第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 基础类
package com.wulss.jakartapoi.hssf;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Component;
@Component
public class ExportExcelBaseService {
/**
* 获取设置好的样式
* @param workbook 工作空间
* @return
*/
public HSSFCellStyle 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));//设置字体
return cellStyle;
}
/**
* 获取设置好的字体
* @param workbook 工作空间
* @return
*/
public HSSFFont 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);//下划线
return fontStyle;
}
/**
* 通过流的方式输出excle到页面
* @param response 响应
* @param workbook 工作空间
* @param fileName 文件名
*/
public void outExcelStream(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 void deleteFileDir(String fileName) {
File file = new File(fileName);
DeleteAll(file);
}
public void DeleteAll(File dir) {
if (dir.isFile()) {
dir.delete();
return;
} else {
File[] files = dir.listFiles();
for (File file : files) {
DeleteAll(file);
}
}
dir.delete();
}
}
ExportExcelController.java
package com.wulss.jakartapoi.hssf;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/exportExlce")
public class ExportExcelController {
@Autowired
IExportExcelService iExportExcelService;
@RequestMapping(value="/withSimple",method=RequestMethod.GET)
public String withSimple(HttpServletRequest req,HttpServletResponse resp) {
List<UserConsumeDetailRecord> list = new ArrayList<>();
UserConsumeDetailRecord record = null;
for(int i=0;i<10;i++) {
record = new UserConsumeDetailRecord();
record.setUserName("奥雷里亚诺");
record.setConsumeAmount(6.66);
record.setConsumeDate(new Date());
record.setConsumeTitle("喝酒吃肉");
list.add(record);
}
iExportExcelService.exportExcelWithSimple(req, resp, list);
return "success";
}
@RequestMapping(value="/withDispose",method=RequestMethod.GET)
public String WithDispose(HttpServletRequest req,HttpServletResponse resp) {
List<UserConsumeDetailRecord> list = new ArrayList<>();
UserConsumeDetailRecord record = null;
for(int i=0;i<10;i++) {
record = new UserConsumeDetailRecord();
record.setUserName("奥雷里亚诺");
record.setConsumeAmount(6.66);
record.setConsumeDate(new Date());
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='导出字段表';