FrameworkPoiexcel，csv文件導(dǎo)入導(dǎo)出解決方案
poi 框架 framework-poi
> 簡單、好用且輕量級的海量 excel，csv 文件導(dǎo)入導(dǎo)出解決方案。
1.解決火狐瀏覽器，中文名亂碼問題 2.解決數(shù)據(jù)量過大 OOM 問題 3.導(dǎo)出字段可以自定義格式 4.支持多sheet導(dǎo)出 5.支持excel單個sheet最大行，自動生成新sheet繼續(xù)導(dǎo)出
> 注：excel 的合并功能及復(fù)雜功能，使用代碼實現(xiàn)比較復(fù)雜，框架只提供單行的導(dǎo)入導(dǎo)出。
如何使用？
引入 Maven 依賴或下載 jar 包
<dependency> <groupId>com.github.fartherp</groupId> <artifactId>framework-poi</artifactId> <version>3.0.5</version> </dependency>
CSV 常用例子：
CSVRead.read(CSVReadTest.class.getResourceAsStream("/a.csv"), new CSVReadDeal<CsvReadDto>() {
// 單條數(shù)據(jù)處理（每一行對應(yīng)一個javabean）
public CsvReadDto dealBean(String[] arr) {
CsvReadDto dto = new CsvReadDto();
dto.setId(Long.valueOf(arr[0]));
dto.setName(arr[1]);
dto.setAge(Integer.valueOf(arr[2]));
return dto;
}
// 批量數(shù)據(jù)處理（可以批量入庫）
public void dealBatchBean(List<CsvReadDto> list) {
Assert.assertEquals("name1";, list.get(0).getName());
Assert.assertEquals("name2", list.get(1).getName());
Assert.assertEquals("name3", list.get(2).getName());
}
// 批量加載多少數(shù)據(jù)，統(tǒng)一處理（默認1000）
public int getBatchCount() {
return super.getBatchCount();
}
// 從第幾行開始加載（默認跳過第一行）
public int skipLine() {
return super.skipLine();
}
});
1. CSV 文件導(dǎo)出：
String filename = "TEST"; String[] title = SheetsTitlesEnum.USER_LOGIN_LOG.getTitle(); List<String[]> bodyList = new ArrayList<>(); CsvUtil.writeCsvFile(filename, title, bodyList);
2.瀏覽器下載 CSV 文件：
HttpServletResponse response = null; HttpServletRequest request = null; String filename = "TEST"; String[] title = SheetsTitlesEnum.USER_LOGIN_LOG.getTitle(); List<String[]> bodyList = new ArrayList<>(); CsvUtil.writeCsvFile(response, request, filename, title, bodyList);
Excel 常用例子：
1. Excel 文件導(dǎo)入：
ExcelRead.read(ExcelReadTest.class.getResourceAsStream("/a.xls"), new ExcelReadDeal<ExcelReadDto>() {
// 單條數(shù)據(jù)處理（每一行對應(yīng)一個javabean）
public ExcelReadDto dealBean(Row row) {
ExcelReadDto dto = new ExcelReadDto();
dto.setId(new BigDecimal(row.getCell(0).toString()).longValue());
dto.setName(row.getCell(1).toString());
dto.setAge(Integer.valueOf(row.getCell(2).toString()));
return dto;
}
// 批量數(shù)據(jù)處理（可以批量入庫）
public void dealBatchBean(List<ExcelReadDto> list) {
Assert.assertEquals("name1", list.get(0).getName());
Assert.assertEquals("name2", list.get(1).getName());
Assert.assertEquals("name3", list.get(2).getName());
}
// 批量加載多少數(shù)據(jù)，統(tǒng)一處理（默認1000）
public int getBatchCount() {
return super.getBatchCount();
}
// 從第幾行開始加載（默認跳過第一行）
public int skipLine() {
return super.skipLine();
}
});
2.Excel 文件導(dǎo)出：
private WriteDeal<ExcelDto> get() {
return obj -> {
String[] result = new String[6];
result[0] = obj.getTime();
result[1] = obj.getName();
result[2] = obj.getClient();
result[3] = obj.getVersion();
result[4] = obj.getIp();
result[5] = obj.getStatus() + "";
return result;
};
}
String[] title = new String [6];
title[0] = "登錄時間";
title[1] = "用戶名";
title[2] = "訪問端";
title[3] = "版本系統(tǒng)";
title[4] = "登錄IP";
title[5] = "狀態(tài)";
String fileName = "D:\\style1.xls";
FileExcelWrite.build(fileName)
.deal(title, get(), ExcelWriteStyleTest.getList())
.deal(title, get(), ExcelWriteStyleTest.getList1())
.write();
3. Excel 文件導(dǎo)出（風(fēng)格，可以自定義風(fēng)格）：
String fileName = "D:\\styleInputStream.xls";
FileExcelWrite.build(this.getClass().getResourceAsStream("/c.xls"), fileName)
.deal(new WriteDeal<ExcelDto>() {
public String[] dealBean(ExcelDto obj) {
String[] result = new String[3];
result[0] = obj.getId() + "";
result[1] = obj.getName();
result[2] = obj.getAge() + "";
return result;
}
public int skipLine() {
return 4;
}
public Map<String, Object> additional() {
Map<String, Object> map = new HashMap<>();
map.put("quoteCurrency", "ETH");
map.put("symbol", "USDT_ETH");
map.put("startTime", "2019-01-09 00:00:00");
map.put("endTime", "2019-01-09 12:00:00");
return map;
}
}, getList())
.write();
4.多個 sheet 導(dǎo)出：
String[] title = new String [6];
title[0] = "登錄時間";
title[1] = "用戶名";
title[2] = "訪問端";
title[3] = "版本系統(tǒng)";
title[4] = "登錄IP";
title[5] = "狀態(tài)";
String fileName = "D:\\style1.xls";
String[] title1 = new String [6];
title1[0] = "id";
title1[1] = "type";
title1[2] = "desc";
FileExcelWrite.build(fileName)
.deal(title, obj -> {
String[] result = new String[6];
result[0] = obj.getTime();
result[1] = obj.getName();
result[2] = obj.getClient();
result[3] = obj.getVersion();
result[4] = obj.getIp();
result[5] = obj.getStatus() + "";
return result;
}, ExcelDataList.getList1())
.deal(title1, obj -> {
String[] result = new String[3];
result[0] = obj.getId() + "";
result[1] = obj.getType();
result[2] = obj.getDesc();
return result;
}, ExcelDataList.getTenList1())
.write();
5.多個 sheet 導(dǎo)出（自定義sheet名稱）：
String[] title = new String [6];
title[0] = "登錄時間";
title[1] = "用戶名";
title[2] = "訪問端";
title[3] = "版本系統(tǒng)";
title[4] = "登錄IP";
title[5] = "狀態(tài)";
String fileName = "D:\\style1.xls";
String[] title1 = new String [6];
title1[0] = "id";
title1[1] = "type";
title1[2] = "desc";
FileExcelWrite.build(fileName)
.deal(title, new WriteDeal<ExcelDto>() {
@Override
public String[] dealBean(ExcelDto obj) {
String[] result = new String[6];
result[0] = obj.getTime();
result[1] = obj.getName();
result[2] = obj.getClient();
result[3] = obj.getVersion();
result[4] = obj.getIp();
result[5] = obj.getStatus() + "";
return result;
}
@Override
public String name() {
return "ExcelDto";
}
}, ExcelDataList.getList1())
.deal(title1, new WriteDeal<ExcelDto1>() {
@Override
public String[] dealBean(ExcelDto1 obj) {
String[] result = new String[3];
result[0] = obj.getId() + "";
result[1] = obj.getType();
result[2] = obj.getDesc();
return result;
}
@Override
public String name() {
return "test";
}
}, ExcelDataList.getTenList1())
.write();
6.瀏覽器下載 Excel 文件：
String[] title = new String [6]; title[0] = "登錄時間"; title[1] = "用戶名"; title[2] = "訪問端"; title[3] = "版本系統(tǒng)"; title[4] = "登錄IP"; title[5] = "狀態(tài)"; String fileName = "D:\\style1.xls"; HttpServletResponseExcelWrite.build(fileName, request, response) .deal(title, get(), ExcelWriteStyleTest.getList()) .deal(title, get(), ExcelWriteStyleTest.getList1()) .write();
評論
圖片
表情