List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setFillPattern
@Override public void setFillPattern(FillPatternType pattern)
From source file:DSC.NewClientReport.java
private static void createExcelReport() { XSSFWorkbook workbook = new XSSFWorkbook(); clients.sort(new Comparator<Client>() { @Override//w w w .j a va2 s .co 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/*from w ww . j a v a 2 s . com*/ 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 ww . j av a2 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:ec.sirec.web.impuestos.GestionAlcabalasControlador.java
public void postProcessXLS(Object document) { XSSFWorkbook wb = (XSSFWorkbook) document; XSSFSheet sheet = wb.getSheetAt(0); //Creo variable hoja ()contiene los atributos para la hoja de calculo List<String> encabezadoColumna = new ArrayList<String>(); for (Row row : sheet) { //Recorre los valores de la fila 1 (encabezado) pero en dataTable=0 if (row.getRowNum() == 0) { for (Cell cell : row) { encabezadoColumna.add(cell.getStringCellValue() + " "); }//from w w w. j a va2 s . co m } else { break; } } //----inicio crea estilo XSSFCellStyle style = wb.createCellStyle(); //Se crea el estilo XSSFFont font = wb.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.WHITE.getIndex()); style.setFont(font); byte[] rgb = new byte[3]; rgb[0] = (byte) 076; rgb[1] = (byte) 145; rgb[2] = (byte) 065; XSSFColor myColor = new XSSFColor(rgb); style.setFillForegroundColor(myColor); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); XSSFRow row0 = sheet.createRow((short) 0); //Creo una fila en la posicion 0 //----fin crea estilo for (int i = 0; i <= encabezadoColumna.size() - 1; i++) { createCell(row0, i, encabezadoColumna.get(i), style); //agrego celdas en la posicion indicada con los valores de los encabezados } //Ajusta el ancho de las columnas for (int i = 0; i < 20; i++) { sheet.autoSizeColumn((short) i); } }
From source file:edu.vt.vbi.patric.common.ExcelHelper.java
License:Apache License
/** * This method creates a map of Cellstyle objects for page building. Note: this method used for XSSF pages * /*from ww w .j a v a 2s . c om*/ * @return hashmap of styles */ private Map<String, XSSFCellStyle> createXStyles() { XSSFCellStyle style = (XSSFCellStyle) createBorderStyle(); // create style for cells in header row Font headerFont = xwb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, 52, 94))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); style.setFont(headerFont); xstyles.put("header", style); // create styles for alternating background color cells Font bodyFont = xwb.createFont(); bodyFont.setFontHeightInPoints((short) 8); style = (XSSFCellStyle) createBorderStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(230, 240, 248))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(bodyFont); style.setWrapText(true); xstyles.put("bg2", style); style = (XSSFCellStyle) createBorderStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(bodyFont); xstyles.put("bg1", style); style = (XSSFCellStyle) createBorderStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 193, 193))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(bodyFont); style.setWrapText(true); xstyles.put("empty", style); return xstyles; }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *///www. ja v a 2s . c om @Test public void testWriteExcelFileAttribute() throws Exception { try { LOGGER.debug("testWriteExcelFileAttribute start...."); short rowheight = 40 * 10; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); XSSFCellStyle cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(CellStyle.ALIGN_RIGHT); cs.setFillPattern(CellStyle.DIAMONDS); // ? XSSFRow r1 = sheet1.createRow(0); r1.createCell(0); // ? ? cs.setFillForegroundColor(IndexedColors.BLUE.getIndex()); // cs.setFillBackgroundColor(IndexedColors.RED.getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); LOGGER.debug("getFillPattern : {}", cs1.getFillPattern()); assertEquals(XSSFCellStyle.DIAMONDS, cs1.getFillPattern()); LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor()); LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor()); LOGGER.debug( "XSSFWorkbook.getFillBackgroundColor(), XSSFColor().getIndexed() ? ? 0 ? ?"); assertEquals(IndexedColors.BLUE.getIndex(), cs1.getFillForegroundColor()); assertEquals(IndexedColors.RED.getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testWriteExcelFileAttribute end...."); } }
From source file:es.tena.foundation.util.POIUtil.java
/** * Crea un map con los estilos para cada tipo de datos * * @param wb/* w w w . j a v a 2 s. co m*/ * @return un hashMap con las claves 'porcentaje', 'coeficiente', 'moneda', * 'fecha' y 'cabecera' */ public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) { Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>(); XSSFDataFormat fmt = wb.createDataFormat(); XSSFCellStyle style1 = wb.createCellStyle(); style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style1.setDataFormat(fmt.getFormat("0.0%")); styles.put("porcentaje", style1); XSSFCellStyle style2 = wb.createCellStyle(); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setDataFormat(fmt.getFormat("0.0X")); styles.put("coeficiente", style2); XSSFCellStyle style3 = wb.createCellStyle(); style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style3.setDataFormat(fmt.getFormat("#,##0.00")); styles.put("moneda", style3); XSSFCellStyle style4 = wb.createCellStyle(); style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style4.setDataFormat(fmt.getFormat("mmm dd")); styles.put("fecha", style4); XSSFCellStyle style5 = wb.createCellStyle(); XSSFFont headerFont = wb.createFont(); headerFont.setBold(true); style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style5.setFont(headerFont); styles.put("cabecera", style5); return styles; }
From source file:gov.anl.cue.arcane.engine.matrix.MatrixEngine.java
License:Open Source License
/** * Creates the row header.//from w w w . ja v a 2 s . c o m * * @param workbook the workbook * @param sheet the sheet * @param rowIndex the row index * @param label1 the label1 * @param label2 the label2 */ public void createRowHeader(XSSFWorkbook workbook, XSSFSheet sheet, int rowIndex, String label1, String label2) { // Create the needed highlight style. XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(197, 217, 241))); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(font); // Create the requested row. XSSFRow row = sheet.createRow(0); XSSFCell cell = row.createCell(0); cell.setCellValue(label1); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(label2); cell.setCellStyle(cellStyle); }
From source file:gov.anl.cue.arcane.engine.matrix.MatrixModel.java
License:Open Source License
/** * Write.//from w w w.j a v a 2s . co m * * @param fileName the file name * @return true, if successful */ public boolean write(String fileName) { // Check for errors. try { // Create the requested workbook image. XSSFWorkbook workbook = new XSSFWorkbook(); // Create the needed highlight style. XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(197, 217, 241))); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(font); // Fill in the fitness sheet. this.writeFitnessFunctionInformation(workbook, cellStyle); // Fill in the variables. this.writeScanVariables(workbook, cellStyle); // Attempt to create the requested file from the workbook image. FileOutputStream file = new FileOutputStream(fileName); workbook.write(file); file.close(); // Catch errors. } catch (Exception e) { // Note the error. return false; } // Return the default results. return true; }
From source file:htmlparser.xls.XLSFile.java
public void createScoreTable(XSSFColor oddrow_color, XSSFColor title_bg_color, XSSFColor title_font_color, int highlight) { String sheetname = WorkbookUtil.createSafeSheetName(this.parser.getCompetitionName()); this.scoresheet = this.excelfile.createSheet(sheetname); CreationHelper createHelper = this.excelfile.getCreationHelper(); CellStyle cellStyle = this.excelfile.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); int rows = 0; Row headline = this.scoresheet.createRow(rows); Cell cheadline = headline.createCell(0); cheadline.setCellValue(createHelper.createRichTextString(this.parser.getCompetitionName())); XSSFCellStyle customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); XSSFFont fh = (XSSFFont) this.excelfile.createFont(); fh.setFontHeightInPoints((short) 16); fh.setBoldweight(Font.BOLDWEIGHT_BOLD); fh.setColor(title_bg_color);//from www. j a v a 2 s . c om customstyle.setFont(fh); cheadline.setCellStyle(customstyle); int length = this.parser.getTeams().get(0).getData().size(); CellRangeAddress headrow = new CellRangeAddress(rows, rows, 0, length); this.scoresheet.addMergedRegion(headrow); RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); RegionUtil.setBorderRight(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); RegionUtil.setBorderTop(CellStyle.BORDER_THIN, headrow, this.scoresheet, this.excelfile); RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), headrow, this.scoresheet, this.excelfile); rows++; Row colNms = this.scoresheet.createRow(rows++); customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(title_bg_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont f1 = (XSSFFont) this.excelfile.createFont(); f1.setColor(title_font_color); f1.setBoldweight(Font.BOLDWEIGHT_BOLD); customstyle.setFont(f1); int cCN = 0; for (String s : this.shColNms) { Cell c = colNms.createCell(cCN); c.setCellValue(createHelper.createRichTextString(s)); c.setCellStyle(customstyle); cCN++; } double ordNum = 1; customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(cellStyle); customstyle.setFillForegroundColor(oddrow_color); customstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); for (Team t : this.parser.getTeams()) { Row r = this.scoresheet.createRow(rows++); int cell = 0; Cell order = r.createCell(cell++); order.setCellValue(ordNum++); if (rows % 2 == 0) order.setCellStyle(customstyle); else order.setCellStyle(cellStyle); for (String s : t.getData()) { Cell c = r.createCell(cell); c.setCellValue(createHelper.createRichTextString(s)); if (rows % 2 == 0) c.setCellStyle(customstyle); else c.setCellStyle(cellStyle); cell++; } } for (int i = 0; i <= length; i++) { this.scoresheet.autoSizeColumn(i); } if (highlight >= 0) { highlight += 2; Row r = this.scoresheet.getRow(highlight); customstyle = (XSSFCellStyle) this.excelfile.createCellStyle(); customstyle.cloneStyleFrom(r.getCell(0).getCellStyle()); Font bold = this.excelfile.createFont(); bold.setBoldweight(Font.BOLDWEIGHT_BOLD); customstyle.setFont(bold); for (Cell c : r) { c.setCellStyle(customstyle); } } }