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/