You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by AlyssaK <al...@hotmail.com> on 2009/06/18 06:09:34 UTC
Parse Excel with Picture
Hello everyone,
I have a requirement to insert a picture within a cell in an excel workbook
and write a program to parse the contents of the cells and save it into the
database.
I have looked at the process of inserting a picture in a cell
(Insert->Picture->From File) and it doesn't seem to relate to one cell only,
i.e. B4 etc. Is this correct?
How do I parse this? If there is an example out there I would greatly
appreciate it.
Thanks
--
View this message in context: http://www.nabble.com/Parse-Excel-with-Picture-tp24086106p24086106.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Parse Excel with Picture
Posted by MSB <ma...@tiscali.co.uk>.
Do not take this as being the last word on the matter but I think it is safe
to assume that with Excel, you cannot insert an image into a cell; the best
you could do would be to limit the size of the image to a single cell.
You are correct in saying that images are related to workbooks and not cells
within POI and the code you have looks fine, furthermore, I cannot think why
you are not getting the extension from the call to suggestFileExtension().
The only thing I might suggest, if you have not already tried this, is to
test the type or format of the image rather than getting the extension.
Rather than casting the element you recover from the Iterator to
PictureData, you can test the actual type of the object you have, cast it
and then call other methods, something like this;
HSSFPictureData hssfPictData = null;
Object next = it.next();
if(next instanceof HSSFPictureData) {
hssfPictData = (HSSFPictureData)next;
switch(hssfPictData.getFormat()) {
case HSSFPictureData.MSOBI_JPEG:
// and so on. The other types can be found in the javadoc
}
}
If you are using an XSSFWorkbook then you can do something exactly similar
by casting to an XSSFPictureData instance and calling getFormat(). As with
the HSSF stream there are constants defined against which you can test the
type of the picture.
Now, to store an image into a database, there I have to admit to knowing
absolutely nothing. I have found an article from a source I trust that
demonstrates some code for storing an image into an Oracle or MySQL database
and the link to the page is;
http://www.java2s.com/Code/Java/Database-SQL-JDBC/InsertpicturetoMySQL.htm
Hope this helps a little.
Yours
Mark B
AlyssaK wrote:
>
> Thanks for your response!
>
> Yes I am asking how to use Excel to insert a picture into a cell and then
> POI to parse the spreadsheet file to store the information into a
> database.
>
> I have parsed most of the data that are strings and inserted them into the
> database, I wasn't sure how to get the picture or if there was in fact
> another way to insert the picture to confine it to a cell. I assumed it
> was the same process as parsing strings - getting the data from a specific
> cell. So from your response I understand that pictures are inserted in a
> worksheet and will float.
>
> From the link provided I have tried:
>
> List lst = workbook.getAllPictures();
> for (Iterator it = lst.iterator(); it.hasNext(); ) {
> PictureData pict = (PictureData)it.next();
> String ext = pict.suggestFileExtension();
> byte[] data = pict.getData();
> if (ext.equals("jpeg")){
> FileOutputStream out = new FileOutputStream("pict.jpg");
> out.write(data);
> out.close();
> }
> }
>
> I'm not sure why its complaining PictureData has no method suggestFile
> Extension. Once I solve this I assume the next step is to save the output
> stream into a blob? I've downloaded poi 3.2.
>
> I appreciate your help,
>
> Alyssa
>
>
>
>
>
> MSB wrote:
>>
>> Hello Alyssa,
>>
>> Sorry to say that I am not completely clear about what you are asking.
>> Are you saying that you want to use POI to insert a picture into a cell
>> and to parse the contents of an Excel spreadsheet file so that they can
>> be stored away into a database? Alternatively, are you asking how to use
>> Excel to insert a picture into a cell and then POI to parse the
>> spreadsheet file to store the information away into a database?
>>
>> As I am not too sure about the first part, the picture bit, that will
>> have to wait for your reply. Parsing the file however should be quite
>> straightforward but does again depend a little on exactly what you want
>> to do. Do you just need the contents of the cells as String(s)? If so
>> then the ExcelExtractor class is the one you want to use. Do you want to
>> preserve the data type of the cells contents and, perhaps more
>> improtantly, the formatting applied to it? Do you need to extract this
>> picture and store it to the database as, for example, a BLOB?
>>
>> The basic approach is to;
>>
>> Open an Workbook.
>> Find out how many Sheets there are in the Workbook.
>> Iterate through the Sheets and for each Sheet;
>> Get an Iterator for the Rows on the Sheet and for each Row;
>> Get an Iterator for the Cells on the Row and for each Cell;
>> Get the formatting applied to the Cell
>> Get the type of the cell and call the appropriate method to
>> recover the cells contents as a String, Number, etc
>>
>> For a general guide of how to use the API, the best place to start is the
>> Quick Guide;
>>
>> http://poi.apache.org/spreadsheet/quick-guide.html
>>
>> Or, if you can let me have a little more information about what you are
>> doing, I can point you in the direction of some code.
>>
>> Yours
>>
>> Mark B
>>
>> PS, I just found a contribution to another list from the Editor of PC Pro
>> Magazine with regard to pictures and Excel;
>>
>> "No version of Excel allows you to insert a picture into a cell. Pictures
>> are inserted into the worksheet and will always float.
>>
>> One of the properties of a picture can be set to "move and size with
>> cells" but that only moves or stretches the picture when the underlying
>> rows and columns are inserted, deleted or sized. It does not confine a
>> picture to a cell.
>>
>> You may be better off using Word rather than Excel where you can create a
>> table and insert pictures into the cells of the table.
>>
>> Simon Jones
>> Contributing Editor
>> PC Pro Magazine"
>>
>>
>> AlyssaK wrote:
>>>
>>> Hello everyone,
>>>
>>> I have a requirement to insert a picture within a cell in an excel
>>> workbook and write a program to parse the contents of the cells and save
>>> it into the database.
>>>
>>> I have looked at the process of inserting a picture in a cell
>>> (Insert->Picture->From File) and it doesn't seem to relate to one cell
>>> only, i.e. B4 etc. Is this correct?
>>>
>>> How do I parse this? If there is an example out there I would greatly
>>> appreciate it.
>>>
>>> Thanks
>>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Parse-Excel-with-Picture-tp24086106p24095346.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Parse Excel with Picture
Posted by AlyssaK <al...@hotmail.com>.
Thanks for your response!
Yes I am asking how to use Excel to insert a picture into a cell and then
POI to parse the spreadsheet file to store the information into a database.
I have parsed most of the data that are strings and inserted them into the
database, I wasn't sure how to get the picture or if there was in fact
another way to insert the picture to confine it to a cell. I assumed it was
the same process as parsing strings - getting the data from a specific cell.
So from your response I understand that pictures are inserted in a worksheet
and will float.
>From the link provided I have tried:
List lst = workbook.getAllPictures();
for (Iterator it = lst.iterator(); it.hasNext(); ) {
PictureData pict = (PictureData)it.next();
String ext = pict.suggestFileExtension();
byte[] data = pict.getData();
if (ext.equals("jpeg")){
FileOutputStream out = new FileOutputStream("pict.jpg");
out.write(data);
out.close();
}
}
I'm not sure why its complaining PictureData has no method suggestFile
Extension. Once I solve this I assume the next step is to save the output
stream into a blob? I've downloaded poi 3.2.
I appreciate your help,
Alyssa
MSB wrote:
>
> Hello Alyssa,
>
> Sorry to say that I am not completely clear about what you are asking. Are
> you saying that you want to use POI to insert a picture into a cell and to
> parse the contents of an Excel spreadsheet file so that they can be stored
> away into a database? Alternatively, are you asking how to use Excel to
> insert a picture into a cell and then POI to parse the spreadsheet file to
> store the information away into a database?
>
> As I am not too sure about the first part, the picture bit, that will have
> to wait for your reply. Parsing the file however should be quite
> straightforward but does again depend a little on exactly what you want to
> do. Do you just need the contents of the cells as String(s)? If so then
> the ExcelExtractor class is the one you want to use. Do you want to
> preserve the data type of the cells contents and, perhaps more
> improtantly, the formatting applied to it? Do you need to extract this
> picture and store it to the database as, for example, a BLOB?
>
> The basic approach is to;
>
> Open an Workbook.
> Find out how many Sheets there are in the Workbook.
> Iterate through the Sheets and for each Sheet;
> Get an Iterator for the Rows on the Sheet and for each Row;
> Get an Iterator for the Cells on the Row and for each Cell;
> Get the formatting applied to the Cell
> Get the type of the cell and call the appropriate method to
> recover the cells contents as a String, Number, etc
>
> For a general guide of how to use the API, the best place to start is the
> Quick Guide;
>
> http://poi.apache.org/spreadsheet/quick-guide.html
>
> Or, if you can let me have a little more information about what you are
> doing, I can point you in the direction of some code.
>
> Yours
>
> Mark B
>
> PS, I just found a contribution to another list from the Editor of PC Pro
> Magazine with regard to pictures and Excel;
>
> "No version of Excel allows you to insert a picture into a cell. Pictures
> are inserted into the worksheet and will always float.
>
> One of the properties of a picture can be set to "move and size with
> cells" but that only moves or stretches the picture when the underlying
> rows and columns are inserted, deleted or sized. It does not confine a
> picture to a cell.
>
> You may be better off using Word rather than Excel where you can create a
> table and insert pictures into the cells of the table.
>
> Simon Jones
> Contributing Editor
> PC Pro Magazine"
>
>
> AlyssaK wrote:
>>
>> Hello everyone,
>>
>> I have a requirement to insert a picture within a cell in an excel
>> workbook and write a program to parse the contents of the cells and save
>> it into the database.
>>
>> I have looked at the process of inserting a picture in a cell
>> (Insert->Picture->From File) and it doesn't seem to relate to one cell
>> only, i.e. B4 etc. Is this correct?
>>
>> How do I parse this? If there is an example out there I would greatly
>> appreciate it.
>>
>> Thanks
>>
>
>
--
View this message in context: http://www.nabble.com/Parse-Excel-with-Picture-tp24086106p24089517.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Parse Excel with Picture
Posted by MSB <ma...@tiscali.co.uk>.
Hello Alyssa,
Sorry to say that I am not completely clear about what you are asking. Are
you saying that you want to use POI to insert a picture into a cell and to
parse the contents of an Excel spreadsheet file so that they can be stored
away into a database? Alternatively, are you asking how to use Excel to
insert a picture into a cell and then POI to parse the spreadsheet file to
store the information away into a database?
As I am not too sure about the first part, the picture bit, that will have
to wait for your reply. Parsing the file however should be quite
straightforward but does again depend a little on exactly what you want to
do. Do you just need the contents of the cells as String(s)? If so then the
ExcelExtractor class is the one you want to use. Do you want to preserve the
data type of the cells contents and, perhaps more improtantly, the
formatting applied to it? Do you need to extract this picture and store it
to the database as, for example, a BLOB?
The basic approach is to;
Open an Workbook.
Find out how many Sheets there are in the Workbook.
Iterate through the Sheets and for each Sheet;
Get an Iterator for the Rows on the Sheet and for each Row;
Get an Iterator for the Cells on the Row and for each Cell;
Get the formatting applied to the Cell
Get the type of the cell and call the appropriate method to
recover the cells contents as a String, Number, etc
For a general guide of how to use the API, the best place to start is the
Quick Guide;
http://poi.apache.org/spreadsheet/quick-guide.html
Or, if you can let me have a little more information about what you are
doing, I can point you in the direction of some code.
Yours
Mark B
PS, I just found a contribution to another list from the Editor of PC Pro
Magazine with regard to pictures and Excel;
"No version of Excel allows you to insert a picture into a cell. Pictures
are inserted into the worksheet and will always float.
One of the properties of a picture can be set to "move and size with cells"
but that only moves or stretches the picture when the underlying rows and
columns are inserted, deleted or sized. It does not confine a picture to a
cell.
You may be better off using Word rather than Excel where you can create a
table and insert pictures into the cells of the table.
Simon Jones
Contributing Editor
PC Pro Magazine"
AlyssaK wrote:
>
> Hello everyone,
>
> I have a requirement to insert a picture within a cell in an excel
> workbook and write a program to parse the contents of the cells and save
> it into the database.
>
> I have looked at the process of inserting a picture in a cell
> (Insert->Picture->From File) and it doesn't seem to relate to one cell
> only, i.e. B4 etc. Is this correct?
>
> How do I parse this? If there is an example out there I would greatly
> appreciate it.
>
> Thanks
>
--
View this message in context: http://www.nabble.com/Parse-Excel-with-Picture-tp24086106p24087212.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org