Java操作Excel完成导入导出

最新推荐文章于 2022-11-17 14:08:29 发布

踏浪

最新推荐文章于 2022-11-17 14:08:29 发布

阅读量1.5k

点赞数

分类专栏：面试文章标签： poi excel导出

本文链接：https://blog.csdn.net/u010559460/article/details/121607146

版权

面试专栏收录该内容

10 篇文章 2 订阅

订阅专栏

介绍

本章教程介绍使用Java程序针对Excel 07版本的导入导出操作。

环境搭建

创建Maven工程

过程略

引入依赖

        <!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>

创建实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {

    private Long id;
    private String name;
    private String password;
    private Integer age;
    private Integer height;

}

导出Excel

构建导出数据

        User u1 = new User(1L, "张三", "123456", 18, 175);
        User u2 = new User(2L, "李四", "123456", 19, 176);
        User u3 = new User(3L, "王五", "123456", 20, 177);
        User u4 = new User(4L, "赵六", "123456", 21, 178);
        User u5 = new User(5L, "田七", "123456", 22, 179);

        ArrayList<User> list = new ArrayList<>();
        list.add(u1);
        list.add(u2);
        list.add(u3);
        list.add(u4);
        list.add(u5);

数据基本导出操作

        Workbook wb = new XSSFWorkbook(); // 创建工作簿
        Sheet sheet = wb.createSheet("用户信息");  // 创建工作表
        for(int rownum = 0; rownum < list.size(); rownum++) {
            // 产生一行
            Row row = sheet.createRow(rownum);
            User user = list.get(rownum);
            // 通过反射获取到 User类中所有属性
            Field[] declaredFields = User.class.getDeclaredFields(); 
            for(int column = 0; column < 5; column++) {
                Field field = declaredFields[column];
                field.setAccessible(true); // 打开访问控制，才能获取属性的值
                Cell cell = row.createCell(column);
                cell.setCellValue(field.get(user).toString());
            }
        }
        // 创建文件输出流，将 Workbook 中的内容写入到流中
        try (OutputStream fileOut = new FileOutputStream("app.xlsx")) {
            wb.write(fileOut);
        }
        wb.close();

实现效果：

添加列信息

上一步操作中，我们只看到了数据，但不清楚这些数据代表什么信息。

这一步将完成列信息的添加操作

        String[] names = new String[]{"id", "用户名", "密码", "年龄", "升高"};

        Workbook wb = new XSSFWorkbook(); // 创建工作簿
        Sheet sheet = wb.createSheet("用户信息");
        /*
            添加列信息
         */
        Row row1 = sheet.createRow(0);
        for (int i = 0; i < names.length; i++) {
            Cell cell = row1.createCell(i);
            cell.setCellValue(names[i]);
        }

        // 由于添加了列头，占用了一行，因此数据从下标为1开始
        for(int rownum = 1; rownum < list.size() + 1; rownum++) {  
            // 产生一行
            Row row = sheet.createRow(rownum);
            User user = list.get(rownum-1);
            Field[] declaredFields = User.class.getDeclaredFields();
            for(int column = 0; column < 5; column++) {
                Field field = declaredFields[column];
                field.setAccessible(true);
                Cell cell = row.createCell(column);
                cell.setCellValue(field.get(user).toString());
            }
        }
        try (OutputStream fileOut = new FileOutputStream("app2.xlsx")) {
            wb.write(fileOut);
        }
        wb.close();

实现效果：

样式添加

给列头和表数据添加样式，并解决在单元格中如果存的是数字，会在单元格左上角出现绿色三角的问题。

        String[] names = new String[]{"id", "用户名", "密码", "年龄", "身高"};

        Workbook wb = new XSSFWorkbook(); // 创建工作簿
        Sheet sheet = wb.createSheet("用户信息");

        Row row1 = sheet.createRow(0);
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);


        for (int i = 0; i < names.length; i++) {
            Cell cell = row1.createCell(i);
            cell.setCellValue(names[i]);
            cell.setCellStyle(style);
        }


        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);

        for(int rownum = 1; rownum < list.size() + 1; rownum++) {
            // 产生一行
            Row row = sheet.createRow(rownum);
            User user = list.get(rownum-1);
            Field[] declaredFields = User.class.getDeclaredFields();
            for(int column = 0; column < 5; column++) {
                Field field = declaredFields[column];
                field.setAccessible(true);
                Cell cell = row.createCell(column);
                if(field.getGenericType().toString().equals("class java.lang.Long")) {
                    cell.setCellValue(Long.valueOf(field.get(user).toString()));
                } else if(field.getGenericType().toString().equals("class java.lang.Integer")) {
                    cell.setCellValue(Integer.valueOf(field.get(user).toString()));
                } else {
                    cell.setCellValue(field.get(user).toString());
                }
                cell.setCellStyle(style);
            }
        }
        try (OutputStream fileOut = new FileOutputStream("app4.xlsx")) {
            wb.write(fileOut);
        }
        wb.close();

实现效果：

小结

一、创建工作簿，并以流的形式输出，代码：

Workbook wb = new XSSFWorkbook();
...
try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
    wb.write(fileOut);
}

二、通过 sheet创建 Row，通过Row创建 Cell，然后将调用 cell.setCellValue 方法将数据写入单元格

三、单元格添加样式，先通过 CellStyle style = wb.createCellStyle() 创建样式，然后调用

style.setXXX方法设置样式值，最后在 cell.setCellValue 给单元格赋值的同时，给单元格的样式也赋值 cell.setCellStyle(style)

四、在调用 cell.setCellValue 方法往单元格赋值之前，需要判断存放值的类型，可能会出现类型转换，如下：

if(field.getGenericType().toString().equals("class java.lang.Long")) {
    cell.setCellValue(Long.valueOf(field.get(user).toString()));
}

导入Excel

基本操作

读取 Excel中的内容，并打印出来

        // 使用 InputStream 方式，需要占用更多内存
        Workbook wb = WorkbookFactory.create(new FileInputStream("app4.xlsx"));
        Sheet sheet1 = wb.getSheetAt(0);
        for (Sheet sheet : wb) {
            for (Row row : sheet) {
                for (Cell cell : row) {
                    switch (cell.getCellType()) {
                        case STRING:
                            System.out.print(cell.getRichStringCellValue().getString() + "\t");
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                System.out.print(cell.getDateCellValue() + "\t");
                            } else {
                                System.out.print(cell.getNumericCellValue() + "\t");
                            }
                            break;
                        case BOOLEAN:
                            System.out.print(cell.getBooleanCellValue() + "\t");
                            break;
                        case FORMULA:
                            System.out.print(cell.getCellFormula() + "\t");
                            break;
                        case BLANK:
                            System.out.print("\t");
                            break;
                        default:
                            System.out.print("\t");
                    }
                }
                System.out.println();
            }
        }

实现效果：