poi导出Excel工具类

一只小猿i

于 2023-04-13 11:03:57 发布

阅读量254

点赞数

文章标签： excel java

本文链接：https://blog.csdn.net/qq_43614322/article/details/130125315

版权

文章提供了一个使用ApachePOI库的Java工具类，用于创建和导出Excel文件。该工具类包含多个方法，支持单表头报表和带有自定义表头的报表创建，可以处理各种数据结构并设置单元格样式，如垂直居中和居中对齐。代码示例展示了如何在Maven项目中引用ApachePOI库，并创建Excel文件以供下载。

摘要由CSDN通过智能技术生成

maven依赖

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.8</version>
            <type>jar</type>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>

工具类代码

public class ExcelExportUtils<T> {

    /**
     * 单表头报表
     *
     * @param data      数据结构
     *                  [
     *                  表头(title)
     *                  内容(data)
     *                  ]
     * @param sheetName sheet名称
     * @param fileName  文件地址
     * @return
     * @throws IllegalAccessException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     */
    public HSSFWorkbook createExcel(List<T> data, String sheetName, String fileName, HttpServletResponse response) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);
        int index = 0;
        int rowIndex = 0;
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 设置垂直居中
        cellStyle.setVerticalAlignment( VerticalAlignment.CENTER);
        // 设置居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 输出
        try {
            for (T t : data
            ) {
                Row row = sheet.createRow(rowIndex);
                rowIndex++;
                Field[] declaredFields = t.getClass().getDeclaredFields();
                for (Field f : declaredFields
                ) {
                    //  BeanUtils.getProperty(t,t.getClass().getDeclaredField("index").name)
                    String property = BeanUtils.getProperty(t, f.getName());
                    if (StringUtils.isNotBlank(property)) {
                        HSSFCell cell = (HSSFCell) row.createCell(index++);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(property);
                    }
                }
                index = 0;
            }
            response.setCharacterEncoding("UTF-8");
            response.addHeader("Content-Disposition","attachment;filename="+java.net.URLEncoder.encode(fileName, "utf-8"));
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }

        return workbook;
    }

    /**
     * 单个sheet页导出
     *
     * @param data
     * @param title
     * @param sheetName
     * @param fileName
     * @return
     */
    public HSSFWorkbook createExcel(List<T> data, Map<String, String> title, String sheetName, String fileName) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);
        int index = 0;
        int rowIndex = 0;
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 设置垂直居中
        cellStyle.setVerticalAlignment( VerticalAlignment.CENTER);
        // 设置居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        Set entrySet = title.entrySet();
        Iterator integer1 = entrySet.iterator();
        while (integer1.hasNext()) {
            Object obj = integer1.next();
            Map.Entry entry = (Map.Entry) obj;
            Row row = sheet.createRow(rowIndex);
            rowIndex++;
            HSSFCell cell = (HSSFCell) row.createCell(index);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(String.valueOf(entry.getKey()));
            index++;
            cell = (HSSFCell) row.createCell(index);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(String.valueOf(entry.getValue()));
            index = 0;
        }
        index = 0;
        FileOutputStream fileOutputStream = null;
        // 输出
        try {
            for (T t : data
            ) {
                Row row = sheet.createRow(rowIndex);
                rowIndex++;
                Field[] declaredFields = t.getClass().getDeclaredFields();
                for (Field f : declaredFields
                ) {
                    String property = BeanUtils.getProperty(t, f.getName());
                    if (StringUtils.isNotBlank(property)) {
                        HSSFCell cell = (HSSFCell) row.createCell(index);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(property);
                    }
                    index++;
                }
                index = 0;
            }
            fileOutputStream = new FileOutputStream(fileName);
            workbook.write(fileOutputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } finally {
            if (fileOutputStream != null) {
                try {
                    // 6.关闭流
                    fileOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return workbook;
    }

    /**
     * 创建单个sheet页
     *
     * @param data      excel数据
     * @param title     表头内容 ,可以为空，eg：
     *                  （以下数据表头）
     *                  数据范围	全部公司-全部
     *                  日期：	2022-02-22—2023年02月22日
     *                  成果来源	全部
     *                  所属专业线	全部
     *                  (以下属于内容，内容上同时存在表头)
     *                  序号	权利主体	申请量	权证量	未获授权数量
     *                  1	中国联通总部	60	    45	        15
     * @param sheetName sheet页
     * @return
     */
    public void createExcel(HSSFWorkbook workbook, List<T> data, Map<String, String> title, String sheetName) {
        Sheet sheet = workbook.createSheet(sheetName);
        int index = 0;
        int rowIndex = 0;
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        Set entrySet = title.entrySet();
        Iterator integer1 = entrySet.iterator();
        while (integer1.hasNext()) {
            Object obj = integer1.next();
            Map.Entry entry = (Map.Entry) obj;
            Row row = sheet.createRow(rowIndex);
            rowIndex++;
            HSSFCell cell = (HSSFCell) row.createCell(index);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(String.valueOf(entry.getKey()));
            index++;
            cell = (HSSFCell) row.createCell(index);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(String.valueOf(entry.getValue()));
            index = 0;
        }
        index = 0;
        // 输出
        try {
            for (T t : data
            ) {
                Row row = sheet.createRow(rowIndex);
                rowIndex++;
                Field[] declaredFields = t.getClass().getDeclaredFields();
                for (Field f : declaredFields
                ) {
                    //  BeanUtils.getProperty(t,t.getClass().getDeclaredField("index").name)
                    String property = BeanUtils.getProperty(t, f.getName());
                    if (StringUtils.isNotBlank(property)) {
                        HSSFCell cell = (HSSFCell) row.createCell(index);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(property);
                        index++;
                    }
                }
                index = 0;
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出单个sheet
     * @param workbook 对象
     * @param data 数据
     * @param title 表头
     * @param sheetName sheet页名称
     * @param filename 文件名称
     * @param response 响应对象
     */
    public void exportSingleExcel(HSSFWorkbook workbook, List<T> data, Map<String, String> title, String sheetName, String filename, HttpServletResponse response){
        Sheet sheet = workbook.createSheet(sheetName);
        int index = 0;
        int rowIndex = 0;
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 设置垂直居中
        cellStyle.setVerticalAlignment( VerticalAlignment.CENTER);
        // 设置居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        Set entrySet = title.entrySet();
        Iterator integer1 = entrySet.iterator();
        while (integer1.hasNext()) {
            Object obj = integer1.next();
            Map.Entry entry = (Map.Entry) obj;
            Row row = sheet.createRow(rowIndex);
            rowIndex++;
            HSSFCell cell = (HSSFCell) row.createCell(index);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(String.valueOf(entry.getKey()));
            index++;
            cell = (HSSFCell) row.createCell(index);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(entry.getValue()==null?"":String.valueOf(entry.getValue()));
            index = 0;
        }
        index = 0;
        // 输出
        try {
            for (T t : data
            ) {
                Row row = sheet.createRow(rowIndex);
                rowIndex++;
                Field[] declaredFields = t.getClass().getDeclaredFields();
                for (Field f : declaredFields
                ) {
                    String property = BeanUtils.getProperty(t, f.getName());
                    if (StringUtils.isNotBlank(property)) {
                        HSSFCell cell = (HSSFCell) row.createCell(index++);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(property);
                    }
                }
                index = 0;
            }
            //设置响应的编码方式;
            try {
                response.setCharacterEncoding("UTF-8");
                response.addHeader("Content-Disposition","attachment;filename="+java.net.URLEncoder.encode(filename, "utf-8"));
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                ServletOutputStream out = response.getOutputStream();
                workbook.write(out);
            } catch (Exception e) {
                System.out.println("导出"+filename+"出错！");
                e.printStackTrace();
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
    }


}

应用

public class A {

    public static void main(HttpServletResponse response) {
        List<TestClass> list = new ArrayList<>();
        TestClass testClass = new TestClass();
        // 创建表头
        testClass.setName("名字");
        testClass.setAge("年龄");
        testClass.setSex("性别");
        // 获取数据
        TestClass t1 = new TestClass("张三","18","女");
        TestClass t2 = new TestClass("李四","18","男");

        list.add(testClass);
        list.add(t1);
        list.add(t2);
        
        ExcelExportUtils<TestClass> exportUtils = new ExcelExportUtils<>();
        exportUtils.createExcel(list, "人员信息", "人员信息.xls", response);

    }
}

@Data
@AllArgsConstructor
@NoArgsConstructor
class TestClass{
    private String name;
    private String age;
    private String sex;
}