import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.*;
import java.util.*;
import javax.swing.*;
// reading from excel
public class apacheexcel
{ //read  String from excell
public static String[][] read_String_from_excel(String filename, int pageno)
{
  String a[][]=new String[10][10];	  
  try{
  int nx,ny=0,nx0=0,ny0=0,nx1=0,ny1=0;
  Workbook workbook =wb(filename);
  Sheet sheet= workbook.getSheetAt(pageno);
  nx1=sheet.getLastRowNum();
  nx0=sheet.getFirstRowNum();
  nx=nx1-nx0;
  System.out.println("nx0="+nx0+"nx1="+nx1+"nx="+nx);
  for(int i=0;i<nx;i++)
  {   Row r=sheet.getRow(i);
      if(r==null) break;
      if(i==nx0)
      {ny0= r.getFirstCellNum();
	   ny1= r.getLastCellNum();
	   ny=ny1-ny0;
	   System.out.println("ny0="+ny0+"ny1="+ny1+"ny="+ny);
       a=new String[nx][ny];
      }
	  for(int j=0;j<ny;j++)
      {  Cell c=r.getCell(j+ny0,Row.RETURN_NULL_AND_BLANK);
         if(c==null) a[i][j]="null";
         else a[i][j] =toString(c);
      } 
  }
} 
  catch(ArrayIndexOutOfBoundsException e2) {System.err.println("wrong array dimension");}
  catch(NullPointerException e3) {System.err.println("null pointer exception");}
  return a;
}
public static String[][] read_String_from_excel1(String filename, int pageno)
{
    Workbook Work=wb(filename);
    String s[][]=new String [2000][2000];
    int i=0,j=0,i1=0,j1=0;
    Sheet sheet1 = Work.getSheetAt(pageno);
    Iterator rowItr = sheet1.rowIterator();  
    while ( rowItr.hasNext() ) 
    {   j=0;
	    Row row = (Row) rowItr.next();             
        Iterator cellItr = row.cellIterator();
        while ( cellItr.hasNext() ) 
        { s[i][j]=toString(cellItr);
          //in case of an empty line stop iteration
          if(s[i][j].charAt(0)==' ') {i=i1;j=j1;break;}
          //System.out.print("i="+i+"j="+j+"*"+s[i][j]+"* ");
          j++;j1=j;
        } 
   i++;i1=i;
   }
   int n=i1;
   int m=j1;
   String s1[][]=new String [n][m];
   for(i=0;i<n;i++)
   {for(j=0;j<m;j++)
     {s1[i][j]=s[i][j];}
   }
   return s1;
}
public static String[][] read_String_from_excel(String filename, int pageno,String xy0,String xyn)
{int n0[]=excelcode(xy0);
 int n1[]=excelcode(xyn);
 int nx=n1[0]-n0[0]+1;
 int ny=n1[1]-n0[1]+1;
 return read_String_from_excel(filename,pageno,n0[0],n0[1],nx,ny);
}
public static String[][] read_String_from_excel(String filename, int pageno,int x0,int y0,int nx,int ny)
{
  String a[][]=new String[nx][ny];
  int nx1,ny1;	  
  //try{
  Workbook workbook =wb(filename);
  Sheet sheet= workbook.getSheetAt(pageno);
  int totalrows=sheet.getLastRowNum();
  if(totalrows<nx) nx1=totalrows;
  else nx1=nx;
  for(int i=0;i<nx1;i++)
  {   Row r=sheet.getRow(x0+i);
      int totalCells = r.getPhysicalNumberOfCells();
      if(totalCells<ny) ny1=totalCells;
      else ny1=ny;
	  for(int j=0;j<ny1;j++)
      {  Cell c=r.getCell((y0+j),Row.RETURN_BLANK_AS_NULL);
	     if(c==null) break;
	     else {a[i][j] =toString(c);}
      } 
  }
  return a;
}
//read double from excel
public static double[][] read_Double_from_excel(String filename, int pageno,int x0,int y0,int nx,int ny)
{
  double a[][]=new double[nx][ny];
  int nx1,ny1;	  
  //try{
  Workbook workbook =wb(filename);
  Sheet sheet= workbook.getSheetAt(pageno);
  int totalrows=sheet.getLastRowNum();
  if(totalrows<nx) nx1=totalrows;
  else nx1=nx;
  for(int i=0;i<nx1;i++)
  {   Row r=sheet.getRow(x0+i);
      int totalCells = r.getPhysicalNumberOfCells();
      if(totalCells<ny) ny1=totalCells;
      else ny1=ny;
	  for(int j=0;j<ny1;j++)
      {  Cell c=r.getCell((y0+j),Row.RETURN_BLANK_AS_NULL);
	     if(c==null) break;
	     else {a[i][j] =toDouble(c);}
      } 
  }
  return a;
}

public static double[][] read_Double_from_excel(String filename, int pageno)
{
  double a[][]=new double[10][10];	  
  int nx,ny,nx0=0,ny0=0,nx1=0,ny1=0;
  Workbook workbook =wb(filename);
  Sheet sheet= workbook.getSheetAt(pageno);
  nx1=sheet.getLastRowNum();
  nx0=sheet.getFirstRowNum();
  nx=nx1-nx0;
  for(int i=nx0;i<nx1;i++)
  {   Row r=sheet.getRow(i);
      if(i==0)
      {ny0= r.getFirstCellNum();
	   ny1= r.getLastCellNum();
	   ny=ny1-ny0;
       a=new double[nx][ny];
      }
	  for(int j=ny0;j<ny1;j++)
      {  Cell c=r.getCell(j,Row.RETURN_BLANK_AS_NULL);
	     if(c==null) break;
	     else {a[i][j] =toDouble(c);}
      } 
  }
  return a;
}

public static String[][] read_String_from_excel(String filename, int pageno,String xy0,int nx,int ny)
{int n0[]=excelcode(xy0);
 return read_String_from_excel(filename,pageno,n0[0],n0[1],nx,ny);
}


public static double[][] read_Double_from_excel(String filename, int pageno,String xy0,int nx,int ny)
{int n0[]=excelcode(xy0);
 return read_Double_from_excel(filename,pageno,n0[0],n0[1],nx,ny);
}

public static double[][] read_Double_from_excel(String filename, int pageno,String xy0,String xyn)
{int n0[]=excelcode(xy0);
 int n1[]=excelcode(xyn);
 int nx=n1[0]-n0[0]+1;
 int ny=n1[1]-n0[1]+1;
 return read_Double_from_excel(filename,pageno,n0[0],n0[1],nx,ny);
}
//writing data to excel
public static void write_to_excel(String filename, int pageno,String xy0,Object a[][])
{
int n0[]=excelcode(xy0);
int nx=n0[0];
int ny=n0[1];
Workbook workbook =wb1(filename);
String s1=""+pageno;
 Sheet sheet= workbook.createSheet(s1);
 int n=a.length;
 int m=a[0].length;
 for(int i=0;i<n;i++)
 { Row row=sheet.createRow(nx+i);
   for(int j=0;j<m;j++)
   {Cell cell=row.createCell(ny+j);
    String adress=new CellReference(cell).formatAsString();
    cell.setCellValue((String)a[i][j]);
   }
 }
try {   
     FileOutputStream out=new FileOutputStream(filename);
     workbook.write(out);
     out.close();
    } catch (Exception e) {e.printStackTrace();} 
}
public static void write_to_excel(String filename, int pageno,Object a[][])
{write_to_excel(filename,pageno,"A1",a);}
public static void write_Double_to_excel(String filename, int pageno,String xy0,double a[][])
{
int n0[]=excelcode(xy0);
int nx=n0[0];
int ny=n0[1];
Workbook workbook =wb1(filename);
String s1=""+pageno;
 Sheet sheet= workbook.createSheet(s1);
 int n=a.length;
 int m=a[0].length;
 for(int i=0;i<n;i++)
 { Row row=sheet.createRow(nx+i);
   for(int j=0;j<n;j++)
   {Cell cell=row.createCell(ny+j);
    String adress=new CellReference(cell).formatAsString();
    cell.setCellValue(Double.toString(a[i][j]));
   }
 }
try {   
     FileOutputStream out=new FileOutputStream(filename);
     workbook.write(out);
     out.close();
    } catch (Exception e) {e.printStackTrace();} 
}
public static void write_Double_to_excel(String filename, int pageno,double a[][])
{write_Double_to_excel(filename,pageno,"A1",a);}
public static Workbook wb1(String filename)
{   String s2=word(filename);
    Workbook Work=new HSSFWorkbook();
    try {   
    if(s2.equals("xls"))
    {Work=new HSSFWorkbook();}
    else
    {Work= new SXSSFWorkbook() ;} 
    } catch (Exception e) {e.printStackTrace();} 
    return Work;	
}
//updating data
public static void update_String_from_excel(String filename, int pageno,int x0,int y0,Object a[][])
{
  int nx1,ny1;	
  int nx=a.length;
  int ny=a[0].length;  
  Workbook Work=null;
   FileInputStream inputStr=null;
   String s2=word(filename);
    try {   
    inputStr= new FileInputStream(new File(filename));
    if(s2.equals("xls"))
    {Work=new HSSFWorkbook(inputStr);}
    else
    {Work= new XSSFWorkbook(inputStr) ;} 
    
  Sheet sheet= Work.getSheetAt(pageno);
  for(int i=0;i<nx;i++)
  {   Row r=sheet.getRow(x0+i);
	  for(int j=0;j<ny;j++)
      {  Cell c=r.getCell((y0+j),Row.RETURN_BLANK_AS_NULL);
         c.setCellValue((String)a[i][j]);
      } 
  }
    inputStr.close();
    } catch (IOException e) {e.printStackTrace();} 
  FileOutputStream outFile=null;
  try{
  outFile =new FileOutputStream(new File(filename));
    Work.write(outFile);
    outFile.close();    
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}
}
//general methods
public static boolean isLetter(char x)
{ boolean x1;
  if((x>='A' && x<='Z') || (x>='a' && x<='z')) x1=true;
  else x1=false;  
  return x1;	
}

public static boolean isNumber(char x)
{ boolean x1;
  if(x>='0' && x<='9') x1=true;
  else x1=false;
  return x1;	
}

public static int charnumber(char x)
{ 
  char b1[]={'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'};
  char b2[]={'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'};
  for(int i=0;i<26;i++)
  {if(x==b1[i] || x==b2[i]) return i;}
  return 0;
}

public static int[] excelcode(String s)
{  s=s.trim();
   int len = s.length();
   //System.out.println("excelcode = "+s+"len="+len);
 int n[]=new int[2];
 String s1="";
 char a[]=new char[len];
 int n1[]=new int[2];
 for(int i=0;i<len;i++)
 {a[i]=s.charAt(i);
 }
 if(isLetter(a[0]) && isLetter(a[1]))
 {
 n1[1]=(charnumber(a[0])+1)*26+charnumber(a[1]);
 for(int i=2;i<len;i++) s1+=a[i];
 }
 else
 {n1[1]=charnumber(a[0]);
 for(int i=1;i<len;i++) s1+=a[i];
 }
 n1[0]=Integer.parseInt(s1)-1;
 return n1;
}	

public static String toString(Cell cell)
{
            String s="";
            if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN) 
            { s=cell.getBooleanCellValue() + "\t\t";}
            else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC)
            { s=cell.getNumericCellValue() + "\t\t";}
            else if(cell.getCellType()==Cell.CELL_TYPE_STRING)
            {s=cell.getStringCellValue() + "\t\t";}
            else if(cell.getCellType()==Cell.CELL_TYPE_FORMULA)
            {s=cell.getNumericCellValue() + "\t\t";}
            return s;
}
     public static String word(String s)
     {  //returns extension group of file name 
     StringTokenizer token=new StringTokenizer(s,".");
     int m=token.countTokens();
     String b[]=new String[m];
     int j=0;           
     while(token.hasMoreTokens())
     {
     String ax=(String)token.nextToken();
     b[j++]=ax;
     }
     return b[m-1];
     }
public static Workbook wb(String filename)
{
    String s2=word(filename);
    Workbook Work=null;
    try {   
     FileInputStream inputStr= new FileInputStream(filename);
    if(s2.equals("xls"))
    {Work=new HSSFWorkbook(inputStr);}
    else
    {Work= new XSSFWorkbook(inputStr) ;} 
    } catch (Exception e) {e.printStackTrace();} 
    return Work;	
}    

public static String toString(Iterator cellItr)
{
            String s="";
            Cell cell = (Cell) cellItr.next();
            if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN) 
            { s=cell.getBooleanCellValue() + "\t\t";}
            else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC)
            { s=cell.getNumericCellValue() + "\t\t";}
            else if(cell.getCellType()==Cell.CELL_TYPE_STRING)
            {s=cell.getStringCellValue() + "\t\t";}
            else if(cell.getCellType()==Cell.CELL_TYPE_FORMULA)
            {s=cell.getNumericCellValue() + "\t\t";}
            return s;
}

public static double toDouble(Cell cell)
{
            String s="";
            if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN) 
            { s=0.0 + "\t\t";}
            else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC)
            { s=cell.getNumericCellValue() + "\t\t";}
            else if(cell.getCellType()==Cell.CELL_TYPE_STRING)
            {s=0.0 + "\t\t";}
            else if(cell.getCellType()==Cell.CELL_TYPE_FORMULA)
            {s=cell.getNumericCellValue() + "\t\t";}
            return Double.parseDouble(s);
}
public static void main(String arg[])
{         String s[][]={{"0","1"},{"10","11"},{"20","21"}};
          update_String_from_excel("DATA1.xls",0,1,1,s);
}
}