Apache – Get picture position in Apache POI from Excel xls HSSF

apacheapache-poiimage

My need is to get a picture data as bytes, then it's anchor details and finaly
display the result on screen at target position and size for my users…

The API shows we can get a list of all pictures through workbook object like this:

List<HSSFPictureData> picturesData = workbook.getAllPictures();

and also we are able to fetch all anchors details (see HSSFClientAnchor details here).

Problem is HSSFPicture.getPictureIndex() refered by an anchor does not match HSSFPictureData collection index of getAllPictures() method above…

…now the question is: how to map HSSFClientAnchor instances to specific HSSFPictureData instances or the opposite?

Best Answer

List<HSSFShape> shapes  = this.tSheet.sheet().getDrawingPatriarch().getChildren();
        for (int i = 0; i < shapes.size(); i++)
        {
            if(shapes.get(i) instanceof HSSFPicture)
            {   
                HSSFPicture pic = (HSSFPicture) shapes.get(i);
                HSSFPictureData picdata = this.tSheet.sheet().getWorkbook().getAllPictures().get(pic.getPictureIndex());
                int pictureIndex = this.newSheet.getWorkbook().addPicture( picdata.getData(), picdata.getFormat());

this.newSheet.createDrawingPatriarch().createPicture((HSSFClientAnchor)pic.getAnchor()r, pictureIndex);

            }


        }

NOTE: Above code will read pic data from one sheet to another sheet this.tSheet.sheet() is source sheet this.newSheet is new sheet

Related Solutions

Java – Removing a row from an Excel sheet with Apache POI HSSF

 /**
 * Remove a row by its index
 * @param sheet a Excel sheet
 * @param rowIndex a 0 based index of removing row
 */
public static void removeRow(HSSFSheet sheet, int rowIndex) {
    int lastRowNum=sheet.getLastRowNum();
    if(rowIndex>=0&&rowIndex<lastRowNum){
        sheet.shiftRows(rowIndex+1,lastRowNum, -1);
    }
    if(rowIndex==lastRowNum){
        HSSFRow removingRow=sheet.getRow(rowIndex);
        if(removingRow!=null){
            sheet.removeRow(removingRow);
        }
    }
}

Java – How to read Excel cell having Date with Apache POI

NOTE: HSSFDateUtil is deprecated

If you know which cell i.e. column position say 0 in each row is going to be a date, you can go for row.getCell(0).getDateCellValue() directly.
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html#getDateCellValue()

UPDATE: Here is an example - you can apply this in your switch case code above. I am checking and printing the Numeric as well as Date value. In this case the first column in my sheet has dates, hence I use row.getCell(0).

You can use the if (HSSFDateUtil.isCellDateFormatted .. code block directly in your switch case.

if (row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
    System.out.println ("Row No.: " + row.getRowNum ()+ " " +
        row.getCell(0).getNumericCellValue());

    if (HSSFDateUtil.isCellDateFormatted(row.getCell(0))) {
        System.out.println ("Row No.: " + row.getRowNum ()+ " " + 
            row.getCell(0).getDateCellValue());
    }
}

The output is

Row No.: 0 39281.0
Row No.: 0 Wed Jul 18 00:00:00 IST 2007
Row No.: 1 39491.0
Row No.: 1 Wed Feb 13 00:00:00 IST 2008
Row No.: 2 39311.0
Row No.: 2 Fri Aug 17 00:00:00 IST 2007

Best Answer

Related Solutions

Java – Removing a row from an Excel sheet with Apache POI HSSF

Java – How to read Excel cell having Date with Apache POI

Related Topic