一:导包
这里是导两个包,其实导一个就可以了
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
二:代码
//统计的
@GetMapping("/statistics")
public void statistics(HttpServletResponse response) throws IOException {
OutputStream os = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=" + new String("在线报考.xls".getBytes("GB2312"), "ISO8859-1"));
response.setContentType("application/msexcel");
String[] title = new String[]{
"序号", "姓名", "身份证号", "电话", "方向",
"准考证号", "缴费金额(元)", "支付方式", "订单号", "支付订单号",
"支付时间", "考区", "报考专业"};
HSSFWorkbook sheets = new HSSFWorkbook();
HSSFSheet sheet = sheets.createSheet("demo"); //一个表
HSSFRow row = sheet.createRow(0); //第一行
for (int i = 0; i < title.length; i++) {
row.createCell(i).setCellValue(title[i]); //序号
}
//查出已经购买的人
List<String> ids = userMapper.selectAlreadyBuy();
Long num = 1l;
//查出每个人查出他们的订单号,
for (String id : ids) {
//一个用户有多个订单
List<Orders> orders = userMapper.selectOrdersSnById(id);
//查出所有的订单号,一个订单号就是一条数据
for (Orders orders1 : orders) {
//查出每个用户所有购买的课程
List<ProductBuy> buys = userMapper.selectProductBuy(orders1.getOrderSn());
String level = buys.get(0).getStatus().substring(6);//(平面)
String zhuanye = buys.get(0).getStatus().substring(0, 6);// 报考专业
HSSFRow nrow = sheet.createRow(num.intValue()); //第二......三.............行
nrow.createCell(0).setCellValue(num); //序号
nrow.createCell(1).setCellValue(orders1.getMemberName()); //姓名
nrow.createCell(2).setCellValue(orders1.getMemberIdcard()); //身份证号
nrow.createCell(3).setCellValue(orders1.getMemberPhone()); //电话
nrow.createCell(4).setCellValue(level); //方向
nrow.createCell(5).setCellValue(orders1.getOkCard()); //准考证号
nrow.createCell(6).setCellValue(orders1.getActAmount().toString()); //缴费金额
nrow.createCell(7).setCellValue(orders1.getPayStatus().equals("1") ? "支付宝" : "微信"); 支付方式
nrow.createCell(8).setCellValue(orders1.getOrderSn()); //订单号
nrow.createCell(9).setCellValue(orders1.getPayOrgSn()); //支付订单号
nrow.createCell(10).setCellValue(new Date()); //支付时间
nrow.createCell(11).setCellValue(orders1.getCity()); //考区
nrow.createCell(12).setCellValue(zhuanye); //报考专业
num++;
}
}
sheets.write(os);
sheets.close();
}