POI导出数据到Excel

POI导出数据到Excel

简介

自定义工具类源码：

/**
 * @author zhangzh
 * @version 1.0
 * @date 2019/12/18 13:49
 */
public class WriterExcelUtils{

    private WriterExcelUtils(){}

    /**
     * 根据需要的文档类型不同，创建不同的工作簿
     * @param type Excel文档类型名称
     */
    public static Workbook createWorkbook(String type){
        Workbook workbook = null;
        if ("xls".equalsIgnoreCase(type)){
            workbook = new HSSFWorkbook();
        }else if ("xlsx".equalsIgnoreCase(type)){
            workbook = new SXSSFWorkbook();
        }
        return workbook;
    }

    /**
     * 返回一个CellStyle给用户设置工作簿样式
     * @param workbook
     * @return
     */
    public static CellStyle setCellStyle(Workbook workbook){
        return workbook.createCellStyle();
    }

    /**
     * 获取工作簿一页对象
     * @param workbook 工作簿对象
     * @param head 页头部信息
     * @param rowHeight 行高
     * @param columnWidth 单元格列宽
     * @param cellStyle 单元格样式
     * @return
     */
    public static Sheet createSheet(Workbook workbook, List<String> head, Short rowHeight, Integer columnWidth, CellStyle cellStyle){
        Sheet sheet = workbook.createSheet();
        //设置行高
        sheet.setDefaultRowHeight(rowHeight);
        //创建sheet页面第一行
        Row row = sheet.createRow(0);
        //设置单元格列宽,单元格样式,设置头部信息
        if (cellStyle != null) {
            for (int i = 0; i < head.size(); i++) {
                sheet.setColumnWidth(i, columnWidth);
                Cell cell = row.createCell(i);
                cell.setCellValue(head.get(i));
                cell.setCellStyle(cellStyle);
            }
        }else{
            for (int i = 0; i < head.size(); i++) {
                sheet.setColumnWidth(i, columnWidth);
                Cell cell = row.createCell(i);
                cell.setCellValue(head.get(i));
            }
        }
        return sheet;
    }

    /**
     * 将实体类数据封装到Row对象中
     * @param t 实体类对象
     * @param row Row对象
     * @param cellStyle 设置单元格样式
     * @param <T>
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    private static <T> void exportDataToRow(T t, Row row, Integer size, CellStyle cellStyle) throws InvocationTargetException, IllegalAccessException, InstantiationException {
        //获取导出实体类的字节码文件
        Class clazz = t.getClass();
        //获取实体类的所有属性
        Field[] fields = clazz.getDeclaredFields();
        //获取实体类的所有方法
        Method[] methods = clazz.getMethods();
        for (int i = 0; i < size; i++) {
            //获取属性名
            String fieldName = fields[i].getName();
            String str = fieldName.substring(0,1).toUpperCase().concat(fieldName.substring(1).toLowerCase());
            //获取属性get方法名
            String methodName = "get" + str;
            //创建单元格
            Cell cell = row.createCell(i);
            if (cellStyle != null) {
                //将实体类的数据以字符串形式封装到Sheet对象的一行中去
                for (Method method : methods) {
                    if (methodName.equals(method.getName())){
                        cell.setCellValue(String.valueOf(method.invoke(t)));
                        cell.setCellStyle(cellStyle);
                        break;
                    }
                }
            }else{
                //将实体类的数据以字符串形式封装到Sheet对象的一行中去
                for (Method method : methods) {
                    if (methodName.equals(method.getName())){
                        cell.setCellValue(String.valueOf(method.invoke(t)));
                        break;
                    }
                }
            }
        }
    }

    /**
     * 导出数据到工作簿中
     * @param sheet 封装好的sheet对象
     * @param dataList 数据集合,若数据集合为null，则下载Excel模板
     * @param cellStyle 单元格样式
     * @param <T>
     * @return
     */
    public static <T> Workbook writeExcel(Sheet sheet, List<T> dataList, CellStyle cellStyle){
        Workbook workbook = sheet.getWorkbook();
        //因为已经有头部信息要占一行所以从第二行开始写数据
        AtomicInteger rowCount = new AtomicInteger(1);
        dataList.forEach(t ->{
            if (t == null){
                return;
            }
            Row row = sheet.createRow(rowCount.getAndIncrement());
            try {
                exportDataToRow(t, row, sheet.getRow(0).getPhysicalNumberOfCells(), cellStyle);
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InstantiationException e) {
                e.printStackTrace();
            }
        });
        return workbook;
    }

    /**
     * 获取Excel文档，写到指定目录
     * @param path 指定目录
     * @param workbook 工作簿
     * @throws IOException
     */
    public static void getExcel(String path, Workbook workbook) throws IOException {
        OutputStream os = null;
        try {
            File file = new File(path);
            if (!file.exists()){
                file.createNewFile();
            }
            os = new FileOutputStream(file);
            workbook.write(os);
            os.flush();
        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            if (workbook != null){
                workbook.close();
            }
            if (os != null){
                os.close();
            }
        }
    }

    /**
     * 获取Excel文档,发送到前端
     */
    public static void getExcel(HttpServletRequest request, HttpServletResponse response, Workbook workbook) throws IOException {
        OutputStream os = null;
        try {
            String fileName = "数据信息" + ".xlsx";
            fileName = new String(fileName.getBytes("UTF-8"), "iso8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setContentType("application/x-download");
            response.setCharacterEncoding("UTF-8");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            response.flushBuffer();
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            if (workbook != null){
                workbook.close();
            }
            if (os != null){
                os.close();
            }
        }
    }
}

使用步骤

1. 导入依赖

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

2. 申明Excel文档的头部信息

	List<String> head = new ArrayList<>();
    head.add("序号");
    head.add("姓名");
    head.add("年龄");
    head.add("电话");

3. 获取数据集合

	List<User> data = new ArrayList<>();
	data.add(new User((long) 1, "张三", 25, "13281815055"));
	data.add(new User((long) 2, "李四", 22, "110"));
	data.add(new User((long) 3, "王五", 21, "1231543134"));

4. 根据工作簿后缀名创建工作簿（参数为“xls”或“xlsx”）

	Workbook workbook = WriterExcelUtils.createWorkbook("xlsx");

5. 创建一个Sheet页对象，设置头部信息以及头部单元格行高和列宽，CellStyle为null时单元格格式为默认格式

	Sheet sheet = WriterExcelUtils.createSheet(workbook, head, (short) 400, 4000, null);

6. 获取已经封装好数据的工作簿对象

	Workbook workbook1 = WriterExcelUtils.writeExcel(sheet, data, null);

7. 获取Excel文档（当参数为String，Workbook时，导出文档到指定目录；当参数为HttpServletRequest，HttpServletResponse，Workbook），导出文档到浏览器

	WriterExcelUtils.getExcel("C:/Users/admin/Desktop/测试.xlsx",workbook1);