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: [email protected]
For additional commands, e-mail: [email protected]
