Java导出完整版模板(export)
程序员文章站
2022-03-15 12:58:30
...
1.在pom引入jra包
<!--导入导出excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
<exclusions>
<exclusion>
<groupId>stax</groupId>
<artifactId>stax-api</artifactId>
</exclusion>
</exclusions>
</dependency>
2.导出模板(util文件)
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
@Slf4j
public class ExportExcelStandard {
private String sheetName; // 导出表格的表名
private String[] rowName;// 导出表格的列名
private List<LinkedHashMap<String, Object>> dataList = new ArrayList<>(); // 对象数组的List集合
private HttpServletResponse response;
private HttpServletRequest request;
private String fileName;
private String filePath;
public ExportExcelStandard(String sheetName, String[] rowName, List<LinkedHashMap<String, Object>> dataList, HttpServletRequest request, HttpServletResponse response) {
this.rowName = rowName;
this.dataList = dataList;
this.response = response;
this.request = request;
this.sheetName = sheetName;
}
public ExportExcelStandard(String sheetName, String fileName, String[] rowName, List<LinkedHashMap<String, Object>> dataList, HttpServletRequest request, HttpServletResponse response) {
this.rowName = rowName;
this.dataList = dataList;
this.fileName = fileName;
this.response = response;
this.request = request;
this.sheetName = sheetName;
}
public ExportExcelStandard(String sheetName, String[] rowName, List<LinkedHashMap<String, Object>> dataList, String fileName, String filePath) {
this.rowName = rowName;
this.dataList = dataList;
this.sheetName = sheetName;
this.fileName = fileName;
this.filePath = filePath;
}
public ExportExcelStandard() {
}
// 导出数据
public void exportData() throws Exception {
log.info("excel导出创建work");
SXSSFWorkbook workbook = new SXSSFWorkbook();//声明一个工作薄 Excel 2007 OOXML (.xlsx)格式
SXSSFSheet sheet = workbook.createSheet(sheetName); // 创建表格
for (int i = 1; i < rowName.length; i++) { //根据列名设置每一列的宽度
int length = rowName[i].toString().length();
sheet.setColumnWidth(i, 2 * (length + 1) * 256);
}
// sheet样式定义
CellStyle columnTopStyle = ExportExcelUtil.getColumnTopStyle(workbook, 14); // 头样式
CellStyle columnStyle = ExportExcelUtil.getColumnStyle(workbook, 12); // 标题样式
CellStyle style = ExportExcelUtil.getStyle(workbook, 11); // 单元格样式
CellStyle styleError = ExportExcelUtil.getStyleError(workbook, 11); // 单元格样式
// 产生第一行(列名)
int columnNum = rowName.length; // 表格列的长度
SXSSFRow rowRowName = sheet.createRow(0); // 在第二行创建行
rowRowName.setHeightInPoints(21f);
CellStyle cells = workbook.createCellStyle();
cells.setBottomBorderColor(HSSFColor.BLACK.index);
rowRowName.setRowStyle(cells);
for (int i = 0; i < columnNum; i++) {
SXSSFCell cellRowName = rowRowName.createCell(i);
cellRowName.setCellType(SXSSFCell.CELL_TYPE_STRING); // 单元格类型
XSSFRichTextString text = new XSSFRichTextString(rowName[i]); // 得到列的值
cellRowName.setCellValue(text); // 设置列的值
cellRowName.setCellStyle(columnStyle); // 样式
}
// 产生其它行(将数据列表设置到对应的单元格中)注意:默认添加了第一列的序号,如果不要可以注释掉
for (int i = 0; i < dataList.size(); i++) {
LinkedHashMap<String, Object> linkedHashMapData = dataList.get(i);//遍历每个对象
SXSSFRow row = sheet.createRow(i + 1);//创建所需的行数
row.setHeightInPoints(17.25f);
int j = 0;
Iterator iter = linkedHashMapData.keySet().iterator();
while (iter.hasNext()) {
SXSSFCell cell = null; //设置单元格的数据类型
cell = row.createCell(j, SXSSFCell.CELL_TYPE_STRING);
Object key = iter.next();
Object val = linkedHashMapData.get(key);
Boolean isNum = false;//val是否为数值型
Boolean isInteger=false;//val是否为整数
Boolean isPercent=false;//val是否为百分数
if (val != null || "".equals(val)) {
//判断val是否为数值型
isNum = val.toString().matches("^(-?\\d+)(\\.\\d+)?$");
//判断val是否为整数(小数部分是否为0)
isInteger=val.toString().matches("^[-\\+]?[\\d]*$");
//判断val是否为百分数(是否包含“%”)
isPercent=val.toString().contains("%");
}
//如果单元格内容是数值类型,涉及到金钱(金额、本、利),则设置cell的类型为数值型,设置val的类型为数值类型
if (isNum && !isPercent) {
HSSFDataFormat.getBuiltinFormat("0.00");
if (isInteger) {
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,#0"));//数据格式只显示整数
}else{
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点
}
// 设置单元格内容为double类型
cell.setCellValue(Double.parseDouble(val.toString()));
} else if (null != val && !"".equals(val)){
// 设置单元格内容为字符型
cell.setCellValue(val.toString());
}else{
cell.setCellValue("");
}
// 设置单元格格式
cell.setCellStyle(style);
j++;
}
}
// 让列宽随着导出的列长自动适应,但是对中文支持不是很好 也可能在linux(无图形环境的操作系统)下报错,报错再说
sheet.trackAllColumnsForAutoSizing();
for (int i = 0; i < columnNum; i++) {
sheet.autoSizeColumn(i);
//sheet.setColumnWidth(i, sheet.getColumnWidth(i)*2);//适当再宽点
int colWidth = sheet.getColumnWidth(i)*2;
if(colWidth<255*256){
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}else{
sheet.setColumnWidth(i,6000 );
}
}
log.info("excel导出生成完成");
if (workbook != null) {
// 输出到用户浏览器上
if (null != filePath) {
ExportExcelUtil.saveFile(filePath, fileName, workbook);
} else {
if (workbook != null) {
ExportExcelUtil.responWrite(response, request, workbook,sheetName);
}
}
}
}
}
3.service
public void exportOutsourcingAuditBills(String billMonth, String companyId, HttpServletRequest request, HttpServletResponse response) {
String[] rowName = {"列名1", "列名2", "列名3", "列名4", "列名5", "列名6", "列名7"};
String SheetName = "表名";
String billId = mapper.getBillId(billMonth, companyId);
List<LinkedHashMap<String, Object>> taskStatusList = new ArrayList<>();
if (null != billId) {
taskStatusList = beOutbillDescMapper.getOutsourcingAuditBills(billId);// 得到要展示的值
}
ExportExcelStandard exportExcel = new ExportExcelStandard(SheetName, rowName, taskStatusList, request, response);// 加载导出文件a
try {
exportExcel.exportData();// 调导出的方法
} catch (Exception e) {
log.info(e + "");
}
}
4.controller
@GetMapping("/export/exportFile/exportOutsourcingAuditBills")
public void exportOutsourcingAuditBills(String billMonth,String companyId,HttpServletRequest request, HttpServletResponse response){
try {
service.exportOutsourcingAuditBills(billMonth,companyId,request,response);
} catch (Exception e) {
log.error("/export/exportFile/exportOutsourcingAuditBills"+e);
}
}
5.前端调用导出
window.location.href = getApi()+"report/export/exportFile/exportOutsourcingAuditBills?billMonth="+billMonth+"&companyId="+companyId