List of usage examples for org.apache.poi.ss.usermodel Sheet createFreezePane
void createFreezePane(int colSplit, int rowSplit);
From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.export.template.AbstractSheetGenerator.java
License:Open Source License
/** * Creates the header for the specified sheet. *///w w w .j av a2 s. c om private void createSheetHeadline() { Sheet sheet = sheetContext.getSheet(); sheet.createFreezePane(0, FIRST_DATA_ROW_NO); Cell sheetHeaderCell = sheet.createRow(0).createCell(0); sheetHeaderCell.setCellStyle(wbContext.getStyles().get(IteraExcelStyle.HEADER)); sheetHeaderCell.setCellValue(createCompleteSheetName()); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1")); // headline }
From source file:demo.poi.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); // turn off gridlines sheet.setDisplayGridlines(false);// ww w.j a v a 2 s . com sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); // the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); // the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } // columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } // freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { String str = data[i][j]; cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } // group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); // set column widths, the width is measured in units of 1/256th of a // character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "target/businessplan.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:edu.casetools.rcase.extensions.excel.control.ContainerTableExporter.java
License:Open Source License
private void createSheet(String sheetName) { Sheet sheet = workbook.createSheet(ExcelUtils.getInstance().createSafeSheetName(sheetName)); List<TableHeaderData> columnHeader = table.getData().getxHeaderList(); createColumnHeaders(sheet, columnHeader); sheet.createFreezePane(1, 1); int rowNumber = 1; for (ContainerRow rowData : table.getData().getDataList()) { createRow(sheet, rowNumber, rowData); rowNumber++;// www .ja v a 2 s. c om } resizeColumn(sheet, columnHeader.size()); }
From source file:edu.casetools.rcase.extensions.excel.control.DependencyTableExporter.java
License:Open Source License
private void createSheet(String sheetName) { Sheet sheet = workbook.createSheet(ExcelUtils.getInstance().createSafeSheetName(sheetName)); List<MObject> columnHeader = table.getData().getxHeaderList(); createColumnHeaders(sheet, columnHeader, 0); sheet.createFreezePane(2, 2); int rowNumber = 2; for (MObject rowHeader : table.getData().getyHeaderList()) { createRow(sheet, rowNumber, rowHeader, columnHeader); rowNumber++;//from www. j a v a 2 s . co m } resizeColumn(sheet, columnHeader.size()); }
From source file:excel.PoiWriteExcelFile.java
public static int generarReporte() { //Calendar cal=Calendar.getInstance(); Calendar cal = WorkMonitorUI.instante; try {/*from www .j a v a 2 s.com*/ FileOutputStream fileOut = new FileOutputStream("HH_" + instante.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()) .toUpperCase() + "_" + persona.getNombre().toUpperCase().charAt(0) + "." + persona.getApellido().toUpperCase() + "_" + instante.get(Calendar.YEAR) + ".xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet( cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()).toUpperCase() + "-" + cal.get(Calendar.YEAR)); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Calibri"); font.setItalic(false); font.setBold(true); font.setColor(HSSFColor.BLACK.index); cellStyle.setFont(font); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle diasStyle = workbook.createCellStyle(); diasStyle.setFillForegroundColor(HSSFColor.SEA_GREEN.index); diasStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setFontName("Calibri"); font.setItalic(false); font.setBold(true); font.setColor(HSSFColor.WHITE.index); diasStyle.setFont(font); diasStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); diasStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); diasStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle schedStyle = workbook.createCellStyle(); schedStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); schedStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font3 = workbook.createFont(); font3.setFontHeightInPoints((short) 11); font3.setFontName("Calibri"); font3.setItalic(false); font3.setColor(HSSFColor.BLACK.index); schedStyle.setFont(font3); schedStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); schedStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); schedStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle workdayStyle = workbook.createCellStyle(); //workdayStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); workdayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); workdayStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); workdayStyle.setWrapText(true); HSSFFont font2 = workbook.createFont(); font2.setFontHeightInPoints((short) 8); font2.setFontName("Serif"); font2.setItalic(false); //font2.setColor(HSSFColor.YELLOW.index); workdayStyle.setFont(font2); workdayStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle weekendStyle = workbook.createCellStyle(); weekendStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); weekendStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); weekendStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); weekendStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); weekendStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle horarioStyle = workbook.createCellStyle(); horarioStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); horarioStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); horarioStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); horarioStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); horarioStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font4 = workbook.createFont(); font4.setFontHeightInPoints((short) 10); font4.setFontName("Serif"); font4.setItalic(false); font4.setBold(true); //font2.setColor(HSSFColor.YELLOW.index); horarioStyle.setFont(font4); // index from 0,0... cell A1 is cell(0,0) HSSFRow row1 = worksheet.createRow((short) 0); row1.setHeight((short) 500); //System.out.println("cal.get(Calendar.YEAR)="+cal.get(Calendar.YEAR)); HSSFCell cellA1 = row1.createCell((short) 0); cellA1.setCellValue( cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()).toUpperCase() + "-" + cal.get(Calendar.YEAR)); cellA1.setCellStyle(cellStyle); HSSFRow row2 = worksheet.createRow((short) 1); HSSFCell cellA4 = row2.createCell((short) 0); cellA4.setCellValue("Horario"); cellA4.setCellStyle(horarioStyle); //row2.setHeight((short)500); HSSFRow row3 = worksheet.createRow((short) 2); HSSFCell cellA3 = row3.createCell((short) 0); cellA3.setCellValue("Inicio - Trmino"); cellA3.setCellStyle(diasStyle); Calendar hora = Calendar.getInstance(); hora.set(Calendar.HOUR_OF_DAY, 9); hora.set(Calendar.MINUTE, 0); hora.set(Calendar.SECOND, 0); SimpleDateFormat sdf = new SimpleDateFormat("HH:mm"); HSSFCell cellXn; for (int i = 0; i < 29; ++i) { HSSFRow row = worksheet.createRow((short) i + 3); row.setHeight((short) 500); cellXn = row.createCell((short) 0); String horaIni = sdf.format(hora.getTime()); hora.add(Calendar.MINUTE, 30); String horaFin = sdf.format(hora.getTime()); cellXn.setCellValue(horaIni + " - " + horaFin); cellXn.setCellStyle(schedStyle); } System.out.println("cal.get(Calendar.MONTH)1=" + cal.get(Calendar.MONTH)); cal.add(Calendar.DAY_OF_MONTH, -cal.get(Calendar.DAY_OF_MONTH) + 1); int diasMes = cal.getActualMaximum(Calendar.DAY_OF_MONTH); System.out.println("cal.get(Calendar.MONTH)2=" + cal.get(Calendar.MONTH)); sdf = new SimpleDateFormat("EEEE d"); System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)1=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); for (int i = 0; i < diasMes; ++i) { cellXn = row2.createCell((short) i + 1); String dia = sdf.format(cal.getTime()); dia = Character.toUpperCase(dia.charAt(0)) + dia.substring(1); cellXn.setCellValue(dia); cellXn.setCellStyle(horarioStyle); //System.out.println("cal.get(Calendar.DAY_OF_MONTH)="+cal.get(Calendar.DAY_OF_MONTH)); cal.add(Calendar.DAY_OF_MONTH, 1); } for (int i = 0; i < diasMes; ++i) { cellXn = row3.createCell((short) i + 1); cellXn.setCellValue("Descripcin"); cellXn.setCellStyle(diasStyle); } System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)2=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); // Retroceder mes para que quede como estaba cal.add(Calendar.MONTH, -1); //cal.add(Calendar.DAY_OF_MONTH, -1); System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)3=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); HhDao hhDao = new HhDao(); Object[][] hh = new Object[29][cal.getActualMaximum(Calendar.DAY_OF_MONTH)]; hh = hhDao.getByMes(WorkMonitorUI.persona.getId(), cal.getTime()); cal.set(Calendar.DAY_OF_MONTH, 1); Sheet sheet = workbook.getSheetAt(0); sdf = new SimpleDateFormat("EEEE"); HSSFPatriarch _drawing = (HSSFPatriarch) sheet.createDrawingPatriarch(); CreationHelper factory = workbook.getCreationHelper(); for (int i = 0; i < 29; ++i) { Row r = sheet.getRow(i + 3); for (int j = 0; j < diasMes; ++j) { if (hh[i][j].toString() != "") { cellXn = (HSSFCell) r.createCell((short) j + 1); Hh _hh = (Hh) hh[i][j]; cellXn.setCellValue( _hh.getTarea().getNombre().trim() + ": " + _hh.getActividad().getNombre().trim()); HSSFAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5); org.apache.poi.ss.usermodel.Comment comment = _drawing.createComment(anchor); String comentario = _hh.getTarea().getComentario().toLowerCase(); if (_hh.getComentario() != null) comentario = comentario + _hh.getComentario().toLowerCase(); RichTextString str = factory.createRichTextString(comentario); comment.setString(str); cellXn.setCellComment(comment); } else { cellXn = (HSSFCell) r.createCell((short) j + 1); cellXn.setCellValue(""); } //System.out.println("sdf.format(cal.getTime())="+sdf.format(cal.getTime())); if (Arrays.asList("sbado", "domingo").contains(sdf.format(cal.getTime()))) cellXn.setCellStyle(weekendStyle); else cellXn.setCellStyle(workdayStyle); sheet.setColumnWidth(j, 5000); cal.add(Calendar.DAY_OF_MONTH, 1); //sheet.autoSizeColumn(j); } // Retroceder mes para que quede como estaba cal.add(Calendar.MONTH, -1); System.out.println("cal.get(Calendar.MONTH)3=" + cal.get(Calendar.MONTH)); cal.set(Calendar.DAY_OF_MONTH, 1); } sheet.setColumnWidth(diasMes, 5000); WorkMonitorUI.instante = Calendar.getInstance(); sheet.setColumnWidth(0, 5000); sheet.createFreezePane(1, 3); // Freeze just one row //sheet.createFreezePane( 0, 1, 0, 1 ); workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); return -1; } catch (IOException e) { e.printStackTrace(); return -2; } return 1; }
From source file:fi.thl.pivot.export.XlsxExporter.java
private void doExport(Model model, OutputStream out) throws IOException { Map<String, ?> params = model.asMap(); Workbook wb = new XSSFWorkbook(); createExportStyles(wb);/*from w ww . j ava 2 s.c om*/ Sheet sheet = wb.createSheet( WorkbookUtil.createSafeSheetName(((Label) params.get("cubeLabel")).getValue(language))); Pivot pivot = (Pivot) params.get("pivot"); int rowNumber = 0; boolean showCodes = params.containsKey("sc"); rowNumber = createColumnHeaders(pivot, sheet, showCodes); rowNumber = printData(sheet, pivot, rowNumber, showCodes); mergeRowHeaders(sheet, pivot); rowNumber = printFilters(params, sheet, rowNumber, pivot.getColumnCount() + pivot.getColumns().size()); printCopyrightNotice(sheet, rowNumber, params, pivot.getColumnCount() + pivot.getColumns().size()); printCurrentMeasureIfOnlyOneMeasureShown(params, sheet, pivot); mergeTopLeftCorner(sheet, pivot); autosizeColumns(sheet, pivot); sheet.createFreezePane(pivot.getRows().size(), pivot.getColumns().size()); wb.write(out); wb.close(); }
From source file:gov.nih.nci.cadsr.cdecurate.test.TestSpreadsheetDownload.java
License:BSD License
private void createDownloadColumns(ArrayList<String[]> allRows) { final int MAX_ROWS = 65000; String sheetName = "Custom Download"; int sheetNum = 1; String fillIn = "false";// set true to fill in all values. String[] columns = null;// w w w . j a va 2 s . c o m ArrayList<String> defaultHeaders = new ArrayList<String>(); for (String cName : allExpandedColumnHeaders) { if (cName.endsWith("IDSEQ") || cName.startsWith("CD ") || cName.startsWith("Conceptual Domain")) { /* skip */ } else { System.out.println("cName = " + cName); defaultHeaders.add(cName); } } columns = defaultHeaders.toArray(new String[defaultHeaders.size()]); int[] colIndices = new int[columns.length]; for (int i = 0; i < columns.length; i++) { String colName = columns[i]; if (columnHeaders.indexOf(colName) < 0) { String tempType = arrayColumnTypes.get(colName); int temp = columnTypes.indexOf(tempType); colIndices[i] = temp; } else { int temp = columnHeaders.indexOf(colName); colIndices[i] = temp; } } Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet(sheetName); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle boldCellStyle = wb.createCellStyle(); boldCellStyle.setFont(font); boldCellStyle.setAlignment(CellStyle.ALIGN_GENERAL); Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); String temp; for (int i = 0; i < columns.length; i++) { Cell cell = headerRow.createCell(i); temp = columns[i]; cell.setCellValue(temp); cell.setCellStyle(boldCellStyle); } // freeze the first row sheet.createFreezePane(0, 1); Row row = null; Cell cell; int rownum = 1; int bump = 0; int i = 0; try { System.out.println("Total CDEs to download [" + allRows.size() + "]"); for (i = 0; i < allRows.size(); i++, rownum++) { // Check if row already exists int maxBump = 0; if (sheet.getRow(rownum + bump) == null) { row = sheet.createRow(rownum + bump); } if (allRows.get(i) == null) continue; for (int j = 0; j < colIndices.length; j++) { cell = row.createCell(j); String currentType = columnTypes.get(colIndices[j]); if (currentType.endsWith("_T")) { // Deal with CS/CSI String[] originalArrColNames = typeMap.get(currentType).get(0); // Find current column in original data int originalColumnIndex = -1; for (int a = 0; a < originalArrColNames.length; a++) { if (columns[j].equals(originalArrColNames[a])) { originalColumnIndex = a; break; } } // ArrayList<HashMap<String,ArrayList<String[]>>> // arrayData1 = // (ArrayList<HashMap<String,ArrayList<String[]>>>)arrayData; HashMap<String, List<String[]>> typeArrayData = arrayData.get(i); ArrayList<String[]> rowArrayData = (ArrayList<String[]>) typeArrayData.get(currentType); if (rowArrayData != null) { int tempBump = 0; for (int nestedRowIndex = 0; nestedRowIndex < rowArrayData.size(); nestedRowIndex++) { String[] nestedData = rowArrayData.get(nestedRowIndex); String data = ""; if (currentType.contains("DERIVED")) { // Derived data element is special double // nested, needs to be modified to be more // general. // General DDE information is in the first 4 // columns, but contained in the first row // of the Row Array Data if (originalColumnIndex < 5) { if (nestedRowIndex == 0) data = (originalColumnIndex > 0) ? nestedData[originalColumnIndex] : nestedData[originalColumnIndex + 1]; } else { if (nestedRowIndex + 1 < rowArrayData.size()) { data = rowArrayData.get(nestedRowIndex + 1)[originalColumnIndex - 5]; } } } else data = nestedData[originalColumnIndex]; logger.debug( "at line 828 of TestSpreadsheetDownload.java*****" + data + currentType); if (currentType.contains("VALID_VALUE")) { data = AdministeredItemUtil.truncateTime(data); } cell.setCellValue(data); tempBump++; if (nestedRowIndex < rowArrayData.size() - 1) { row = sheet.getRow(rownum + bump + tempBump); if (row == null) { if (rownum + bump + tempBump >= MAX_ROWS) { sheet = wb.createSheet(sheetName + "_" + sheetNum); sheetNum++; rownum = 1; bump = 0; tempBump = 0; } row = sheet.createRow(rownum + bump + tempBump); } cell = row.createCell(j); } else { // Go back to top row row = sheet.getRow(rownum + bump); if (tempBump > maxBump) maxBump = tempBump; } } } } else { temp = allRows.get(i)[colIndices[j]]; logger.debug("at line 866 of TestSpreadsheetDownload.java*****" + temp + currentType); if (currentType.equalsIgnoreCase("Date")) { temp = AdministeredItemUtil.truncateTime(temp); } cell.setCellValue(temp); } } bump = bump + maxBump; if (fillIn != null && (fillIn.equals("true") || fillIn.equals("yes") && bump > 0)) { sheet = fillInBump(sheet, i, rownum, bump, allRows, columnTypes, colIndices); rownum = rownum + bump; bump = 0; } } } catch (Exception e) { e.printStackTrace(); } try { // Please specify the path below if needed, otherwise it will create in the root/dir where this test class is run fileOutputStream = new FileOutputStream("Test_Excel.xls"); wb.write(fileOutputStream); } catch (Exception e) { e.printStackTrace(); } finally { /** * Close the fileOutputStream. */ try { if (fileOutputStream != null) { fileOutputStream.close(); } } catch (IOException ex) { ex.printStackTrace(); } } }
From source file:gov.nih.nci.cadsr.cdecurate.tool.CustomDownloadServlet.java
License:BSD License
private void createDownloadColumns(ArrayList<String[]> allRows) { final int MAX_ROWS = 65000; String sheetName = "Custom Download"; int sheetNum = 1; String colString = (String) this.m_classReq.getParameter("cdlColumns"); String fillIn = (String) this.m_classReq.getParameter("fillIn"); ArrayList<String> allHeaders = (ArrayList<String>) m_classReq.getSession().getAttribute("headers"); ArrayList<String> allExpandedHeaders = (ArrayList<String>) m_classReq.getSession() .getAttribute("allExpandedHeaders"); ArrayList<String> allTypes = (ArrayList<String>) m_classReq.getSession().getAttribute("types"); HashMap<String, ArrayList<String[]>> typeMap = (HashMap<String, ArrayList<String[]>>) m_classReq .getSession().getAttribute("typeMap"); ArrayList<HashMap<String, ArrayList<String[]>>> arrayData = (ArrayList<HashMap<String, ArrayList<String[]>>>) m_classReq .getSession().getAttribute("arrayData"); HashMap<String, String> arrayColumnTypes = (HashMap<String, String>) m_classReq.getSession() .getAttribute("arrayColumnTypes"); String[] columns = null;/*from ww w .j a v a 2 s. c o m*/ if (colString != null && !colString.trim().equals("")) { columns = colString.split(","); } else { ArrayList<String> defaultHeaders = new ArrayList<String>(); for (String cName : allExpandedHeaders) { if (cName.endsWith("IDSEQ") || cName.startsWith("CD ") || cName.startsWith("Conceptual Domain")) { /*skip*/ } else { System.out.println("cName = " + cName); defaultHeaders.add(cName); } } columns = defaultHeaders.toArray(new String[defaultHeaders.size()]); } int[] colIndices = new int[columns.length]; for (int i = 0; i < columns.length; i++) { String colName = columns[i]; if (allHeaders.indexOf(colName) < 0) { String tempType = arrayColumnTypes.get(colName); int temp = allTypes.indexOf(tempType); colIndices[i] = temp; } else { int temp = allHeaders.indexOf(colName); colIndices[i] = temp; } } Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet(sheetName); Font font = wb.createFont(); //GF30779 font.setBoldweight(Font.BOLDWEIGHT_BOLD); //GF30779 CellStyle boldCellStyle = wb.createCellStyle(); //GF30779 boldCellStyle.setFont(font); //GF30779 boldCellStyle.setAlignment(CellStyle.ALIGN_GENERAL); //GF30779 Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); String temp; for (int i = 0; i < columns.length; i++) { Cell cell = headerRow.createCell(i); temp = columns[i]; cell.setCellValue(temp); cell.setCellStyle(boldCellStyle); //GF30779 } //freeze the first row sheet.createFreezePane(0, 1); Row row = null; Cell cell; int rownum = 1; int bump = 0; boolean fillRow = false; int i = 0; long startTime = System.currentTimeMillis(); try { System.out.println("Total CDEs to download [" + allRows.size() + "]"); for (i = 0; i < allRows.size(); i++, rownum++) { //Check if row already exists int maxBump = 0; if (sheet.getRow(rownum + bump) == null) { row = sheet.createRow(rownum + bump); } if (allRows.get(i) == null) continue; for (int j = 0; j < colIndices.length; j++) { cell = row.createCell(j); String currentType = allTypes.get(colIndices[j]); if (currentType.endsWith("_T")) { //Deal with CS/CSI String[] originalArrColNames = typeMap.get(currentType).get(0); //Find current column in original data int originalColumnIndex = -1; for (int a = 0; a < originalArrColNames.length; a++) { if (columns[j].equals(originalArrColNames[a])) { originalColumnIndex = a; break; } } HashMap<String, ArrayList<String[]>> typeArrayData = arrayData.get(i); ArrayList<String[]> rowArrayData = typeArrayData.get(currentType); if (rowArrayData != null) { int tempBump = 0; for (int nestedRowIndex = 0; nestedRowIndex < rowArrayData.size(); nestedRowIndex++) { String[] nestedData = rowArrayData.get(nestedRowIndex); String data = ""; if (currentType.contains("DERIVED")) { //Derived data element is special double nested, needs to be modified to be more general. //General DDE information is in the first 4 columns, but contained in the first row of the Row Array Data if (originalColumnIndex < 5) { if (nestedRowIndex == 0) data = (originalColumnIndex > 0) ? nestedData[originalColumnIndex] : nestedData[originalColumnIndex + 1]; //This skips the 2nd entry, description, which is not to be shown. } else { if (nestedRowIndex + 1 < rowArrayData.size()) { data = rowArrayData.get(nestedRowIndex + 1)[originalColumnIndex - 5]; } } } else data = nestedData[originalColumnIndex]; logger.debug("at line 960 of CustomDownloadServlet.java*****" + data + currentType); if (currentType.contains("VALID_VALUE")) { //GF30779 data = AdministeredItemUtil.truncateTime(data); } cell.setCellValue(data); tempBump++; if (nestedRowIndex < rowArrayData.size() - 1) { row = sheet.getRow(rownum + bump + tempBump); if (row == null) { if (rownum + bump + tempBump >= MAX_ROWS) { sheet = wb.createSheet(sheetName + "_" + sheetNum); sheetNum++; rownum = 1; bump = 0; tempBump = 0; } row = sheet.createRow(rownum + bump + tempBump); } cell = row.createCell(j); } else { //Go back to top row row = sheet.getRow(rownum + bump); if (tempBump > maxBump) maxBump = tempBump; } } } } else { temp = allRows.get(i)[colIndices[j]]; logger.debug("at line 993 of CustomDownloadServlet.java*****" + temp + currentType); if (currentType.equalsIgnoreCase("Date")) { //GF30779 temp = AdministeredItemUtil.truncateTime(temp); } cell.setCellValue(temp); } } bump = bump + maxBump; if (fillIn != null && (fillIn.equals("true") || fillIn.equals("yes") && bump > 0)) { sheet = fillInBump(sheet, i, rownum, bump, allRows, allTypes, colIndices); rownum = rownum + bump; bump = 0; } } } catch (Exception e) { e.printStackTrace(); } // sheet.setZoom(3, 4); //GF30779 // Write the output to response stream. try { m_classRes.setContentType("application/vnd.ms-excel"); m_classRes.setHeader("Content-Disposition", "attachment; filename=\"customDownload.xls\""); OutputStream out = m_classRes.getOutputStream(); wb.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:gov.nih.nci.evs.app.neopl.XLSXMetadataUtils.java
License:Open Source License
public static boolean freezeRow(String filename, int sheetNumber, int rowNum) { FileOutputStream fileOut = null; boolean status = false; try {/*from w w w . j a v a 2 s.c o m*/ InputStream inp = new FileInputStream(filename); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(sheetNumber); sheet.createFreezePane(0, rowNum); // this will freeze first rowNum rows fileOut = new FileOutputStream(filename); wb.write(fileOut); status = true; System.out.println("File modified " + filename); } catch (Exception ex) { //ex.printStackTrace(); System.out.println("ERROR: freezeRow " + filename); } finally { try { fileOut.close(); } catch (Exception ex) { ex.printStackTrace(); } } return status; }
From source file:guru.qas.martini.report.DefaultTraceabilityMatrix.java
License:Apache License
protected void addHeader(Sheet sheet) { Row row = sheet.createRow(0);// w ww . j a v a2s .c o m Workbook workbook = sheet.getWorkbook(); CellStyle style = getHeaderStyle(workbook); for (int i = 0; i < columns.size(); i++) { TraceabilityColumn column = columns.get(i); Cell cell = row.createCell(i, CellType.STRING); String label = column.getLabel(); cell.setCellValue(label); cell.setCellStyle(style); } sheet.createFreezePane(0, 1); }