Wednesday, 31 July 2013

Convert Excel sheet to Images


Aspose provides us java liberary (called Aspose cells) to work with Microsoft excel document to insert content, retrieve content, export worksheet into images. So lets talk about to convert .xls adn .xlsx file to images(png, jpeg format).

Here is the link from where we can download latest Aspose cells jar.
http://www.aspose.com/java/excel-component.aspx
click on download button.

Generate images of .xls/.xlsx file


 public void generateImages(final String sourcePath) {  
     try {  
         Workbook workbook = new Workbook(sourcePath);  
         List<Worksheet> worksheets = getAllWorksheets(workbook);  
         if (worksheets != null) {  
             int noOfImages = 0;  
             for (Worksheet worksheet : worksheets) {  
                 if (worksheet.getCells().getCount() > 0 || worksheet.getCharts().getCount() > 0 || worksheet.getPictures().getCount() > 0) {  
                     String imageFilePath = sourcePath + "_output_" + (noOfImages++) + ".jpeg";   
                     SheetRender sr = new SheetRender(worksheet, getImageOrPrintOptions());  
                     sr.toImage(0, imageFilePath);  
                 }  
             }  
         }  
     } catch (Exception e) {  
         e.printStackTrace();  
     }  
 }  
 /**  
  * Returns all worksheets present in given workbook.  
  *   
  * @param workbook  
  * @return all worksheets present in given workbook.  
  */  
 private List<Worksheet> getAllWorksheets(final Workbook workbook) {  
     List<Worksheet> worksheets = new ArrayList<Worksheet>();  
     WorksheetCollection worksheetCollection = workbook.getWorksheets();  
     for (int i = 0; i < worksheetCollection.getCount(); i++) {  
         worksheets.add(worksheetCollection.get(i));  
     }  
     return worksheets;  
 }  
 /**  
  * Returns ImageOrPrintOptions for png images  
  *   
  * @return  
  */  
 private ImageOrPrintOptions getImageOrPrintOptions() {  
     ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();  
     imgOptions.setImageFormat(ImageFormat.getJpeg());  
     imgOptions.setOnePagePerSheet(true);  
     return imgOptions;  
 }  



Generate images of all charts present in .xls/.xlsx file

 public void generateChartsAsImages(final String sourcePath) {  
     try {  
         Workbook workbook = new Workbook(sourcePath);;  
         List<Chart> charts = getAllCharts(workbook);  
         if (charts != null) {  
             int noOfImages = 0;  
             for (Chart chart : charts) {  
                 String imageFilePath = sourcePath + "_output_" + (noOfImages++) + ".jpeg";   
                 chart.toImage(imageFilePath, getImageOrPrintOptions());  
             }  
         }  
     } catch (Exception e) {  
         e.printStackTrace();  
     }  
 }  
 /**  
  * Returns all charts present in given workbook.  
  *   
  * @param workbook  
  * @return all charts present in given workbook.  
  */  
 private List<Chart> getAllCharts(final Workbook workbook) {  
     List<Chart> charts = new ArrayList<Chart>();  
     List<Worksheet> worksheets = getAllWorksheets(workbook);  
     for (Worksheet worksheet : worksheets) {  
         charts.addAll(getAllCharts(worksheet));  
     }  
     return charts;  
 }  
 /**  
  * Returns all charts present in given worksheet.  
  *   
  * @param worksheet  
  * @return all charts present in given worksheet.  
  */  
 private List<Chart> getAllCharts(final Worksheet worksheet) {  
     List<Chart> charts = new ArrayList<Chart>();  
     ChartCollection chartCollection = worksheet.getCharts();  
     for (int i = 0; i < chartCollection.getCount(); i++) {  
         charts.add(chartCollection.get(i));  
     }  
     return charts;  
 }  


Generate images of all pictures present in .xls/.xlsx file


 public void generatePicturesAsImages(final String sourcePath) {  
     try {  
         Workbook workbook = new Workbook(sourcePath);;  
         List<Picture> pictures = getAllPictures(workbook);  
         if (pictures != null) {  
             int noOfImages = 0;  
             for (Picture picture : pictures) {  
                 String imageFilePath = sourcePath + "_output_" + (noOfImages++) + ".jpeg";   
                 picture.toImage(imageFilePath, getImageOrPrintOptions());  
                 filePaths.add(imageFilePath);  
             }  
         }  
     } catch (Exception e) {  
         e.printStackTrace();  
     }  
 }  
 /**  
  * Returns all pictures present in given workbook.  
  *   
  * @param workbook  
  * @return all pictures present in given workbook.  
  */  
 private List<Picture> getAllPictures(final Workbook workbook) {  
     List<Picture> pictures = new ArrayList<Picture>();  
     List<Worksheet> worksheets = getAllWorksheets(workbook);  
     for (Worksheet worksheet : worksheets) {  
         pictures.addAll(getAllPictures(worksheet));  
     }  
     return pictures;  
 }  
 /**  
  * Returns all pictures present in given worksheet.  
  *   
  * @param worksheet  
  * @return all pictures present in given worksheet.  
  */  
 private List<Picture> getAllPictures(final Worksheet worksheet) {  
     List<Picture> pictures = new ArrayList<Picture>();  
     PictureCollection pictureCollection = worksheet.getPictures();  
     for (int i = 0; i < pictureCollection.getCount(); i++) {  
         pictures.add(pictureCollection.get(i));  
     }  
     return pictures;  
 }  


Generate images of all tables present in .xls/.xlsx file

 public void generateTablesAsImages(final String sourcePath) {  
     try {  
         Workbook workbook = new Workbook(sourcePath);;  
         Map<Worksheet, List<ListObject>> tablesByWokeSheet = getAllTables(workbook);  
         int noOfImages = 0;  
         for (Entry<Worksheet, List<ListObject>> entry : tablesByWokeSheet.entrySet()) {  
             for (ListObject table : entry.getValue()) {  
                 String imageFilePath = sourcePath + "_output_" + (noOfImages++) + ".jpeg";   
                 tableToImage(entry.getKey(), table, imageFilePath);  
             }  
         }  
     } catch (Exception e) {  
         e.printStackTrace();  
     }  
 }  
 /**  
  * Returns all tables present in given workbook.  
  *   
  * @param workbook  
  * @return all tables present in given workbook.  
  */  
 private Map<Worksheet, List<ListObject>> getAllTables(final Workbook workbook) {  
     Map<Worksheet, List<ListObject>> tables = new HashMap<Worksheet, List<ListObject>>();  
     List<Worksheet> worksheets = getAllWorksheets(workbook);  
     for (Worksheet worksheet : worksheets) {  
         tables.put(worksheet, getAllTables(worksheet));  
     }  
     return tables;  
 }  
 /**  
  * Returns all tables present in given worksheet.  
  *   
  * @param worksheet  
  * @return all tables present in given worksheet.  
  */  
 private List<ListObject> getAllTables(final Worksheet worksheet) {  
     List<ListObject> tables = new ArrayList<ListObject>();  
     ListObjectCollection listObjectCollection = worksheet.getListObjects();  
     for (int i = 0; i < listObjectCollection.getCount(); i++) {  
         tables.add(listObjectCollection.get(i));  
     }  
     return tables;  
 }  
 /**  
  * Convert excel entity table to image.  
  *   
  * @param worksheet  
  * @param table  
  * @param outPutFilePath  
  * @throws FileNotFoundException  
  * @throws Exception  
  */  
 private void tableToImage(final Worksheet worksheet, final ListObject table, final String outPutFilePath) throws FileNotFoundException, Exception {  
     Workbook workbook = createWorkBook(worksheet, table);  
     Worksheet sheet = workbook.getWorksheets().get(0);  
     ImageOrPrintOptions imageOrPrintOptions = getImageOrPrintOptions();  
     imageOrPrintOptions.setOnlyArea(true);  
     SheetRender sr = new SheetRender(sheet, imageOrPrintOptions);  
     sr.toImage(0, outPutFilePath);  
 }  
 /**  
  * Returns a new workbook having only single worksheet with a table representing the same content as present in the given table.  
  *   
  * @param worksheet  
  *      representing the excel file having actual content.  
  * @param listObject  
  *      worksheet table whose content has to copied in new workbook  
  * @return newly created workbook containing only table content.  
  * @throws Exception  
  */  
 private Workbook createWorkBook(final Worksheet worksheet, final ListObject listObject) throws Exception {  
     Workbook workbook = new Workbook();  
     // As this workbook is blank workbook so take cells of first worksheet of this workbook  
     Cells cells = workbook.getWorksheets().get(0).getCells();  
     Cells cellsTobeCopied = worksheet.getCells();  
     int totalNoOfRows = listObject.getEndRow() - listObject.getStartRow() + 1;  
     int totalNoOfColumns = listObject.getEndColumn() - listObject.getStartColumn() + 1;  
     cells.setStandardHeight(cellsTobeCopied.getStandardHeight());  
     cells.setStandardWidth(cellsTobeCopied.getStandardWidth());  
     // Set height of each row as the height of actual rows of table  
     for (int row = 0; row < totalNoOfRows; row++) {  
         cells.setRowHeight(row, cellsTobeCopied.getRowHeight(row));  
     }  
     // Set width of each column as the width of actual columns of table  
     for (int column = 0; column < totalNoOfColumns; column++) {  
         cells.setColumnWidth(column, cellsTobeCopied.getColumnWidth(column));  
     }  
     // Copy data of table from worksheet to newly created workbook cells  
     for (int row = 0; row < totalNoOfRows; row++) {  
         for (int column = 0; column < totalNoOfColumns; column++) {  
             Cell copiedFrom = worksheet.getCells().get(listObject.getStartRow() + row, listObject.getStartColumn() + column);  
             Cell copyTo = cells.get(row, column);  
             copyTo.setHtmlString(copiedFrom.getHtmlString());  
         }  
     }  
     // Create table in newly created workbook  
     ListObjectCollection tables = workbook.getWorksheets().get(0).getListObjects();  
     tables.add(0, 0, totalNoOfRows - 1, totalNoOfColumns - 1, listObject.getShowHeaderRow());  
     return workbook;  
 }  


Help4J - Jar Search Engine. Easiest way to find jar and its source.

profile for Ashish Aggarwal on Stack Exchange, a network of free, community-driven Q&A sites

2 comments:

  1. Ashish & Aspose Rocks, Thanks for Sharing ... Loved it

    ReplyDelete
  2. This is nice. I will definitely try using java as u have written here. Mostly i use the Universal Document Converter from http://www.print-driver.com to convert Excel to JPG

    ReplyDelete