example code

example.java (text/x-java), 8.29 KB, created by ethan on 2009-02-04 07:26:38 UTC

(hide)

Description: example code

Filename:

MIME Type:

Creator: ethan

Created: 2009-02-04 07:26:38 UTC

Size: 8.29 KB

patch

obsolete

>package excelUnhider; > >// version 0.8 > >import java.io.FileNotFoundException; >import java.io.IOException; >import java.lang.NullPointerException; >import gnu.getopt.*; > >import org.apache.poi.ss.usermodel.*; > > >public class example { > > private static org.apache.poi.ss.usermodel.Workbook wb = null; > > private static int getWidthByType(Cell thisCell, org.apache.poi.ss.usermodel.Workbook wb ) { > int width = 0; > int celltype = thisCell.getCellType(); > > // FIXME: Should be finding out the widest character in the font and using > // that as a base for the size. > // NEVERMIND: Windows doesn't handle font sizes correctly, so there's no > // guarantee that 10 pt. (for example) will render the same way every time > // if the file is printed using Excel. > > if (celltype == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC) > { > width = (((Double)thisCell.getNumericCellValue()).toString().length() + 4) * 256; > if (thisCell.getCellStyle().getDataFormatString().length() > width) > width = thisCell.getCellStyle().getDataFormatString().length(); > } > if (celltype == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING) > { > if (thisCell.getStringCellValue().matches(" *")) > return 0; > width = (thisCell.getStringCellValue().length() + 4) * 256; > } > if (celltype == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN) > { > width = 1024; > } > if (celltype == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR) > { > width = 2048; > } > if (celltype == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA) > { > //width = thisCell.getCellFormula().length() * 256; > // To render formulae and find out how long they are we need > // to be able to deal with external references, and I need to > // find out how to do that. Since most of them will render as > // #REF! or #VALUE! in our sandbox anyway, just use a default. > width = 3584; > } > > if (wb.getFontAt(thisCell.getCellStyle().getFontIndex()).getBoldweight() == wb.getFontAt(thisCell.getCellStyle().getFontIndex()).BOLDWEIGHT_BOLD) > width *= 1.5; > > return width; > } > > private static void removeBlankPages(int sheetNum, int finalRowNum) > { > int oldColNum = 0; > int finalColNum = 0; > org.apache.poi.ss.usermodel.Sheet thisSheet = wb.getSheetAt(sheetNum); > > java.util.Iterator<org.apache.poi.ss.usermodel.Row> rows = thisSheet.rowIterator(); > while (rows.hasNext()) > { > org.apache.poi.ss.usermodel.Row thisRow = rows.next(); > if (thisRow.getRowNum() < finalRowNum) > { > java.util.Iterator<org.apache.poi.ss.usermodel.Cell> cells = thisRow.cellIterator(); > while (cells.hasNext()) > { > org.apache.poi.ss.usermodel.Cell thisCell = cells.next(); > if (thisCell.getColumnIndex() > finalColNum) > { > if (thisCell.getColumnIndex() > oldColNum + 25) > { > finalColNum = oldColNum; > break; > } > if (thisCell.getColumnIndex() > oldColNum) > oldColNum = thisCell.getColumnIndex(); > } > } > } > } > if (finalColNum == 0) > finalColNum = oldColNum; > wb.setPrintArea(sheetNum, 0, finalColNum, 0, finalRowNum); > } > > public static void main(String[] args) { > java.io.FileInputStream infile = null; > int maxCellsThisSheet = 0; > int cellsThisRow = 0; > String outfname = ""; > //boolean shortenPrintRange = false; > > outfname = "/home/baldridgeec/example-out.xls"; > > try { > infile = new java.io.FileInputStream("/home/baldridgeec/example.xls"); > } catch (FileNotFoundException e) { > System.err.println("example.xls" + ": File not found"); > System.exit(2); > } > > // Load data into Workbook model > org.apache.poi.ss.usermodel.WorkbookFactory wbf = new org.apache.poi.ss.usermodel.WorkbookFactory(); > try { > wb = wbf.create(infile); > } catch (Exception e) { > System.err.println("example.xls" + ": Couldn't open file or non-XLS or XLSX file"); > System.exit(2); > } > try { > infile.close(); > } catch (IOException e1) { > // TODO Auto-generated catch block > e1.printStackTrace(); > } > > // Iterate and parse workbook sheets > for (int i = 0; i < wb.getNumberOfSheets(); i++) > { > int oldRowNum = 0, finalRowNum = 0; > java.util.Map colWidths = new java.util.HashMap(); > colWidths = new java.util.TreeMap(); > org.apache.poi.ss.usermodel.Sheet thisSheet = wb.getSheetAt(i); > > maxCellsThisSheet = 0; > wb.setSheetHidden(i, false); > thisSheet.setDisplayGridlines(true); > thisSheet.setPrintGridlines(true); > thisSheet.setDefaultColumnWidth(8); > thisSheet.getPrintSetup().setPaperSize(thisSheet.getPrintSetup().LETTER_PAPERSIZE); > thisSheet.getPrintSetup().setLandscape(true); > > for (int row = 0; row < thisSheet.getLastRowNum()+1; row++) > { > if (thisSheet.getRow(row) != null) > { > boolean isValidRow = true, anyCellData = false; > org.apache.poi.ss.usermodel.Row thisRow = thisSheet.getRow(row); > if (thisRow.getPhysicalNumberOfCells() == 0) > isValidRow = false; > cellsThisRow = thisRow.getLastCellNum(); > if (thisRow.getZeroHeight()) > { > thisRow.setZeroHeight(false); > //thisRow.setHeight((short)-1); > thisRow.setHeight((short)256); > } > if (cellsThisRow > maxCellsThisSheet) > { > for (int col = maxCellsThisSheet; col < cellsThisRow; col++) > { > thisSheet.setColumnHidden(col, false); > } > maxCellsThisSheet = cellsThisRow; > } > for (int col = 0; col < cellsThisRow; col++) > { > try { > int dataLen = 2048; > // if current width is less than default width or there is no > // current width, set to default > try { > if ((Integer)colWidths.get(col) < dataLen) > thisSheet.setColumnWidth(col, dataLen); > } catch (NullPointerException e) { > thisSheet.setColumnWidth(col, dataLen); > } > dataLen = getWidthByType(thisRow.getCell(col), wb); > if (dataLen == 0) > { > break; > } > // if the actual width of the data in this cell is greater > // than the current width, set it and update current width > try { > if ((Integer)colWidths.get(col) < dataLen) > { > thisSheet.setColumnWidth(col, dataLen); > colWidths.put(col, dataLen); > } > } catch (NullPointerException e) > // a null pointer here means data but no previous, set and update > { > thisSheet.setColumnWidth(col, dataLen); > colWidths.put(col, dataLen); > } > if (wb.getFontAt(thisRow.getCell(col).getCellStyle().getFontIndex()).getColor() == thisRow.getCell(col).getCellStyle().getFillBackgroundColor()) > { > wb.getFontAt(thisRow.getCell(col).getCellStyle().getFontIndex()).setColor((short)org.apache.poi.ss.usermodel.Font.COLOR_NORMAL); > thisRow.getCell(col).getCellStyle().setFillBackgroundColor(org.apache.poi.hssf.util.HSSFColor.WHITE.index); > } > anyCellData = true; > } catch (NullPointerException e) > // getCellType() will throw a NullPointerException if > // the cell is empty. Since we've set a minimum width already > // we don't really care. > { ; } > } > if (!anyCellData) > { > isValidRow = false; > } > > if (isValidRow) > oldRowNum = row; > } else { > // row has no contents but needs to take up space anyway > // also: empty rows can exist (previously hidden) with massive heights > try { > thisSheet.getRow(row).setZeroHeight(false); > thisSheet.getRow(row).setHeight((short)256); > } catch (NullPointerException e) > { ; } > } > } > removeBlankPages(i, 1096); > } > >// Save file > String retrievedPrintArea = wb.getPrintArea(0); > if (retrievedPrintArea != null) > System.out.println("Print Area: " + retrievedPrintArea); > > try { > java.io.File fileTmp = java.io.File.createTempFile("ExcelUNHIDE","tmp"); > java.io.File fileFinal = new java.io.File(outfname); > java.io.FileOutputStream out = new java.io.FileOutputStream(fileTmp); > wb.write(out); > out.flush(); > out.close(); > fileTmp.renameTo(fileFinal); > System.out.println(outfname); > } catch (SecurityException e) { > System.err.println(outfname + ": Permission denied"); > System.exit(2); > } catch (FileNotFoundException e) { > System.err.println(outfname + ": Couldn't open file for writing"); > System.exit(2); > } catch (IOException e) { > System.err.println(outfname + ": I/O Error"); > System.exit(2); > } catch (IllegalStateException e) { > System.err.println("example.xls" + ": Workbook protected or unreadable"); > System.exit(2); > } > System.exit(0); > } >} View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]