List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createFont
@Override
public XSSFFont createFont()
From source file:ec.sirec.web.impuestos.GestionImpuestoPredialControlador.java
public void postProcessXLS(Object document) throws IOException { XSSFWorkbook wb = (XSSFWorkbook) document; XSSFSheet hoja = wb.getSheetAt(0);// ww w .j av a 2 s .c om CellStyle style = wb.createCellStyle(); style.setFillPattern(CellStyle.NO_FILL); org.apache.poi.ss.usermodel.Font font = wb.createFont(); font.setFontName("Times Roman"); font.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.BLACK.getIndex()); style.setFont(font); /** * ** Configuracin del estilo de la celda header de la tabla. ***** */ CellStyle styleHeaderTable = wb.createCellStyle(); styleHeaderTable.setFillPattern(CellStyle.NO_FILL); org.apache.poi.ss.usermodel.Font fontHeaderTable = wb.createFont(); fontHeaderTable.setFontName("Times Roman"); fontHeaderTable.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); fontHeaderTable.setColor(IndexedColors.BLACK.getIndex()); styleHeaderTable.setFont(fontHeaderTable); Sheet sheet = wb.getSheetAt(0); sheet.autoSizeColumn((short) 0); //ajusta el ancho de la primera columna sheet.autoSizeColumn((short) 1); sheet.autoSizeColumn((short) 2); for (int i = 0; i < 20; i++) { hoja.autoSizeColumn((short) i); } }
From source file:edu.cmu.emfta.actions.CutSet.java
License:Open Source License
public XSSFWorkbook toSingleSheetWorkbook() { XSSFWorkbook workbook = new XSSFWorkbook(); int cutSetIdentifier = 0; double cutsetProbability; XSSFSheet sheet = workbook.createSheet(); XSSFTable table = sheet.createTable(); table.setDisplayName("Cutsets"); CTTable cttable = table.getCTTable(); // Set which area the table should be placed in AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(2, 2)); cttable.setRef(reference.formatAsString()); cttable.setId((long) 1); cttable.setName("Cutsets"); cttable.setTotalsRowCount((long) 1); CTTableColumns columns = cttable.addNewTableColumns(); columns.setCount((long) 3); CTTableColumn column;//from ww w. j a v a 2s .c o m XSSFRow row; XSSFCell cell; column = columns.addNewTableColumn(); // Create row row = sheet.createRow(0); CellStyle headingCellStyle = workbook.createCellStyle(); XSSFFont headingFont = workbook.createFont(); headingFont.setBold(true); headingCellStyle.setFont(headingFont); row.setRowStyle(headingCellStyle); CellStyle normalCellStyle = workbook.createCellStyle(); XSSFFont normalFont = workbook.createFont(); normalFont.setBold(false); normalCellStyle.setFont(normalFont); for (int j = 0; j < 3; j++) { // Create cell cell = row.createCell(j); switch (j) { case 0: { cell.setCellValue("Identifier"); break; } case 1: { cell.setCellValue("Description"); break; } case 2: { cell.setCellValue("Probability"); break; } } } int rowId = 1; for (List<Event> events : cutset) { row = sheet.createRow(rowId++); row = sheet.createRow(rowId++); row.setRowStyle(normalCellStyle); cell = row.createCell(0); cell.setCellValue("Cutset #" + cutSetIdentifier); cutsetProbability = 1; for (int i = 0; i < events.size(); i++) { cutsetProbability = cutsetProbability * events.get(i).getProbability(); } cell = row.createCell(2); if (cutsetProbability != 1) { cell.setCellValue("" + cutsetProbability); } else { cell.setCellValue("" + cutsetProbability); } // System.out.println("[CutSet] cutset id=" + cutSetIdentifier); for (int i = 0; i < events.size(); i++) { Event e = events.get(i); // System.out.println("[CutSet] event name=" + e.getName()); // Create row row = sheet.createRow(rowId++); row.setRowStyle(normalCellStyle); for (int j = 0; j < 3; j++) { // Create cell cell = row.createCell(j); switch (j) { case 0: { cell.setCellValue(e.getName()); break; } case 1: { cell.setCellValue(e.getDescription()); break; } case 2: { cell.setCellValue(e.getProbability()); break; } } } } cutSetIdentifier = cutSetIdentifier + 1; } return workbook; }
From source file:edu.cmu.emfta.actions.CutSet.java
License:Open Source License
public XSSFWorkbook toMultiSheetsWorkbook() { XSSFWorkbook workbook = new XSSFWorkbook(); int cutSetIdentifier = 0; double cutsetProbability; for (List<Event> events : cutset) { cutsetProbability = 1;/* w w w . ja va2 s . c o m*/ for (int i = 0; i < events.size(); i++) { cutsetProbability = cutsetProbability * events.get(i).getProbability(); } // System.out.println("[CutSet] cutset id=" + cutSetIdentifier); XSSFSheet sheet = workbook.createSheet(); XSSFTable table = sheet.createTable(); table.setDisplayName("Cutset"); CTTable cttable = table.getCTTable(); // Set which area the table should be placed in AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(2, 2)); cttable.setRef(reference.formatAsString()); cttable.setId((long) 1); cttable.setName("Cutset " + cutSetIdentifier); cttable.setTotalsRowCount((long) 1); CTTableColumns columns = cttable.addNewTableColumns(); columns.setCount((long) 3); CTTableColumn column; XSSFRow row; XSSFCell cell; column = columns.addNewTableColumn(); // Create row row = sheet.createRow(0); CellStyle headingCellStyle = workbook.createCellStyle(); XSSFFont headingFont = workbook.createFont(); headingFont.setBold(true); headingCellStyle.setFont(headingFont); row.setRowStyle(headingCellStyle); CellStyle normalCellStyle = workbook.createCellStyle(); XSSFFont normalFont = workbook.createFont(); normalFont.setBold(false); normalCellStyle.setFont(normalFont); for (int j = 0; j < 3; j++) { // Create cell cell = row.createCell(j); switch (j) { case 0: { cell.setCellValue("Identifier"); break; } case 1: { cell.setCellValue("Description"); break; } case 2: { if (cutsetProbability == 1) { cell.setCellValue("Probability"); } else { cell.setCellValue("Probability (" + cutsetProbability + ")"); } break; } } } for (int i = 0; i < events.size(); i++) { Event e = events.get(i); System.out.println("[CutSet] event name=" + e.getName()); // Create column column = columns.addNewTableColumn(); column.setName("Column"); column.setId((long) i + 1); // Create row row = sheet.createRow(i + 1); row.setRowStyle(normalCellStyle); for (int j = 0; j < 3; j++) { // Create cell cell = row.createCell(j); switch (j) { case 0: { cell.setCellValue(e.getName()); break; } case 1: { cell.setCellValue(e.getDescription()); break; } case 2: { cell.setCellValue(e.getProbability()); break; } } } } cutSetIdentifier = cutSetIdentifier + 1; } return workbook; }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-2] ? : ? ? ? ? ?/*ww w .j a v a 2s .c o m*/ */ @Test public void testModifyCellContents() throws Exception { try { String content = "Use \n with word wrap on to create a new line"; short rownum = 2; int cellnum = 2; LOGGER.debug("testModifyCellContents start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellContents.xlsx"); if (!EgovFileUtil.isExistsFile(sb.toString())) { Workbook wbT = new XSSFWorkbook(); wbT.createSheet(); // ? ? excelService.createWorkbook(wbT, sb.toString()); } // ? XSSFWorkbook wb = null; wb = excelService.loadWorkbook(sb.toString(), wb); LOGGER.debug("testModifyCellContents after loadWorkbook...."); Sheet sheet = wb.getSheetAt(0); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); //Word Wrap MUST be turned on cs.setWrapText(true); Row row = sheet.createRow(rownum); row.setHeight((short) 0x349); Cell cellx = row.createCell(cellnum); cellx.setCellType(XSSFCell.CELL_TYPE_STRING); cellx.setCellValue(new XSSFRichTextString(content)); cellx.setCellStyle(cs); sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20))); //excelService.writeWorkbook(wb); FileOutputStream outx = new FileOutputStream(sb.toString()); wb.write(outx); outx.close(); // ? Workbook wb1 = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheet1 = wb1.getSheetAt(0); Row row1 = sheet1.getRow(rownum); Cell cell1 = row1.getCell(cellnum); // ? ? ? LOGGER.debug("cell ###{}###", cell1.getRichStringCellValue()); LOGGER.debug("cont ###{}###", content); assertNotSame("TEST", cell1.getRichStringCellValue().toString()); assertEquals(content, cell1.getRichStringCellValue().toString()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellContents end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *//*from w w w . ja va2 s.c om*/ @Test public void testWriteExcelFileAttribute() throws Exception { try { LOGGER.debug("testWriteExcelFileAttribute start...."); short rowheight = 40 * 10; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); XSSFCellStyle cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(CellStyle.ALIGN_RIGHT); cs.setFillPattern(CellStyle.DIAMONDS); // ? XSSFRow r1 = sheet1.createRow(0); r1.createCell(0); // ? ? cs.setFillForegroundColor(IndexedColors.BLUE.getIndex()); // cs.setFillBackgroundColor(IndexedColors.RED.getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); LOGGER.debug("getFillPattern : {}", cs1.getFillPattern()); assertEquals(XSSFCellStyle.DIAMONDS, cs1.getFillPattern()); LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor()); LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor()); LOGGER.debug( "XSSFWorkbook.getFillBackgroundColor(), XSSFColor().getIndexed() ? ? 0 ? ?"); assertEquals(IndexedColors.BLUE.getIndex(), cs1.getFillForegroundColor()); assertEquals(IndexedColors.RED.getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testWriteExcelFileAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-6] ? : ? ?(?, ? )? /*from ww w. j ava2s.c om*/ */ @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...."); } }
From source file:es.tena.foundation.util.POIUtil.java
/** * Crea un map con los estilos para cada tipo de datos * * @param wb//from ww w . ja va 2 s . com * @return un hashMap con las claves 'porcentaje', 'coeficiente', 'moneda', * 'fecha' y 'cabecera' */ public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) { Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>(); XSSFDataFormat fmt = wb.createDataFormat(); XSSFCellStyle style1 = wb.createCellStyle(); style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style1.setDataFormat(fmt.getFormat("0.0%")); styles.put("porcentaje", style1); XSSFCellStyle style2 = wb.createCellStyle(); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setDataFormat(fmt.getFormat("0.0X")); styles.put("coeficiente", style2); XSSFCellStyle style3 = wb.createCellStyle(); style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style3.setDataFormat(fmt.getFormat("#,##0.00")); styles.put("moneda", style3); XSSFCellStyle style4 = wb.createCellStyle(); style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style4.setDataFormat(fmt.getFormat("mmm dd")); styles.put("fecha", style4); XSSFCellStyle style5 = wb.createCellStyle(); XSSFFont headerFont = wb.createFont(); headerFont.setBold(true); style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style5.setFont(headerFont); styles.put("cabecera", style5); return styles; }
From source file:GapAnalysis.gapAnalysis.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); //PrintWriter out = response.getWriter(); try {//from w ww. j ava 2 s. c o m ArrayList keyal = new ArrayList(); ArrayList countyal = new ArrayList(); ArrayList scountyal = new ArrayList(); ArrayList facilal = new ArrayList(); ArrayList yearmonthal = new ArrayList(); ArrayList monthal = new ArrayList(); ArrayList sectional = new ArrayList(); XSSFWorkbook wb; String periodname = ""; String allpath = getServletContext().getRealPath("/Gapanalysis.xlsm"); System.out.println(allpath); XSSFWorkbook workbook; String mydrive = allpath.substring(0, 1); // wb = new XSSFWorkbook( OPCPackage.open(allpath) ); Date da = new Date(); String dat2 = da.toString().replace(" ", "_"); dat2 = dat2.toString().replace(":", "_"); String np = mydrive + ":\\APHIAPLUS\\InternalSystem\\Gapanalysis" + dat2 + ".xlsm"; System.out.println("path:: " + np); // String desteepath1 = getServletContext().getRealPath("/Females 15to24.xlsm"); String sr = getServletContext().getRealPath("/Gapanalysis.xlsm"); //check if file exists //first time , it should create those folders that host the macro file File f = new File(np); if (!f.exists() && !f.isDirectory()) { /* do something */ copytemplates ct = new copytemplates(); ct.transfermacros(sr, np); //rem np is the destination file name System.out.println("Copying macro template first time .."); } else //copy the file alone { copytemplates ct = new copytemplates(); //copy the agebased file only ct.copymacros(sr, np); } String filepth = np; File allpathfile = new File(filepth); OPCPackage pkg = OPCPackage.open(allpathfile); pathtodelete = filepth; wb = new XSSFWorkbook(pkg); dbConn conn = new dbConn(); HashMap<String, String> rawdatahashmap = new HashMap<String, String>(); int year = 0; String yearval = ""; int prevyear = 0; String quarter = ""; String yearmonth = ""; String startyearmonth = ""; String endyearmonth = ""; yearval = request.getParameter("year").toString(); System.out.println("YEARVAL" + yearval); year = Integer.parseInt(yearval); prevyear = year - 1; quarter = request.getParameter("quarter"); periodname += yearval + "_"; if (quarter.equals("1")) { startyearmonth = prevyear + "10"; endyearmonth = prevyear + "12"; periodname = prevyear + "_(Oct_Dec)"; } else if (quarter.equals("2")) { startyearmonth = year + "01"; endyearmonth = year + "03"; periodname = yearval + "_(Jan-Mar)"; } else if (quarter.equals("3")) { startyearmonth = year + "04"; endyearmonth = year + "06"; periodname = yearval + "_(Apr_Jun)"; } else if (quarter.equals("4")) { startyearmonth = year + "07"; endyearmonth = year + "09"; periodname = yearval + "_(Jul_Sep)"; } int colsmerging = 6; String Sections[] = { "ART", "HTC", "PMTCT" }; String headers[] = { "County", "Sub-County", "Facility", "Year", "Month" }; String headergsn[] = { "County", "Sub-County", "Facility" }; //if one wants gaps for one service area if (request.getParameterValues("gapsection") != null) { Sections = request.getParameterValues("gapsection"); } //This is the loop that well use to create worksheets for each String period = " 1=1 and Annee=" + yearval + " and yearmonth between " + startyearmonth + " and " + endyearmonth + " "; String gsnperiod = " 1=1 "; //______________________________________________________________________________________ //______________________________________________________________________________________ Font font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellStyle stylex1 = wb.createCellStyle(); stylex1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex1.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex1.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex1.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex1.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex1.setAlignment(HSSFCellStyle.ALIGN_LEFT); CellStyle stylex2 = wb.createCellStyle(); stylex2.setFillForegroundColor(HSSFColor.SKY_BLUE.index); stylex2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex2.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex2.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex2.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex2.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex2.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellStyle stylex3 = wb.createCellStyle(); stylex3.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); stylex3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex3.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex3.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex3.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex3.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex3.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylex1.setFont(fontx); stylex1.setWrapText(true); stylex2.setFont(fontx); stylex2.setWrapText(true); //================================================== for (int a = 0; a < Sections.length; a++) { int column = 0; int Row = 3; Sheet shet = wb.createSheet(Sections[a]); Row rwx = shet.createRow(2); Row rw1 = null; Row rw2 = null; Row rw = shet.createRow(0); rw.setHeightInPoints(25); Cell cl0 = rw.createCell(0); cl0.setCellValue(Sections[a] + " GAP ANALYSIS"); cl0.setCellStyle(stylex1); //this will depend on the length of the number of elements being checked for (int b = 1; b <= colsmerging; b++) { Cell clx = rw.createCell(b); clx.setCellValue(""); clx.setCellStyle(stylex); } //now go to the database and do a query for each section int determinant = 2; String getqueries = " Select * from gap_analysis where active=1 and section='" + Sections[a] + "' "; conn.rs = conn.st.executeQuery(getqueries); while (conn.rs.next()) { //if an excel sheet exists, then get the row number 1 if (shet.getRow(1) != null) { rw1 = shet.getRow(1); } else { rw1 = shet.createRow(1); rw1.setHeightInPoints(25); } //print blanks before printing real header //for gsns, we only print three columns and no period if (conn.rs.getString("id").equals("1")) { for (int p = 0; p < headergsn.length; p++) { Cell cl2 = rw1.createCell(column + p); cl2.setCellValue(""); cl2.setCellStyle(stylex); shet.setColumnWidth(column + p, 5000); } } else { for (int p = 0; p < headers.length; p++) { Cell cl2 = rw1.createCell(column + p); cl2.setCellValue(""); cl2.setCellStyle(stylex); shet.setColumnWidth(column + p, 5000); } } determinant++; if (determinant % 2 == 0) { Cell cl1 = rw1.createCell(column); cl1.setCellValue(conn.rs.getString("rule")); cl1.setCellStyle(stylex3); } else { Cell cl1 = rw1.createCell(column); cl1.setCellValue(conn.rs.getString("rule")); cl1.setCellStyle(stylex2); } //Create the column header if (shet.getRow(2) != null) { rw2 = shet.getRow(2); } else { rw2 = shet.createRow(2); rw2.setHeightInPoints(25); } if (conn.rs.getString("id").equals("1")) { for (int p = 0; p < headergsn.length; p++) { Cell cl2 = rw2.createCell(column + p); cl2.setCellValue(headergsn[p]); cl2.setCellStyle(stylex); } } else { for (int p = 0; p < headers.length; p++) { Cell cl2 = rw2.createCell(column + p); cl2.setCellValue(headers[p]); cl2.setCellStyle(stylex); } } String currentqry = conn.rs.getString("query"); //process each query as you //pass the necessary period parameters from the interface //rem each query ends with a 'and' if (conn.rs.getString("id").equals("1")) { currentqry += gsnperiod; } else { currentqry += period + " and subpartnera." + Sections[a] + "= 1 "; } System.out.println("" + currentqry); Row = 3; conn.rs1 = conn.st1.executeQuery(currentqry); while (conn.rs1.next()) { if (shet.getRow(Row) != null) { rwx = shet.getRow(Row); } else { rwx = shet.createRow(Row); rwx.setHeightInPoints(25); } Cell cly = rwx.createCell(column); cly.setCellValue(conn.rs1.getString("County")); cly.setCellStyle(style2); Cell cly2 = rwx.createCell(column + 1); cly2.setCellValue(conn.rs1.getString("DistrictNom")); cly2.setCellStyle(style2);//gsn sites do not have a yearmonth Cell cly1 = rwx.createCell(column + 2); cly1.setCellValue(conn.rs1.getString("SubPartnerNom")); cly1.setCellStyle(style2); //if the current list is not inclusive of GSNs if (!conn.rs.getString(1).equals("1")) { Cell cly3 = rwx.createCell(column + 3); cly3.setCellValue(new Integer(conn.rs1.getString("yearmonth").substring(0, 4))); cly3.setCellStyle(style2); //the month section Cell cly3x = rwx.createCell(column + 4); cly3x.setCellValue(new Integer(conn.rs1.getString("yearmonth").substring(4))); cly3x.setCellStyle(style2); //my key is a String mykey = Sections[a] + conn.rs1.getString("SubPartnerNom") + "_" + conn.rs1.getString("yearmonth") + "_"; //add all the facilities at this point //ignore the sites in ART since they are static if (!keyal.contains(mykey)) { keyal.add(mykey); countyal.add(conn.rs1.getString("County")); scountyal.add(conn.rs1.getString("DistrictNom")); facilal.add(conn.rs1.getString("SubPartnerNom")); sectional.add(Sections[a]); yearmonthal.add(conn.rs1.getString("yearmonth")); monthal.add(conn.rs1.getString("yearmonth").substring(4)); } } Row++; } if (conn.rs.getString(1).equals("1")) { column += 3; } else { column += 5; } if (conn.rs.getString("id").equals("1")) { shet.addMergedRegion(new CellRangeAddress(1, 1, 0, column - 1)); } else { shet.addMergedRegion(new CellRangeAddress(1, 1, column - 5, column - 1)); } } //end of all queries per section shet.addMergedRegion(new CellRangeAddress(0, 0, 0, column - 1)); } // end of sheets loop //create a new sheet //county subcounty facility yearmonth section Sheet shet = wb.getSheet("Sheet1"); Row rw = shet.createRow(0); Cell cl0 = rw.createCell(0); cl0.setCellValue("county"); cl0.setCellStyle(stylex1); Cell cl1 = rw.createCell(1); cl1.setCellValue("subcounty"); cl1.setCellStyle(stylex1); Cell cl2 = rw.createCell(2); cl2.setCellValue("facility"); cl2.setCellStyle(stylex1); Cell cl3 = rw.createCell(3); cl3.setCellValue("year"); cl3.setCellStyle(stylex1); Cell cl4 = rw.createCell(4); cl4.setCellValue("month"); cl4.setCellStyle(stylex1); Cell cl5 = rw.createCell(5); cl5.setCellValue("section"); cl5.setCellStyle(stylex1); for (int q = 0; q < keyal.size(); q++) { Row rwx = shet.createRow(q + 1); Cell cl01 = rwx.createCell(0); cl01.setCellValue(countyal.get(q).toString()); cl01.setCellStyle(style2); Cell cl11 = rwx.createCell(1); cl11.setCellValue(scountyal.get(q).toString()); cl11.setCellStyle(style2); Cell cl21 = rwx.createCell(2); cl21.setCellValue(facilal.get(q).toString()); cl21.setCellStyle(style2); Cell cl31 = rwx.createCell(3); cl31.setCellValue(new Integer(yearmonthal.get(q).toString().substring(0, 4))); cl31.setCellStyle(style2); Cell cl41 = rwx.createCell(4); cl41.setCellValue(new Integer(monthal.get(q).toString())); cl41.setCellStyle(style2); Cell cl51 = rwx.createCell(5); cl51.setCellValue(sectional.get(q).toString()); cl51.setCellStyle(style2); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=GapAnalysis_For" + periodname + "_Generatted_On_" + createdOn + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); outStream.close(); pkg.close(); if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.st != null) { conn.st.close(); } File file = new File(pathtodelete); System.out.println("path: 2" + pathtodelete); if (file.delete()) { System.out.println(file.getName() + " is deleted!"); } else { System.out.println("Delete operation failed."); } } catch (SQLException ex) { Logger.getLogger(gapAnalysis.class.getName()).log(Level.SEVERE, null, ex); } catch (InvalidFormatException ex) { Logger.getLogger(gapAnalysis.class.getName()).log(Level.SEVERE, null, ex); } finally { } }
From source file:gov.anl.cue.arcane.engine.matrix.MatrixEngine.java
License:Open Source License
/** * Creates the row header./*from w w w . j a v a 2 s . c o m*/ * * @param workbook the workbook * @param sheet the sheet * @param rowIndex the row index * @param label1 the label1 * @param label2 the label2 */ public void createRowHeader(XSSFWorkbook workbook, XSSFSheet sheet, int rowIndex, String label1, String label2) { // Create the needed highlight style. XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(197, 217, 241))); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(font); // Create the requested row. XSSFRow row = sheet.createRow(0); XSSFCell cell = row.createCell(0); cell.setCellValue(label1); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(label2); cell.setCellStyle(cellStyle); }
From source file:gov.anl.cue.arcane.engine.matrix.MatrixModel.java
License:Open Source License
/** * Write.//from w w w .j av a 2s . c o m * * @param fileName the file name * @return true, if successful */ public boolean write(String fileName) { // Check for errors. try { // Create the requested workbook image. XSSFWorkbook workbook = new XSSFWorkbook(); // Create the needed highlight style. XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(197, 217, 241))); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); XSSFFont font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(font); // Fill in the fitness sheet. this.writeFitnessFunctionInformation(workbook, cellStyle); // Fill in the variables. this.writeScanVariables(workbook, cellStyle); // Attempt to create the requested file from the workbook image. FileOutputStream file = new FileOutputStream(fileName); workbook.write(file); file.close(); // Catch errors. } catch (Exception e) { // Note the error. return false; } // Return the default results. return true; }