欢迎访问我的个人网站==》 jiashubing.cn

POI实现,两个Excel里重复字段去重,之后写到另一个Excel(代码库)

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.jetbrains.annotations.NotNull;

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

/**
 * @author jiash
 * @since 2023/4/18
 */
public class StringQuChongForTwoExcelTest {
    private static final Map<String, YhtCountry> countryMap = new HashMap<>();


    public static void main(String[] args) throws Exception {
        String file1 = "E:\\_user_id_varchar_927-1_.xlsx";
        String file2 = "E:\\_user_id_varchar_927-2_.xlsx";

        List<String> list1 = getListFromExcel(file1);
        List<String> list2 = getListFromExcel(file2);
        System.out.println(list1.size());
        Collection union = CollectionUtils.union(list1, list2);
        List<String> ans = new ArrayList<>(union);

        try (InputStream is = new FileInputStream("E:\\cccc.xlsx");
             Workbook book = WorkbookFactory.create(is);
             OutputStream os = new FileOutputStream("E:\\cccc.xlsx");) {
            Sheet sheet = book.getSheetAt(0);

            for (int i = 0; i < ans.size(); i++) {
                Row row = sheet.createRow(i + 1);
                Cell cell = row.createCell(0);
                if (cell != null) {
                    cell.setCellValue(ans.get((i)));
                }
            }
            book.write(os);
        } catch (IOException e) {
            //return;
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    @NotNull
    private static List<String> getListFromExcel(String file1) {
        List<String> list1 = new ArrayList<>(3000000);
        try (InputStream is = new FileInputStream(file1);) {
            Workbook book = WorkbookFactory.create(is);
            Sheet sheet = book.getSheetAt(0);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                Cell cell = row.getCell(0);
                if (cell != null) {
                    String userId = cell.getStringCellValue();
                    if (StringUtils.isEmpty(userId)) {
                        continue;
                    }
                    list1.add(userId);
                }
            }
        } catch (IOException e) {
            //return;
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return list1;
    }

}

 

posted @ 2023-10-23 15:17  贾树丙  阅读(21)  评论(0编辑  收藏  举报