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

参数解释： 

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>

 导出模板：