List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getColumnWidth
@Override public int getColumnWidth(int columnIndex)
Note, the returned value is always gerater that #getDefaultColumnWidth() because the latter does not include margins.
From source file:DSC.AccountantReport.java
private static void createExcelReport() { XSSFWorkbook workbook = new XSSFWorkbook(); clients.sort(new Comparator<Client>() { @Override/* www . j av a 2 s.c o m*/ public int compare(Client o1, Client o2) { return (o1.getSurname() + " " + o1.getName()).compareTo(o2.getSurname() + " " + o2.getName()); } }); int lastIndex = 0; for (int letter = 0; letter < 26; letter++) { XSSFSheet sheet = workbook.createSheet("AccountReport " + (char) (65 + letter)); Map<String, Object[]> data = new TreeMap<>(); data.put("1", new Object[] { "Doorstep Chef Accountant Sheet", "", "", "", "", "", "", "Week: " + DriverReport.returnWeekInt(), "", "" }); data.put("2", new Object[] { "", "", "", "", "", "", "", "", "", "" }); data.put("3", new Object[] { "Customer", "Contact", "Fam", "4Day", "Mthly", "EFT", "Cash", "Date Paid", "Stay", "Comments" }); int reduction = 0; for (int i = 0; i < clients.size(); i++) { Client client = clients.get(i); if (client.getSurname().toUpperCase().charAt(0) == (char) (65 + letter)) { data.put((i + 4 - reduction) + "", new Object[] { client.getName() + " " + client.getSurname(), client.getContactNumber().substring(0, 3) + " " + client.getContactNumber().substring(3, 6) + " " + client.getContactNumber().substring(6, 10), client.getAdditionalInfo(), "", "", "", "", "", "", "" }); } else { reduction++; } } Set<String> keySet = data.keySet(); int totalSize = 34900; int longestCustomer = 0; for (int key = 1; key < keySet.size() + 1; key++) { Row row = sheet.createRow(key - 1); Object[] arr = data.get(key + ""); for (int i = 0; i < arr.length; i++) { Cell cell = row.createCell(i); cell.setCellValue((String) arr[i]); if (i == 0 && !(key + "").equals("1") && longestCustomer < ((String) arr[i]).length()) { longestCustomer = ((String) arr[i]).length(); } XSSFCellStyle borderStyle = workbook.createCellStyle(); 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 != 9) { borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); } if ((Integer.parseInt((key + ""))) != keySet.size()) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); } borderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); } } else { if (i == 7) { borderStyle.setAlignment(HorizontalAlignment.RIGHT); } XSSFFont font = workbook.createFont(); font.setFontName("Calibri"); font.setFontHeightInPoints((short) 13); font.setBold(true); borderStyle.setFont(font); } cell.setCellStyle(borderStyle); } if (key == 1) { sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 9)); } } sheet.setColumnWidth(0, (longestCustomer + 1) * 240); sheet.setColumnWidth(1, 11 * 240); sheet.setColumnWidth(2, 5 * 240); sheet.setColumnWidth(3, 5 * 240); sheet.setColumnWidth(4, 5 * 240); sheet.setColumnWidth(5, 5 * 240); sheet.setColumnWidth(6, 5 * 240); sheet.setColumnWidth(7, 10 * 240); sheet.setColumnWidth(8, 5 * 240); for (int i = 0; i < 9; i++) { totalSize -= sheet.getColumnWidth(i); } sheet.setColumnWidth(9, totalSize); Row rowDate = sheet.createRow(keySet.size() + 1); 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() + 1, keySet.size() + 1, 0, 9)); } try { workbook.write(excelOut); excelOut.close(); System.out.println("Done - Accountant"); if (!(DSC_Main.generateAllReports)) { accountLoadObj.setVisible(false); accountLoadObj.dispose(); JOptionPane.showMessageDialog(null, "AccountReports Succesfully Generated", "Success", JOptionPane.INFORMATION_MESSAGE); } else { DSC_Main.reportsDone++; if (DSC_Main.reportsDone == DSC_Main.TOTAL_REPORTS) { DSC_Main.reportsDone(); } } } catch (IOException io) { accountLoadObj.setVisible(false); accountLoadObj.dispose(); JOptionPane.showMessageDialog(null, "An error occured\nCould not create AccountReport", "Error", JOptionPane.ERROR_MESSAGE); System.err.println("Error - Could not create new AccountReport: "); io.printStackTrace(); } }
From source file:DSC.DriverReport.java
private static void createSpreadsheets() { XSSFWorkbook workbook = new XSSFWorkbook(); for (Route route : routeList) { XSSFSheet sheet = workbook.createSheet("DriverReports Route - " + route.getID()); Map<String, Object[]> data = new TreeMap<>(); data.put("1", new Object[] { "Doorstep Chef Driver Sheet Week: " + returnWeekInt() + " - " + returnWeekString(), "", "", "", "", "", "", "", "", "Driver: " + route.getDrivers().get(0).getDriver().getDriverName().split(" ")[0] + " - " + route.getDrivers().get(0).getDriver().getContactNumber(), "Route: " + route.getID() }); data.put("2", new Object[] { "", "", "", "", "", "", "", "", "" }); data.put("3", new Object[] { "Customer", "Contact", "Cash", "DatePaid", "Mon", "Tue", "Wed", "Thu", "Fri", "Address", "AdditionalInfo" }); int counter = 4; for (Order order : orderList) { if (order.getRoute().equals(route.getID())) { Client client = order.getClient(); String durationMarker = ""; if (order.getDuration().equals("Monday - Thursday")) { durationMarker = "X"; }/*from w w w . j a v a2 s.c om*/ data.put("" + counter, new Object[] { client.getName() + " " + client.getSurname(), client.getContactNumber().substring(0, 3) + " " + client.getContactNumber().substring(3, 6) + " " + client.getContactNumber().substring(6, 10), "", "", "", "", "", "", durationMarker, client.getAddress().replaceAll("\n", ", "), client.getAdditionalInfo() }); counter++; } } Set<String> keySet = data.keySet(); int longestCustomer = 0; int totalWidth = 34900; for (int key = 1; key < keySet.size() + 1; key++) { Row row = sheet.createRow(key - 1); Object[] arr = data.get(key + ""); for (int i = 0; i < arr.length; i++) { Cell cell = row.createCell(i); cell.setCellValue((String) arr[i]); if (i == 0 && !(key + "").equals("1") && longestCustomer < ((String) arr[i]).length()) { longestCustomer = ((String) arr[i]).length(); } XSSFCellStyle borderStyle = workbook.createCellStyle(); 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 != 10) { borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); } if (i == 8 && ((String) arr[i]).contains("X")) { cell.setCellValue(""); borderStyle.setFillPattern(XSSFCellStyle.FINE_DOTS); borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); borderStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); } if ((Integer.parseInt((key + ""))) != keySet.size()) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); } borderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); } if ((i == 9 || i == 10) && !(key + "").equals("3")) { borderStyle.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY); borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY); } } else { if (i == 9) { borderStyle.setAlignment(HorizontalAlignment.CENTER); } else if (i == 10) { borderStyle.setAlignment(HorizontalAlignment.RIGHT); } XSSFFont font = workbook.createFont(); font.setFontName("Calibri"); font.setFontHeightInPoints((short) 13); font.setBold(true); borderStyle.setFont(font); } cell.setCellStyle(borderStyle); } } sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8)); if ((longestCustomer) < 14) { longestCustomer = 14; } sheet.setColumnWidth(0, (longestCustomer + 1) * 240); sheet.setColumnWidth(1, 12 * 240); for (int i = 0; i < 6; i++) { sheet.setColumnWidth(i + 4, 1000); } sheet.setColumnWidth(2, 1000); sheet.setColumnWidth(3, 10 * 240); for (int i = 0; i < 9; i++) { totalWidth -= sheet.getColumnWidth(i); } sheet.setColumnWidth(9, (totalWidth / 3) * 2); sheet.setColumnWidth(10, totalWidth / 3); Row rowDate = sheet.createRow(keySet.size() + 1); 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() + 1, keySet.size() + 1, 0, 10)); } try { excelOut = new FileOutputStream(file); workbook.write(excelOut); excelOut.close(); if (!(DSC_Main.generateAllReports)) { driverLoadingObj.setVisible(false); driverLoadingObj.dispose(); JOptionPane.showMessageDialog(null, "DriverReports Succesfully Generated", "Success", JOptionPane.INFORMATION_MESSAGE); } else { DSC_Main.reportsDone++; if (DSC_Main.reportsDone == DSC_Main.TOTAL_REPORTS) { DSC_Main.reportsDone(); } } } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "Please close the excel file before using generating.", "Error", JOptionPane.ERROR_MESSAGE); System.err.println("Error - Could not create new DriverReport: File currently in use."); ex.printStackTrace(); } catch (IOException io) { JOptionPane.showMessageDialog(null, "An error occured\nCould not create Driver Report", "Error", JOptionPane.ERROR_MESSAGE); System.err.println("Error - Could not create new Driver Report: "); io.printStackTrace(); } System.out.println("Done - Driver"); }
From source file:DSC.NewClientReport.java
private static void createExcelReport() { XSSFWorkbook workbook = new XSSFWorkbook(); clients.sort(new Comparator<Client>() { @Override/*from w w w .j a v a2 s. c o m*/ public int compare(Client o1, Client o2) { return (o1.getSurname() + " " + o1.getName()).compareTo(o2.getSurname() + " " + o2.getName()); } }); XSSFSheet sheet = workbook.createSheet("NewClient Report"); Map<String, Object[]> data = new TreeMap<>(); data.put("1", new Object[] { "Doorstep Chef NewClient Sheet", "", "", "Week: " + DriverReport.returnWeekInt(), "" }); data.put("2", new Object[] { "", "", "", "", "" }); data.put("3", new Object[] { "Customer", "Contact", "DriverName", "R.ID", "Email", "Address" }); int counter = 4; for (Client client : clients) { data.put(counter + "", new Object[] { client.getName() + " " + client.getSurname(), client.getContactNumber().substring(0, 3) + " " + client.getContactNumber().substring(3, 6) + " " + client.getContactNumber().substring(6, 10), client.getAdditionalInfo(), client.getAlternativeNumber(), client.getEmail(), client.getAddress() }); counter++; } Set<String> keySet = data.keySet(); int totalSize = 34900; int longestCustomer = 0; for (int key = 1; key < keySet.size() + 1; key++) { Row row = sheet.createRow(key - 1); Object[] arr = data.get(key + ""); for (int i = 0; i < arr.length; i++) { Cell cell = row.createCell(i); cell.setCellValue((String) arr[i]); if (i == 0 && !(key + "").equals("1") && longestCustomer < ((String) arr[i]).length()) { longestCustomer = ((String) arr[i]).length(); } XSSFCellStyle borderStyle = workbook.createCellStyle(); 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 != 4) { borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); } if ((Integer.parseInt((key + ""))) != keySet.size()) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); } borderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); } } else { if (i == 3) { borderStyle.setAlignment(HorizontalAlignment.RIGHT); } XSSFFont font = workbook.createFont(); font.setFontName("Calibri"); font.setFontHeightInPoints((short) 13); font.setBold(true); borderStyle.setFont(font); } cell.setCellStyle(borderStyle); } } sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 5)); sheet.setColumnWidth(0, (longestCustomer + 2) * 240); sheet.setColumnWidth(1, 13 * 240); sheet.setColumnWidth(2, 11 * 240); sheet.setColumnWidth(3, 5 * 240); for (int i = 0; i < 4; i++) { totalSize -= sheet.getColumnWidth(i); } sheet.setColumnWidth(4, totalSize / 2); sheet.setColumnWidth(5, totalSize / 2); Row rowDate = sheet.createRow(keySet.size() + 1); 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() + 1, keySet.size() + 1, 0, 5)); try { workbook.write(excelOut); excelOut.close(); System.out.println("Done - New Client"); if (!(DSC_Main.generateAllReports)) { newClientLoadObj.setVisible(false); newClientLoadObj.dispose(); JOptionPane.showMessageDialog(null, "NewClientReports Succesfully Generated", "Success", JOptionPane.INFORMATION_MESSAGE); } else { DSC_Main.reportsDone++; if (DSC_Main.reportsDone == 5) { DSC_Main.reportsDone(); } } } catch (IOException io) { newClientLoadObj.setVisible(false); newClientLoadObj.dispose(); JOptionPane.showMessageDialog(null, "An error occured\nCould not create NewClientReports", "Error", JOptionPane.ERROR_MESSAGE); System.err.println("Error - Could not create new NewClientReports: "); io.printStackTrace(); } }
From source file:DSC.PackerReport.java
private static void createSpreadsheets() { orderList.sort(new Comparator<Order>() { @Override//w ww .j a va 2 s . co 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:org.apache.solr.handler.extraction.TestXLSXResponseWriter.java
License:Apache License
@Test public void testStructuredDataViaBaseWriters() throws IOException, Exception { SolrQueryResponse rsp = new SolrQueryResponse(); // Don't send a ContentStream back, this will fall back to the configured base writer. // But abuse the CONTENT key to ensure writer is also checking type rsp.add(RawResponseWriter.CONTENT, "test"); rsp.add("foo", "bar"); SolrQueryRequest r = req();/*from w ww . j ava 2s. c o m*/ // check Content-Type assertEquals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", writerXlsx.getContentType(r, rsp)); // test our basic types,and that fields come back in the requested order XSSFSheet resultSheet = getWSResultForQuery( req("q", "id:1", "wt", "xlsx", "fl", "id,foo_s,foo_i,foo_l,foo_b,foo_f,foo_d,foo_dt1")); assertEquals( "id,foo_s,foo_i,foo_l,foo_b,foo_f,foo_d,foo_dt1\n1,hi,-1,12345678987654321,F,1.414,-1.0E300,2000-01-02T03:04:05Z\n", getStringFromSheet(resultSheet)); resultSheet = getWSResultForQuery(req("q", "id:1^0", "wt", "xlsx", "fl", "id,score,foo_s")); // test retrieving score assertEquals("id,score,foo_s\n1,0.0,hi\n", getStringFromSheet(resultSheet)); resultSheet = getWSResultForQuery(req("q", "id:1^0", "wt", "xlsx", "colname.id", "I.D.", "colwidth.id", "10", "fl", "id,score,foo_s")); // test override colname/width assertEquals("I.D.,score,foo_s\n1,0.0,hi\n", getStringFromSheet(resultSheet)); // test colwidth (value returned is in 256ths of a character as per excel standard) assertEquals(10 * 256, resultSheet.getColumnWidth(0)); resultSheet = getWSResultForQuery(req("q", "id:2", "wt", "xlsx", "fl", "id,v_ss")); // test multivalued assertEquals("id,v_ss\n2,hi; there\n", getStringFromSheet(resultSheet)); // test retrieving fields from index resultSheet = getWSResultForQuery(req("q", "*:*", "wt", "xslx", "fl", "*,score")); String result = getStringFromSheet(resultSheet); for (String field : "id,foo_s,foo_i,foo_l,foo_b,foo_f,foo_d,foo_dt1,v_ss,v2_ss,score".split(",")) { assertTrue(result.indexOf(field) >= 0); } // test null values resultSheet = getWSResultForQuery(req("q", "id:2", "wt", "xlsx", "fl", "id,foo_s,v_ss")); assertEquals("id,foo_s,v_ss\n2,,hi; there\n", getStringFromSheet(resultSheet)); // now test SolrDocumentList SolrDocument d = new SolrDocument(); SolrDocument d1 = d; d.addField("id", "1"); d.addField("foo_i", -1); d.addField("foo_s", "hi"); d.addField("foo_l", "12345678987654321L"); d.addField("foo_b", false); d.addField("foo_f", 1.414f); d.addField("foo_d", -1.0E300); d.addField("foo_dt1", new Date(Instant.parse("2000-01-02T03:04:05Z").toEpochMilli())); d.addField("score", "2.718"); d = new SolrDocument(); SolrDocument d2 = d; d.addField("id", "2"); d.addField("v_ss", "hi"); d.addField("v_ss", "there"); d.addField("v2_ss", "nice"); d.addField("v2_ss", "output"); d.addField("score", "89.83"); d.addField("shouldbeunstored", "foo"); SolrDocumentList sdl = new SolrDocumentList(); sdl.add(d1); sdl.add(d2); SolrQueryRequest req = req("q", "*:*"); rsp = new SolrQueryResponse(); rsp.addResponse(sdl); rsp.setReturnFields(new SolrReturnFields("id,foo_s", req)); resultSheet = getWSResultForQuery(req, rsp); assertEquals("id,foo_s\n1,hi\n2,\n", getStringFromSheet(resultSheet)); // try scores rsp.setReturnFields(new SolrReturnFields("id,score,foo_s", req)); resultSheet = getWSResultForQuery(req, rsp); assertEquals("id,score,foo_s\n1,2.718,hi\n2,89.83,\n", getStringFromSheet(resultSheet)); // get field values from docs... should be ordered and not include score unless requested rsp.setReturnFields(new SolrReturnFields("*", req)); resultSheet = getWSResultForQuery(req, rsp); assertEquals("id,foo_i,foo_s,foo_l,foo_b,foo_f,foo_d,foo_dt1,v_ss,v2_ss\n" + "1,-1,hi,12345678987654321L,false,1.414,-1.0E300,2000-01-02T03:04:05Z,,\n" + "2,,,,,,,,hi; there,nice; output\n", getStringFromSheet(resultSheet)); // get field values and scores - just check that the scores are there... we don't guarantee where rsp.setReturnFields(new SolrReturnFields("*,score", req)); resultSheet = getWSResultForQuery(req, rsp); String s = getStringFromSheet(resultSheet); assertTrue(s.indexOf("score") >= 0 && s.indexOf("2.718") > 0 && s.indexOf("89.83") > 0); // Test field globs rsp.setReturnFields(new SolrReturnFields("id,foo*", req)); resultSheet = getWSResultForQuery(req, rsp); assertEquals( "id,foo_i,foo_s,foo_l,foo_b,foo_f,foo_d,foo_dt1\n" + "1,-1,hi,12345678987654321L,false,1.414,-1.0E300,2000-01-02T03:04:05Z\n" + "2,,,,,,,\n", getStringFromSheet(resultSheet)); rsp.setReturnFields(new SolrReturnFields("id,*_d*", req)); resultSheet = getWSResultForQuery(req, rsp); assertEquals("id,foo_d,foo_dt1\n" + "1,-1.0E300,2000-01-02T03:04:05Z\n" + "2,,\n", getStringFromSheet(resultSheet)); // Test function queries rsp.setReturnFields(new SolrReturnFields("sum(1,1),id,exists(foo_s1),div(9,1),foo_f", req)); resultSheet = getWSResultForQuery(req, rsp); assertEquals("sum(1,1),id,exists(foo_s1),div(9,1),foo_f\n" + ",1,,,1.414\n" + ",2,,,\n", getStringFromSheet(resultSheet)); // Test transformers rsp.setReturnFields(new SolrReturnFields("mydocid:[docid],[explain]", req)); resultSheet = getWSResultForQuery(req, rsp); assertEquals("mydocid,[explain]\n" + ",\n" + ",\n", getStringFromSheet(resultSheet)); req.close(); }
From source file:org.kuali.test.runner.output.PoiHelper.java
License:Educational Community License
private void copySheets(XSSFSheet newSheet, XSSFSheet sheet) { int maxColumnNum = 0; Map<Integer, XSSFCellStyle> styleMap = new HashMap<Integer, XSSFCellStyle>(); int mergedReqionsCount = sheet.getNumMergedRegions(); for (int i = 0; i < mergedReqionsCount; ++i) { newSheet.addMergedRegion(sheet.getMergedRegion(i)); }//from w w w . j a v a2s. com for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { XSSFRow srcRow = sheet.getRow(i); XSSFRow destRow = newSheet.createRow(i); if (srcRow != null) { copyRow(srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } }
From source file:sv.com.mined.sieni.controller.GestionNotasController.java
public static void copyRow(XSSFSheet worksheetSource, XSSFSheet worksheetDestination, int sourceRowNum, int destinationRowNum) { // Get the source / new row XSSFRow origen = worksheetSource.getRow(sourceRowNum); XSSFRow destino = worksheetDestination.createRow(destinationRowNum); // Loop through source columns to add to new row for (int i = 0; i < origen.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = origen.getCell(i); XSSFCell newCell = destino.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null;//w ww .j a va 2 s . c om continue; } //Ajustar tamaos columnas worksheetDestination.setColumnWidth(i, worksheetSource.getColumnWidth(i)); // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.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; } } }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.AutoFilterTest.java
License:Open Source License
@Test public void autoFilter() throws Exception { debug = false;//from ww w.j a v a 2 s . co m autoFilter = true; InputStream inputStream = runAndRenderReport("SideBySideMultiColumns.rptdesign", "xlsx"); assertNotNull(inputStream); try { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); assertEquals("Sheet0", workbook.getSheetAt(0).getSheetName()); XSSFSheet sheet = workbook.getSheetAt(0); assertEquals(124, this.firstNullRow(sheet)); assertEquals(297, sheet.getRow(0).getHeightInPoints(), 1.0); assertEquals(2048, sheet.getColumnWidth(0)); assertEquals(6196, sheet.getColumnWidth(1)); assertEquals(3749, sheet.getColumnWidth(2)); assertEquals(2396, sheet.getColumnWidth(3)); assertEquals(4516, sheet.getColumnWidth(4)); assertEquals(7072, sheet.getColumnWidth(5)); assertEquals(2048, sheet.getColumnWidth(6)); assertEquals(3509, sheet.getColumnWidth(7)); assertEquals(2048, sheet.getColumnWidth(8)); assertEquals(2314, sheet.getColumnWidth(9)); assertEquals(2338, sheet.getColumnWidth(10)); assertEquals(2048, sheet.getColumnWidth(11)); assertEquals(2048, sheet.getColumnWidth(12)); assertTrue(mergedRegion(sheet, 0, 0, 0, 5)); assertTrue(mergedRegion(sheet, 0, 7, 0, 12)); XSSFName name = workbook.getName(XSSFName.BUILTIN_FILTER_DB); assertEquals(0, name.getSheetIndex()); assertEquals("Sheet0!$A$1:$M$2", name.getRefersToFormula()); } finally { inputStream.close(); } }