17. READING/WRITING DATA FROM THE SPREADSHEET PROGRAMS

17.1 GENERAL DEFINITIONS

Spreadsheet programs such as Microsoft spreadsheet, OpenOffice calc, Lotus, StarOffice Calc are common calculating and data recording environments nowadays. In this chapter, accessing (reading and writing) to spreadshets will be examined. In order to do this a public access java-spreadsheet interphase apache poi  will be used. You can download free program apache poi from the internet adres http://poi.apache.org/After downloading, unzip the zip folder and open it. Under the lib directory, you will find jar files

copy jar files into /java home/jre/lib/ext folder. Now you are ready to access the spreadsheets.

17.2 READING DATA FROM SPREADHEETS

Poi has subsystems to communucate with all Microsoft Office products including excel files. can read an excel spreadsheet from a file stored on the local filesystem or from some input stream. The first step when reading a spreadsheet from a file or input stream is to create a Workbook. In modern excel, there are two different systems, xlsx and xls files. In order to open xlsx files workbook class XSSFWorkbook, for xls files HSSFWorkbook class is used. The code fragment below illustrates creating a workbook from a file on the local filesystem.

import org.apache.poi.xssf.usermodel.*;

import java.io.*;

import java.util.*;

…….

FileInputStream file = new FileInputStream("DATA1.xlsx");

XSSFWorkbook xssfWork = new XSSFWorkbook(file);

Or if the file is an xls file

import org.apache.poi.hssf.usermodel.*;

import java.io.*;

import java.util.*;

.....

FileInputStream file = new FileInputStream(new File("DATA1.xls"));    

HSSFWorkbook workbook = new HSSFWorkbook(file);

There is also a third way that can handle both xls and xlsx cases through an interphase class org.apache.poi.ss. By using this

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.usermodel.*;

import java.io.*;

import java.util.*;

….

FileInputStream file = new FileInputStream("DATA1.xls");

Workbook workbook = new HSSFWorkbook(file) ;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.usermodel.Cell;

import java.io.*;

import java.util.*;

.....

FileInputStream file = new FileInputStream(new File("DATA1.xlsx"));    

Workbook workbook = new XSSFWorkbook(file);

Once you have accessed the workbook, you can use this to Access to  the individual sheets. These are zero indexed - the first sheet being 0, the second sheet being 1, and so on

Sheet sheet = workbook.getSheetAt(0);

Once you have a sheet, you can then start accessing the cells.

Row r=sheet.getRow(i);

Cell c=r.getCell(j);

Once Cell object created content of the cell can be accessed by detecting cell type

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.getCellFormula() + "\t\t";}

You can retrieve the cell's contents as a string by using the convenience method getStringCellValue(), or as a numerical value by using getNumericCellValue(). If Cell formula is needed to be extract directly getCellFormula()  is available. In the following example codes below, data is extracted as String from the excell file.

PROGRAM 17.1 apachePOI3 program, an example of reading real data from spreadsheet

import java.util.*;

import javax.swing.*;

// reading from excel

public class apachePOI3

{

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;

  for(int i=0;i<nx;i++)

  {   Row r=sheet.getRow(i+nx0);

      if(i==nx0)

      {ny0= r.getFirstCellNum();

                  ny1= r.getLastCellNum();

                  ny=ny1-ny0;

       a=new String[nx][ny];

      }

                 for(int j=0;j<ny;j++)

      {  Cell c=r.getCell(j+ny0,Row.RETURN_BLANK_AS_NULL);

                    a[i][j] =toString(c);

      }

  }

}

  catch(ArrayIndexOutOfBoundsException e2) {System.err.println("wrong array dimension");}

  return a;

}

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=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 void main(String arg[])

{   

     String s[][]=read_String_from_excel("DATA1.xls",0);

     JTableP.print(s);

}

}

The next program is carrying out the same extraction but, it utilizes Iterator class for detecting the data.

PROGRAM 17.2 apachePOI2 program, an example of reading real data from spreadsheet

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 apachePOI2

{

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 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 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=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 void main(String arg[])

{   

     String s[][]=read_String_from_excel1("DATA1.xls",0);

     JTableP.print(s);

}}

In the next program exact starting point of the data is defined by the program

PROGRAM 17.3 apachePOI4 program, an example of reading real data from spreadsheet. Exact start and end is given as B2 to E5

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 apachePOI4

{

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;

}

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=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 void main(String arg[])

{   

     String s[][]=read_String_from_excel("DATA1.xls",1,"B2","D5");

     JTableP.print(s);

}

}

17.3 WRITING DATA INTO SPREADHEETS

This section describes how to write out simple spreadsheet data without any formatting information, such as fonts or decimal places. Similarly to reading a spreadsheet, the first step is to create a writable workbook using the factory method on the Workbook class.

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.*;...

…….,

Workbook workbook = new HSSFWorkbook();

Or

Workbook workbook = new SXSSFWorkbook() ;

And then Sheet and Row and Cell definitions are done similar to reading from excell file

Sheet sheet= workbook.createSheet(0);

…

Row row=sheet.createRow(i);

…

Cell cell=row.createCell(j);

Then cell value is set into the cell:

cell.setCellValue(cell_value);

After writing all the desired cells, the excell file is created and written through a FileOutputStream

try {  

     FileOutputStream out=new FileOutputStream(filename);

     workbook.write(out);

     out.close();

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

PROGRAM 17.4 an example of writing real data into spreadsheet

 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 apachePOI5

{

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 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 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 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 void main(String arg[])

{   

     String s[][]={{"0","1","2","3","4","5","6","7","8","9"},

                   {"10","11","12","13","14","15","16","17","18","19"},

                   {"20","21","22","23","24","25","26","27","28","29"}};

     write_to_excel("DATA2.xls",0,"A1",s);;

}

}

17.3 UPDATING THE SPREADSHEET

In order to update data first read data make the changes then close the communucation channel and reopened it as write channel to apply the changes. For example if the salaries of the following spreadsheet should be

PROGRAM 17.5 an updating example of data into spreadsheet

 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 apachePOI6

{

public static void main(String arg[])

{

try {

    FileInputStream file = new FileInputStream(new File("new.xls"));

    Workbook workbook = new HSSFWorkbook(file);

    Sheet sheet = workbook.getSheetAt(0);

    Cell cell = null;

    //Update the value of cell

    double a[]=new double[3];

    double r[]=new double[3];

    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();

      a[i]*=(1+r[i]);

      cell.setCellValue(a[i]);

      }    

    file.close();

    FileOutputStream outFile =new FileOutputStream(new File("new.xls"));

    workbook.write(outFile);

    outFile.close();

} catch (FileNotFoundException e) {

    e.printStackTrace();

} catch (IOException e) {

    e.printStackTrace();

}

}

}

After the updating the spreadsheet will look as:

17.4 ADDING AND USİNG FORMULAS

Spreadsheet formulas can be add and use easily. IN the following example, data read from new.xls and copied into new1.xls, forthermore a new column E is added and a new salary formula is integrated and calculated. The Input spreadsheet new.xls will look like

PROGRAM 17.6 reading and writing into spreadsheet (Formulas using to recalculate)

 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();

}

}

}

Output spreadsheet new1.xls :

17.5 ADDING FONT AND FORMAT INFORMATION INTO SPREADHEETS

The previous sections illustrates the fundamentals of generating an Spreadsheet compatible spreadsheet using the apache POI. However, as it stands Spreadsheet will render the data in the default font. In order to supply formatting information to Spreadsheet, we must make use of the overloaded constructor, which takes an additional object containing the cell's formatting information (both the font and the style).

The code  below illustrates creating a label cell for an arial 16 point font with GREEN color

PROGRAM 17.7  creating a label cell

import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.hssf.util.*;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ss.util.*;

public class apachePOI8 {

    public static void main(String[] args) {

        Workbook workbook = new HSSFWorkbook();

        Sheet firstSheet = workbook.createSheet("Sheet 1");

        // Write a String in Cell 2B

        Row row1 = firstSheet.createRow(1);

        Cell cell2B = row1.createCell(1);

        cell2B.setCellValue(new HSSFRichTextString("Sample String"));

        // Style Font in Cell 2B

        CellStyle cellStyle = workbook.createCellStyle();

        cellStyle = workbook.createCellStyle();

        Font hSSFFont = workbook.createFont();

        hSSFFont.setFontName(HSSFFont.FONT_ARIAL);

        hSSFFont.setFontHeightInPoints((short) 16);

        hSSFFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        hSSFFont.setColor(HSSFColor.GREEN.index);

        cellStyle.setFont(hSSFFont);

        cell2B.setCellStyle(cellStyle);

        FileOutputStream fileOutputStream = null;

        try {

            fileOutputStream = new FileOutputStream(new File("Test5.xls"));

            workbook.write(fileOutputStream);

        } catch (IOException e) {

            e.printStackTrace();

        } finally {

            if (fileOutputStream != null) {

                try {

                    fileOutputStream.flush();

                    fileOutputStream.close();

                } catch (IOException e) {

                    e.printStackTrace();

                }

            }

        }

    }

}

Number formatting information may be passed to the cell format object by a similar mechanism to that described for fonts.

PROGRAM 17.8  number formatting

import java.io.*;

import java.util.*;

import javax.swing.*;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.hssf.util.*;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ss.util.*;

public class apachePOI9 {

    public static void main(String[] args) {

Workbook wb = new HSSFWorkbook();

Sheet sheet = wb.createSheet("format sheet");

CellStyle style;

DataFormat format = wb.createDataFormat();

Row row;

Cell cell;

short rowNum = 0;

short colNum = 0;

row = sheet.createRow(rowNum++);

cell = row.createCell(colNum);

cell.setCellValue(11111.25);

style = wb.createCellStyle();

style.setDataFormat(format.getFormat("0.0"));

cell.setCellStyle(style);

row = sheet.createRow(rowNum++);

cell = row.createCell(colNum);

cell.setCellValue(11111.25);

style = wb.createCellStyle();

style.setDataFormat(format.getFormat("#,##0.0000"));

cell.setCellStyle(style);

try{

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

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

 catch (IOException e1) {e1.printStackTrace();}

}

}

A Picture can be added into the spreadsheet by using anchor statement

PROGRAM 17.9 apachePO10 program, an example of adding a picture into spreadsheet

 import java.io.*;

import java.util.*;

import javax.swing.*;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.hssf.util.*;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ss.util.*;

import org.apache.poi.util.IOUtils;

public class apachePOI10 {

    public static void main(String[] args) {

   Workbook wb = new HSSFWorkbook();

   Sheet sheet = wb.createSheet("My Sample Excel");

   InputStream inputStream=null;

   try{

   inputStream = new FileInputStream("fall.jpg");

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

   int pictureIdx=0;

 try{

    byte[] bytes = IOUtils.toByteArray(inputStream);

   pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);

   inputStream.close();

   }catch (IOException e1) {e1.printStackTrace();}

   CreationHelper helper = wb.getCreationHelper();

   Drawing drawing = sheet.createDrawingPatriarch();

   ClientAnchor anchor;

   anchor=new HSSFClientAnchor(0,0,0,255,(short)1,2,(short)7,10);

    anchor.setAnchorType(2);

    //= helper.createClientAnchor();

   //set top-left corner for the image

   anchor.setCol1(1);

   anchor.setRow1(2);

   Picture pict = drawing.createPicture(anchor, pictureIdx);

   pict.resize(0.1);

   try{

   FileOutputStream fileOut = new FileOutputStream("picture.xls");

   wb.write(fileOut);

   fileOut.close();

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

    catch (IOException e1) {e1.printStackTrace();}

}

}

EXERCISE 17 (WEEK 12)

EX1 ) Temperature and specific heat of CO₂  gas is given in file co2_cv.xls . Write an excell program to read the data and print it out to the screen

EX 2)  : Create table sinx with 2 column x and y. Enter x column values .  0.1 0.2 .03 0.4 0.5 , enter all y values as sin(x). Write the result into data6.xls spreadsheet program (excel)

EX3) : Add also Picture fall.png into data6.xls spreadsheet

HOMEWORKS  17 (WEEK  8)

HW 1 :  Create spreadsheet  student.xls with data fields name , surname , grade1, grade 2, result Input the following data

‘Ali’ ‘Çiçek’ 24 58

‘Veli’ ‘Durmuş’ 49 73

‘Hasan’ ‘Yücel’ 72 68

‘Mehmet’ ‘Demir’ 63 55

Write a java program to read the data from student.xls . Calculate result as 40% grade1 and 60% grade2 and write down into spreadsheet  result column the resulting data.

HW 2 :  read the data from co2_cv.xls  , Calculate avarage of temperature and Cv and write the results into co2_cv.xls.  

.