POI load, edit, save excel file

Most common usage can be found here.

HERE is something about HSSF and XSSF

Issue 1: can not save

if open an existing file and edit then save, you may encounter fileNotFoundException, Access is denied.

I think this is because the file is still opened by the POI.

my solution is using POIFSFileSystem to open an inputStream :


FileInputStream fis = new FileInputStream(location);
 POIFSFileSystem fs = new POIFSFileSystem(fis);
 wb = new HSSFWorkbook(fs);

After our edit, close the inputStream, remove orginal file and create new.


fis.close();
 File file = new File(location);
 file.delete();

FileOutputStream out = new FileOutputStream(location);
 wb.write(out);
 out.close();

Issue 2 edit color

to get color , here is what we can do:


CellStyle style = cell.getCellStyle();
short fontIdx = style.getFontIndex();
Font font = workbook.getFontAt(fontIdx);
String fontName = font.getFontName();

Issue 3 remove sheet by name

Current POI(3.9) only support remove by position. We need to tweak ourselves.

One thing to mention is if you wanna remove multiple sheet, do remember not to iterate from head, otherwise weird stuff would happen. You know the reason, Ah.

private void removeSheetByName(String name)
{
for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--)
{
Sheet tmpSheet = wb.getSheetAt(i);
if (tmpSheet.getSheetName().equals(name))
{
wb.removeSheetAt(i);
return;
}
}
}

Issue 4 Copy Row

Row copy method:

 private void copyRow(Row sourceRow, Row newRow)
    {

        // Loop through source columns to add to new row
        for (int i = 0; i < sourceRow.getLastCellNum(); i++)
        {
            // Grab a copy of the old/new cell
            Cell oldCell = sourceRow.getCell(i);
            Cell newCell = newRow.createCell(i);

            // If the old cell is null jump to next cell
            if (oldCell == null)
            {
                newCell = null;
                continue;
            }

            // Copy style from old cell and apply to new cell
            CellStyle newCellStyle = wb.createCellStyle();
            newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
            ;
            newCell.setCellStyle(newCellStyle);

            // If there is a cell comment, copy
            if (newCell.getCellComment() != null)
            {
                newCell.setCellComment(oldCell.getCellComment());
            }

            // If there is a cell hyperlink, copy
            if (oldCell.getHyperlink() != null)
            {
                newCell.setHyperlink(oldCell.getHyperlink());
            }

            // Set the cell data type
            newCell.setCellType(oldCell.getCellType());

            // Set the cell data value
            switch (oldCell.getCellType())
            {
                case Cell.CELL_TYPE_BLANK:
                    newCell.setCellValue(oldCell.getStringCellValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    newCell.setCellValue(oldCell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_ERROR:
                    newCell.setCellErrorValue(oldCell.getErrorCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    newCell.setCellFormula(oldCell.getCellFormula());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    newCell.setCellValue(oldCell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    newCell.setCellValue(oldCell.getRichStringCellValue());
                    break;
            }
        }
    }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s