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 I need to delete, but can't find a function for 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 (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 following the statement */
HSSFRow urlRow = sheet.getRow(j + 1);
HSSFCell urlCell = urlRow.getCell((short) k);
urlCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
/* Grab all the local images for comparison against the images in the excel sheet */
File imgDir = new File("images");
File[] localPics = imgDir.listFiles();
/* Grab all the images in the excel sheet */
List allPics = wb.getAllPictures();
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 icons */
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);
}
} }
/* Load image list back into the workbook */
}
}
}
}
}
}
Edited by: kellb on Oct 5, 2008 10:25 PM