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;
使用的方法片段。记录一下