import java.io.*;
import javax.swing.*;
import jxl.*; 
import jxl.write.*;
import jxl.format.*;
import java.io.File;
import java.util.Hashtable;
import java.util.Enumeration;
import javax.swing.filechooser.*;
import java.util.*;
import java.awt.*;
import java.applet.Applet;
import java.awt.event.*;
import java.text.*;
import java.util.Locale;
 
//import java.util.Locale;

public class elba_kalibrasyon_excel
{
   public static String toString(double left, int w, int d)
   // converts a double to a string with given width and decimals.
   {
	java.text.NumberFormat df=java.text.NumberFormat.getInstance(Locale.US);
	df.setMaximumFractionDigits(d);
    df.setMinimumFractionDigits(d);
    df.setGroupingUsed(false);
    String s = df.format(left);
    while (s.length() < w)
      s = " " + s;
    if (s.length() > w)
    {
        s = "";
        for (int i=0; i<w; i++)
          s = s + "-";
    }
    return s;
    } 

	
public static double[] verigir(String s)
     {
     String s1=JOptionPane.showInputDialog(s);        
     StringTokenizer token=new StringTokenizer(s1);
     int n=token.countTokens()-1;
     int m=n+1;
     double a[]=new double[m];
     int j=0;           
     while(token.hasMoreTokens())
     {
     Double ax=new Double(token.nextToken());
     a[j++]=ax.doubleValue();
     }
     return a; 
     }
//en kucuk kareler metodu	
  public static double[] pivotlugauss(double a[][],double b[])
  { //kısmi pivotlu gauss eleme yöntemi
  int n=b.length;	  
  double x[]=new double[n];
  double carpan=0;
  double toplam=0;
  double buyuk;
  double dummy=0;
  //gauss eleme
  int i,j,k,p,ii,jj;
  for(k=0;k<(n-1);k++)
  { //pivotlama
	p=k;
	buyuk=Math.abs(a[k][k]);
	for(ii=k+1;ii<n;ii++)
	{  dummy=Math.abs(a[ii][k]);
	   if(dummy > buyuk) {buyuk=dummy;p=ii;}
	}
	if(p!=k)
	{ for(jj=k;jj<n;jj++)
	  { dummy=a[p][jj];
	    a[p][jj]=a[k][jj];
	    a[k][jj]=dummy;
	  }
	  dummy=b[p];
	  b[p]=b[k];
	  b[k]=dummy;
	}
	//gauss elemeyi çözme  
    for(i=k+1;i<n;i++)
    {  carpan=a[i][k]/a[k][k];
       a[i][k]=0;
       for(j=k+1;j<n;j++)
       {   a[i][j]-=carpan*a[k][j]; }
           b[i]   =b[i]   -carpan*b[k];
	}
  }
  //geriye doğru yerine koyma
  x[n-1]=b[n-1]/a[n-1][n-1];
  for(i=n-2;i>=0;i--)
  {
    toplam=0;
    for(j=i+1;j<n;j++)
    {  toplam+=a[i][j]*x[j];}
    x[i]=(b[i]-toplam)/a[i][i];
  }
  return x;
}
  
public static double[] EKK(double xi[],double yi[],int n)
{
int l=xi.length;
int i,j,k;
int np1=n+1;
double A[][];
A=new double[np1][np1];
double B[];
B=new double[np1];
double X[];
X=new double[np1];
for(i=0;i<n+1;i++)
  {  for(j=0;j<n+1;j++)
     {if(i==0 && j==0) A[i][j]=l;
     else  for(k=0;k<l;k++) A[i][j] += Math.pow(xi[k],(i+j));
     }
     for(k=0;k<l;k++) { if(i==0)  B[i]+= yi[k];
                       else      B[i] += Math.pow(xi[k],i)*yi[k];}
  }
//System.out.println(Matrix.toString(A));
//System.out.println(Matrix.toStringT(B));  
X=pivotlugauss(A,B);
//X=B/A;
double max=0;
for(i=0;i<n+1;i++)
 if(Math.abs(X[i]) > max) max = Math.abs(X[i]);
for(i=0;i<n+1;i++)
 if((Math.abs(X[i]/max) > 0) && (Math.abs(X[i]/max) < 1.0e-100)) X[i]=0;
return X;
}

public static double fi(double Km,double n,double dT)
{return Km*Math.pow(dT,n);}
 		
public static void main(String arg[]) throws IOException
{
  double x[];
  double y[];
  double Sk;
  double np;
  double fp; 
  double fi;
  double lnKm,Km;
  String RaporNo;
  String ModelAdi;
  double ntoplam=0;
  jxl.write.WritableImage image;
  // KALİBRASYON EĞRİLERİ
  // giriş termometresi kalibrasyonu katsayıları
  double cg[]={4.673275527289139,
        0.757887078980871,
        0.005230752085567,
       -0.000051897165933,
        0.000000189552524};
  // çıkış termometresi kalibrasyon katsayları     
  double cc[]={ -5.394797610888966,
        1.349079320504222,
       -0.008092803140707,
        0.000082497039519,
       -0.000000321682776};
  // kütle kalibrasyonu katsayıları
  double cm[]={ -0.926269919575315,
        1.000398607930474,
       -0.000000010332328,
        0.000000000000131};              	
steam b;
b=new steam();
double P=1.2;
double Pref=101.3; //kPa
jxl.format.Colour acolor[];
  RaporNo=JOptionPane.showInputDialog("RaporNo (Rapor başlığı gir)");
  String s="üç deney basınç değerini aralarında (kPa) boşluk bırakarak giriniz: ";
  double [] P0=verigir(s);
  Sk=Double.parseDouble(JOptionPane.showInputDialog("Isıl güç ışınım faktörü Sk bknz TS EN 442-2/Mart 1998 ÇİZELGE 4"));
  np=Double.parseDouble(JOptionPane.showInputDialog("Basınç düzeltme np üssü bknz TS EN 442-2/Mart 1998 ÇİZELGE 4"));
  //Locale abd=new Locale("US");
  String s1="Hesaplanmamış Ham Değerler.xls";
  String sr1[]={"   ","Model adı:","Rapor No:","Rapor Tarihi",
  "Model No:"," "," ","Test Ölçüm Bilgileri",
  "Hava Basıncı","Referans Hava sıcaklığı",
  "Giriş suyu sıcaklığı","Çıkış suyu sıcaklığı",
  "Radyatör Sıcaklık Farkı","Giriş suyu entalpisi",
  "Çıkış suyu entalpisi","Entalpi farkı",
  "Radyatör ortalama su sıcaklığı", "Ortalama su ile ortam sıcaklığı farkı",
  "Debi","Ölçülen kapasite","Barometrik basınç ile düzeltilen kapasite","Km"," "};
  String sr2[]={"   ","    "," "," ",
  "Test Tarihi:"," "," "," ",
  "Air Pressure","Reference Air Temperature","Water inlet temperature",
  "Water outlet temperature",
  "Temperature Difference","Inlet Water Entalphy",
  "Exit Water Entalphy","Entalphy difference",
  "Mean water temperature", "Excess temperature",
  "Water flow rate","Thermal measured output","Corrected thermal output","n"," "};
  String sr3[]={"   ","    "," "," ",
  "S"," "," "," ",
  "P","t","t1",
  "t2",
  "t1-t2","h1",
  "h2",'\u0394'+"h",
  "tm", '\u0394'+"t",
  "qm",""+'\u03A6'+"me",""+'\u03A6'+"","Ölçüm sayısı"," "};
  String sr4[]={"   ","    "," "," ",
  "B"," "," "," ",
  "kPa",'\u0080'+"C",'\u0080'+"C",
  '\u0080'+"C",
  '\u0080'+"C","J/kg",
  "J/kg","J/kg",'\u0080'+"C",
  "K",
  "kg/s","W","W"," "," "," "};
  //s1=JOptionPane.showInputDialog("dosya adı ("Hesaplanmamış Ham Değerler.xls");
  JFileChooser fc=new JFileChooser();
  ExampleFileFilter filter = new ExampleFileFilter();
  filter.addExtension("xls");
  filter.setDescription("Excel dosyası açımı");
  fc.setFileFilter(filter);
  if (fc.showOpenDialog(null) == JFileChooser.APPROVE_OPTION) {File file = fc.getSelectedFile();s1=file.getName(); }
  File f=new File(s1);
  int snl=s1.length();
  ModelAdi=s1;
  StringBuffer sb1=new StringBuffer(s1);
  sb1.insert(snl-4,"_1");
  String s2=sb1.toString();
  File f1=new File(s2);
  
  //System.out.println(s2);
  int nos;
  try
  {
  Workbook workbook = Workbook.getWorkbook(f); 
  nos=workbook.getNumberOfSheets();
  double xi[]=new double[nos];
  double yi[]=new double[nos];
  String rn[]=new String[nos];
  
  //int sn=Integer.parseInt(JOptionPane.showInputDialog("excel sayfa numarası (0,1,2...)"));
  WritableWorkbook workbook1 = Workbook.createWorkbook(f1, workbook);  
  WritableSheet wreportsheet = workbook1.createSheet("Report Page", nos+3);  
  jxl.write.Label l1;
  jxl.write.Number number;
 try{
    WritableCellFormat wcf=new WritableCellFormat();
	wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THICK);
	wcf.setWrap(true);
	WritableCellFormat wcf1=new WritableCellFormat();
    wcf1.setBorder(jxl.format.Border.LEFT,jxl.format.BorderLineStyle.DOUBLE);

	
	for(int ii=1;ii<4;ii++)
    {
	l1=new jxl.write.Label(1,ii+3,sr1[ii]);
	//l1.setCellFormat(wcf);
    wreportsheet.addCell(l1);
    }
    
    for(int ii=4;ii<sr1.length;ii++)
    {
	l1=new jxl.write.Label(1,ii+3,sr1[ii]);
	l1.setCellFormat(wcf1);
	l1.setCellFormat(wcf);
    wreportsheet.addCell(l1);
    }
    
    for(int ii=4;ii<sr1.length;ii++)
    {
    l1=new jxl.write.Label(2,ii+3,sr2[ii]);
    l1.setCellFormat(wcf);
    wreportsheet.addCell(l1);
    }
    
    for(int ii=4;ii<sr1.length;ii++)
    {
    l1=new jxl.write.Label(3,ii+3,sr3[ii]);
    l1.setCellFormat(wcf);
    wreportsheet.addCell(l1);
    }
    
    for(int ii=4;ii<sr1.length;ii++)
    {
    l1=new jxl.write.Label(4,ii+3,sr4[ii]);
    l1.setCellFormat(wcf);
    wreportsheet.addCell(l1);
    }

	l1=new jxl.write.Label(5,4+3,"Ölçüm Noktaları");
	l1.setCellFormat(wcf);
    wreportsheet.addCell(l1);    
    }catch(jxl.write.WriteException we) {System.out.println("write exception");}
  int sn;
  Sheet sheet;
  WritableSheet wsheet;
  try{
  for(sn=0;sn<nos;sn++)
  {  
  sheet = workbook.getSheet(sn);
  rn[sn]=sheet.getName();
  //System.out.println("name="+rn[sn]);
  wsheet = workbook1.createSheet("K_"+rn[sn], sn+3); 
  Cell a1 = sheet.getCell(0,0); 
  String stringa1 = a1.getContents();
  int i=1; 
  int j=0;
  try
  {
  while(stringa1!="")
  {	  
  a1 = sheet.getCell(0,i);
  stringa1 = a1.getContents();
  //System.out.println(stringa1);
  i++; 	  
  }
  }catch(ArrayIndexOutOfBoundsException ai) {}
  int n=i-2;
  //System.out.println("n="+n);
  double a[][]=new double[8][n];
  acolor=new jxl.format.Colour[n];
  for(i=0;i<n;i++)
  {	  
  for(j=0;j<8;j++)
  {  
	  a1 = sheet.getCell(j,i+1);
      if (a1.getType() == CellType.NUMBER)
      {
      NumberCell nc = (NumberCell) a1;
      a[j][i] = nc.getValue();
      //CellFeatures features = nc.getCellFeatures();
      if(j==0)
      {
      jxl.format.CellFormat format1=a1.getCellFormat();
      acolor[i]=format1.getBackgroundColour();
      System.out.println("i="+i+"==="+acolor[i].getValue());
      }
      } 
  }
  }
double gs[]=new double[n];
double cs[]=new double[n];
double W[]=new double[n];
double m[]=new double[n]; 
for(i=0;i<n;i++)
{
double tg=a[1][i];
double tc=a[2][i];
double md=a[6][i]*60000.0;	
gs[i]=cg[0]+cg[1]*tg+cg[2]*tg*tg+cg[3]*tg*tg*tg+cg[4]*tg*tg*tg*tg;
cs[i]=cc[0]+cc[1]*tc+cc[2]*tc*tc+cc[3]*tc*tc*tc+cc[4]*tc*tc*tc*tc;
m[i]=(cm[0]+cm[1]*md+cm[2]*md*md+cm[3]*md*md*md)/60000.0;
double a2[]=b.property("tp",gs[i],P0[sn]*1e-2);
double a3[]=b.property("tp",cs[i],P0[sn]*1e-2);
double h1=a2[3];
double h2=a3[3];
//System.out.println(gs[i]+" "+h1+" "+cs[i]+" "+h2);
W[i]=m[i]*(h1-h2)*1000;
}
double bb[][]=new double[9][n];
for(i=0;i<n;i++)
{
bb[0][i]=a[1][i];
bb[1][i]=gs[i];
bb[2][i]=a[2][i];
bb[3][i]=cs[i];
bb[4][i]=a[3][i];
bb[5][i]=a[6][i];
bb[6][i]=m[i];
bb[7][i]=a[7][i];
bb[8][i]=W[i];
}	
//s1="Hesaplanmış Kalibre Değerler1.xls";
//s1=JOptionPane.showInputDialog("çıktı dosya adı(Hesaplanmış Kalibre Değerler1.xls",s1);

String s3[]={"D.G.sıcaklığı","K.G.sıcaklığı","D.Ç.Sıcaklığı","K.Ç.Sıcaklığı","Ortam sıcaklığı","D. Debi","K. Debi","D. Watt","K. Watt"};	
jxl.write.Label l;

for(i=0;i<9;i++)
{l=new jxl.write.Label(i,0+3,s3[i]);
 wsheet.addCell(l);
}
  //jxl.write.Number number;  
  double ortalama[]=new double[9];
  double toplam[]=new double[9];
  int n1[]=new int[9];  
  for(i=0;i<n;i++)
  {	  
  for(j=0;j<9;j++)
  {  	  
	  WritableCellFormat wcf=new WritableCellFormat();
	  System.out.println("i="+i+"***"+acolor[i].getValue());
	  wcf.setBackground(acolor[i]);
	  //jxl.format.Border bdr=new jxl.format.Border("all"); 
	  wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THICK);
	  if(acolor[i].getValue()==11) 
	  {  toplam[j]+=bb[j][i];
	     n1[j]++;
      }   
	  number = new jxl.write.Number(j,i+4,bb[j][i]);
	  number.setCellFormat(wcf);
      wsheet.addCell(number);     
  }
  }
  ntoplam+=n1[0];
  for(j=0;j<9;j++)
  {  ortalama[j]=toplam[j]/n1[j];
     number = new jxl.write.Number(j,n+5,ortalama[j]);
     WritableCellFormat wcf=new WritableCellFormat();
	 wcf.setBackground(jxl.format.Colour.LIGHT_BLUE); 
     wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THICK);
	 number.setCellFormat(wcf);
     wsheet.addCell(number);   
  }
  WritableCellFormat wcf=new WritableCellFormat();
  wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THICK);
	//Hava Basıncı
    l1=new jxl.write.Label((5+sn),8+3,toString(P0[sn],5,1));
	l1.setCellFormat(wcf);
    wreportsheet.addCell(l1);  
    //Referans hava sıaklığı
    l1=new jxl.write.Label((5+sn),9+3,toString(ortalama[4],4,1));
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
	//Giriş suyu sıcaklığı
    l1=new jxl.write.Label((5+sn),10+3,toString(ortalama[1],4,1));
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
	//Çıkış suyu sıcaklığı
    l1=new jxl.write.Label((5+sn),11+3,toString(ortalama[3],4,1));
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
    //Radyatör sıcaklık farkı
    l1=new jxl.write.Label((5+sn),12+3,toString((ortalama[1]-ortalama[3]),4,1));
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
    //Giriş suyu entalpisi
    double a4[]=b.property("tp",ortalama[1],P0[sn]*1e-2);
    double h4=a4[3]*1e3;    
    l1=new jxl.write.Label((5+sn),13+3,toString(h4,6,0));
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
    //Çıkış suyu entalpisi
    double a5[]=b.property("tp",ortalama[3],P0[sn]*1e-2);
    double h5=a5[3]*1e3;    
    l1=new jxl.write.Label((5+sn),14+3,toString(h5,6,0));
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
    //entalpi farkı
    double h6=h4-h5;    
    l1=new jxl.write.Label((5+sn),15+3,toString(h6,6,0));
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
    //Radyatör ortalama su sıcaklığı
    double to=(ortalama[1]+ortalama[3])/2.0;
    l1=new jxl.write.Label((5+sn),16+3,toString(to,4,1));
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
    //ortalama su ile ortam sıcaklığı farkı
    xi[sn]=to-ortalama[4];
    l1=new jxl.write.Label((5+sn),17+3,toString(xi[sn],4,1));
    xi[sn]=Math.log(xi[sn]);
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
    //Debi
    number=new jxl.write.Number((5+sn),18+3,ortalama[6]);
	number.setCellFormat(wcf);
	wreportsheet.addCell(number); 
    // ölçülen kapasite
    l1=new jxl.write.Label((5+sn),19+3,toString(ortalama[8],5,0));
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
    // barometrik basınç ile düzeltilmiş kapasite
    fp=Math.pow((P0[sn]/Pref),np);
    fi=ortalama[8]*(Sk+(1.0-Sk)*fp);
    yi[sn]=Math.log(fi);
    l1=new jxl.write.Label((5+sn),20+3,toString(fi,4,0));
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
Text.print(Text.T(bb),s3,"Kalibre edilmiş değerler "+rn[sn]);	
}
double d[]=EKK(xi,yi,1);
Km=Math.exp(d[0]);
double n=d[1];
double fistn[]=new double[3];
fistn[0]=fi(Km,n,60.0);
fistn[1]=fi(Km,n,50.0);
fistn[2]=fi(Km,n,30.0);
File ff=new File("ELBAlogo.png");
image=new jxl.write.WritableImage(6,0,2,5,ff);
  WritableCellFormat wcf=new WritableCellFormat();
  wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THICK);
  WritableCellFormat wcf1=new WritableCellFormat();
  wcf1.setBorder(jxl.format.Border.LEFT,jxl.format.BorderLineStyle.DOUBLE);
wreportsheet.addImage(image);
wreportsheet.setColumnView(1,20);
wreportsheet.setColumnView(2,20);
	
    l1=new jxl.write.Label(5,5+3,rn[0]);
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1);
	
    l1=new jxl.write.Label(6,5+3,rn[1]);
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1);
	
    l1=new jxl.write.Label(7,5+3,rn[2]);
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1);
	
	//Km
	number=new jxl.write.Number(1,22+3,Km);
    number.setCellFormat(wcf);
	wreportsheet.addCell(number); 
	//n
	number=new jxl.write.Number(2,22+3,n);
    number.setCellFormat(wcf);
	wreportsheet.addCell(number); 
	//ölçüm sayısı
    number=new jxl.write.Number(3,22+3,ntoplam);
    number.setCellFormat(wcf);
	wreportsheet.addCell(number); 
	//dt60 dt50 dt30
	l1=new jxl.write.Label(5,22+3,'\u0394'+"t 60");
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1);
	l1=new jxl.write.Label(6,22+3,'\u0394'+"t 50");
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1);
	l1=new jxl.write.Label(7,22+3,'\u0394'+"t 30");
	l1.setCellFormat(wcf);
	wreportsheet.addCell(l1);
	
	l1=new jxl.write.Label(5,23+3,toString(fistn[0],5,0));
    l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
	l1=new jxl.write.Label(6,23+3,toString(fistn[1],5,0));
    l1.setCellFormat(wcf);
	wreportsheet.addCell(l1); 
	l1=new jxl.write.Label(7,23+3,toString(fistn[2],5,0));
    l1.setCellFormat(wcf);
	wreportsheet.addCell(l1);
	
	l1=new jxl.write.Label(2,4,ModelAdi);
	//l1.setCellFormat(wcf);
	wreportsheet.addCell(l1);
	
    l1=new jxl.write.Label(2,5,RaporNo);
	//l1.setCellFormat(wcf);
	wreportsheet.addCell(l1);
	
	jxl.write.WritableFont times18font = new jxl.write.WritableFont(WritableFont.TIMES, 18); 
    jxl.write.WritableCellFormat times18format = new jxl.write.WritableCellFormat(times18font); 
    //WritableCellFormat wcf1=new WritableCellFormat(times18format);
    
    l1=new jxl.write.Label(5,21+3,"       "+'\u03A6'+"L(W)");
	l1.setCellFormat(wcf1);
	wreportsheet.addCell(l1);
	
	Date now = Calendar.getInstance().getTime(); 
    jxl.write.DateFormat customDateFormat = new jxl.write.DateFormat ("dd MMM yyyy"); 
    WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat); 
    DateTime dateCell = new DateTime(2, 6, now, dateFormat); 
    //dateFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
    dateCell.setCellFormat(dateFormat);
    wreportsheet.addCell(dateCell); 


workbook1.write();
workbook1.close(); 
}catch(jxl.write.WriteException we) {System.out.println("write exception");}
}catch(jxl.read.biff.BiffException be) {System.err.println("BiffException");}  
}
}