Excel Reader

Excel Reader

package com.parser;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Main {
	public static DataFormatter dataFormatter = new DataFormatter();
	private static ExcelParser excelParser;
	
	public static void main(String[] args) throws IOException {
		// https://howtodoinjava.com/apache-commons/readingwriting-excel-files-in-java-poi-tutorial/
		
	  String excelFilePath = "seat.xlsx";
	  String outPutExcelFile = "final_seat_plan";
	  	
	  excelParser = new AgraniBankParser();
	  excelParser.parse();
	  
	  //excelParser = new FiveBanksResultParser(excelFilePath, outPutExcelFile);
	  //excelParser.parse();
		
	/*	String excelFilePath = "NPSB.xlsx";
		try {
			readTable();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		}*/
	
	}


	public static void readTable() throws InvalidFormatException, IOException {

		try {
			FileInputStream file = new FileInputStream(new File("seat.xlsx"));

			// Create Workbook instance holding reference to .xlsx file
			XSSFWorkbook workbook = new XSSFWorkbook(file);

			// Get first/desired sheet from the workbook
			XSSFSheet sheet = workbook.getSheetAt(0);
			// Iterate through each rows one by one

			// Create a DataFormatter to format and get each cell's value as String
			

			// 1. You can obtain a rowIterator and columnIterator and iterate over them
			/*
			 * System.out.println("\n\nIterating over Rows and Columns using Iterator\n");
			 * Iterator<Row> rowIterator = sheet.rowIterator(); while
			 * (rowIterator.hasNext()) { Row row = rowIterator.next();
			 * 
			 * // Now let's iterate over the columns of the current row Iterator<Cell>
			 * cellIterator = row.cellIterator();
			 * 
			 * while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String
			 * cellValue = dataFormatter.formatCellValue(cell); System.out.print(cellValue +
			 * "\t"); } System.out.println(); }
			 */

			// 2. Or you can use a for-each loop to iterate over the rows and columns
			System.out.println("\n\nIterating over Rows and Columns using for-each loop\n");

			/*
			 * for (Row row: sheet) { for(Cell cell: row) {
			 * 
			 * String cellValue = dataFormatter.formatCellValue(cell);
			 * System.out.print(cellValue + "\t"); } System.out.println(); }
			 */

			Workbook xworkbook = new XSSFWorkbook(); // new HSSFWorkbook() for generating `.xls` file

	        /* CreationHelper helps us create instances for various things like DataFormat, 
	           Hyperlink, RichTextString etc, in a format (HSSF, XSSF) independent way */
	        CreationHelper createHelper = xworkbook.getCreationHelper();
	        // Create a Sheet
	        org.apache.poi.ss.usermodel.Sheet xsheet = xworkbook.createSheet("seat plan");

	        // Create a Row
	        Row headerRow = xsheet.createRow(0);

	        String[]columns = {"job_id", "room_location", "start_roll", "roll_to"};
	        // Creating cells
	        for(int i = 0; i < columns.length; i++) {
	            Cell cell = headerRow.createCell(i);
	            cell.setCellValue(columns[i]);
	        }
	
			StringBuilder rowBuilder = new StringBuilder();
			
			boolean isComma = false;

			String first = "", second = "", four ="", five="";

			String[]myRows = new String[4];
			int rowNum = 1; 
			for (Row row : sheet) {

				if(allEmpty(row)) {
					first = "";
					second = "";
				}
				
				String firstCellValue = dataFormatter.formatCellValue(row.getCell(0));

				if (!firstCellValue.isEmpty() && Character.isDigit(firstCellValue.charAt(0))) {

					String cellFirst = dataFormatter.formatCellValue(row.getCell(1));
					if (!cellFirst.isEmpty()) {
						first = cellFirst;
					}
					String cellSecond = dataFormatter.formatCellValue(row.getCell(2));
					if(!cellSecond.isEmpty()) {
						second = cellSecond;
					}
					String third = dataFormatter.formatCellValue(row.getCell(3));
					four = dataFormatter.formatCellValue(row.getCell(4));
					five = dataFormatter.formatCellValue(row.getCell(5));

					if (!first.isEmpty()) {
						rowBuilder.append(first);
						isComma = true;
					}

					if (!second.isEmpty()) {
						if (isComma) {
							rowBuilder.append(", ");
						}
						rowBuilder.append(second);
						isComma = true;
					}

					if (!third.isEmpty()) {
						if (isComma) {
							rowBuilder.append(", ");
						}
						rowBuilder.append("Room no: " + third);
					}

					isComma = false;
				}
				if (rowBuilder.length() != 0) {
					///System.out.println(rowBuilder.toString());
				
					myRows[0]="10041";
					myRows[1]= rowBuilder.toString();
					myRows[2] = four;
					myRows[3] = five;
					
					  Row xrow = xsheet.createRow(rowNum++);
				    for(int j =0; j<myRows.length; j++) {
			          
			            xrow.createCell(j)
			                    .setCellValue(myRows[j]);
			            }

					rowBuilder.setLength(0);
					System.out.println();
				}

				/*
				 * for(int i = row.getFirstCellNum(); i< row.getLastCellNum(); i++) { row.get
				 * String cellValue = dataFormatter.formatCellValue(row.getCell(i));
				 * System.out.print(cellValue +"\t"); }
				 */
				// System.out.println(row.getFirstCellNum() + " " + row.getLastCellNum());

			}

			// 3. Or you can use Java 8 forEach loop with lambda

			/*
			 * sheet.forEach(row -> { row.forEach(cell -> { printCellValue(cell); });
			 * System.out.println(); });
			 */

			// Closing the workbook
			workbook.close();
			
			
			
			
			// Resize all columns to fit the content size
	        for(int i = 0; i < columns.length; i++) {
	            xsheet.autoSizeColumn(i);
	        }

	        // Write the output to a file
	        FileOutputStream fileOut = new FileOutputStream("final_seat_plan.xlsx");
	        xworkbook.write(fileOut);
	        fileOut.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static boolean allEmpty(Row row) {
		String cellFirst = dataFormatter.formatCellValue(row.getCell(1));
		String second = dataFormatter.formatCellValue(row.getCell(2));
		String third = dataFormatter.formatCellValue(row.getCell(3));
		String fourth = dataFormatter.formatCellValue(row.getCell(4));
		String fifth = dataFormatter.formatCellValue(row.getCell(5));
		return cellFirst.isEmpty() && second.isEmpty() && third.isEmpty() && fourth.isEmpty() && fifth.isEmpty();
	}

	public static void readXLSFile() throws IOException {
		// File myFile = new File("NPSB.xlsx");

		try {
			FileInputStream file = new FileInputStream(new File("seat.xlsx"));

			// Create Workbook instance holding reference to .xlsx file
			XSSFWorkbook workbook = new XSSFWorkbook(file);

			// Get first/desired sheet from the workbook
			XSSFSheet sheet = workbook.getSheetAt(0);
			// Iterate through each rows one by one

			Iterator<Row> rowIterator = sheet.iterator();
			// using for loop on rows of excel sheet
			for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
				Row ro = sheet.getRow(i);
				for (int j = ro.getFirstCellNum(); j < ro.getLastCellNum(); j++) {
					Cell ce = ro.getCell(j);
					System.out.println(ce);
					// add the logic to update the aaray
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}