引入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
创建表头与设置样式
/**
* 订单导出Excel
* 创建表头
*/
private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet){
HSSFRow row = sheet.createRow(0);
//设置列宽,setColumnWidth的第二个参数要乘以256,这个参数的单位是1/256个字符宽度
sheet.setColumnWidth(2, 12*256);
sheet.setColumnWidth(3, 17*256);
//设置为居中加粗
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBold(true);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFont(font);
HSSFCell cell;
cell = row.createCell(0);
cell.setCellValue("id");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("用户ID");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("订单编号");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("应收金额");
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("实际金额");
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue("付款方式");
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue("订单状态");
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue("是否提醒");
cell.setCellStyle(style);
cell = row.createCell(8);
cell.setCellValue("下单时间");
cell.setCellStyle(style);
cell = row.createCell(9);
cell.setCellValue("更新时间");
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellValue("付款时间");
cell.setCellStyle(style);
cell = row.createCell(11);
cell.setCellValue("发货时间");
cell.setCellStyle(style);
cell = row.createCell(12);
cell.setCellValue("收货时间");
cell.setCellStyle(style);
cell = row.createCell(13);
cell.setCellValue("交易完成时间");
cell.setCellStyle(style);
cell = row.createCell(14);
cell.setCellValue("交易关闭时间");
cell.setCellStyle(style);
cell = row.createCell(15);
cell.setCellValue("物流名称");
cell.setCellStyle(style);
cell = row.createCell(16);
cell.setCellValue("物流编号");
cell.setCellStyle(style);
cell = row.createCell(17);
cell.setCellValue("运费");
cell.setCellStyle(style);
cell = row.createCell(18);
cell.setCellValue("收货方式");
cell.setCellStyle(style);
cell = row.createCell(19);
cell.setCellValue("是否发放积分");
cell.setCellStyle(style);
cell = row.createCell(20);
cell.setCellValue("退货截至时间");
cell.setCellStyle(style);
}
获取Excel数据
/**
* 获取excel数据
*/
@GetMapping("/getExcel")
public void getExcel(HttpServletResponse response) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("订单表");
createTitle(workbook,sheet);
List<OrderInfo> orderInfos;
---------------------分割线-----------------------------------
此处请自行插入需查询的业务逻辑数据
---------------------------------------------------------------
//设置日期格式
HSSFCellStyle style=workbook.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//新增数据行,并且设置单元格数据
int rowNum = 1;
for (OrderInfo oderInfo: orderInfos) {
HSSFRow row = sheet.createRow(rowNum);
row.createCell(0).setCellValue(oderInfo.getId());
row.createCell(1).setCellValue(oderInfo.getUserId());
if (oderInfo.getCode()!=null)
row.createCell(2).setCellValue(oderInfo.getCode());
if (oderInfo.getShouldPayment()!=null)
row.createCell(3).setCellValue(oderInfo.getShouldPayment());
if (oderInfo.getActualPayment()!=null)
row.createCell(4).setCellValue(oderInfo.getActualPayment());
if (oderInfo.getStatus()!=null)
row.createCell(5).setCellValue(oderInfo.getStatus());
if (oderInfo.getIsRemind()!=null)
row.createCell(6).setCellValue(oderInfo.getIsRemind());
if (oderInfo.getCreateTime()!=null)
row.createCell(7).setCellValue(dateFormat.format(oderInfo.getCreateTime()));
if (oderInfo.getUpdateTime()!=null)
row.createCell(8).setCellValue(dateFormat.format(oderInfo.getUpdateTime()));
if (oderInfo.getPaymentTime()!=null)
row.createCell(9).setCellValue(dateFormat.format(oderInfo.getPaymentTime()));
if (oderInfo.getConsignTime()!=null)
row.createCell(10).setCellValue(dateFormat.format(oderInfo.getConsignTime()));
if (oderInfo.getReceiverTime()!=null)
row.createCell(11).setCellValue(dateFormat.format(oderInfo.getReceiverTime()));
if (oderInfo.getEndTime()!=null)
row.createCell(12).setCellValue(dateFormat.format(oderInfo.getEndTime()));
if (oderInfo.getCloseTime()!=null)
row.createCell(13).setCellValue(dateFormat.format(oderInfo.getCloseTime()));
if (oderInfo.getShippingName()!=null)
row.createCell(14).setCellValue(oderInfo.getShippingName());
if (oderInfo.getShippingCode()!=null)
row.createCell(15).setCellValue(oderInfo.getShippingCode());
if (oderInfo.getShippingMoney()!=null)
row.createCell(16).setCellValue(oderInfo.getShippingMoney());
if (oderInfo.getReceiverWay()!=null)
row.createCell(17).setCellValue(oderInfo.getReceiverWay());
if (oderInfo.getPaymentStatus()!=null)
row.createCell(18).setCellValue(oderInfo.getPaymentStatus());
if (oderInfo.getCutoff_time()!=null)
row.createCell(19).setCellValue(dateFormat.format(oderInfo.getCutoff_time()));
rowNum++;
}
// 拼装表名称
String fileName = "订单表.xlsx";
String dateTime = dateFormat.format(new Date());
String blobName = dateTime + "/" + UUID.randomUUID().toString().replaceAll("-", "") + "/" + fileName;
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename=" + blobName);
response.flushBuffer();
workbook.write(response.getOutputStream());
workbook.close();
}
本人98年菜鸟一枚,希望大神多多指点