一、easyPoi
1、依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
2、相关注解
(1)@Excel 实体类属性和表头信息对应
属性 | 类型 | 默认值 | 功能 |
---|---|---|---|
name | String | null | 对应Excel的列名 |
orderNum | String | “0” | 列的排序 |
format | String | ” “ | 时间格式化,相当于同时设置exportFormat和importFormat |
exportFormat | String | " " | 导出时的时间格式 |
importFormat | String | " " | 导入时的时间格式 |
type | int | 1 | 导出类型:1是文本(默认),2是图片,3是函数,10是数字 |
replace | String[] | {} | 值的替换,replace = {"男_1", "女_2"} 将查出来的值1的替换为男 |
needMerge | boolean | false | 是否需要纵向合并单元格(用于list创建的多个row) |
numFormat | String | " " | 数字格式化,使用对象DecimalFormat |
suffix | String | " " | 文字后缀,常用添加单位 |
width | double | 10 | 列宽 |
height | double | 10 | 行高,后期打算统一使用@ExcelTarget的height,这个会被废弃 |
savePath | String | “/upload/” | 导入文件保存路径,默认是”target/classes/upload/类名“ |
isStatistics | boolean | false | 自动统计数据,在行尾进行统计,会吞没异常 |
isImportField | boolean | false | 导入Excel时,对Excel中的字段进行校验,如果没有该字段,导入失败 |
isColumnHidden | boolean | false | 导出隐藏列 |
databaseFormat | String | “yyyyMMddHHmmss” | 导出时间设置,如果字段是data类型则不需要设置,数据库如果是String类型,这个需要设置这个数据库格式,用来转换时间格式输出 |
isWrap | boolean | true | 是否换行及支持\n |
mergeRely | int[] | {} | 合并单元格依赖关系,比如第二列合并是基于第一列 |
imageType | int | 1 | 导出类型1:冲file读取,2:从数据库中读取,默认是文件,导入也是一样 |
(2)@ExcelTarget 作用于对象
(3) @@ExcelIgnore 作用于属性,标记这个属性被忽略
(4)@ExcelCollection(name = "sass") 表示一个集合,针对对象一对多关系的导出,比如一个人名下几套房产
属性 | 类型 | 默认值 | 功能 |
---|---|---|---|
name | String | null | 对应集合的列名 |
orderNum | String | “0” | 排序 |
type | Class | ArrayList.class | 导入时创建对象时使用 |
(5)@ExcelEntity 一对一
(6)导出的类必须实现序列化
3、导出相关注解
3.1.集合数据导出解决方案
默认导出的list格式
手动转换,添加映射字段,重写get方法
3.2.对象一对一关系导出
3.3.对象一对多导出
3.4.导出图片
//表示type =2 该字段类型为图片,imageType=1 (默认可以不填),表示从file读取,字段类型是个字符串类型 可以用相对路径也可以用绝对路径,绝对路径优先依次获取
@Excel(name = "公司LOGO", type = 2 ,width = 40 , height = 20,imageType = 1)
private String companyLogo;
4、导入excel
4.1读取本地导入数据库
package com.jt.pojo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.math.BigDecimal;
@Data
@NoArgsConstructor
@AllArgsConstructor
@ExcelTarget("goods")
public class Goods implements Serializable {
private static final long serialVersionUID = 1L;
// 商品id
@Excel(name = "商品id")
private Long id;
// 商品名称
@Excel(name = "商品名称")
private String goodsName;
// 商品标题
@Excel(name = "商品标题")
private String goodsTitle;
// 商品图片
@Excel(name = "商品图片")
private String goodsImg;
// 商品详情
@Excel(name = "商品详情")
private String goodsDetail;
// 商品价格
@Excel(name = "商品价格")
private BigDecimal goodsPrice;
// 商品库存,-1表示没有限制
@Excel(name = "商品库存")
private Integer goodsStock;
}
@GetMapping("/importEasyPoi")
public void importEasyPoi() {
ImportParams params = new ImportParams();
// 设置大标题占几行
params.setTitleRows(0);
// 设置小标题占几行
params.setHeadRows(1);
// 校验小标题列是否存在
params.setImportFields(new String[]{"商品id", "商品名称", "商品标题", "商品图片"});
// 参数一:导入的文件流 参数二:导入的类型 参数三:导入的配置对象
List<Goods> excel = null;
try {
excel = ExcelImportUtil.importExcel(new FileInputStream("D:\\Java\\Java-XM\\jt\\jt.3\\jt\\src\\main\\resources\\templates\\easyPoi用户导入导出.xls"), Goods.class, params);
} catch (Exception e) {
e.printStackTrace();
}
for (Goods goods : excel) {
System.out.println(goods);
}
}
4.2前端导入到数据库
controller层MultipartFile接收导入excel文件,将MultipartFile转为流
@GetMapping("/exportWellLid")
public Result exportWellLid(MultipartFile multipartFile) {
if (multipartFile == null) {
return ResultUtil.error(ResultEnum.FAILD);
}
// 数据的获取需要放到异步之外
byte [] byteArr= new byte[0];
try {
byteArr = multipartFile.getBytes();
} catch (IOException e) {
e.printStackTrace();
}
InputStream inputStream = new ByteArrayInputStream(byteArr);
// 开启异步操作
lidInfoService.exportWellLid(inputStream);
return ResultUtil.success(ResultEnum.SUCCESS);
}
service操作该流,向数据库导入数据
@Override
public void exportWellLid(InputStream inputStream) {
// 设置导入excel表格
ImportParams params = new ImportParams();
// 设置大标题占几行
params.setTitleRows(1);
// 设置小标题占几行
params.setHeadRows(1);
List<LidInfoSelectPageEntity> excelList = null;
try {
// 参数一:导入的文件流 参数二:导入的类型 参数三:导入的配置对象
excelList = ExcelImportUtil.importExcel(inputStream, LidInfoSelectPageEntity.class, params);
} catch (Exception e) {
e.printStackTrace();
}
// 数据入库
for (int i = 0; i < excelList.size(); i++) {
// 获取每一条井盖数据
LidInfoSelectPageEntity lidInfoSelectPageEntity = excelList.get(i);
// 主键设置uuid
lidInfoSelectPageEntity.setId(UUID.randomUUID().toString());
// 数据入库
if (lidInfoDao.excelImportDatabase(excelList.get(i)) != 1) {
throw new RuntimeException("插入失败");
}
}
}
5、导出world文档
(1)添加world模板,src/main/resources/templates/easypoi.docx
(2)代码实现--导出到本地
public static void main(String[] args) throws Exception {
// 模板地址
String docxPath = "templates/easyPoi.docx";
// 导出文件路径
String exportPath = "D:\\Java\\Java-XM\\秒杀-宣传\\003_software";
// 导出文件姓名
String exportName = "\\exportName.docx";
// 用户数据
Map<String, Object> map = new HashMap<>();
map.put("company", "xxx公司");
map.put("date", "2020-04-20 08:00:00");
map.put("name", "小明");
map.put("sex", "男");
map.put("hobby", "唱跳rap");
map.put("birthdate", "2020-04-20 08:00:00");
XWPFDocument xwpfDocument = WordExportUtil.exportWord07(docxPath, map);
FileOutputStream fos = new FileOutputStream(exportPath + exportName);
xwpfDocument.write(fos);
fos.flush();
fos.close();
}
(3)代码实现--前端下载
@RequestMapping("/exportWord")
public void exportWord(HttpServletResponse response){
Map<String,Object> map = new HashMap<>();
Map<String, Object> map = new HashMap<>();
map.put("company", "xxx公司");
map.put("date", "2020-04-20 08:00:00");
map.put("name", "小明");
map.put("sex", "男");
map.put("hobby", "唱跳rap");
map.put("birthdate", "2020-04-20 08:00:00");
try {
response.setContentType("application/msword");
// 编码
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测试","UTF-8" );
//String fileName = "测试"
response.setHeader("Content-disposition","attachment;filename="+fileName+".docx" );
XWPFDocument doc = WordExportUtil.exportWord07("templates/demo.docx",map);
doc.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
//WordUtil.exportWord("templates/demo.docx","D:/" ,"生成文件.docx" ,map );
}
6、工具类
样式
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;
/**
* @Description 样式工具类
*
* @Author syh
* @Date 2023/9/26 17:07
*/
public class ExcelStyleUtil implements IExcelExportStyler {
private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
private static final short FONT_SIZE_TEN = 10;
private static final short FONT_SIZE_ELEVEN = 14;
private static final short FONT_SIZE_TWELVE = 18;
/**
* 大标题样式
*/
private CellStyle headerStyle;
/**
* 每列标题样式
*/
private CellStyle titleStyle;
/**
* 数据行样式
*/
private CellStyle styles;
public ExcelStyleUtil(Workbook workbook) {
this.init(workbook);
}
/**
* 初始化样式
*
* @param workbook
*/
private void init(Workbook workbook) {
this.headerStyle = initHeaderStyle(workbook);
this.titleStyle = initTitleStyle(workbook);
this.styles = initStyles(workbook);
}
/**
* 大标题样式
*
* @param color
* @return
*/
@Override
public CellStyle getHeaderStyle(short color) {
return headerStyle;
}
/**
* 每列标题样式
*
* @param color
* @return
*/
@Override
public CellStyle getTitleStyle(short color) {
return titleStyle;
}
/**
* 数据行样式
*
* @param parity 可以用来表示奇偶行
* @param entity 数据内容
* @return 样式
*/
@Override
public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
return styles;
}
/**
* 获取样式方法
*
* @param dataRow 数据行
* @param obj 对象
* @param data 数据
*/
@Override
public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
return getStyles(true, entity);
}
/**
* 模板使用的样式设置
*/
@Override
public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
return null;
}
/**
* 设置隔行背景色
*/
public static CellStyle getStyles(Workbook workbook,boolean isBold,short size) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, size,isBold));
//背景色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setDataFormat(STRING_FORMAT);
return style;
}
/**
* 初始化--大标题样式
*
* @param workbook
* @return
*/
private CellStyle initHeaderStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
return style;
}
/**
* 初始化--每列标题样式
*
* @param workbook
* @return
*/
private CellStyle initTitleStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, true));
//背景色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
/**
* 初始化--数据行样式
*
* @param workbook
* @return
*/
private CellStyle initStyles(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
style.setDataFormat(STRING_FORMAT);
return style;
}
/**
* 基础样式
*
* @return
*/
private static CellStyle getBaseCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
style.setWrapText(true);
return style;
}
/**
* 字体样式
*
* @param size 字体大小
* @param isBold 是否加粗
* @return
*/
private static Font getFont(Workbook workbook, short size, boolean isBold) {
Font font = workbook.createFont();
//字体样式
font.setFontName("宋体");
//是否加粗
font.setBold(isBold);
//字体大小
font.setFontHeightInPoints(size);
return font;
}
}
导出
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
/**
* @Description ExcelUtil 导出工具类
*
* @Author syh
* @Date 2023/9/26 17:05
*/
public class ExcelUtil {
/**
* 导出设置隔行背景色
* @param params
* @param list
* @param pojoClass
* @param fileName
* @param response
*/
public static void exportExcel(ExportParams params, List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(params,pojoClass,list);
if (workbook != null);
setRowBackground(workbook);
downLoadExcel(fileName, response, workbook);
}
/**
* 偶数行设置背景色
*/
private static void setRowBackground(Workbook workbook){
Sheet sheet = workbook.getSheetAt(0);
// 设置单元格大小自适应
int maxColumn = sheet.getRow(0).getPhysicalNumberOfCells();
for(int i = 0; i < maxColumn; i++){
sheet.autoSizeColumn(i);
}
for (int i = 0; i < maxColumn; i++) {
int orgWidth = sheet.getColumnWidth(i);
sheet.autoSizeColumn(i, true);
int newWidth = (int) (sheet.getColumnWidth(i) + 100);
int maxWith = 256*255;
//限制下最大宽度
if(newWidth > maxWith) {
sheet.setColumnWidth(i, maxWith);
}else if (newWidth > orgWidth) {
sheet.setColumnWidth(i, newWidth);
} else {
sheet.setColumnWidth(i, orgWidth);
}
}
// 设置隔行变色
CellStyle styles = ExcelStyleUtil.getStyles(workbook,false,(short) 12);
for(int i = 0; i <= sheet.getLastRowNum(); i ++) {
Row row = sheet.getRow(i);
if (i%2!=0 && i>=2){//标题用全局的标题样式,就不单独设置样式了,所以排除标题
for(int j = 0; j < row.getPhysicalNumberOfCells(); j ++) {
Cell cell = row.getCell(j);
cell.setCellStyle(styles);
}
}
}
}
/**
* 下载
* @param fileName 文件名称
* @param response
* @param workbook excel数据
*/
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
}
隔行变色使用模版
// 使用模版
@GetMapping("/exportUserList")
public void exportUserList(HttpServletResponse response) throws IOException {
// 获得全部用户列表
List<User> list = userMapper.selectUserList(); // 数据列表,实体类添加注解
ExportParams params = new ExportParams("用户信息", "用户信息"); // 大标题信息
params.setTitleHeight((short) 13); // 标题高度
params.setHeight((short) 8); // 单元格高度
params.setStyle(ExcelStyleUtil.class); // 样式导入
ExcelUtil.exportExcel(params,list, User.class,"用户列表",response); // 前端下载
}
二、poi
easypoi的依赖 包含 poi
1、读取本地excel 导入数据库
public static void main(String[] args) throws IOException {
//创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook("D:/储存/程序垃圾/2.xlsx");
//获取工作表,既可以根据工作表的顺序获取,也可以根据工作表的名称获取
XSSFSheet sheet = workbook.getSheetAt(0);
//遍历工作表获得行对象
for (Row row : sheet) {
//遍历行对象获取单元格对象
for (Cell cell : row) {
// 将表格内容转换成String类型
cell.setCellType(CellType.STRING);
//获得单元格中的值
String value = cell.getStringCellValue();
System.out.println(value);
}
}
workbook.close();
}
2、导出Excel到本地(包括很全的样式设置)
@GetMapping("/exportPoi")
public void exportPoi(HttpServletResponse response) throws IOException {
// 1.创建excel文档对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 2.获取excel的sheet页对象(如果需要导出多个sheet,那就设置多个HSSFSheet对象)
// 设置第一页sheet
HSSFSheet sheet = workbook.createSheet("sheet");
// 设置第二页sheet
HSSFSheet sheet1 = workbook.createSheet("sheet1");
// 3、样式设置(可以单独提取到一个方法中,或者单独写一个工具类)
//(1)设置列宽,参数一为列号(0开始),参数二为列宽
sheet.setColumnWidth(1,5000);
//(2)设置字体居中,也可以靠左靠右(哪个单元格字体居中,就需要单独调用)
// 注释:有些地方使用CellStyle,这俩的关系public final class HSSFCellStyle implements CellStyle
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 设置单元格的中心水平对齐-居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置单元格的垂直对齐类型-居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//(3)设置单元格边框
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框颜色
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
//(4)字体设置
HSSFFont font = workbook.createFont();// 生成字体
font.setFontName("宋体");// 设置字体类型
font.setFontHeightInPoints((short) 10);// 设置字体大小
font.setBold(true); //粗体显示 flase常规
cellStyle.setFont(font);// 将字体样式放入总样式中
// 4、以下内容为合并单元格,创建标题行
//获取标题行(第1行)对象
HSSFRow row = sheet.createRow(0);
//获取标题行中列(第1列)对象
HSSFCell cell = row.createCell(0);
//设置标题名称
cell.setCellValue("标题demo");
// 设置标题单元格样式
cell.setCellStyle(cellStyle);
//合并单元格,将坐标(0,0)单元格到(5,5)单元格合并(默认行数和列数都是从0开始的)
/*
参数1:开始行号
参数2:结束行号
参数3:开始列号
参数4:终止列号
*/
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 1);
// 设置合并的标题列的边框(其他合并单元格也同样设置)
// 参数一边框样式,参数二合并的单元格,参数三哪个sheet页
RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet); // 下边框
RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet); // 上边框
RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet); // 左边框
RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet); // 右边框
sheet.addMergedRegion(cellRangeAddress);
// 5、填入列名
/*
1)获取第一行对象
2)获取各个列对象
3)设置各个列的内容
*/
HSSFRow row1 = sheet.createRow(1);
HSSFCell r1c1 = row1.createCell(0);
r1c1.setCellValue("姓名");
HSSFCell r1c2 = row1.createCell(1);
r1c2.setCellValue("学号");
// 6、填入数据
HSSFRow row2 = sheet.createRow(2);
HSSFCell r2c1 = row2.createCell(0);
r2c1.setCellValue("吴照生");
// 姓名列对应的单元格传入样式
r2c1.setCellStyle(cellStyle);
HSSFCell r2c2 = row2.createCell(1);
// 注意:当导出的数据是数值型的字符串,wps会提示格式错误,这时把字符串转为number即可。实际使先判断字符串是否能转为number,再转化。(其实解不解决都不影响使用)
r2c2.setCellValue(Integer.valueOf(“1111223233”));
// 学号列对应的单元格传入样式
r2c2.setCellStyle(cellStyle);
// 7、导出数据到excel
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream("D:\\SmartCityFile\\demo.xls");
workbook.write(fileOutputStream);
fileOutputStream.flush();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if(fileOutputStream != null){
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
4、导出excel到前端
将标题3的第七步替换成本示例代码
// 浏览器下载
OutputStream stream = null;
try {
response.setHeader("content-Type", "application/vnd.ms-excel");
// 设置表格名称,这样处理避免中文名称乱码
String codedFileName = java.net.URLEncoder.encode("测试表格", "UTF-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + ".xlsx");
stream = response.getOutputStream();
workbook.write(stream);
}catch (IOException ioException){
System.out.println("===========excel表格浏览器下载异常===========, 异常信息:" + ioException);
}finally {
if (workbook != null) {
workbook.close();
}
if (stream != null) {
stream.close();
}
}
5、导出单元格设置下拉框
原地址使用POI生成Excel下拉框_梅比斯-维维亚米利欧的博客-CSDN博客_poi导出excel生成下拉框
方式一:下拉框值有限
public static void main(String[] args) {
//1.创建excel文档对象
HSSFWorkbook workbook = new HSSFWorkbook();
//2.获取excel表单对象
HSSFSheet sheet = workbook.createSheet("表数据demo");
// 设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
//设置下拉框数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[]{"选项一", "选项二", "选项三",});
//设置生效的起始行、终止行、起始列、终止列
CellRangeAddressList addressList = new CellRangeAddressList(6,6,0,0);
DataValidation validation = helper.createValidation(constraint,addressList);
//适配xls和xlsx
if(validation instanceof HSSFDataValidation){
validation.setSuppressDropDownArrow(false);
}else{
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
}
sheet.addValidationData(validation);
// 给下拉框所在的单元格设置默认值
HSSFRow row21 = sheet.createRow(6);
HSSFCell r2c11 = row21.createCell(0);
r2c11.setCellValue("下拉框默认值");
}
方式二:下拉框值无限
public static void main(String[] args) {
//1.创建excel文档对象
HSSFWorkbook workbook = new HSSFWorkbook();
//2.获取excel表单对象
HSSFSheet sheet = workbook.createSheet("表数据demo");
// 创建隐藏sheet。保存下拉框的数据
HSSFSheet hiddenSheet = workbook.createSheet("hiddenSheet");
//设置下拉框数据
List<String> list = Arrays.asList("选项一", "选项二", "选项三", "选项四");
for (int i = 0; i < list.size(); i++) {
HSSFRow row = hiddenSheet.createRow(i);
HSSFCell cell = row.createCell(0);
cell.setCellValue(list.get(i));
}
workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet),true);
//设置下拉框数据引用
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint("hiddenSheet!$A$1:$A$" + list.size());
//设置生效的起始行、终止行、起始列、终止列
CellRangeAddressList addressList = new CellRangeAddressList(6,6,0,0);
DataValidation validation = helper.createValidation(constraint,addressList);
//适配xls和xlsx
if(validation instanceof HSSFDataValidation){
validation.setSuppressDropDownArrow(false);
}else{
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
}
sheet.addValidationData(validation);
// 给下拉框所在的单元格设置默认值
HSSFRow row21 = sheet.createRow(6);
HSSFCell r2c11 = row21.createCell(0);
r2c11.setCellValue("下拉框默认值");
}
6、单元格各种数据格式
原贴地址:(6条消息) POI导出excel日期格式_它山之石,可以攻玉的博客-CSDN博客_poi导出日期格式
// TODO 使用发现每次单独修改某个单元格格式,最后都会全局修改,原因不明
导出时间格式
// 创建excel文档对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建样式对象
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 设置时间样式
HSSFDataFormat dataFormat = workbook.createDataFormat();
// cellStyle.setDataFormat(0)为默认值
cellStyle.setDataFormat(dataFormat.getFormat("yyyy-MM-dd hh:mm:ss"));
7、单元格颜色
IndexedColors.AQUA.getIndex(); //X1
IndexedColors.AUTOMATIC.getIndex(); //X2
IndexedColors.BLUE.getIndex(); //X3
IndexedColors.BLUE_GREY.getIndex(); //X4
IndexedColors.BRIGHT_GREEN.getIndex(); //X5
IndexedColors.BROWN.getIndex(); //X6
IndexedColors.CORAL.getIndex(); //X7
IndexedColors.CORNFLOWER_BLUE.getIndex(); //X8
IndexedColors.DARK_BLUE.getIndex(); //X9
IndexedColors.DARK_GREEN.getIndex(); //X10
IndexedColors.DARK_RED.getIndex(); //X11
IndexedColors.DARK_TEAL.getIndex(); //X12
IndexedColors.DARK_YELLOW.getIndex(); //X13
IndexedColors.GOLD.getIndex(); //14
IndexedColors.GREEN.getIndex(); //15
IndexedColors.GREY_25_PERCENT.getIndex(); //X16
IndexedColors.GREY_40_PERCENT.getIndex(); //X17
IndexedColors.GREY_50_PERCENT.getIndex(); //X18
IndexedColors.GREY_80_PERCENT.getIndex(); //X19
IndexedColors.INDIGO.getIndex(); //X20
IndexedColors.LAVENDER.getIndex(); //X21
IndexedColors.LEMON_CHIFFON.getIndex(); X22
IndexedColors.LIGHT_BLUE.getIndex(); //X23
IndexedColors.LEMON_CHIFFON.getIndex(); //24
IndexedColors.LIGHT_BLUE.getIndex(); //X25
IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex(); //X26
IndexedColors.LIGHT_GREEN.getIndex(); //X27
IndexedColors.LIGHT_ORANGE.getIndex(); //X28
IndexedColors.LIGHT_TURQUOISE.getIndex(); //X29
IndexedColors.LIGHT_YELLOW.getIndex(); //X30
IndexedColors.LIME.getIndex(); //X31
IndexedColors.MAROON.getIndex(); //X32
IndexedColors.OLIVE_GREEN.getIndex(); //X33
IndexedColors.ORANGE.getIndex(); //34
IndexedColors.ORCHID.getIndex(); //X35
IndexedColors.PALE_BLUE.getIndex(); //X36
IndexedColors.PINK.getIndex(); //X37
IndexedColors.PLUM.getIndex(); //X38
IndexedColors.RED.getIndex(); //X39
IndexedColors.ROSE.getIndex(); //X40
IndexedColors.ROYAL_BLUE.getIndex(); //X41
IndexedColors.SEA_GREEN.getIndex(); //X42
IndexedColors.SKY_BLUE.getIndex(); //X43
IndexedColors.TAN.getIndex(); //X44
IndexedColors.TEAL.getIndex(); //X45
IndexedColors.TURQUOISE.getIndex(); //X46
IndexedColors.VIOLET.getIndex(); //X47
IndexedColors.WHITE.getIndex(); //X48
IndexedColors.YELLOW.getIndex(); //X49
8、方法总结
package com.hssmartcity.common.utils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.commons.beanutils.BeanUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
/**
* poi工具类
* @作者 syh
* @Date 2022.10.11
*/
public class PoiUtils {
/**
* 单元格样式设置
*
* @param workbook excel文档对象
* @param fontName 字体样式名称(微软雅黑,宋体),为null默认
* @param fontSize 字体大小(15,16),为null默认
* @param borderStyle 边框线的样式,为null默认 推荐BorderStyle.THIN
* @param backgroundColor 单元格背景颜色,为null默认
* @return HSSFCellStyle 样式对象
*/
public static HSSFCellStyle worldStyle(HSSFWorkbook workbook,
String fontName, short fontSize,
BorderStyle borderStyle,
IndexedColors backgroundColor) {
// 创建样式对象
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 设置字体显示位置,固定水平垂直居中
// 设置单元格的中心水平对齐-居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置单元格的垂直对齐类型-居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 字体样式设置
if (StringUtils.isNotBlank(fontName) || fontSize != 0) {
HSSFFont font = workbook.createFont();// 生成字体
if (StringUtils.isNotBlank(fontName)) {
font.setFontName(fontName);// 设置字体类型
}
if (fontSize != 0) {
font.setFontHeightInPoints(fontSize);// 设置字体大小
}
cellStyle.setFont(font);// 将字体样式放入总样式中
}
// 设置边框样式,四个边框样式统一。 边框颜色使用默认,如果需要自定义,自行扩展
if (borderStyle != null) {
cellStyle.setBorderBottom(borderStyle); //下边框
cellStyle.setBorderTop(borderStyle);//上边框
cellStyle.setBorderLeft(borderStyle);//左边框
cellStyle.setBorderRight(borderStyle);//右边框
}
// 单元格背景颜色
if (backgroundColor != null) {
cellStyle.setFillForegroundColor(backgroundColor.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
return cellStyle;
}
/**
* 设置列宽
*
* @param sheet 所属sheet页
* @param startCol 起始列号(0开始)
* @param closeCol 终止列号
* @param width 列宽 建议值(5000)
*/
public static void colWidth(HSSFSheet sheet, int startCol, int closeCol, Integer width) {
if (width == null || width == 0) {
width = 5000;
}
for (int i = startCol; i<=closeCol; i++) {
sheet.setColumnWidth(i, width);
}
}
/**
* 设置某单元格列宽
*
* @param sheet 所属sheet页
* @param col 所属列号(0开始)
* @param width 列宽
*/
public static void colWidth(HSSFSheet sheet, int col, Integer width) {
if (width == null) {
width = 0;
}
sheet.setColumnWidth(col, width);
}
/**
* 设置单元格下拉框
*
* @param isPattern 下拉框方式 0:有限值下拉框(workbook为null) 1:无限值下拉框(内容多的时候使用)
* @param workbook 单元格对象
* @param sheet 所属sheet页
* @param str 下拉框选项
* @param startRow 起始行
* @param closeRow 终止行
* @param startCol 起始列
* @param closeCol 终止列
*/
public static void cellComboBox(int isPattern,
HSSFWorkbook workbook,
HSSFSheet sheet,
String[] str,
int startRow, int closeRow,
int startCol, int closeCol) {
//设置生效的起始行、终止行、起始列、终止列
CellRangeAddressList addressList = new CellRangeAddressList(startRow,closeRow,startCol,closeCol);
if (isPattern == 0) {
// 设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
//设置下拉框数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(str);
DataValidation validation = helper.createValidation(constraint,addressList);
//适配xls和xlsx
if(validation instanceof HSSFDataValidation){
validation.setSuppressDropDownArrow(false);
}else{
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
}
sheet.addValidationData(validation);
} else {
// 隐藏sheet的名字为UUID
String sheetName = UUID.randomUUID().toString()
// 创建隐藏sheet。保存下拉框的数据
HSSFSheet hiddenSheet = workbook.createSheet(sheetName);
//设置下拉框数据
for (int i = 0; i < str.length; i++) {
HSSFRow row = hiddenSheet.createRow(i);
HSSFCell cell = row.createCell(0);
cell.setCellValue(str[i]);
}
workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet),true);
//设置下拉框数据引用
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint(sheetName + "!$A$1:$A$" + str.length);
DataValidation validation = helper.createValidation(constraint,addressList);
//适配xls和xlsx
if(validation instanceof HSSFDataValidation){
validation.setSuppressDropDownArrow(false);
}else{
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
}
sheet.addValidationData(validation);
}
}
/**
* 将excel表格导出到本地
*
* @param workbook 单元格对象
* @param pathStr 保存地址(如果不存在,则创建) C:/a/c/c
* @param excelName 保存文件名 a.xlsx/a.xls
*/
public static String exportLocalhost(HSSFWorkbook workbook, String pathStr, String excelName) {
FileOutputStream fileOutputStream = null;
try {
File file = new File(pathStr);
if (file.exists()) {
file.mkdirs();
}
String excelPath = pathStr + "/" + excelName;
fileOutputStream = new FileOutputStream(excelPath);
workbook.write(fileOutputStream);
fileOutputStream.flush();
return excelPath;
} catch (IOException e) {
e.printStackTrace();
return "失败";
} finally {
if(fileOutputStream != null){
try {
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 前端下载excel表格
*
* @param workbook 单元格对象
* @param response response对象
* @param excelName 表格名字
* @param fileType 文件格式 .xlsx/.xls
*/
public static void exportIE(HSSFWorkbook workbook, HttpServletResponse response, String excelName, String fileType) {
OutputStream stream = null;
try {
response.setHeader("content-Type", "application/vnd.ms-excel");
// 设置表格名称,这样处理避免中文名称乱码
String codedFileName = java.net.URLEncoder.encode(excelName, "UTF-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + fileType);
stream = response.getOutputStream();
workbook.write(stream);
}catch (IOException ioException){
System.out.println("===========excel表格浏览器下载异常===========, 异常信息:" + ioException);
}finally {
try {
if (workbook != null) {
workbook.close();
}
if (stream != null) {
stream.close();
}
} catch (IOException ioException) {
System.out.println("===========流关闭异常===========, 异常信息:" + ioException);
}
}
}
/**
* 合并单元格(使用前需要提前调用本工具类中worldStyle方法,设置样式)
*
* @param sheet 所属sheet页
* @param startRow 起始行
* @param closeRow 终止行
* @param startCol 起始列
* @param closeCol 终止列
* @param tableCell 单元格内容
* @param borderStyle 边框线的样式,为null默认 推荐BorderStyle.THIN
* @param titleStyle 合并单元格样式,为null默认 推荐BorderStyle.THIN
*/
public static void cellJoin(HSSFSheet sheet,
int startRow, int closeRow, int startCol, int closeCol,
String tableCell,
BorderStyle borderStyle,
HSSFCellStyle titleStyle) {
HSSFRow row = sheet.createRow(startRow);
HSSFCell cell = row.createCell(startCol);
//设置单元格内容名称
cell.setCellValue(tableCell);
// 设置单元格样式
if (titleStyle != null) {
cell.setCellStyle(titleStyle);
}
//合并单元格,将坐标(0,0)单元格到(5,5)单元格合并(默认行数和列数都是从0开始的)
CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, closeRow, startCol, closeCol);
// 设置合并的标题列的边框(其他合并单元格也同样设置)
// 参数一边框样式,参数二合并的单元格,参数三哪个sheet页
if (borderStyle != null) {
// 边框颜色采用默认,如需设置,自行扩展
RegionUtil.setBorderBottom(borderStyle, cellRangeAddress, sheet); // 下边框
RegionUtil.setBorderTop(borderStyle, cellRangeAddress, sheet); // 上边框
RegionUtil.setBorderLeft(borderStyle, cellRangeAddress, sheet); // 左边框
RegionUtil.setBorderRight(borderStyle, cellRangeAddress, sheet); // 右边框
}
sheet.addMergedRegion(cellRangeAddress);
}
/**
* 样式中添加单元格数据格式化
*
* @param workbook 单元格对象
* @param style 单元格样式对象,每次调用需要单独创建样式
* @param isFormat 格式化为什么类型的数据 0:不需要 1:日期yyyy-MM-dd HH:mm:ss 2、保留n个小数位"0.00" 3:货币格式"¥#,##0" 4、百分比格式"0.00%"
* @param formatStr 格式化数据样式
*/
public static void cellDataFormat(HSSFWorkbook workbook, HSSFCellStyle style, int isFormat, String formatStr) {
if (isFormat != 0) {
if (isFormat == 1) {
HSSFDataFormat format= workbook.createDataFormat();
style.setDataFormat(format.getFormat(formatStr));
} else if (isFormat ==2) {
style.setDataFormat(HSSFDataFormat.getBuiltinFormat(formatStr));
} else if (isFormat == 3) {
HSSFDataFormat format= workbook.createDataFormat();
style.setDataFormat(format.getFormat(formatStr));
} else {
style.setDataFormat(HSSFDataFormat.getBuiltinFormat(formatStr));
}
}
}
/**
* 填入单元格数据(String类型)
*
* @param hssfRow 所属单元格
* @param colNum 单元格列号
* @param str 写入单元格的数据(String类型)
* @param style 单元格样式
*/
public static void cellWriteData(HSSFRow hssfRow, int colNum, String str, HSSFCellStyle style) {
HSSFCell titleCell = hssfRow.createCell(colNum);
titleCell.setCellValue(str);
titleCell.setCellStyle(style);
}
/**
* 填入单元格数据(Long)
*
* @param hssfRow 所属单元格
* @param colNum 单元格列号
* @param lon 写入单元格的数据(Long)
* @param style 单元格样式
*/
public static void cellWriteData(HSSFRow hssfRow, int colNum, Long lon, HSSFCellStyle style) {
HSSFCell titleCell = hssfRow.createCell(colNum);
titleCell.setCellValue(lon);
titleCell.setCellStyle(style);
}
/**
* 填入单元格数据(Date日期类)
*
* @param hssfRow 所属单元格
* @param colNum 单元格列号
* @param date 写入单元格的数据(Date日期类)
* @param style 单元格样式
*/
public static void cellWriteData(HSSFRow hssfRow, int colNum ,Date date, HSSFCellStyle style) {
HSSFCell titleCell = hssfRow.createCell(colNum);
titleCell.setCellValue(date);
titleCell.setCellStyle(style);
}
/**
* 填入单元格数据(int)
*
* @param hssfRow 所属单元格
* @param colNum 单元格列号
* @param integer 写入单元格的数据(int)
* @param style 单元格样式
*/
public static void cellWriteData(HSSFRow hssfRow, int colNum ,Integer integer, HSSFCellStyle style) {
HSSFCell titleCell = hssfRow.createCell(colNum);
titleCell.setCellValue(integer);
titleCell.setCellStyle(style);
}
}
9、实战导出
9.1.一个标题对应多个标题
@GetMapping("/DeptNumByRoadExcel")
public void DeptNumByRoadExcel(HttpServletResponse response) {
// 获得数据
List<DeptNumByRoad> deptNumByRoads = lidInfoService.selectDeptNumByRoad();
// 将数据封装成可以使用的形式
List<DeptNumByRoad> deptNumByRoads1 = new ArrayList<>();
// 将数据转换成需要使用的形式
// ============================================移植若依平台代码,该模块固定写死12个部门===========================================================
for (int i = 0; i < deptNumByRoads.size(); i++) {
List<DeptNumByRoadS> deptNumByRoadSList = new ArrayList<>();
deptNumByRoadSList.add(deptNumByRoads.get(i).getDept1());
deptNumByRoadSList.add(deptNumByRoads.get(i).getDept2());
deptNumByRoadSList.add(deptNumByRoads.get(i).getDept3());
deptNumByRoadSList.add(deptNumByRoads.get(i).getDept4());
deptNumByRoadSList.add(deptNumByRoads.get(i).getDept5());
deptNumByRoadSList.add(deptNumByRoads.get(i).getDept6());
deptNumByRoadSList.add(deptNumByRoads.get(i).getDept7());
deptNumByRoadSList.add(deptNumByRoads.get(i).getDept8());
deptNumByRoadSList.add(deptNumByRoads.get(i).getDept9());
deptNumByRoadSList.add(deptNumByRoads.get(i).getDept10());
deptNumByRoadSList.add(deptNumByRoads.get(i).getDept11());
deptNumByRoadSList.add(new DeptNumByRoadS(deptNumByRoads.get(i).getDept12()));
DeptNumByRoad deptNumByRoad = new DeptNumByRoad();
deptNumByRoad.setRoad(deptNumByRoads.get(i).getRoad());
deptNumByRoad.setDeptNumByRoadSList(deptNumByRoadSList);
deptNumByRoads1.add(deptNumByRoad);
}
// excel设计
List<ExcelExportEntity> colList = new ArrayList<>();
// 表头设置
ExcelExportEntity colEntity = new ExcelExportEntity("街道", "distributorName");
colEntity.setNeedMerge(true);
colEntity.setWidth(20);
colList.add(colEntity);
// ============================================移植若依平台代码,该模块固定写死12个部门=======================================================
List<String> listSStr = new ArrayList();
listSStr.add(deptNumByRoads.get(0).getDept1().getDept());
listSStr.add(deptNumByRoads.get(0).getDept2().getDept());
listSStr.add(deptNumByRoads.get(0).getDept3().getDept());
listSStr.add(deptNumByRoads.get(0).getDept4().getDept());
listSStr.add(deptNumByRoads.get(0).getDept5().getDept());
listSStr.add(deptNumByRoads.get(0).getDept6().getDept());
listSStr.add(deptNumByRoads.get(0).getDept7().getDept());
listSStr.add(deptNumByRoads.get(0).getDept8().getDept());
listSStr.add(deptNumByRoads.get(0).getDept9().getDept());
listSStr.add(deptNumByRoads.get(0).getDept10().getDept());
listSStr.add(deptNumByRoads.get(0).getDept11().getDept());
listSStr.add("无主井盖");
for (int i = 1; i <= 12; i++) {
ExcelExportEntity group_1 = new ExcelExportEntity(listSStr.get(i-1), "day");
List<ExcelExportEntity> exportEntities = new ArrayList<>();
ExcelExportEntity appalyExcel = new ExcelExportEntity("已标记", "applyNum" + i);
appalyExcel.setStatistics(true);
exportEntities.add(appalyExcel);
if (i != listSStr.size()) {
ExcelExportEntity adoptExcel = new ExcelExportEntity("未标记", "adoptNum" + i);
adoptExcel.setStatistics(true);
exportEntities.add(adoptExcel);
}
group_1.setList(exportEntities);
colList.add(group_1);
}
//文件数据
List<Map<String, Object>> list = new ArrayList<>();
// 封装好的数据 街道对应不同部门井盖的集合
List<DeptNumByRoad> disList = deptNumByRoads1;
for (int i = 0; i < disList.size(); i++) {
// 获取一个街道对应的井盖数据
DeptNumByRoad dis = disList.get(i);
Map<String, Object> valMap = new HashMap<>();
// 街道名字放入集合
valMap.put("distributorName", dis.getRoad());
// 获取部门对应的井盖数据
List<DeptNumByRoadS> dayDataList = dis.getDeptNumByRoadSList();
List<Map<String, Object>> list_1 = new ArrayList<>();
Map<String, Object> valMap_1 = new HashMap<>();
for (int j = 1; j <= dayDataList.size(); j++) {
valMap_1.put("applyNum" + j, dayDataList.get(j - 1).getMark());
valMap_1.put("adoptNum" + j, dayDataList.get(j - 1).getNotMark());
}
list_1.add(valMap_1);
valMap.put("day", list_1);
list.add(valMap);
}
//导出
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("所有街道所有部门不同状态的井盖信息", "数据"), colList, list);
ServletOutputStream outputStream=null;
try {
//流形式
response.setHeader("content-type","application/octet-stream");
//防止中文乱码
response.setHeader("content-disposition","attachment;filename="+ URLEncoder.encode("所有街道所有部门不同状态的井盖信息.xls","UTF-8"));
outputStream = response.getOutputStream();
workbook.write(outputStream);
} catch (Exception e) {
} finally {
if (null != outputStream) {
try {
outputStream.close();
} catch (IOException e) {
//skip
e.printStackTrace();
}
}
}
}
9.2.完美导出模板
public Result exportDepartmentStatisticsEvaluation(@NotNull @RequestBody DateVO dateVO, @PathVariable Integer total) throws InvocationTargetException, IllegalAccessException, IOException, ParseException {
// 部门统计
Map<String, Object> resultMap = statisticalService.bm_tj(param, organizationsID);
List<BmTjVO> listDepartment = (List<BmTjVO>)resultMap.get("data");
// 部门考评
Map<String, Object> mapDepartment = statisticalService.bmkp_tj(param, organizationsID);
List<BmkpTjVO> listDepartment1 = (List<BmkpTjVO>)mapDepartment.get("data");
// 数据封装
List<ExportDepartmentStatisticsEvaluationVO> exportDepartmentStatisticsEvaluationVOList = new ArrayList<>();
// 统计部门字符串
StringBuilder depStr = new StringBuilder();
for (BmTjVO bmTjVO : listDepartment) {
depStr.append(bmTjVO.getOrganizationsName()).append(";");
ExportDepartmentStatisticsEvaluationVO evaluationVO = new ExportDepartmentStatisticsEvaluationVO();
BeanUtils.copyProperties(evaluationVO, bmTjVO);
for (int i = 0; i < listDepartment1.size(); i++) {
if (bmTjVO.getOrganizationsID().equals(listDepartment1.get(i).getOrganizationsID())) {
BeanUtils.copyProperties(evaluationVO, listDepartment1.get(i));
break;
}
}
exportDepartmentStatisticsEvaluationVOList.add(evaluationVO);
}
// 标题信息
depStr.append(" 】");
StringBuilder titleStr = new StringBuilder( "统计条件: " + "【上报时间 介于 " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(dateVO.getStartTime()) + " 和 " +
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(dateVO.getEndTime()) + "】并且 【处置部门 等于 ");
titleStr.append(depStr.toString());
// 表格创建
List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
ExcelExportEntity yjczbm = new ExcelExportEntity("一级处置部门", "yjczbm");
// 一样的单元格合并
yjczbm.setMergeVertical(true);
yjczbm.setWidth(20);
colList.add(yjczbm);
ExcelExportEntity bmmc = new ExcelExportEntity("部门名称", "bmmc");
bmmc.setWidth(20);
colList.add(bmmc);
ExcelExportEntity yczs = new ExcelExportEntity("应处置数", "yczs");
// 单元格宽度
yczs.setWidth(15);
// 导出数据类型为数值
yczs.setType(10);
colList.add(yczs);
ExcelExportEntity yczs1 = new ExcelExportEntity("已处置数", "yczs1");
yczs1.setWidth(15);
yczs1.setType(10);
colList.add(yczs1);
List<Map<String, Object>> mapList = new ArrayList<>();
for (int i = 0; i < exportDepartmentStatisticsEvaluationVOList.size(); i++) {
Map<String, Object> valMap = new HashMap<>();
valMap.put("yjczbm", "泊头市");
valMap.put("bmmc", exportDepartmentStatisticsEvaluationVOList.get(i).getOrganizationsName());
valMap.put("yczs", exportDepartmentStatisticsEvaluationVOList.get(i).getShouldMangNums());
valMap.put("yczs1", exportDepartmentStatisticsEvaluationVOList.get(i).getManagFinsihNums());
mapList.add(valMap);
}
ExportParams exportParams = new ExportParams("市县专业部门综合评价", "数据");
// 传入样式
exportParams.setStyle(ExcelStyleUtil.class);
// 设置标题行下的说明
exportParams.setSecondTitle(titleStr.toString());
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, colList, mapList);
try {
// 此处在返回给浏览器流的形式
// //流形式
// response.setHeader("content-type","application/octet-stream");
// //防止中文乱码
// response.setHeader("content-disposition","attachment;filename="+ URLEncoder.encode("市县专业部门综合评价.xls","UTF-8"));
// outputStream = response.getOutputStream();
// workbook.write(outputStream);
// 先判断是否存在该路径
File file = new File(DEPTEXPORTSavePath);
// 如果路径不存在,创建多级目录
if (!file.exists()) {
file.mkdirs();
}
// 固定导出表的名字,每次覆盖,可以保持服务器内部始终只有一个最新文件
FileOutputStream fos = new FileOutputStream(DEPTEXPORTSavePath + "市县专业部门综合评价.xlsx");
workbook.write(fos);
workbook.close();
return ResultUtil.success(ResultEnum.SUCCESS, DEPTEXPORTReadpath.substring(0, DEPTEXPORTReadpath.length() - 2) + "市县专业部门综合评价.xlsx");
} catch (Exception e) {
return ResultUtil.success(ResultEnum.FAILD);
} finally {
if (workbook != null) {
workbook.close();
}
}
}