package com.example.demo; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.openxml4j.exceptions.OpenXML4JException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.SharedStringsTable; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.springframework.stereotype.Component; import org.xml.sax.XMLReader; import javax.print.attribute.standard.DateTimeAtCompleted; import java.io.*; import java.util.HashMap; import java.util.Iterator; @Component public class xlsxFile { public static final String file = "C:/Users/ff/Desktop/inv.xlsx" ; public static void main(String[] arags) throws IOException, OpenXML4JException, FileNotFoundException { Workbook wb = WorkbookFactory.create(new File(file)); DataFormatter dataFormatter = new DataFormatter(); wb.forEach(sheet -> { if(sheet.getSheetName().contains("VM")) { try { content(sheet,dataFormatter); } catch (FileNotFoundException e) { e.printStackTrace(); } } }); } public HashMap vmSheet() throws IOException { Workbook wb = WorkbookFactory.create(new File(file)); DataFormatter dataFormatter = new DataFormatter(); Sheet sheet = wb.getSheetAt(1); return showContent(sheet,dataFormatter); } public HashMap showContent(Sheet sheet, DataFormatter df) throws FileNotFoundException { HashMap hm = new HashMap<String, String>(); int lastRow = sheet.getLastRowNum(); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); String key = null; String value = null; Iterator<Cell> cell = row.cellIterator(); while (cell.hasNext()) { Cell c = cell.next(); int columnIndex = c.getColumnIndex(); if (columnIndex == 0) { key = c.getStringCellValue(); } if (columnIndex == 2) { value = c.getStringCellValue(); } if (key != null & value != null) { hm.put(key, value); } } } return hm; } public static void content(Sheet sheet,DataFormatter dt) throws FileNotFoundException { host h = new host(); System.setOut(new PrintStream(new FileOutputStream("C:/Users/ff/Desktop/inv.txt"))); sheet.forEach(row -> { row.forEach( cell -> { String cellValue = dt.formatCellValue(cell); if ((cell.getColumnIndex() == 0 || cell.getColumnIndex() == 2 ) && cell.getRowIndex() > 0 && cellValue.length() !=0 ) { System.out.printf("%-20s",cellValue); } }); System.out.println(); }); } }