List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:DSC.PackerReport.java
private static void createSpreadsheets() { orderList.sort(new Comparator<Order>() { @Override/* w ww . j a v a2 s .c o m*/ public int compare(Order o1, Order o2) { int result; if (o1.getFamilySize() < o2.getFamilySize()) { result = -1; } else if (o1.getFamilySize() == o2.getFamilySize()) { result = 0; } else { result = 1; } return result; } }); XSSFWorkbook workbook = new XSSFWorkbook(); for (Route route : routeList) { XSSFSheet sheet = workbook.createSheet("PackerReports Route - " + route.getID()); Map<String, String[]> data = new TreeMap<>(); data.put("1", new String[] { "Doorstep Chef Packer Sheet", "", "", route.getDrivers().get(0).getDriver().getDriverName().split(" ")[0] + " - " + route.getDrivers().get(0).getDriver().getContactNumber(), "", " Week: " + DriverReport.returnWeekInt() + " Route: " + route.getID() }); data.put("2", new String[] { "", "", "", "", "", "" }); data.put("3", new String[] { "Customer", "FamSize", "MealType", "Qty", "Allergies", "Exclutions" }); int[] totals = new int[11]; int counter = 4; for (Order order : orderList) { if (order.getRoute().equals(route.getID())) { Client client = order.getClient(); String customer = client.getName() + " " + client.getSurname(); String famSize = order.getFamilySize() + ""; for (Meal meal : order.getMeals()) { data.put(counter + "", new String[] { customer, famSize, meal.getMealType(), meal.getQuantity() + "", meal.getAllergies(), meal.getExclusions() }); customer = ""; famSize = ""; counter++; if (meal.getMealType().equals("Standard")) { totals[1] += meal.getQuantity(); } else if (meal.getMealType().equals("Low Carb")) { totals[2] += meal.getQuantity(); } else if (meal.getMealType().equals("Kiddies")) { totals[3] += meal.getQuantity(); } switch (meal.getQuantity()) { case 1: totals[4]++; break; case 2: totals[5]++; break; case 3: totals[6]++; break; case 4: totals[7]++; break; case 5: totals[8]++; break; case 6: totals[9]++; break; default: if (meal.getQuantity() > 6) { totals[10]++; } } } totals[0]++; } } Set<String> keySet = data.keySet(); int totalSize = 22000; int longestCustomer = 0; for (int key = 1; key < keySet.size() + 1; key++) { Row row = sheet.createRow(key - 1); String[] arr = data.get(key + ""); for (int i = 0; i < arr.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(arr[i]); XSSFCellStyle borderStyle = workbook.createCellStyle(); if (i == 0 && !(key + "").equals("1") && longestCustomer < ((String) arr[i]).length()) { longestCustomer = ((String) arr[i]).length(); } if (!((key + "").equals("1") || (key + "").equals("2"))) { borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); if ((key + "").equals("3")) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setAlignment(HorizontalAlignment.CENTER); borderStyle.setFillPattern(XSSFCellStyle.LESS_DOTS); borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); XSSFFont font = workbook.createFont(); font.setColor(IndexedColors.WHITE.getIndex()); font.setBold(true); borderStyle.setFont(font); } else { if (i != 0) { borderStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); } if (i != 5) { borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); } if (i == 5 || i == 4) { borderStyle.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY); borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY); } if ((Integer.parseInt((key + ""))) != keySet.size()) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); } borderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); } if (i == 3 || i == 1) { borderStyle.setAlignment(HorizontalAlignment.CENTER); } } else { if (key != 3 && (i == 4 || i == 5)) { borderStyle.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY); borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY); } if (i == 3) { borderStyle.setAlignment(HorizontalAlignment.CENTER); } else if (i == 5) { borderStyle.setAlignment(HorizontalAlignment.RIGHT); } XSSFFont font = workbook.createFont(); font.setFontName("Calibri"); font.setFontHeightInPoints((short) 13); font.setBold(true); borderStyle.setFont(font); } cell.setCellStyle(borderStyle); } } //<editor-fold defaultstate="collapsed" desc="Add Totals"> Row row = sheet.createRow(keySet.size()); Cell cell1 = row.createCell(0); cell1.setCellValue("Clients: " + totals[0]); XSSFCellStyle cellStyle1 = workbook.createCellStyle(); XSSFFont font = workbook.createFont(); font.setBold(true); cellStyle1.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); cellStyle1.setFont(font); cell1.setCellStyle(cellStyle1); Cell cell2 = row.createCell(1); cell2.setCellValue("Standard: " + totals[1]); XSSFCellStyle cellStyle2 = workbook.createCellStyle(); font.setBold(true); cellStyle2.setFont(font); cell2.setCellStyle(cellStyle2); Cell cell3 = row.createCell(4); cell3.setCellValue("Low Carb: " + totals[2]); XSSFCellStyle cellStyle3 = workbook.createCellStyle(); font.setBold(true); cellStyle3.setFont(font); cell3.setCellStyle(cellStyle3); Cell cell4 = row.createCell(5); cell4.setCellValue("Kiddies: " + totals[3]); XSSFCellStyle cellStyle4 = workbook.createCellStyle(); font.setBold(true); cellStyle4.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); cellStyle4.setFont(font); cell4.setCellStyle(cellStyle4); row = sheet.createRow(keySet.size() + 1); Cell holder = row.createCell(0); XSSFCellStyle border1 = workbook.createCellStyle(); border1.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); holder.setCellStyle(border1); cell2 = row.createCell(1); cell2.setCellValue("Single: " + totals[4]); XSSFCellStyle cellStyle6 = workbook.createCellStyle(); font.setBold(true); cellStyle6.setFont(font); cell2.setCellStyle(cellStyle6); cell3 = row.createCell(4); cell3.setCellValue("Couple: " + totals[5]); XSSFCellStyle cellStyle7 = workbook.createCellStyle(); font.setBold(true); cellStyle7.setFont(font); cell3.setCellStyle(cellStyle7); cell4 = row.createCell(5); cell4.setCellValue("Small(3): " + totals[6]); XSSFCellStyle cellStyle8 = workbook.createCellStyle(); font.setBold(true); cellStyle8.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); cellStyle8.setFont(font); cell4.setCellStyle(cellStyle8); row = sheet.createRow(keySet.size() + 2); Cell holder2 = row.createCell(0); XSSFCellStyle border2 = workbook.createCellStyle(); border2.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); border2.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); holder2.setCellStyle(border2); cell2 = row.createCell(1); cell2.setCellValue("Medium(4): " + totals[7]); XSSFCellStyle cellStyle9 = workbook.createCellStyle(); font.setBold(true); cellStyle9.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); cellStyle9.setFont(font); cell2.setCellStyle(cellStyle9); Cell holder3 = row.createCell(2); XSSFCellStyle border3 = workbook.createCellStyle(); border3.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); holder3.setCellStyle(border3); Cell holder4 = row.createCell(3); XSSFCellStyle border4 = workbook.createCellStyle(); border4.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); holder4.setCellStyle(border4); cell3 = row.createCell(4); cell3.setCellValue("Large(5): " + totals[8]); XSSFCellStyle cellStyle10 = workbook.createCellStyle(); font.setBold(true); cellStyle10.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); cellStyle10.setFont(font); cell3.setCellStyle(cellStyle10); cell4 = row.createCell(5); cell4.setCellValue("XLarge(6): " + totals[9]); XSSFCellStyle cellStyle11 = workbook.createCellStyle(); font.setBold(true); cellStyle11.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); cellStyle11.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); cellStyle11.setFont(font); cell4.setCellStyle(cellStyle11); //</editor-fold> sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 4)); sheet.addMergedRegion(new CellRangeAddress(keySet.size(), keySet.size(), 1, 3)); sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 1, keySet.size() + 1, 1, 3)); sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 2, keySet.size() + 2, 1, 3)); sheet.setColumnWidth(0, (longestCustomer + 1) * 240); sheet.setColumnWidth(1, 8 * 240); sheet.setColumnWidth(2, 10 * 240); sheet.setColumnWidth(3, 4 * 240); int usedSize = 0; for (int i = 0; i <= 3; i++) { usedSize += sheet.getColumnWidth(i); } sheet.setColumnWidth(4, (totalSize - usedSize) / 2); sheet.setColumnWidth(5, (totalSize - usedSize) / 2); Row rowDate = sheet.createRow(keySet.size() + 4); Cell cell = rowDate.createCell(0); SimpleDateFormat sf = new SimpleDateFormat("EEE MMM yyyy HH:mm:ss"); cell.setCellValue(sf.format(Calendar.getInstance().getTime())); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 4, keySet.size() + 4, 0, 5)); } try { workbook.write(excelOut); excelOut.close(); System.out.println("Done - Packer"); if (DSC_Main.generateAllReports) { DSC_Main.reportsDone++; if (DSC_Main.reportsDone == DSC_Main.TOTAL_REPORTS) { DSC_Main.reportsDone(); } } else { packerLoadingObj.setVisible(false); packerLoadingObj.dispose(); JOptionPane.showMessageDialog(null, "PackerReport Succesfully Generated", "Success", JOptionPane.INFORMATION_MESSAGE); } } catch (IOException io) { packerLoadingObj.setVisible(false); packerLoadingObj.dispose(); JOptionPane.showMessageDialog(null, "An error occured\nCould not create PackerReport", "Error", JOptionPane.ERROR_MESSAGE); System.err.println("Error - Could not create new PackerReport: "); io.printStackTrace(); } }
From source file:DSC.QuantityReport.java
private static void processQuantityReport() { int excelNumber = 0; int sheetNumber = 0; workbook = new XSSFWorkbook(); Map<String, String[]> data = new TreeMap<>(); data.put(0 + "", new String[] { "Doorstep Chef - Quantity Report " + currentWeek(), "", "", "Week : " + returnWeekInt() + " " + " " }); data.put(1 + "", new String[] { "", "", "", "", "", "", "" }); data.put(2 + "", new String[] { "Total Of ", "Single", "Couple", "Three", "Four", "Five", "Six", "Extra" }); XSSFSheet sheet = workbook.createSheet("Quantity Report " + sheetNumber); int rowNum = 0; int cellNum = 0; for (int i = 3; i < 19; i++) { if (i == 3) { data.put(i + "", new String[] { "Orders", quantityObj.getCountFamilySize_1() + "", quantityObj.getCountFamilySize_2() + "", quantityObj.getCountFamilySize_3() + "", quantityObj.getCountFamilySize_4() + "", quantityObj.getCountFamilySize_5() + "", quantityObj.getCountFamilySize_6() + "", quantityObj.getCountFamilySizeMoreThanSix() + "" }); } else if (i == 4) { data.put(i + "", new String[] { "", "", "", "", "", "", "", "" }); } else if (i == 5) { data.put(i + "", new String[] { "Meals", quantityObj.getQuantityFamSize1() + "", quantityObj.getQuantityFamSize2() + "", quantityObj.getQuantityFamSize3() + "", quantityObj.getQuantityFamSize4() + "", quantityObj.getQuantityFamSize5() + "", quantityObj.getQuantityFamSize6() + "", quantityObj.getQuantityFamSizeMoreThanSix() + "" }); } else if (i == 6) { data.put(i + "", new String[] { "", "", "", "", "", "", "", "" }); } else if (i == 7) { data.put(i + "", new String[] { "Standard Meals", quantityObj.getCountFamSize1_Standard() + "", quantityObj.getCountFamSize2_Standard() + "", quantityObj.getCountFamSize3_Standard() + "", quantityObj.getCountFamSize4_Standard() + "", quantityObj.getCountFamSize5_Standard() + "", quantityObj.getCountFamSize6_Standard() + "", quantityObj.getCountFamilySizeMoreThanSix_Standard() + "" }); } else if (i == 8) { data.put(i + "", new String[] { "Low Carb Meals", quantityObj.getCountFamSize1_LC() + "", quantityObj.getCountFamSize2_LC() + "", quantityObj.getCountFamSize3_LC() + "", quantityObj.getCountFamSize4_LC() + "", quantityObj.getCountFamSize5_LC() + "", quantityObj.getCountFamSize6_LC() + "", quantityObj.getCountFamilySizeMoreThanSix_LC() + "" }); } else if (i == 9) { data.put(i + "", new String[] { "Kiddies Meals", quantityObj.getCountFamSize1_KD() + "", quantityObj.getCountFamSize2_KD() + "", quantityObj.getCountFamSize3_KD() + "", quantityObj.getCountFamSize4_KD() + "", quantityObj.getCountFamSize5_KD() + "", quantityObj.getCountFamSize6_KD() + "", quantityObj.getCountFamilySizeMoreThanSix_KD() + "" }); } else if (i == 10) { data.put(i + "", new String[] { "", "", "", "", "", "", "", "" }); } else if (i == 11) { data.put(i + "", new String[] { "Totals", quantityObj.totalSingleMeals() + "", quantityObj.totalCoupleMeals() + "", quantityObj.totalThreeMeals() + "", quantityObj.totalFourMeals() + "", quantityObj.totalFiveMeals() + "", quantityObj.totalSixMeals() + "", quantityObj.totalExtraMeals() + "" }); } else if (i == 12) { data.put(i + "", new String[] { "", "", "", "", "", "", "", "" }); } else if (i == 13) { data.put(i + "", new String[] { "Standard Individuals", "", "", "", "", "", "", quantityObj.returnTotalStandardMeals() + "" }); } else if (i == 14) { data.put(i + "", new String[] { "Low Carb Individuals", "", "", "", "", "", "", quantityObj.returnTotalLowCarbMeals() + "" }); } else if (i == 15) { data.put(i + "", new String[] { "Kiddies Individuals", "", "", "", "", "", "", quantityObj.returnTotalKiddiesMeals() + "" }); } else if (i == 16) { data.put(i + "", new String[] { "", "", "", "", "", "", "", "" }); } else if (i == 17) { data.put(i + "", new String[] { "Total Clients", "", "", "", "", "", "", quantityObj.returnTotalClients() + "" }); } else if (i == 18) { int totalIndividuals = quantityObj.returnTotalStandardMeals() + quantityObj.returnTotalLowCarbMeals() + quantityObj.returnTotalKiddiesMeals(); data.put(i + "", new String[] { "Total Individuals", "", "", "", "", "", "", totalIndividuals + "" }); }/*from w w w .j a v a 2 s .c om*/ } Set<String> keySet = data.keySet(); for (int keyIterate = 0; keyIterate < keySet.size(); keyIterate++) { Row row = sheet.createRow(rowNum); Object[] arr = data.get(keyIterate + ""); XSSFCellStyle borderStyle = workbook.createCellStyle(); XSSFCellStyle borderStyle2 = workbook.createCellStyle(); for (int i = 0; i < arr.length; i++) { XSSFFont font = workbook.createFont(); Cell cell = row.createCell(i); cell.setCellValue((String) arr[i]); if ((keyIterate + "").equals("0") || (keyIterate + "").equals("1")) { font.setFontName("Calibri"); font.setFontHeightInPoints((short) 13); font.setBold(true); borderStyle.setFont(font); borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setAlignment(HorizontalAlignment.LEFT); } else if (keyIterate > 1 && keyIterate < 12) { borderStyle.setBorderBottom(BorderStyle.THIN); borderStyle.setBorderTop(BorderStyle.THIN); borderStyle.setBorderLeft(BorderStyle.THIN); borderStyle.setBorderRight(BorderStyle.THIN); } if ((keyIterate + "").equals("2")) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setAlignment(HorizontalAlignment.CENTER); borderStyle.setFillPattern(XSSFCellStyle.LESS_DOTS); borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); XSSFFont font2 = workbook.createFont(); font2.setColor(IndexedColors.WHITE.getIndex()); borderStyle.setFont(font2); } cell.setCellStyle(borderStyle); } rowNum++; cellNum++; sheetNumber++; } sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); for (int i = 0; i < 8; i++) { if (i == 0) { sheet.setColumnWidth(i, 5000); } else { sheet.setColumnWidth(i, 2000); } } try { creatSheet(sheetNumber + "", workbook); } catch (IOException ex) { JOptionPane.showMessageDialog(null, "File Could Not Be Found."); } excelNumber++; }
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 {/* w w w. j a v a2s. 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 w w .ja va2 s . co 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 srcSheet the sheet to copy.//from www . j a v a 2 s .com * @param destSheet the sheet to create. * @param srcRow the row to copy. * @param destRow the row to create. * @param styleMap - */ public static void copyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { // manage a list of merged zone in order to not insert two times a // merged zone Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>(); destRow.setHeight(srcRow.getHeight()); // pour chaque row for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); // ancienne cell XSSFCell newCell = destRow.getCell(j); // new cell if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); } // copy chaque cell copyCell(oldCell, newCell, styleMap); // copy les informations de fusion entre les cellules // System.out.println("row num: " + srcRow.getRowNum() + // " , col: " + (short)oldCell.getColumnIndex()); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { // System.out.println("Selected merged region: " + // mergedRegion.toString()); CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn()); // System.out.println("New merged region: " + // newMergedRegion.toString()); CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion); if (isNewMergedRegion(wrapper, mergedRegions)) { mergedRegions.add(wrapper); destSheet.addMergedRegion(wrapper.range); } } } } }
From source file:kp.servlet.ExportRpt.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.// ww w .j a v a 2s .c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); Logger.getLogger(ExportRpt.class.getName()).log(Level.SEVERE, "accRole :" + request.getParameter("accRole")); Logger.getLogger(ExportRpt.class.getName()).log(Level.SEVERE, "Unit :" + request.getParameter("unit")); ArrayList<MocWfTran> Mocstatus = new ArrayList<>(); TranDao tdao = new TranDao(); Mocstatus = tdao.getMocStatusList(request.getParameter("accRole"), request.getParameter("unit"), request.getParameter("user")); //Developing Metadata String rptName = "MOC Status Excel Report"; ArrayList<String> colLabel = new ArrayList<>(); colLabel.add("Case Id"); colLabel.add("Moc NO"); colLabel.add("Moc Title"); colLabel.add("Moc Status"); colLabel.add("Creation Date"); colLabel.add("Owner's Name"); colLabel.add("Unit"); colLabel.add("Plant"); colLabel.add("Current Stage"); colLabel.add("Pending At"); //Starting EXCEL Creating //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet(rptName); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue(rptName); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colLabel.size() - 1)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); for (int i = 0; i < colLabel.size(); i++) { cell = row.createCell(i); cell.setCellValue(colLabel.get(i)); cell.setCellStyle(xstyle); } //Itrate or Database data and write int i = 2; for (MocWfTran bean : Mocstatus) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(bean.getCaseId()); cell = row.createCell(1); cell.setCellValue(bean.getMocNo()); cell = row.createCell(2); cell.setCellValue(bean.getCaseName()); cell = row.createCell(3); cell.setCellValue(bean.getMocStatus()); cell = row.createCell(4); cell.setCellValue(bean.getCrDateString()); cell = row.createCell(5); cell.setCellValue(bean.getCaseOwnerName()); cell = row.createCell(6); cell.setCellValue(bean.getUnitId()); cell = row.createCell(7); cell.setCellValue(bean.getPlantId()); cell = row.createCell(8); cell.setCellValue(bean.getStgNname()); cell = row.createCell(9); cell.setCellValue(bean.getUserNname()); i++; } //Export to Excel String file_name = "MocStatus"; String path = getServletContext().getRealPath("/"); String full_path = path + "/report/" + file_name + ".xlsx"; // FileOutputStream out = new FileOutputStream(new File("D://" + file_name + ".xlsx")); FileOutputStream out = new FileOutputStream(new File(full_path)); workbook.write(out); //Download code // reads input file from an absolute path File downloadFile = new File(full_path); OutputStream outStream; // obtains ServletContext try (FileInputStream inStream = new FileInputStream(downloadFile)) { //obtains ServletContext ServletContext context = getServletContext(); // gets MIME type of the file String mimeType = context.getMimeType(full_path); if (mimeType == null) { // set to binary type if MIME mapping not found mimeType = "application/octet-stream"; } // modifies response response.setContentType(mimeType); response.setContentLength((int) downloadFile.length()); // forces download String headerKey = "Content-Disposition"; String headerValue = String.format("attachment; filename=\"%s\"", downloadFile.getName()); response.setHeader(headerKey, headerValue); // obtains response's output stream outStream = response.getOutputStream(); byte[] buffer = new byte[4096]; int bytesRead = -1; while ((bytesRead = inStream.read(buffer)) != -1) { outStream.write(buffer, 0, bytesRead); } } outStream.close(); // response.sendRedirect("mocstatus.jsp"); }
From source file:Logic.RStoXL.java
public void genXLS(ResultSet rs, String Rpt_name, String path) { try {//from w ww .ja v a2s. c o m //RS METE DATA ResultSetMetaData rsmd = rs.getMetaData(); int col_count = rsmd.getColumnCount(); ArrayList<String> col_name = new ArrayList<String>(); for (int i = 1; i <= col_count; i++) { col_name.add(rsmd.getColumnLabel(i)); } //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet(Rpt_name); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue(Rpt_name); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_count - 1)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); for (int i = 0; i < col_count; i++) { cell = row.createCell(i); cell.setCellValue(col_name.get(i)); cell.setCellStyle(xstyle); } //Itrate or Database data and write int i = 2; while (rs.next()) { row = spreadsheet.createRow(i); for (int j = 1; j <= col_count; j++) { cell = row.createCell(j - 1); cell.setCellValue(rs.getString(j)); } i++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + Rpt_name + ".xlsx")); FileOutputStream out = new FileOutputStream(new File(path)); workbook.write(out); Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, ""); } catch (SQLException ex) { Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:Logic.Xls.java
public void genXLS(ResultSet rs, String Rpt_name, String path) { try {/*from www . jav a 2 s .c o m*/ //RS METE DATA ResultSetMetaData rsmd = rs.getMetaData(); int col_count = rsmd.getColumnCount(); ArrayList<String> col_name = new ArrayList<>(); for (int i = 1; i <= col_count; i++) { col_name.add(rsmd.getColumnLabel(i)); } //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet(Rpt_name); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue(Rpt_name); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_count - 1)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); for (int i = 0; i < col_count; i++) { cell = row.createCell(i); cell.setCellValue(col_name.get(i)); cell.setCellStyle(xstyle); } //Itrate or Database data and write int i = 2; while (rs.next()) { row = spreadsheet.createRow(i); for (int j = 1; j <= col_count; j++) { cell = row.createCell(j - 1); cell.setCellValue(rs.getString(j)); } i++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + Rpt_name + ".xlsx")); FileOutputStream out = new FileOutputStream(new File(path)); workbook.write(out); Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, ""); } catch (Exception ex) { Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, "Exception : " + ex); } }
From source file:mvjce.internal_sheet.java
public static void internal_details(XSSFWorkbook workbook) { XSSFSheet spreadsheet = workbook.createSheet("test_excel_internal"); XSSFRow row = spreadsheet.createRow((short) 0); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("MVJ College of Bangalore- 560067"); //MEARGING CELLS spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 19)); XSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setBold(true);// w w w . ja v a2s .co m XSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(font); cell.setCellStyle(style); Excel_operations.set_subcode(); row = spreadsheet.createRow((short) 1); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Department of " + dept_name); spreadsheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 19)); cell.setCellStyle(style); row = spreadsheet.createRow(3); row.setHeight((short) 600); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Semester: \n" + sem_string + sec); cell.setCellStyle(style); row = spreadsheet.createRow(4); String[] text = new String[3]; text[0] = "SI.No"; text[1] = "USN"; text[2] = "STUDENT NAME"; for (int i = 0; i < 3; i++) { cell = (XSSFCell) row.createCell((short) i); cell.setCellValue(text[i]); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(4, 5, i, i)); } XSSFRow row2 = spreadsheet.createRow((short) 5); for (int i = 0, j = 3; j <= 23; j += 4) { cell = row.createCell((short) j); cell.setCellValue(sub[i]); i++; cell.setCellStyle(style); cell = row2.createCell(j); cell.setCellValue("T1"); cell.setCellStyle(style); cell = row2.createCell(j + 1); cell.setCellValue("T2"); cell.setCellStyle(style); cell = row2.createCell(j + 2); cell.setCellValue("T3"); cell.setCellStyle(style); cell = row2.createCell(j + 3); cell.setCellValue("Avg"); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(4, 4, j, j + 3)); Excel_operations.insert_internals(workbook, spreadsheet); } }
From source file:mvjce.Writesheet.java
public static void writesheet() { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet(sem_string + sec); XSSFRow row = spreadsheet.createRow((short) 0); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("MVJ College of Bangalore- 560067"); //MEARGING CELLS spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 19)); XSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setBold(true);// w ww .ja va2 s.c o m XSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(font); cell.setCellStyle(style); Excel_operations.set_subcode(); for (int i = 0; i < 8; i++) { internal_sheet.sub[i] = sub[i]; } internal_sheet.dept_name = dept_name; internal_sheet.sec = sec; internal_sheet.sem_string = sem_string; internal_sheet.internal_details(workbook); row = spreadsheet.createRow((short) 1); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Department of " + dept_name); spreadsheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 19)); cell.setCellStyle(style); row = spreadsheet.createRow(4); row.setHeight((short) 600); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Semester: \n" + sem_string + sec); cell.setCellStyle(style); row = spreadsheet.createRow(5); String[] text = new String[3]; text[0] = "SI.No"; text[1] = "USN"; text[2] = "STUDENT\nNAME"; for (int i = 0; i < 3; i++) { cell = (XSSFCell) row.createCell((short) i); cell.setCellValue(text[i]); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(5, 7, i, i)); } int j = 0; XSSFRow row1 = spreadsheet.createRow((short) 6); row1.setHeight((short) 1000); XSSFRow row2 = spreadsheet.createRow((short) 7); row2.setHeight((short) 1000); for (int i = 3; i < 18; i++) { cell = row1.createCell((short) i); cell.setCellValue("Total no. of classes"); cell.setCellStyle(style); cell = row2.createCell((short) i); cell.setCellValue("No.of Classes attended"); cell.setCellStyle(style); cell = row2.createCell((short) i + 1); cell.setCellValue("%"); cell.setCellStyle(style); cell = row.createCell((short) i); cell.setCellValue(sub[j]); j++; cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(5, 5, i, i + 1)); i++; } cell = row1.createCell((short) 19); cell.setCellValue("%"); cell.setCellStyle(style); cell = row.createCell((short) 19); cell.setCellValue("AVG"); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(5, 5, 19, 19)); Excel_operations.fill_exceldata(workbook, spreadsheet); try { FileOutputStream out = new FileOutputStream(new File("test_excel.xlsx")); workbook.write(out); out.close(); } catch (Exception e) { Database.print_error("Excel_output_stream"); } System.out.println("typesofcells.xlsx written successfully"); }