一篇在一个Excel表中创建多个sheet的代码

佚名 5年前 (2019-04-20) 随笔 1623人围观抢沙发百度已收录

 1 package projectUtil;
 2 
 3 import org.apache.commons.lang3.StringUtils;
 4 import org.apache.poi.hssf.usermodel.*;
 5 import org.apache.poi.ss.usermodel.HorizontalAlignment;
 6 import org.apache.poi.ss.usermodel.VerticalAlignment;
 7 
 8 import java.util.List;
 9 import java.util.Map;
10 
11 /**
12  * 创建Excel表
13  * @author tian
14  * @date 2019/3/2315:46
15  */
16 public class WorkbookUtil {
17     private HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
18 
19 
20     public HSSFWorkbook getWorkbook() {
21         return this.workbook;
22     }
23 
24     public HSSFSheet writeExcel(List<Map<String, Object>> list, List<String> head, String sheetName) {
25         HSSFSheet sheet;// 创建一个Excel的Sheet
26         if (!StringUtils.isBlank(sheetName)) {
27             sheet = workbook.createSheet(sheetName);// 创建一个Excel的Sheet
28         } else {
29             sheet = workbook.createSheet("Sheet");// 创建一个Excel的Sheet
30         }
31         HSSFRow row4 = sheet.createRow(0);
32 
33         HSSFCellStyle style4 = workbook.createCellStyle();
34         style4.setAlignment(HorizontalAlignment.CENTER);//水平居中
35         style4.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
36         style4.setWrapText(true);//自动换行
37         style4.setAlignment(HorizontalAlignment.CENTER);//水平居中
38         style4.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
39         HSSFFont font4 = workbook.createFont();
40         // font.setFontName("华文行楷");//设置字体名称
41         font4.setFontHeightInPoints((short) 12);//设置字号
42         // font4.setBold( true);
43         style4.setFont(font4);
44         for (int i = 0; i < head.size(); i++
45         ) {
46             sheet.setColumnWidth(i, 20 * 256);
47             HSSFCell cell4_1 = row4.createCell(i);
48             cell4_1.setCellValue(head.get(i));
49             cell4_1.setCellStyle(style4);
50         }
51         for (int i = 0; i < list.size(); i++) {
52             HSSFRow row5 = sheet.createRow(i + 1);
53             for (int y = 0; y < head.size(); y++
54             ) {
55                 HSSFCell cell5_1 = row5.createCell(y);
56                 cell5_1.setCellValue(list.get(i).get(head.get(y)) == null ? "" : list.get(i).get(head.get(y)) + "");
57                 cell5_1.setCellStyle(style4);
58             }
59 
60         }
61 
62         return sheet;
63     }
64 
65 
66 }

　　　　自己写的工具类，可以参考一下，如果有bug希望各位大侠指教。

 1    List<Map<String, Object>> rows = new ArrayList<>();
 2         List<Map<String, Object>> zifu = new ArrayList<>();
 3         List<Map<String, Object>> yinhang = new ArrayList<>();
 4         for (Map one : list) {
 5             Integer state = (Integer) one.get("state");
 6             Map<String, Object> reMap = new LinkedHashMap<>();
 7             reMap.put("提现编号", one.get("id"));
 8             reMap.put("用户ID", one.get("userId"));
 9             reMap.put("用户名称", one.get("name"));
10             reMap.put("用户手机号", one.get("phone"));
11             reMap.put("银行卡户主名称", one.get("userName"));
12             reMap.put("银行", one.get("bankName"));
13             reMap.put("所属支行", one.get("subBranchName"));
14             reMap.put("银行卡号", one.get("cardNumber"));
15             reMap.put("提现金额", one.get("arrivalAmountMoney"));
16             reMap.put("提现状态", state == -1 ? "处理中" : state == 1 ? "已处理" : state == 0 ? "待处理" : state == 2 ? "信息错误" : "千万不要打款");
17             reMap.put("支付宝账号", one.get("withdraw"));
18             reMap.put("提现方式", (Integer) one.get("withdrawType") == 0 ? "支付宝" : "银行卡");
19             reMap.put("备注", one.get("remark"));
20             reMap.put("打款进度", "");
21             rows.add(reMap);
22             if ((Integer) one.get("withdrawType") == 0) {//支付宝提现
23                 Map<String, Object> ss = reMap;
24                 ss.remove("银行卡户主名称", one.get("userName"));
25                 ss.remove("银行", one.get("bankName"));
26                 ss.remove("银行卡号", one.get("cardNumber"));
27                 ss.remove("所属支行", one.get("subBranchName"));
28                 zifu.add(ss);
29             }
30             if ((Integer) one.get("withdrawType") == 1) {//银行卡提现
31                 Map<String, Object> ss = reMap;
32                 ss.remove("支付宝账号", one.get("withdraw"));
33                 yinhang.add(ss);
34             }
35         }
36         List<String> head = new ArrayList<>();
37         head.add("提现编号");
38         head.add("用户ID");
39         head.add("用户名称");
40         head.add("用户手机号");
41         head.add("银行卡户主名称");
42         head.add("银行");
43         head.add("所属支行");
44         head.add("银行卡号");
45         head.add("提现金额");
46         head.add("提现状态");
47         head.add("支付宝账号");
48         head.add("提现方式");
49         head.add("备注");
50         head.add("打款进度");
51         WorkbookUtil workbookUtil = new WorkbookUtil();
52         HSSFSheet sheet1 = workbookUtil.writeExcel(rows, head, "用户总提现");
53         sheet1.setColumnWidth(5,30 * 256);
54         sheet1.setColumnWidth(6,30 * 256);
55         sheet1.setColumnWidth(7,30 * 256);
56         List<String> head1= (List<String>) ((ArrayList<String>) head).clone();
57         head1.remove(10);
58         HSSFSheet sheet2 = workbookUtil.writeExcel(yinhang, head1, "银行卡提现");
59         sheet2.setColumnWidth(5,30 * 256);
60         sheet2.setColumnWidth(6,30 * 256);
61         sheet2.setColumnWidth(7,30 * 256);
62         List<String> head2=(List<String>) ((ArrayList<String>) head).clone();
63         head2.remove(4);
64         head2.remove(4);
65         head2.remove(4);
66         head2.remove(4);
67         HSSFSheet sheet3 = workbookUtil.writeExcel(zifu, head2, "支付宝提现");
68         // 第六步，将文件存到指定位置
69         ServletOutputStream out = response.getOutputStream();
70         try {
71             response.setContentType("application/vnd.ms-excel;charset=ISO8859-1");
72             response.setHeader("Content-Disposition", "attachment;filename=" + new String("用户提现".getBytes("UTF-8"), "ISO8859-1") + new SimpleDateFormat("yyyy-MM-dd->HH").format(new Date()) + ".xls");
73             workbookUtil.getWorkbook().write(out);
74         } catch (Exception e) {
75             e.printStackTrace();
76         } finally {
77             out.close();
78         }
79         return;