List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getCellStyle
public HSSFCellStyle getCellStyle()
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 ww w . j a va 2 s . co 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:include.excel_import.Outter.java
License:Open Source License
private String getCellType(HSSFCell hssfcell) { HSSFDataFormat hssfdataformat = wb.createDataFormat(); HSSFCellStyle hssfcellstyle = hssfcell.getCellStyle(); short word0 = hssfcellstyle.getDataFormat(); String s = hssfdataformat.getFormat(word0); String s1 = ""; switch (hssfcell.getCellType()) { case 0: // '\0' if (s.indexOf("0_") == 0 || s.indexOf("0;") == 0 || s.indexOf("#,##0_") == 0 || s.indexOf("#,##0;") == 0 || s.equals("0")) { s1 = "INT"; break; }/*from w w w .jav a2 s .c o m*/ if (s.equals("yyyy\\-mm\\-dd") || s.equals("yyyy-mm-dd") || s.equals("yyyy/mm/dd") || s.equals("m/d/yy") || s.equals("0x1f")) { s1 = "DATE"; break; } if (s.indexOf("#,##0.") == 0 || s.indexOf("0.0") == 0) { s1 = "DOUBLE"; break; } if (s.equals("General")) s1 = "INT1"; else s1 = "NUMBERIC"; break; case 1: // '\001' if (s.equals("General")) { s1 = "STRING"; break; } if (s.equals("@")) { s1 = "STRING"; break; } if (s.indexOf("0_") == 0 || s.indexOf("0;") == 0 || s.indexOf("#,##0_") == 0 || s.indexOf("#,##0;") == 0 || s.equals("0")) { s1 = "INT"; break; } if (s.indexOf("#,##0.") == 0 || s.indexOf("0.0") == 0) s1 = "DOUBLE"; else System.out.println(s); break; case 3: // '\003' s1 = "BLANK"; break; case 2: // '\002' s1 = "FORMULA"; break; case 5: // '\005' s1 = "ERROR"; break; case 4: // '\004' default: s1 = "UNKNOWN"; break; } return s1; }
From source file:include.excel_import.XlsInfo.java
License:Open Source License
private String getCellDataType(HSSFCell cell) { String type = ""; HSSFCellStyle cellstyle = cell.getCellStyle(); short datatype = cellstyle.getDataFormat(); //STYLE?//from w w w.java 2 s . c o m if (df == null) System.err.println("df==null"); String dataFormatStr = df.getFormat(datatype); //System.out.println("DATATYPE="+dataFormatStr); //System.out.println("CellType+"+cell.getCellType()); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (dataFormatStr.indexOf("0_") == 0 || dataFormatStr.indexOf("0;") == 0 || dataFormatStr.indexOf("#,##0_") == 0 || dataFormatStr.indexOf("#,##0;") == 0 || dataFormatStr.equals("0")) { type = "INT"; } else if (dataFormatStr.equals("yyyy\\-mm\\-dd") || dataFormatStr.equals("yyyy-mm-dd") || dataFormatStr.equals("yyyy/mm/dd") || dataFormatStr.equals("m/d/yy") || dataFormatStr.equals("0x1f")) {//?2004915 type = "DATE"; } else if (dataFormatStr.indexOf("#,##0.") == 0 || dataFormatStr.indexOf("0.0") == 0) { type = "DOUBLE"; } else if (dataFormatStr.equals("General")) { //INT??????? type = "INT1"; } else { type = "NUMBERIC"; } // end of else break; case HSSFCell.CELL_TYPE_STRING: if (dataFormatStr.equals("General")) { type = "STRING"; } else if (dataFormatStr.equals("@")) { type = "STRING"; } else if (dataFormatStr.indexOf("0_") == 0 || //HSSf??? dataFormatStr.indexOf("0;") == 0 || dataFormatStr.indexOf("#,##0_") == 0 || dataFormatStr.indexOf("#,##0;") == 0 || dataFormatStr.equals("0")) { type = "INT"; } else if (dataFormatStr.indexOf("#,##0.") == 0 || dataFormatStr.indexOf("0.0") == 0) { type = "DOUBLE"; } else { System.out.println(dataFormatStr); } // end of else break; case HSSFCell.CELL_TYPE_BLANK: type = "BLANK"; break; case HSSFCell.CELL_TYPE_FORMULA: type = "FORMULA"; break; case HSSFCell.CELL_TYPE_ERROR: type = "ERROR"; break; default: type = "UNKNOWN"; break; } cellstyle = null; dataFormatStr = null; //System.gc(); return type; }
From source file:org.apache.cocoon.generation.HSSFGenerator.java
License:Apache License
/** * Writes out the workbook data as XML, with formatting information *///ww w . j a v a2s . c om 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"); }
From source file:org.beangle.commons.transfer.excel.ExcelItemWriterTest.java
License:Open Source License
@Test(dependsOnMethods = { "testWrite" })
public void testRead() throws Exception {
File file = new File("src/test/resources/tmp.xls");
FileInputStream in = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(in);
HSSFCell cell = wb.getSheetAt(0).getRow(0).getCell(0);
// HSSFFont f = cell.getCellStyle().getFont(wb);
// Assert.assertEquals(f.getBoldweight(), HSSFFont.BOLDWEIGHT_BOLD);
Assert.assertEquals(cell.getCellStyle().getAlignment(), HSSFCellStyle.ALIGN_CENTER);
in.close();/*w ww . j a v a 2 s . co m*/
file.delete();
}
From source file:org.dbunit.dataset.excel.XlsTable.java
License:Open Source License
protected BigDecimal getNumericValue(HSSFCell cell) { logger.debug("getNumericValue(cell={}) - start", cell); String formatString = cell.getCellStyle().getDataFormatString(); String resultString = null;/* w w w.ja va2 s. c o m*/ double cellValue = cell.getNumericCellValue(); if ((formatString != null)) { if (!formatString.equals("General") && !formatString.equals("@")) { logger.debug("formatString={}", formatString); DecimalFormat nf = new DecimalFormat(formatString, symbols); resultString = nf.format(cellValue); } } BigDecimal result; if (resultString != null) { try { result = new BigDecimal(resultString); } catch (NumberFormatException e) { logger.debug("Exception occurred while trying create a BigDecimal. value={}", resultString); // Probably was not a BigDecimal format retrieved from the excel. Some // date formats are not yet recognized by HSSF as DateFormats so that // we could get here. result = toBigDecimal(cellValue); } } else { result = toBigDecimal(cellValue); } return result; }
From source file:org.exoplatform.services.document.impl.MSExcelDocumentReader.java
License:Open Source License
public static boolean isCellDateFormatted(HSSFCell cell) { boolean bDate = false; double d = cell.getNumericCellValue(); if (HSSFDateUtil.isValidExcelDate(d)) { HSSFCellStyle style = cell.getCellStyle(); int i = style.getDataFormat(); switch (i) { case 0xe: // m/d/yy case 0xf: // d-mmm-yy case 0x10: // d-mmm case 0x11: // mmm-yy case 0x12: // h:mm AM/PM case 0x13: // h:mm:ss AM/PM case 0x14: // h:mm case 0x15: // h:mm:ss case 0x16: // m/d/yy h:mm case 0x2d: // mm:ss case 0x2e: // [h]:mm:ss case 0x2f: // mm:ss.0 case 0xa5: // ?? case 0xa7: // ?? case 0xa9: // ?? case 0xac: // mm:dd:yy not specified in javadoc case 0xad: // yyyy-mm-dd not specified in javadoc case 0xae: // mm:dd:yyyy not specified in javadoc case 0xaf: // m:d:yy not specified in javadoc bDate = true;/*from w ww. j a v a 2 s . com*/ break; default: bDate = false; break; } } return bDate; }
From source file:org.gageot.excel.core.StringCellMapper.java
License:Apache License
private static boolean isTextFormat(HSSFCell cell) { short cellFormat = cell.getCellStyle().getDataFormat(); return ((TEXT_CELL_FORMAT == cellFormat) || (OPENOFFICE_TEXT_CELL_FORMAT == cellFormat)); }
From source file:org.gageot.excel.core.StringCellMapper.java
License:Apache License
private static boolean isDateFormat(HSSFCell cell) { short cellFormat = cell.getCellStyle().getDataFormat(); return (OPENOFFICE_DATE_CELL_FORMAT == cellFormat); }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row HSSFRow newRow = worksheet.getRow(destinationRowNum); HSSFRow sourceRow = worksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {/*from w w w.ja v a 2s . c o m*/ newRow = worksheet.createRow(destinationRowNum); } // 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 HSSFCell oldCell = sourceRow.getCell(i); HSSFCell 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 HSSFCellStyle newCellStyle = workbook.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; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } }