Read any Row in Excel - HSSF POI

This function helps to read any row in excel using HSSF POI. Instantiate this class in your main class and call ReadRow (int, int) function by providing row & column value as parameters.

Anatomy of ReadRow () function

·         Parameters – Two integers, Former denoting row & Later for column to be referenced

·         Return value – String Value of the corresponding cell

Here we go with implementation..

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

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

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

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

import java.io.FileInputStream;

import java.io.FileNotFoundException;

public class ExcelRead

{

                                public ExcelRead()           {}

       public String ReadRow(int i ,int j)    {

                FileInputStream  inputStream=null;

                String cellvalue=null;     

                try

                {              //Input File to be read    

                    inputStream = new FileInputStream ("Input.xls");

                }

                catch (FileNotFoundException e)

                {

                System.out.println ("File not found in the specified path.");

                e.printStackTrace ();

                }

               

                try

                {

                HSSFWorkbook workbook = new HSSFWorkbook(inputStream);

              // Get the first sheet on the workbook.

              HSSFSheet sheet = workbook.getSheetAt(0);

              HSSFRow row=sheet.getRow(i);       

             HSSFCell myCell = row.getCell(j);            

             cellvalue=myCell.toString();        

                }

                catch(Exception e)

                {

              System.out.println ("IO Exception");

                e.printStackTrace ();

                }

                                return cellvalue;        

    }

  

    public static void main(String[] args) throws Exception {                            

                ExcelRead ob=new ExcelRead();   

         String value=null;

                value=ob.ReadRow(1,1);

                System.out.println(op);

    }   

}

Importing Images to Excel

Wondering how to Insert/Import images from any location to Excel using POI- HSSF                        [Source: http://poi.apache.org/spreadsheet/index.html], here is one solution. With the assistance of this java file, image formats like .DIB, .EMF, .PNG, .PICT, .JPEG and .WMF can be  to any excel file at the specified cell and sheet. This feed will be of immense comfort for JFreeChart users, to import the generated charts back to excel.

XLInsertImage.java

//set of header files to be included

import java.io.FileInputStream;

import java.io.FileOutputStream;

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

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

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

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

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

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

import org.apache.poi.util.IOUtils;

public class XlInsertImage {

      

       void write(String filename,String sheetname,int row, int col){

             

        try

        {          

               HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("C:\\Output.xls"));  

                

                     FileInputStream fis=new FileInputStream(filename);

                     byte[] img_bytes=IOUtils.toByteArray(fis);

                    

                     //specify the format of picture to be imported

                     int index=wb.addPicture(img_bytes,HSSFWorkbook.PICTURE_TYPE_JPEG);

                     fis.close();

                      

            // This will embed the picture from (1,1) cell to (row,col) cell of   

            // excel sheet.     

 

                     CreationHelper helper = wb.getCreationHelper();

                     ClientAnchor anchor = helper.createClientAnchor();

                             

                     anchor.setCol1((short)col);       

                     anchor.setRow1((short)row);

                                 

                     //sepecify the sheet name to copy the picture

                     HSSFSheet sheet=wb.getSheet(sheetname);

                     HSSFPatriarch patriarch=sheet.createDrawingPatriarch();

                     HSSFPicture pict= patriarch.createPicture(anchor,index);     

                     pict.resize();

                    

                     //"Writing data to the xls file";

                     FileOutputStream fileOut = null;   

                     fileOut = new FileOutputStream("C:\\Output.xls");  

                     wb.write(fileOut);   

                     fileOut.close();

        }

        catch(Exception e)

        {

              e.printStackTrace();

        }

    } 

        public static void main(String args[])

              

            {

                new XlInsertImage();

            }

}

Syntax for calling XlInsertImage function in another class

public class MasterClass {

       public static void main(String args[]) throws Exception

   

    {

       

        

         XlInsertImage img= new XlInsertImage();      

        //Embeds image at row-15 col-0 in sheet name passed as argument

        img.write("C:\\chart.jpg",”sheetname”,15,0);

}