@Slf4j
public class ExcelUtils {
private final static String xls = "xls";
private final static String xlsx = "xlsx";
private final static String csv = "csv";
/**
* 校验Excel
*
* @param file 文件
*/
public static void checkFile(MultipartFile file) {
//判断文件是否存在
if (null == file) {
log.error("文件不存在！");
throw new BusinessException("文件不存在");
}
//获得文件名
String fileName = file.getOriginalFilename();
if (StringUtils.isBlank(fileName)) {
log.error("文件名称不存在！");
throw new BusinessException("文件名称不存在");
}
//判断文件是否是excel文件
if (!fileName.endsWith(xls) && !fileName.endsWith(xlsx) && !fileName.endsWith(csv)) {
log.error("文件不是excel文件");
throw new BusinessException("文件不是excel文件");
}
}
/**
* 导出Excel
*
* @param workbook
* @param response
* @return
*/
public static void exportExcelName(Workbook workbook, HttpServletResponse response, String fileName) {
try {
OutputStream out = response.getOutputStream();
String fileNameStr = fileName + ".xls";
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileNameStr, "utf-8"));
BufferedOutputStream bufferedOutPut = new BufferedOutputStream(out);
bufferedOutPut.flush();
workbook.write(bufferedOutPut);
bufferedOutPut.close();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 上传Excel
*
* @param
* @param titleRows
* @param headerRows
* @param pojoClass
* @param <T>
* @return
*/
public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
if (inputStream == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list;
try {
list = ExcelImportUtil.importExcel(inputStream, pojoClass, params);
} catch (Exception e) {
e.printStackTrace();
log.error("Excel导入异常", e);
throw new BusinessException("Excel导入异常");
}
return list;
}
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
log.error("[monitor][IO][表单功能]", e);
}
}
/**
* 下载
*
* @param fileName 文件名称
* @param sheetName sheet名称
* @param title 表格标题
* @param values 内容
* @return Excel
*/
public static ResponseEntity<Resource> download(String fileName, String sheetName, String[] title, String[][] values, boolean cvs) {
ByteArrayOutputStream bos = null;
String expandedName = ".xlsx";
String contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
if (cvs) {
bos = createCvsOutStream(title, values);
expandedName = ".csv";
contentType = "text/csv";
} else {
Workbook workbook = getWorkbook(sheetName, title, values);
try {
bos = new ByteArrayOutputStream();
workbook.write(bos);
} catch (Exception e) {
log.error("get workbook", e);
}
}
HttpHeaders headers = new HttpHeaders();
headers.add("Cache-Control", "no-cache, no-store, must-revalidate");
headers.add("Pragma", "no-cache");
headers.add("Expires", "0");
headers.add("charset", "utf-8");
try {
//设置下载文件名
fileName = new String((fileName + expandedName).getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
headers.add("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
assert bos != null;
Resource resource = new InputStreamResource(new ByteArrayInputStream(bos.toByteArray()));
return ResponseEntity.ok().headers(headers).contentType(MediaType.parseMediaType(contentType)).body(resource);
} catch (Exception e) {
log.error("download", e);
}
return ResponseEntity.noContent().build();
}
/**
* 下载文件流
*
* @param sheetName sheet名称
* @param title 表格标题
* @param values 内容
* @return ByteArrayInputStream
*/
public static ByteArrayInputStream downloadSteam(String sheetName, String[] title, String[][] values, boolean cvs) {
ByteArrayOutputStream bos = null;
Workbook workbook = getWorkbook(sheetName, title, values);
try {
bos = new ByteArrayOutputStream();
workbook.write(bos);
} catch (Exception e) {
log.error("get workbook", e);
}
assert bos != null;
return new ByteArrayInputStream(bos.toByteArray());
}
/**
* 导出Excel
*
* @param sheetName sheet名称
* @param title 标题
* @param values 内容
* @return excel
*/
public static Workbook getWorkbook(String sheetName, String[] title, String[][] values) {
// 第一步，创建一个Workbook，对应一个Excel文件
Workbook wb = new SXSSFWorkbook();
// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.createSheet(sheetName);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
Row row = sheet.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
CellStyle style = wb.createCellStyle();
//声明列对象
Cell cell = null;
//创建标题
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
//创建内容
for (int i = 0; i < values.length; i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < values[i].length; j++) {
//将内容按顺序赋给对应的列对象
row.createCell(j).setCellValue(values[i][j]);
}
}
return wb;
}
public static ByteArrayOutputStream createCvsOutStream(String[] titles, String[][] values) {
ByteArrayOutputStream bos = null;
try {
bos = new ByteArrayOutputStream();
StringBuilder datas = new StringBuilder();
// 写入文件头部
int i = 0;
for (String title : titles) {
datas.append("\"").append(title).append("\"");
if (i + 1 < titles.length) {
datas.append(",");
}
i++;
}
datas.append("\r\n");
// 写入文件内容
for (String[] row : values) {
i = 0;
for (String cell : row) {
datas.append("\"").append(cell).append("\"");
if (i + 1 < row.length) {
datas.append(",");
}
i++;
}
datas.append("\r\n");
}
bos.write(datas.toString().getBytes("GB2312"));
} catch (Exception e) {
e.printStackTrace();
}
return bos;
}
}导出模板
public void wmPickUpReport(String startTime, String endTime, HttpServletResponse response) {
// 数据源
List<ExportPickUpExcel> exportPickUpExcels = new ArrayList<>();
try {
// 每次只处理的条数
int pointsDataLimit = 10;
// 临时存储数据 - 集合
List<CarNavigation> navigationList = new ArrayList<>();
// 分批次处理
for (int i = 0; i < carNavigationList.size(); i++) {
// 将10条数据加到临时集合中处理
navigationList.add(carNavigationList.get(i));
if (pointsDataLimit == navigationList.size() || i == carNavigationList.size() - 1) {
// 业务功能呢
for (CarNavigation navigation : navigationList) {
ExportPickUpExcel excel = new ExportPickUpExcel();
exportPickUpExcels.add(excel);
}
// 清空临时空间 释放内存
log.info("clear informationList =======>>>>> " + navigationList.size() + " navigation Data!");
navigationList.clear();
}
}
// 数据导出
//FileWithExcelUtil.exportExcel(exportPickUpExcels, "数据报表", "数据报表", ExportPickUpExcel.class, "数据报表.xls", response);
ExportParams exportParams = new ExportParams();
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, ExportPickUpExcel.class, exportPickUpExcels);
ExcelUtils.exportExcelName(workbook, response, "威马接送数据报表");
workbook.write(response.getOutputStream());
response.flushBuffer();
log.info("数据导出成功！！");
} catch (Exception e) {
log.info("exportExcel", e);
log.info("数据导出失败！！");
}
}
