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 apachePOI7
{
public static void main(String arg[])
{ String header[]=new String[5];
  Cell cell = null; 
  Cell cell1 = null; 
  Workbook workbook=null;
  Sheet sheet=null;
      double a[]=new double[3];
    double r[]=new double[3];
    String s[]=new String[3];
    double n[]=new double[3];
        String name[]=new String[3];
    String r_ref,a_ref;
try {
    FileInputStream file = new FileInputStream(new File("C:\\okul\\sco1\\new.xls"));
    workbook = new HSSFWorkbook(file);
    sheet = workbook.getSheetAt(0);    
    for(int j=0;j<5;j++)
    {cell = sheet.getRow(0).getCell(j);
     header[j]=cell.getStringCellValue();
    }

    for(int i=0;i<3;i++)
      {
	  cell = sheet.getRow(i+1).getCell(3);
      r[i]=cell.getNumericCellValue();
      cell = sheet.getRow(i+1).getCell(2);
      a[i]=cell.getNumericCellValue();
      cell = sheet.getRow(i+1).getCell(1);
      name[i]=cell.getStringCellValue();
      cell = sheet.getRow(i+1).getCell(0);
      n[i]=cell.getNumericCellValue();
      r_ref=CellReference.convertNumToColString(3)+(i+2);
      a_ref=CellReference.convertNumToColString(2)+(i+2);     
      s[i]=a_ref+"*(1.0+"+r_ref+")";
      System.out.println(r_ref+" "+a_ref+"s="+s[i]);
      }     
    file.close();    
    FileOutputStream outFile =new FileOutputStream(new File("C:\\okul\\sco1\\new1.xls"));
    HSSFWorkbook workbook1 = new HSSFWorkbook();
    Sheet sheet1 = workbook1.createSheet();
    Row head = sheet1.createRow(0);
    for(int j=0;j<5;j++)
    {  System.out.println("j="+j+" "+header[j]);
	    head.createCell(j).setCellValue(header[j]);
	    cell1 = head.getCell(j);
	    System.out.println(cell1.getStringCellValue());
	}
     for(int i=0;i<3;i++)
     { Row r1=sheet.createRow(i+1);
	     r1.createCell(0).setCellValue(n[i]);
	     r1.createCell(1).setCellValue(name[i]);
	     r1.createCell(2).setCellValue(a[i]);
	     r1.createCell(3).setCellValue(r[i]);
	     r1.createCell(4).setCellFormula(s[i]);
      }
    workbook.write(outFile);
    outFile.close();
     
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}
}
}
