Excel poi4.0导出工具类兼容.xls和.xlsx

Love The Way You Lie 2022-01-23 08:15 205阅读 0赞

### 参数解释：  ###

String sheetName = "前台导入错误提示";
    String[] headers = {"错误序号", "错误信息"};
    String[] columns = {"xuhao", "info"};
    originalFilename =======》例：xxxx.xls

### 工具类：  ###

/**
     * @author: lucifer
     * @description: Excel导出工具类
     */
    @Slf4j
    public class ExportExcelUtil<T> {
    
        /**
         * 兼容.xls和.xlsx格式
         *
         * @param originalFilename
         * @return
         */
        private Workbook getWorkbook(String originalFilename) {
            Workbook workbook = null;
            if (originalFilename.endsWith(EXTENSION_XLS)) {
                workbook = new HSSFWorkbook();
            } else if (originalFilename.endsWith(EXTENSION_XLSX)) {
                workbook = new XSSFWorkbook();
            }
            return workbook;
        }
    
    
        /**
         * 导出
         *
         * @param originalFilename
         * @param sheetName
         * @param headers
         * @param columns
         * @param lists
         * @throws Exception
         */
        public void export(String originalFilename, String sheetName, String[] headers, String[] columns, List<T> lists) throws Exception {
            Workbook workbook = getWorkbook(originalFilename);
            Sheet sheet = workbook.createSheet(sheetName);
            sheet.setDefaultColumnWidth(15);
            //设置表头样式
            CellStyle style = setHeaderStyle(workbook);
            Row row = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                Cell headerCell = row.createCell(i);
                headerCell.setCellValue(headers[i]);
                headerCell.setCellStyle(style);
            }
            Iterator<T> it = lists.iterator();
            int rowIndex = 0;
            while (it.hasNext()) {
                rowIndex++;
                row = sheet.createRow(rowIndex);
                T t = it.next();
                Field[] fields = t.getClass()
                        .getDeclaredFields();
                for (int i = 0; i < fields.length; i++) {
                    Field field = fields[i];
                    String fieldName = field.getName();
                    for (int j = 0; j < columns.length; j++) {
                        if (fieldName.equals(columns[j])) {
                            String getMethodName = "get" +
                                    fieldName.substring(0, 1).
                                            toUpperCase() + fieldName.
                                    substring(1);
                            Class cls = t.getClass();
                            Method getMethod = cls.getMethod(
                                    getMethodName, new Class[]{});
                            Object val = getMethod.invoke(
                                    t, new Object[]{});
                            String textVal;
                            if (null != val) {
                                textVal = val.toString();
                            } else {
                                textVal = null;
                            }
                            Cell cell = row.createCell(j);
                            cell.setCellValue(textVal);
                            CellStyle cellStyle = setCellStyle(workbook, i);
                            cell.setCellStyle(cellStyle);
                            //单元格列宽设置
                            sheet.autoSizeColumn(j);
                            sheet.setColumnWidth(j, sheet.getColumnWidth(j)*17/10 > 255 * 256 ? 255 * 256 : sheet.getColumnWidth(j)*17/10);
                            log.info("======================:"+sheet.getColumnWidth(i));
                            log.info("textVal.getBytes().length:" + textVal.getBytes().length);
                            log.info("i:===============" + i + ",j:================" + j + ",textVal:" + textVal);
                        }
                    }
                }
            }
            String filename = sheetName + System.currentTimeMillis() + ".xlsx";
            //判断是否有文件夹，没有就创建
            File file = new File(BaseConstant.EXCEL_PATH);
            if (!file.exists()) {
                file.mkdirs();
            }
            String filepath = BaseConstant.EXCEL_PATH + "\\" + filename;
            System.out.println("filepath:" + filepath);
            FileOutputStream out = new FileOutputStream(filepath);
            workbook.write(out);
            out.close();
            // downloadExcel(filepath, response);
        }
    
        /**
         * 设置表头样式
         *
         * @param workbook
         * @return
         */
        private CellStyle setHeaderStyle(Workbook workbook) {
            CellStyle cellStyle = workbook.createCellStyle();
            //水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
            //垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置边框
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            //设置字体
            Font cellFont = workbook.createFont();
            cellFont.setBold(true);
            cellStyle.setFont(cellFont);
            return cellStyle;
        }
    
        /**
         * 设置单元格样式
         */
        private CellStyle setCellStyle(Workbook workbook, int i) {
            CellStyle cellStyle = workbook.createCellStyle();
            //奇数列 左对齐
            if ((i & 1) != 1) {
                cellStyle.setAlignment(HorizontalAlignment.LEFT);
            } else {
                //水平居中
                cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
            }
            //垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置边框
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            //设置字体
            Font cellFont = workbook.createFont();
            cellFont.setFontName("仿宋_GB2312");
            cellStyle.setFont(cellFont);
            return cellStyle;
        }
    
    
        /**
         * 下载
         */
        public static void downloadExcel(String filepath, HttpServletResponse response)
                throws IOException {
            File file = new File(filepath);
            String fileName = file.getName();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));
            response.setCharacterEncoding("utf-8");
            InputStream fis = new BufferedInputStream(new FileInputStream(file));
            byte[] b = new byte[fis.available()];
            fis.read(b);
            response.getOutputStream().write(b);
            fis.close();
        }
    
    }

### BaseConstant:常量类： ###

/**
         * excel ./xls后缀
         */
        public static final String EXTENSION_XLS = ".xls";
    
    
        /**
         * excel ./xlsx后缀
         */
        public static final String EXTENSION_XLSX = ".xlsx";

### pom.xml：  ###

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.0.0</version>
            </dependency>

## ##

###  导出模板： ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDk1Nzg2_size_16_color_FFFFFF_t_70]: /images/20220123/f77be630b1a743aeac7ea0232973958f.png