List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getCellStyle
@Override
public XSSFCellStyle getCellStyle()
From source file:de.escnet.ExcelTable.java
License:Open Source License
private void styleVerticalAlignment(Map<String, String> styles, XSSFCell cell) { XSSFCellStyle cellStyle = cell.getCellStyle(); if (cellStyle != null) { short alignment = cellStyle.getVerticalAlignment(); if (alignment == XSSFCellStyle.VERTICAL_BOTTOM) { styles.put("vertical-align", "bottom"); return; }// w w w . j av a2s. com if (alignment == XSSFCellStyle.VERTICAL_CENTER) { styles.put("vertical-align", "middle"); return; } } styles.put("vertical-align", "top"); }
From source file:de.escnet.ExcelTable.java
License:Open Source License
private void styleBackground(Map<String, String> styles, XSSFCell cell) { XSSFCellStyle cellStyle = cell.getCellStyle(); if (cellStyle != null) { XSSFColor xssf = cellStyle.getFillForegroundXSSFColor(); if (xssf != null) { String color = getHtmlColor(xssf); if (color != null && !color.equals("#ffffff")) { styles.put("background-color", color); }/*from w w w . java 2s . co m*/ } } }
From source file:de.escnet.ExcelTable.java
License:Open Source License
private String styleFont(Map<String, String> styles, XSSFCell cell) { XSSFCellStyle cellStyle = cell.getCellStyle(); if (cellStyle != null) { short fontIndex = cellStyle.getFontIndex(); XSSFFont font = sheet.getWorkbook().getFontAt(fontIndex); if (font.getItalic()) { styles.put("font-style", "italic"); }//from w w w . j a va2s. com if (font.getBoldweight() == Font.BOLDWEIGHT_BOLD) { styles.put("font-weight", "bold"); } styles.put("font", "" + font.getFontHeightInPoints() + "px " + font.getFontName()); if (FONT_COLORS) { String color = getHtmlColor(font.getXSSFColor()); if (color != null && !color.equals("#000000")) { return color; } } } return null; }
From source file:de.escnet.ExcelTable.java
License:Open Source License
private void styleBorders(Map<String, String> styles, int rowIndex, int colIndex, Span span) { XSSFCell cell = getCell(rowIndex, colIndex); XSSFCellStyle cellStyle = cell.getCellStyle(); if (cellStyle != null) { int topThickness = 0; if (rowIndex > rowMin) { XSSFCell topCell = getCell(rowIndex - 1, colIndex); if (topCell != null) { XSSFCellStyle topCellStyle = topCell.getCellStyle(); if (topCellStyle != null) { topThickness = getThickness(topCellStyle.getBorderBottom()); }/*from www . j ava 2s . c om*/ } } styleBorder(styles, "top", cellStyle.getBorderTop(), topThickness, cellStyle.getTopBorderColor()); int leftThickness = 0; if (colIndex > colMin) { XSSFCell leftCell = getCell(rowIndex, colIndex - 1); if (leftCell != null) { XSSFCellStyle leftCellStyle = leftCell.getCellStyle(); if (leftCellStyle != null) { leftThickness = getThickness(leftCellStyle.getBorderRight()); } } } styleBorder(styles, "left", cellStyle.getBorderLeft(), leftThickness, cellStyle.getLeftBorderColor()); } cell = getCell(rowIndex + span.rowspan - 1, colIndex); cellStyle = cell.getCellStyle(); if (cellStyle != null) { styleBorder(styles, "bottom", cellStyle.getBorderBottom(), 0, cellStyle.getBottomBorderColor()); } cell = getCell(rowIndex, colIndex + span.colspan - 1); cellStyle = cell.getCellStyle(); if (cellStyle != null) { styleBorder(styles, "right", cellStyle.getBorderRight(), 0, cellStyle.getRightBorderColor()); } }
From source file:es.upm.oeg.tools.rdfshapes.patterns.DatatypeObjectPropertyPatterns.java
License:Apache License
public static void main(String[] args) throws Exception { String endpoint = "http://3cixty.eurecom.fr/sparql"; List<String> classList = Files.readAllLines(Paths.get(classListPath), Charset.defaultCharset()); String classPropertyQueryString = readFile(classPropertyQueryPath, Charset.defaultCharset()); String propertyCardinalityQueryString = readFile(propertyCardinalityQueryPath, Charset.defaultCharset()); String individualCountQueryString = readFile(individualCountQueryPath, Charset.defaultCharset()); String objectCountQueryString = readFile(objectCountQueryPath, Charset.defaultCharset()); String tripleCountQueryString = readFile(tripleCountQueryPath, Charset.defaultCharset()); String literalCountQueryString = readFile(literalCountQueryPath, Charset.defaultCharset()); String blankCountQueryString = readFile(blankCountQueryPath, Charset.defaultCharset()); String iriCountQueryString = readFile(iriCountQueryPath, Charset.defaultCharset()); String datatypeCountQueryString = readFile(datatypeCountsPath, Charset.defaultCharset()); DecimalFormat df = new DecimalFormat("0.0000"); //Create the Excel workbook and sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("Cardinality"); int currentExcelRow = 0; int classStartRow = 0; for (String clazz : classList) { System.out.println("Class: " + clazz); Map<String, String> litMap = new HashMap<>(); Map<String, String> iriMap = ImmutableMap.of("class", clazz); String queryString = bindQueryString(individualCountQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap)); int individualCount; List<RDFNode> c = executeQueryForList(queryString, endpoint, "c"); if (c.size() == 1) { individualCount = c.get(0).asLiteral().getInt(); } else {// w ww . j av a2s.c o m continue; } // If there are zero individuals, continue if (individualCount == 0) { throw new IllegalStateException("Check whether " + classListPath + " and " + endpoint + " match."); } classStartRow = currentExcelRow; XSSFRow row = sheet.createRow(currentExcelRow); XSSFCell cell = row.createCell(0); cell.setCellValue(clazz); litMap = new HashMap<>(); iriMap = ImmutableMap.of("class", clazz); queryString = bindQueryString(classPropertyQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap)); List<RDFNode> nodeList = executeQueryForList(queryString, endpoint, "p"); // System.out.println("***"); // System.out.println("### **" + clazz + "**"); // System.out.println("***"); // System.out.println(); cell.getCellStyle().setAlignment(CellStyle.ALIGN_CENTER); for (RDFNode property : nodeList) { if (property.isURIResource()) { System.out.println(" " + property); int tripleCount; int objectCount; int literalCount; int blankCount; int iriCount; String propertyURI = property.asResource().getURI(); XSSFRow propertyRow = sheet.getRow(currentExcelRow); if (propertyRow == null) { propertyRow = sheet.createRow(currentExcelRow); } currentExcelRow++; XSSFCell propertyCell = propertyRow.createCell(1); propertyCell.setCellValue(propertyURI); litMap = new HashMap<>(); iriMap = ImmutableMap.of("class", clazz, "p", propertyURI); queryString = bindQueryString(tripleCountQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap)); c = executeQueryForList(queryString, endpoint, "c"); if (c.size() > 0) { tripleCount = c.get(0).asLiteral().getInt(); } else { tripleCount = 0; } queryString = bindQueryString(objectCountQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap)); c = executeQueryForList(queryString, endpoint, "c"); if (c.size() > 0) { objectCount = c.get(0).asLiteral().getInt(); } else { objectCount = 0; } queryString = bindQueryString(literalCountQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap)); c = executeQueryForList(queryString, endpoint, "c"); if (c.size() > 0) { literalCount = c.get(0).asLiteral().getInt(); } else { literalCount = 0; } queryString = bindQueryString(blankCountQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap)); c = executeQueryForList(queryString, endpoint, "c"); if (c.size() > 0) { blankCount = c.get(0).asLiteral().getInt(); } else { blankCount = 0; } queryString = bindQueryString(iriCountQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap)); c = executeQueryForList(queryString, endpoint, "c"); if (c.size() > 0) { iriCount = c.get(0).asLiteral().getInt(); } else { iriCount = 0; } XSSFCell objectCountCell = propertyRow.createCell(2); objectCountCell.setCellValue(objectCount); XSSFCell uniqueObjectsCell = propertyRow.createCell(3); uniqueObjectsCell.setCellValue(df.format(((double) objectCount) / tripleCount)); XSSFCell literalCell = propertyRow.createCell(4); literalCell.setCellValue(df.format((((double) literalCount) / objectCount))); XSSFCell iriCell = propertyRow.createCell(5); iriCell.setCellValue(df.format((((double) iriCount) / objectCount))); XSSFCell blankCell = propertyRow.createCell(6); blankCell.setCellValue(df.format((((double) blankCount) / objectCount))); if (literalCount > 0) { litMap = new HashMap<>(); iriMap = ImmutableMap.of("class", clazz, "p", propertyURI); queryString = bindQueryString(datatypeCountQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap)); List<Map<String, RDFNode>> solnMaps = executeQueryForList(queryString, endpoint, ImmutableSet.of("datatype", "c")); int i = 1; for (Map<String, RDFNode> soln : solnMaps) { String datatype = soln.get("datatype").asResource().getURI(); int count = soln.get("c").asLiteral().getInt(); XSSFCell dataCell = propertyRow.createCell(6 + i++); dataCell.setCellValue(datatype); dataCell = propertyRow.createCell(6 + i++); dataCell.setCellValue(df.format((((double) count) / objectCount))); } } // System.out.println("* " + propertyURI); // System.out.println(); // // System.out.println("| Object Count | Unique Objects | Literals | IRIs | Blank Nodes | "); // System.out.println("|---|---|---|---|---|"); // System.out.println(String.format("|%d|%d (%.2f%%) |%d (%.2f%%)|%d (%.2f%%)|%d (%.2f%%)|", // tripleCount, // objectCount, ((((double) objectCount)/tripleCount)*100), // literalCount, ((((double) literalCount)/objectCount)*100), // iriCount, ((((double) iriCount)/objectCount)*100), // blankCount, ((((double) blankCount)/objectCount)*100))); // System.out.println(); } } } String filename = "literals.xls"; FileOutputStream fileOut = new FileOutputStream(filename); wb.write(fileOut); fileOut.close(); }
From source file:es.upm.oeg.tools.rdfshapes.utils.CadinalityResultGenerator.java
License:Apache License
public static void main(String[] args) throws Exception { String endpoint = "http://3cixty.eurecom.fr/sparql"; List<String> classList = Files.readAllLines(Paths.get(classListPath), Charset.defaultCharset()); String classPropertyQueryString = readFile(classPropertyQueryPath, Charset.defaultCharset()); String propertyCardinalityQueryString = readFile(propertyCardinalityQueryPath, Charset.defaultCharset()); String individualCountQueryString = readFile(individualCountQueryPath, Charset.defaultCharset()); DecimalFormat df = new DecimalFormat("0.0000"); //Create the Excel workbook and sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("Cardinality"); int currentExcelRow = 0; int classStartRow = 0; for (String clazz : classList) { Map<String, String> litMap = new HashMap<>(); Map<String, String> iriMap = ImmutableMap.of("class", clazz); String queryString = bindQueryString(individualCountQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap)); int individualCount; List<RDFNode> c = executeQueryForList(queryString, endpoint, "c"); if (c.size() == 1) { individualCount = c.get(0).asLiteral().getInt(); } else {//from w w w .j a v a2 s.c o m continue; } // If there are zero individuals, continue if (individualCount == 0) { throw new IllegalStateException("Check whether " + classListPath + " and " + endpoint + " match."); } // System.out.println("***"); // System.out.println("### **" + clazz + "** (" + individualCount + ")"); // System.out.println("***"); // System.out.println(); classStartRow = currentExcelRow; XSSFRow row = sheet.createRow(currentExcelRow); XSSFCell cell = row.createCell(0); cell.setCellValue(clazz); cell.getCellStyle().setAlignment(CellStyle.ALIGN_CENTER); queryString = bindQueryString(classPropertyQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap)); List<RDFNode> nodeList = executeQueryForList(queryString, endpoint, "p"); for (RDFNode property : nodeList) { if (property.isURIResource()) { DescriptiveStatistics stats = new DescriptiveStatistics(); String propertyURI = property.asResource().getURI(); // System.out.println("* " + propertyURI); // System.out.println(); XSSFRow propertyRow = sheet.getRow(currentExcelRow); if (propertyRow == null) { propertyRow = sheet.createRow(currentExcelRow); } currentExcelRow++; XSSFCell propertyCell = propertyRow.createCell(1); propertyCell.setCellValue(propertyURI); Map<String, String> litMap2 = new HashMap<>(); Map<String, String> iriMap2 = ImmutableMap.of("class", clazz, "p", propertyURI); queryString = bindQueryString(propertyCardinalityQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap2, LITERAL_BINDINGS, litMap2)); List<Map<String, RDFNode>> solnMaps = executeQueryForList(queryString, endpoint, ImmutableSet.of("card", "count")); int sum = 0; List<CardinalityCount> cardinalityList = new ArrayList<>(); if (solnMaps.size() > 0) { for (Map<String, RDFNode> soln : solnMaps) { int count = soln.get("count").asLiteral().getInt(); int card = soln.get("card").asLiteral().getInt(); for (int i = 0; i < count; i++) { stats.addValue(card); } CardinalityCount cardinalityCount = new CardinalityCount(card, count, (((double) count) / individualCount) * 100); cardinalityList.add(cardinalityCount); sum += count; } // Check for zero cardinality instances int count = individualCount - sum; if (count > 0) { for (int i = 0; i < count; i++) { stats.addValue(0); } CardinalityCount cardinalityCount = new CardinalityCount(0, count, (((double) count) / individualCount) * 100); cardinalityList.add(cardinalityCount); } } Map<Integer, Double> cardMap = new HashMap<>(); for (CardinalityCount count : cardinalityList) { cardMap.put(count.getCardinality(), count.getPrecentage()); } XSSFCell instanceCountCell = propertyRow.createCell(2); instanceCountCell.setCellValue(individualCount); XSSFCell minCell = propertyRow.createCell(3); minCell.setCellValue(stats.getMin()); XSSFCell maxCell = propertyRow.createCell(4); maxCell.setCellValue(stats.getMax()); XSSFCell p1 = propertyRow.createCell(5); p1.setCellValue(stats.getPercentile(1)); XSSFCell p99 = propertyRow.createCell(6); p99.setCellValue(stats.getPercentile(99)); XSSFCell mean = propertyRow.createCell(7); mean.setCellValue(df.format(stats.getMean())); for (int i = 0; i < 21; i++) { XSSFCell dataCell = propertyRow.createCell(8 + i); Double percentage = cardMap.get(i); if (percentage != null) { dataCell.setCellValue(df.format(percentage)); } else { dataCell.setCellValue(0); } } // System.out.println("| Min Card. |Max Card. |"); // System.out.println("|---|---|"); // System.out.println("| ? | ? |"); // System.out.println(); } } //System.out.println("class start: " + classStartRow + ", class end: " + (currentExcelRow -1)); //We have finished writting properties of one class, now it's time to merge the cells int classEndRow = currentExcelRow - 1; if (classStartRow < classEndRow) { sheet.addMergedRegion(new CellRangeAddress(classStartRow, classEndRow, 0, 0)); } } String filename = "3cixty.xls"; FileOutputStream fileOut = new FileOutputStream(filename); wb.write(fileOut); fileOut.close(); }
From source file:es.upm.oeg.tools.rdfshapes.utils.CardinalityTemplateGenerator.java
License:Apache License
public static void main(String[] args) throws Exception { OntModel model = ModelFactory.createOntologyModel(OntModelSpec.OWL_MEM_RULE_INF, ModelFactory.createDefaultModel()); model.read("http://dublincore.org/2012/06/14/dcelements.ttl"); String endpoint = "http://infra2.dia.fi.upm.es:8899/sparql"; List<String> classList = Files.readAllLines(Paths.get(classListPath), Charset.defaultCharset()); String classPropertyQueryString = readFile(classPropertyQueryPath, Charset.defaultCharset()); String propertyCardinalityQueryString = readFile(propertyCardinalityQueryPath, Charset.defaultCharset()); String individualCountQueryString = readFile(individualCountQueryPath, Charset.defaultCharset()); //Create the Excel workbook and sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("Cardinality"); int currentExcelRow = 0; int classStartRow = 0; for (String clazz : classList) { Map<String, String> litMap = new HashMap<>(); Map<String, String> iriMap = ImmutableMap.of("class", clazz); String queryString = bindQueryString(individualCountQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap)); int individualCount; List<RDFNode> c = executeQueryForList(queryString, endpoint, "c"); if (c.size() == 1) { individualCount = c.get(0).asLiteral().getInt(); } else {//from w ww . ja v a 2 s . c o m continue; } // If there are zero individuals, continue if (individualCount == 0) { throw new IllegalStateException("Check whether " + classListPath + " and " + endpoint + " match."); } // System.out.println("***"); // System.out.println("### **" + clazz + "** (" + individualCount + ")"); // System.out.println("***"); // System.out.println(); classStartRow = currentExcelRow; XSSFRow row = sheet.createRow(currentExcelRow); XSSFCell cell = row.createCell(0); cell.setCellValue(clazz); cell.getCellStyle().setAlignment(CellStyle.ALIGN_CENTER); queryString = bindQueryString(classPropertyQueryString, ImmutableMap.of(IRI_BINDINGS, iriMap, LITERAL_BINDINGS, litMap)); List<RDFNode> nodeList = executeQueryForList(queryString, endpoint, "p"); for (RDFNode property : nodeList) { if (property.isURIResource()) { String propertyURI = property.asResource().getURI(); // System.out.println("* " + propertyURI); // System.out.println(); XSSFRow propertyRow = sheet.getRow(currentExcelRow); if (propertyRow == null) { propertyRow = sheet.createRow(currentExcelRow); } currentExcelRow++; XSSFCell propertyCell = propertyRow.createCell(1); propertyCell.setCellValue(propertyURI); // System.out.println("| Min Card. |Max Card. |"); // System.out.println("|---|---|"); // System.out.println("| ? | ? |"); // System.out.println(); } } //System.out.println("class start: " + classStartRow + ", class end: " + (currentExcelRow -1)); //We have finished writting properties of one class, now it's time to merge the cells int classEndRow = currentExcelRow - 1; if (classStartRow < classEndRow) { sheet.addMergedRegion(new CellRangeAddress(classStartRow, classEndRow, 0, 0)); } } String filename = "test.xls"; FileOutputStream fileOut = new FileOutputStream(filename); wb.write(fileOut); fileOut.close(); }
From source file:Import.Utils.XSSFConvert.java
/** * @param oldCell//from w ww . j a v a 2 s .com * @param newCell * @param styleMap */ public static void copyCell(HSSFCell oldCell, XSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { if (styleMap != null) { int stHashCode = oldCell.getCellStyle().hashCode(); HSSFCellStyle sourceCellStyle = styleMap.get(stHashCode); XSSFCellStyle destnCellStyle = newCell.getCellStyle(); if (sourceCellStyle == null) { sourceCellStyle = oldCell.getSheet().getWorkbook().createCellStyle(); } destnCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, sourceCellStyle); newCell.setCellStyle(destnCellStyle); } 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:localization.excel.java
public static void convert(String filePath) { Vector<String> zFile; if (filePath.endsWith(".zip")) { zFile = readzipfile(filePath);//from ww w . ja v a 2 s. co m for (String s : zFile) { if (s.endsWith(".xlsx")) { //System.out.println(s); convert(s); } } } else if (!filePath.endsWith(".xlsx")) { return; } else { try { FileInputStream file = new FileInputStream(new File(filePath)); System.out.println(filePath); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row; XSSFCell cell; rowNumber = sheet.getPhysicalNumberOfRows(); try { for (int i = 0; i < rowNumber; i++) { row = sheet.getRow(i); if (row != null) { int columnNum = row.getPhysicalNumberOfCells(); //System.out.println(columnNum); for (int j = 0; j < columnNum; j++) { cell = row.getCell(j); if (j == 0) { String name = cell.getRichStringCellValue().getString(); if (name.equalsIgnoreCase("Esri")) { langNumber++; } //System.out.println(name); } } if (i == 3) { cell = row.getCell(30); XSSFCellStyle cs = cell.getCellStyle(); cell = row.createCell(32); cell.setCellValue("Additional Charge per language"); cell.setCellStyle(cs); } } } } catch (Exception e) { } System.out.println(langNumber); double total = Double.parseDouble(sheet.getRow(langNumber + 3).getCell(29).getRawValue()); double subTotal = total / langNumber; DecimalFormat df = new DecimalFormat("#.000"); for (int i = 0; i < langNumber; i++) { cell = sheet.getRow(i + 4).createCell(32); cell.setCellValue("$" + df.format(subTotal)); } file.close(); FileOutputStream outFile = new FileOutputStream(filePath); workbook.write(outFile); outFile.close(); rowNumber = 0; langNumber = 0; System.out.println("Done"); } catch (Exception e) { e.printStackTrace(); } } }
From source file:mil.tatrc.physiology.utilities.testing.validation.ValidationMatrix.java
License:Apache License
public static void convert(String from, String to) throws IOException { FileInputStream xlFile = new FileInputStream(new File(from)); // Read workbook into HSSFWorkbook XSSFWorkbook xlWBook = new XSSFWorkbook(xlFile); List<SheetSummary> sheetSummaries = new ArrayList<SheetSummary>();// has to be an ordered list as sheet names can only be so long Map<String, String> refs = new HashMap<String, String>(); List<Sheet> Sheets = new ArrayList<Sheet>(); for (int s = 0; s < xlWBook.getNumberOfSheets(); s++) { XSSFSheet xlSheet = xlWBook.getSheetAt(s); Log.info("Processing Sheet : " + xlSheet.getSheetName()); if (xlSheet.getSheetName().equals("Summary")) { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 1; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; SheetSummary ss = new SheetSummary(); sheetSummaries.add(ss);// ww w . j a v a 2 s. c o m ss.name = row.getCell(0).getStringCellValue(); ss.description = row.getCell(1).getStringCellValue(); ss.validationType = row.getCell(2).getStringCellValue(); } } else if (xlSheet.getSheetName().equals("References")) { int rows = xlSheet.getPhysicalNumberOfRows(); for (int r = 1; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; refs.put("\\[" + r + "\\]", "@cite " + row.getCell(1).getStringCellValue()); } } else { int rows = xlSheet.getPhysicalNumberOfRows(); Sheet sheet = new Sheet(); sheet.summary = sheetSummaries.get(s - 2); Sheets.add(sheet); int cells = xlSheet.getRow(0).getPhysicalNumberOfCells(); for (int r = 0; r < rows; r++) { XSSFRow row = xlSheet.getRow(r); if (row == null) continue; String cellValue = null; for (int c = 0; c < cells; c++) { List<Cell> column; if (r == 0) { column = new ArrayList<Cell>(); sheet.table.add(column); } else { column = sheet.table.get(c); } XSSFCell cell = row.getCell(c); if (cell == null) { column.add(new Cell("", Agreement.NA, refs)); continue; } cellValue = null; switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } if (cellValue == null || cellValue.isEmpty()) column.add(new Cell("", Agreement.NA, refs)); else { Agreement a = Agreement.NA; XSSFColor color = cell.getCellStyle().getFillForegroundColorColor(); if (color != null) { byte[] rgb = color.getRGB(); if (rgb[0] < -25 && rgb[1] > -25 && rgb[2] < -25) { a = Agreement.Good; sheet.summary.goodAgreement++; } else if (rgb[0] > -25 && rgb[1] > -25 && rgb[2] < -25) { a = Agreement.Ok; sheet.summary.okAgreement++; } else if (rgb[0] > -25 && rgb[1] < -25 && rgb[2] < -25) { a = Agreement.Bad; sheet.summary.badAgreement++; } } column.add(new Cell(cellValue, a, refs)); } } } } } xlWBook.close(); xlFile.close(); //close xls // Generate our Tables for each Sheet PrintWriter writer = null; try { String name = from.substring(from.lastIndexOf('/') + 1, from.lastIndexOf('.')) + "Scenarios"; writer = new PrintWriter(to + name + "Summary.md", "UTF-8"); writer.println( "|Scenario|Description|Validation Type|Good agreement|General agreement with deviations|Some major disagreements|"); writer.println("|--- |--- |:---: |:---: |:---: |:---: |"); for (Sheet sheet : Sheets) { writer.println("|" + sheet.summary.name + "|" + sheet.summary.description + "|" + sheet.summary.validationType + "|" + success + sheet.summary.goodAgreement + endSpan + "|" + warning + sheet.summary.okAgreement + endSpan + "|" + danger + sheet.summary.badAgreement + endSpan + "|"); } writer.close(); // Create file and start the table writer = new PrintWriter(to + name + ".md", "UTF-8"); writer.println(name + " {#" + name + "}"); writer.println("======="); writer.println(); writer.println(); for (Sheet sheet : Sheets) { Log.info("Writing table : " + sheet.summary.name); writer.println("## " + sheet.summary.name); writer.println(sheet.summary.description); writer.println("We used a " + sheet.summary.validationType + " validation method(s)."); writer.println(""); for (int row = 0; row < sheet.table.get(0).size(); row++) { for (int col = 0; col < sheet.table.size(); col++) { writer.print("|" + sheet.table.get(col).get(row).text); } writer.println("|"); if (row == 0) { for (int col = 0; col < sheet.table.size(); col++) { writer.print("|--- "); } writer.println("|"); } } writer.println(); writer.println(); } writer.close(); } catch (Exception ex) { Log.error("Error writing tables for " + from, ex); writer.close(); } }