- Excel工具类
package com.szjzscx.aqgl.utils;
import com.bidb.core.entity.BaseEntity;
import com.szjzscx.aqgl.entity.aqjsjd.AqJsjdt1151Safetytechnicaldisclosure;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;
import org.springframework.web.bind.annotation.GetMapping;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;
@Component
public class FileUtil {
public static String ROW_NUM = "RN_XLSX";
/**
* @author thenstro
* 排放记录数据导出
* @param response
* @param fileName 文件名+内容标题
* @param headers 列标题数组
* @param titlesFields 每列对应属性名
* @param records 导出的原生数据(若导出列包含父类属性应该在子类重新定义)
* */
public static void exportRecordsAsExcel(HttpServletResponse response, String fileName, String[] headers, String[] titlesFields, List<?> records) throws IOException {
Assert.hasLength(fileName, "文件名不为空");
if(headers == null || headers.length < 1 || titlesFields == null && titlesFields.length < 1 || records == null || headers.length != titlesFields.length){
System.err.println("参数异常,请保证所有参数非空且标题数组与标题字段数组容量一致");
return;
}
//工作簿excel
XSSFWorkbook workbook = new XSSFWorkbook();
//工作表(sheet)
XSSFSheet sheet = workbook.createSheet("排放记录");
//文件名
String hStr = "";
if(!fileName.contains(".xlsx")){
hStr = fileName;
fileName += ".xlsx";
}else{
hStr = fileName.split(".xlsx")[0];
}
int width = hStr.length() * 256;
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);
//XSSFFont font = workbook.createFont();
/*
* 表头标题
* */
XSSFRow rowHeader = sheet.createRow(0);
rowHeader.setHeight((short)1500);
XSSFCell cellHeader = rowHeader.createCell(0);
cellHeader.setCellValue(hStr);
cellHeader.setCellStyle(cellStyle);
//合并单元格CellRangeAddress r = new CellRangeAddress(startRow, endRow, startCol, endCol);
CellRangeAddress range = new CellRangeAddress(0, 0, 0, 4);
sheet.addMergedRegion(range);
//序号、垃圾排放量、垃圾类型、运输时间、运输成本
XSSFRow rowTitle = sheet.createRow(1);
for(int i = 0;i < headers.length;i++){
XSSFCell cell = rowTitle.createCell(i);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
int rowNum = 2;
for (Object r : records) {
XSSFRow row1 = sheet.createRow(rowNum);
/*row1.createCell(0).setCellValue(rowNum - 1);
row1.createCell(1).setCellValue(r.getDcc022Value1() + r.getDcc022Value1Unit());
row1.createCell(2).setCellValue(r.getDcc022Value3());
row1.createCell(3).setCellValue(r.getDcc022Value2());
row1.createCell(4).setCellValue(r.getDcc022Value4() + r.getDcc022Value4Unit());*/
for(int i = 0; i < titlesFields.length; i ++){
if(titlesFields[i].equals(ROW_NUM)){
row1.createCell(i).setCellValue(rowNum - 1);
}else{
row1.createCell(i).setCellValue(getFieldValueByClass(r, titlesFields[i]));
}
}
rowNum ++;
}
for (int k = 0; k < headers.length; k++) {
sheet.autoSizeColumn(k);
}
//自动调整列宽
setSizeColumn(sheet, headers.length);
//response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName.toString(),"UTF-8"));
response.addHeader("Access-Control-Expose-Headers", "Content-disposition");
response.flushBuffer();
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
}
// excel列自适应宽度(中文支持)
private static void setSizeColumn(XSSFSheet sheet, int size) {
for (int columnNum = 0; columnNum < size; columnNum ++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum ++) {
XSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
XSSFCell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == CellType.STRING.getCode()) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256);
}
}
/**
* 属性取值
* @param t 实例
* @param field 取值字段
* @return
*/
private static String getFieldValueByClass(Object t, String field) {
Class<?> aClass = t.getClass();
try {
Field f = aClass.getDeclaredField(field);
f.setAccessible(true);
return f.get(t).toString();
} catch (Exception e) {
e.printStackTrace();
}
return "";
}
}
- 接口调用
public void exportExcel(Search search, HttpServletResponse response) throws IOException {
List<AqWzglt1451Emergencysupplies> list = service.findByFilters(search);
String fileName = "应急物资导出"+ DateUtils.getCurrentDate().getTime();
String [] headers = new String[]{"序号","单位名称","项目名称","应急物品名称","规格/型号","数量","单位","存放地点","保管人","编制","物资存放时间","备注"};
String [] titleFields = new String[]{FileUtil.ROW_NUM,"tyCompanyname","tyProjectname","aqWzglc1451Itemname","aqWzglc1451Specifications","aqWzglc1451Quantity","aqWzglc1451Company","aqWzglc1451Storagelocation","aqWzglc1451Custodian","aqWzglc1451Organization","aqWzglc1451Storagetime","aqWzglc1451Remarks"};
FileUtil.exportRecordsAsExcel(response,fileName,headers,titleFields,list);
}
- 导出的Excel数据
总结:本人实测好用拿出来分享一下,欢迎各位大佬提建议。Thanks!