Search in sources :

Example 51 with ExcelWriter

use of com.alibaba.excel.ExcelWriter in project eden-architect by shiyindaxiaojie.

the class EasyExcelWriter method write.

public <T> void write(@NonNull OutputStream outputStream, @NonNull List<T> datas, @NonNull String sheetName, Set<String> includeColumns) {
    Class<T> head = ReflectionUtils.getActualTypeArgument(datas.getClass());
    ExcelWriter excelWriter = excelWriter(outputStream, head);
    WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
    if (includeColumns != null && !includeColumns.isEmpty()) {
        // FIXME:写错单词,这个 API 后面肯定会变化
        writeSheet.setIncludeColumnFiledNames(includeColumns);
    }
    excelWriter.write(datas, writeSheet);
}
Also used : ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet)

Example 52 with ExcelWriter

use of com.alibaba.excel.ExcelWriter in project SpringBoot-Hello by ruiyeclub.

the class ExcelUtil method writeExcel.

/**
 * @param outputStream  Excel的输出流
 * @param sheetAndTable sheet和table名,格式:<sheet名,<table名集合>>
 * @param data          <sheet名,<table名,table数据集>>
 * @param clazz         <sheet名,<table名,table数据集实体class类型>>
 * @param excelTypeEnum Excel的格式(XLS或XLSX)
 * @Description: 使用模型来写入Excel,多sheet,多table
 * @Date: 2020/1/16 21:43
 * @Return: byte[]
 * @Throws: Exception
 */
public static byte[] writeExcel(ByteArrayOutputStream outputStream, Map<String, List<String>> sheetAndTable, Map<String, Map<String, List<? extends BaseRowModel>>> data, Map<String, Map<String, Class<? extends BaseRowModel>>> clazz, ExcelTypeEnum excelTypeEnum) throws Exception {
    // 这里指定需要表头,因为model通常包含表头信息
    ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum, true);
    Iterator<Map.Entry<String, List<String>>> iterator = sheetAndTable.entrySet().iterator();
    int sheetNo = 1;
    // 遍历sheet
    while (iterator.hasNext()) {
        Map.Entry<String, List<String>> next = iterator.next();
        // 当前sheet名
        String sheetName = next.getKey();
        // 当前sheet对应的table的实体类class对象集合
        Map<String, Class<? extends BaseRowModel>> tableClasses = clazz.get(sheetName);
        // 当前sheet对应的table的数据集合
        Map<String, List<? extends BaseRowModel>> dataListMaps = data.get(sheetName);
        Sheet sheet = new Sheet(sheetNo, 0);
        sheet.setSheetName(sheetName);
        int tableNo = 1;
        Iterator<Map.Entry<String, Class<? extends BaseRowModel>>> iterator1 = tableClasses.entrySet().iterator();
        // 遍历table
        while (iterator1.hasNext()) {
            Map.Entry<String, Class<? extends BaseRowModel>> next1 = iterator1.next();
            // 当前table名
            String tableName = next1.getKey();
            // 当前table对应的class
            Class<? extends BaseRowModel> tableClass = next1.getValue();
            // 当前table对应的数据集
            List<? extends BaseRowModel> tableData = dataListMaps.get(tableName);
            Table table = new Table(tableNo);
            table.setClazz(tableClass);
            writer.write(tableData, sheet, table);
            tableNo++;
        }
        sheetNo++;
    }
    writer.finish();
    return outputStream.toByteArray();
}
Also used : Table(com.alibaba.excel.metadata.Table) ExcelWriter(com.alibaba.excel.ExcelWriter) BaseRowModel(com.alibaba.excel.metadata.BaseRowModel) List(java.util.List) Map(java.util.Map) Sheet(com.alibaba.excel.metadata.Sheet)

Example 53 with ExcelWriter

use of com.alibaba.excel.ExcelWriter in project SpringBoot-Hello by ruiyeclub.

the class ExcelUtil method writeExcel.

/**
 * @param outputStream  Excel的输出流
 * @param data          要写入的以 模型 为单位的数据
 * @param clazz         模型的类
 * @param excelTypeEnum Excel的格式(XLS或XLSX)
 * @Description: 使用模型来写入Excel,单sheet,单table(返回字节数组)
 * @Date: 2020/1/16 21:43
 * @Return: byte[]
 * @Throws: Exception
 */
public static byte[] writeExcel(ByteArrayOutputStream outputStream, List<? extends BaseRowModel> data, Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum) throws Exception {
    // 这里指定需要表头,因为model通常包含表头信息
    ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum, true);
    // 写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
    Sheet sheet1 = new Sheet(1, 0, clazz);
    writer.write(data, sheet1);
    writer.finish();
    return outputStream.toByteArray();
}
Also used : ExcelWriter(com.alibaba.excel.ExcelWriter) Sheet(com.alibaba.excel.metadata.Sheet)

Example 54 with ExcelWriter

use of com.alibaba.excel.ExcelWriter in project easyexcel by alibaba.

the class FillTempTest method complexFillWithTable.

/**
 * 数据量大的复杂填充
 * <p>
 * 这里的解决方案是 确保模板list为最后一行,然后再拼接table.还有03版没救,只能刚正面加内存。
 *
 * @since 2.1.1
 */
@Test
public void complexFillWithTable() {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    // {} 代表普通变量 {.} 代表是list的变量
    // 这里模板 删除了list以后的数据,也就是统计的这一行
    String templateFileName = TestFileUtil.getPath() + "demo" + File.separator + "fill" + File.separator + "complexFillWithTable.xlsx";
    String fileName = TestFileUtil.getPath() + "complexFillWithTable" + System.currentTimeMillis() + ".xlsx";
    ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    // 直接写入数据
    excelWriter.fill(data(), writeSheet);
    excelWriter.fill(data(), writeSheet);
    // 写入list之前的数据
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("date", "2019年10月9日13:28:28");
    excelWriter.fill(map, writeSheet);
    // list 后面还有个统计 想办法手动写入
    // 这里偷懒直接用list 也可以用对象
    List<List<String>> totalListList = new ArrayList<List<String>>();
    List<String> totalList = new ArrayList<String>();
    totalListList.add(totalList);
    totalList.add(null);
    totalList.add(null);
    totalList.add(null);
    // 第四列
    totalList.add("统计:1000");
    // 这里是write 别和fill 搞错了
    excelWriter.write(totalListList, writeSheet);
    excelWriter.finish();
// 总体上写法比较复杂 但是也没有想到好的版本 异步的去写入excel 不支持行的删除和移动,也不支持备注这种的写入,所以也排除了可以
// 新建一个 然后一点点复制过来的方案,最后导致list需要新增行的时候,后面的列的数据没法后移,后续会继续想想解决方案
}
Also used : HashMap(java.util.HashMap) ExcelWriter(com.alibaba.excel.ExcelWriter) ArrayList(java.util.ArrayList) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) ArrayList(java.util.ArrayList) List(java.util.List) Test(org.junit.Test)

Example 55 with ExcelWriter

use of com.alibaba.excel.ExcelWriter in project easyexcel by alibaba.

the class FillTest method TestFillNullPoint.

@Test
public void TestFillNullPoint() {
    String templateFileName = TestFileUtil.getPath() + "temp/issue1663" + File.separator + "template.xlsx";
    String fileName = TestFileUtil.getPath() + "temp/issue1663" + File.separator + "issue1663.xlsx";
    ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).build();
    excelWriter.fill(new FillWrapper("data1", data()), fillConfig, writeSheet);
    Map<String, Object> map = new HashMap<String, Object>();
    // Variable {date} does not exist in the template.xlsx, which should be ignored instead of reporting an error.
    map.put("date", "2019年10月9日13:28:28");
    excelWriter.fill(map, writeSheet);
    excelWriter.finish();
}
Also used : HashMap(java.util.HashMap) ExcelWriter(com.alibaba.excel.ExcelWriter) FillWrapper(com.alibaba.excel.write.metadata.fill.FillWrapper) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) FillConfig(com.alibaba.excel.write.metadata.fill.FillConfig) Test(org.junit.Test)

Aggregations

ExcelWriter (com.alibaba.excel.ExcelWriter)67 WriteSheet (com.alibaba.excel.write.metadata.WriteSheet)52 Test (org.junit.Test)31 Sheet (com.alibaba.excel.metadata.Sheet)13 HashMap (java.util.HashMap)13 FillConfig (com.alibaba.excel.write.metadata.fill.FillConfig)11 List (java.util.List)9 FileOutputStream (java.io.FileOutputStream)7 IOException (java.io.IOException)7 WriteTable (com.alibaba.excel.write.metadata.WriteTable)6 ArrayList (java.util.ArrayList)6 ExcelReader (com.alibaba.excel.ExcelReader)5 ReadSheet (com.alibaba.excel.read.metadata.ReadSheet)5 FillWrapper (com.alibaba.excel.write.metadata.fill.FillWrapper)4 UnExpectedRequestException (com.webank.wedatasphere.qualitis.exception.UnExpectedRequestException)4 OutputStream (java.io.OutputStream)4 Map (java.util.Map)4 LargeDataTest (com.alibaba.easyexcel.test.core.large.LargeDataTest)3 PermissionDeniedRequestException (com.webank.wedatasphere.qualitis.exception.PermissionDeniedRequestException)3 WriteExcelException (com.webank.wedatasphere.qualitis.rule.exception.WriteExcelException)3