使用 Apache POI 根据 csv 工作表更新 Excel 工作表值 [英] Updating Excel sheet values based on a csv sheet with Apache POI
本文介绍了使用 Apache POI 根据 csv 工作表更新 Excel 工作表值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我还是 Java 新手.我在 java 语法方面遇到了一些问题.我的程序应该执行以下程序:
1) 它需要一个 csv 文件作为输入.
2) 它需要一个 excel 文件作为输入.
3) 它应该遍历写入日期的两个文件的第一列.
4) 通过添加 csv 表中的信息来更新 excel 文件并保存其更改.
我有一个 live.com/?cid=24b4fceb4f4e4098&id=24B4FCEB4F4E4098%213018&authkey=%21AKKzaZsJ5pkd5NE
我有两个输入示例以及结果 Excel 表的外观.
两个输入文件:
export-csv-input.csv
export-excel-input.xlsx
更新后的 excel 文件应如下所示:
export-excel-output.xlsx
我的 Java 代码:
import java.io.File;导入 java.io.FileInputStream;导入 java.io.FileOutputStream;导入 java.io.IOException;导入 java.io.Reader;导入 java.nio.file.Files;导入 java.nio.file.Paths;导入 java.util.ArrayList;导入 java.util.List;导入 org.apache.commons.csv.CSV 格式；导入 org.apache.commons.csv.CSVParser；导入 org.apache.commons.csv.CSVRecord；导入 org.apache.poi.openxml4j.exceptions.InvalidFormatException;导入 org.apache.poi.ss.usermodel.Cell;导入 org.apache.poi.ss.usermodel.DataFormatter;导入 org.apache.poi.ss.usermodel.Row;导入 org.apache.poi.xssf.usermodel.XSSFSheet;导入 org.apache.poi.xssf.usermodel.XSSFWorkbook；公共类 CsvToExcelConverter {公共静态最终字符串 SAMPLE_XLSX_FILE_PATH ="C:/Users/blawand/Desktop/CSV_to_Excel/export-excel-test.xlsx";公共静态最终字符串 SAMPLE_CSV_FILE_PATH ="C:/Users/blawand/Desktop/CSV_to_Excel/export-csv-test.csv";公共静态列表date_csv = new ArrayList<>();公共静态列表日期_excel = new ArrayList<>();public static void main(String[] args) 抛出 IOException，无效格式异常{尝试(读者读者=Files.newBufferedReader(Paths.get(SAMPLE_CSV_FILE_PATH));CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT);){for (CSVRecord csvRecord : csvParser) {//通过列索引访问值字符串名称 = csvRecord.get(0);date_csv.add(name);}日期_csv.remove(0);}FileInputStream fsIP = new FileInputStream(new文件(SAMPLE_XLSX_FILE_PATH))；/** ====================================================================迭代所有* 表格中的行和列(多种方式)* ====================================================================*///获取索引为零的工作表XSSFWorkbook 工作簿 = 新 XSSFWorkbook(fsIP);XSSFSheet sheet = workbook.getSheetAt(0);//从上面的行中获取索引 2 处的单元格//单元格 cell1 = sheet.getRow(1).getCell(0);//for (int i = 0; i 我已经阅读了这两个文件，但是我在更新 excel 文件和将项目名称添加到正确的日期时遇到了问题.如果相同的日期在 csv 表中被写入两次或更多次.
您还想知道哪些信息?
我会感谢每一个帮助或建议！
解决方案
我有一个例子给你，主要是通过代码注释来解释的.尽管如此，代码基本上做了以下事情:
在构造函数中获取 xlsx 和 csv 文件的文件路径.
更新时，它首先将csv文件的内容读入一个Map，以LocalDate为键，一个List作为值.
然后它遍历工作簿的行，跳过标题行并将第一列中的日期与 Map 的键进行比较.如果映射包含该键，它会开始检查该行中的单元格是否存在当前值，并将它们保存在一个列表中，以便以后不会写入它们.
然后它开始将值写入具有关键日期的行的单元格中.
我希望这会有所帮助.
import java.io.FileInputStream;导入 java.io.FileOutputStream;导入 java.io.IOException;导入 java.nio.file.Files;导入 java.nio.file.Path;导入 java.nio.file.Paths;导入 java.time.Instant;导入 java.time.LocalDate;导入 java.time.ZoneId;导入 java.time.format.DateTimeFormatter;导入 java.util.ArrayList;导入 java.util.Arrays;导入 java.util.Date;导入 java.util.Iterator;导入 java.util.List;导入 java.util.Map;导入 java.util.TreeMap;导入 org.apache.poi.ss.usermodel.Cell;导入 org.apache.poi.ss.usermodel.Row;导入 org.apache.poi.xssf.usermodel.XSSFRow;导入 org.apache.poi.xssf.usermodel.XSSFSheet;导入 org.apache.poi.xssf.usermodel.XSSFWorkbook；公共类 CsvXlsxUpdater {private static final DateTimeFormatter DATE_TIME_FORMATTER_CSV = DateTimeFormatter.ofPattern(dd.MM.yyyy");私有路径 csvFilePath;私有路径 xlsxFilePath;私有 XSSFWorkbook 工作簿；私人 XSSFSheet 表；私有地图>csvContent = new TreeMap>();私人 ZoneId zoneId = ZoneId.systemDefault();公共 CsvXlsxUpdater(String pathToCsvFile, String pathToXlsxFile) {csvFilePath = Paths.get(pathToCsvFile);xlsxFilePath = Paths.get(pathToXlsxFile);}/*** 将csv文件的内容读入对应的类变量中，* 这是一个 {@link TreeMap}，它有一个 {@link LocalDate} 作为键和一个* {@link List} 作为值.*/私有无效 readCsvContent() {列表<字符串>csv行；尝试 {csvLines = Files.readAllLines(csvFilePath);for (int i = 1; i < csvLines.size(); i++) {字符串行 = csvLines.get(i);String[] splitValues = line.split(",");如果(splitValues.length > 1){列表<字符串>lineValues = Arrays.asList(splitValues);列表<字符串>项目 = getProjectValuesFrom(lineValues);LocalDate localDate = LocalDate.parse(lineValues.get(0), DATE_TIME_FORMATTER_CSV);如果 (csvContent.containsKey(localDate)) {project.forEach((String project) -> {列表<字符串>csvProjects = csvContent.get(localDate);如果 (!csvProjects.contains(project)) {csvProjects.add(project);}});} 别的 {csvContent.put(localDate, 项目);}} 别的 {LocalDate localDate = LocalDate.parse(splitValues[0], DATE_TIME_FORMATTER_CSV);csvContent.put(localDate, new ArrayList());}}} catch (IOException e) {System.err.println("无法找到或读取 CSV 文件:" + e.getMessage());e.printStackTrace();} catch (UnsupportedOperationException e) {System.err.println("不支持的操作:" + e.getMessage());e.printStackTrace();}}/*** 从给定的(已弃用)获取相应的 {@link LocalDate}* {@link 日期}** @param date 已弃用的 {@link Date} 对象* @return 对应的 {@link LocalDate}*/私有 LocalDate parseLocalDateFrom(Date date) {Instant InstantDate = date.toInstant();return InstantDate.atZone(zoneId).toLocalDate();}/*** 从 csv 文件中获取读取值列表并返回包含* 给定列表的所有值 除了第一个* 元素</strong>，它是日期的 {@link String} 表示和* 在这种情况下应该区别对待.** @param 值 {@link String} 的原始列表* @return 另一个没有给定列表第一个元素的列表*/私人列表<字符串>getProjectValuesFrom(List values) {列表<字符串>projectValues = new ArrayList();for (int i = 1; i < values.size(); i++) {字符串值 = values.get(i);如果(！value.equals(")){projectValues.add(value);}}返回项目值；}/*** 使用从 csv 文件读取的值更新工作簿*/公共无效更新工作簿(){readCsvContent();尝试 {FileInputStream fis = new FileInputStream(xlsxFilePath.toAbsolutePath().toString());工作簿 = 新的 XSSFWorkbook(fis);sheet = workbook.getSheetAt(0);//遍历行迭代器<行>rowIterator = sheet.rowIterator();而 (rowIterator.hasNext()) {XSSFRow row = (XSSFRow) rowIterator.next();如果 (row.getRowNum() == 0) {//跳过这个或设置更新的标题} 别的 {//检查 csvContent 是否包含 cell(0) 的值LocalDate dateKey = parseLocalDateFrom(row.getCell(0).getDateCellValue());如果(csvContent.containsKey(dateKey)){//如果是，获取键的列表值列表<字符串>值 = csvContent.get(dateKey);//检查是否有值如果(值！= null){如果 (values.size() > 0) {//如果有，则检查单元格值列表<字符串>projectInXlsx = new ArrayList();迭代器<Cell>cellIterator = row.cellIterator();int lastColumnIndex = 1;//遍历除第一个以外的所有具有值的单元格而(cellIterator.hasNext()){Cell cell = cellIterator.next();//跳过第一列，因为它包含日期如果(cell.getColumnIndex()！= 0){lastColumnIndex = cell.getColumnIndex();System.out.println("访问列中的单元格" + lastColumnIndex);//如果有一个带有值的单元格如果(cell.getStringCellValue()！= null){if (!cell.getStringCellValue().equals("")) {//检查单元格中的值是否也在 csv 值中if (values.contains(cell.getStringCellValue())) {projectInXlsx.add(cell.getStringCellValue());lastColumnIndex++;}}}}}//现在遍历 csv 文件的值整数偏移= 0;//每个日期多个条目的单元格列偏移for(字符串值:值){如果 (!projectsInXlsx.contains(value)) {//在最后一个单元格之后创建一个带有值的单元格row.createCell(lastColumnIndex + offset).setCellValue(value);偏移++；}}}}}}}fis.close();FileOutputStream fileOutputStream = new FileOutputStream(xlsxFilePath.toAbsolutePath().toString());workbook.write(fileOutputStream);fileOutputStream.flush();fileOutputStream.close();workbook.close();} catch (IOException e) {e.printStackTrace();}}} 在 main 方法中，您只需调用构造函数，将文件路径作为 String 传递，然后调用 updateWorkbook() 方法，因为它内部先读取 csv.
示例:
公共类 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";公共静态无效主(字符串 [] args){CsvXlsxUpdater cxu = new CsvXlsxUpdater(CSV_FILE_PATH, XLSX_FILE_PATH);cxu.updateWorkbook();}}<块引用>
请记住，此代码未经过全面测试，将来可能会出现交替资源问题如果需要，请使用符合您要求的各种 xlsx 和 csv 输入对其进行测试.
我没有使用任何库来解析 csv 文件！
希望对你有所帮助...
I am still new to java. I am having a bit problems with the java syntax. My Program should do the following procedure:
1) It takes a csv file as an input.
2) It takes an excel file as an input.
3) It should iterate over the first columns of the two files where the dates are written.
4) Update the excel file by adding the information from the csv sheet and save its changes.
where I have the two input examples and how the result excel sheet should look like.
Two Input files:
export-csv-input.csv
export-excel-input.xlsx
The updated excel file should look like:
export-excel-output.xlsx
My Java Code yet:
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.
Which information would you like also to know?
I would be thankful for every help or advice!
解决方案
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.
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.
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.
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.
I haven't used any library to parse the csv file!
I hope this helps you a little…
这篇关于使用 Apache POI 根据 csv 工作表更新 Excel 工作表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
