| 提交 | 用户 | 时间 | ||
| a6de49 | 1 | package com.iailab.module.data.common.utils; |
| H | 2 | |
| 3 | import org.apache.poi.hssf.usermodel.*; | |
| 4 | import org.apache.poi.ss.usermodel.HorizontalAlignment; | |
| 5 | import org.apache.poi.ss.usermodel.VerticalAlignment; | |
| 6 | import org.apache.poi.ss.util.CellRangeAddress; | |
| 7 | ||
| 8 | import javax.servlet.http.HttpServletResponse; | |
| 9 | import java.io.IOException; | |
| 10 | import java.io.OutputStream; | |
| 11 | import java.lang.reflect.Field; | |
| 12 | import java.util.List; | |
| 13 | import java.util.logging.Level; | |
| 14 | import java.util.logging.Logger; | |
| 15 | ||
| 16 | /** | |
| 17 | * Excel 导出通用工具类 | |
| 18 | * | |
| 19 | * @author PanZhibao | |
| 20 | * @date 2021年06月29日 9:06 | |
| 21 | */ | |
| 22 | public class ExcelUtil { | |
| 23 | ||
| 24 | /** | |
| 25 | * 导出PointValueExcel | |
| 26 | * | |
| 27 | * @param sheetTitle | |
| 28 | * @param title | |
| 29 | * @param list | |
| 30 | * @param response | |
| 31 | * @return | |
| 32 | */ | |
| 33 | public static byte[] exportPointValue(String sheetTitle, String[] title, List<Object> list, HttpServletResponse response) { | |
| 34 | ||
| 35 | HSSFWorkbook wb = new HSSFWorkbook();//创建excel表 | |
| 36 | HSSFSheet sheet = wb.createSheet(sheetTitle); | |
| 37 | sheet.setDefaultColumnWidth(20);//设置默认行宽 | |
| 38 | ||
| 39 | //表头样式（加粗，水平居中，垂直居中） | |
| 40 | HSSFCellStyle cellStyle = wb.createCellStyle(); | |
| 41 | cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中 | |
| 42 | cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 | |
| 43 | ||
| 44 | HSSFFont fontStyle = wb.createFont(); | |
| 45 | ||
| 46 | cellStyle.setFont(fontStyle); | |
| 47 | ||
| 48 | //标题样式（加粗，垂直居中） | |
| 49 | HSSFCellStyle cellStyle2 = wb.createCellStyle(); | |
| 50 | cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 | |
| 51 | cellStyle2.setFont(fontStyle); | |
| 52 | ||
| 53 | //字段样式（垂直居中） | |
| 54 | HSSFCellStyle cellStyle3 = wb.createCellStyle(); | |
| 55 | cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 | |
| 56 | ||
| 57 | //数字样式 | |
| 58 | HSSFCellStyle cellStyle4 = wb.createCellStyle(); | |
| 59 | HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式 | |
| 60 | cellStyle4.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 | |
| 61 | cellStyle4.setDataFormat(df.getFormat("#,##0.000000"));//设置数值类型格式为保留两位小数 | |
| 62 | ||
| 63 | //创建表头 | |
| 64 | HSSFRow row = sheet.createRow(0); | |
| 65 | //row.setHeightInPoints(20);//行高 | |
| 66 | ||
| 67 | HSSFCell cell = row.createCell(0); | |
| 68 | cell.setCellValue(sheetTitle); | |
| 69 | cell.setCellStyle(cellStyle); | |
| 70 | ||
| 71 | sheet.addMergedRegion(new CellRangeAddress(0,0,0,(title.length-1))); | |
| 72 | ||
| 73 | //创建标题 | |
| 74 | HSSFRow rowTitle = sheet.createRow(1); | |
| 75 | //rowTitle.setHeightInPoints(20); | |
| 76 | ||
| 77 | HSSFCell hc; | |
| 78 | for (int i = 0; i < title.length; i++) { | |
| 79 | hc = rowTitle.createCell(i); | |
| 80 | hc.setCellValue(title[i]); | |
| 81 | hc.setCellStyle(cellStyle2); | |
| 82 | } | |
| 83 | ||
| 84 | byte result[] = null; | |
| 85 | ||
| 86 | try { | |
| 87 | //创建表格数据 | |
| 88 | Field[] fields; | |
| 89 | int i = 2; | |
| 90 | for (Object obj : list) { | |
| 91 | fields = obj.getClass().getDeclaredFields(); | |
| 92 | ||
| 93 | HSSFRow rowBody = sheet.createRow(i); | |
| 94 | //rowBody.setHeightInPoints(20); | |
| 95 | ||
| 96 | int j = 0; | |
| 97 | for (Field f : fields) { | |
| 98 | ||
| 99 | f.setAccessible(true); | |
| 100 | ||
| 101 | Object va = f.get(obj); | |
| 102 | if (null == va) { | |
| 103 | va = ""; | |
| 104 | } | |
| 105 | ||
| 106 | hc = rowBody.createCell(j); | |
| 107 | ||
| 108 | if (j == 0) { | |
| 109 | hc.setCellValue(Double.parseDouble(va.toString())); | |
| 110 | hc.setCellStyle(cellStyle4); | |
| 111 | } else { | |
| 112 | hc.setCellValue(va.toString()); | |
| 113 | hc.setCellStyle(cellStyle3); | |
| 114 | } | |
| 115 | ||
| 116 | ||
| 117 | j++; | |
| 118 | } | |
| 119 | ||
| 120 | i++; | |
| 121 | } | |
| 122 | ||
| 123 | //设置Http响应头告诉浏览器下载这个附件 | |
| 124 | response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls"); | |
| 125 | OutputStream outputStream = response.getOutputStream(); | |
| 126 | wb.write(outputStream); | |
| 127 | outputStream.close(); | |
| 128 | return wb.getBytes(); | |
| 129 | } catch (Exception ex) { | |
| 130 | Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex); | |
| 131 | return result; | |
| 132 | } finally{ | |
| 133 | try { | |
| 134 | wb.close(); | |
| 135 | } catch (IOException ex) { | |
| 136 | Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex); | |
| 137 | } | |
| 138 | } | |
| 139 | } | |
| 140 | } | |
