說(shuō)明
- EasyExcel重寫(xiě)了poi對(duì)07版Excel的解析，降低了內(nèi)存消耗查蓉，對(duì)模型轉(zhuǎn)換進(jìn)行了封裝乌询，然后寫(xiě)了下簡(jiǎn)單案例。
- 完整代碼地址在結(jié)尾Ｍ阊小妹田！
第一步，導(dǎo)入maven依賴
<!-- 阿里開(kāi)源框架EasyExcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
<!-- 將excel轉(zhuǎn)成csv格式的poi依賴 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
第二步鹃共，創(chuàng)建模型解析監(jiān)聽(tīng)器鬼佣，以下提供兩種方式
第一種，創(chuàng)建模型解析監(jiān)聽(tīng)器類(lèi)及汉，ModelExcelListener
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
* @Description: 模型解析監(jiān)聽(tīng)器 -- 每解析一行會(huì)回調(diào)invoke()方法沮趣，整個(gè)excel解析結(jié)束會(huì)執(zhí)行doAfterAllAnalysed()方法
* @Author: jinhaoxun
* @Date: 2020/1/14 15:51
* @Version: 1.0.0
*/
public class ModelExcelListener<E> extends AnalysisEventListener<E> {
private List<E> dataList = new ArrayList<E>();
@Override
public void invoke(E object, AnalysisContext context) {
dataList.add(object);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
public List<E> getDataList() {
return dataList;
}
public void setDataList(List<E> dataList) {
this.dataList = dataList;
}
}
第二種，創(chuàng)建StringList解析監(jiān)聽(tīng)器類(lèi)坷随，StringExcelListener
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
* @Description: StringList 解析監(jiān)聽(tīng)器
* @Author: jinhaoxun
* @Date: 2020/1/15 11:31
* @Version: 1.0.0
*/
public class StringExcelListener extends AnalysisEventListener {
/**
* 自定義用于暫時(shí)存儲(chǔ)data
* 可以通過(guò)實(shí)例獲取該值
*/
private List<List<String>> datas = new ArrayList<List<String>>();
/**
* 每解析一行都會(huì)回調(diào)invoke()方法
*
* @param object
* @param context
*/
@Override
public void invoke(Object object, AnalysisContext context) {
List<String> stringList= (List<String>) object;
//數(shù)據(jù)存儲(chǔ)到list房铭，供批量處理驻龟，或后續(xù)自己業(yè)務(wù)邏輯處理。
datas.add(stringList);
//根據(jù)自己業(yè)務(wù)做處理
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//解析結(jié)束銷(xiāo)毀不用的資源
//注意不要調(diào)用datas.clear(),否則getDatas為null
}
public List<List<String>> getDatas() {
return datas;
}
public void setDatas(List<List<String>> datas) {
this.datas = datas;
}
}
第三步缸匪，分別創(chuàng)建三個(gè)模型類(lèi)翁狐，ExcelModel，ExcelModel1凌蔬，ExcelModel2
ExcelModel
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
/**
* @Description: 生成excel文件數(shù)據(jù)字段模板
* @Author: jinhaoxun
* @Date: 2020/1/14 15:45
* @Version: 1.0.0
*/
@Data
public class ExcelModel extends BaseRowModel {
public ExcelModel(){
}
public ExcelModel(String dateJuly, String onDuty, String offDuty, String overtime, String last){
this.dateJuly = dateJuly;
this.onDuty = onDuty;
this.offDuty = offDuty;
this.overtime = overtime;
this.last = last;
}
@ExcelProperty(value = "日期", index = 0)
private String dateJuly;
@ExcelProperty(value = "上班時(shí)間", index = 1)
private String onDuty;
@ExcelProperty(value = "下班時(shí)間", index = 2)
private String offDuty;
@ExcelProperty(value = "加班時(shí)長(zhǎng)", index = 3)
private String overtime;
@ExcelProperty(value = "備注", index = 4)
private String last;
}
ExcelModel1
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
/**
* @Description: 生成excel文件數(shù)據(jù)字段模板
* @Author: jinhaoxun
* @Date: 2020/1/14 15:45
* @Version: 1.0.0
*/
@Data
public class ExcelModel1 extends BaseRowModel {
public ExcelModel1(){
}
public ExcelModel1(String dateJuly, String onDuty, String offDuty, String overtime, String last){
this.dateJuly = dateJuly;
this.onDuty = onDuty;
this.offDuty = offDuty;
this.overtime = overtime;
this.last = last;
}
@ExcelProperty(value = "日期", index = 0)
private String dateJuly;
@ExcelProperty(value = "上班時(shí)間", index = 1)
private String onDuty;
@ExcelProperty(value = "下班時(shí)間", index = 2)
private String offDuty;
@ExcelProperty(value = "加班時(shí)長(zhǎng)", index = 3)
private String overtime;
@ExcelProperty(value = "備注", index = 4)
private String last;
}
ExcelModel2
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
/**
* @Description: 生成excel文件數(shù)據(jù)字段模板
* @Author: jinhaoxun
* @Date: 2020/1/14 15:45
* @Version: 1.0.0
*/
@Data
public class ExcelModel2 extends BaseRowModel {
public ExcelModel2(){
}
public ExcelModel2(String dateJuly, String onDuty, String offDuty, String overtime, String last){
this.dateJuly = dateJuly;
this.onDuty = onDuty;
this.offDuty = offDuty;
this.overtime = overtime;
this.last = last;
}
@ExcelProperty(value = "日期", index = 0)
private String dateJuly;
@ExcelProperty(value = "上班時(shí)間", index = 1)
private String onDuty;
@ExcelProperty(value = "下班時(shí)間", index = 2)
private String offDuty;
@ExcelProperty(value = "加班時(shí)長(zhǎng)", index = 3)
private String overtime;
@ExcelProperty(value = "備注", index = 4)
private String last;
}
第四步露懒，創(chuàng)建工具類(lèi)，DataConvertUtil砂心，ExcelUtil懈词，ExcelConvertCsvUtil
DataConvertUtil
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
/**
* @Description:
* @Author: jinhaoxun
* @Date: 2020/4/14 下午5:26
* @Version: 1.0.0
*/
public class DataConvertUtil {
/**
* @Author: jinhaoxun
* @Description: 將inputStream轉(zhuǎn)byte[]
* @param inputStream 輸入流
* @Date: 2020/1/16 21:43
* @Return: byte[]
* @Throws: Exception
*/
public static byte[] inputStreamTobyte2(InputStream inputStream) throws IOException {
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
byte[] buff = new byte[100];
int rc = 0;
while ((rc = inputStream.read(buff, 0, 100)) > 0) {
byteArrayOutputStream.write(buff, 0, rc);
}
return byteArrayOutputStream.toByteArray();
}
/**
* @Author: jinhaoxun
* @Description: 將byte[]轉(zhuǎn)inputStream
* @param bytes byte數(shù)組
* @Date: 2020/1/16 21:43
* @Return: InputStream
* @Throws: Exception
*/
public static InputStream byte2ToInputStream(byte[] bytes) {
return new ByteArrayInputStream(bytes);
}
}
ExcelUtil
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.jinhaoxun.easyexcel.listener.ModelExcelListener;
import com.jinhaoxun.easyexcel.listener.StringExcelListener;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* @Description: excel導(dǎo)入導(dǎo)出工具類(lèi)
* 1.支持按行導(dǎo)入字符串方式
* 2.支持導(dǎo)入實(shí)體類(lèi)映射
* 3.支持按行導(dǎo)出字符串方式
* 4.支持導(dǎo)出實(shí)體類(lèi)映射
* @Author: jinhaoxun
* @Date: 2020/1/15 11:20
* @Version: 1.0.0
*/
public class ExcelUtil {
/**
* @Author: jinhaoxun
* @Description: 使用 StringList 來(lái)讀取Excel
* @param inputStream Excel的輸入流
* @param excelTypeEnum Excel的格式(XLS或XLSX)
* @Date: 2020/1/16 21:40
* @Return: java.util.List<java.util.List<java.lang.String>>
* @Throws: Exception
*/
public static List<List<String>> readExcel(InputStream inputStream, ExcelTypeEnum excelTypeEnum) throws Exception{
StringExcelListener listener = new StringExcelListener();
ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
excelReader.read();
return listener.getDatas();
}
/**
* @Author: jinhaoxun
* @Description: 使用模型來(lái)讀取Excel
* @param inputStream Excel的輸入流
* @param clazz 模型的類(lèi)
* @param excelTypeEnum Excel的格式(XLS或XLSX)
* @Date: 2020/1/16 21:41
* @Return: java.util.List<E>
* @Throws: Exception
*/
public static <E> List<E> readExcel(InputStream inputStream, Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum) throws Exception {
// 解析每行結(jié)果在listener中處理
ModelExcelListener<E> listener = new ModelExcelListener<E>();
ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
//默認(rèn)只有一列表頭
excelReader.read(new Sheet(1, 1, clazz));
return listener.getDataList();
}
/**
* @Author: jinhaoxun
* @Description: 使用StringList來(lái)寫(xiě)入Excel，單sheet辩诞，單table
* @param outputStream Excel的輸出流
* @param data 要寫(xiě)入的以StringList為單位的數(shù)據(jù)
* @param table 配置Excel的表的屬性
* @param excelTypeEnum Excel的格式(XLS或XLSX)
* @Date: 2020/1/16 21:42
* @Return: void
* @Throws: Exception
*/
public static void writeExcel(OutputStream outputStream, List<List<String>> data, Table table, ExcelTypeEnum excelTypeEnum) throws Exception {
/**
* @Author: jinhaoxun
* @Description:
* @param outputStream
* @param data
* @param table
* @param excelTypeEnum
* @Date: 2020/1/16 21:42
* @Return: void
* @Throws:
*/
//這里指定不需要表頭坎弯，因?yàn)镾tring通常表頭已被包含在data里
ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,false);
//寫(xiě)第一個(gè)sheet, sheet1 數(shù)據(jù)全是List<String> 無(wú)模型映射關(guān)系,無(wú)表頭
Sheet sheet1 = new Sheet(0, 0);
writer.write0(data, sheet1,table);
writer.finish();
}
/**
* @Author: jinhaoxun
* @Description: 使用StringList來(lái)寫(xiě)入Excel，單sheet译暂，單table（返回byte數(shù)組）
* @param outputStream Excel的輸出流
* @param data 要寫(xiě)入的以StringList為單位的數(shù)據(jù)
* @param table 配置Excel的表的屬性
* @param excelTypeEnum Excel的格式(XLS或XLSX)
* @Date: 2020/1/16 21:43
* @Return: byte[]
* @Throws: Exception
*/
public static byte[] writeExcel(ByteArrayOutputStream outputStream, List<List<String>> data, Table table, ExcelTypeEnum excelTypeEnum) throws Exception {
//這里指定不需要表頭抠忘，因?yàn)镾tring通常表頭已被包含在data里
ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,false);
//寫(xiě)第一個(gè)sheet, sheet1 數(shù)據(jù)全是List<String> 無(wú)模型映射關(guān)系,無(wú)表頭
Sheet sheet1 = new Sheet(0, 0);
writer.write0(data, sheet1,table);
writer.finish();
return outputStream.toByteArray();
}
/**
* @Author: jinhaoxun
* @Description: 使用模型來(lái)寫(xiě)入Excel，單sheet外永，單table
* @param outputStream Excel的輸出流
* @param data 要寫(xiě)入的以 模型 為單位的數(shù)據(jù)
* @param clazz 模型的類(lèi)
* @param excelTypeEnum Excel的格式(XLS或XLSX)
* @Date: 2020/1/16 21:43
* @Return: byte[]
* @Throws: Exception
*/
public static void writeExcel(OutputStream outputStream, List<? extends BaseRowModel> data,
Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum) throws Exception {
//這里指定需要表頭崎脉，因?yàn)閙odel通常包含表頭信息
ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,true);
//寫(xiě)第一個(gè)sheet, sheet1 數(shù)據(jù)全是List<String> 無(wú)模型映射關(guān)系
Sheet sheet1 = new Sheet(1, 0, clazz);
writer.write(data, sheet1);
writer.finish();
}
/**
* @Author: jinhaoxun
* @Description: 使用模型來(lái)寫(xiě)入Excel，單sheet伯顶，單table（返回字節(jié)數(shù)組）
* @param outputStream Excel的輸出流
* @param data 要寫(xiě)入的以 模型 為單位的數(shù)據(jù)
* @param clazz 模型的類(lèi)
* @param excelTypeEnum Excel的格式(XLS或XLSX)
* @Date: 2020/1/16 21:43
* @Return: byte[]
* @Throws: Exception
*/
public static byte[] writeExcel(ByteArrayOutputStream outputStream, List<? extends BaseRowModel> data,
Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum) throws Exception {
//這里指定需要表頭囚灼，因?yàn)閙odel通常包含表頭信息
ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,true);
//寫(xiě)第一個(gè)sheet, sheet1 數(shù)據(jù)全是List<String> 無(wú)模型映射關(guān)系
Sheet sheet1 = new Sheet(1, 0, clazz);
writer.write(data, sheet1);
writer.finish();
return outputStream.toByteArray();
}
/**
* @Author: jinhaoxun
* @Description: 使用模型來(lái)寫(xiě)入Excel，多sheet祭衩，單table （返回字節(jié)數(shù)組）
* @param outputStream Excel的輸出流
* @param sheetName sheet名集合
* @param datas 要寫(xiě)入的以 模型 為單位的數(shù)據(jù)
* @param clazzs 模型的類(lèi)
* @param excelTypeEnum Excel的格式(XLS或XLSX)
* @Date: 2020/1/16 21:43
* @Return: byte[]
* @Throws: Exception
*/
public static byte[] writeExcel(ByteArrayOutputStream outputStream,List<String> sheetName,List<List<? extends BaseRowModel>> datas,
List<Class<? extends BaseRowModel>> clazzs, ExcelTypeEnum excelTypeEnum) throws Exception {
//這里指定需要表頭啦撮，因?yàn)閙odel通常包含表頭信息
ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,true);
if (sheetName.size()!=datas.size()||datas.size()!=clazzs.size()){
throw new ArrayIndexOutOfBoundsException();
}
int i = 0;
//寫(xiě)第一個(gè)sheet, sheet1 數(shù)據(jù)全是List<String> 無(wú)模型映射關(guān)系
for (String name:sheetName){
Sheet sheet1 = new Sheet(1, 0, clazzs.get(i));
sheet1.setSheetName(name);
writer.write(datas.get(i), sheet1);
}
writer.finish();
return outputStream.toByteArray();
}
/**
* @Author: jinhaoxun
* @Description: 使用模型來(lái)寫(xiě)入Excel，多sheet汪厨，多table
* @param outputStream Excel的輸出流
* @param sheetAndTable sheet和table名，格式：<sheet名愉择，<table名集合>>
* @param data <sheet名劫乱，<table名，table數(shù)據(jù)集>>
* @param clazz <sheet名锥涕，<table名衷戈，table數(shù)據(jù)集實(shí)體class類(lèi)型>>
* @param excelTypeEnum Excel的格式(XLS或XLSX)
* @Date: 2020/1/16 21:43
* @Return: byte[]
* @Throws: Exception
*/
public static byte[] writeExcel(ByteArrayOutputStream outputStream,Map<String,List<String>> sheetAndTable,
Map<String,Map<String,List<? extends BaseRowModel>>> data,Map<String,Map<String,Class<? extends BaseRowModel>>> clazz,
ExcelTypeEnum excelTypeEnum) throws Exception {
//這里指定需要表頭，因?yàn)閙odel通常包含表頭信息
ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,true);
Iterator<Map.Entry<String, List<String>>> iterator = sheetAndTable.entrySet().iterator();
int sheetNo = 1;
//遍歷sheet
while (iterator.hasNext()){
Map.Entry<String, List<String>> next = iterator.next();
//當(dāng)前sheet名
String sheetName = next.getKey();
//當(dāng)前sheet對(duì)應(yīng)的table的實(shí)體類(lèi)class對(duì)象集合
Map<String, Class<? extends BaseRowModel>> tableClasses = clazz.get(sheetName);
//當(dāng)前sheet對(duì)應(yīng)的table的數(shù)據(jù)集合
Map<String, List<? extends BaseRowModel>> dataListMaps = data.get(sheetName);
Sheet sheet = new Sheet(sheetNo, 0);
sheet.setSheetName(sheetName);
int tableNo = 1;
Iterator<Map.Entry<String, Class<? extends BaseRowModel>>> iterator1 = tableClasses.entrySet().iterator();
//遍歷table
while (iterator1.hasNext()){
Map.Entry<String, Class<? extends BaseRowModel>> next1 = iterator1.next();
//當(dāng)前table名
String tableName = next1.getKey();
//當(dāng)前table對(duì)應(yīng)的class
Class<? extends BaseRowModel> tableClass = next1.getValue();
//當(dāng)前table對(duì)應(yīng)的數(shù)據(jù)集
List<? extends BaseRowModel> tableData = dataListMaps.get(tableName);
Table table = new Table(tableNo);
table.setClazz(tableClass);
writer.write(tableData, sheet, table);
tableNo++;
}
sheetNo++;
}
writer.finish();
return outputStream.toByteArray();
}
}
ExcelConvertCsvUtil
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @Description: excel文件轉(zhuǎn)成csv格式
* @Author: jinhaoxun
* @Date: 2020/4/14 上午10:07
* @Version: 1.0.0
*/
public class ExcelConvertCsvUtil {
/**
* @Author: jinhaoxun
* @Description: 將excel字節(jié)碼數(shù)組轉(zhuǎn)成csv字節(jié)碼數(shù)組
* @param bytes excel字節(jié)碼數(shù)組
* @Date: 2020/1/16 21:43
* @Return: byte[]
* @Throws: Exception
*/
public static byte[] convertExcelToCsv(byte[] bytes) throws Exception {
InputStream inputStream = new ByteArrayInputStream(bytes);
Workbook wb = WorkbookFactory.create(inputStream);
String buffer = "";
Sheet sheet = null;
Row row = null;
List<Map<String,String>> list = null;
String cellData = null;
if(wb != null){
//用來(lái)存放表中數(shù)據(jù)
list = new ArrayList<Map<String,String>>();
//獲取第一個(gè)sheet
sheet = wb.getSheetAt(0);
//獲取最大行數(shù)
int rownum = sheet.getPhysicalNumberOfRows();
//獲取第一行
row = sheet.getRow(0);
//獲取最大列數(shù)
int colnum = row.getPhysicalNumberOfCells();
for (int i = 0; i<rownum; i++) {
row = sheet.getRow(i);
for (int j = 0; j < colnum; j++) {
cellData = (String) getCellFormatValue(row.getCell(j));
buffer +=cellData;
}
buffer = buffer.substring(0, buffer.lastIndexOf(",")).toString();
buffer += "\n";
}
return buffer.getBytes();
}
return null;
}
/**
* @Author: jinhaoxun
* @Description: 讀取excel
* @param filePath 本地文件路徑
* @Date: 2020/1/16 21:43
* @Return: Workbook
* @Throws: Exception
*/
public static Workbook readExcel(String filePath){
Workbook wb = null;
if(filePath==null){
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if(".xls".equals(extString)){
return wb = new HSSFWorkbook(is);
}else if(".xlsx".equals(extString)){
return wb = new XSSFWorkbook(is);
}else{
return wb = null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
/**
* @Author: jinhaoxun
* @Description:
* @param cell
* @Date: 2020/1/16 21:43
* @Return: Workbook
* @Throws: Exception
*/
public static Object getCellFormatValue(Cell cell){
Object cellValue = null;
if(cell!=null){
//判斷cell類(lèi)型
switch(cell.getCellType()){
case NUMERIC:{
cellValue = String.valueOf(cell.getNumericCellValue()).replaceAll("\n", " ") + ",";
break;
}
case FORMULA:{
//判斷cell是否為日期格式
if(DateUtil.isCellDateFormatted(cell)){
//轉(zhuǎn)換為日期格式Y(jié)YYY-mm-dd
cellValue = String.valueOf(cell.getDateCellValue()).replaceAll("\n", " ") + ",";;
}else{
//數(shù)字
cellValue = String.valueOf(cell.getNumericCellValue()).replaceAll("\n", " ") + ",";;
}
break;
}
case STRING:{
cellValue = cell.getRichStringCellValue().getString().replaceAll("\n", " ") + ",";;
break;
}
default:
cellValue = "";
}
}else{
cellValue = "";
}
return cellValue;
}
}
第五步层坠，編寫(xiě)單元測(cè)試類(lèi)殖妇，EasyexcelApplicationTests，并進(jìn)行測(cè)試破花，使用方法基本都有注釋
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.jinhaoxun.easyexcel.entity.ExcelModel;
import com.jinhaoxun.easyexcel.entity.ExcelModel1;
import com.jinhaoxun.easyexcel.entity.ExcelModel2;
import com.jinhaoxun.easyexcel.util.DataConvertUtil;
import com.jinhaoxun.easyexcel.util.ExcelConvertCsvUtil;
import com.jinhaoxun.easyexcel.util.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Slf4j
// 獲取啟動(dòng)類(lèi)谦趣，加載配置疲吸，確定裝載 Spring 程序的裝載方法，它回去尋找 主配置啟動(dòng)類(lèi)（被 @SpringBootApplication 注解的）
@SpringBootTest
class EasyexcelApplicationTests {
@Test
void readExcelTest() throws Exception {
//讀取excel
File file = new File("E:\\2.xlsx");
InputStream inputStream = new FileInputStream(file);
//導(dǎo)入excle
List<ExcelModel> datas = ExcelUtil.readExcel(inputStream, ExcelModel.class, ExcelTypeEnum.XLSX);
log.info(datas.toString());
}
@Test
void writeExcelTest() throws Exception {
//單sheet,單table導(dǎo)出測(cè)試
List<ExcelModel> excelModelList = new ArrayList<ExcelModel>();
for (int i = 0;i<5;i++){
ExcelModel excelModel = new ExcelModel("日期"+i, "上班時(shí)間" + i,
"下班時(shí)間" + i, "加班時(shí)長(zhǎng)" + i, "備注" + i);
excelModelList.add(excelModel);
}
File file1 = new File("E:\\2.xlsx");
ByteArrayOutputStream outputStream1 = new ByteArrayOutputStream();
byte[] bytes = ExcelUtil.writeExcel(outputStream1, excelModelList, ExcelModel.class, ExcelTypeEnum.XLSX);
FileOutputStream outputStream = new FileOutputStream(file1);
outputStream.write(bytes);
}
@Test
void writeExcelTest1() throws Exception {
//多sheet前鹅，多table導(dǎo)出測(cè)試摘悴，數(shù)據(jù)集制作
List<ExcelModel> excelModelList = new ArrayList<ExcelModel>();
List<ExcelModel1> excelModel1List = new ArrayList<ExcelModel1>();
List<ExcelModel2> excelModel2List = new ArrayList<ExcelModel2>();
for (int i = 0;i<5;i++){
ExcelModel excelModel = new ExcelModel("日期"+i, "上班時(shí)間" + i,
"下班時(shí)間" + i, "加班時(shí)長(zhǎng)" + i, "備注" + i);
ExcelModel1 excelModel1 = new ExcelModel1("日期"+i, "上班時(shí)間" + i,
"下班時(shí)間" + i, "加班時(shí)長(zhǎng)" + i, "備注" + i);
ExcelModel2 excelModel2 = new ExcelModel2("日期"+i, "上班時(shí)間" + i,
"下班時(shí)間" + i, "加班時(shí)長(zhǎng)" + i, "備注" + i);
excelModelList.add(excelModel);
excelModel1List.add(excelModel1);
excelModel2List.add(excelModel2);
}
Map<String,List<String>> sheetAndTable = new HashMap<String, List<String>>();
//構(gòu)造第一個(gè)sheet，此sheet內(nèi)有兩個(gè)table
List<String> sheet1tableNames = new ArrayList();
sheet1tableNames.add("表一");
sheet1tableNames.add("表二");
//構(gòu)造第二個(gè)sheet舰绘，此sheet內(nèi)有一個(gè)table
List<String> Sheet2tableNames = new ArrayList<String>();
Sheet2tableNames.add("表三");
sheetAndTable.put("sheet1",sheet1tableNames);
sheetAndTable.put("sheet2",Sheet2tableNames);
Map<String,Map<String,Class<? extends BaseRowModel>>> clazz = new HashMap<String, Map<String, Class<? extends BaseRowModel>>>();
//第一個(gè)sheet
Map<String,Class<? extends BaseRowModel>> tables = new HashMap<String, Class<? extends BaseRowModel>>();
tables.put("表一",ExcelModel.class);
tables.put("表二",ExcelModel1.class);
clazz.put("sheet1",tables);
Map<String,Class<? extends BaseRowModel>> tables1 = new HashMap<String, Class<? extends BaseRowModel>>();
tables1.put("表三",ExcelModel2.class);
clazz.put("sheet2",tables1);
Map<String,Map<String,List<? extends BaseRowModel>>> data= new HashMap<String, Map<String, List<? extends BaseRowModel>>>();
//第一個(gè)sheet
Map<String,List<? extends BaseRowModel>> map1 = new HashMap<String, List<? extends BaseRowModel>>();
map1.put("表一",excelModelList);
map1.put("表二",excelModel1List);
data.put("sheet1",map1);
Map<String,List<? extends BaseRowModel>> map2 = new HashMap<String, List<? extends BaseRowModel>>();
map2.put("表三",excelModel2List);
data.put("sheet2",map2);
File file1 = new File("E:\\3.xlsx");
ByteArrayOutputStream outputStream1 = new ByteArrayOutputStream();
byte[] bytes = ExcelUtil.writeExcel(outputStream1,sheetAndTable,data,clazz,ExcelTypeEnum.XLSX);
FileOutputStream outputStream = new FileOutputStream(file1);
outputStream.write(bytes);
}
@Test
void convertExcelToCsvTest() throws Exception {
//讀取excel
File file = new File("/Users/ao/Desktop/activitycode_20200414102350.xlsx");
InputStream inputStream = new FileInputStream(file);
byte[] bytes = DataConvertUtil.inputStreamTobyte2(inputStream);
ExcelConvertCsvUtil.convertExcelToCsv(bytes);
}
@BeforeEach
void testBefore(){
log.info("測(cè)試開(kāi)始!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
}
@AfterEach
void testAfter(){
log.info("測(cè)試結(jié)束!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
}
}
注：此工程包含多個(gè)module蹂喻，本文所用代碼均在easyexcel-demo模塊下
