poi导出案例
- 一般导出(不合并单元格)
public void downloadOrdersUnSettlement(HttpServletResponse response, List<OrderSalesRecords> dataList) {
// 用于金额保留两位小数的格式转换
DecimalFormat df = new DecimalFormat("#.00");
String path = "tempfile" + File.separator + "temp-unSettlement.xlsx";
XSSFWorkbook book = null;
OutputStream out = null;
String fpath = "tempfile" + File.separator + UUID.randomUUID().toString() + ".xlsx";
File newFile = new File(fpath);
try {
File file = new File(path);
Files.copy(file.toPath(), newFile.toPath());
book = new XSSFWorkbook(new FileInputStream(newFile));
XSSFSheet sheet = book.getSheetAt(0);
int lastRow = sheet.getLastRowNum();
for (int i = 1; i <= lastRow; i++) {
sheet.removeRow(sheet.getRow(i));
}
if (MyListUtils.isNotEmpty(dataList)) {
int rowNum = 1;
for (OrderSalesRecords e : dataList) {
XSSFRow row = sheet.createRow(rowNum++);
if (e.getOrderNumber() != null) {
row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(e.getOrderNumber());
}
if (e.getOrderCtime() != null) {
row.createCell(1, Cell.CELL_TYPE_STRING).setCellValue(e.getOrderCtime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
}
if (e.getSalesAmount() != null) {
Double value = e.getSalesAmount() / 100.0;
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(2, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
}
if (e.getDiscount() != null) {
Double value = e.getDiscount() / 100.0;
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(3, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
}
if (e.getServiceFee() != null) {
Double value = e.getServiceFee() / 100.0;
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(4, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
}
if (e.getDistrib() != null) {
Double value = e.getDistrib() / 100.0;
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(5, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
}
if (e.getSettlementAmount() != null) {
Double value = e.getSettlementAmount() / 100.0;
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(6, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
}
}
}
out = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=orders.xlsx");
response.setContentType("application/vnd.ms-excel");
book.write(out);
out.close();
} catch (Exception e) {
log.error("导出文件异常", e);
} finally {
try {
if (null != out)
out.close();
if (null != newFile)
newFile.delete();
} catch (IOException e1) {
}
}
}
2 合并单元格导出
/**
* 微信流水导出
*
* @param response
* @param dataList
*/
public void downloadFlowExcel(HttpServletResponse response, List<FlowParams> dataList) {
// 用于金额保留两位小数的格式转换
DecimalFormat df = new DecimalFormat("#.00");
String path = "tempfile" + File.separator + "temp-flow.xlsx";
XSSFWorkbook book = null;
OutputStream out = null;
String fpath = "tempfile" + File.separator + UUID.randomUUID().toString() + ".xlsx";
File newFile = new File(fpath);
try {
File file = new File(path);
Files.copy(file.toPath(), newFile.toPath());
book = new XSSFWorkbook(new FileInputStream(newFile));
XSSFSheet sheet = book.getSheetAt(0);
int lastRow = sheet.getLastRowNum();
for (int i = 1; i <= lastRow; i++) {
sheet.removeRow(sheet.getRow(i));
}
if (MyListUtils.isNotEmpty(dataList)) {
int rowNum = 1;
for (FlowParams e : dataList) {
if (e.getOrders().size() > 0) {
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 2, 2));
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 3, 3));
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 4, 4));
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 5, 5));
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + e.getOrders().size() - 1, 6, 6));
}
XSSFRow row = sheet.createRow(rowNum++);
if (MyStringUtils.isNotEmpty(e.getFlowNumber())) {
row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(e.getFlowNumber());
}
if (e.getPayTime() != null) {
row.createCell(1, Cell.CELL_TYPE_STRING).setCellValue(e.getPayTime().toString().replace("T", " "));
}
if (e.getRefundTime() != null) {
row.createCell(2, Cell.CELL_TYPE_STRING).setCellValue(e.getRefundTime().replaceAll("T", " "));
}
if (MyStringUtils.isNotEmpty(e.getInAmount())) {
Double value = Double.valueOf(e.getInAmount());
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(3, Cell.CELL_TYPE_STRING).setCellValue(last);
} else {
row.createCell(3, Cell.CELL_TYPE_STRING).setCellValue(0);
}
if (MyStringUtils.isNotEmpty(e.getOutAmount())) {
Double value = Double.valueOf(e.getOutAmount());
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(4, Cell.CELL_TYPE_STRING).setCellValue(last);
} else {
row.createCell(4, Cell.CELL_TYPE_STRING).setCellValue(0);
}
if (MyStringUtils.isNotEmpty(e.getServiceFee())) {
row.createCell(5, Cell.CELL_TYPE_NUMERIC).setCellValue(e.getServiceFee());
} else {
row.createCell(5, Cell.CELL_TYPE_NUMERIC).setCellValue(0);
}
if (MyStringUtils.isNotEmpty(e.getWechatOrderId()))
row.createCell(6, Cell.CELL_TYPE_NUMERIC).setCellValue(e.getWechatOrderId());
for (int i = 0; i < e.getOrders().size(); i++) {
Orders o = e.getOrders().get(i);
row.createCell(7, Cell.CELL_TYPE_STRING).setCellValue(o.getOrderNumber());
row.createCell(8, Cell.CELL_TYPE_STRING).setCellValue(o.getMerchantCode());
if (MyStringUtils.isNotEmpty(o.getShopName())) {
row.createCell(9, Cell.CELL_TYPE_STRING).setCellValue(o.getShopName());
} else {
row.createCell(9, Cell.CELL_TYPE_STRING).setCellValue(o.getShopName());
}
if (MyStringUtils.isNotEmpty(o.getUserName())) {
row.createCell(10, Cell.CELL_TYPE_STRING).setCellValue(o.getUserName());
} else {
row.createCell(10, Cell.CELL_TYPE_STRING).setCellValue(o.getUserName());
}
if (null != o.getTurnover()) {
Double value = o.getTurnover() / 100.0;
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(11, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
}
if (null != o.getTotal()) {
Double value = o.getTotal() / 100.0;
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(12, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
}
if (o.getPayRefundePrice() != null) {
Double value = o.getPayRefundePrice() / 100.0;
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(13, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
} else {
row.createCell(13, Cell.CELL_TYPE_NUMERIC).setCellValue(0);
}
if (1 == 1) {
String value = (o.getIsAfterSale() != null && o.getIsAfterSale() == 1) ? "售后订单" : "支付订单";
row.createCell(14, Cell.CELL_TYPE_STRING).setCellValue(value);
}
if (MyStringUtils.isNotEmpty(o.getRemark()))
row.createCell(15, Cell.CELL_TYPE_STRING).setCellValue(o.getRemark());
if (o.getStatus() != null)
row.createCell(16, Cell.CELL_TYPE_STRING).setCellValue(OrderStatus.getValueByCode(o.getStatus()));
if (1 == 1) {
Double value = (o.getDistrib() == null ? 0 : o.getDistrib()) / 100.0;
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(17, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
}
if (1 == 1) {
Double value = (o.getServiceFee() == null ? 0 : o.getServiceFee()) / 100.0;
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(18, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
}
if (1 == 1) {
int turnover = o.getTurnover() == null ? 0 : o.getTurnover();
int serviceFee = o.getServiceFee() == null ? 0 : o.getServiceFee();
int distrib = o.getDistrib() == null ? 0 : o.getDistrib();
Double value = (turnover - serviceFee - distrib) / 100.0;
Double last = value.toString().indexOf(".") > -1 ? Double.valueOf(df.format(value)) : value;
row.createCell(19, Cell.CELL_TYPE_NUMERIC).setCellValue(last);
}
if (1 == 1) {
row.createCell(20, Cell.CELL_TYPE_STRING).setCellValue(o.getAccountType() == null ? "-" : o.getAccountType());
}
if (i < e.getOrders().size() - 1) {
row = sheet.createRow(rowNum++);
}
}
}
}
out = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=temp-order-settlement.xlsx");
response.setContentType("application/vnd.ms-excel");
book.write(out);
out.close();
} catch (Exception e) {
log.error("导出文件异常", e);
} finally {
try {
if (null != out)
out.close();
if (null != newFile)
newFile.delete();
} catch (IOException e1) {
}
}
}