List of usage examples for org.apache.poi.hssf.usermodel HSSFPalette getColor
public HSSFColor getColor(int index)
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. //from w w w .j a va 2 s .co 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
private static boolean isSameColor(int a, int b, HSSFPalette apalette, HSSFPalette bpalette) { if (a == b)/*from ww w . java 2s. co m*/ return true; HSSFColor acolor = apalette.getColor(a); HSSFColor bcolor = bpalette.getColor(b); if (acolor == null) return true; if (bcolor == null) return false; return acolor.getHexString().equals(bcolor.getHexString()); }
From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java
public HSSFColor setColor(HSSFWorkbook workbook, byte r, byte g, byte b) { HSSFPalette palette = workbook.getCustomPalette(); HSSFColor hssfColor = null;/*from w w w .ja v a2s . co m*/ try { hssfColor = palette.findColor(r, g, b); if (hssfColor == null) { palette.setColorAtIndex(HSSFColor.LAVENDER.index, r, g, b); hssfColor = palette.getColor(HSSFColor.LAVENDER.index); } } catch (Exception e) { logger.error(e); Utility.LOG(true, false, e, "::BULKUPLOAD_ERROR:: Exception occured in setColor method of " + this.getClass().getSimpleName()); } return hssfColor; }
From source file:com.sevenorcas.openstyle.app.service.spreadsheet.SpreadSheet.java
/** * Set a color by substituting an used color * /*from w w w . ja v a 2 s . c o m*/ * Thanks to http://stackoverflow.com/questions/10528516/poi-setting-background-color-to-a-cell * Thanks to http://stackoverflow.com/questions/842817/how-does-java-convert-int-into-byte * * @param workbook * @param r * @param g * @param b * @return */ public HSSFColor setColor(HSSFWorkbook workbook, int r, int g, int b) { HSSFPalette palette = workbook.getCustomPalette(); HSSFColor hssfColor = null; try { byte rb = (byte) (r); byte gb = (byte) (g); byte bb = (byte) (b); hssfColor = palette.findColor(rb, gb, bb); if (hssfColor == null) { short s = colorCustomers.getColorIndex(colorCustomers.getNextIndex()); palette.setColorAtIndex(s, rb, gb, bb); hssfColor = palette.getColor(s); } } catch (Exception e) { } return hssfColor; }
From source file:com.yukthi.excel.exporter.InternalWorkbook.java
License:Open Source License
/** * Gets the index of specified color in the current workbook * /*from ww w .j a v a 2s . c o m*/ * @param color * @return */ public HSSFColor getColor(Color color) { Integer index = colorToIndex.get(color); HSSFPalette palette = workbook.getCustomPalette(); // if color was already added get color based on found index if (index != null) { return palette.getColor(index); } //add color to the workbook and return the same HSSFColor hssfColor = null; try { palette.setColorAtIndex(nextColorIndex, (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue()); hssfColor = palette.getColor(nextColorIndex); nextColorIndex++; } catch (Exception ex) { throw new IllegalStateException("An error occurred while adding color to workbook", ex); } return hssfColor; }
From source file:es.jamisoft.comun.io.excel.ExcelGenerator.java
License:Apache License
/** * Este mtodo gestiona el estilo que se proporcionar a las cabeceras. * * @param wb Objeto Excel.// w w w. j a v a 2 s . c o m */ private void initStylesHeader(HSSFWorkbook wb) { // create instance of HSSFCellStyle HSSFCellStyle headerStyle = wb.createCellStyle(); // Create Font Header createFontCell(wb, headerStyle, ep.getFillFontColorHeader()); headerStyle.getFont(wb).setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // Create style of header cell HSSFPalette palette = wb.getCustomPalette(); HSSFColor colorMapfreHSSF = palette.findColor((byte) ep.getColorMapfre().getRed(), (byte) ep.getColorMapfre().getGreen(), (byte) ep.getColorMapfre().getBlue()); if (colorMapfreHSSF == null) { palette.setColorAtIndex(HSSFColor.LAVENDER.index, (byte) ep.getColorMapfre().getRed(), (byte) ep.getColorMapfre().getGreen(), (byte) ep.getColorMapfre().getBlue()); colorMapfreHSSF = palette.getColor(HSSFColor.LAVENDER.index); } short fillBGColor = colorMapfreHSSF.getIndex(); headerStyle.setFillForegroundColor(fillBGColor); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // create border of header cell createBorderCells(headerStyle); // set ep.setHeaderStyle(headerStyle); }
From source file:guineu.database.intro.impl.WriteFile.java
License:Open Source License
/** * Writes the basic data set into an excel file. * * @param dataset basic data set//from w w w . j av a 2 s. c o m * @param path Path where the new file will be created */ public void WriteXLSFileBasicDataset(Dataset dataset, String path) { FileOutputStream fileOut = null; try { HSSFSheet sheet; try { FileInputStream fileIn = new FileInputStream(path); POIFSFileSystem fs = new POIFSFileSystem(fileIn); wb = new HSSFWorkbook(fs); int NumberOfSheets = wb.getNumberOfSheets(); sheet = wb.createSheet(String.valueOf(NumberOfSheets)); } catch (Exception exception) { wb = new HSSFWorkbook(); sheet = wb.createSheet("Page 1"); } HSSFRow row = sheet.getRow(0); if (row == null) { row = sheet.createRow(0); } int cont = 0; for (String experimentName : dataset.getAllColumnNames()) { this.setCell(row, cont++, experimentName, null); } HSSFPalette palette = wb.getCustomPalette(); Color[] colors = dataset.getRowColor(); if (colors.length > 0) { for (int i = 0; i < dataset.getNumberRows(); i++) { palette.setColorAtIndex((short) 0x12, (byte) colors[i].getRed(), (byte) colors[i].getGreen(), (byte) colors[i].getBlue()); HSSFColor mycolor = palette.getColor((short) 0x12); //unmodified SimplePeakListRowOther lipid = (SimplePeakListRowOther) dataset.getRow(i); row = sheet.getRow(i + 1); if (row == null) { row = sheet.createRow(i + 1); } int c = 0; for (String experimentName : dataset.getAllColumnNames()) { if (lipid.getPeak(experimentName) == null) { this.setCell(row, c++, "", mycolor); } else { this.setCell(row, c++, lipid.getPeak(experimentName), mycolor); } } } } else { List<String> names = dataset.getAllColumnNames(); for (int i = 0; i < dataset.getNumberRows(); i++) { SimplePeakListRowOther lipid = (SimplePeakListRowOther) dataset.getRow(i); row = sheet.getRow(i + 1); if (row == null) { row = sheet.createRow(i + 1); } for (int j = 0; j < names.size(); j++) { Color c = dataset.getCellColor(i, j + 1); HSSFColor mycolor = null; if (c != null) { mycolor = palette.findColor((byte) c.getRed(), (byte) c.getGreen(), (byte) c.getBlue()); } if (lipid.getPeak(names.get(j)) == null) { this.setCell(row, j, "", null); } else { this.setCell(row, j, lipid.getPeak(names.get(j)), mycolor); } } } } //Write the output to a file fileOut = new FileOutputStream(path); wb.write(fileOut); fileOut.close(); } catch (Exception exception) { } }
From source file:Importers.ExcelImporter.java
License:Apache License
@Override public DefaultMutableTreeNode readFile(File file) { System.out.println("==ExcelImporter=readFile: " + file.getAbsolutePath()); DefaultMutableTreeNode root = new DefaultMutableTreeNode("vulns"); try {//from w ww . j av a2 s .c o m POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } for (int r = 1; r < rows; r++) { row = sheet.getRow(r); if (row != null) { // Create a new vuln Vulnerability vuln = new Vulnerability(); vuln.setTitle("NEW"); vuln.setIs_custom_risk(true); vuln.setRisk_category("None"); for (int c = 0; c < cols; c++) { cell = row.getCell(c); if (cell != null) { // Your code here String value = cell.getStringCellValue(); switch (c) { case 1:// title vuln.setTitle(value); break; case 2: // Risk CellStyle style = cell.getCellStyle(); short colorIdx = style.getFillForegroundColor(); HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); HSSFColor color = palette.getColor(colorIdx); String cc = color.getHexString(); System.out.println(cc); if (cc.equalsIgnoreCase("8080:8080:0")) { vuln.setRisk_category("Critical"); } else if (cc.equalsIgnoreCase("FFFF:0:0")) { vuln.setRisk_category("High"); } else if (cc.equalsIgnoreCase("FFFF:6666:0")) { vuln.setRisk_category("Medium"); } else if (cc.equalsIgnoreCase("F2F2:ECEC:0")) { vuln.setRisk_category("Low"); } else if (cc.equalsIgnoreCase("0:0:FFFF")) { vuln.setRisk_category("Info"); } break; case 3:// cvss string System.out.println(value); if (value.equalsIgnoreCase("No CVSS Vector")) { vuln.setIs_custom_risk(true); } else { vuln.setIs_custom_risk(false); vuln.setCvss_vector_string("CVSS2#" + value); } break; case 4://Description vuln.setDescription(value); break; case 5://Recommendation vuln.setRecommendation(value); break; case 6://Affected Hosts try { String[] lines = value.split("\n"); for (String line : lines) { String[] bits = line.split(" "); Host host = new Host(); host.setIp_address(bits[0]); String portprotocol = bits[2]; host.setPortnumber(portprotocol.split("/")[0]); host.setProtocol(portprotocol.split("/")[1]); vuln.addAffectedHost(host); } } catch (Exception ex) { ; } break; } } } System.out.println(vuln); root.add(new DefaultMutableTreeNode(vuln)); } } } catch (Exception ex) { ex.printStackTrace(); } return root; }
From source file:org.bbreak.excella.core.test.util.TestUtil.java
License:Open Source License
private static String getHSSFColorString(HSSFWorkbook workbook, short index) { HSSFPalette palette = workbook.getCustomPalette(); if (palette.getColor(index) != null) { HSSFColor color = palette.getColor(index); return color.getHexString(); } else {//from w w w . j a v a 2 s.com return ""; } }
From source file:org.bbreak.excella.reports.ReportsTestUtil.java
License:Open Source License
/** * HSSF????/* w w w . j a va 2 s .c om*/ * * @param workbook * @param index * @return HSSF?? */ private static String getHSSFColorString(HSSFWorkbook workbook, short index) { HSSFPalette palette = workbook.getCustomPalette(); if (palette.getColor(index) != null) { HSSFColor color = palette.getColor(index); return color.getHexString(); } else { return ""; } }