List of usage examples for org.apache.poi.xssf.usermodel XSSFFont setBold
public void setBold(boolean bold)
From source file:com.netsteadfast.greenstep.bsc.command.PerspectivesDashboardExcelCommand.java
License:Apache License
@SuppressWarnings("unchecked") private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception { String pieBase64Content = SimpleUtils.getPNGBase64Content((String) context.get("pieCanvasToData")); String barBase64Content = SimpleUtils.getPNGBase64Content((String) context.get("barCanvasToData")); BufferedImage pieImage = SimpleUtils.decodeToImage(pieBase64Content); BufferedImage barImage = SimpleUtils.decodeToImage(barBase64Content); ByteArrayOutputStream pieBos = new ByteArrayOutputStream(); ImageIO.write(pieImage, "png", pieBos); pieBos.flush();/* w w w. j av a2 s . c o m*/ ByteArrayOutputStream barBos = new ByteArrayOutputStream(); ImageIO.write(barImage, "png", barBos); barBos.flush(); SimpleUtils.setCellPicture(wb, sh, pieBos.toByteArray(), 0, 0); SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), 0, 9); int row = 21; List<Map<String, Object>> chartDatas = (List<Map<String, Object>>) context.get("chartDatas"); String year = (String) context.get("year"); XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5"))); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true); //cellHeadFont.setColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#000000" ) ) ); cellHeadStyle.setFont(cellHeadFont); int titleRow = row - 1; int titleCellSize = 14; Row headRow = sh.createRow(titleRow); for (int i = 0; i < titleCellSize; i++) { Cell headCell = headRow.createCell(i); headCell.setCellStyle(cellHeadStyle); headCell.setCellValue("Perspectives metrics gauge ( " + year + " )"); } sh.addMergedRegion(new CellRangeAddress(titleRow, titleRow, 0, titleCellSize - 1)); int cellLeft = 10; int rowSpace = 17; for (Map<String, Object> data : chartDatas) { Map<String, Object> nodeData = (Map<String, Object>) ((List<Object>) data.get("datas")).get(0); String pngImageData = SimpleUtils.getPNGBase64Content((String) nodeData.get("outerHTML")); BufferedImage imageData = SimpleUtils.decodeToImage(pngImageData); ByteArrayOutputStream imgBos = new ByteArrayOutputStream(); ImageIO.write(imageData, "png", imgBos); imgBos.flush(); SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, 0); XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("bgColor"))); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("fontColor"))); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(bgColor); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(true); cellFont.setColor(fnColor); cellStyle.setFont(cellFont); int perTitleCellSize = 4; Row nowRow = sh.createRow(row); for (int i = 0; i < perTitleCellSize; i++) { Cell cell1 = nowRow.createCell(cellLeft); cell1.setCellStyle(cellStyle); cell1.setCellValue((String) nodeData.get("name")); } sh.addMergedRegion(new CellRangeAddress(row, row, cellLeft, cellLeft + perTitleCellSize - 1)); nowRow = sh.createRow(row + 1); Cell cell2 = nowRow.createCell(cellLeft); cell2.setCellValue("Target: " + String.valueOf(nodeData.get("target"))); nowRow = sh.createRow(row + 2); Cell cell3 = nowRow.createCell(cellLeft); cell3.setCellValue("Min: " + String.valueOf(nodeData.get("min"))); nowRow = sh.createRow(row + 3); Cell cell4 = nowRow.createCell(cellLeft); cell4.setCellValue("Score: " + String.valueOf(nodeData.get("score"))); row += rowSpace; } return row; }
From source file:com.netsteadfast.greenstep.bsc.command.TimeSeriesAnalysisExcelCommand.java
License:Apache License
private void putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception { TsaVO tsa = (TsaVO) context.get("tsa"); @SuppressWarnings("unchecked") List<BbTsaMaCoefficients> coefficients = (List<BbTsaMaCoefficients>) context.get("coefficients"); @SuppressWarnings("unchecked") List<TimeSeriesAnalysisResult> tsaResults = (List<TimeSeriesAnalysisResult>) context.get("tsaResults"); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true); XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5"))); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellHeadStyle.setBorderBottom(BorderStyle.THIN); cellHeadStyle.setBorderTop(BorderStyle.THIN); cellHeadStyle.setBorderRight(BorderStyle.THIN); cellHeadStyle.setBorderLeft(BorderStyle.THIN); cellHeadStyle.setFont(cellHeadFont); XSSFCellStyle cellHeadStyleBlank = wb.createCellStyle(); cellHeadStyleBlank.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff"))); cellHeadStyleBlank.setFont(cellHeadFont); XSSFCellStyle cellHeadStyle2 = wb.createCellStyle(); cellHeadStyle2.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff"))); cellHeadStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellHeadStyle2.setBorderBottom(BorderStyle.THIN); cellHeadStyle2.setBorderTop(BorderStyle.THIN); cellHeadStyle2.setBorderRight(BorderStyle.THIN); cellHeadStyle2.setBorderLeft(BorderStyle.THIN); sh.setColumnWidth(0, 12000);// w ww.j ava 2s .c om int row = 0; // ============================================================== Row nowRow = sh.createRow(row); Cell cellTitle = nowRow.createCell(0); cellTitle.setCellStyle(cellHeadStyleBlank); cellTitle.setCellValue("Forecast analysis - " + context.get("visionName")); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell0a = nowRow.createCell(0); cell0a.setCellStyle(cellHeadStyleBlank); cell0a.setCellValue("Frequency: " + context.get("frequencyName") + " , Date range: " + context.get("date1") + " - " + context.get("date2") + " , " + "Measure data type for: " + context.get("dataFor") + " , " + context.get("organizationName") + context.get("employeeName")); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell0b = nowRow.createCell(0); cell0b.setCellStyle(cellHeadStyleBlank); cell0b.setCellValue(""); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell1 = nowRow.createCell(0); cell1.setCellStyle(cellHeadStyleBlank); cell1.setCellValue("Param infornation"); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell2_a = nowRow.createCell(0); cell2_a.setCellStyle(cellHeadStyle); cell2_a.setCellValue("Item"); Cell cell2_b = nowRow.createCell(1); cell2_b.setCellStyle(cellHeadStyle); cell2_b.setCellValue("Value"); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell3_a = nowRow.createCell(0); cell3_a.setCellStyle(cellHeadStyle2); cell3_a.setCellValue("Param name"); Cell cell3_b = nowRow.createCell(1); cell3_b.setCellStyle(cellHeadStyle2); cell3_b.setCellValue(tsa.getName()); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell4_a = nowRow.createCell(0); cell4_a.setCellStyle(cellHeadStyle2); cell4_a.setCellValue("Integration order"); Cell cell4_b = nowRow.createCell(1); cell4_b.setCellStyle(cellHeadStyle2); cell4_b.setCellValue(tsa.getIntegrationOrder()); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell5_a = nowRow.createCell(0); cell5_a.setCellStyle(cellHeadStyle2); cell5_a.setCellValue("Forecast next"); Cell cell5_b = nowRow.createCell(1); cell5_b.setCellStyle(cellHeadStyle2); cell5_b.setCellValue(tsa.getForecastNext()); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell6_a = nowRow.createCell(0); cell6_a.setCellStyle(cellHeadStyle2); cell6_a.setCellValue("Description"); Cell cell6_b = nowRow.createCell(1); cell6_b.setCellStyle(cellHeadStyle2); cell6_b.setCellValue(StringUtils.defaultString(tsa.getDescription()).trim()); row++; // ============================================================== for (int i = 0; coefficients != null && i < coefficients.size(); i++) { BbTsaMaCoefficients coefficient = coefficients.get(i); nowRow = sh.createRow(row); Cell cell7x_a = nowRow.createCell(0); cell7x_a.setCellStyle(cellHeadStyle2); cell7x_a.setCellValue("Coefficient (" + (i + 1) + ")"); Cell cell7x_b = nowRow.createCell(1); cell7x_b.setCellStyle(cellHeadStyle2); cell7x_b.setCellValue(String.valueOf(coefficient.getSeqValue())); row++; } // ============================================================== nowRow = sh.createRow(row); Cell cellTitle3a = nowRow.createCell(0); cellTitle3a.setCellStyle(cellHeadStyleBlank); cellTitle3a.setCellValue(""); row++; // nowRow = sh.createRow(row); Cell cellTitle2a = nowRow.createCell(0); cellTitle2a.setCellStyle(cellHeadStyle); cellTitle2a.setCellValue("KPIs"); int j = 1; TimeSeriesAnalysisResult firstResult = tsaResults.get(0); for (int i = 0; i < firstResult.getKpi().getDateRangeScores().size(); i++) { DateRangeScoreVO dateRangeScore = firstResult.getKpi().getDateRangeScores().get(i); Cell cellTitle2a_dateRange = nowRow.createCell(j); j++; cellTitle2a_dateRange.setCellStyle(cellHeadStyle); cellTitle2a_dateRange.setCellValue(dateRangeScore.getDate()); } for (int i = 0; i < firstResult.getForecastNext().size(); i++) { Cell cellTitle2a_dateRange = nowRow.createCell(j); j++; cellTitle2a_dateRange.setCellStyle(cellHeadStyle); cellTitle2a_dateRange.setCellValue("next(" + (i + 1) + ")"); } row++; // Date Range score Forecast next score for (int i = 0; i < tsaResults.size(); i++) { nowRow = sh.createRow(row); j = 0; TimeSeriesAnalysisResult resultModel = tsaResults.get(i); Cell cell_kpi = nowRow.createCell(j); cell_kpi.setCellStyle(cellHeadStyle); cell_kpi.setCellValue(resultModel.getKpi().getName()); j++; for (int n = 0; n < resultModel.getKpi().getDateRangeScores().size(); n++) { DateRangeScoreVO dateRangeScore = resultModel.getKpi().getDateRangeScores().get(n); Cell cell_dateRangeScore = nowRow.createCell(j); cell_dateRangeScore.setCellStyle(cellHeadStyle2); cell_dateRangeScore.setCellValue(dateRangeScore.getScore()); j++; } for (int n = 0; n < resultModel.getForecastNext().size(); n++) { double forecastScore = resultModel.getForecastNext().get(n); Cell cell_forecastScore = nowRow.createCell(j); cell_forecastScore.setCellStyle(cellHeadStyle2); cell_forecastScore.setCellValue(forecastScore); j++; } row++; } }
From source file:com.philips.his.pixiu.cdr.poi.BigGridDemo.java
License:Apache License
/** * Create a library of cell styles./*from w w w. ja v a 2s .co m*/ */ private 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(HorizontalAlignment.RIGHT); style1.setDataFormat(fmt.getFormat("0.0%")); styles.put("percent", style1); XSSFCellStyle style2 = wb.createCellStyle(); style2.setAlignment(HorizontalAlignment.CENTER); style2.setDataFormat(fmt.getFormat("0.0X")); styles.put("coeff", style2); XSSFCellStyle style3 = wb.createCellStyle(); style3.setAlignment(HorizontalAlignment.RIGHT); style3.setDataFormat(fmt.getFormat("$#,##0.00")); styles.put("currency", style3); XSSFCellStyle style4 = wb.createCellStyle(); style4.setAlignment(HorizontalAlignment.RIGHT); style4.setDataFormat(fmt.getFormat("mmm dd")); styles.put("date", style4); XSSFCellStyle style5 = wb.createCellStyle(); XSSFFont headerFont = wb.createFont(); headerFont.setBold(true); style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style5.setFillPattern(FillPatternType.SOLID_FOREGROUND); style5.setFont(headerFont); styles.put("header", style5); return styles; }
From source file:com.tikal.tallerWeb.servicio.reporte.cliente.AbstractSeccionXLS.java
License:Apache License
public void addHeaderStyle(XSSFCellStyle cellStyle, XSSFWorkbook wb) { cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // cellStyle.setFillForegroundColor(new XSSFColor(new Color(85, 142, 213))); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont font = wb.createFont(); font.setColor(IndexedColors.WHITE.getIndex()); font.setBold(true); cellStyle.setFont(font);//from w w w. j a va 2s.com }
From source file:com.tikal.tallerWeb.servicio.reporte.cliente.CostoHojalateriaReporteCliente.java
License:Apache License
@Override public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) { Sheet sheet = contexto.getSheet();//from w w w . jav a 2 s. c om XSSFWorkbook wb = contexto.getWb(); int initialRow = borde.getUpperRow(); int initialColumn = borde.getLeftColumn(); BordeSeccion r = new BordeSeccion(); r.setLeftColumn(initialColumn); r.setUpperRow(initialRow); Cell cell; Row row = getRow(sheet, initialRow); cell = row.createCell(initialColumn); cell.setCellValue("Hojalteria y pintura"); XSSFCellStyle cellStyle = wb.createCellStyle(); addHeaderStyle(cellStyle, wb); addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM); cell.setCellStyle(cellStyle); for (int i = 1; i < 3; i++) { cell = row.createCell(initialColumn + i); cellStyle = wb.createCellStyle(); addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM); cell.setCellStyle(cellStyle); } //merge de celdas sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based) initialRow, //last row (0-based) initialColumn, //first column (0-based) initialColumn + 2 //last column (0-based) )); //detalle cellStyle = wb.createCellStyle(); XSSFDataFormat df = wb.createDataFormat(); cellStyle.setDataFormat(df.getFormat("$#,##0.00")); Cell inicio = null; Cell fin = null; for (RegistroCostoRC x : datos.getRegistroHojalateriaPintura()) { initialRow = initialRow + 1; row = getRow(sheet, initialRow); //tipo cell = row.createCell(initialColumn); cell.setCellValue(x.getTipo()); //descripcion cell = row.createCell(initialColumn + 1); cell.setCellValue(x.getDescripcion()); //costo cell = row.createCell(initialColumn + 2); cell.setCellValue(x.getCosto()); cell.setCellStyle(cellStyle); if (inicio == null) { inicio = cell; } } if (inicio != null) { fin = cell; } initialRow = initialRow + 1; row = getRow(sheet, initialRow); cell = row.createCell(initialColumn + 1); cell.setCellValue("Total"); cellStyle = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setBold(true); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); cellStyle.setDataFormat(df.getFormat("$#,##0.00")); cell = row.createCell(initialColumn + 2); cell.setCellStyle(cellStyle); if (inicio != null) { String formula = "SUM(" + getSimpleReference(inicio) + ":" + getSimpleReference(fin) + ")"; cell.setCellFormula(formula); contexto.put("totalHojalateria", cell); } else { cell.setCellValue(0.0); } r.setLowerRow(initialRow); r.setRightColumn(initialColumn + 2); BordeSeccion sinTotal = new BordeSeccion(r); sinTotal.setLowerRow(r.getLowerRow() - 1); paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, sinTotal); return r; }
From source file:com.tikal.tallerWeb.servicio.reporte.cliente.CostoMecanicaReporteCliente.java
License:Apache License
@Override public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) { Sheet sheet = contexto.getSheet();// w w w.j a v a 2 s . c o m XSSFWorkbook wb = contexto.getWb(); int initialRow = borde.getUpperRow(); int initialColumn = borde.getLeftColumn(); BordeSeccion r = new BordeSeccion(); r.setLeftColumn(initialColumn); r.setUpperRow(initialRow); Cell cell; Row row = getRow(sheet, initialRow); cell = row.createCell(initialColumn); cell.setCellValue("Mecanica"); XSSFCellStyle cellStyle = wb.createCellStyle(); addHeaderStyle(cellStyle, wb); addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM); cell.setCellStyle(cellStyle); for (int i = 1; i < 3; i++) { cell = row.createCell(initialColumn + i); cellStyle = wb.createCellStyle(); addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM); cell.setCellStyle(cellStyle); } //merge de celdas sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based) initialRow, //last row (0-based) initialColumn, //first column (0-based) initialColumn + 2 //last column (0-based) )); //detalle cellStyle = wb.createCellStyle(); XSSFDataFormat df = wb.createDataFormat(); cellStyle.setDataFormat(df.getFormat("$#,##0.00")); Cell inicio = null; Cell fin = null; for (RegistroCostoRC x : datos.getRegistroMecanica()) { initialRow = initialRow + 1; row = getRow(sheet, initialRow); //tipo cell = row.createCell(initialColumn); cell.setCellValue(x.getTipo()); //descripcion cell = row.createCell(initialColumn + 1); cell.setCellValue(x.getDescripcion()); //costo cell = row.createCell(initialColumn + 2); cell.setCellValue(x.getCosto()); cell.setCellStyle(cellStyle); if (inicio == null) { inicio = cell; } } if (inicio != null) { fin = cell; } initialRow = initialRow + 1; row = getRow(sheet, initialRow); cell = row.createCell(initialColumn + 1); cell.setCellValue("Total"); cellStyle = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setBold(true); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); cellStyle.setDataFormat(df.getFormat("$#,##0.00")); cell = row.createCell(initialColumn + 2); cell.setCellStyle(cellStyle); if (inicio != null) { String formula = "SUM(" + getSimpleReference(inicio) + ":" + getSimpleReference(fin) + ")"; cell.setCellFormula(formula); contexto.put("totalMecanica", cell); } else { cell.setCellValue(0.0); } r.setLowerRow(initialRow); r.setRightColumn(initialColumn + 2); BordeSeccion sinTotal = new BordeSeccion(r); sinTotal.setLowerRow(r.getLowerRow() - 1); paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, sinTotal); return r; }
From source file:Dao.XlsBillDao.java
public void GenFullXLS(String pono, String relpath) { try {/*ww w.j av a 2s. c o 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 ww w. j a v a 2s . 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 . jav a2 s . c o m //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:das.pf.io.IOExcel.java
License:Open Source License
private XSSFCellStyle getXSSFCellStyleValues(Workbook worbook, TypeValues typeValue, TypeUnits typeUnit) { XSSFCellStyle style = (XSSFCellStyle) worbook.createCellStyle(); XSSFFont font = (XSSFFont) worbook.createFont(); font.setBold(true); font.setColor(new XSSFColor(Color.WHITE)); style.setAlignment(HorizontalAlignment.CENTER); switch (typeValue) { case VALUES:/*from w w w . j a v a 2 s . c o m*/ switch (typeUnit) { case MTH: style.setFillForegroundColor(new XSSFColor(Color.BLUE.darker())); break; case QRT: style.setFillForegroundColor(new XSSFColor(Color.BLUE.darker().darker())); break; case YTD: style.setFillForegroundColor(new XSSFColor(Color.BLUE)); break; case MAT: style.setFillForegroundColor(new XSSFColor(Color.CYAN)); break; } break; case UNITS: switch (typeUnit) { case MTH: style.setFillForegroundColor(new XSSFColor(Color.RED.darker())); break; case QRT: style.setFillForegroundColor(new XSSFColor(Color.RED.darker().darker())); break; case YTD: style.setFillForegroundColor(new XSSFColor(Color.ORANGE)); break; case MAT: style.setFillForegroundColor(new XSSFColor(Color.YELLOW)); break; } break; case U_E: switch (typeUnit) { case MTH: style.setFillForegroundColor(new XSSFColor(Color.GRAY.darker())); break; case QRT: style.setFillForegroundColor(new XSSFColor(Color.GREEN.darker().darker())); break; case YTD: style.setFillForegroundColor(new XSSFColor(Color.GRAY)); break; case MAT: style.setFillForegroundColor(new XSSFColor(Color.LIGHT_GRAY)); break; } break; } style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(font); return style; }