Apache POI的maven坐標(biāo)：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
將數(shù)據(jù)寫入Excel文件
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;
public class POITest {
/**
* 基于POI向Excel文件寫入數(shù)據(jù)
* @throws Exception
*/
public static void write() throws Exception{
//在內(nèi)存中創(chuàng)建一個(gè)Excel文件對象
XSSFWorkbook excel = new XSSFWorkbook();
//創(chuàng)建Sheet頁
XSSFSheet sheet = excel.createSheet("itcast");
//在Sheet頁中創(chuàng)建行，0表示第1行
XSSFRow row1 = sheet.createRow(0);
//創(chuàng)建單元格并在單元格中設(shè)置值，單元格編號也是從0開始，1表示第2個(gè)單元格
row1.createCell(1).setCellValue("姓名");
row1.createCell(2).setCellValue("城市");
XSSFRow row2 = sheet.createRow(1);
row2.createCell(1).setCellValue("張三");
row2.createCell(2).setCellValue("北京");
XSSFRow row3 = sheet.createRow(2);
row3.createCell(1).setCellValue("李四");
row3.createCell(2).setCellValue("上海");
FileOutputStream out = new FileOutputStream(new File("D:\\itcast.xlsx"));
//通過輸出流將內(nèi)存中的Excel文件寫入到磁盤上
excel.write(out);
//關(guān)閉資源
out.flush();
out.close();
excel.close();
}
public static void main(String[] args) throws Exception {
write();
}
}
讀取Excel文件中的數(shù)據(jù)
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;
public class POITest {
/**
* 基于POI讀取Excel文件
* @throws Exception
*/
public static void read() throws Exception{
FileInputStream in = new FileInputStream(new File("D:\\itcast.xlsx"));
//通過輸入流讀取指定的Excel文件
XSSFWorkbook excel = new XSSFWorkbook(in);
//獲取Excel文件的第1個(gè)Sheet頁
XSSFSheet sheet = excel.getSheetAt(0);
//獲取Sheet頁中的最后一行的行號
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i <= lastRowNum; i++) {
//獲取Sheet頁中的行
XSSFRow titleRow = sheet.getRow(i);
//獲取行的第2個(gè)單元格
XSSFCell cell1 = titleRow.getCell(1);
//獲取單元格中的文本內(nèi)容
String cellValue1 = cell1.getStringCellValue();
//獲取行的第3個(gè)單元格
XSSFCell cell2 = titleRow.getCell(2);
//獲取單元格中的文本內(nèi)容
String cellValue2 = cell2.getStringCellValue();
System.out.println(cellValue1 + " " +cellValue2);
}
//關(guān)閉資源
in.close();
excel.close();
}
public static void main(String[] args) throws Exception {
read();
}
}
項(xiàng)目使用
實(shí)現(xiàn)步驟
1). 設(shè)計(jì)Excel模板文件
2). 查詢近30天的運(yùn)營數(shù)據(jù)
3). 將查詢到的運(yùn)營數(shù)據(jù)寫入模板文件
4). 通過輸出流將Excel文件下載到客戶端瀏覽器
1)controller：
/**
* 導(dǎo)出運(yùn)營數(shù)據(jù)報(bào)表
* @param response
*/
@GetMapping("/export")
@ApiOperation("導(dǎo)出運(yùn)營數(shù)據(jù)報(bào)表")
public void export(HttpServletResponse response){
reportService.exportBusinessData(response);
}
2)service:
/**
* 導(dǎo)出運(yùn)營數(shù)據(jù)報(bào)表
* @param response
*/
@GetMapping("/export")
@ApiOperation("導(dǎo)出運(yùn)營數(shù)據(jù)報(bào)表")
public void export(HttpServletResponse response){
reportService.exportBusinessData(response);
}
3)Service層實(shí)現(xiàn)類
在ReportServiceImpl實(shí)現(xiàn)類中實(shí)現(xiàn)導(dǎo)出運(yùn)營數(shù)據(jù)報(bào)表的方法:
提前將資料中的運(yùn)營數(shù)據(jù)報(bào)表模板.xlsx拷貝到項(xiàng)目的resources/template目錄中
/**導(dǎo)出近30天的運(yùn)營數(shù)據(jù)報(bào)表
* @param response
**/
public void exportBusinessData(HttpServletResponse response) {
LocalDate begin = LocalDate.now().minusDays(30);
LocalDate end = LocalDate.now().minusDays(1);
//查詢概覽運(yùn)營數(shù)據(jù)，提供給Excel模板文件
BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(begin,LocalTime.MIN), LocalDateTime.of(end, LocalTime.MAX));
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("template/運(yùn)營數(shù)據(jù)報(bào)表模板.xlsx");
try {
//基于提供好的模板文件創(chuàng)建一個(gè)新的Excel表格對象
XSSFWorkbook excel = new XSSFWorkbook(inputStream);
//獲得Excel文件中的一個(gè)Sheet頁
XSSFSheet sheet = excel.getSheet("Sheet1");
sheet.getRow(1).getCell(1).setCellValue(begin + "至" + end);
//獲得第4行
XSSFRow row = sheet.getRow(3);
//獲取單元格
row.getCell(2).setCellValue(businessData.getTurnover());
row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
row.getCell(6).setCellValue(businessData.getNewUsers());
row = sheet.getRow(4);
row.getCell(2).setCellValue(businessData.getValidOrderCount());
row.getCell(4).setCellValue(businessData.getUnitPrice());
for (int i = 0; i < 30; i++) {
LocalDate date = begin.plusDays(i);
//準(zhǔn)備明細(xì)數(shù)據(jù)
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());
}
//通過輸出流將文件下載到客戶端瀏覽器中
ServletOutputStream out = response.getOutputStream();
excel.write(out);
//關(guān)閉資源
out.flush();
out.close();
excel.close();
}catch (IOException e){
e.printStackTrace();
}
}
