生成Excel工具类

代码

/**
 * @ClassName ExcelUtil
 * @Description TODO Excel工具类
 * @Date 2021/1/11 17:26
 */

@Slf4j
public class ExcelUtil<T> implements Serializable {

    private static final long serialVersionUID = 230702500721813925L;
    /**
     * Excel类型
     * HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
     * XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
     */
    private static final String EXCEL_TYPE_XLS = "application/vnd.ms-excel";
    private static final String EXCEL_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    /**
     * HttpServletResponse
     */
    private HttpServletResponse response;
    /**
     * ServletOutputStream
     */
    private ServletOutputStream outputStream;
    /**
     * 默认列宽
     */
    private short columnWidth = (short) 5000;
    /**
     * 默认字体
     */
    private String fontName = "宋体";
    /**
     * 默认字体大小
     */
    private short fontHigth = (short) 12;

    /**
     * 首行小标题
     */
    private String[] romTitle;

    /**
     * 样式
     */
    private CellStyle cellStyle;
    /**
     * 字体
     */
    private Font font;

    /**
     * 使用默认样式
     */
    public ExcelUtil() {
    }

    /**
     * 自定义样式
     */
    public ExcelUtil(short columnWidth, String fontName, short fontHigth) {
        this.columnWidth = columnWidth;
        this.fontName = fontName;
        this.fontHigth = fontHigth;
    }

    /**
     * 在每次创建对象时获取HttpServletResponse对象
     */ {
        try {
            response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            outputStream = response.getOutputStream();
        } catch (IOException e) {
            log.error("在类加载时获取HttpServletResponse对象失败");
        }
    }

    /**
     * 设置首行小标题，标题之间用逗号隔开
     */
    public void setRowTitle(String titles) {
        this.romTitle = titles.split(",");
    }

    /**
     * @return org.apache.poi.ss.usermodel.Workbook
     * @Description TODO 导出2007版本Excel
     * @Param [excelName 导出Excel名称,titles 首行小标题, content 具体数据]
     * @date 2021/1/5 14:33
     * @auther liubo
     */
    public void exportXlsxExcel(String excelName, List<T> content) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // 设置列宽
        for (int i = 0; i < romTitle.length; i++) {
            sheet.setColumnWidth((short) i, columnWidth);
        }
        cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        font = workbook.createFont();
        font.setFontName(fontName);
        font.setColor(Font.COLOR_NORMAL);
        font.setFontHeightInPoints(fontHigth);
        cellStyle.setFont(font);
        try {
            setSheetFirstRowTitle(sheet);
            // 具体内容
            if (null != content && content.size() > 0) {
                for (int i = 0; i < content.size(); i++) {
                    Row row = sheet.createRow(sheet.getLastRowNum() + 1);
                    Class<?> contentsClass = content.get(i).getClass();
                    Field[] fields = contentsClass.getDeclaredFields();
                    if (null != fields && fields.length > 0) {
                        for (int j = 0; j < fields.length; j++) {
                            Field field = fields[j];
                            System.out.println(field.getName());
                            System.out.println();
                            field.setAccessible(true);
                            if (null != field.get(content.get(i))) {
                                row.createCell(j).setCellValue(String.valueOf(field.get(content.get(i))));
                            }
                        }
                    }
                }
            }
            response.reset();
            response.setHeader("Content-Type", "application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName + ".xlsx", "UTF-8"));
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Pragma", "no-cache");
            workbook.write(outputStream);
        } catch (IllegalAccessException e) {
            throw new IllegalAccessException(e.getMessage());
        } finally {
            workbook.close();
            outputStream.close();
        }
    }

    /**
     * @return void
     * @Description TODO 导出Excel模板
     * @Param [excelName]
     * @date 2021/1/14 13:33
     * @author liubo
     */
    public void expoetXlxsExcelTemplate(String excelName) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // 设置列宽
        for (int i = 0; i < romTitle.length; i++) {
            sheet.setColumnWidth((short) i, columnWidth);
        }
        cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        font = workbook.createFont();
        font.setFontName(fontName);
        font.setColor(Font.COLOR_NORMAL);
        font.setFontHeightInPoints(fontHigth);
        cellStyle.setFont(font);
        try {
            setSheetFirstRowTitle(sheet);
            response.reset();
            response.setHeader("Content-Type", "application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName + ".xlsx", "UTF-8"));
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Pragma", "no-cache");
            workbook.write(outputStream);
        } catch (Exception e) {

        } finally {
            workbook.close();
            outputStream.close();
        }
    }

    /**
     * @return T
     * @Description TODO 导入2007版Excel进行解析(结果为集合)
     * @Param [obj, file]
     * @date 2021/1/13 10:37
     * @author liubo
     */
    public List<T> importXlsxExcel(T obj, MultipartFile file) {
        List<T> list = new ArrayList<>();
        Class<?> aClass = obj.getClass();
        Field[] fields = aClass.getDeclaredFields();
        try {
            // 获取文件输入流
            InputStream inputStream = file.getInputStream();
            // 创建XSSFWorkbook将输入流作为参数
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            //获取第一行页 sheet
            Sheet sheet = workbook.getSheetAt(0);
            // 获取第一行
            Row row = sheet.getRow(0);
            //获取最大行数
            int rowNum = sheet.getPhysicalNumberOfRows();
            //获取最大列
            int maxColNum = row.getPhysicalNumberOfCells();
            // 行
            for (int i = 1; i < rowNum; i++) {
                T o = (T) aClass.newInstance();
                // 每一行
                Row row1 = sheet.getRow(i);
                // 列
                for (int j = 0; j < maxColNum; j++) {
                    Cell cell = row1.getCell(j);
                    if (null != fields && fields.length > 0) {
                        fields[j].setAccessible(true);
                        if (fields[j].getType().getSimpleName().equals("String")) {
                            fields[j].set(o, cell.getStringCellValue());
                        } else {
                            System.out.println(cell.getNumericCellValue());
                            fields[j].set(o, cell.getNumericCellValue());
                        }
                    }
                }
                list.add(o);
            }
        } catch (Exception e) {
            throw new WarehouseException("Excel导入失败");
        }
        return list;
    }

    /**
     * @return void
     * @Description TODO 设置首行小标题
     * @Param [sheet]
     * @date 2021/1/14 13:25
     * @author liubo
     */
    private void setSheetFirstRowTitle(Sheet sheet) {
        // 小标题
        if (Objects.nonNull(romTitle)) {
            Row row = sheet.createRow(0);
            for (int i = 0; i < romTitle.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(romTitle[i]);
            }
        }
    }
}

demo链接

github
码云

关于

近期在开发中遇到一个问题，就是将数据库中的数据导出成excel，就此问题编写了一个Excel小工具类，如有不对，可以一起探讨。