<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.15</version>
<exclusions>
<exclusion>
<artifactId>poi</artifactId>
<groupId>org.apache.poi</groupId>
</exclusion>
</exclusions>
</dependency>
1:得到类的list就是你要到处的数据
List<CsfAuditHistoryDto> list = csfAuditHistoryBeanSv.exportHisList(req)
2:接口处定义response
3:生成内容导出,调用编写的工具类
HSSFWorkbook workbook = exportAuditUtil.exportCsfdeclareHisListExcel(list);
String fileName = "csf声明环境审核历史报表" + ".xls";
fileName = URLEncoder.encode(fileName, "utf-8");
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);
response.flushBuffer();
workbook.write(response.getOutputStream());//输出流
4:详细分解上一步,工具类
public HSSFWorkbook exportCsfHisListExcel(List<CsfAuditHistoryDto> list) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("excell底部的表名");
//设置每列的宽度
setSheetWidth(sheet);//自定义方法
//设置宽度自适应
sheet.autoSizeColumn(1, true);
//添加第一行标题
HSSFRow rowFirst = sheet.createRow(0);//框架内部方法,创建第一行
setFirstRow(rowFirst, workbook);
for (int i = 1; i < list.size() + 1; i++) {
//获取固定的style,需要重新获取style,否则属性会覆盖
CellStyle style = setFixedCellStyle(workbook);
CsfAuditHistoryDto csfAuditHistoryDto = list.get(i - 1);
SimpleDateFormat myFmt1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
HSSFRow row = sheet.createRow(i);
//获取每行特有style属性
setCellStyle(style, workbook, i);
//服务编码
getCell(0, row, csfAuditHistoryDto.getServiceCode(), style);
//服务名称
getCell(1, row, csfAuditHistoryDto.getServiceName(), style);
//审核人
getCell(2, row, csfAuditHistoryDto.getAuditUser(), style);
//审核结果
String auditResult = getAuditResult(csfAuditHistoryDto.getAuditResult());
getCell(3, row, auditResult, style);
if (csfAuditHistoryDto.getAuditDate() != null) {
//审核日期
getCell(4, row, myFmt1.format(csfAuditHistoryDto.getAuditDate()), style);
}else {
getCell(4, row, "", style);
}
//需求编码
getCell(5, row, csfAuditHistoryDto.getRequireNum(), style);
//申请人
getCell(6, row, csfAuditHistoryDto.getApplyUser(), style);
if (csfAuditHistoryDto.getApplyDate() != null) {
//申请日期
getCell(7, row, myFmt1.format(csfAuditHistoryDto.getApplyDate()), style);
}else {
getCell(7, row, "", style);
}
//操作类型
String operateType = getOperateType(csfAuditHistoryDto.getOperateType());
getCell(8, row, operateType, style);
//申请说明
getCell(9, row, csfAuditHistoryDto.getRemarks(), style);
//审核说明
//getCell(10, row, csfAuditHistoryDto.getAuditRemarks(), style);
}
return workbook;
}
//第一行列名的设置
private void setdeclareFirstRow(HSSFRow rowFirst, HSSFWorkbook workbook) {
//获取固定的style属性
CellStyle styleFirst = setFixedCellStyle(workbook);
HSSFPalette palette = workbook.getCustomPalette();
styleFirst.setFillPattern(FillPatternType.SOLID_FOREGROUND);
palette.setColorAtIndex((short) 9, (byte) 0, (byte) 176, (byte) 240);
styleFirst.setFillForegroundColor((short) 9);
getCell(0, rowFirst, "目录名称", styleFirst);
getCell(1, rowFirst, "服务名称", styleFirst);
getCell(0, rowFirst, "审核人", styleFirst);
getCell(1, rowFirst, "审核结果", styleFirst);
getCell(2, rowFirst, "审核日期", styleFirst);
getCell(3, rowFirst, "需求编码", styleFirst);
getCell(4, rowFirst, "申请人", styleFirst);
getCell(5, rowFirst, "申请日期", styleFirst);
getCell(6, rowFirst, "操作类型", styleFirst);
getCell(7, rowFirst, "申请说明", styleFirst);
}
/**
* 功能描述: 设置某个单元格的样式和值
* @param i 单元格列数
* @param row 单元格行
* @param value 单元格值
* @param style 单元格样式
* @return void
*/
private void getCell(int i, HSSFRow row, String value, CellStyle style) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(value);
}
/**
* 功能描述:设置宽度
*
* @param sheet HSSFSheet
* @return void
*/
private void setSheetWidth(HSSFSheet sheet) {
sheet.setColumnWidth(0, 10000);
sheet.setColumnWidth(1, 8000);
sheet.setColumnWidth(2, 3000);
sheet.setColumnWidth(3, 3000);
sheet.setColumnWidth(4, 6000);
sheet.setColumnWidth(5, 3000);
sheet.setColumnWidth(6, 3000);
sheet.setColumnWidth(7, 6000);
sheet.setColumnWidth(8, 3000);
sheet.setColumnWidth(9, 6000);
}
/**
* 功能描述: 根据数据库的数字状态码返回中文操作类型
*
* @param operateType 数字状态码
* @return java.lang.String
*/
private String getOperateType(String operateType) {
if (StringUtils.isNotEmpty(operateType)) {
switch (operateType) {
case "1":
return "新增";
case "2":
return "编辑";
case "3":
return "删除";
default:
return "";
}
} else {
return "";
}
}
private CellStyle setFixedCellStyle(HSSFWorkbook workbook) {
CellStyle style = workbook.createCellStyle();
//设置上下左右边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
//设置字体大小
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 12);
style.setFont(font);
//设置不自动换行
style.setWrapText(false);
//设置居中
style.setAlignment(HorizontalAlignment.CENTER);
return style;
}
private String getAuditResult(String auditResult) {
if (StringUtils.isNotEmpty(auditResult)) {
switch (auditResult) {
case "Y":
return "通过";
case "N":
return "拒绝";
default:
return "";
}
} else {
return "";
}
}
创建多个sheet页导出
只用创建多个sheet然后每个sheet按照之前一样赋值,设置格式就可以
//创建工作表sheet
HSSFSheet sheet = workbook.createSheet("服务复用统计");
HSSFSheet sheet1 = workbook.createSheet("需求服务绑定记录");