I am currently working with the Apache POI tools to manipulate Excel files.
I have injected some text and an image into an existing sheet,
and would like to add the capability of removing this information as well.
I can remove the text fine, but I am having removing the image after
injection. I can get a list of images from the workbook level,
and identify which image within the images index I need to delete, but
can't find a function for actually removing it.
Does anyone have any help they can offer in this area? Here's a snippet of
the code.
/* Check for licenses inserted within existing sheets as well */
int numSheets = wb.getNumberOfSheets();
/* for each sheet */
for (int i = 0; i < numSheets; i++) {
HSSFSheet sheet = wb.getSheetAt(i);
if (sheet != null) {
int numRows =
sheet.getPhysicalNumberOfRows();
/* for each row */
for (int j = 0; j < numRows; j++) {
HSSFRow row = sheet.getRow(j);
if (row != null) {
int numCells =
row.getPhysicalNumberOfCells();
/* for each cell */
for (int k = 0; k <
numCells; k++) {
HSSFCell cell =
row.getCell((short) k);
if (cell != null) {
int cellType =
cell.getCellType();
if (cellType ==
HSSFCell.CELL_TYPE_STRING) {
/*
* only
check string cells, since we
* know
the license statement is a
* string
*/
String
cellContent = cell
.getStringCellValue();
if
(cellContent
.startsWith("This document is licensed under a")) {
/*
*
We have found the cell. So we
*
need to clear one cell below
*
(the URL link)
*/
cell
.setCellType(HSSFCell.CELL_TYPE_BLANK);
/*
Grab the URL row and cell */
HSSFRow urlRow = sheet
.getRow(j + 1);
HSSFCell urlCell = urlRow
.getCell((short) k);
urlCell
.setCellType(HSSFCell.CELL_TYPE_BLANK);
/*
Get a list of local images to compare to */
File imgDir = new File("images");
File[] localPics = imgDir.listFiles();
/*
Get a list of images within the spreadsheet */
List allPics = wb.getAllPictures();
/*
for each pic in the spreadsheet */
for
(int l = 0; l < allPics.size(); l++){
Object o = allPics.get(l);
HSSFPictureData hssfPic = (HSSFPictureData)o;
byte[] hssfPicData = hssfPic.getData();
/* Compare pic against all available local images */
for (int m = 0; m < localPics.length; m++){
File localPicFile = localPics[m];
long length = localPicFile.length();
byte[] localPicData = new byte[(int) length];
FileInputStream localPicIn = new
FileInputStream(localPicFile);
localPicIn.read(localPicData);
String localPicStr = new String(localPicData);
String hssfPicStr = new String(hssfPicData);
if (localPicStr.equals(hssfPicStr)){
/* This doesn't work, but you get the
* idea of what I am trying to do. */
allPics.remove(l);
/* Also tried wb.getAllPictures().remove(l) but
* it corrupted the file. */
}
}
}
/*
Load image list back into the workbook */
}
}
}
}
}
}
}
------------------------------------------------------------------------------------------------
NatStats08 Conference, 19-21 November 2008, Melbourne. Register now and Save!
www.nss.gov.au/natstats
