EasyExcel的使用的心得体会

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.metadata.TableStyle;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;

import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.assertj.core.util.Lists;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

public class ExcelImport {

@Test
public void test() throws IOException {
    // 头的策略
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    // 背景设置为灰色
    headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    //表头居中
    headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short)12);
    headWriteFont.setFontName("Arial");
    headWriteCellStyle.setWriteFont(headWriteFont);
    // 内容的策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    //内容居中
    contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    WriteFont contentWriteFont = new WriteFont();
    // 字体大小
    contentWriteFont.setFontHeightInPoints((short)10);
    contentWriteFont.setFontName("Arial");
    contentWriteCellStyle.setWriteFont(contentWriteFont);
    // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
    HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

    // 文件输出位置
    OutputStream out = new FileOutputStream("d://测试导出123.xlsx");
    ExcelWriter writer = EasyExcelFactory
            .write(out)
            .registerWriteHandler(horizontalCellStyleStrategy)
            .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
            .build();
    for (int i=0;i<10;i++){
            // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class
            // 实际上可以一直变
            WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(head()).build();
            // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
            writer.write(contentData(),writeSheet);
    }
   writer.finish();
    out.close();

}

/**
 * 生成动态表头，表头数据可以当做参数传入
 *
 * @return 表头list
 */
private static List<List<String>> head() {
    List<List<String>> headTitles = Lists.newArrayList();
    String warZone = "表头1", base = "表头2", personal = "表头3";
    headTitles.add(Lists.newArrayList(warZone));
    headTitles.add(Lists.newArrayList(base));
    headTitles.add(Lists.newArrayList(personal));
    return headTitles;
}


/**
 * 导入数据封装，需要导出数据进行传参
 *
 * @return 导出数据集合
 */
private static List<List<Object>> contentData() {
    List<List<Object>> contentList = Lists.newArrayList();
    // 这里一个List<Object>代表一行数据，需要映射成每行数据填充，横向填充（把实体数据的字段设置成一个List<Object>）
    contentList.add(Lists.newArrayList("测试", "测试A", "测试B"));
    contentList.add(Lists.newArrayList("测试", null, "测试B1"));
    return contentList;
}

}