Java导出数据到Excel

偏执的太偏执、 2024-03-22 23:37 83阅读 0赞

Java导出数据到Excel分3步处理  
1、构建Workbook 数据  
2、设置Workbook 格式  
3、导出到Excel

--------------------

## 1、构建Workbook 数据 ##

public static void buildData(Workbook wb, List<Person> list) {
        
        Sheet sheetName = wb.createSheet("sheetName");
        
        Row row = sheetName.createRow(0);
        row.createCell(0).setCellValue("Id");
        row.createCell(1).setCellValue("Name");
        row.createCell(2).setCellValue("Age");
        row.createCell(3).setCellValue("NickName");
        
        for (int i = 0; i < list.size(); i++) {
        
            Person person = list.get(i);
            row = sheetName.createRow(i + 1);
            row.createCell(0).setCellValue(person.getId());
            row.createCell(1).setCellValue(person.getName());
            row.createCell(2).setCellValue(person.getAge());
            row.createCell(3).setCellValue(person.getNickName());
        }
        
        wb.setSheetName(0, "sheet");
    }

--------------------

## 2、设置Workbook 格式 ##

public static void buildStyle(Workbook wb) {
        
        Sheet sheet;
        Row row;
        for (int s = 0; s < wb.getNumberOfSheets(); s++) {
        
            sheet = wb.getSheetAt(s);
            for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
        
                row = sheet.getRow(r);
                for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
        
                    if (r == 0) {
         // 表头
                        row.getCell(c).setCellStyle(ExcelStyleUtils.headerStyle(wb));
                        sheet.autoSizeColumn(c);
                    } else {
         // 数据
                        row.getCell(c).setCellStyle(ExcelStyleUtils.cellsStyle(wb));
                        if (sheet.getColumnWidth(c) > 50 * 256) {
        
                            sheet.setColumnWidth(c, 50 * 256);
                        }
                    }
                }
            }
        }
    }

--------------------

## 3、导出到Excel ##

public static void writeExcel(Workbook workbook) throws Exception {
        
        ExcelUtils.deleteOldExcelFile(OUT_PATH);
        ExcelUtils.makeExcelFile(OUT_PATH);
        FileOutputStream fileOutputStream = new FileOutputStream(genExcelName(OUT_PATH), false);
        workbook.write(fileOutputStream);
        fileOutputStream.close();
    }
    public static void writeExcel2(Workbook workbook) throws Exception {
        
        ExcelUtils.deleteOldExcelFile(OUT_PATH);
        ExcelUtils.makeExcelFile(OUT_PATH);
        OutputStream outputStream = new FileOutputStream(genExcelName(OUT_PATH));
        workbook.write(outputStream);
        outputStream.close();
    }
    
    private static String genExcelName(String path) {
        
        String postName = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHMMSS"));
        return path + "/输出" + postName + ".xlsx";
    }
    
    public static void deleteOldExcelFile(String path) throws Exception {
        
        File file = new File(path);
        if (file.exists() && file.isDirectory()) {
        
            FileUtils.deleteDirectory(file);
        }
    }
    public static void makeExcelFile(String path) throws Exception {
        
        File file = new File(path);
        if (!file.exists()) {
        
            boolean mkdirs = file.mkdirs();
            if (!mkdirs) {
        
                throw new IOException("mkdirs failed");
            }
        }
    }

--------------------

## 4、UT ##

@Test
    public void testWriteExcel2() throws Exception {
        
        List<Person> persons = new ArrayList<>();
        persons.add(new Person(10, "John", 11, "nickName"));
        persons.add(new Person(11, "John1", 11, "nickName1"));
        persons.add(new Person(12, "John2", 11, "nickName2"));
        persons.add(new Person(13, "John3", 11, "nickName3"));
        persons.add(new Person(14, "John4", 11, "nickName4"));
        
        XSSFWorkbook wb = new XSSFWorkbook();
        buildData(wb, persons); // 构建数据
        
        buildStyle(wb); // 设置格式
        
    	writeExcel(wb); 或 writeExcel2(wb); // 导出数据
    }

--------------------

## 5、结果展示 ##

![在这里插入图片描述][faa63fac10b54fceb2eeb13d3b8247e2.png]

--------------------

## 6、核心import ##

import model.Person;
    import org.apache.commons.io.FileUtils;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import java.io.*;
    import java.time.LocalDateTime;
    import java.time.format.DateTimeFormatter;

--------------------

## 7、maven 依赖 ##

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
    
    <dependency>
        <groupId>commons-io</groupId>
        <artifactId>commons-io</artifactId>
        <version>2.11.0</version>
    </dependency>
    
    
    
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-api</artifactId>
        <version>2.15.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-core</artifactId>
        <version>2.15.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-slf4j-impl</artifactId>
        <version>2.15.0</version>
    </dependency>

[faa63fac10b54fceb2eeb13d3b8247e2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/27f81e887dd94406ade2a3c56ff35e33.png