一个导出押金数据到EXCEL的小栗子
controller
@ApiOperation(value = "导出押金订单信息", notes = "导出押金订单信息")
@GetMapping(value = "/exportDeposit")
public RespResultVO<String> exportDeposit(HttpServletResponse response,@RequestParam(required = true) @ApiParam(value = "押金状态")String status,@RequestParam(required = false) @ApiParam(value = "订单号")String orderNum) throws IOException {
return excelExportService.exportDeposit(response,status,orderNum);
}
Service
public RespResultVO<String> exportDeposit(HttpServletResponse response,String status,String orderNum) {
response.setContentType("application/vnd.ms-excel;charset=utf-8");
try {
if (orderNum==null){
orderNum="";
}
List<DepositExportVO> depositExportVOS=new ArrayList<DepositExportVO>();
if (status.equals("已交押金")){
depositExportVOS = orderHeaderRepository.findPayDepositFlow("%"+orderNum+"%");
}else if (status.equals("已退押金")){
depositExportVOS = orderHeaderRepository.findRefundDepositFlow("%"+orderNum+"%");
}
String[] fields = {"订单号","押金应收流水ID", "应收金额", "支付方", "支付方式", "支付状态"};
ExcelExport excelExport = new ExcelExport();
HSSFWorkbook wb = excelExport.generateExcel();
wb = excelExport.generateOrderDepositVOSheet(wb, "押金订单信息", fields, depositExportVOS);
excelExport.export("押金订单信息", wb, response);
return RespResultVO.<String>builder().message("success").build();
} catch (Exception e) {
return RespResultVO.<String>builder().message(e.getMessage()).build();
}
}
ExcelExport util
import org.apache.poi.hssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
/**
* Created by Jens on 2017/6/5.
*/
public class ExcelExport {
// 第一步,创建一个webbook,对应一个Excel文件
public HSSFWorkbook generateExcel() {
return new HSSFWorkbook();
}
public HSSFWorkbook createRow(HSSFWorkbook wb, String sheetName, String[] fields) {
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头
HSSFCellStyle style = wb.createCellStyle();
HSSFCell cell;
int m = 0;
for (String fieldName : fields) {
cell = row.createCell(m);
cell.setCellValue(fieldName);
cell.setCellStyle(style);
m++;
}
return wb;
}
public HSSFWorkbook generateOrderDepositVOSheet(HSSFWorkbook wb, String sheetName, String[] fields,
List<DepositExportVO> list) {
HSSFWorkbook hssfWorkbook = this.createRow(wb, sheetName, fields);
HSSFSheet hssfSheet = hssfWorkbook.getSheet(sheetName);
HSSFRow hssfRow;
for (int i = 0; i < list.size(); i++) {
hssfRow = hssfSheet.createRow(i + 1);
DepositExportVO data = list.get(i);
// 第五步,创建单元格,并设置值
hssfRow.createCell(0).setCellValue(data.getNumber());
hssfRow.createCell(1).setCellValue(data.getPayNumber());
hssfRow.createCell(2).setCellValue(data.getOrderAmount().toString());
hssfRow.createCell(3).setCellValue(data.getMemberName());
hssfRow.createCell(4).setCellValue(data.getSource());
hssfRow.createCell(5).setCellValue(data.getStatus());
}
return wb;
}
public void export(String title, HSSFWorkbook wb, HttpServletResponse response) {
// 第六步,实现文件下载保存
try {
response.setHeader("content-disposition", "attachment;filename="
+ URLEncoder.encode(title, "utf-8") + ".xls");
OutputStream outputStream = response.getOutputStream();
ByteArrayOutputStream baos = new ByteArrayOutputStream();
wb.write(baos);
byte[] xlsBytes = baos.toByteArray();
outputStream.write(xlsBytes);
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}