java office

excel导出

• 导出数据到不同sheet

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.WriteSheet;

import java.util.Date;
import java.util.List;

public class Test02 {
    public static void main(String[] args) {
        noModelWrite();
    }

    /**
     * 不创建对象的写
     */
    public static void noModelWrite() {
        // 写法1
        String fileName = "D:/test.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
//        EasyExcel.write(fileName).head(head()).sheet(2, "Sheet2").doWrite(dataList());
        try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
            // 去调用写入,这里调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
            for (int i = 0; i < 5; i++) {
                // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class
                // 实际上可以一直变
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(head()).build();
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                List<List<Object>> data = dataList();
                excelWriter.write(data, writeSheet);
            }
        }
    }

    private static List<List<String>> head() {
        List<List<String>> list = ListUtils.newArrayList();
        List<String> head0 = ListUtils.newArrayList();
        head0.add("字符串");
        List<String> head1 = ListUtils.newArrayList();
        head1.add("数字");
        List<String> head2 = ListUtils.newArrayList();
        head2.add("日期");
        list.add(head0);
        list.add(head1);
        list.add(head2);
        return list;
    }

    private static List<List<Object>> dataList() {
        List<List<Object>> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            List<Object> data = ListUtils.newArrayList();
            data.add("字符串" + i);
            data.add(new Date());
            data.add(0.56);
            list.add(data);
        }
        return list;
    }
}

excel 导入

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.read.listener.ReadListener;
import com.wayserkon.demo1.domain.dto.DistrictExcelDTO;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.util.*;

@Slf4j
public class Test02 {
    public static void main(String[] args) {
        String fileName = "D:/tmp" + File.separator + "demo2.xlsx";
        // 读取全部sheet
        // 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
        List<DistrictExcelDTO> list = new ArrayList<>(128);
        EasyExcel.read(fileName, DistrictExcelDTO.class, new ReadListener<DistrictExcelDTO>() {
            @Override
            public void invoke(DistrictExcelDTO data, AnalysisContext context) {
                list.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {

            }
        }).registerConverter(new Converter<List<String>>() {    // 注册List的转换器
            @Override
            public CellDataTypeEnum supportExcelTypeKey() {
                return CellDataTypeEnum.STRING;
            }

            @Override
            public Class<?> supportJavaTypeKey() {
                return List.class;
            }

            @Override
            public List<String> convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
                String value = cellData.getStringValue();
                String[] split = value.split(" +");
                return Arrays.stream(split).toList();
            }
        }).registerConverter(new Converter<Map<String, List<Integer>>>() {  // 注册Map的转换器
            @Override
            public CellDataTypeEnum supportExcelTypeKey() {
                return CellDataTypeEnum.STRING;
            }

            @Override
            public Class<?> supportJavaTypeKey() {
                return Map.class;
            }

            @Override
            public Map<String, List<Integer>> convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
                String value = cellData.getStringValue();   // 获取一个单元格中的string
                String[] districts = value.split(" +");
                Map<String, List<Integer>> map = new LinkedHashMap<>(16);
                for (String district : districts) {
                    String[] split = district.split(":");
                    List<Integer> offsets = Arrays.stream(split[1].split(",")).map(Integer::valueOf).toList();
                    map.put(split[0], offsets);
                }
                return map;
            }
//        }).sheet(1).doRead();
        }).doReadAll();

        System.out.println(list.size());
        System.out.println(list.get(0).getFactorOffsetMap().keySet().getClass());

    }

}