List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getCellStyle
public HSSFCellStyle getCellStyle()
From source file:cn.trymore.core.util.excel.PoiExcelParser.java
License:Open Source License
public String getCellBorderStyle(HSSFCell cell) { HSSFCellStyle style = cell.getCellStyle(); StringBuilder sb = new StringBuilder(); if ((cell != null) && (style != null)) { switch (style.getBorderBottom()) { case 3://from w w w. j a va2 s. c om sb.append("border-bottom:dashed;"); break; case 7: sb.append("border-bottom:dotted;"); break; case 0: sb.append("border-bottom:none;"); break; case 2: sb.append("border-bottom:medium none;"); break; case 6: sb.append("border-bottom:double;"); break; case 8: sb.append("border-bottom:medium dashed;"); case 1: case 4: case 5: } switch (style.getBorderLeft()) { case 3: sb.append("border-left:dashed;"); break; case 7: sb.append("border-left:dotted;"); break; case 0: sb.append("border-left:none;"); break; case 2: sb.append("border-left:medium none;"); break; case 6: sb.append("border-left:double;"); break; case 8: sb.append("border-left:medium dashed;"); case 1: case 4: case 5: } switch (style.getBorderRight()) { case 3: sb.append("border-right:dashed;"); break; case 7: sb.append("border-right:dotted;"); break; case 0: sb.append("border-right:none;"); break; case 2: sb.append("border-right:medium none;"); break; case 6: sb.append("border-right:double;"); break; case 8: sb.append("border-right:medium dashed;"); case 1: case 4: case 5: } switch (style.getBorderTop()) { case 3: sb.append("border-top:dashed;"); break; case 7: sb.append("border-top:dotted;"); break; case 0: sb.append("border-top:none;"); break; case 2: sb.append("border-top:medium none;"); break; case 6: sb.append("border-top:double;"); break; case 8: sb.append("border-top:medium dashed;"); case 1: case 4: case 5: } if (tripleToRGBString(style.getBottomBorderColor()) != null) sb.append(new StringBuilder().append("border-bottom:1px thin ") .append(tripleToRGBString(style.getBottomBorderColor())).append(";").toString()); else { sb.append("border-bottom:1px thin windowtext;"); } if (tripleToRGBString(style.getLeftBorderColor()) != null) sb.append(new StringBuilder().append("border-left:1px thin ") .append(tripleToRGBString(style.getLeftBorderColor())).append(";").toString()); else { sb.append("border-left:1px thin windowtext;"); } if (tripleToRGBString(style.getRightBorderColor()) != null) sb.append(new StringBuilder().append("border-right:1px thin ") .append(tripleToRGBString(style.getRightBorderColor())).append(";").toString()); else { sb.append("border-right:1px thin windowtext;"); } if (tripleToRGBString(style.getTopBorderColor()) != null) sb.append(new StringBuilder().append("border-top:1px thin ") .append(tripleToRGBString(style.getTopBorderColor())).append(";").toString()); else { sb.append("border-top:1px thin windowtext;"); } return sb.toString(); } return ""; }
From source file:cn.trymore.core.util.excel.PoiExcelParser.java
License:Open Source License
public String getCellFontStyle(HSSFCell cell) { HSSFCellStyle style = cell.getCellStyle(); StringBuilder sb = new StringBuilder(); if ((cell != null) && (style != null)) { HSSFFont font = style.getFont(this.book); sb.append(new StringBuilder().append("font-size:").append(font.getFontHeightInPoints()).append("pt;") .toString());//from w w w. ja va 2 s. c om sb.append( new StringBuilder().append("font-weight:").append(font.getBoldweight()).append(";").toString()); if (tripleToRGBString(font.getColor()) != null) { sb.append(new StringBuilder().append("color:").append(tripleToRGBString(font.getColor())) .append(";").toString()); } sb.append("font-family:tahoma;"); if (font.getItalic()) { sb.append("font-style:italic;"); } return sb.toString(); } return ""; }
From source file:com.bayareasoftware.chartengine.ds.util.ExcelDump.java
License:Apache License
private static String cell2string(HSSFCell c) { if (c == null) { return "<i>NULL CELL</i>"; }// www . j a v a2 s . c om int type = c.getCellType(); String t = null, v = null; switch (type) { case HSSFCell.CELL_TYPE_BLANK: t = "BLANK"; v = ""; break; case HSSFCell.CELL_TYPE_STRING: t = "STRING"; v = c.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: if (ExcelInference.isCellDateFormatted(c)) { t = "DATE"; v = c.getDateCellValue() + ""; } else { t = "NUMERIC"; v = c.getNumericCellValue() + ""; } break; case HSSFCell.CELL_TYPE_ERROR: t = "ERROR"; v = "(errByte=" + c.getErrorCellValue() + "/toString=" + c + ")"; break; case HSSFCell.CELL_TYPE_FORMULA: t = "FORMULA"; v = c.getCellFormula(); break; default: t = "(UNKNOWN TYPE: " + type + ")"; v = c.toString(); break; } short style = c.getCellStyle().getDataFormat(); return v + "<br/>(" + t + ")<br/>dataformat=0x" + Integer.toHexString(style); }
From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java
License:Apache License
private void loadListGenotypingSewrvices() { int size = setShipmentDetail.iterator().next().getStSampleDetail().getLabstudyid().getPlatesize(); HSSFSheet sheetgrid = listBook.getSheetAt(5); HSSFCell cellStyle = sheetgrid.getRow(11).getCell(0); HSSFCellStyle style = cellStyle.getCellStyle(); HSSFCell cellStyleIntertek = sheetgrid.getRow(19).getCell(11); HSSFCellStyle styleIntertek = cellStyleIntertek.getCellStyle(); if (size == SIZE_PLATE_96) { rowsplate = 8;/*from w ww . j av a 2 s .c o m*/ colsplate = 12; } else { rowsplate = 16; colsplate = 24; } LinkedHashMap<String, Map<String, SampleDetail>> mapPlates = new LinkedHashMap<String, Map<String, SampleDetail>>(); for (ShipmentDetail shipmentDetail : setShipmentDetail) { SampleDetail detail = shipmentDetail.getStSampleDetail(); if (mapPlates.containsKey(detail.getPlatename())) { Map<String, SampleDetail> map = mapPlates.get(detail.getPlatename()); String letter = detail.getPlateloc().substring(0, 1); String number = detail.getPlateloc().substring(1, detail.getPlateloc().length()); map.put(getEquivalenceInInteger(letter) + "|" + number, detail); } else { Map<String, SampleDetail> map = new HashMap<String, SampleDetail>(); String letter = detail.getPlateloc().substring(0, 1); String number = detail.getPlateloc().substring(1, detail.getPlateloc().length()); map.put(getEquivalenceInInteger(letter) + "|" + number, detail); mapPlates.put(detail.getPlatename(), map); } } Iterator<Entry<String, Map<String, SampleDetail>>> it = mapPlates.entrySet().iterator(); int row = 10; int column = 0; while (it.hasNext()) { Map.Entry<String, Map<String, SampleDetail>> entry = (Entry<String, Map<String, SampleDetail>>) it .next(); if (entry.getKey() != null) { Map<String, SampleDetail> map = entry.getValue(); validateRow(sheetgrid, row); HSSFCell cellPlateName = validateCell(sheetgrid, row, 0); cellPlateName.setCellValue("Plate"); cellPlateName.setCellStyle(style); HSSFCell cellPlate = validateCell(sheetgrid, row, 1); cellPlate.setCellValue(entry.getKey()); row = row + 1; drawingFormatPlate(style, sheetgrid, row); for (int indexRow = 1; indexRow <= rowsplate; indexRow++) { for (int indexCol = 1; indexCol <= colsplate; indexCol++) { SampleDetail detail = map.get(indexRow + "|" + indexCol); validateRow(sheetgrid, row + indexRow); HSSFCellStyle styleCell = null; if (detail.getControltype() != null && !detail.getControltype().equals("")) styleCell = validateStatusSample(detail.getControltype()); else { styleCell = styleCellNormally; } if (sheetgrid.getRow(row + indexRow).getCell(column + indexCol) == null) sheetgrid.getRow(row + indexRow).createCell(column + indexCol); HSSFCell cell = sheetgrid.getRow(row + indexRow).getCell(column + indexCol); if (detail.getSamplegid() == null || (detail.getControltype() != null && !detail.getControltype().equals(""))) { cell.setCellValue(mapBlank.get(detail.getStudysampleid())); cell.setCellStyle(styleCell); } else { styleCell = styleCellNormally; cell.setCellValue(detail.getLabstudyid().getPrefix() + (detail.getLabstudyid().isUsePadded() ? StrUtils.getPaddingCeros(detail.getSamplegid()) : String.valueOf(detail.getSamplegid())) + "-" + detail.getStudysampleid()); } detail.getPlateloc(); if (detail.getPlateloc().equals("H11") || detail.getPlateloc().equals("H12")) { cell.setCellStyle(styleIntertek); } } } row = row + rowsplate; row = row + 2; } } }
From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java
License:Apache License
private void loadListGridIntertek() { int size = setShipmentDetail.iterator().next().getStSampleDetail().getLabstudyid().getPlatesize(); HSSFSheet sheetgrid = listBook.getSheetAt(4); HSSFCell cellStyle = sheetgrid.getRow(5).getCell(2); HSSFCellStyle style = cellStyle.getCellStyle(); if (size == SIZE_PLATE_96) { rowsplate = 8;/* www .j a v a 2s. co m*/ colsplate = 12; } else { rowsplate = 16; colsplate = 24; } LinkedHashMap<String, Map<String, SampleDetail>> mapPlates = new LinkedHashMap<String, Map<String, SampleDetail>>(); for (ShipmentDetail shipmentDetail : setShipmentDetail) { SampleDetail detail = shipmentDetail.getStSampleDetail(); if (mapPlates.containsKey(detail.getPlatename())) { Map<String, SampleDetail> map = mapPlates.get(detail.getPlatename()); String letter = detail.getPlateloc().substring(0, 1); String number = detail.getPlateloc().substring(1, detail.getPlateloc().length()); map.put(getEquivalenceInInteger(letter) + "|" + number, detail); } else { Map<String, SampleDetail> map = new HashMap<String, SampleDetail>(); String letter = detail.getPlateloc().substring(0, 1); String number = detail.getPlateloc().substring(1, detail.getPlateloc().length()); map.put(getEquivalenceInInteger(letter) + "|" + number, detail); mapPlates.put(detail.getPlatename(), map); } } Iterator<Entry<String, Map<String, SampleDetail>>> it = mapPlates.entrySet().iterator(); int row = 4; int column = 2; while (it.hasNext()) { Map.Entry<String, Map<String, SampleDetail>> entry = (Entry<String, Map<String, SampleDetail>>) it .next(); if (entry.getKey() != null) { Map<String, SampleDetail> map = entry.getValue(); validateRow(sheetgrid, row); HSSFCell cellPlate = validateCell(sheetgrid, row, 1); cellPlate.setCellValue(entry.getKey()); row = row + 1; drawingFormatPlate(style, sheetgrid, row); for (int indexRow = 1; indexRow <= rowsplate; indexRow++) { for (int indexCol = 1; indexCol <= colsplate; indexCol++) { SampleDetail detail = map.get(indexRow + "|" + indexCol); validateRow(sheetgrid, row + indexRow); HSSFCellStyle styleCell = null; if (detail.getControltype() != null && !detail.getControltype().equals("")) styleCell = validateStatusSample(detail.getControltype()); else { styleCell = styleCellNormally; } if (sheetgrid.getRow(row + indexRow).getCell(column + indexCol) == null) sheetgrid.getRow(row + indexRow).createCell(column + indexCol); HSSFCell cell = sheetgrid.getRow(row + indexRow).getCell(column + indexCol); if (detail.getSamplegid() == null || (detail.getControltype() != null && !detail.getControltype().equals(""))) { cell.setCellValue("BLANK"); cell.setCellStyle(styleCell); } else { styleCell = styleCellNormally; cell.setCellValue( detail.getLabstudyid().getPrefix() + (detail.getLabstudyid().isUsePadded() ? StrUtils.getPaddingCeros(detail.getSamplegid()) : String.valueOf(detail.getSamplegid()))); } } } row = row + rowsplate; row = row + 2; } } }
From source file:com.cms.utils.ExcelReader.java
public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { if (styleMap != null) { if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) { newCell.setCellStyle(oldCell.getCellStyle()); } else {/*from ww w.java2 s . c om*/ int stHashCode = oldCell.getCellStyle().hashCode(); HSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); } newCell.setCellStyle(newCellStyle); } } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
From source file:com.cn.util.ExcelImport.java
/** * ?2003excel/*from ww w. j a v a 2 s. com*/ * @param file * @return */ private static List<List<Object>> read2003Excel(InputStream inputStream) throws IOException { List<List<Object>> dataList = new ArrayList<>(); HSSFWorkbook wb = new HSSFWorkbook(inputStream); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null; HSSFCell cell = null; Object val = null; DecimalFormat df = new DecimalFormat("0");// ? SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// ? for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); if (row == null) { continue; } List<Object> objList = new ArrayList<>(); for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell == null) { val = null; objList.add(val); continue; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { val = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle().getDataFormatString())) { val = df.format(cell.getNumericCellValue()); } else { val = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); } break; case HSSFCell.CELL_TYPE_BOOLEAN: val = cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: val = ""; break; default: val = cell.toString(); break; } objList.add(val); } dataList.add(objList); } return dataList; }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copyCellStyle(HSSFWorkbook destwb, HSSFCell dest, HSSFWorkbook srcwb, HSSFCell src) { if (src == null || dest == null) return;// www .ja va2 s .c o m HSSFCellStyle nstyle = findStyle(src.getCellStyle(), srcwb, destwb); if (nstyle == null) { nstyle = destwb.createCellStyle(); copyCellStyle(destwb, nstyle, srcwb, src.getCellStyle()); } dest.setCellStyle(nstyle); }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copyCell(HSSFWorkbook destwb, HSSFCell dest, HSSFWorkbook srcwb, HSSFCell src) { if (src == null) { dest.setCellType(HSSFCell.CELL_TYPE_BLANK); return;// w w w .j a va 2 s. com } if (src.getCellComment() != null) dest.setCellComment(src.getCellComment()); if (src.getCellStyle() != null) { HSSFCellStyle nstyle = findStyle(src.getCellStyle(), srcwb, destwb); if (nstyle == null) { nstyle = destwb.createCellStyle(); copyCellStyle(destwb, nstyle, srcwb, src.getCellStyle()); } dest.setCellStyle(nstyle); } dest.setCellType(src.getCellType()); switch (src.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: dest.setCellValue(src.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: dest.setCellFormula(src.getCellFormula()); break; case HSSFCell.CELL_TYPE_ERROR: dest.setCellErrorValue(src.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: dest.setCellValue(src.getNumericCellValue()); break; default: dest.setCellValue(new HSSFRichTextString(src.getRichStringCellValue().getString())); break; } }
From source file:com.eryansky.core.excelTools.JsGridReportBase.java
License:Apache License
/** * // ww w .jav a2 s .c o m * @param * @return void */ private void buildStyle(HSSFWorkbook wb, HSSFWorkbook src, HSSFSheet sheet, int index, HashMap<String, HSSFCellStyle> ret, String key) { HSSFRow row = sheet.getRow(index); HSSFCell cell = row.getCell(1); HSSFCellStyle nstyle = wb.createCellStyle(); ExcelUtils.copyCellStyle(wb, nstyle, src, cell.getCellStyle()); ret.put(key, nstyle); }