本文主要使用poi與easypoi工具來操作excel。
easypoi
官方文件：easypoi.mydoc.io/#text_20297…
新增依賴
<!-- easypoi 核心依賴包 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.1.0</version>
</dependency>
<!-- lombok 的依賴 可以省去物件的getter、setter方法-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.20</version>
<scope>provided</scope>
</dependency>
複製程式碼匯出
實體類
StudentModel類包括學生姓名，學生性別，出生日期以及進校日期：
package com.lamarsan.excel_demo.model;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.validation.constraints.NotBlank;
import java.io.Serializable;
import java.util.Date;
/**
* className: StudentModel
* description: TODO
*
* @author hasee
* @version 1.0
* @date 2019/7/11 17:43
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("studentEntity")
public class StudentModel implements Serializable {
/**
* id
*/
private String id;
/**
* 學生姓名
*/
@Excel(name = "學生姓名", height = 20, width = 30, isImportField = "true_st")
private String name;
/**
* 學生性別
*/
@Excel(name = "學生性別", replace = {"男_1", "女_2"}, suffix = "生", isImportField = "true_st")
private int sex;
@Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true_st", width = 20)
private Date birthday;
@Excel(name = "進校日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd")
private Date registrationDate;
}
複製程式碼匯出
使用建構函式構造相應的幾個資料後，編寫匯出語句。
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("2412312", "測試"),CourseModel.class, courseModels);
複製程式碼呼叫介面
下載後的excel表格如下。
匯入
匯入
編寫匯入語句。
@PostMapping(value = "/excelImport")
public Object importExcel(@RequestParam("file") MultipartFile file) {
//接收匯入陣列
List<StudentModel> studentModels = null;
try {
studentModels = ExcelImportUtil.importExcel(file.getInputStream(), StudentModel.class, new ImportParams());
} catch (Exception e) {
e.printStackTrace();
}
return studentModels;
}
複製程式碼呼叫介面
使用postman呼叫介面localhost:8080/excelReader/excelImport，並去掉表格中的表格頭，在body的formdata中選擇file，上傳剛剛匯出的檔案，key填寫file，得到返回結果如下。
合併單元格匯出
實體類定義：
課程實體類CourseModel：
package com.lamarsan.excel_demo.model;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.List;
/**
* className: CourseModel
* description: TODO
*
* @author hasee
* @version 1.0
* @date 2019/7/11 17:53
*/
@Data
@ExcelTarget("courseEntity")
@NoArgsConstructor
@AllArgsConstructor
public class CourseModel implements Serializable {
/**
* 主鍵
*/
private String id;
/**
* 課程名稱
*/
@Excel(name = "課程名稱", orderNum = "1", width = 25)
private String name;
/**
* 老師主鍵
*/
@ExcelEntity(id = "absent")
private TeacherModel mathTeacher;
@ExcelCollection(name = "學生", orderNum = "4")
private List<StudentModel> students;
}
複製程式碼教師實體類TeacherModel：
package com.lamarsan.excel_demo.model;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
/**
* className: TeacherModel
* description: TODO
*
* @author hasee
* @version 1.0
* @date 2019/7/11 17:55
*/
@Data
@ExcelTarget("teacherEntity")
@AllArgsConstructor
@NoArgsConstructor
public class TeacherModel implements Serializable {
private String id;
/** name */
@Excel(name = "主講老師_major,代課老師_absent", orderNum = "1", isImportField = "true_major,true_absent")
private String name;
}
複製程式碼匯出
使用建構函式構造相應的幾個資料後，編寫匯出語句。
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("2412312", "測試"),CourseModel.class, courseModels);
複製程式碼匯出結果
匯出結果如圖所示：
優化與合併：
可以發現，空著並不是很好看，可以合併單元格，進行美化操作。
@Excel(name = "課程名稱", orderNum = "1",needMerge = true, width = 25)
private String name;
@Excel(name = "主講老師_major,代課老師_absent",needMerge = true, orderNum = "1", isImportField = "true_major,true_absent")
private String name;
複製程式碼結果：
多sheet匯出
定義基礎配置物件。
匯出基本採用ExportParams 這個物件，進行引數配置；我們需要進行多Sheet匯出，那麼就需要定義一個基礎配置物件。
package com.lamarsan.excel_demo.common;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import java.util.List;
/**
* className: ExportView
* description: TODO
*
* @author hasee
* @version 1.0
* @date 2019/7/11 18:56
*/
public class ExportView {
public ExportView() {
}
private ExportParams exportParams;
private List<?> dataList;
private Class<?> cls;
public ExportParams getExportParams() {
return exportParams;
}
public void setExportParams(ExportParams exportParams) {
this.exportParams = exportParams;
}
public Class<?> getCls() {
return cls;
}
public void setCls(Class<?> cls) {
this.cls = cls;
}
public List<?> getDataList() {
return dataList;
}
public void setDataList(List<?> dataList) {
this.dataList = dataList;
}
public ExportView(Builder builder) {
this.exportParams = builder.exportParams;
this.dataList = builder.dataList;
this.cls = builder.cls;
}
public static class Builder {
private ExportParams exportParams = null;
private List<?> dataList = null;
private Class<?> cls = null;
public Builder() {
}
public Builder exportParams(ExportParams exportParams) {
this.exportParams = exportParams;
return this;
}
public Builder dataList(List<?> dataList) {
this.dataList = dataList;
return this;
}
public Builder cls(Class<?> cls) {
this.cls = cls;
return this;
}
public ExportView create() {
return new ExportView(this);
}
}
}
複製程式碼解析
最後在實現呼叫的方法中，對整個集合進行配置和解析。
List<Map<String, Object>> exportParamList = Lists.newArrayList();
ExportView studentView = new ExportView(new ExportParams("學生表","表1",XSSF), studentModelList, StudentModel.class);
ExportView courseView = new ExportView(new ExportParams("課程表","表2",XSSF), courseModelList, CourseModel.class);
List<ExportView> exportViews = new ArrayList<>();
//匯入studentlist
exportViews.add(studentView);
//匯入courselist
exportViews.add(courseView);
for (ExportView view : exportViews) {
Map<String, Object> valueMap = Maps.newHashMap();
valueMap.put("title", view.getExportParams());
valueMap.put("data", view.getDataList());
valueMap.put("entity", view.getCls());
exportParamList.add(valueMap);
}
// 執行方法
Workbook workBook = ExcelExportUtil.exportExcel(exportParamList, XSSF);
ExcelUtil.downloadExcel(response, workBook, "計算機二班選課情況");
複製程式碼- 匯出結果：
Poi
從本小節開始，將脫離easyExcel工具，轉而使用POI工具。
匯出圖片到Excel
1）傳入圖片IO流，如下所示：
//先把讀進來的圖片放到一個ByteArrayOutputStream中，以便產生ByteArray
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
ByteArrayOutputStream byteArrayOut2 = new ByteArrayOutputStream();
ByteArrayOutputStream byteArrayOut3 = new ByteArrayOutputStream();
//將圖片讀到BufferedImage
bufferImg = ImageIO.read(new File(\"D:\\\\圖片\\\\140.png\"));
bufferImg2 = ImageIO.read(new File(\"D:\\\\圖片\\\\137.png\"));
bufferImg3 = ImageIO.read(new File(\"D:\\\\圖片\\\\139.png\"));
// 將圖片寫入流中
ImageIO.write(bufferImg, \"png\", byteArrayOut);
ImageIO.write(bufferImg2, \"png\", byteArrayOut2);
ImageIO.write(bufferImg3, \"png\", byteArrayOut3);
複製程式碼2）利用Drawing將圖片寫入EXCEL，如下所示：
Drawing patriarch = sheet.createDrawingPatriarch();
/**
* 該建構函式有8個引數
* 前四個引數是控制圖片在單元格的位置，分別是圖片距離單元格left，top，right，bottom的畫素距離
* 後四個引數，前兩個表示圖片左上角所在的cellNum和 rowNum，後兩個引數對應的表示圖片右下角所在的cellNum和 rowNum，
* excel中的cellNum和rowNum的index都是從0開始的
*
*/
//圖片一匯出到單元格B2中
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short) 7, 4, (short) 8, 5);
HSSFClientAnchor anchor2 = new HSSFClientAnchor(0, 0, 0, 0,(short) 7, 5, (short) 8, 6);
HSSFClientAnchor anchor3 = new HSSFClientAnchor(0, 0, 0, 0,(short) 7, 6, (short) 8, 7);
// 插入圖片
patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
patriarch.createPicture(anchor2, workBook.addPicture(byteArrayOut2.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
patriarch.createPicture(anchor3, workBook.addPicture(byteArrayOut3.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
複製程式碼4）最終效果如下圖所示：
多EXCEL打包
1）編寫檔案壓縮工具類如下，編寫srcFiles與zipFile的資訊：
package com.lamarsan.excel_demo.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
/**
* className: ZipMultiFile
* description: TODO
*
* @author hasee
* @version 1.0
* @date 2019/7/12 17:46
*/
public class ZipMultiFile {
public static void main(String[] args) {
File[] srcFiles = { new File("D:\\記事本\\公司\\下載2.xls"), new File("D:\\記事本\\公司\\標書遞交_投標檔案遞交記錄表（非物資）.xls") };
File zipFile = new File("D:\\記事本\\公司\\ZipFile.zip");
// 呼叫壓縮方法
zipFiles(srcFiles, zipFile);
}
public static void zipFiles(File[] srcFiles, File zipFile) {
// 判斷壓縮後的檔案存在不，不存在則建立
if (!zipFile.exists()) {
try {
zipFile.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}
// 建立 FileOutputStream 物件
FileOutputStream fileOutputStream = null;
// 建立 ZipOutputStream
ZipOutputStream zipOutputStream = null;
// 建立 FileInputStream 物件
FileInputStream fileInputStream = null;
try {
// 例項化 FileOutputStream 物件
fileOutputStream = new FileOutputStream(zipFile);
// 例項化 ZipOutputStream 物件
zipOutputStream = new ZipOutputStream(fileOutputStream);
// 建立 ZipEntry 物件
ZipEntry zipEntry = null;
// 遍歷原始檔陣列
for (int i = 0; i < srcFiles.length; i++) {
// 將原始檔陣列中的當前檔案讀入 FileInputStream 流中
fileInputStream = new FileInputStream(srcFiles[i]);
// 例項化 ZipEntry 物件，原始檔陣列中的當前檔案
zipEntry = new ZipEntry(srcFiles[i].getName());
zipOutputStream.putNextEntry(zipEntry);
// 該變數記錄每次真正讀的位元組個數
int len;
// 定義每次讀取的位元組陣列
byte[] buffer = new byte[1024];
while ((len = fileInputStream.read(buffer)) > 0) {
zipOutputStream.write(buffer, 0, len);
}
}
zipOutputStream.closeEntry();
zipOutputStream.close();
fileInputStream.close();
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
複製程式碼2）壓縮結果如圖所示：
Zip包解壓，獲取excel檔案並進行分析
1）編寫介面如下，主要思路是解壓zip，獲取每個檔案的io流，然後建立工作簿類獲取到每個類的cell資訊：
@PostMapping(value = "/importZip")
private Object importZip(@RequestParam("file") MultipartFile zipFile) {
//獲得檔名
String fileName = zipFile.getOriginalFilename();
//檢查檔案
if ("".equals(fileName)) {
System.out.println("檔案為空");
}
List<List<PoiModel>> poiModelLists = new ArrayList<>();
try {
//再本地建立一個檔案，讀取此檔案 防止瀏覽器讀取的檔案被損壞
File localFile = new File("D:\\記事本\\公司\\fyJyqdYhqdxxZip.zip");
FileOutputStream ftpOutstream = new FileOutputStream(localFile);
byte[] appByte = zipFile.getBytes();
ftpOutstream.write(appByte);
ftpOutstream.flush();
ftpOutstream.close();//建立完畢後刪除
File file = new File("D:\\記事本\\公司\\fyJyqdYhqdxxZip.zip");
//不解壓直接讀取,加上UTF-8解決亂碼問題,file轉ZipInputStream
ZipInputStream in = new ZipInputStream(new FileInputStream(file), Charset.forName("GBK"));
//不解壓直接讀取,加上UTF-8解決亂碼問題,ZipInputStream轉BufferedReader
BufferedReader br = new BufferedReader(new InputStreamReader(in, "gbk"));
//把InputStream轉成ByteArrayOutputStream 多次使用
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ZipEntry ze;
while ((ze = in.getNextEntry()) != null) {
if (ze.isDirectory()) {
//如果是目錄，不處理
continue;
}
try {
String zipFileName = ze.getName();
//不是我們指定的檔案不匯入，XXXXX.市場化清單.xls
//if (zipFileName != null && zipFileName.indexOf(".") != -1
// && zipFileName.equals(zipFileName.substring(0, zipFileName.indexOf(".xls")) + "市場化清單.xls")) {
// continue;
//}
byte[] buffer = new byte[1024];
int len;
while ((len = in.read(buffer)) > -1) {
baos.write(buffer, 0, len);
}
baos.flush();
InputStream stream = new ByteArrayInputStream(baos.toByteArray());
//獲取Excel物件
HSSFWorkbook wb = new HSSFWorkbook(stream);
int sheets = wb.getNumberOfSheets();
for (int i = 0; i < sheets; i++) {
HSSFSheet sheet = wb.getSheetAt(i);
// 獲取多少行
List<PoiModel> poiModels = new ArrayList<>();
int rows = sheet.getPhysicalNumberOfRows();
for (int j = 0; j < rows; j++) {
//獲取Row物件
HSSFRow row = sheet.getRow(j);
//獲取Cell物件的值並輸出
PoiModel poiModel = new PoiModel(row.getCell(0).toString(), row.getCell(1).toString(), row.getCell(2).toString(), row.getCell(3).toString(), row.getCell(4).toString(), row.getCell(5).toString(), row.getCell(6).toString(), row.getCell(7).toString(), row.getCell(8).toString(), row.getCell(9).toString(), row.getCell(10).toString());
System.out.println(row.getCell(0) + " " + row.getCell(1));
poiModels.add(poiModel);
}
poiModelLists.add(poiModels);
}
baos.reset();
} catch (Exception e) {
e.printStackTrace();
}
}
br.close();
in.close();
baos.close();
//處理完畢刪除
localFile.delete();
} catch (IOException e) {
e.printStackTrace();
}
return poiModelLists;
}
複製程式碼2）返回結果如下：
專案github地址：github.com/lamarsan/ex…