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 excel
{
// read double data assumes remaining of the page is empty
public static double[][] read_double(String filename, int pageno)
{String[][] s=read_String(filename,pageno);
 double a[][]=new double[s.length][s[0].length];
    int i,j;
 for(i=0;i<s.length;i++)
 {for(j=0;j<s[0].length;j++)
   {a[i][j]=Double.parseDouble(s[i][j]);}
 }
 return a;
}
// read String data assumes remaining of the page is empty
public static String[][] read_String(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<Row> rowItr = sheet1.rowIterator();  
    while ( rowItr.hasNext() ) 
    {   j=0;
	    Row row = rowItr.next();             
        Iterator<Cell> cellItr = row.cellIterator();
        while ( cellItr.hasNext() ) 
        { s[i][j]=toString(cellItr.next());
          //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;	
}
//read String data from xy0 to xyn (For example form A1 to D5)
public static String[][] read_String(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(filename,pageno,n0[0],n0[1],nx,ny);
}
//read double data from xy0 to xyn (For example form A1 to D5)
public static double[][] read_double(String filename, int pageno,String xy0,String xyn)
{String[][] s=read_String(filename,pageno,xy0,xyn);
 double a[][]=new double[s.length][s[0].length];
    int i,j;
 for(i=0;i<s.length;i++)
 {for(j=0;j<s[0].length;j++)
   {a[i][j]=Double.parseDouble(s[i][j]);}
 }
 return a;
}
// starting form cell reference (x0,y0) and  the length of nx,ny
public static String[][] read_String(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()+1;
  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()+1;
      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;
}
//converts double array to string array
public String[][] doubletoString(double a[][])
{ String s[][]=new String[a.length][a[0].length];
    int i,j;
 for(i=0;i<a.length;i++)
 {for(j=0;j<a[0].length;j++)
   {s[i][j]=""+a[i][j];}
 }
 return s;
}
//converts String array to double array
public double[][] StringtoDouble(String s[][])
{ double a[][]=new double[s.length][s[0].length];
    int i,j;
 for(i=0;i<s.length;i++)
 {for(j=0;j<s[0].length;j++)
   {a[i][j]=Double.parseDouble(s[i][j]);}
 }
 return a;
}
public static void writeT(String filename, int pageno,String xy0,double b[])
{double b1[][]={b};
 double a[][]=T(b1);
 writeT(filename,pageno,xy0,a);
}

public static void writeT(String filename, int pageno,String xy0,double b[][])
{
double a[][]=T(b);
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(""+a[i][j]);
   }
 }
try {   
     FileOutputStream out=new FileOutputStream(filename);
     workbook.write(out);
     out.close();
    } catch (Exception e) {e.printStackTrace();} 
}

public static void writeT(String filename, int pageno,String xy0,Object b[][])
{
Object a[][]=T(b);
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(String filename, int pageno,String xy0,double b[])
{double b1[][]={b};
 writeT(filename,pageno,xy0,b1);
}

public static void write(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(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<m;j++)
   {Cell cell=row.createCell(ny+j);
    String adress=new CellReference(cell).formatAsString();
    cell.setCellValue(""+a[i][j]);
   }
 }
try {   
     FileOutputStream out=new FileOutputStream(filename);
     workbook.write(out);
     out.close();
    } catch (Exception e) {e.printStackTrace();} 
}
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=token.nextToken();
     b[j++]=ax;
     }
     return b[m-1];
     }
     
public static Workbook wb(String filename)
{
    String s2=word(filename);
    Workbook Work=new HSSFWorkbook();
    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 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;	
}
   
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;
}
    //Transpose matrix invert column and rows of data
     public static double[][] T(double a[][])
     { 
	 int n=a.length;
	 int i,j;
	 int m=a[0].length;
	 double b[][]=new double[m][n];    
     for(i=0;i<n;i++)
     { for(j=0;j<m;j++)
       {b[j][i]=a[i][j];}}
     return b;
     }
     public static Object[][] T(Object a[][])
     { 
	 int n=a.length;
	 int i,j;
	 int m=a[0].length;
	 Object b[][]=new Object[m][n];    
     for(i=0;i<n;i++)
     { for(j=0;j<m;j++)
       {b[j][i]=a[i][j];}}
     return b;
     }
public static Plot plotT(String filename, int pageno,String xy0,String xyn,int index[][]) 
{ //xy0 starting adress
  //xyn end of block adress
  //index plotting pair column numbers (of array a, not the excell file) ie {{0,1},{0,2}}
  double a[][]=T(read_double(filename,pageno,xy0,xyn));
  int n=index.length;
  Plot pp=new Plot(a[index[0][0]],a[index[0][1]]);
  for(int i=1;i<n;i++) {pp.addData(a[index[i][0]],a[index[i][1]]);}
  return pp;
}
     
public static Plot plot(String filename, int pageno,String xy0,String xyn,int index[][]) 
{ double a[][]=read_double(filename,pageno,xy0,xyn);
  int n=index.length;
  Plot pp=new Plot(a[index[0][0]],a[index[0][1]]);
  for(int i=1;i<n;i++) pp.addData(a[index[i][0]],a[index[i][1]]);
  return pp;
}

public static Plot plot(String filename, int pageno,String xy0,String xyn) 
{
double a[][]=read_double(filename,pageno,xy0,xyn);
Plot pp=new Plot(a);
return pp;
}

public static Plot plotT(String filename, int pageno,String xy0,String xyn) 
{
double a[][]=T(read_double(filename,pageno,xy0,xyn));
Plot pp=new Plot(a);
return pp;
}
public static Plot plotT(String filename, int pageno) 
{
double a[][]=T(read_double(filename,pageno));
Plot pp=new Plot(a);
return pp;
}
public static Plot plot(String filename, int pageno) 
{
double a[][]=read_double(filename,pageno);
Plot pp=new Plot(a);
return pp;
}
public static void update(String filename, int pageno,String xy0,double b[])
{double a[][]={b};
 update(filename,pageno,xy0,a);
}

public static void updateT(String filename, int pageno,String xy0,double c[])
{ double b[][]={c};
  double a[][]=T(b);
  update(filename,pageno,xy0,a);
}

public static void updateT(String filename, int pageno,String xy0,double b[][])
{double a[][]=T(b);
 update(filename,pageno,xy0,a);
}

public static void update(String filename, int pageno,String xy0,double a[][])
{   String s2=word(filename);
    Workbook workbook=new HSSFWorkbook();
    try {   
     FileInputStream inputStr= new FileInputStream(filename);
    if(s2.equals("xls"))
    {workbook=new HSSFWorkbook(inputStr);}
    else
    {workbook= new XSSFWorkbook(inputStr) ;} 
    } catch (Exception e) {System.out.println("File not found error");}
    int n0[]=excelcode(xy0);
    int nx=n0[0];
    int ny=n0[1];
    Sheet sheet= workbook.getSheetAt(pageno);
    int n=a.length;
    int m=a[0].length;
    for(int i=0;i<n;i++)
    { Row row=sheet.getRow(nx+i);
         for(int j=0;j<m;j++)
        {Cell cell=row.createCell((ny+j));
         String adress=new CellReference(cell).formatAsString();
         cell.setCellValue(a[i][j]);
       }         
    }
try {   
     FileOutputStream out=new FileOutputStream(filename);
     workbook.write(out);
     out.close();
    } catch (Exception e) {System.out.println("File not found , it might be opened by another application, please close it and retry");}
}

public static void main(String arg[])
{   //update(write) bessel functions into the excell file
	double a[][]=new double[5][200];
     for(int j=0;j<a[0].length;j++)
     { a[0][j]=0.1*j;
       for(int i=1;i<a.length;i++)
       {a[i][j]=Mathd.J(i-1,a[0][j]);}
     }
     update("DATA4.xlsx",0,"A1",T(a));
	 int index[][]={{0,1},{0,2},{0,2},{0,3},{1,2}};
	 //Plot the data from the excel file
     Plot pp=plotT("DATA4.xlsx",0,"A30","E100",index);
     //pp.setPlabel("J(i,x) as read from DATA4.xlsx page 0");
    pp.plot();
}

}
