利用POI导出Excel表

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.16</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.16</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.16</version>
    </dependency>

/**
* 订单列表导出
*/
@RequestMapping(value = “orderDownload”)
public void orderDownloads(HttpServletResponse response, OrderListQuery vo) throws Exception, ParseException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(“订单列表”);
//数据列表
vo.setTransstatus(-1);
List transPage = salesTransService.findTransPage(vo);
//设置要导出的文件的名字
String fileName = “订单列表” + “.xls”;
// 如果文件名有中文，必须URL编码
fileName = URLEncoder.encode(fileName, “UTF-8”);
//新增数据行，并且设置单元格数据
int rowNum = 1;
String[] headers = {“外部订单号”, “商户订单号”, “票号”, “城市”, “景区”, “门票名称”, “订单状态”, “退单状态”, “人数”, “已检票”, “刷脸/二维码”, “应付金额”, “实付金额”, “结算金额”, “退款金额”, “下单时间”, “游玩日期”, “取票人”, “联系电话”};
//headers表示excel表中第一行的表头
HSSFRow row = sheet.createRow(0);
//在excel表中添加表头
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//日期格式
SimpleDateFormat sdf1 = new SimpleDateFormat(“EEE MMM dd HH:mm:ss z yyyy”, Locale.ENGLISH);
SimpleDateFormat sdf2 = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);
HSSFCellStyle dateCellStyle = workbook.createCellStyle();
short df = workbook.createDataFormat().getFormat(“yyyy-MM-dd HH:mm:ss”);
dateCellStyle.setDataFormat(df);
//在表中存放查询到的数据放入对应的列
for (OrderList order : transPage) {
HSSFRow row1 = sheet.createRow(rowNum);
row1.createCell(0).setCellValue(order.getOrdersheetid());
row1.createCell(1).setCellValue(order.getPaychannelorderid());
row1.createCell(2).setCellValue(order.getTicketno());
row1.createCell(3).setCellValue(order.getCityname());
row1.createCell(4).setCellValue(order.getScenicname());
row1.createCell(5).setCellValue(order.getTicketname());
row1.createCell(6).setCellValue(order.getTransStatusStr());
row1.createCell(7).setCellValue(order.getReturnStatusStr());
row1.createCell(8).setCellValue(order.getTickepersonnum());
row1.createCell(9).setCellValue(order.getCheckpersonnum());
row1.createCell(10).setCellValue(order.getPaytypeStr());
row1.createCell(11).setCellValue(String.valueOf(order.getOutmoney()));
row1.createCell(12).setCellValue(String.valueOf(order.getPaymoney()));
row1.createCell(13).setCellValue(String.valueOf(order.getSummoney()));
row1.createCell(14).setCellValue(String.valueOf(order.getRtOutmoney()));
row1.createCell(15).setCellValue(sdf2.format(sdf1.parse(order.getSaletime().toString())));
row1.createCell(16).setCellValue(sdf2.format(sdf1.parse(order.getTourday().toString())));
row1.createCell(17).setCellValue(order.getCustomname());
row1.createCell(18).setCellValue(order.getLinkphone());
rowNum++;
}
//设置头信息
// ContentType 可以不设置
response.setHeader(“Content-disposition”, “attachment;filename=” + fileName);
response.setContentType(“application/vnd.ms-excel;charset=UTF-8”);
workbook.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();

}