package com.walker.di.univocity;
|
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.EasyExcelFactory;
|
import com.alibaba.excel.ExcelWriter;
|
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
|
import com.alibaba.excel.write.metadata.WriteSheet;
|
import com.walker.di.DataImportException;
|
import com.walker.di.excel.ExcelTemplateGenerator;
|
import com.walker.infrastructure.utils.StringUtils;
|
import org.junit.Before;
|
import org.junit.Test;
|
|
import java.io.BufferedInputStream;
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.FileOutputStream;
|
import java.io.InputStream;
|
import java.io.OutputStream;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
public class TestExcel {
|
|
private InputStream inputStream = null;
|
|
@Before
|
public void init() throws Exception{
|
// inputStream = new BufferedInputStream(new FileInputStream(new File("d:/tmp/test_user_30.xlsx")));
|
inputStream = new BufferedInputStream(new FileInputStream(new File("d:/tmp/user_import.xlsx")));
|
}
|
|
@Test
|
public void testTemplateGenerator() throws Exception{
|
ExcelTemplateGenerator templateGenerator = new ExcelTemplateGenerator() {
|
@Override
|
protected String acquireWriteFilePath(Object option) {
|
return "d:/tmp/template_demo.xlsx";
|
}
|
|
@Override
|
protected List<Map<String, String>> acquireWriteContent(Object option) {
|
List<Map<String, String>> resultList = new ArrayList<>();
|
Map<String, String> map = new HashMap<>();
|
map.put("id", "编号");
|
resultList.add(map);
|
|
Map<String, String> map2 = new HashMap<>();
|
map2.put("name", "姓名");
|
resultList.add(map2);
|
return resultList;
|
}
|
};
|
|
templateGenerator.generate(new Object());
|
}
|
|
// @Test
|
public void testLoadExcelImportor(){
|
DefaultExcelDataImportor dataImportor = new DefaultExcelDataImportor(this.inputStream);
|
dataImportor.setBatchEnabled();
|
dataImportor.setHeadRowNumber(2);
|
// dataImportor.setIgnoreRows(1);
|
dataImportor.setBatchSleepMills(1000);
|
dataImportor.setId("789009");
|
dataImportor.setSaveFileFolder("d:/tmp/");
|
dataImportor.setSaveSizeOnce(5);
|
try {
|
dataImportor.load();
|
System.out.println("批量导入完成");
|
} catch (DataImportException e) {
|
System.out.println("导入异常:" + e.getMessage());
|
System.out.println(dataImportor.getBatchError());
|
e.printStackTrace();
|
}
|
}
|
|
// @Test
|
public void loadExcel(){
|
ArrayDataListener arrayDataListener = new ArrayDataListener();
|
// ObjectDataListener arrayDataListener = new ObjectDataListener();
|
EasyExcel.read(this.inputStream, arrayDataListener).sheet(0).headRowNumber(2).doRead();
|
System.out.println("........end");
|
}
|
|
// @Test
|
public void writeExcel() throws Exception{
|
File file = new File("d:/tmp/test_write.xlsx");
|
|
OutputStream out = new FileOutputStream(file);
|
ExcelWriterBuilder writer = EasyExcelFactory.write(out);
|
writer.head(this.getHeaders());
|
// WriteSheet writeSheet = writer.sheet(0).build();
|
// writer.sheet(0);
|
writer.sheet("test_write");
|
ExcelWriter excelWriter = writer.build();
|
|
WriteSheet writeSheet = new WriteSheet();
|
// writer.sheet(0, "test_write");
|
writeSheet.setSheetName("demo");
|
excelWriter.write(this.getData(), writeSheet);
|
excelWriter.finish();
|
out.close();
|
}
|
|
private List<List<String>> getData(){
|
List<List<String>> headers = new ArrayList<>();
|
headers.add(StringUtils.asList(new String[]{"123","shikeying","1233889746596"}));
|
headers.add(StringUtils.asList(new String[]{"789","孙悟空","13838277463"}));
|
return headers;
|
}
|
private List<List<String>> getHeaders(){
|
List<List<String>> headers = new ArrayList<>();
|
headers.add(StringUtils.asList(new String[]{"编号","id"}));
|
headers.add(StringUtils.asList(new String[]{"名字","name"}));
|
headers.add(StringUtils.asList(new String[]{"身份证","card"}));
|
// headers.add(StringUtils.asList(new String[]{"编号","名字","身份证"}));
|
// headers.add(StringUtils.asList(new String[]{"id","name","card"}));
|
return headers;
|
}
|
}
|
