List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getFontAt
@Override
public HSSFFont getFontAt(int idx)
From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java
License:Open Source License
/** * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>. * Only the sheet on the given sheetIndex is copied. /* www . j a va2 s . c o m*/ * @param igrid the XMA model where to copy the data * @param book the POI represntation of the data * @param sheetIndex the index of the sheet to copy * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges */ public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) { GridWM grid = (GridWM) igrid; try { List errorList = new ArrayList(); grid.setSheetName(book.getSheetName(sheetIndex)); grid.colors.clear(); grid.initBuildInColors(); short ic = GridWM.HSSF_FIRST_COLOR_INDEX; HSSFPalette palette = book.getCustomPalette(); for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) { grid.colors.add(ic, new GridColor(color.getTriplet())); } grid.fonts.clear(); int numFonts = book.getNumberOfFonts(); if (numFonts > 4) { // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt() numFonts += 1; } for (short i = 0; i < numFonts; i++) { HSSFFont font = book.getFontAt(i); byte fontstyle = GridFont.FONT_NORML; if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD) fontstyle |= GridFont.FONT_BOLD; if (font.getItalic()) fontstyle |= GridFont.FONT_ITALIC; grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor())); } grid.styles.clear(); for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) { HSSFCellStyle style = book.getCellStyleAt(i); grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor())); } grid.namedRanges.clear(); for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) { HSSFName name = book.getNameAt(i); String rangeName = name.getNameName(); String rangeRef = null; try { // ranges not defined but referenced by formulas have a name but no reference in HSSF rangeRef = name.getReference(); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName)) .setCode(GridWM.CODE_inconsistentRange)); } if (rangeRef != null) { try { GridRange range = grid.getJeksDelegate().toRange(rangeRef); range.setKey(rangeName); grid.namedRanges.put(rangeName, range); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef)) .setCode(GridWM.CODE_unsupportedReference)); } } } grid.rows.clear(); grid.cols.clear(); grid.cells.clear(); grid.delegate = new GridJeksDelegate(grid); HSSFSheet sheet = book.getSheetAt(sheetIndex); int firstColNum = Integer.MAX_VALUE; int lastColNum = Integer.MIN_VALUE; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); if (row == null) continue; if (row.getFirstCellNum() >= 0) firstColNum = Math.min(firstColNum, row.getFirstCellNum()); lastColNum = Math.max(lastColNum, row.getLastCellNum()); if (lastColNum > 255) lastColNum = 255; for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { HSSFCell hssfcell = row.getCell(j); if (hssfcell == null) continue; GridCell gridcell = grid.getOrCreateCell(i, j); switch (hssfcell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: gridcell.setValue(hssfcell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: // TODO: recherche error text byte errorCode = hssfcell.getErrorCellValue(); // gridcell.setValue(errorCode); gridcell.setValue("#ERROR"); errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord", grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode))) .setCode(GridWM.CODE_errorRecord)); break; case HSSFCell.CELL_TYPE_FORMULA: String formula = null; try { formula = hssfcell.getCellFormula(); gridcell.setFormula(formula); } catch (SysException e) { if (formula != null) gridcell.setValue("=" + formula); //set it as text without interpretation errorList.add(e); } break; case HSSFCell.CELL_TYPE_NUMERIC: if (isDateCell(book, hssfcell)) { gridcell.setValue(hssfcell.getDateCellValue()); } else { gridcell.setValue(hssfcell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: gridcell.setValue(hssfcell.getStringCellValue()); break; default: throw new SysException("unknown cell type " + hssfcell.getCellType()); } gridcell.setEditable(!hssfcell.getCellStyle().getLocked()); gridcell.setStyle(hssfcell.getCellStyle().getIndex()); } } final int scalefactor = 256 / 7; // empirically testet // int width = sheet.getDefaultColumnWidth(); // returns nonsense // width = width/scalefactor; // grid.setDefaultColumnWidth(width); for (short i = (short) firstColNum; i <= lastColNum; i++) { int width = sheet.getColumnWidth(i); width = width / scalefactor; grid.getOrCreateColumn(i).setWidth(width); } if (firstColNum == Integer.MAX_VALUE) firstColNum = 0; if (lastColNum == Integer.MIN_VALUE) lastColNum = 0; grid.setMaxRange( new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum)); grid.setVisibleRange(grid.getMaxRange()); return errorList; } finally { grid.handle(grid.new GridReloadEvent()); } }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static HSSFCellStyle findStyle(HSSFCellStyle style, HSSFWorkbook srcwb, HSSFWorkbook destwb) { HSSFPalette srcpalette = srcwb.getCustomPalette(); HSSFPalette destpalette = destwb.getCustomPalette(); for (short i = 0; i < destwb.getNumCellStyles(); i++) { HSSFCellStyle old = destwb.getCellStyleAt(i); if (old == null) continue; if (style.getAlignment() == old.getAlignment() && style.getBorderBottom() == old.getBorderBottom() && style.getBorderLeft() == old.getBorderLeft() && style.getBorderRight() == old.getBorderRight() && style.getBorderTop() == old.getBorderTop() && isSameColor(style.getBottomBorderColor(), old.getBottomBorderColor(), srcpalette, destpalette)//from w w w. j av a 2s. c o m && style.getDataFormat() == old.getDataFormat() && isSameColor(style.getFillBackgroundColor(), old.getFillBackgroundColor(), srcpalette, destpalette) && isSameColor(style.getFillForegroundColor(), old.getFillForegroundColor(), srcpalette, destpalette) && style.getFillPattern() == old.getFillPattern() && style.getHidden() == old.getHidden() && style.getIndention() == old.getIndention() && isSameColor(style.getLeftBorderColor(), old.getLeftBorderColor(), srcpalette, destpalette) && style.getLocked() == old.getLocked() && isSameColor(style.getRightBorderColor(), old.getRightBorderColor(), srcpalette, destpalette) && style.getRotation() == old.getRotation() && isSameColor(style.getTopBorderColor(), old.getTopBorderColor(), srcpalette, destpalette) && style.getVerticalAlignment() == old.getVerticalAlignment() && style.getWrapText() == old.getWrapText()) { HSSFFont oldfont = destwb.getFontAt(old.getFontIndex()); HSSFFont font = srcwb.getFontAt(style.getFontIndex()); if (oldfont.getBoldweight() == font.getBoldweight() && oldfont.getItalic() == font.getItalic() && oldfont.getStrikeout() == font.getStrikeout() && oldfont.getCharSet() == font.getCharSet() && isSameColor(oldfont.getColor(), font.getColor(), srcpalette, destpalette) && oldfont.getFontHeight() == font.getFontHeight() && oldfont.getFontName().equals(font.getFontName()) && oldfont.getTypeOffset() == font.getTypeOffset() && oldfont.getUnderline() == font.getUnderline()) { return old; } } } return null; }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copyCellStyle(HSSFWorkbook destwb, HSSFCellStyle dest, HSSFWorkbook srcwb, HSSFCellStyle src) {/* w w w.j a va2 s . co m*/ if (src == null || dest == null) return; dest.setAlignment(src.getAlignment()); dest.setBorderBottom(src.getBorderBottom()); dest.setBorderLeft(src.getBorderLeft()); dest.setBorderRight(src.getBorderRight()); dest.setBorderTop(src.getBorderTop()); dest.setBottomBorderColor(findColor(src.getBottomBorderColor(), srcwb, destwb)); dest.setDataFormat( destwb.createDataFormat().getFormat(srcwb.createDataFormat().getFormat(src.getDataFormat()))); dest.setFillPattern(src.getFillPattern()); dest.setFillForegroundColor(findColor(src.getFillForegroundColor(), srcwb, destwb)); dest.setFillBackgroundColor(findColor(src.getFillBackgroundColor(), srcwb, destwb)); dest.setHidden(src.getHidden()); dest.setIndention(src.getIndention()); dest.setLeftBorderColor(findColor(src.getLeftBorderColor(), srcwb, destwb)); dest.setLocked(src.getLocked()); dest.setRightBorderColor(findColor(src.getRightBorderColor(), srcwb, destwb)); dest.setRotation(src.getRotation()); dest.setTopBorderColor(findColor(src.getTopBorderColor(), srcwb, destwb)); dest.setVerticalAlignment(src.getVerticalAlignment()); dest.setWrapText(src.getWrapText()); HSSFFont f = srcwb.getFontAt(src.getFontIndex()); HSSFFont nf = findFont(f, srcwb, destwb); if (nf == null) { nf = destwb.createFont(); nf.setBoldweight(f.getBoldweight()); nf.setCharSet(f.getCharSet()); nf.setColor(findColor(f.getColor(), srcwb, destwb)); nf.setFontHeight(f.getFontHeight()); nf.setFontHeightInPoints(f.getFontHeightInPoints()); nf.setFontName(f.getFontName()); nf.setItalic(f.getItalic()); nf.setStrikeout(f.getStrikeout()); nf.setTypeOffset(f.getTypeOffset()); nf.setUnderline(f.getUnderline()); } dest.setFont(nf); }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
private static HSSFFont findFont(HSSFFont font, HSSFWorkbook src, HSSFWorkbook dest) { for (short i = 0; i < dest.getNumberOfFonts(); i++) { HSSFFont oldfont = dest.getFontAt(i); if (font.getBoldweight() == oldfont.getBoldweight() && font.getItalic() == oldfont.getItalic() && font.getStrikeout() == oldfont.getStrikeout() && font.getCharSet() == oldfont.getCharSet() && font.getColor() == oldfont.getColor() && font.getFontHeight() == oldfont.getFontHeight() && font.getFontName().equals(oldfont.getFontName()) && font.getTypeOffset() == oldfont.getTypeOffset() && font.getUnderline() == oldfont.getUnderline()) { return oldfont; }/* w w w . j av a 2 s .c om*/ } return null; }
From source file:fitlibrary.runner.SpreadsheetRunner.java
License:Open Source License
private String format(HSSFCell cell, HSSFWorkbook workbook) { if (cell == null) return ""; String value = value(cell);//from w ww . j a va 2 s .c o m HSSFCellStyle style = cell.getCellStyle(); HSSFFont font = workbook.getFontAt(style.getFontIndex()); // System.err.println("Formatting "+value(cell)+"= "+font.getFontHeight()); if (font.getItalic()) value = tag("i", value); if (font.getBoldweight() > 400) value = tag("b", value); if (font.getUnderline() > 0) value = tag("u", value); if (font.getFontHeight() >= 480) value = tag("h1", value); else if (font.getFontHeight() >= 280) value = tag("h2", value); else if (font.getFontHeight() > 200) value = tag("h3", value); return value; }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
public static boolean getIsBold(HSSFWorkbook workbook, HSSFSheet sheet, int row, short col) { HSSFCell cell = sheet.getRow(row).getCell(col); HSSFFont font = workbook.getFontAt(cell.getCellStyle().getFontIndex()); return font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD; }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
public static boolean getIsItalic(HSSFWorkbook workbook, HSSFSheet sheet, int row, short col) { HSSFCell cell = sheet.getRow(row).getCell(col); HSSFFont font = workbook.getFontAt(cell.getCellStyle().getFontIndex()); return font.getItalic(); }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java
License:BSD License
public static short getFontColor(HSSFWorkbook workbook, HSSFSheet sheet, int row, short col) { HSSFCell cell = sheet.getRow(row).getCell(col); HSSFFont font = workbook.getFontAt(cell.getCellStyle().getFontIndex()); return font.getColor(); }
From source file:guineu.data.parser.impl.ParserXLS.java
License:Open Source License
public int v_type(HSSFWorkbook wb, HSSFRow row, HSSFCell cell) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: System.out.println(" Error - Row: " + row.getRowNum() + " Cell: " + cell.getColumnIndex() + " - Cell type Blank - " + cell.toString()); return 0; case HSSFCell.CELL_TYPE_BOOLEAN: System.out.println(" Error - Row: " + row.getRowNum() + " Cell: " + cell.getColumnIndex() + " - Cell type boolean - " + cell.toString()); return 0; case HSSFCell.CELL_TYPE_FORMULA: System.out.println(" Error - Row: " + row.getRowNum() + " Cell: " + cell.getColumnIndex() + " - Cell type formula - " + cell.toString()); return 1; case HSSFCell.CELL_TYPE_NUMERIC: HSSFCellStyle style = cell.getCellStyle(); HSSFFont font = wb.getFontAt(style.getFontIndex()); if (font.getColor() == (new HSSFColor.RED().getIndex())) { return 0; }//ww w . j a va 2s. c o m return 1; case HSSFCell.CELL_TYPE_STRING: style = cell.getCellStyle(); font = wb.getFontAt(style.getFontIndex()); if (font.getColor() == (new HSSFColor.RED().getIndex())) { return 0; } return 2; default: return 0; } }
From source file:org.apache.cocoon.generation.HSSFGenerator.java
License:Apache License
/** * Writes out the workbook data as XML, with formatting information *//*from ww w . j a v a 2 s . co m*/ private void writeStyles(HSSFWorkbook workbook, HSSFSheet sheet) throws SAXException { start("Styles"); HSSFRow row = null; HSSFCell cell = null; Iterator cells = null; Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (HSSFRow) rows.next(); cells = row.cellIterator(); while (cells.hasNext()) { cell = (HSSFCell) cells.next(); attribute("startRow", Integer.toString(row.getRowNum())); attribute("endRow", Integer.toString(row.getRowNum())); attribute("startCol", Short.toString(cell.getCellNum())); attribute("endCol", Short.toString(cell.getCellNum())); start("StyleRegion"); HSSFCellStyle style = cell.getCellStyle(); attribute("HAlign", Integer.toString(style.getAlignment())); attribute("VAlign", Integer.toString(style.getVerticalAlignment())); attribute("WrapText", ((style.getWrapText()) ? "1" : "0")); attribute("Orient", Integer.toString(style.getRotation())); attribute("Indent", Integer.toString(style.getIndention())); attribute("Locked", ((style.getLocked()) ? "1" : "0")); attribute("Hidden", ((style.getHidden()) ? "1" : "0")); attribute("Fore", workbook.getCustomPalette().getColor(style.getFillForegroundColor()).getHexString()); attribute("Back", workbook.getCustomPalette().getColor(style.getFillBackgroundColor()).getHexString()); attribute("PatternColor", Integer.toString(style.getFillPattern())); // TODO attribute("Format", "General"); // TODO start("Style"); HSSFFont font = workbook.getFontAt(style.getFontIndex()); attribute("Unit", Short.toString(font.getFontHeightInPoints())); attribute("Bold", Short.toString(font.getBoldweight())); attribute("Italic", ((font.getItalic()) ? "1" : "0")); attribute("Unterline", Integer.toString(font.getUnderline())); attribute("StrikeThrough", ((font.getStrikeout()) ? "1" : "0")); start("Font"); data(font.getFontName()); end("Font"); end("Style"); end("StyleRegion"); } } end("Styles"); }