介绍

可以使用POI在JAVA程序中对Office各种文件进行读写操作

核心代码

导入依赖

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

写操作

package com.sky.test;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;

/**
 * 使用POI操作Excel文件
 */
public class POITest {

    /**
     * 通过POI创建Excel文件并且写入文件内容
     */
    public static void write() throws Exception{
        //在内存中创建一个Excel文件
        XSSFWorkbook excel = new XSSFWorkbook();
        //在Excel文件中创建一个Sheet页
        XSSFSheet sheet = excel.createSheet("info");
        //在Sheet中创建行对象,rownum编号从0开始
        XSSFRow row = sheet.createRow(1);
        //创建单元格并且写入文件内容
        row.createCell(1).setCellValue("姓名");
        row.createCell(2).setCellValue("城市");

        //创建一个新行
        row = sheet.createRow(2);
        row.createCell(1).setCellValue("张三");
        row.createCell(2).setCellValue("北京");

        row = sheet.createRow(3);
        row.createCell(1).setCellValue("李四");
        row.createCell(2).setCellValue("南京");

        //通过输出流将内存中的Excel文件写入到磁盘
        FileOutputStream out = new FileOutputStream(new File("E:\\桌面\\learning-files\\code\\waimai\\init\\excel\\excel.xlsx"));
        excel.write(out);

        //关闭资源
        out.close();
        excel.close();
    }

    public static void main(String[] args) throws Exception {
        write();
    }
}

读操作

package com.sky.test;  
  
import org.apache.poi.xssf.usermodel.XSSFCell;  
import org.apache.poi.xssf.usermodel.XSSFRow;  
import org.apache.poi.xssf.usermodel.XSSFSheet;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
import java.io.File;  
import java.io.FileInputStream;  
import java.io.FileOutputStream;  
import java.io.InputStream;  
  
/**  
* 使用POI操作Excel文件  
*/  
public class POITest {  
  
    /**  
    * 通过POI读取Excel文件中的内容  
    * @throws Exception  
    */  
    public static void read() throws Exception{  
    InputStream in = new FileInputStream(new File("E:\\桌面\\learning-files\\code\\waimai\\init\\excel\\excel.xlsx"));  

    //读取磁盘上已经存在的Excel文件  
    XSSFWorkbook excel = new XSSFWorkbook(in);  
    //读取Excel文件中的第一个Sheet页  
    XSSFSheet sheet = excel.getSheetAt(0);  

    //获取Sheet中最后一行的行号  
    int lastRowNum = sheet.getLastRowNum();  

    for (int i = 1; i <= lastRowNum ; i++) {  
        //获得某一行  
        XSSFRow row = sheet.getRow(i);  
        //获得单元格对象  
        String cellValue1 = row.getCell(1).getStringCellValue();  
        String cellValue2 = row.getCell(2).getStringCellValue();  
        System.out.println(cellValue1 + " " + cellValue2);  
    }  

    //关闭资源  
    in.close();  
    excel.close();  
    }  

    public static void main(String[] args) throws Exception {  
    read();  
    }  
}

案例

创建excel模板

导出报表

/**  
* 导出运营数据报表  
* @param response  
*/  
public void exportBusinessData(HttpServletResponse response) {  

    //1. 查询数据库，获取营业数据---查询最近30天的运营数据  
    LocalDate dateBegin = LocalDate.now().minusDays(30);  
    LocalDate dateEnd = LocalDate.now().minusDays(1);  

    //查询概览数据  
    BusinessDataVO businessDataVO = workspaceService.getBusinessData(LocalDateTime.of(dateBegin, LocalTime.MIN), LocalDateTime.of(dateEnd, LocalTime.MAX));  

    //2. 通过POI将数据写入到Excel文件中  
    InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");  

    try {  
    //基于模板文件创建一个新的Excel文件  
    XSSFWorkbook excel = new XSSFWorkbook(in);  

    //获取表格文件的Sheet页  
    XSSFSheet sheet = excel.getSheet("Sheet1");  

    //填充数据--时间  
    sheet.getRow(1).getCell(1).setCellValue("时间：" + dateBegin + "至" + dateEnd);  

    //获得第4行  
    XSSFRow row = sheet.getRow(3);  
    row.getCell(2).setCellValue(businessDataVO.getTurnover());  
    row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());  
    row.getCell(6).setCellValue(businessDataVO.getNewUsers());  

    //获得第5行  
    row = sheet.getRow(4);  
    row.getCell(2).setCellValue(businessDataVO.getValidOrderCount());  
    row.getCell(4).setCellValue(businessDataVO.getUnitPrice());  

    //填充明细数据  
    for (int i = 0; i < 30; i++) {  
    LocalDate date = dateBegin.plusDays(i);  
    //查询某一天的营业数据  
    BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));  

    //获得某一行  
    row = sheet.getRow(7 + i);  
    row.getCell(1).setCellValue(date.toString());  
    row.getCell(2).setCellValue(businessData.getTurnover());  
    row.getCell(3).setCellValue(businessData.getValidOrderCount());  
    row.getCell(4).setCellValue(businessData.getOrderCompletionRate());  
    row.getCell(5).setCellValue(businessData.getUnitPrice());  
    row.getCell(6).setCellValue(businessData.getNewUsers());  
    }  

    //3. 通过输出流将Excel文件下载到客户端浏览器  
    ServletOutputStream out = response.getOutputStream();  
    excel.write(out);  

    //关闭资源  
    out.close();  
    excel.close();  
    } catch (IOException e) {  
    e.printStackTrace();  
    }  
  
}