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

}

TOP N-Analysis using JFreeChart

This post will narrate about plotting a Top-N analysis chart using JFreeChart.

Top-N analysis is used in many occasions like "No of defects pending" per tester or "outstanding components with more defects". When i tried to implement the same in JFreeChart i faced lot of issues, so that of sharing my learning to JFreeChart users. Here i have used bar chart with horizontal orientation. The percentage bar is made invisible from user view, by drawing the same in white color. I have also suppressed legends from view.

//set of headers to be included

import java.awt.Color;

import java.awt.Font;

import java.io.*;

import java.text.DecimalFormat;

import org.jfree.chart.JFreeChart;

import org.jfree.chart.ChartFactory;

import org.jfree.chart.ChartUtilities;

import org.jfree.chart.labels.CategoryItemLabelGenerator;

import org.jfree.chart.labels.StandardCategoryItemLabelGenerator;

import org.jfree.chart.plot.PlotOrientation;

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

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

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

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

import org.jfree.data.category.DefaultCategoryDataset;

import org.jfree.chart.axis.NumberAxis;

import org.jfree.chart.renderer.category.BarRenderer;

import org.jfree.ui.ApplicationFrame;

import java.io.File; 

import org.jfree.chart.plot.CategoryPlot;

import org.jfree.chart.axis.AxisLocation;

import org.jfree.chart.axis.CategoryAxis;

  

public class HorizonBarChart extends ApplicationFrame

{

         @SuppressWarnings("deprecation")                 

         //Constructor

         HorizonBarChart(final String title,String ipfilename,String Datasheetname,String opfilename)

         {

           super(title);                                             

           DefaultCategoryDataset dataset  = createDataset(ipfilename,Datasheetname);          

           JFreeChart chart = ChartFactory.createBarChart( title, 

                    "", 

                   "# of defect pending", 

                   dataset, 

                   PlotOrientation.HORIZONTAL,  //Sets chart orientation as Horizontal

                   false, 

                   false, 

                  false );                         

           final CategoryPlot plot = chart.getCategoryPlot();

         //Customization of chart

        plot.setRangeAxisLocation(AxisLocation.BOTTOM_OR_LEFT);

        plot.setBackgroundPaint(null);

        plot.setOutlineVisible(false);             

        plot.getRenderer().setSeriesPaint(0, new Color(0, 0, 255));   

        //This line ensure the other bar is not visible to user at all, since they are drawn  in white color
        plot.getRenderer().setSeriesPaint(1, new Color(255, 255, 255));  

  

       //Only numbers are displayed in range axis

        final NumberAxis rangeAxis = (NumberAxis)plot.getRangeAxis();     

        rangeAxis.setStandardTickUnits(NumberAxis.createIntegerTickUnits());         

        //sets font & margin on domain axis

        final CategoryAxis domainAxis = plot.getDomainAxis();     

        domainAxis.setCategoryMargin(0.2);      

        Font ft = new Font("SansSerif",Font.BOLD|Font.ROMAN_BASELINE, 12 ) ;

        domainAxis.setTickLabelFont(ft);

        domainAxis.setMaximumCategoryLabelWidthRatio(1);          

       

        BarRenderer renderer = ( BarRenderer)plot.getRenderer();

        renderer.setItemMargin(0.3);       

        renderer.setBaseItemLabelGenerator(new StandardCategoryItemLabelGenerator());

        renderer.setBaseItemLabelsVisible(true);

      

          // " % " is shown next to series value

          CategoryItemLabelGenerator categoryLabel = new StandardCategoryItemLabelGenerator("{2}",
          new DecimalFormat("0%"));     

          renderer.setSeriesItemLabelGenerator(1,categoryLabel);                               

          //series value are shown next to bars

          renderer.setSeriesItemLabelsVisible(0, true);

          renderer.setSeriesItemLabelsVisible(1, true);

                               

          Font rft = new Font("SansSerif",Font.BOLD|Font.ROMAN_BASELINE, 12 ) ;

          renderer.setSeriesItemLabelFont(0, rft);

          renderer.setSeriesItemLabelFont(1, rft);               

          //Chart generation       

           try {

              ChartUtilities.saveChartAsJPEG(new File(opfilename), chart,800, 700);      

               } catch (IOException e) {

              System.out.println("Problem in creating chart.");

              e.printStackTrace();

              }//End of try

           }                     

           //Main function 

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

               {   new VerticalBarChart("chart title","No such file","Data_2","No such file");  }

           private static DefaultCategoryDataset createDataset(String filename,String sheetname) {

                 DefaultCategoryDataset dataset1= new DefaultCategoryDataset();

                  Double x=0.0; 

                  String z=null;   

                  try

                               {                   

                                   FileInputStream fs =new FileInputStream(filename);        

                                    HSSFWorkbook wb = new HSSFWorkbook(fs);  

                                     HSSFSheet sheet = wb.getSheet(sheetname);      

                                     //The below section illustrates how to insert the values in dataset.

                                     //Please add your appropriate logic here.

                                     //This section is just to give an abstract view about dataset values

                                                 HSSFCell cell1 = sheet.getRow(r).getCell(col);

                                                 x=(double)cell1.getNumericCellValue();                                               

                                                 if(col==row.getLastCellNum()-1)

                                                     {

                                                          //To set the value in terms of percentage                                

                                                           x=x/100;  

                                                     }

                                                HSSFCell cell3 = sheet.getRow(r).getCell(row.getLastCellNum());

                                                 z=cell3.toString();                                               

                                                dataset1.addValue(x,"y-String to be constant",z);

                                        }

                  }//try       

                 catch(Exception e){

                                  e.printStackTrace();}

                 return dataset1;

              }

}

Input for this chart sample, is cited below.Happy Analysis!!