List of usage examples for org.apache.poi.ss.usermodel Font setBold
public void setBold(boolean bold);
From source file:com.guardias.excel.CalendarToExcel.java
License:Apache License
/** * cell styles used for formatting calendar sheets *//* w ww . ja v a 2 s. co m*/ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); short borderColor = IndexedColors.GREY_50_PERCENT.getIndex(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 48); titleFont.setColor(IndexedColors.DARK_BLUE.getIndex()); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 12); monthFont.setColor(IndexedColors.WHITE.getIndex()); monthFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(monthFont); styles.put("month", style); Font dayFont = wb.createFont(); dayFont.setFontHeightInPoints((short) 14); dayFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderLeft(BorderStyle.THIN); style.setLeftBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); style.setFont(dayFont); styles.put("weekend_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("weekend_right", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setLeftBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); style.setFont(dayFont); styles.put("workday_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("workday_right", style); style = wb.createCellStyle(); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("grey_left", style); style = wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("grey_right", style); return styles; }
From source file:com.liferay.dynamic.data.lists.internal.exporter.DDLXLSExporter.java
License:Open Source License
protected CellStyle createCellStyle(Workbook workbook, boolean bold, String fontName, short heightInPoints) { Font font = workbook.createFont(); font.setBold(bold); font.setFontHeightInPoints(heightInPoints); font.setFontName(fontName);//from w ww. j a v a 2 s.co m CellStyle style = workbook.createCellStyle(); style.setFont(font); return style; }
From source file:com.lufs.java.apache.poi.example.CalendarDemo.java
License:Apache License
/** * cell styles used for formatting calendar sheets *///from w ww. j av a2 s. c o m private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<>(); short borderColor = IndexedColors.GREY_50_PERCENT.getIndex(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 48); titleFont.setColor(IndexedColors.DARK_BLUE.getIndex()); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 12); monthFont.setColor(IndexedColors.WHITE.getIndex()); monthFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(monthFont); styles.put("month", style); Font dayFont = wb.createFont(); dayFont.setFontHeightInPoints((short) 14); dayFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderLeft(BorderStyle.THIN); style.setLeftBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); style.setFont(dayFont); styles.put("weekend_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("weekend_right", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setVerticalAlignment(VerticalAlignment.TOP); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setLeftBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); style.setFont(dayFont); styles.put("workday_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.TOP); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("workday_right", style); style = wb.createCellStyle(); style.setBorderLeft(BorderStyle.THIN); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("grey_left", style); style = wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderRight(BorderStyle.THIN); style.setRightBorderColor(borderColor); style.setBorderBottom(BorderStyle.THIN); style.setBottomBorderColor(borderColor); styles.put("grey_right", style); return styles; }
From source file:com.lwr.software.reporter.restservices.ReportExportService.java
License:Open Source License
public Response exportExcel(Report toExport, Set<ReportParameter> reportParams) { Workbook wb = new XSSFWorkbook(); Font boldFont = wb.createFont(); boldFont.setBold(true); CellStyle headerStyle = wb.createCellStyle(); headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBorderTop(BorderStyle.THIN); headerStyle.setBorderRight(BorderStyle.THIN); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex()); headerStyle.setFont(boldFont);//from www.j a v a 2 s. c o m CellStyle cellStyle = wb.createCellStyle(); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); CellStyle titleStyle = wb.createCellStyle(); titleStyle.setBorderBottom(BorderStyle.THIN); titleStyle.setBorderTop(BorderStyle.THIN); titleStyle.setBorderRight(BorderStyle.THIN); titleStyle.setBorderLeft(BorderStyle.THIN); List<RowElement> rows = toExport.getRows(); int sheetIndex = 0; for (RowElement rowElement : rows) { List<Element> elements = rowElement.getElements(); for (Element element : elements) { try { element.setParams(reportParams); element.init(); } catch (Exception e) { logger.error("Unable to init '" + element.getTitle() + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage(), e); return Response.serverError().entity("Unable to init '" + element.getTitle() + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage()).build(); } String sheetName = element.getTitle().substring(0, element.getTitle().length() > 30 ? 30 : element.getTitle().length()) + (sheetIndex++); Sheet sheet = wb.createSheet(sheetName); Row reportTitleRow = sheet.createRow(0); Cell reportTitleHeader = reportTitleRow.createCell(0); reportTitleHeader.setCellStyle(headerStyle); reportTitleHeader.setCellValue("Report Title:"); Cell reportTitleCell = reportTitleRow.createCell(1); reportTitleCell.setCellStyle(titleStyle); reportTitleCell.setCellValue(toExport.getTitle()); Row elementTitleRow = sheet.createRow(1); Cell elementTitleHeader = elementTitleRow.createCell(0); elementTitleHeader.setCellStyle(headerStyle); elementTitleHeader.setCellValue("Element Title:"); Cell elementTitleCell = elementTitleRow.createCell(1); elementTitleCell.setCellStyle(titleStyle); elementTitleCell.setCellValue(element.getTitle()); List<List<Object>> dataToExport = element.getData(); int rowIndex = 3; Row headerRow = sheet.createRow(rowIndex++); List<Object> unifiedHeaderRow = element.getHeader(); for (int i = 0; i < unifiedHeaderRow.size(); i++) { Cell headerCell = headerRow.createCell(i); String headerCellValue = unifiedHeaderRow.get(i).toString(); headerCell.setCellValue(headerCellValue); headerCell.setCellStyle(headerStyle); } for (int i = 0; i < dataToExport.size(); i++) { Row row = sheet.createRow(rowIndex++); List<Object> unifiedRow = dataToExport.get(i); int cellIndex = 0; for (Object cellValue : unifiedRow) { Cell cell = row.createCell(cellIndex); cell.setCellStyle(cellStyle); try { double val = Double.parseDouble(cellValue.toString()); cell.setCellValue(val); } catch (NumberFormatException e) { cell.setCellValue(cellValue.toString()); } cellIndex++; } } } } try { File file = new File(DashboardConstants.APPLN_TEMP_DIR + System.nanoTime()); logger.info("Export CSV temp file path is " + file.getAbsoluteFile()); wb.write(new FileOutputStream(file)); wb.close(); ResponseBuilder responseBuilder = Response.ok((Object) file); responseBuilder.header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); responseBuilder.header("Content-Transfer-Encoding", "binary"); responseBuilder.header("Content-Disposition", "attachment;filename=" + file.getName()); responseBuilder.header("Content-Length", file.length()); Response responseToSend = responseBuilder.build(); file.deleteOnExit(); return responseToSend; } catch (Exception e1) { return Response.serverError() .entity("Unable to export " + toExport.getTitle() + " report " + e1.getMessage()).build(); } }
From source file:com.onsemi.cdars.config.FtpConfigUSL24hrs.java
public void cronRun() throws FileNotFoundException, IOException { LOGGER.info(//from w ww. j a va 2 s.c om "Upper Spec Limit (USL Shipping) executed at everyday on 8:00 am. Current time is : " + new Date()); String username = System.getProperty("user.name"); if (!"fg79cj".equals(username)) { username = "imperial"; } DateFormat dateFormat = new SimpleDateFormat("ddMMMyyyy"); Date date = new Date(); String todayDate = dateFormat.format(date); String reportName = "C:\\Users\\" + username + "\\Documents\\CDARS\\HIMS USL for Sending to SBN Factory Report (" + todayDate + ").xls"; FileOutputStream fileOut = new FileOutputStream(reportName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("HIMS PROCESS EXCEED USL"); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName(HSSFFont.FONT_ARIAL); font.setBoldweight(HSSFFont.COLOR_NORMAL); font.setBold(true); font.setColor(HSSFColor.DARK_BLUE.index); style.setFont(font); sheet.createFreezePane(0, 1); // Freeze 1st Row HSSFRow rowhead = sheet.createRow((short) 0); rowhead.setRowStyle(style); HSSFCell cell1_0 = rowhead.createCell(0); cell1_0.setCellStyle(style); cell1_0.setCellValue("HARDWARE TYPE"); HSSFCell cell1_1 = rowhead.createCell(1); cell1_1.setCellStyle(style); cell1_1.setCellValue("HARDWARE ID"); HSSFCell cell1_2 = rowhead.createCell(2); cell1_2.setCellStyle(style); cell1_2.setCellValue("MATERIAL PASS NO"); HSSFCell cell1_3 = rowhead.createCell(3); cell1_3.setCellStyle(style); cell1_3.setCellValue("DURATION"); HSSFCell cell1_4 = rowhead.createCell(4); cell1_4.setCellStyle(style); cell1_4.setCellValue("CURRENT STATUS"); // WhUSLDAO whUslDAO = new WhUSLDAO(); // List<WhUSL> whUslList = whUslDAO.getWhUSLLog(); String materialPassNo = ""; String hardwareId = ""; String hardwareType = ""; String duration = ""; String status = ""; String text = ""; WhStatusLogDAO statusD = new WhStatusLogDAO(); List<WhStatusLog> whUslList = statusD.getTLReqToApproveAndApproveToMpCreatedList(); boolean checksize1 = false; boolean checksize2 = false; for (int i = 0; i < whUslList.size(); i++) { checksize1 = true; hardwareType = whUslList.get(i).getEquipmentType(); hardwareId = whUslList.get(i).getEquipmentId(); materialPassNo = whUslList.get(i).getMpNo(); String hourReqApp = whUslList.get(i).getRequestToApprove24(); String hourReqAppIfNull = whUslList.get(i).getRequestToApproveTemp24(); String hourAppMp = whUslList.get(i).getApproveToMPCreated24(); String hourAppMpIfNull = whUslList.get(i).getApproveToMPCreatedTemp24(); boolean flag = false; if (hourReqAppIfNull != null) { if (Integer.parseInt(hourReqAppIfNull) >= 24 && hourReqApp == null) { duration = whUslList.get(i).getRequestToApproveTemp(); status = "Pending Approval"; flag = true; } } if (hourAppMpIfNull != null) { if (Integer.parseInt(hourAppMpIfNull) >= 24 && hourAppMp == null && hourReqApp != null) { duration = whUslList.get(i).getApproveToMPCreatedTemp(); status = "Pending Material Pass Number"; flag = true; } } if (flag == true) { HSSFRow contents = sheet.createRow(sheet.getLastRowNum() + 1); // HSSFCell cell2_0 = contents.createCell(0); cell2_0.setCellValue(hardwareType); HSSFCell cell2_1 = contents.createCell(1); cell2_1.setCellValue(hardwareId); HSSFCell cell2_2 = contents.createCell(2); cell2_2.setCellValue(materialPassNo); HSSFCell cell2_3 = contents.createCell(3); cell2_3.setCellValue(duration); HSSFCell cell2_4 = contents.createCell(4); cell2_4.setCellValue(status); } } WhStatusLogDAO statusD2 = new WhStatusLogDAO(); List<WhStatusLog> whUslList2 = statusD2.getTLMpCreatedToFinalInventoryDateList(); for (int i = 0; i < whUslList2.size(); i++) { checksize2 = true; hardwareType = whUslList2.get(i).getEquipmentType(); hardwareId = whUslList2.get(i).getEquipmentId(); materialPassNo = whUslList2.get(i).getMpNo(); String hourMpTt = whUslList2.get(i).getMpCreatedToTtScan24(); String hourMpTtIfNull = whUslList2.get(i).getMpCreatedToTtScanTemp24(); String hourTtBs = whUslList2.get(i).getTtScanToBsScan24(); String hourTtBsIfNull = whUslList2.get(i).getTtScanToBsScanTemp24(); String hourBsShip = whUslList2.get(i).getBsScanToShip24(); String hourBsShipIfNull = whUslList2.get(i).getBsScanToShipTemp24(); String hourShipInv = whUslList2.get(i).getShipToInventory24(); String hourShipInvIfNull = whUslList2.get(i).getShipToInventoryTemp24(); boolean flag = false; if (hourMpTtIfNull != null) { if (Integer.parseInt(hourMpTtIfNull) >= 24 && hourMpTt == null) { duration = whUslList2.get(i).getMpCreatedToTtScanTemp(); status = "Pending Trip Ticket Scanning"; flag = true; } } if (hourTtBsIfNull != null) { if (Integer.parseInt(hourTtBsIfNull) >= 24 && hourTtBs == null && hourMpTt != null) { duration = whUslList2.get(i).getTtScanToBsScanTemp(); status = "Pending Barcode Sticker Scanning"; flag = true; } } if (hourBsShipIfNull != null) { if (Integer.parseInt(hourBsShipIfNull) >= 24 && hourBsShip == null && hourTtBs != null && hourMpTt != null) { duration = whUslList2.get(i).getBsScanToShipTemp(); status = "Pending Shipping Packing List"; flag = true; } } if (hourShipInvIfNull != null) { if (Integer.parseInt(hourShipInvIfNull) >= 24 && hourShipInv == null && hourBsShip != null && hourTtBs != null && hourMpTt != null) { duration = whUslList2.get(i).getShipToInventoryTemp(); status = "Pending Inventory in Seremban Factory"; flag = true; } } if (flag == true) { HSSFRow contents = sheet.createRow(sheet.getLastRowNum() + 1); // HSSFCell cell2_0 = contents.createCell(0); cell2_0.setCellValue(hardwareType); HSSFCell cell2_1 = contents.createCell(1); cell2_1.setCellValue(hardwareId); HSSFCell cell2_2 = contents.createCell(2); cell2_2.setCellValue(materialPassNo); HSSFCell cell2_3 = contents.createCell(3); cell2_3.setCellValue(duration); HSSFCell cell2_4 = contents.createCell(4); cell2_4.setCellValue(status); } } if (checksize1 == true || checksize2 == true) { workbook.write(fileOut); workbook.close(); //send email LOGGER.info("send email to person in charge"); EmailSender emailSender = new EmailSender(); com.onsemi.cdars.model.User user = new com.onsemi.cdars.model.User(); user.setFullname("All"); List<String> a = new ArrayList<String>(); String emailApprover = ""; String emaildistList1 = ""; String emaildistList2 = ""; String emaildistList3 = ""; String emaildistList4 = ""; emailApprover = "fg79cj@onsemi.com"; a.add(emailApprover); EmailConfigDAO econfD = new EmailConfigDAO(); int countDistList1 = econfD.getCountTask("Dist List 1"); if (countDistList1 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList1 = econfD.getEmailConfigByTask("Dist List 1"); emaildistList1 = distList1.getEmail(); a.add(emaildistList1); } econfD = new EmailConfigDAO(); int countDistList2 = econfD.getCountTask("Dist List 2"); if (countDistList2 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList2 = econfD.getEmailConfigByTask("Dist List 2"); emaildistList2 = distList2.getEmail(); a.add(emaildistList2); } econfD = new EmailConfigDAO(); int countDistList3 = econfD.getCountTask("Dist List 3"); if (countDistList3 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList3 = econfD.getEmailConfigByTask("Dist List 3"); emaildistList3 = distList3.getEmail(); a.add(emaildistList3); } econfD = new EmailConfigDAO(); int countDistList4 = econfD.getCountTask("Dist List 4"); if (countDistList4 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList4 = econfD.getEmailConfigByTask("Dist List 4"); emaildistList4 = distList4.getEmail(); a.add(emaildistList4); } String[] myArray = new String[a.size()]; String[] emailTo = a.toArray(myArray); // String[] to = {"fg79cj@onsemi.com"}; emailSender.htmlEmailWithAttachment(servletContext, user, //user name requestor emailTo, new File("C:\\Users\\" + username + "\\Documents\\CDARS\\HIMS USL for Sending to SBN Factory Report (" + todayDate + ").xls"), "List of Hardware Exceed USL (24 hours) for Sending to SBN Factory", //subject "Report for Hardware Process from HIMS(Hadware Sending to SBN Factory) that exceed Upper Specs Limit (24 hours) has been made. <br />" + "Hence, attached is the report file for your view and perusal. <br /><br />" + "<br /><br /> " + "<style>table, th, td {border: 1px solid black;} </style>" + "<table style=\"width:100%\">" //tbl + "<tr>" + "<th>HARDWARE TYPE</th> " + "<th>HARDWARE ID</th> " + "<th>MATERIAL PASS NO.</th>" + "<th>DURATION</th>" + "<th>CURRENT STATUS</th>" + "</tr>" + table() + "</table>" + "<br />Thank you." //msg ); } // } }
From source file:com.onsemi.cdars.config.FtpConfigUSL24hrs.java
public void cronRun2() throws FileNotFoundException, IOException { LOGGER.info("Upper Spec Limit (USL Retrieval) executed at everyday on 8:00 am. Current time is : " + new Date()); String username = System.getProperty("user.name"); if (!"fg79cj".equals(username)) { username = "imperial"; }/*w w w . j a v a 2s . c om*/ DateFormat dateFormat = new SimpleDateFormat("ddMMMyyyy"); Date date = new Date(); String todayDate = dateFormat.format(date); String reportName = "C:\\Users\\" + username + "\\Documents\\CDARS\\HIMS USL for Retrieving from SBN Factory Report (" + todayDate + ").xls"; FileOutputStream fileOut = new FileOutputStream(reportName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("HIMS PROCESS EXCEED USL"); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName(HSSFFont.FONT_ARIAL); font.setBoldweight(HSSFFont.COLOR_NORMAL); font.setBold(true); font.setColor(HSSFColor.DARK_BLUE.index); style.setFont(font); sheet.createFreezePane(0, 1); // Freeze 1st Row HSSFRow rowhead = sheet.createRow((short) 0); rowhead.setRowStyle(style); HSSFCell cell1_0 = rowhead.createCell(0); cell1_0.setCellStyle(style); cell1_0.setCellValue("HARDWARE TYPE"); HSSFCell cell1_1 = rowhead.createCell(1); cell1_1.setCellStyle(style); cell1_1.setCellValue("HARDWARE ID"); HSSFCell cell1_2 = rowhead.createCell(2); cell1_2.setCellStyle(style); cell1_2.setCellValue("MATERIAL PASS NO"); HSSFCell cell1_3 = rowhead.createCell(3); cell1_3.setCellStyle(style); cell1_3.setCellValue("DURATION"); HSSFCell cell1_4 = rowhead.createCell(4); cell1_4.setCellStyle(style); cell1_4.setCellValue("CURRENT STATUS"); String materialPassNo = ""; String hardwareId = ""; String hardwareType = ""; String duration = ""; String status = ""; String text = ""; WhStatusLogDAO statusD = new WhStatusLogDAO(); List<WhStatusLog> whUslList = statusD.getTLRetrieveRequestToCloseList(); boolean checksize1 = false; for (int i = 0; i < whUslList.size(); i++) { checksize1 = true; hardwareType = whUslList.get(i).getEquipmentType(); hardwareId = whUslList.get(i).getEquipmentId(); materialPassNo = whUslList.get(i).getMpNo(); String hourReqVer = whUslList.get(i).getRequestToVerifiedDate24(); String hourReqVerIfNull = whUslList.get(i).getRequestToVerifiedDateTemp24(); String hourVerShip = whUslList.get(i).getVerifiedDatetoShipDate24(); String hourVerShipIfNull = whUslList.get(i).getVerifiedDatetoShipDateTemp24(); String hourShipBScan = whUslList.get(i).getShipDateToBsScan24(); String hourShipBScanIfNull = whUslList.get(i).getShipDateToBsScanTemp24(); String hourBScanTT = whUslList.get(i).getBsScanToTtScan24(); String hourBScanTTIfNull = whUslList.get(i).getBsScanToTtScanTemp24(); boolean flag = false; if (hourReqVerIfNull != null) { if (Integer.parseInt(hourReqVerIfNull) >= 24 && hourReqVer == null) { duration = whUslList.get(i).getRequestToVerifiedDateTemp(); status = "Pending Box Barcode Verification at SBN Factory"; flag = true; } } if (hourVerShipIfNull != null) { if (Integer.parseInt(hourVerShipIfNull) >= 24 && hourVerShip == null && hourReqVer != null) { duration = whUslList.get(i).getVerifiedDatetoShipDateTemp(); status = "Pending Shipping Packing List"; flag = true; } } if (hourShipBScanIfNull != null) { if (Integer.parseInt(hourShipBScanIfNull) >= 24 && hourShipBScan == null && hourVerShip != null) { duration = whUslList.get(i).getShipDateToBsScanTemp(); status = "Pending Box Barcode Verification at Rel Lab"; flag = true; } } if (hourBScanTTIfNull != null) { if (Integer.parseInt(hourBScanTTIfNull) >= 24 && hourBScanTT == null && hourShipBScan != null) { duration = whUslList.get(i).getBsScanToTtScanTemp(); status = "Pending Trip Ticket Verification at Rel Lab"; flag = true; } } if (flag == true) { HSSFRow contents = sheet.createRow(sheet.getLastRowNum() + 1); // HSSFCell cell2_0 = contents.createCell(0); cell2_0.setCellValue(hardwareType); HSSFCell cell2_1 = contents.createCell(1); cell2_1.setCellValue(hardwareId); HSSFCell cell2_2 = contents.createCell(2); cell2_2.setCellValue(materialPassNo); HSSFCell cell2_3 = contents.createCell(3); cell2_3.setCellValue(duration); HSSFCell cell2_4 = contents.createCell(4); cell2_4.setCellValue(status); } } if (checksize1 == true) { workbook.write(fileOut); workbook.close(); //send email LOGGER.info("send email to person in charge"); EmailSender emailSender = new EmailSender(); com.onsemi.cdars.model.User user = new com.onsemi.cdars.model.User(); user.setFullname("All"); List<String> a = new ArrayList<String>(); String emailApprover = ""; String emaildistList1 = ""; String emaildistList2 = ""; String emaildistList3 = ""; String emaildistList4 = ""; emailApprover = "fg79cj@onsemi.com"; a.add(emailApprover); EmailConfigDAO econfD = new EmailConfigDAO(); int countDistList1 = econfD.getCountTask("Dist List 1"); if (countDistList1 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList1 = econfD.getEmailConfigByTask("Dist List 1"); emaildistList1 = distList1.getEmail(); a.add(emaildistList1); } econfD = new EmailConfigDAO(); int countDistList2 = econfD.getCountTask("Dist List 2"); if (countDistList2 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList2 = econfD.getEmailConfigByTask("Dist List 2"); emaildistList2 = distList2.getEmail(); a.add(emaildistList2); } econfD = new EmailConfigDAO(); int countDistList3 = econfD.getCountTask("Dist List 3"); if (countDistList3 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList3 = econfD.getEmailConfigByTask("Dist List 3"); emaildistList3 = distList3.getEmail(); a.add(emaildistList3); } econfD = new EmailConfigDAO(); int countDistList4 = econfD.getCountTask("Dist List 4"); if (countDistList4 == 1) { econfD = new EmailConfigDAO(); EmailConfig distList4 = econfD.getEmailConfigByTask("Dist List 4"); emaildistList4 = distList4.getEmail(); a.add(emaildistList4); } String[] myArray = new String[a.size()]; String[] emailTo = a.toArray(myArray); // String[] to = {"hmsrelon@gmail.com", "hmsrelontest@gmail.com"}; //9/11/16 // String[] to = {"fg79cj@onsemi.com"}; emailSender.htmlEmailWithAttachment(servletContext, user, //user name requestor emailTo, new File("C:\\Users\\" + username + "\\Documents\\CDARS\\HIMS USL for Retrieving from SBN Factory Report (" + todayDate + ").xls"), "List of Hardware Exceed USL (24 hours) for Retrieval from SBN Factory", //subject "Report for Hardware Process from HIMS(Hadware Retrieval from SBN Factory) that exceed Upper Specs Limit (24 hours) has been made. <br />" + "Hence, attached is the report file for your view and perusal. <br /><br />" + "<br /><br /> " + "<style>table, th, td {border: 1px solid black;} </style>" + "<table style=\"width:100%\">" //tbl + "<tr>" + "<th>HARDWARE TYPE</th> " + "<th>HARDWARE ID</th> " + "<th>MATERIAL PASS NO.</th>" + "<th>DURATION</th>" + "<th>CURRENT STATUS</th>" + "</tr>" + table2() + "</table>" + "<br />Thank you." //msg ); } // } }
From source file:com.plugin.excel.util.ExcelFileHelper.java
License:Apache License
private static void formatCell(Workbook workbook, Cell cell, ExcelCell excell, Map<IndexedColors, CellStyle> s_cellStyle, Font font, Font invisibleFont) { if (excell.getFormat() != null) { ExcelFormat format = excell.getFormat(); CellStyle style = s_cellStyle.get(format.getBackgroundColor()); if (format.isDate()) { // for date create a new style style = getDateStyle("date", cell.getSheet(), font); XSSFCreationHelper createHelper = (XSSFCreationHelper) cell.getSheet().getWorkbook() .getCreationHelper(); style.setDataFormat(createHelper.createDataFormat().getFormat("MMMM dd, yyyy")); font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); font.setBold(false); font.setFontHeightInPoints((short) 12); style.setFont(font);//from w ww .j a v a2s. c om cell.setCellValue(new Date()); } if (style == null) { style = workbook.createCellStyle(); s_cellStyle.put(format.getBackgroundColor(), style); } if (format.getAlignment() > 0) { style.setAlignment(format.getAlignment()); } if (format.getBackgroundColor() != null && !IndexedColors.WHITE.equals(format.getBackgroundColor())) { style.setFillForegroundColor(format.getBackgroundColor().getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); } if (format.getTextColor() != null) { font.setColor(format.getTextColor().getIndex()); style.setFont(font); } if (format.isBold()) { font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); } if (format.getFontHeight() > 0) { font.setFontHeightInPoints(format.getFontHeight()); } if (format.isWrapText()) { style.setWrapText(true); } style.setFont(font); if (format.isHideText()) { invisibleFont.setColor(IndexedColors.WHITE.getIndex()); style.setFont(invisibleFont); } cell.setCellStyle(style); } else { // Let's set default formatting for free text cell IndexedColors defaultStyle = IndexedColors.AUTOMATIC; // we are using this index CellStyle style = s_cellStyle.get(defaultStyle); if (style == null) { style = workbook.createCellStyle(); s_cellStyle.put(defaultStyle, style); } style.setWrapText(true); cell.setCellStyle(style); } }
From source file:com.rapidminer.operator.io.ExcelExampleSetWriter.java
License:Open Source License
/** * Writes the provided {@link ExampleSet} to a XLSX formatted data sheet. * * @param wb/*from ww w .j ava2s.c o m*/ * the workbook to use * @param sheet * the excel sheet to write to. * @param dateFormat * a string which describes the format used for dates. * @param numberFormat * a string which describes the format used for numbers. * @param exampleSet * the exampleSet to write * @param op * needed for checkForStop * @throws ProcessStoppedException * if the process was stopped by the user. * @throws WriteException */ private static void writeXLSXDataSheet(SXSSFWorkbook wb, Sheet sheet, String dateFormat, String numberFormat, ExampleSet exampleSet, Operator op) throws WriteException, ProcessStoppedException { Font headerFont = wb.createFont(); headerFont.setBold(true); CellStyle headerStyle = wb.createCellStyle(); headerStyle.setFont(headerFont); // create the header Iterator<Attribute> a = exampleSet.getAttributes().allAttributes(); int columnCounter = 0; int rowCounter = 0; Row headerRow = sheet.createRow(rowCounter); while (a.hasNext()) { Attribute attribute = a.next(); Cell headerCell = headerRow.createCell(columnCounter); headerCell.setCellValue(attribute.getName()); headerCell.setCellStyle(headerStyle); columnCounter++; } rowCounter++; // body font Font bodyFont = wb.createFont(); bodyFont.setBold(false); CreationHelper createHelper = wb.getCreationHelper(); // number format CellStyle numericalStyle = wb.createCellStyle(); numericalStyle.setDataFormat(createHelper.createDataFormat().getFormat(numberFormat)); numericalStyle.setFont(bodyFont); // date format CellStyle dateStyle = wb.createCellStyle(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat(dateFormat)); dateStyle.setFont(bodyFont); // create nominal cell style CellStyle nominalStyle = wb.createCellStyle(); nominalStyle.setFont(bodyFont); // fill body for (Example example : exampleSet) { // create new row Row bodyRow = sheet.createRow(rowCounter); // iterate over attributes and save examples a = exampleSet.getAttributes().allAttributes(); columnCounter = 0; while (a.hasNext()) { Attribute attribute = a.next(); Cell currentCell = bodyRow.createCell(columnCounter); if (!Double.isNaN(example.getValue(attribute))) { if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.DATE_TIME)) { Date dateValue = example.getDateValue(attribute); currentCell.setCellValue(dateValue); currentCell.setCellStyle(dateStyle); } else if (Ontology.ATTRIBUTE_VALUE_TYPE.isA(attribute.getValueType(), Ontology.NUMERICAL)) { double numericalValue = example.getNumericalValue(attribute); currentCell.setCellValue(numericalValue); currentCell.setCellStyle(numericalStyle); } else { currentCell.setCellValue( stripIfNecessary(replaceForbiddenChars(example.getValueAsString(attribute)))); currentCell.setCellStyle(nominalStyle); } } columnCounter++; } rowCounter++; // checkForStop every 100 examples if (op != null && rowCounter % 100 == 0) { op.checkForStop(); } } }
From source file:com.rarediscovery.services.logic.WorkPad.java
protected CellStyle applySelectedStyle() { Font font = workbook.createFont(); if (bold) {// www . java 2s . co m font.setBold(true); } else { font.setBold(false); } CellStyle style = workbook.createCellStyle(); style.setFont(font); if (alignRight) { style.setAlignment(CellStyle.ALIGN_RIGHT); } else { style.setAlignment(CellStyle.ALIGN_LEFT); } if (backgroundColor) { style.setFillBackgroundColor(IndexedColors.ORANGE.getIndex()); style.setFillPattern(CellStyle.BIG_SPOTS); } else { style.setFillBackgroundColor(IndexedColors.WHITE.getIndex()); } return style; }
From source file:com.respam.comniq.models.POIexcelExporter.java
License:Open Source License
public void createFile() throws IOException { String path = System.getProperty("user.home") + File.separator + "comniq" + File.separator + "output"; File file = new File(path + File.separator + "POImovieInfo.xlsx"); // Blank Workbook XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Movies"); // Data for Labels Map<String, Object[]> label = new TreeMap<>(); label.put("1", new Object[] { "Poster", "Title", "Release Date", "Metascore", "IMDB Rating", "Plot", "IMDB URL", "Genre", "Director", "Actors", "Rating", "Runtime" }); // Iterate over label and write to sheet Set<String> keyset = label.keySet(); // Setting Style for the Label Row Font font = workbook.createFont(); font.setFontHeight((short) 240); font.setFontName("Courier New"); font.setBold(true); XSSFCellStyle labelStyle = workbook.createCellStyle(); labelStyle.setWrapText(true);// w w w.j a va 2s . com labelStyle.setFont(font); // Setting column widths sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 8500); sheet.setColumnWidth(2, 4000); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 3500); sheet.setColumnWidth(5, 9500); sheet.setColumnWidth(6, 5000); sheet.setColumnWidth(7, 4000); sheet.setColumnWidth(8, 3500); sheet.setColumnWidth(9, 4000); sheet.setColumnWidth(10, 3000); sheet.setColumnWidth(11, 4000); // Freezing the first row sheet.createFreezePane(0, 1); // Filling each cell with Label data for (String key : keyset) { Row row = sheet.createRow(0); Object[] objArr = label.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellStyle(labelStyle); cell.setCellValue((String) obj); } } // Writing the excel file try { FileOutputStream out = new FileOutputStream(file); workbook.write(out); out.close(); System.out.println("Excel File Created"); } catch (Exception e) { e.printStackTrace(); } }