You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Daniel Noll <da...@nuix.com.au> on 2006/01/12 06:31:24 UTC

Image extraction

Hi.

I've been testing POI lately on Word, Excel and PowerPoint documents 
with the goal of extracting text from those document types, and I have 
to say that the scratchpad in the latest alpha is quite good for 
extracting text from even PowerPoint documents.

But, is it possible to use the API to extract embedded images from these 
documents as well?

Daniel

-- 
Daniel Noll

Nuix Australia Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia
Phone: (02) 9280 0699
Fax:   (02) 9212 6902

This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Re: Image extraction

Posted by Nick Burch <ni...@torchbox.com>.
On Wed, 18 Jan 2006, Daniel Noll wrote:
> Nick Burch wrote:
>> I've committed Yegor's code (cheers Yegor!). You should look at his comment 
>> on http://issues.apache.org/bugzilla/show_bug.cgi?id=38289 - it shows an 
>> example of how to extract the images
> That looks excellent.
>
> How long until this commit appears in the downloadable snapshots?  For 
> some reason, the Apache Subversion server doesn't work through our Squid 
> proxy, always gives an HTTP 400 error.

You should be able to find it in the latest one from
   http://encore.torchbox.com/poi-cvs-build/

> I've been digging through Excel to try and find where it hides the 
> image.  I can't see the binary as-is in any of the OLE streams.  The 
> only stream big enough to hide the image would be the "Workbook" itself, 
> and there aren't any records in the workbook large enough to be storing 
> a 4kB image.  The only conclusion I can come up with is that it's stored 
> right at the end and not inside a record of any sort.

Have you tried comparing the output from poifs.dev.POIFSViewer to 
hslf.dev.BiffViewer, so see if there is in fact extra data at the end?

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Re: Image extraction

Posted by Daniel Noll <da...@nuix.com.au>.
Nick Burch wrote:
> I've committed Yegor's code (cheers Yegor!). You should look at his 
> comment on http://issues.apache.org/bugzilla/show_bug.cgi?id=38289 - 
> it shows an example of how to extract the images
That looks excellent.

How long until this commit appears in the downloadable snapshots?  For 
some reason, the Apache Subversion server doesn't work through our Squid 
proxy, always gives an HTTP 400 error.

I've been digging through Excel to try and find where it hides the 
image.  I can't see the binary as-is in any of the OLE streams.  The 
only stream big enough to hide the image would be the "Workbook" itself, 
and there aren't any records in the workbook large enough to be storing 
a 4kB image.  The only conclusion I can come up with is that it's stored 
right at the end and not inside a record of any sort.

I will probably try Word today just to get a break from Excel.

Is nobody with experience in extracting images from Word and Excel 
watching the list? :-)

Daniel

-- 
Daniel Noll

Nuix Australia Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia
Phone: (02) 9280 0699
Fax:   (02) 9212 6902

This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Re: Image extraction

Posted by Nick Burch <ni...@torchbox.com>.
On Mon, 16 Jan 2006, Daniel Noll wrote:
> Bug created: http://issues.apache.org/bugzilla/show_bug.cgi?id=38283

I've committed Yegor's code (cheers Yegor!). You should look at his 
comment on http://issues.apache.org/bugzilla/show_bug.cgi?id=38289 - it 
shows an example of how to extract the images

> I had to set the component to "POI Overall" as there was no component 
> yet for HSLF.

Ah, the depths of Bugzilla.... Anyone know how I go about getting a HSLF 
component added?

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Re: Image extraction

Posted by Daniel Noll <da...@nuix.com.au>.
Nick Burch wrote:
> On Fri, 13 Jan 2006, Daniel Noll wrote:
>> Actually, how long do you think it would take?  We only really need 
>> to get at the binary data for the embedded image files (e.g. source 
>> PNGs), being able to render the vector drawings would be a bonus, but 
>> not necessarily a requirement at this point.
>
> I'm not sure, as I haven't looked that closely at how they're stored.
>
> If you fancied adding two files to bugzilla: a powerpoing document 
> containing a PNG (single page, just the PNG and some text), and the 
> original PNG, then I can look and see if powerpoint actually stores 
> the data as-is.
>
> If the PNG is stored in the PPT file as-is, I could probably knock up 
> something for you in an hour or so over the weekend. If it isn't 
> stored as-is, it'll take much longer (since I'd have to figure out how 
> to turn what's stored into something useful)
Bug created: http://issues.apache.org/bugzilla/show_bug.cgi?id=38283

I had to set the component to "POI Overall" as there was no component 
yet for HSLF.

In the meantime I'll probably be conducting my own tests to see how the 
same image is stored in Word and Excel, unless someone responsible for 
either of those two responds before I finish. ;-)

Daniel

-- 
Daniel Noll

Nuix Australia Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia
Phone: (02) 9280 0699
Fax:   (02) 9212 6902

This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Re: Image extraction

Posted by Nick Burch <ni...@torchbox.com>.
On Fri, 13 Jan 2006, Daniel Noll wrote:
> Actually, how long do you think it would take?  We only really need to 
> get at the binary data for the embedded image files (e.g. source PNGs), 
> being able to render the vector drawings would be a bonus, but not 
> necessarily a requirement at this point.

I'm not sure, as I haven't looked that closely at how they're stored.

If you fancied adding two files to bugzilla: a powerpoing document 
containing a PNG (single page, just the PNG and some text), and the 
original PNG, then I can look and see if powerpoint actually stores the 
data as-is.

If the PNG is stored in the PPT file as-is, I could probably knock up 
something for you in an hour or so over the weekend. If it isn't stored 
as-is, it'll take much longer (since I'd have to figure out how to turn 
what's stored into something useful)

I'll leave to someone else to answer for Word and Excel.

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Re: Image extraction

Posted by Daniel Noll <da...@nuix.com.au>.
Nick Burch wrote:
> On Thu, 12 Jan 2006, Daniel Noll wrote:
>> But, is it possible to use the API to extract embedded images from 
>> these documents as well?
>
> It's not currently possible to extract images from PowerPoint files. 
> It's on the todo list, but I probably won't get around to it for 
> another month or two (need to finish rich text first).
>
That sounds good, actually (although my personal bias would say images 
before rich text, but that's not the point, really.)

What about Word and Excel?  I've had a bit of a navigate around the HWPF 
and HSSL APIs but I can't easily see how to get to the images with those 
either.
> If you fancied adding the support, I'd be happy to give you some 
> pointers on where to start
>
:-)

Actually, how long do you think it would take?  We only really need to 
get at the binary data for the embedded image files (e.g. source PNGs), 
being able to render the vector drawings would be a bonus, but not 
necessarily a requirement at this point.

If I implemented text now and then waited for two months and implemented 
images, our own backwards compatibility requirements might add enough 
work to my plate as it is... if the work to rip out the images in all 
formats isn't "too much" work, it might be simpler to get it out of the 
way earlier and suffer less changes to our own code in the future.

Daniel


-- 
Daniel Noll

Nuix Australia Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia
Phone: (02) 9280 0699
Fax:   (02) 9212 6902

This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Re: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE

Posted by ichy <ic...@gmail.com>.
hi Nancy.

this may be your problem.
http://java2.5341.com/msg/106624.html

regards
ichy

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Re: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE

Posted by ichy <ic...@gmail.com>.
Hi Nancy.

well, this is a little code that i tested to get date value.

---------------------------------------------------------------------------
import java.io.FileInputStream;
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;

public class Main {

    public static void main( String[] args ) {
        String filename = "date-formula.xls";

        HSSFWorkbook hWorkbook;
        try {
            hWorkbook = new HSSFWorkbook( new FileInputStream( filename ) );
            HSSFSheet hSheet = hWorkbook.getSheetAt( 0 );
            HSSFRow hRow = hSheet.getRow( 0 );
            HSSFCell hCell = hRow.getCell( (short)0 );
            HSSFCell hCell2 = hRow.getCell( (short)1 );

            System.out.println("type of A1=" + getCellTypeString(
hCell.getCellType() ) );
            System.out.println( hCell.getNumericCellValue() );
            System.out.println( hCell.getDateCellValue() );

            System.out.println("type of B1=" + getCellTypeString(
hCell2.getCellType() ) );
            System.out.println( hCell2.getNumericCellValue() );
            System.out.println( hCell2.getDateCellValue() );
        } catch ( Exception e ) {
            e.printStackTrace();
        }
    }

    private static String getCellTypeString( int cellType ) {
        switch ( cellType ) {
            case HSSFCell.CELL_TYPE_BLANK :
                return "BLANK";
            case HSSFCell.CELL_TYPE_BOOLEAN :
                return "BOOLEAN";
            case HSSFCell.CELL_TYPE_ERROR :
                return "ERROR";
            case HSSFCell.CELL_TYPE_FORMULA :
                return "FORMULA";
            case HSSFCell.CELL_TYPE_NUMERIC :
                return "NUMERIC";
            case HSSFCell.CELL_TYPE_STRING :
                return "STRING";
            default :
                return "UNKNOWN";
        }
    }
}
---------------------------------------------------------------------------

if i run the code above with a excel file "date-formula.xls" which
has date "2006/1/20" on cell A1 and formula "A1+1" on cell B1,

i get a result as:

type of A1=NUMERIC
38737.0
Fri Jan 20 00:00:00 JST 2006
type of B1=FORMULA
38738.0
Sat Jan 21 00:00:00 JST 2006

so, if you know that a1 has a number, you can use getNumericCellValue()
and if you know that a1 has a date, you can use getDateCellValue().
but you may not be able to know which cell type a1 has by the formula
"A1+1".

and the number 38737 and 38738 are the way excel handles date values
if i remember correctly.

i hope this will help you a bit.

regards
ichy

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Re: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE

Posted by Nancy Espinoza <ne...@yahoo.com>.
Hi ichy 
  Well in fact I need to read a date  for example
  if in cell a1 is 17/07/1980 and the next cell I have a formula that is =a1+1
  who shows 18/07/1980 in the Excel I need to read this values
  Reading the first cell its easy I use getDateCellValue().toString()
  but when I try to read the another cell using getNumericCellValue() I didn´t obtain the value of the cell and when I use getCellFormula() I obtain the formula
  how can I read the value? please help me
  thanks
   
  regards,
  NANCY
   
      
   
ichy <ic...@gmail.com> escribió:
  hi Nancy.

did you try getNumericCellValue() to get the value of
the formula?

regards
ichy

2006/1/17, Nancy Espinoza :
> Hello people
>
> I have an archive in Excel who have formulas that shows values
> I need to save this values
> With POI I use the method getCellFormula()
> so it reads the formula but I need to save the value of the formula
> For example if the formula is sum(A1:A2) and the value in A1 and A2 is 1
> the result of the formula is 2
> I need to read the result 2 no sum(A1:A2)
> how can I do this?
> Please help me
> Thanks a lot
>
> Nancy
>
> __________________________________________________
> Correo Yahoo!
> Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
> Regístrate ya - http://correo.espanol.yahoo.com/
>

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/

  


__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis! 
Regístrate ya - http://correo.espanol.yahoo.com/ 

Re: HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE

Posted by ichy <ic...@gmail.com>.
hi Nancy.

did you try getNumericCellValue() to get the value of
the formula?

regards
ichy

2006/1/17, Nancy Espinoza <ne...@yahoo.com>:
> Hello people
>
>  I have an archive in Excel who have formulas that shows values
>  I need to save this values
>  With POI I use the method getCellFormula()
>  so it reads the formula but I need to save the value of the formula
>  For example if the formula is sum(A1:A2) and the value in A1 and A2 is 1
>  the result of the formula is 2
>  I need to read the result 2 no sum(A1:A2)
>  how can I do this?
>  Please help me
>  Thanks a lot
>
>  Nancy
>
> __________________________________________________
> Correo Yahoo!
> Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
> Regístrate ya - http://correo.espanol.yahoo.com/
>

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


HSSF POI READING FORMULAS IN AN EXCEL ARCHIVE

Posted by Nancy Espinoza <ne...@yahoo.com>.
Hello people
   
  I have an archive in Excel who have formulas that shows values
  I need to save this values 
  With POI I use the method getCellFormula()
  so it reads the formula but I need to save the value of the formula
  For example if the formula is sum(A1:A2) and the value in A1 and A2 is 1
  the result of the formula is 2 
  I need to read the result 2 no sum(A1:A2)
  how can I do this?
  Please help me
  Thanks a lot
   
  Nancy

__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis! 
Regístrate ya - http://correo.espanol.yahoo.com/ 

Re: Image extraction

Posted by Nick Burch <ni...@torchbox.com>.
On Thu, 12 Jan 2006, Daniel Noll wrote:
> But, is it possible to use the API to extract embedded images from these 
> documents as well?

It's not currently possible to extract images from PowerPoint files. It's 
on the todo list, but I probably won't get around to it for another month 
or two (need to finish rich text first).

If you fancied adding the support, I'd be happy to give you some pointers 
on where to start

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Re[2]: Image extraction

Posted by Yegor Kozlov <ye...@dinom.ru>.
Hi Nick,

"Pictures" OLE stream is a tightly packed sequence of HEADER + picture
data. Header is 25 bytes. JPEG and PNG are stored as-is. BMP and WMF
are compressed. New pictures are added to the end with no change to
the start.

For each picture there is a EscherBStoreContainer defined on the
document level. It stores checksum and offset to the picture in the
"Pictures" stream.

>From slide's point of view Picture is a shape. That is for each picture in a
slide there is a EscherSp container with information about position, borders,
etc. EscherSp refers to EscherBStoreContainer which knows about image
position in the "Pictures" stream.

Regards, Yegor

NB> On Mon, 16 Jan 2006, Yegor Kozlov wrote:
>> PowerPoint stores images in a separate OLE stream named "Pictures".

NB> Looking at the supplied file, I can confirm that

>> Extracting image data from it is not difficult, I will commit the patch 
>> in a couple of days.

NB> That would be great, thanks. For now, probably just a method on 
NB> HSLFSlideShow (like the DocumentSummaryInformation/SummaryInformation 
NB> ones) is probably all we need. That said, if you fancied adding the 
NB> records to make use of them, that'd be even better :)

NB> OOI, how does PPT know how many images are in the pictures block? It looks 
NB> to me (on adding a second PNG) that it just appends it in the block, with 
NB> no change to the start. Is it just an atom like structure, or something 
NB> fancier?

NB> Nick

NB> ---------------------------------------------------------------------
NB> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
NB> Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
NB> The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Re: Image extraction

Posted by Nick Burch <ni...@torchbox.com>.
On Mon, 16 Jan 2006, Yegor Kozlov wrote:
> PowerPoint stores images in a separate OLE stream named "Pictures".

Looking at the supplied file, I can confirm that

> Extracting image data from it is not difficult, I will commit the patch 
> in a couple of days.

That would be great, thanks. For now, probably just a method on 
HSLFSlideShow (like the DocumentSummaryInformation/SummaryInformation 
ones) is probably all we need. That said, if you fancied adding the 
records to make use of them, that'd be even better :)

OOI, how does PPT know how many images are in the pictures block? It looks 
to me (on adding a second PNG) that it just appends it in the block, with 
no change to the start. Is it just an atom like structure, or something 
fancier?

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Re: Image extraction

Posted by Yegor Kozlov <ye...@dinom.ru>.
Hi

I think I know how to extract images from a ppt file.
PowerPoint stores images in a separate OLE stream named "Pictures".
Extracting image data from it is not difficult, I will commit the
patch in a couple of days.

Regards,
Yegor

DN> Hi.

DN> I've been testing POI lately on Word, Excel and PowerPoint documents 
DN> with the goal of extracting text from those document types, and I have 
DN> to say that the scratchpad in the latest alpha is quite good for 
DN> extracting text from even PowerPoint documents.

DN> But, is it possible to use the API to extract embedded images from these 
DN> documents as well?

DN> Daniel


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/