Mark, thank you very much for the help. I tried this code and it works pretty
good. I can get a list of column and row number. But then I noticed it couldn't
retrieve all the images:
I tried to get all images from a single sheet xls file by flowing code:
InputStream myxls = new
FileInputStream(filename);
HSSFWorkbook wb = new HSSFWorkbook(myxls);
List list = wb.getAllPictures();
I can get a list of Pictures as total as 65 element; but when I tried to get
their position data by the code you provided, it only return 55 positions. I
double checked spreadsheet and confirm there are 65 Pictures in that sheet. I
guess something missed over there. I might be confused by those Children,
etc....Any suggestion is appreciated. Thanks again for the help.
--Jerry
>>> MSB <[email protected]> 6/8/2010 9:33 AM >>>
I should have known that would be too easy!
This morning, I managed to write the code to recover the anchor information
for images inserted into one of the older, binary, Excel workbooks;
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package imagematrices;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.FileNotFoundException;
import java.io.FilenameFilter;
import java.util.Iterator;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.record.EscherAggregate;
import org.apache.poi.ddf.EscherRecord;
import org.apache.poi.ddf.EscherClientAnchorRecord;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
/**
*
* @author win user
*/
public class Main {
private ArrayList<File> excelFiles = null;
public void getImageMatrices(String folderName)
throws IOException, FileNotFoundException,
InvalidFormatException {
File fileFolder = new File(folderName);
File[] excelWorkbooks = fileFolder.listFiles(new
ExcelFilenameFilter());
for(File excelWorkbook : excelWorkbooks) {
Workbook workbook = WorkbookFactory.create(new
FileInputStream(excelWorkbook));
if(workbook instanceof HSSFWorkbook) {
this.processImages((HSSFWorkbook)workbook);
}
else {
this.processImages((XSSFWorkbook)workbook);
}
}
}
private void processImages(HSSFWorkbook workbook) {
EscherAggregate drawingAggregate = null;
HSSFSheet sheet = null;
List<EscherRecord> recordList = null;
Iterator<EscherRecord> recordIter = null;
int numSheets = workbook.getNumberOfSheets();
for(int i = 0; i < numSheets; i++) {
System.out.println("Processing sheet number: " + (i + 1));
sheet = workbook.getSheetAt(i);
drawingAggregate = sheet.getDrawingEscherAggregate();
if(drawingAggregate != null) {
recordList = drawingAggregate.getEscherRecords();
recordIter = recordList.iterator();
while(recordIter.hasNext()) {
this.iterateRecords(recordIter.next(), 1);
}
}
}
}
private void iterateRecords(EscherRecord escherRecord, int level) {
List<EscherRecord> recordList = null;
Iterator<EscherRecord> recordIter = null;
EscherRecord childRecord = null;
recordList = escherRecord.getChildRecords();
recordIter = recordList.iterator();
while(recordIter.hasNext()) {
childRecord = recordIter.next();
if(childRecord instanceof EscherClientAnchorRecord) {
this.printAnchorDetails((EscherClientAnchorRecord)childRecord);
}
if(childRecord.getChildRecords().size() > 0) {
this.iterateRecords(childRecord, ++level);
}
}
}
private void printAnchorDetails(EscherClientAnchorRecord anchorRecord) {
System.out.println("The top left hand corner of the image can be
found " +
"in the cell at column number " +
anchorRecord.getCol1() +
" and row number " +
anchorRecord.getRow1() +
" at the offset position x " +
anchorRecord.getDx1() +
" and y " +
anchorRecord.getDy1() +
" co-ordinates.");
System.out.println("The bottom right hand corner of the image can be
found " +
"in the cell at column number " +
anchorRecord.getCol2() +
" and row number " +
anchorRecord.getRow2() +
" at the offset position x " +
anchorRecord.getDx2() +
" and y " +
anchorRecord.getDy2() +
" co-ordinates.");
}
private void processImages(XSSFWorkbook workbook) {
System.out.println("No support yet for OOXML based workbooks.
Investigating.");
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
try {
new Main().getImageMatrices("C:/temp/Excel");
}
catch(Exception ex) {
System.out.println("Caught an: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stacktrace follows:.....");
ex.printStackTrace(System.out);
}
}
public class ExcelFilenameFilter implements FilenameFilter {
public boolean accept(File file, String fileName) {
boolean includeFile = false;
if(fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
includeFile = true;
}
return(includeFile);
}
}
}
As you can see, I have had to dig into the bowels of the POI record
structure to get at the image's location. This same tactic will not work for
the OOXML based workbooks and I am still lloking into how to recover that
information but expect it to be much easier and to have something to do with
relations but I cannot be sure yet. Will post again if I find anything out.
Yours
Mark B
jerry-112 wrote:
>
> Hey guys,
>
> I am a new comer to POI framework. In one of my project, I need to read
> images from a .xls file. For each row there is a column contains an image
> and I need to read it out. It looks like I can read all images together,
> but how can I get images position, like column number, row number so I can
> related those images with other data? Any suggestion is highly
> appreciated. Thanks.
>
> --Jiangpeng Shi
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
>
--
View this message in context:
http://old.nabble.com/A-newbie-question%3A-how-to-get-image-position--tp28813811p28818753.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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
