0.使用jar:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>最终结果,生成excel文件,上传到oss再下载,可以留档,但是会慢些;
1.controller
/**
* 条件下载账单信息excel
*
* @param orderCode 检索条件
* @param billDtStart 检索条件
* @param billDtEnd 检索条件
* @param state 检索条件
* @param corganCodes 检索条件
* @param status 检索条件
* @param flag 检索条件
* @param userNameLike 检索条件
* @param capitalSide 资金方
* @param response 相应信息
*/
@GetMapping("/downloadBillExcel")
public void downloadBillExcel(@RequestParam(required = false) String orderCode,
@RequestParam(required = false) String billDtStart,
@RequestParam(required = false) String billDtEnd,
@RequestParam(required = false) String state,
@RequestParam(required = false) String corganCodes,
@RequestParam(required = false) String status,
@RequestParam(required = false) String flag,
@RequestParam(required = false) String userNameLike,
@RequestParam(required = false) String capitalSide, HttpServletResponse response) {
log.info(
"条件下载账单信息excel-orderCode:{},billDtStart:{},billDtEnd:{},state:{},corganCodes:{},status:{},flag:{},userNameLike:{}",
orderCode, billDtStart, billDtEnd, state, corganCodes, status, flag, userNameLike);
PageMapperDto mapperDto = new PageMapperDto();
mapperDto.setPageStart(null);
mapperDto.setPageSize(null);
mapperDto.setUserName(userNameLike);
mapperDto.setOrderCode(orderCode);
mapperDto.setCorganCodeListByParam(VehicleUtils.stringToList(corganCodes));
mapperDto.setFlagList(VehicleUtils.stringToList(flag));
mapperDto.setStateList(VehicleUtils.stringToList(state));
mapperDto.setStatusList(VehicleUtils.stringToList(status));
mapperDto.setCapitalSideList(VehicleUtils.stringToList(capitalSide));
if (StringUtils.isNotBlank(billDtStart) && StringUtils.isNotBlank(billDtEnd)) {
mapperDto
.setBillDtStart(DateUtils.date2String(DateUtils.parseTimestampToDate(Long.parseLong(billDtStart))));
mapperDto.setBillDtEnd(DateUtils.date2String(DateUtils.parseTimestampToDate(Long.parseLong(billDtEnd))));
}
log.info("条件下载账单信息mapperDto:{}", JSON.toJSON(mapperDto));
try {
File file = orderBillingService.genFileByCondition(mapperDto);
commonService.downloadFile(file, response);
} catch (Exception e) {
log.error("Exception:{}", JSON.toJSON(e));
}
}2.serviceImpl
/**
* 条件获取账单excel文件
*
* @param mapperDto 检索条件
* @return excel文件地址
*/
@Override
public File genFileByCondition(PageMapperDto mapperDto) {
List<OrderBillingPageResponseDto> responseDtoList = crudMapper.findOrderBillingPage(mapperDto);
log.info("条件查询账单:{}", responseDtoList.size());
// 文件存放目录
String dir = tempFilePath;
BankUtils.mkDir(dir);
String billExcelPath = String.format(OrderBillingConstants.STRING_FORMAT_4, dir,
OrderBillingConstants.DOWNLOAD_EXCEL_NAME_ORDER_BILL, BankUtils.getCurrentDateTime(),
OrderBillingConstants.EXCEL_FORMAT_XLS);
log.info("准备生成的账单excel文件地址:{}", billExcelPath);
// sheet页名称
String sheetName = OrderBillingConstants.DOWNLOAD_EXCEL_NAME_ORDER_BILL;
// 默认列宽
String defaultColumnLength = OrderBillingConstants.STRING_THIRTY;
// 列宽
List<String> columnLengthList = new ArrayList<>();
// 标题名称
Object[] nameObj = Stream
.of("订单编号", "账单期数", "账单日期", "承租人姓名", "申请时间", "分公司", "分期金额", "应还本金", "应还利息", "逾期违约金", "逾期违约罚息", "代扣结果",
"资方业务状态", "账单标记", "账单状态", "是否已逾期", "实际还款日期", "已还月租", "已还本金", "已还利息", "已还违约金", "已还罚息", "待还月租",
"待还本金", "待还利息", "待还违约金", "待还罚息")
.toArray();
String[] title = new String[nameObj.length];
for (int i = 0; i < title.length; i++) {
title[i] = nameObj[i].toString();
if (i == 0 || i == 4 || i == 5) {
columnLengthList.add("30");
} else {
columnLengthList.add("20");
}
}
int size = responseDtoList.size();
String[][] context = new String[size][title.length];
if (CollectionUtils.isNotEmpty(responseDtoList)) {
responseDtoList.sort(Comparator.comparing(OrderBillingPageResponseDto::getId));
for (int i = 0; i < size; i++) {
OrderBillingPageResponseDto responseDto = responseDtoList.get(i);
log.info("********responseDto:{}", JSON.toJSON(responseDto));
for (int j = 0; j < title.length; j++) {
if (j == 0) {
// 订单编号
context[i][j] = responseDto.getOrderCode();
} else if (j == 1) {
// 账单期数
context[i][j] = responseDto.getBillPeriod().toString();
} else if (j == 2) {
// 账单日期
context[i][j] = DateUtils.formatDate(responseDto.getBillDt(),
OrderBillingConstants.TIME_FORMAT);
} else if (j == 3) {
// 承租人姓名
context[i][j] = String.valueOf(responseDto.getUserName());
} else if (j == 4) {
// 申请时间
if (null != responseDto.getApplyTime()) {
context[i][j] = DateUtils.formatDate(responseDto.getApplyTime(),
VehicleConstants.TIANYI_TIME_FORMAT);
}
} else if (j == 5) {
// 分公司
context[i][j] = responseDto.getOrganName();
} else if (j == 6) {
// 分期金额
context[i][j] = String.valueOf(responseDto.getBillFee());
} else if (j == 7) {
// 账单应还本金
context[i][j] = String.valueOf(responseDto.getBillCapital());
} else if (j == 8) {
// 账单应还利息
context[i][j] = String.valueOf(responseDto.getBillInterest());
} else if (j == 9) {
// 逾期违约金
context[i][j] = String.valueOf(responseDto.getOverdueContractAmt());
} else if (j == 10) {
// 逾期罚息
context[i][j] = String.valueOf(responseDto.getOverduePenalty());
} else if (j == 11) {
// 代扣结果
context[i][j] = responseDto.getWithholdResult();
} else if (j == 12) {
BillStateEnum billStateEnum = BillStateEnum.getEnumByCode(responseDto.getState());
// 资方业务状态
if (billStateEnum != null) {
context[i][j] = billStateEnum.getMsg();
}
} else if (j == 13) {
// 账单标记
BillFlagEnum billFlagEnum = BillFlagEnum.getEnumByCode(responseDto.getFlag());
if (billFlagEnum != null) {
context[i][j] = billFlagEnum.getMsg();
}
} else if (j == 14) {
// 状态
BillStatusEnum billStatusEnum = BillStatusEnum.getEnumByCode(responseDto.getStatus());
if (billStatusEnum != null) {
context[i][j] = billStatusEnum.getMsg();
}
} else if (j == 15) {
// 是否已逾期
YesNoEnum yesNoEnum = YesNoEnum.getEnumByCode(responseDto.getIsOverdue());
if (yesNoEnum != null) {
context[i][j] = yesNoEnum.getMsg();
}
} else if (j == 16) {
// 实际还款日期
if (null != responseDto.getBillRepayDt()) {
context[i][j] = DateUtils.formatDate(responseDto.getBillRepayDt(),
OrderBillingConstants.TIME_FORMAT);
}
} else if (j == 17) {
// 已还月租
context[i][j] = responseDto.getPaidFee().toString();
} else if (j == 18) {
// 已还本金
context[i][j] = responseDto.getBillCapital()
.subtract(responseDto.getUnpaidCapital())
.toString();
} else if (j == 19) {
// 已还利息
context[i][j] = responseDto.getBillInterest()
.subtract(responseDto.getUnpaidIntrest())
.toString();
} else if (j == 20) {
// 已还违约金
context[i][j] = responseDto.getOverdueContractAmt()
.subtract(responseDto.getUnpaidContractAmt())
.toString();
} else if (j == 21) {
// 已还罚息
context[i][j] = responseDto.getOverduePenalty()
.subtract(responseDto.getUnpaidPenalty())
.toString();
} else if (j == 22) {
// 待还月租
context[i][j] = responseDto.getUnpaidFee().toString();
} else if (j == 23) {
// 待还本金
context[i][j] = responseDto.getUnpaidCapital().toString();
} else if (j == 24) {
// 待还利息
context[i][j] = responseDto.getUnpaidIntrest().toString();
} else if (j == 25) {
// 待还违约金
context[i][j] = responseDto.getUnpaidContractAmt().toString();
} else {
// 待还罚息
context[i][j] = responseDto.getUnpaidPenalty().toString();
}
}
}
}
List<Label> labelList = new ArrayList<>();
List<ExcelMergeCellsDto> mergeCellsDtoList = new ArrayList<>();
// 生成excel文件
ExcelUtils.exportExcel(billExcelPath, sheetName, defaultColumnLength, columnLengthList, title, context,
labelList, mergeCellsDtoList);
return new File(billExcelPath);
}3. ExcelUtils
import java.io.File;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;
import com.yifenqi.dto.ExcelMergeCellsDto;
import jxl.CellView;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import lombok.extern.slf4j.Slf4j;
/**
* Excel处理工具类
*/
@Slf4j
public class ExcelUtils {
/**
* WORD_TO_PDF_OPERAND
*/
private static final Integer WORD_TO_PDF_OPERAND = 17;
/**
* PPT_TO_PDF_OPERAND
*/
private static final Integer PPT_TO_PDF_OPERAND = 32;
/**
* EXCEL_TO_PDF_OPERAND
*/
private static final Integer EXCEL_TO_PDF_OPERAND = 0;
/**
* 生成Excel到filePath路径中
*
* @param filePath 生成Excel的保存路径
* @param sheetName Excel中sheet名
* @param defaultColumnLength 整个Excel的默认单元格宽度
* @param columnLengthList 定义每个列宽
* @param title 标题
* @param context 内容
* @param labelList 需要合并处文字设置
* @param mergeCellsDtoList 单元格合并坐标列表
*/
public static void exportExcel(String filePath, String sheetName, String defaultColumnLength,
List<String> columnLengthList, String[] title, String[][] context,
List<Label> labelList, List<ExcelMergeCellsDto> mergeCellsDtoList) {
exportExcel(filePath, sheetName, defaultColumnLength, columnLengthList, title, context, labelList,
mergeCellsDtoList, null);
}
/**
* 生成Excel到filePath路径中
*
* @param filePath 生成Excel的保存路径
* @param sheetName Excel中sheet名
* @param defaultColumnLength 整个Excel的默认单元格宽度
* @param columnLengthList 定义每个列宽
* @param title 标题
* @param context 内容
* @param labelList 需要合并处文字设置
* @param mergeCellsDtoList 单元格合并坐标列表
* @param rowHeightMap 定义行高
*/
public static void exportExcel(String filePath, String sheetName, String defaultColumnLength,
List<String> columnLengthList, String[] title, String[][] context,
List<Label> labelList, List<ExcelMergeCellsDto> mergeCellsDtoList,
Map<Integer, Integer> rowHeightMap) {
//操作执行
try {
//t.xls为要新建的文件名
WritableWorkbook book = Workbook.createWorkbook(new File(filePath));
//生成名为“第一页”的工作表，参数0表示这是第一页
WritableSheet sheet = book.createSheet(sheetName, 0);
if (!StringUtils.isBlank(defaultColumnLength)) {
sheet.getSettings().setDefaultColumnWidth(Integer.valueOf(defaultColumnLength));
}
//Excel相关默认设置
WritableCellFormat titleFormat = genTitleFormat();
WritableCellFormat contentFormat = genContentFormat();
CellView cellView = new CellView();
cellView.setAutosize(true);
//写入标题
for (int i = 0; i < title.length; i++) {
//title
sheet.addCell(new Label(i, 0, title[i], titleFormat));
if (!CollectionUtils.isEmpty(columnLengthList) && columnLengthList.size() >= title.length) {
sheet.setColumnView(i, Integer.valueOf(columnLengthList.get(i)));
}
}
//Excel的内容
//context
for (int i = 0; i < context.length; i++) {
for (int j = 0; j < context[i].length; j++) {
sheet.addCell(new Label(j, i + 1, context[i][j], contentFormat));
}
}
//合并单元格
if (!CollectionUtils.isEmpty(labelList)) {
for (int i = 0; i < labelList.size(); i++) {
addCell(sheet, labelList.get(i));
}
}
if (!CollectionUtils.isEmpty(mergeCellsDtoList)) {
for (int i = 0; i < mergeCellsDtoList.size(); i++) {
ExcelMergeCellsDto record = mergeCellsDtoList.get(i);
mergeCells(sheet, record.getM(), record.getN(), record.getP(), record.getQ());
}
}
//设置行高
if (null != rowHeightMap) {
for (Map.Entry<Integer, Integer> entry : rowHeightMap.entrySet()) {
sheet.setRowView(entry.getKey(), entry.getValue());
}
}
// 空白行，列删除
removeColumns(sheet);
//写入数据
book.write();
//关闭文件
book.close();
} catch (Exception e) {
log.error("exportExcel error{}", e);
}
}
/**
* 组装数据
*
* @param book 文件
* @param sheetName Excel中sheet名
* @param defaultColumnLength 整个Excel的默认单元格宽度
* @param columnLengthList 定义每个列宽
* @param title 标题
* @param context 内容
* @param labelList 需要合并处文字设置
* @param mergeCellsDtoList 单元格合并坐标列表
*/
public static void exportExcelData(WritableWorkbook book, String sheetName, String defaultColumnLength,
List<String> columnLengthList, String[] title, String[][] context,
List<Label> labelList, List<ExcelMergeCellsDto> mergeCellsDtoList) {
exportExcelData(book, sheetName, defaultColumnLength, columnLengthList, title, context, labelList,
mergeCellsDtoList, null);
}
/**
* 组装Excel数据
*
* @param book ExcelBook
* @param sheetName Excel中sheet名
* @param defaultColumnLength 整个Excel的默认单元格宽度
* @param columnLengthList 定义每个列宽
* @param title 标题
* @param context 内容
* @param labelList 需要合并处文字设置
* @param mergeCellsDtoList 单元格合并坐标列表
* @param rowHeightMap 定义行高
*/
public static void exportExcelData(WritableWorkbook book, String sheetName, String defaultColumnLength,
List<String> columnLengthList, String[] title, String[][] context,
List<Label> labelList, List<ExcelMergeCellsDto> mergeCellsDtoList,
Map<Integer, Integer> rowHeightMap) {
//操作执行
try {
//生成名为“第一页”的工作表，参数0表示这是第一页
WritableSheet sheet = book.createSheet(sheetName, 0);
if (!StringUtils.isBlank(defaultColumnLength)) {
sheet.getSettings().setDefaultColumnWidth(Integer.valueOf(defaultColumnLength));
}
//Excel相关默认设置
WritableCellFormat titleFormat = genTitleFormat();
WritableCellFormat contentFormat = genContentFormat();
CellView cellView = new CellView();
cellView.setAutosize(true);
//写入标题
for (int i = 0; i < title.length; i++) {
//title
sheet.addCell(new Label(i, 0, title[i], titleFormat));
if (!CollectionUtils.isEmpty(columnLengthList) && columnLengthList.size() >= title.length) {
sheet.setColumnView(i, Integer.valueOf(columnLengthList.get(i)));
}
}
//Excel的内容
//context
for (int i = 0; i < context.length; i++) {
for (int j = 0; j < context[i].length; j++) {
sheet.addCell(new Label(j, i + 1, context[i][j], contentFormat));
}
}
//合并单元格
if (!CollectionUtils.isEmpty(labelList)) {
for (int i = 0; i < labelList.size(); i++) {
addCell(sheet, labelList.get(i));
}
}
if (!CollectionUtils.isEmpty(mergeCellsDtoList)) {
for (int i = 0; i < mergeCellsDtoList.size(); i++) {
ExcelMergeCellsDto record = mergeCellsDtoList.get(i);
mergeCells(sheet, record.getM(), record.getN(), record.getP(), record.getQ());
}
}
//设置行高
if (null != rowHeightMap) {
for (Map.Entry<Integer, Integer> entry : rowHeightMap.entrySet()) {
sheet.setRowView(entry.getKey(), entry.getValue());
}
}
// 空白行，列删除
removeColumns(sheet);
} catch (Exception e) {
log.error("exportExcel error{}", e);
}
}
/**
* 特殊单元格数据设置
*
* @param sheet Excel中的sheet
* @param label 页面
*/
public static void addCell(WritableSheet sheet, Label label) {
try {
sheet.addCell(label);
} catch (WriteException e) {
log.error("addCell error{}", e);
}
}
/**
* 合并单元格.合并既可以是横向的，也可以是纵向的 WritableSheet.mergeCells(int m,int n,int p,int
* q); 表示由(m,n)到(p,q)的单元格组成的矩形区域合并
*
* @param sheet Excel中的sheet
* @param m 开始单元格x坐标
* @param n 开始单元格y坐标
* @param p 结束单元格x坐标
* @param q 结束单元格y坐标
*/
public static void mergeCells(WritableSheet sheet, int m, int n, int p, int q) {
try {
sheet.mergeCells(m, n, p, q);
} catch (WriteException e) {
log.error("mergeCells error{}", e);
}
}
/**
* Excel中的标题格式
*
* @return 标题格式
*/
public static WritableCellFormat genTitleFormat() {
WritableCellFormat cellFormat = null;
//设置垂直居中;
try {
//设置字体;
WritableFont fontTitle = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
cellFormat = new WritableCellFormat(fontTitle);
//设置背景颜色;
cellFormat.setBackground(Colour.GRAY_25);
//设置边框;
cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
//设置自动换行;
cellFormat.setWrap(true);
//设置文字对齐方式;
cellFormat.setAlignment(Alignment.CENTRE);
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
} catch (WriteException e) {
log.error("genTitleFormat Exception is{}", e);
}
return cellFormat;
}
/**
* Excel中的内容格式
*
* @return 内容格式
*/
public static WritableCellFormat genContentFormat() {
WritableCellFormat cellFormat = null;
//设置垂直居中;
try {
//设置字体;
WritableFont fontTitle = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
cellFormat = new WritableCellFormat(fontTitle);
//设置背景颜色;
cellFormat.setBackground(Colour.WHITE);
//设置边框;
cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
//设置自动换行;
cellFormat.setWrap(true);
//设置文字对齐方式;
cellFormat.setAlignment(Alignment.LEFT);
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
} catch (WriteException e) {
log.error("genContentFormat Exception is{}", e);
}
return cellFormat;
}
/**
* 删除空白行
*
* @param sheet Excel中的sheet
*/
public static void removeColumns(WritableSheet sheet) {
//列数
int rsCols = sheet.getColumns();
//行数
int rsRows = sheet.getRows();
int nullCellNum;
//统计行中为空的单元格数
for (int i = 1; i < rsRows; i++) {
nullCellNum = 0;
for (int j = 0; j < rsCols; j++) {
String val = sheet.getCell(j, i).getContents();
val = StringUtils.trimToEmpty(val);
if (StringUtils.isBlank(val)) {
nullCellNum++;
}
//如果nullCellNum大于或等于总的列数
if (nullCellNum >= rsCols) {
sheet.removeColumn(j);
}
}
}
}
}4.commonServiceImpl
/**
* 下载单个文件
*
* @param file 文件信息
* @param response 响应信息
*/
@Override
public void downloadFile(File file, HttpServletResponse response) {
long start = System.currentTimeMillis();
try {
int fileBufferSize = CommConstant.FILE_BUFFER_SIZE_DEFAULT;
DictCode dictCode = dictCodeService.findByGroupAndCodeNoCache(CommConstant.FILE_BUFFER_SIZE,
CommonFlag.Y.getCode());
if (dictCode != null) {
fileBufferSize = Integer.valueOf(dictCode.getName());
}
if (file == null) {
response.getWriter().println("file is null , please check file!");
return;
} else if (!file.exists() || file.isDirectory()) {
response.getWriter().println("file is error , please check file!");
return;
}
log.error("下载-{}-开始", file.getName());
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + new String((file.getName()).getBytes("gb2312"), "ISO8859-1"));
InputStream in = new FileInputStream(file);
OutputStream out = response.getOutputStream();
byte[] car = new byte[fileBufferSize];
int l;
while ((l = in.read(car)) != -1) {
out.write(car, 0, l);
}
out.flush();
out.close();
in.close();
log.error("下载-完成-文件大小:{} , fileBufferSize:{} byte, 耗时:{} ms", VehicleUtils.getFileSize(file),
fileBufferSize, System.currentTimeMillis() - start);
boolean deleteResult = IOUtils.deleteDir(file);
log.error("下载-完成-删除本地文件:{}", deleteResult);
} catch (Exception e) {
log.error("下载-异常:{}", JSON.toJSON(e));
}
}5.IOUtils
/**
* 递归删除目录下的所有文件及子目录下所有文件
*
* @param dir 将要删除的文件目录
* @return boolean Returns "true" if all deletions were successful. If a
* deletion fails, the method stops attempting to delete and returns
* "false".
*/
public static boolean deleteDir(File dir) {
if (dir.isDirectory()) {
String[] children = dir.list();
//递归删除目录中的子目录下
for (int i = 0; i < children.length; i++) {
boolean success = deleteDir(new File(dir, children[i]));
if (!success) {
return false;
}
}
}
// 目录此时为空，可以删除
return dir.delete();
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
