List of usage examples for org.apache.poi.xssf.usermodel XSSFFont setFontName
public void setFontName(String name)
From source file:Dao.XlsBillDao.java
public void GenFullXLS(String pono, String relpath) { try {/*w ww .ja v a 2s. co m*/ //0.Declare Variables for Sheet //DB Variable // pono = "WO/2015/2005"; //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //1.Get Connection and Fetch Data ArrayList<WorkItemBean> wi1 = new ArrayList<WorkItemBean>(); WorkDao wdao1 = new WorkDao(); wi1 = wdao1.getWOItem(pono); //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet("WorkOrder Detail"); // spreadsheet.protectSheet("kandarpCBA"); // spreadsheet.setColumnWidth(0, 255); //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("WORK ORDER NO : " + pono); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); cell = row.createCell(0); cell.setCellValue("LINE_NO"); cell.setCellStyle(xstyle); cell = row.createCell(1); cell.setCellValue("ITEM_ID"); cell.setCellStyle(xstyle); cell = row.createCell(2); cell.setCellValue("DESCRIPTION"); cell.setCellStyle(xstyle); cell = row.createCell(3); cell.setCellValue("UOM"); cell.setCellStyle(xstyle); cell = row.createCell(4); cell.setCellValue("QTY"); cell.setCellStyle(xstyle); cell = row.createCell(5); cell.setCellValue("RATE"); cell.setCellStyle(xstyle); cell = row.createCell(6); cell.setCellValue("NOTE"); cell.setCellStyle(xstyle); int i = 2; for (WorkItemBean w : wi1) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(w.getLINE_NO()); cell = row.createCell(1); cell.setCellValue(w.getITEM_ID()); cell = row.createCell(2); cell.setCellValue(w.getITEM_DESC()); cell = row.createCell(3); cell.setCellValue(w.getUOM()); cell = row.createCell(4); cell.setCellValue(w.getQTY()); cell = row.createCell(5); cell.setCellValue(w.getRATE()); cell = row.createCell(6); cell.setCellValue(w.getCMT()); i++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + "_Items" + ".xlsx")); FileOutputStream out = new FileOutputStream( new File(relpath + "uxls//" + pono.replace("/", "-") + "_Items" + ".xlsx")); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "{0}uxls//{1}" + "_Items" + ".xlsx", new Object[] { relpath, pono.replace("/", "-") }); } catch (FileNotFoundException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:Dao.XlsBillDao.java
public void GenXLS(String pono, String relpath) { try {/*from w w w. j a v a 2 s .c om*/ //0.Declare Variables for Sheet //DB Variable // pono = "WO/2015/2005"; String sql; Connection con; PreparedStatement ps; ResultSet rs; //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //1.Get Connection and Fetch Data con = DBmanager.GetConnection(); sql = "SELECT DISTINCT cba_wo_item.line_no, cba_wo_item.item_id,\n" + " mtl_system_items.description, cba_wo_item.uom,\n" + " cba_wo_item.qty, cba_wo_item.rate, cba_wo_item.cmt,\n" + " cba_wo_item.plant, cba_wo_item.proj, cba_wo_item.task," + " cba_wo_item.po_no\n" + " FROM cba_wo_item, mtl_system_items\n" + " WHERE ( (cba_wo_item.item_id = mtl_system_items.segment1)\n" + " AND (mtl_system_items.organization_id = 0)\n" + " AND (cba_wo_item.po_no = '" + pono + "')\n" + " )\n" + " ORDER BY cba_wo_item.line_no"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet("WorkOrder Detail"); //spreadsheet.protectSheet("kandarpCBA"); //spreadsheet.setColumnWidth(0, 255); //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); // borderStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex()); // borderStyle.setFillPattern(CellStyle.ALIGN_FILL); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue("WORK ORDER NO : " + pono + " Note : If WO is with project information,each bill item should have project and task"); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); cell = row.createCell(0); cell.setCellValue("LINE_NO"); cell.setCellStyle(xstyle); cell = row.createCell(1); cell.setCellValue("ITEM_ID"); cell.setCellStyle(xstyle); cell = row.createCell(2); cell.setCellValue("DESCRIPTION"); cell.setCellStyle(xstyle); cell = row.createCell(3); cell.setCellValue("UOM"); cell.setCellStyle(xstyle); cell = row.createCell(4); cell.setCellValue("QTY"); cell.setCellStyle(xstyle); cell = row.createCell(5); cell.setCellValue("RATE"); cell.setCellStyle(xstyle); cell = row.createCell(6); cell.setCellValue("WO NOTE"); cell.setCellStyle(xstyle); cell = row.createCell(7); cell.setCellValue("PLANT"); cell.setCellStyle(xstyle); cell = row.createCell(8); cell.setCellValue("COST CENTER"); cell.setCellStyle(xstyle); cell = row.createCell(9); cell.setCellValue("PROJECT"); cell.setCellStyle(xstyle); cell = row.createCell(10); cell.setCellValue("TASK"); cell.setCellStyle(xstyle); cell = row.createCell(11); cell.setCellValue("HERE ADD NOTE"); cell.setCellStyle(xstyle); //Itrate or Database data and write int i = 2; while (rs.next()) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(rs.getString(1)); cell = row.createCell(1); cell.setCellValue(rs.getString(2)); cell = row.createCell(2); cell.setCellValue(rs.getString(3)); cell = row.createCell(3); cell.setCellValue(rs.getString(4)); cell = row.createCell(4); cell.setCellValue(rs.getString(6)); cell = row.createCell(5); cell.setCellValue(rs.getString(5)); cell = row.createCell(6); cell.setCellValue(""); cell = row.createCell(7); cell.setCellValue(rs.getString(7)); cell = row.createCell(8); cell.setCellValue(rs.getString(8)); cell = row.createCell(9); cell.setCellValue(rs.getString(9)); cell = row.createCell(10); cell.setCellValue(rs.getString(10)); cell = row.createCell(11); cell.setCellValue(""); i++; } //SECOND WORKSHEET FOR COST CENTER AND PLANT DETAIL XSSFRow row2; XSSFCell cell2; XSSFSheet ccsheet = workbook.createSheet("Cost Center"); row2 = ccsheet.createRow(0); cell2 = row2.createCell(0); cell2.setCellValue("Cost Center name and code. Please enter only code in excel"); cell2.setCellStyle(borderStyle); ccsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); row2 = ccsheet.createRow(1); cell2 = row2.createCell(0); cell2.setCellValue("CODE"); cell2.setCellStyle(xstyle); cell2 = row2.createCell(1); cell2.setCellValue("NAME"); cell2.setCellStyle(xstyle); con = DBmanager.GetConnection(); sql = "select cc,plant from cba_cc_mst"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); int i2 = 2; while (rs.next()) { row2 = ccsheet.createRow(i2); cell2 = row2.createCell(0); cell2.setCellValue(rs.getString(1)); cell2 = row2.createCell(1); cell2.setCellValue(rs.getString(2)); i2++; } //THIRD SHEET //SECOND WORKSHEET FOR COST CENTER AND PLANT DETAIL XSSFRow row3; XSSFCell cell3; XSSFSheet plantsheet = workbook.createSheet("Plant Center"); row3 = plantsheet.createRow(0); cell3 = row3.createCell(0); cell3.setCellValue("Plant Center name and code. Please enter only code in excel"); cell3.setCellStyle(borderStyle); plantsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); row3 = plantsheet.createRow(1); cell3 = row3.createCell(0); cell3.setCellValue("CODE"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(1); cell3.setCellValue("NAME"); cell3.setCellStyle(xstyle); con = DBmanager.GetConnection(); sql = "select cc,plant from cba_plant_mst"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); int i3 = 2; while (rs.next()) { row3 = plantsheet.createRow(i3); cell3 = row3.createCell(0); cell3.setCellValue(rs.getString(1)); cell3 = row3.createCell(1); cell3.setCellValue(rs.getString(2)); i3++; } //SHEET 3 HEADER //row1 XSSFSheet spreadsheet4 = workbook.createSheet("Project And Task"); XSSFRow row4 = spreadsheet4.createRow(0); XSSFCell cell4 = row4.createCell(0); cell4.setCellValue("Note : Please copy project,task code and paste into 1 sheet"); spreadsheet4.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row4 = spreadsheet4.createRow(1); cell4 = row4.createCell(0); cell4.setCellValue("PROJECT CODE"); cell4.setCellStyle(xstyle); cell4 = row4.createCell(1); cell4.setCellValue("PROJECT NAME"); cell4.setCellStyle(xstyle); cell4 = row4.createCell(2); cell4.setCellValue("TASK CODE"); cell4.setCellStyle(xstyle); //SHEET 3 DATA int j = 2; ArrayList<WorkItemBean> wi1 = Dropdown.LoadProjTaskMst("123"); for (WorkItemBean w : wi1) { row4 = spreadsheet4.createRow(j); cell4 = row4.createCell(0); cell4.setCellValue(w.getPROJ()); cell4 = row4.createCell(1); cell4.setCellValue(w.getPROJ_NAME()); cell4 = row4.createCell(2); cell4.setCellValue(w.getTASK()); j++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + ".xlsx")); FileOutputStream out = new FileOutputStream( new File(relpath + "xls//" + pono.replace("/", "-") + ".xlsx")); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "{0}xls//{1}.xlsx", new Object[] { relpath, pono.replace("/", "-") }); } catch (SQLException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:Dao.XlsWoDao.java
public void GenXLS(String orgId, String relpath) { try {//from w w w. j av a 2s. com //2.Create WorkBook and Sheet XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet1 = workbook.createSheet("WorkOrder Detail"); XSSFSheet spreadsheet2 = workbook.createSheet("Plant Master"); XSSFSheet spreadsheet3 = workbook.createSheet("Project and Task Master"); //style XSSFFont 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); XSSFCellStyle xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //SHEET 1 HEADER //1row XSSFRow row1 = spreadsheet1.createRow(0); XSSFCell cell1 = row1.createCell(0); cell1.setCellValue("Note : If you are not sure " + "about plant,project,task please " + "leave it blank." + "It can be add when release bill"); spreadsheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 15)); //2row row1 = spreadsheet1.createRow(1); cell1 = row1.createCell(0); cell1.setCellValue("Create Work Order Template"); cell1.setCellStyle(borderStyle); spreadsheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); //3row row1 = spreadsheet1.createRow(2); cell1 = row1.createCell(0); cell1.setCellValue("ITEM_NO"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(1); cell1.setCellValue("RATE"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(2); cell1.setCellValue("PLANT"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(3); cell1.setCellValue("PROJECT"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(4); cell1.setCellValue("TASK"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(5); cell1.setCellValue("ADDITIONAL NOTE"); cell1.setCellStyle(xstyle); //SHEET 2 HEADER //row1 XSSFRow row2 = spreadsheet2.createRow(0); XSSFCell cell2 = row2.createCell(0); cell2.setCellValue("Note : Please copy plant code and paste into 1 sheet"); spreadsheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row2 = spreadsheet2.createRow(1); cell2 = row2.createCell(0); cell2.setCellValue("PLANT_CODE"); cell2.setCellStyle(xstyle); cell2 = row2.createCell(1); cell2.setCellValue("PLANT NAME"); cell2.setCellStyle(xstyle); //SHEET 2 DATA int i = 2; Map<String, String> plant = Dropdown.LoadPlantMst(); for (Map.Entry<String, String> entry : plant.entrySet()) { row2 = spreadsheet2.createRow(i); cell2 = row2.createCell(0); cell2.setCellValue(entry.getKey()); cell2 = row2.createCell(1); cell2.setCellValue(entry.getValue()); i++; } //SHEET 3 HEADER //row1 XSSFRow row3 = spreadsheet3.createRow(0); XSSFCell cell3 = row3.createCell(0); cell3.setCellValue("Note : Please copy project,task code and paste into 1 sheet"); spreadsheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row3 = spreadsheet3.createRow(1); cell3 = row3.createCell(0); cell3.setCellValue("PROJECT CODE"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(1); cell3.setCellValue("PROJECT NAME"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(2); cell3.setCellValue("TASK CODE"); cell3.setCellStyle(xstyle); //SHEET 3 DATA int j = 2; ArrayList<WorkItemBean> wi1 = Dropdown.LoadProjTaskMst(orgId); for (WorkItemBean w : wi1) { row3 = spreadsheet3.createRow(j); cell3 = row3.createCell(0); cell3.setCellValue(w.getPROJ()); cell3 = row3.createCell(1); cell3.setCellValue(w.getPROJ_NAME()); cell3 = row3.createCell(2); cell3.setCellValue(w.getTASK()); j++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + "_Items" + ".xlsx")); // FileOutputStream out = new FileOutputStream(new File(relpath + "uxls//" + "WO_Creation_Template" + ".xlsx")); FileOutputStream out = new FileOutputStream(new File(relpath)); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); } catch (FileNotFoundException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:DSC.AccountantReport.java
private static void createExcelReport() { XSSFWorkbook workbook = new XSSFWorkbook(); clients.sort(new Comparator<Client>() { @Override/*from ww w . j av a 2s.com*/ 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.ChefReport.java
public static void processChefReport() throws IOException { boolean firstFamEntry = true; String list[] = { "Standard", "Low Carb", "Kiddies" }; int excelNumber = 0; int sheetNumber = 0; for (mealCounter = 0; mealCounter < 3; mealCounter++) { workbook = new XSSFWorkbook(); sheetNumber = 0;/*from w ww. ja va 2 s .c o m*/ for (String currRoute : allRoutes) { Map<String, Object[]> data = new TreeMap<>(); data.put(0 + "", new String[] { "Doorstep Chef - Chef Report " + DriverReport.returnWeekInt(), "", "", "Meal Type : " + list[mealCounter] + " " + " " + "Route: " + sheetNumber }); data.put(1 + "", new String[] { "", "", "", "", "", "", "" }); data.put(2 + "", new String[] { "Family Size", "Quantity", "Allergies", "Exclusions" }); int counter = 3; XSSFSheet sheet = workbook.createSheet("ChefReports Route - " + sheetNumber); int rowNum = 0; int cellNum = 0; String familysize = ""; ArrayList<Meal> mealList = new ArrayList<>(); boolean hasValue = false; for (Order order : orders) { for (Meal meal : order.getMeals()) { if (meal.getMealType().equals(list[mealCounter]) && order.getRoute().equals(currRoute)) { mealList.add(meal); hasValue = true; } } } mealList.sort(new Comparator<Meal>() { @Override public int compare(Meal o1, Meal o2) { if (o1.getQuantity() < o2.getQuantity()) { return -1; } else if (o1.getQuantity() > o2.getQuantity()) { return 1; } else { return 0; } } }); if (hasValue) { int currQuantity = 0; int bulk = 0; boolean firstIterate = true; for (Meal meal : mealList) { if (meal.getQuantity() != currQuantity) { if (!firstIterate && bulk != 0) { data.put(counter + "", new String[] { familysize + " Normal *", bulk + "", "", "" }); bulk = 0; counter++; } firstIterate = false; switch (meal.getQuantity()) { case 1: familysize = "Single"; break; case 2: familysize = "Couple"; break; case 3: familysize = "Three"; break; case 4: familysize = "Four"; break; case 5: familysize = "Five"; break; case 6: familysize = "Six"; break; default: familysize = "Extra"; } currQuantity = meal.getQuantity(); } if (meal.getAllergies().equals("-") && meal.getExclusions().equals("-")) { bulk++; } else { data.put(counter + "", new String[] { familysize + " Meal", meal.getQuantity() + "", meal.getAllergies(), meal.getExclusions() }); counter++; } } } Set<String> keySet = data.keySet(); Object[] keys = data.keySet().toArray(); Arrays.sort(keys); ArrayList<Object> keyList = new ArrayList(); int longestCustomer = 5; int totalWidth = 50000; boolean isBulk = false; for (Object key : keys) { keyList.add(data.get(key)); } for (int keyIterate = 0; keyIterate < keySet.size(); keyIterate++) { Row row = sheet.createRow(rowNum); Object[] arr = data.get(keyIterate + ""); for (int i = 0; i < arr.length; i++) { XSSFFont font = workbook.createFont(); Cell cell = row.createCell(i); cell.setCellValue((String) arr[i]); XSSFCellStyle borderStyle = workbook.createCellStyle(); 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 { borderStyle.setBorderBottom(BorderStyle.THIN); borderStyle.setBorderTop(BorderStyle.THIN); borderStyle.setBorderLeft(BorderStyle.THIN); borderStyle.setBorderRight(BorderStyle.THIN); if ((arr[0] + "").contains("*")) { isBulk = true; borderStyle.setBorderBottom(BorderStyle.MEDIUM); } } 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++; } sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); for (int i = 0; i < 5; i++) { switch (i) { case 1: sheet.setColumnWidth(i, 3000); break; case 2: sheet.setColumnWidth(i, 8000); break; default: sheet.setColumnWidth(i, 4000); break; } if (i == 3) { sheet.setColumnWidth(i, 8000); } } 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, 3)); sheetNumber++; creatSheet(list[mealCounter], workbook); excelNumber++; if (excelNumber == allRoutes.size()) { if (!(DSC_Main.generateAllReports)) { chefLoadingObj.setVisible(false); chefLoadingObj.dispose(); JOptionPane.showMessageDialog(null, "Chef Reports Successfully Generated."); } } } } }
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 ww w . j av a2 s .c o m 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// w w w. j a v a 2 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/*from w ww . j a va 2s . 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 + "" }); }// w w w . j av a 2 s .com } 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:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-6] ? : ? ?(?, ? )? /*ww w. j a va2 s . co m*/ */ @Test public void testModifyCellAttribute() throws Exception { try { LOGGER.debug("testModifyCellAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellAttribute.xlsx"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new XSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? XSSFWorkbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); LOGGER.debug("testModifyCellAttribute after loadWorkbook...."); Sheet sheet = wb.createSheet("cell test sheet2"); sheet.setColumnWidth((short) 3, (short) 200); // column Width CellStyle cs = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setFontHeight(16); font.setBoldweight((short) 3); font.setFontName("fixedsys"); cs.setFont(font); cs.setAlignment(XSSFCellStyle.ALIGN_RIGHT); // cell cs.setWrapText(true); for (int i = 0; i < 100; i++) { Row row = sheet.createRow(i); row.setHeight((short) 300); // row? height for (int j = 0; j < 5; j++) { Cell cell = row.createCell(j); cell.setCellValue(new XSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? XSSFWorkbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheetT = wbT.getSheet("cell test sheet2"); LOGGER.debug("getNumCellStyles : {}", wbT.getNumCellStyles()); XSSFCellStyle cs1 = (XSSFCellStyle) wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1)); XSSFFont fontT = cs1.getFont(); LOGGER.debug("font getFontHeight : {}", fontT.getFontHeight()); LOGGER.debug("font getBoldweight : {}", fontT.getBoldweight()); LOGGER.debug("font getFontName : {}", fontT.getFontName()); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); LOGGER.debug("getWrapText : {}", cs1.getWrapText()); for (int i = 0; i < 100; i++) { Row row1 = sheetT.getRow(i); for (int j = 0; j < 5; j++) { Cell cell1 = row1.getCell(j); LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue()); assertEquals(320, fontT.getFontHeight()); assertEquals(400, fontT.getBoldweight()); LOGGER.debug("fontT.getBoldweight()? ? 400? ?"); assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); assertTrue(cs1.getWrapText()); } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellAttribute end...."); } }