Create Excel :- Header/color/columns etc...

package Excel_create_style;

import org.apache.poi.*;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PatternFormatting;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

public class Test2 {

public static void main(String[] args)
{
  //Workbook workbook = new XSSFWorkbook();
  Workbook workbook = new HSSFWorkbook();  
  Sheet sheet1 = workbook.createSheet("sheet1");
  Sheet sheet2 = workbook.createSheet("sheet 2");
  HSSFRow row;
 
  Row header = (HSSFRow) sheet1.createRow(0);
  sheet1.addMergedRegion(new CellRangeAddress(0,0,1,7)); //--Merge
  header.createCell(1).setCellValue("Output Structure"); //
  //Heder style
  Font font_h = workbook.createFont();
  font_h.setBold(true);
  font_h.setFontHeightInPoints((short)12);
  CellStyle Styleheader = workbook.createCellStyle();
  Styleheader.setAlignment(HorizontalAlignment.CENTER);  
  Styleheader.setFont(font_h);
  Styleheader.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
  Styleheader.setFillPattern(FillPatternType.SOLID_FOREGROUND);
 
  header.getCell(1).setCellStyle(Styleheader);
 
  //************************************
 
     
  //************************************
 
  //--- Row 2 me ye headers aaega
  row = (HSSFRow) sheet1.createRow(2);
  row.setHeight((short) 500);

  row.createCell(1).setCellValue("Segment");
  row.createCell(2).setCellValue("Region");
  row.createCell(3).setCellValue("Borrower Name");
  row.createCell(4).setCellValue("RM Name");
  row.createCell(5).setCellValue("Date Full Information received");
  row.createCell(6).setCellValue("Date Letter Issued");
  row.createCell(7).setCellValue("Date RM Letter Requested");
 
 
  //---
  //----Font Formatting
 
  for(int i =1;i<=7;i++) {
  CellStyle stylerowHeading = workbook.createCellStyle();
  Font font = workbook.createFont();
  font.setBold(true);
  font.setFontHeightInPoints((short)10);
  stylerowHeading.setFont(font);
  stylerowHeading.setAlignment(HorizontalAlignment.CENTER);
  stylerowHeading.setVerticalAlignment(VerticalAlignment.CENTER);
  stylerowHeading.setFillForegroundColor(IndexedColors.TURQUOISE.getIndex());
  stylerowHeading.setFillPattern(FillPatternType.SOLID_FOREGROUND);
 
  stylerowHeading.setBorderLeft(BorderStyle.MEDIUM);
  stylerowHeading.setBorderRight(BorderStyle.MEDIUM);
  stylerowHeading.setBorderTop(BorderStyle.MEDIUM);
  stylerowHeading.setBorderBottom(BorderStyle.MEDIUM);
 
  row.getCell(i).setCellStyle(stylerowHeading);
  }
 
  //------Auto Fit
  for(int i =1;i<=7;i++)
  {
  sheet1.autoSizeColumn(i);
  }
 
 
  //*****************
  //CellStyle dynamic_rows = workbook.createCellStyle();
  //dynamic_rows.setBorderLeft(BorderStyle.THIN);
  //dynamic_rows.setBorderRight(BorderStyle.THIN);
  //dynamic_rows.setBorderBottom(BorderStyle.THIN);
 
  //******************
 
  String[] rowVal = new String[7];
  insertrowVal(rowVal);  
  Map < String, String[]> m = new LinkedHashMap < String, String[] >();
  //m.put( "1", rowVal);
  m.put( "1", rowVal);
  m.put( "2", rowVal);
  m.put( "3", rowVal);
  m.put( "4", rowVal);
  m.put( "5", rowVal);
   
//Iterate over data and write to sheet
      Set <String> keyid = m.keySet();
      int rowid = 3;

      for (String key : keyid) {
    row = (HSSFRow) sheet1.createRow(rowid++);
         String [] str = m.get(key);
         int cellid = 1;
     
         for (String obj : str)
         {
        //row.getCell(cellid).setCellStyle(dynamic_rows);
            Cell cell = row.createCell(cellid++);
            cell.setCellValue((String)obj);
           
         }
      }
     
  try
  {
  FileOutputStream output = new FileOutputStream("D:\\work\\newgen2.xls");
  workbook.write(output);
  output.close();
  System.out.println("File created ....");

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

private static void insertrowVal(String[] rowVal)
{
rowVal[0]="apple";
rowVal[1]="Orange";
rowVal[2]="Banana";
rowVal[3]="Mango";
rowVal[4]="Papaya";
rowVal[5]="Lichy";
rowVal[6]="Grapes";

}

}

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

JARS :- poi-bin-4.1.0-20190412.tar

bc2

package upload_download_Excel_DB;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

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.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ProcessData
{
static String WI_NAME = "1234";
static Connection con= null;

public static void main(String[] args)
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr");
Statement st = con.createStatement();

con.setAutoCommit(false);
PreparedStatement pstm = null;
FileInputStream fis = new FileInputStream("E:\\Root\\Newgen\\ShareCapital.xlsm");

XSSFWorkbook wb = new XSSFWorkbook(fis);

System.out.println("1");
XSSFSheet sheet1 = wb.getSheetAt(0);
int rowcount=sheet1.getLastRowNum();
System.out.println("Rowcount>>"+rowcount);
XSSFRow row ;
System.out.println(">>>>"+sheet1.getLastRowNum());
Cell cell= null;
DataFormatter formatter = new DataFormatter();
for(int i =6; i<=rowcount+1; i++)  //FOR SHEET 1 || SHARE CAPITAL
{
row = sheet1.getRow(i);
if(row!= null)
{
String sno = formatter.formatCellValue(row.getCell(0));
System.out.println(sno);

String class_name = formatter.formatCellValue(row.getCell(1));
//int result = Integer.parseInt(class_name);
System.out.println(class_name);
}
 
 
 



System.out.println("########Before insert_excel_sharedcapital()");

//insert_(WI_NAME,S_No,Share_Class_Number,Share_Class_Code,Currency_Code,Issued_Value,Number_issued,Voting_Rights,Share_Type_Description);
}


con.close();
/*
* XSSFFactory fs = new POIFSFileSystem( input ); XSSFWorkbook wb = new
* XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(0); Row row;
*/
} catch (ClassNotFoundException e) {
System.out.println(e);
} catch (SQLException ex) {
System.out.println(ex);
} catch (IOException ioe) {
System.out.println(ioe);
}

}
private static void insert_excel_sharedcapital(String WI_NAME,String s_No,
String share_Class_Number, int share_Class_Code,
String currency_Code, String issued_Value, int number_issued,
int voting_Rights, String share_Type_Description)
    {
    System.out.println("Before Inserting into NG_EXP_AR_SCS_CO");

String tableName = "NG_EXP_AR_SCS_CO";
String colNames = "WINAME,s_no,share_class_number,share_class_code,currency_code,issued_value,number_issued,voting_rights,share_type_description";

String colValues = "'"+WI_NAME+",'"+s_No+"','"+share_Class_Number+"',"+share_Class_Code+",'"+currency_Code+"','"+issued_Value+"',"+number_issued+",'"+voting_Rights+"','"+share_Type_Description+",";

//String insertXML = XMLGen.APInsert(objWF.cabinetName, objWF.sessionID, tableName, colValues); //-----------------------------doubt----------------4 PARAMETERS




    }


}

BC

https://stackoverflow.com/questions/2645566/finding-the-last-row-in-an-excel-spreadsheet

The only way to know for sure is to test the rows. Here's the solution I'm using for the same problem:

int lastRowIndex = -1;
if( sheet.getPhysicalNumberOfRows() > 0 )
{
    // getLastRowNum() actually returns an index, not a row number
    lastRowIndex = sheet.getLastRowNum();

    // now, start at end of spreadsheet and work our way backwards until we find a row having data
    for( ; lastRowIndex >= 0; lastRowIndex-- ){
        Row row = sheet.getRow( lastRowIndex );
        if( row != null ){
            break;
        }
    }
}

Note: this doesn't check for rows that appear to be empty but aren't, such as cells that have an empty string in them. For that, you need a more complete solution like:

private int determineRowCount()
{
    this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    this.formatter = new DataFormatter( true );

    int lastRowIndex = -1;
    if( sheet.getPhysicalNumberOfRows() > 0 )
    {
        // getLastRowNum() actually returns an index, not a row number
        lastRowIndex = sheet.getLastRowNum();

        // now, start at end of spreadsheet and work our way backwards until we find a row having data
        for( ; lastRowIndex >= 0; lastRowIndex-- )
        {
            Row row = sheet.getRow( lastRowIndex );
            if( !isRowEmpty( row ) )
            {
                break;
            }
        }
    }
    return lastRowIndex;
}

/**
 * Determine whether a row is effectively completely empty - i.e. all cells either contain an empty string or nothing.
 */
private boolean isRowEmpty( Row row )
{
    if( row == null ){
        return true;
    }

    int cellCount = row.getLastCellNum() + 1;
    for( int i = 0; i < cellCount; i++ ){
        String cellValue = getCellValue( row, i );
        if( cellValue != null && cellValue.length() > 0 ){
            return false;
        }
    }
    return true;
}

/**
 * Get the effective value of a cell, formatted according to the formatting of the cell.
 * If the cell contains a formula, it is evaluated first, then the result is formatted.
 * 
 * @param row the row
 * @param columnIndex the cell's column index
 * @return the cell's value
 */
private String getCellValue( Row row, int columnIndex )
{
    String cellValue;
    Cell cell = row.getCell( columnIndex );
    if( cell == null ){
        // no data in this cell
        cellValue = null;
    }
    else{
        if( cell.getCellType() != Cell.CELL_TYPE_FORMULA ){
            // cell has a value, so format it into a string
            cellValue = this.formatter.formatCellValue( cell );
        }
        else {
            // cell has a formula, so evaluate it
            cellValue = this.formatter.formatCellValue( cell, this.evaluator );
        }
    }
    return cellValue;
}