使用EasyExcel操作Excel
Maven
<!--alibaba easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>
导出:
public class test {
//创建表头
private List<List<String>> createHeadList(){
List<String> headcoulum1 = new ArrayList<>();
List<String> headcoulum2 = new ArrayList<>();
List<String> headcoulum3 = new ArrayList<>();
List<String> headcoulum4 = new ArrayList<>();
List<String> headcoulum5 = new ArrayList<>();
headcoulum1.add("第一列");
headcoulum2.add("第二列");
headcoulum3.add("第三列");
headcoulum4.add("第四列");
headcoulum5.add("第五列");
List<List<String>> list = Arrays.asList(headcoulum1,headcoulum2,headcoulum3,headcoulum4,headcoulum5);
return list;
}
//创建写入内容
private List<List<Object>> createModelList(){
List<List<Object>> list = new ArrayList<>();
for (int i=0;i<100;i++){
List<Object> row = new ArrayList<>();
row.add("第一列第"+i+"行数据");
row.add("第二列第"+i+"行数据");
row.add("第三列第"+i+"行数据");
row.add("第四列第"+i+"行数据");
row.add("第五列第"+i+"行数据");
list.add(row);
}
return list;
}
//创建表格样式
private TableStyle createTableStytle(){
TableStyle tableStyle = new TableStyle();
//设置表头样式
Font headfont = new Font();
//设置是否加粗
headfont.setBold(true);
//设置字体大小
headfont.setFontHeightInPoints((short) 12);
//设置字体
headfont.setFontName("楷体");
tableStyle.setTableHeadFont(headfont);
//设置主体样式
Font contentfont = new Font();
//设置是否加粗
contentfont.setBold(false);
//设置字体大小
contentfont.setFontHeightInPoints((short) 12);
//设置字体
contentfont.setFontName("楷体");
tableStyle.setTableContentFont(contentfont);
return tableStyle;
}
@Test
public void t() throws Exception{
//文件输出位置
FileOutputStream fos = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\test.xlsx");
ExcelWriter excelWriter = EasyExcelFactory.getWriter(fos);
//创建n个Sheet的Excel文件
Sheet sheet = new Sheet(1,0);
//设置sheet名称
sheet.setSheetName("sheet名称");
//创建一个表格在sheet中使用
Table table = new Table(1);
//创建表头
table.setHead(createHeadList());
//自定义表格样式
table.setTableStyle(createTableStytle());
//写入数据
excelWriter.write1(createModelList(),sheet,table);
//写入文件
excelWriter.finish();
//关闭流
fos.close();
}
}
读取:
public class test {
private static List<String> read(String filepath) {
List<String> sheetContent = new ArrayList<>();
//String filepath ="E:\\all_cach\\1.xlsx";
try (InputStream inputStream = new FileInputStream(filepath)) {
ExcelReader excelReader = new ExcelReader(inputStream, null,
new AnalysisEventListener<List<String>>() {
@Override
public void invoke(List<String> object, AnalysisContext context) {
/**
* 自行添加syl 》》》
*/
StringBuffer stringBuffer = new StringBuffer();
if(object != null && !StringUtils.isEmpty(object.get(0))){
for(String s: object){
if(null!=s){
stringBuffer.append(s+"||");
}
}
System.out.println("行->"+context.getCurrentRowNum()+": "+stringBuffer.toString());
sheetContent.add(stringBuffer.toString());
/**
* 《《《自行添加结束
*/
System.out.println("当前sheet:" + context.getCurrentSheet().getSheetNo() + ",当前行:" +
context.getCurrentRowNum());
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
});
excelReader.read();
} catch (Exception e) {
e.printStackTrace();
}
return sheetContent;
}
@Test
public void t() throws Exception{
long act = System.currentTimeMillis();
String filepath ="C:\\Users\\Administrator\\Desktop\\test.xlsx";
List<String> sheetContent = read(filepath);
System.out.println("一共"+sheetContent.size()+"有效数据");
long end = System.currentTimeMillis();
System.out.println("耗时间=======:"+(end-act)+"毫秒");
}
}