使用Apache POI基于csv表更新Excel工作表值

I am still new to java. I am having a bit problems with the java syntax. My Program should do the following procedure:

我还是java新手。我对java语法有点问题。我的程序应该执行以下过程:

1) It takes a csv file as an input.

1)它将csv文件作为输入。

2) It takes an excel file as an input.

2)它将excel文件作为输入。

3) It should iterate over the first columns of the two files where the dates are written.

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

4) Update the excel file by adding the information from the csv sheet and save its changes.

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

I have a https://onedrive.live.com/?cid=24b4fceb4f4e4098&id=24B4FCEB4F4E4098%213018&authkey=%21AKKzaZsJ5pkd5NE

我有一个https://onedrive.live.com/?cid=24b4fceb4f4e4098&id=24B4FCEB4F4E4098%213018&authkey=%21AKKzaZsJ5pkd5NE

where I have the two input examples and how the result excel sheet should look like.

我有两个输入示例,结果excel表应该如何。

Two Input files:

两个输入文件:

export-csv-input.csv

export-excel-input.xlsx

The updated excel file should look like:

更新的excel文件应如下所示:

export-excel-output.xlsx

My Java Code yet:

我的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();

  }
}

I read already the two files but i am having problems with updating the excel file and adding the project names to the correct dates. And if the same date has been written two or more times in the csv sheet.

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

Which information would you like also to know?

您还想知道哪些信息?

I would be thankful for every help or advice!

我会感谢每一个帮助或建议!

1 个解决方案

#1

I have an example for you, mostly explained by code comments. Nevertheless, the code basically does the following:

我有一个例子,主要由代码注释解释。不过,代码基本上做了以下事情:

Takes file paths of the xlsx and csv file in the constructor.

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

When updating, it first reads the content of the csv file into a Map with a LocalDate as key and a List<String> as values.
Then it goes through the rows of the workbook skipping the header row and comparing the dates in column one with the keys of the Map<LocalDate, List<String>>. If the map contains that key, it starts checking the cells in that row for present values and keeps them in a list in order to not write them later.
Then it starts writing the values into the cells of the row with the key date.

更新时,它首先将csv文件的内容读入Map,其中LocalDate为键,List 为值。然后它遍历工作簿的行,跳过标题行并将第一列中的日期与Map >的键进行比较。如果映射包含该键,它将开始检查该行中的单元格以获取当前值,并将它们保留在列表中,以便以后不再写入它们。然后它开始将值写入具有关键日期的行的单元格中。 ,list>

I hope this helps.

我希望这有帮助。

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 method, you would just have to call the constructor, pass the file paths as Strings and then call the updateWorkbook() method, because it internally reads the csv first.

在main方法中,您只需调用构造函数,将文件路径作为字符串传递,然后调用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();
    }
}

Please keep in mind that this CODE IS NOT FULLY TESTED, there may be problems with alternating resources in future If you need, go testing it with various xlsx and csv inputs that fit your requirements.

请记住,此代码未经过全面测试,未来可能存在交换资源的问题如果需要,请使用符合您要求的各种xlsx和csv输入进行测试。

I haven't used any library to parse the csv file!

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

I hope this helps you a little…

我希望这对你有所帮助......