一、概述
在日常的开发过程种，excel报表是常见的一个需求。
execel的操作，可以分为两种，一种是读，一种是写，其他的项目辅助性的操作都是具有共性的，我们就可以将读和写抽象出来，针对简单的Excel导入导出操作，做一个标准的实现。但是在实际的需求中，列的合并，行的合并也是常有的需求，我们可以针对这些特定的需求，快速实现复杂Execel的开发。
二、设计
针对上述的需求，我们采用设计模式中的模板方法模式进行实现，下面我们来看看模板方法的优点和使用场景。
模板方法的优点是：
1.封装不变部分，扩展可变部分
2.提取公共部分代码，便于维护
3.行为由父类控制，子类实现
使用场景：
1.多个子类有公用的方法，并且逻辑相同
2.重要，复杂的算法，可以把核心算法设计为模板方法，相关细节由子类实现
3.重构时把相同的代码提取到父类中，通过勾子函数（单独的方法）约束行为
三、模板类的实现
@Data
public abstract class AbstractExcelUtil<T>{
/**
* 读取数据
* @param workbook 工作薄
* @param columns 数组列 对应 列下标
* @return map
*/
protected abstract List<Map<String,Object>> readData(Workbook workbook, String[] columns);
/**
* 通过url导入数据
* @param networkPath
* @param columns
* @return
*/
public List<Map<String,Object>> importRemote(String networkPath,String[] columns) {
return readData(getImportWorkbook(networkPath, PoiPool.NOT_NATIVE_FILE, getFileSuffix(networkPath)), columns);
}
/**
* 通过本地文件路径导入数据
* @param path
* @param columns
* @return
*/
public List<Map<String,Object>> importNative(String path,String[] columns){
return readData(getImportWorkbook(path,PoiPool.IS_NATIVE_FILE,getFileSuffix(path)),columns);
}
/**
* 读取数据
* @param workbook 工作薄
* @param columns 数组列 对应 列下标
* @param clazz 类名
* @return T 列表
*/
protected abstract List<T> readData(Workbook workbook, String[] columns,Class<T> clazz);
/**
* 获取远程导入数据
* @param networkPath 远程文件地址
* @param columns 需要导出的列名
* @param clazz 类名
* @return T 列表
*/
public List<T> importRemote(String networkPath,String[] columns,Class<T> clazz) {
// return getResultList(importRemote(networkPath,columns),clazz);
return readData(getImportWorkbook(networkPath, PoiPool.NOT_NATIVE_FILE, getFileSuffix(networkPath)),columns,clazz);
}
/**
* 获取本地导入数据
* @param path 本地文件路径
* @param columns 需要导出的列名
* @param clazz 对象名
* @return T 列表
*/
public List<T> importNative(String path,String[] columns,Class<T> clazz){
// return getResultList(importNative(path,columns),clazz);
return readData(getImportWorkbook(path, PoiPool.IS_NATIVE_FILE, getFileSuffix(path)),columns,clazz);
}
//================================================ export ===============================================
/**
* 导出写入数据（一个sheet）
* @param sheet sheet
* @param style 列样式
* @param datas 数据列表
* @param columns 需要导出的列
*/
protected abstract void writeData(Sheet sheet, CellStyle style, List<T> datas, String[] columns);
/**
* 自定义样式导出
* @param workbook 工作薄
* @param datas 数据列表
* @param titles 表头
* @param widths 宽度列表
*/
protected abstract void writeCustomizeData(Workbook workbook,List<T> datas,String[] titles,Integer[] widths);
//============================= export single sheet option =========================================
public byte[] exportBytes(String sheetName, List<T> datas, CellStyle style,int sheetPageSize, String[] titles, String[] columns, Integer[] widths){
Workbook workbook = getWorkbook(datas, sheetName, style, sheetPageSize , titles, columns, widths);
try(ByteArrayOutputStream outputStream = new ByteArrayOutputStream()){
workbook.write(outputStream);
return outputStream.toByteArray();
}catch (Exception e){
e.printStackTrace();
}finally {
close(workbook);
}
return null;
}
public String exportFilePath(String excelName, List<T> datas, String[] titles, String[] columns){
return exportFilePath(excelName,datas,excelName,titles,columns);
}
public String exportFilePath(String excelName, List<T> datas, String sheetName, CellStyle style, String[] titles, String[] columns, Integer[] widths){
return exportFilePath(excelName,datas,sheetName,style,PoiPool.DEFAULT_PAGE_SIZE,titles,columns,widths);
}
public String exportFilePath(String excelName, List<T> datas, String sheetName, CellStyle style,int sheetPageSize, String[] titles, String[] columns, Integer[] widths){
if(sheetPageSize > PoiPool.DEFAULT_PAGE_SIZE){
sheetPageSize = PoiPool.DEFAULT_PAGE_SIZE;
}
Workbook workbook = getWorkbook(datas, sheetName, style, sheetPageSize, titles, columns, widths);
return getExportPath(excelName,workbook);
}
private Workbook getWorkbook( List<T> datas, String sheetName,CellStyle style,int sheetPageSize, String[] titles, String[] columns, Integer[] widths){
Workbook workbook = createXssfWorkbook();
if(style == null){
style = getDefaultCellStyle(workbook);
}
int size = datas.size();
if(size <= sheetPageSize){
generateSheet(workbook,sheetName,datas,style,titles,columns,widths);
}else {
final CellStyle fStyle = style;
//计算总页码
int page = size / sheetPageSize;
page = size % sheetPageSize == 0 ? page : ++page;
//使用线程池处理
ThreadPoolExecutor batchExportJobPool = new ThreadPoolExecutor(
3,
10,
60L,
TimeUnit.SECONDS,
new LinkedBlockingQueue<Runnable>(2000),
new ThreadFactory() {
@Override
public Thread newThread(Runnable r) {
return new Thread(r, "excel batch export job-" + r.hashCode());
}
});
List<CompletableFuture<Void>> list = Lists.newArrayList();
List<T> ts;
AtomicInteger atomicInteger = new AtomicInteger();
try {
for(int i = 1; i <= page ; i++){
if(i == 1){
ts = datas.subList(0, sheetPageSize);
list.add(AsyncExport(workbook,sheetName,ts,fStyle,titles,columns,widths,batchExportJobPool));
}else if(i == page){
ts = datas.subList(sheetPageSize * ( i - 1 ), size);
list.add(AsyncExport(workbook,getCurrentSheetName(sheetName,atomicInteger),ts,fStyle,titles,columns,widths,batchExportJobPool));
}else {
ts = datas.subList(sheetPageSize * ( i - 1 ), sheetPageSize * i);
list.add(AsyncExport(workbook,getCurrentSheetName(sheetName,atomicInteger),ts,fStyle,titles,columns,widths,batchExportJobPool));
}
//当分页太大，需要等待，防止sheet名称 在多线程下出错
if(page > 3){
Thread.sleep(110);
}
}
} catch (Exception e) {
e.printStackTrace();
}
batchExportJobPool.shutdown();
getSyncResult(list);
}
return workbook;
}
private String getCurrentSheetName(String sheetName,AtomicInteger atomicInteger){
return sheetName.concat("-").concat(String.valueOf(atomicInteger.incrementAndGet()));
}
private CompletableFuture<Void> AsyncExport(Workbook workbook,String sheetName,List<T> datas,CellStyle style, String[] titles, String[] columns, Integer[] widths,ThreadPoolExecutor batchExportJobPool){
return CompletableFuture.runAsync(() -> {
generateSheet(workbook,sheetName,datas,style,titles,columns,widths);
}, batchExportJobPool);
}
private void getSyncResult(List<CompletableFuture<Void>> list){
try {
for (CompletableFuture result : list){
result.get(60,TimeUnit.SECONDS);
}
} catch (Exception e) {
e.printStackTrace();
}
}
private void generateSheet(Workbook wb, String sheetName, List<T> datas, CellStyle style,String[] titles, String[] columns, Integer[] widths){
Sheet sheet = createSheet(wb, sheetName);
if(widths != null && widths.length > 0){
setColumnWidth(sheet,widths);
}
createTitleHead(sheet,style,titles);
writeData(sheet,style,datas,columns);
}
//=================================================== export multi sheet option =====================================================================
public String exportCustomize(String excelName, List<T> datas, String[] titles, Integer[] widths){
Workbook workbook = createXssfWorkbook();
writeCustomizeData(workbook,datas,titles,widths);
return getExportPath(excelName,workbook);
}
}四、标准的Execel类的实现（子类使用模板类的相关方法）
public class StandardExcelUtil<T> extends AbstractExcelUtil<T> {
@Override
protected List<Map<String,Object>> readData(Workbook workbook, String[] columns) {
try {
Sheet sheetAt = workbook.getSheetAt(0);
checkTable(sheetAt,columns);
int columnsSize = columns.length;
List<Map<String,Object>> result = Lists.newArrayList();
Map<String,Object> rowMap;
int countRow = sheetAt.getPhysicalNumberOfRows();
Row row;
for(int i = 1; i < countRow; i++){
row = sheetAt.getRow(i);
rowMap = new HashMap<>();
for(int j = 0; j < columnsSize; j++){
rowMap.put(columns[j], getCellValue(row.getCell(j)));
}
result.add(rowMap);
}
return result;
} finally {
close(workbook);
}
}
@Override
protected List<T> readData(Workbook workbook, String[] columns, Class<T> clazz) {
try {
Sheet sheetAt = workbook.getSheetAt(0);
checkTable(sheetAt,columns);
int columnsSize = columns.length;
List<T> result = Lists.newArrayList();
T t;
Map<String,Object> rowMap;
int countRow = sheetAt.getPhysicalNumberOfRows();
Row row;
for(int i = 1; i < countRow; i++){
row = sheetAt.getRow(i);
rowMap = new HashMap<>();
for(int j = 0; j < columnsSize; j++){
rowMap.put(columns[j], getCellValue(row.getCell(j)));
}
result.add(BeanUtil.fillBeanWithMapIgnoreCase(rowMap,ReflectUtil.newInstance(clazz),true));
}
return result;
}finally {
close(workbook);
}
}
@Override
protected void writeData(Sheet sheet, CellStyle style, List<T> datas, String[] columns) {
T t;
Row row;
for (int i = 0; i < datas.size() ; i++) {
t = datas.get(i);
row = sheet.createRow((i + 1));
for(int j = 0; j < columns.length; j++){
try {
setCellGBKValue(style,row.createCell(j, CellType.STRING),getValue(t, columns[j]));
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
private void checkTable(Sheet sheetAt,String[] columns){
//get table head
Row headRow = sheetAt.getRow(0);
if(headRow.getPhysicalNumberOfCells() != columns.length){
throw new RuntimeException("The actual number of columns does not match the expected number of columns");
}
}
@Override
protected void writeCustomizeData(Workbook workbook,List<T> datas,String[] titles,Integer[] widths) {
//标准工具类，不提供自定义实现
}
}五、进行测试
public class ExcelTest {
public static void main(String[] args) throws Exception{
System.out.println(export()); //200000行 多线程(网络IO类型，多线程的效果会更高) 13s 单线程 18s
// importNative();
// importRemote();
// importRemoteMap();
}
private static String export(){
List<User> list = Lists.newArrayList();
long start = System.currentTimeMillis();
for(int i =0;i<200000;i++){
User user = new User();
user.setAge(10+i);
user.setName("aaa"+i);
user.setCreateTime(LocalDateTime.now());
list.add(user);
}
long end = System.currentTimeMillis();
System.out.println("生成数据花费时间：" + (end - start));
String result = new StandardExcelUtil().exportFilePath("测试2222", list, new String[]{"用户名", "年龄", "创建时间"}, new String[]{"name", "age", "createTime"});
System.out.println("生成Excel花费时间：" + (System.currentTimeMillis() - end));
return result;
}
/**
* 注意 当执行导入操作时时，格式化的时间将无法转换成LocalTime,需要重新定义Date 类型进行接收
* 如果没有进行格式化，则可以按照原类型接收
*/
private static void importNative(){
String[] columns = new String[]{"name", "age", "createTime"};
// List<User> users = new StandardExcelUtil<User>().importNative("C:/Users/Administrator/Desktop/excelExport/20190920/测试22220190920093738.xlsx", columns, User.class);
List<UserVO> users = new StandardExcelUtil<UserVO>().importNative("C:/Users/Administrator/Desktop/excelExport/20190920/测试222220190920101839.xlsx", columns, UserVO.class);
users.stream().forEach(obj -> System.out.println(obj.toString()));
}
private static void importRemote(){
String[] columns = new String[]{"name", "age", "createTime"};
List<UserVO> users = new StandardExcelUtil<UserVO>().importRemote("http://localhost:8080/zip/test.xlsx", columns, UserVO.class);
users.stream().forEach(obj -> System.out.println(obj.toString()));
}
private static void importRemoteMap(){
String[] columns = new String[]{"name", "age", "createTime"};
List<Map<String, Object>> maps = new StandardExcelUtil<UserVO>().importRemote("http://localhost:8080/zip//%E6%B5%8B%E8%AF%95222220190920101839.xlsx", columns);
maps.stream().forEach(obj -> System.out.println(obj.toString()));
}
}六、总结
以上代码为使用POI实现，如有错误，欢迎大家匹配指正！！！
最后贴出github源码：https://github.com/awyFamily/awy-common-all/tree/master/common-excel
964
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
