用Java和Apache POI将xlsx文件中的空单元格填充为映射中的结果

huangapple go评论89阅读模式
英文:

Fill xlsx file empty cell with results from a map using Java and Apache POI

问题

  1. private static void writeFile(Map<String, String> products) throws IOException {
  2. File file = new File("./data/stock_zonder_imageurls.xlsx");
  3. FileInputStream fis = new FileInputStream(file);
  4. Workbook workbook = new XSSFWorkbook(fis);
  5. Sheet datatypeSheet = workbook.getSheetAt(0);
  6. Iterator<Row> iterator = datatypeSheet.iterator();
  7. while (iterator.hasNext()) {
  8. Row currentRow = iterator.next();
  9. Iterator<Cell> cellIterator = currentRow.iterator();
  10. while (cellIterator.hasNext()) {
  11. Cell cell = cellIterator.next();
  12. if (cell != null && cell.getCellTypeEnum().equals(CellType.STRING)) {
  13. if (cell.getColumnIndex() == 2) {
  14. String val = cell.getStringCellValue();
  15. if (products.containsKey(val)) {
  16. String image_url = products.get(val);
  17. Cell firstColumnCell = currentRow.createCell(0); // Create a new cell in the first column
  18. firstColumnCell.setCellValue(image_url); // Set the image URL value in the newly created cell
  19. }
  20. }
  21. }
  22. }
  23. }
  24. fis.close(); // Close the FileInputStream before writing to the file
  25. // Write the updated content back to the file
  26. FileOutputStream fos = new FileOutputStream(file);
  27. workbook.write(fos);
  28. fos.close(); // Close the FileOutputStream after writing
  29. }
英文:

I basically have 2 xlsx files, one with image url's and a product id, and one with a empty image url's and a product id.

I already load the contents I need to parse in the other file correctly, the parameter with the map contains all the info required.

So my question is, how can I write in the new file with the results from the mappings? I basically need to put the image url on the first column's index which contains the image urls for all the products.

Here is some sample code,

  1. private static void writeFile(Map&lt;String, String&gt; products) throws IOException {
  2. File file = new File(&quot;./data/stock_zonder_imageurls.xlsx&quot;);
  3. FileInputStream fis = new FileInputStream(file);
  4. Workbook workbook = new XSSFWorkbook(fis);
  5. Sheet datatypeSheet = workbook.getSheetAt(0);
  6. Iterator&lt;Row&gt; iterator = datatypeSheet.iterator();
  7. while (iterator.hasNext()) {
  8. Row currentRow = iterator.next();
  9. Iterator&lt;Cell&gt; cellIterator = currentRow.iterator();
  10. while (cellIterator.hasNext()) {
  11. Cell cell = cellIterator.next();
  12. if(cell != null &amp;&amp; cell.getCellTypeEnum().equals(CellType.STRING)) {
  13. if(cell.getColumnIndex() == 2) {
  14. String val = cell.getStringCellValue();
  15. if(products.containsKey(val)) {
  16. String image_url = products.get(val);
  17. //here I need to write to the file the image url on column 0 of the specified $file with the $image_url
  18. }
  19. }
  20. }
  21. }
  22. }
  23. }

答案1

得分: 1

根据问题,您的要求是更新现有的 Excel 文件。

请尝试以下步骤。

  1. // 创建行 ID 并初始化
  2. int rowId = 0;
  3. while (iterator.hasNext()) {
  4. ---- //另一段代码
  5. rowId++;
  6. if (products.containsKey(val)) {
  7. String image_url = products.get(val);
  8. // 假设第一行是标题
  9. Cell cell = datatypeSheet.getRow(rowId).getCell(0); // 从相关行(rowId)和第一列(列索引为 0)获取单元格
  10. cell.setCellValue(image_url); // 将 image_url 设置到单元格中
  11. }
  12. ---- //另一段代码
  13. }

注意:上述代码是根据您提供的内容进行的翻译,可能需要根据上下文和实际情况进行微调。

英文:

as per the question, your requirement is a update the existing excel file.

try with following steps.

  1. //create a row id and initialized
  2. int rowId = 0;
  3. while (iterator.hasNext()) {
  4. ---- //another code
  5. rowId++;
  6. if(products.containsKey(val)) {
  7. String image_url = products.get(val);
  8. //assumed as first row is header
  9. Cell cell = datatypeSheet.getRow(rowId).getCell(0); //get the cell from relevant row (rowId) and first column (column index is a 0)
  10. cell.setCellValue(image_url); //set image_url into cell
  11. }
  12. ---- //another code
  13. }

huangapple
  • 本文由 发表于 2020年8月29日 22:12:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/63647917.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定