使用 Apache POI 根据 csv 工作表更新 Excel 工作表值

我对java还是个新手。我在 java 语法方面遇到了一些问题。 我的程序应该执行以下过程：

1）它以csv文件作为输入。

2）它以excel文件作为输入。

3) 它应该迭代写入日期的两个文件的第一列。

4) 通过添加 csv 工作表中的信息来更新 excel 文件并保存其更改。

我有一个

我有两个输入示例以及结果 Excel 工作表的外观。

两个输入文件：

导出-csv-输入.csv

导出-excel-输入.xlsx

更新后的 Excel 文件应如下所示：

导出-excel-输出.xlsx

我的 Java 代码：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.Reader;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CsvToExcelConverter {

  public static final String SAMPLE_XLSX_FILE_PATH = 
 "C:/Users/blawand/Desktop/CSV_to_Excel/export-excel-test.xlsx";
  public static final String SAMPLE_CSV_FILE_PATH = 
"C:/Users/blawand/Desktop/CSV_to_Excel/export-csv-test.csv";
  public static List<String> dates_csv = new ArrayList<>();
  public static List<String> dates_excel = new ArrayList<>();

  public static void main(String[] args) throws IOException, 
InvalidFormatException {

    try (Reader reader = 
Files.newBufferedReader(Paths.get(SAMPLE_CSV_FILE_PATH));
        CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT);) 
{

      for (CSVRecord csvRecord : csvParser) {
        // Accessing Values by Column Index
         String name = csvRecord.get(0);
        dates_csv.add(name);
      }
      dates_csv.remove(0);
    }

    FileInputStream fsIP = new FileInputStream(new 
File(SAMPLE_XLSX_FILE_PATH));

    /*
     * ================================================================== 
Iterating over all the
     * rows and columns in a Sheet (Multiple ways)
     * ==================================================================
     */

    // Getting the Sheet at index zero
    XSSFWorkbook workbook = new XSSFWorkbook(fsIP);

    XSSFSheet sheet = workbook.getSheetAt(0);

    // Get the Cell at index 2 from the above row
    // Cell cell1 = sheet.getRow(1).getCell(0);
    // for (int i = 0; i < dates_excel.size(); i++) {
    // XSSFRow rowtest = sheet.createRow((short) i + 1);
    // rowtest.createCell(0).setCellValue(dates_csv.get(i));
    //
    // }

    // cell1.setCellValue(dates_csv.get(0));
    // Create a DataFormatter to format and get each cell's value as 
String
    DataFormatter dataFormatter = new DataFormatter();

    for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) 
{
      Row row = sheet.getRow(rowIndex);
      if (row != null) {
        Cell cell = row.getCell(0); // getColumn(0)
        if (cell != null) {
          // Found column and there is value in the cell.
          // String cellValueMaybeNull = cell.getStringCellValue();
          String cellValueMaybeNull = 
dataFormatter.formatCellValue(cell);

      // String to number set
      dates_excel.add(cellValueMaybeNull);

    }
  }
}

System.out.println(dates_csv);
System.out.println(dates_csv.size());
System.out.println(dates_excel);
System.out.println(dates_excel.size());

while (dates_excel == dates_excel) {

  System.out.println("Yes");
  break;
}
fsIP.close();
FileOutputStream output_file = new FileOutputStream(new 
File(SAMPLE_XLSX_FILE_PATH));
workbook.write(output_file);
output_file.close();

  }
}

我已经阅读了这两个文件，但我在更新 Excel 文件并将项目名称添加到正确的日期时遇到问题。如果相同的日期已在 csv 表中写入两次或多次。

您还想了解哪些信息？

我将感谢每一个帮助或建议！

我有一个例子给你，主要是通过代码注释来解释的。尽管如此，该代码基本上执行以下操作：

在构造函数中获取 xlsx 和 csv 文件的文件路径。

更新时，它首先将csv文件的内容读取到一个Map with a LocalDate作为钥匙和List<String>作为价值观。
然后它会遍历工作簿的行，跳过标题行，并将第一列中的日期与Map<LocalDate, List<String>>。如果映射包含该键，它将开始检查该行中的单元格是否存在当前值，并将它们保留在列表中，以便以后不写入它们。
然后它开始将值写入具有关键日期的行的单元格中。

我希望这有帮助。

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.time.Instant;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CsvXlsxUpdater {

    private static final DateTimeFormatter DATE_TIME_FORMATTER_CSV = DateTimeFormatter.ofPattern("dd.MM.yyyy");

    private Path csvFilePath;
    private Path xlsxFilePath;
    private XSSFWorkbook workbook;
    private XSSFSheet sheet;
    private Map<LocalDate, List<String>> csvContent = new TreeMap<LocalDate, List<String>>();
    private ZoneId zoneId = ZoneId.systemDefault();

    public CsvXlsxUpdater(String pathToCsvFile, String pathToXlsxFile) {
        csvFilePath = Paths.get(pathToCsvFile);
        xlsxFilePath = Paths.get(pathToXlsxFile);
    }

    /**
     * Reads the content of the csv file into the corresponding class variable,
     * which is a {@link TreeMap} that has a {@link LocalDate} as key and a 
     * {@link List<String>} as values.
     */
    private void readCsvContent() {
        List<String> csvLines;

        try {
            csvLines = Files.readAllLines(csvFilePath);

            for (int i = 1; i < csvLines.size(); i++) {
                String line = csvLines.get(i);
                String[] splitValues = line.split(",");

                if (splitValues.length > 1) {
                    List<String> lineValues = Arrays.asList(splitValues);
                    List<String> projects = getProjectValuesFrom(lineValues);
                    LocalDate localDate = LocalDate.parse(lineValues.get(0), DATE_TIME_FORMATTER_CSV);
                    if (csvContent.containsKey(localDate)) {
                        projects.forEach((String project) -> {
                            List<String> csvProjects = csvContent.get(localDate);
                            if (!csvProjects.contains(project)) {
                                csvProjects.add(project);
                            }
                        });
                    } else {
                        csvContent.put(localDate, projects);
                    }
                } else {
                    LocalDate localDate = LocalDate.parse(splitValues[0], DATE_TIME_FORMATTER_CSV);
                    csvContent.put(localDate, new ArrayList<String>());
                }
            }
        } catch (IOException e) {
            System.err.println("CANNOT FIND OR READ CSV FILE: " + e.getMessage());
            e.printStackTrace();
        } catch (UnsupportedOperationException e) {
            System.err.println("UNSUPPORTED OPERATION: " + e.getMessage());
            e.printStackTrace();
        }

    }

    /**
     * Gets the corresponding {@link LocalDate} from a given (and deprecated)
     * {@link Date}
     * 
     * @param date the deprecated {@link Date} object
     * @return the corresponding {@link LocalDate}
     */
    private LocalDate parseLocalDateFrom(Date date) {
        Instant instantDate = date.toInstant();
        return instantDate.atZone(zoneId).toLocalDate();
    }

    /**
     * Takes a list of read values from the csv file and returns a list containing
     * all the values of the given list <strong>except from the first
     * element</strong>, which is a {@link String} representation of a date and
     * should be treated differently in this context.
     * 
     * @param values the original list of {@link String}s
     * @return another list without the first element of the given list
     */
    private List<String> getProjectValuesFrom(List<String> values) {
        List<String> projectValues = new ArrayList<String>();

        for (int i = 1; i < values.size(); i++) {
            String value = values.get(i);
            if (!value.equals("")) {
                projectValues.add(value);
            }
        }

        return projectValues;
    }

    /**
     * Updates the workbook with the values read from the csv file
     */
    public void updateWorkbook() {
        readCsvContent();
        try {
            FileInputStream fis = new FileInputStream(xlsxFilePath.toAbsolutePath().toString());
            workbook = new XSSFWorkbook(fis);
            sheet = workbook.getSheetAt(0);
            
            // iterate over the rows
            Iterator<Row> rowIterator = sheet.rowIterator();
            
            while (rowIterator.hasNext()) {
                XSSFRow row = (XSSFRow) rowIterator.next();
                
                if (row.getRowNum() == 0) {
                    // skip this or set updated headers
                } else {
                    // check if the csvContent contains the value of cell(0)
                    LocalDate dateKey = parseLocalDateFrom(row.getCell(0).getDateCellValue()); 
                    if (csvContent.containsKey(dateKey)) {
                        // if yes, get list-value of the key
                        List<String> values = csvContent.get(dateKey);
                        
                        // check if there are values
                        if (values != null) {
                            if (values.size() > 0) {
                                // if there are, then go checking the cell values
                                List<String> projectsInXlsx = new ArrayList<String>();
                                Iterator<Cell> cellIterator = row.cellIterator();
                                int lastColumnIndex = 1;
                                
                                // go through all cells with a value except from the first one
                                while (cellIterator.hasNext()) {
                                    Cell cell = cellIterator.next();
                                    
                                    // skip the first column as it contains the date
                                    if (cell.getColumnIndex() != 0) {
                                        lastColumnIndex = cell.getColumnIndex();
                                        System.out.println("Accessing cell in column " + lastColumnIndex);
                                        // if there is a cell with a value
                                        if (cell.getStringCellValue() != null) {
                                            if (!cell.getStringCellValue().equals("")) {
                                                // check if the value in the cell is also in the csv values
                                                if (values.contains(cell.getStringCellValue())) {
                                                    projectsInXlsx.add(cell.getStringCellValue());
                                                    lastColumnIndex++;
                                                }
                                            }
                                        }
                                    }
                                }
                                
                                // now go through the values of the csv file
                                int offset = 0; // cell column offset for more than one entry per date
                                for (String value : values) {
                                    if (!projectsInXlsx.contains(value)) {
                                        // create a cell after the last one with a value
                                        row.createCell(lastColumnIndex + offset).setCellValue(value);
                                        offset++;
                                    }
                                }
                            }
                        }
                    }
                }
            }
            
            fis.close();
        
            FileOutputStream fileOutputStream = new FileOutputStream(xlsxFilePath.toAbsolutePath().toString());
            workbook.write(fileOutputStream);
            fileOutputStream.flush();
            fileOutputStream.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

In a main方法，您只需调用构造函数，将文件路径传递为Strings，然后调用updateWorkbook()方法，因为它首先在内部读取 csv。

Example:

public class CsvXlsxMain {

    private static final String CSV_FILE_PATH = "S:\\ome\\example\\path\\to\\csv-input.csv";
    private static final String XLSX_FILE_PATH = "S:\\ome\\example\\path\\to\\excel-input.xlsx";
    
    public static void main(String[] args) {
        CsvXlsxUpdater cxu = new CsvXlsxUpdater(CSV_FILE_PATH, XLSX_FILE_PATH);
        cxu.updateWorkbook();
    }
}

请记住，这代码未经过充分测试，以后可能会出现资源交替的问题 如果需要，请使用适合您要求的各种 xlsx 和 csv 输入进行测试。

我没有使用任何库来解析csv文件！

我希望这对你有一点帮助……