How to export and merge Excel cells in Java-javaTutorial-php.cn

How to export and merge Excel cells in Java

王林

Release： 2023-05-10 17:16:06

forward

4341 people have browsed it

1. Preparation work

Before starting to implement Excel export, we need to prepare the following tools and environment:

1.JDK environment

3.Apache POI library

Apache POI is a Java library that can be used to read and write files in Microsoft Office formats, including Excel, Word, and PowerPoint files. We need to introduce the Apache POI library into the project.

3.Excel template

Excel template refers to the style and format of the Excel file we want to export, including column names, row heights, fonts, colors, etc. of the table. We can create a template in Excel and then populate the template with data. This ensures that the format and style of the exported Excel files are consistent.

2. Implementation steps

1. Create an Excel file

First, we need to create an Excel file in Java. Excel files can be created using the Workbook class in the Apache POI library. The Workbook class has two implementation classes: HSSFWorkbook and XSSFWorkbook. HSSFWorkbook is used to create Excel files in .xls format, and XSSFWorkbook is used to create Excel files in .xlsx format. We can choose the appropriate implementation class according to our needs.

The following is the code to create an Excel file:

// 创建工作簿
Workbook workbook = new HSSFWorkbook();
// 创建工作表
Sheet sheet = workbook.createSheet("Sheet1");

Copy after login

2. Fill the table data

Next, we need to fill the data into the table. Excel tables can be manipulated using the Row and Cell classes in the Apache POI library. Row represents a row in the table, and Cell represents a cell in the table. We can create the header first and then fill the data into the table.

The following is the code to fill the table data:

// 创建表头行
Row headerRow = sheet.createRow(0);
// 创建表头单元格
Cell headerCell1 = headerRow.createCell(0);
headerCell1.setCellValue("姓名");
Cell headerCell2 = headerRow.createCell(1);
headerCell2.setCellValue("年龄");

// 填充数据
List<User> userList = getUserList();
for (int i = 0; i < userList.size(); i++) {
    User user = userList.get(i);
    Row dataRow = sheet.createRow(i + 1);
    Cell dataCell1 = dataRow.createCell(0);
    dataCell1.setCellValue(user.getName());
    Cell dataCell2 = dataRow.createCell(1);
    dataCell2.setCellValue(user.getAge());
}

Copy after login

3. Merge cells

If you need to merge some cells in the table, you can use the Apache POI library The CellRangeAddress class is implemented. CellRangeAddress represents the merged area of cells, including the starting row, ending row, starting column, and ending column. We can create a CellRangeAddress object and then apply it to the cells in the table.

The following is the code for merging cells:

// 合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1);
sheet.addMergedRegion(region);

Copy after login

4. Export Excel file

Finally, we need to export the generated Excel file to the local or server. Excel files can be output to disk using the FileOutputStream class in Java.

The following is the code to export the Excel file:

// 导出Excel文件
File file = new File("user.xls");
FileOutputStream fos = new FileOutputStream(file);
workbook.write(fos);
fos.close();

Copy after login

3. Complete code

public static void exportExcel() throws Exception {
    // 创建工作簿
    Workbook workbook = new HSSFWorkbook();
    // 创建工作表
    Sheet sheet = workbook.createSheet("Sheet1");

    // 创建表头行
    Row headerRow = sheet.createRow(0);
    // 创建表头单元格
    Cell headerCell1 = headerRow.createCell(0);
    headerCell1.setCellValue("姓名");
    Cell headerCell2 = headerRow.createCell(1);
    headerCell2.setCellValue("年龄");

    // 填充数据
    List<User> userList = getUserList();
    for (int i = 0; i < userList.size(); i++) {
        User user = userList.get(i);
        Row dataRow = sheet.createRow(i + 1);
        Cell dataCell1 = dataRow.createCell(0);
        dataCell1.setCellValue(user.getName());
        Cell dataCell2 = dataRow.createCell(1);
        dataCell2.setCellValue(user.getAge());
    }

    // 合并单元格
    CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1);
    sheet.addMergedRegion(region);

    // 导出Excel文件
    File file = new File("user.xls");
    FileOutputStream fos = new FileOutputStream(file);
    workbook.write(fos);
    fos.close();
}

public static List<User> getUserList() {
    List<User> userList = new ArrayList<>();
    userList.add(new User("张三", 20));
    userList.add(new User("李四", 25));
    userList.add(new User("王五", 30));
    return userList;
}

public static class User {
    private String name;
    private int age;

    public User(String name, int age) {
        this.name = name;
        this.age = age;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

Copy after login

The above is the detailed content of How to export and merge Excel cells in Java. For more information, please follow other related articles on the PHP Chinese website!