List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet autoSizeColumn
@Override public void autoSizeColumn(int column)
This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.
From source file:net.mindengine.oculus.frontend.service.report.ExcelTestSearchReportGenerator.java
License:Open Source License
/** * Generates Excel spreadsheet./*from www . java 2 s . co m*/ * * @param searchResult Search result for test or document (test-case) * @throws Exception */ public void writeExcelReports(BrowseResult<?> searchResult, Long projectId, HttpServletRequest request, HttpServletResponse response) throws Exception { int cellOffset = 5; /* * Customizations which will be exported to XLS spreadsheet. */ Collection<Customization> customizationsExport = new LinkedList<Customization>(); /* * Here will be cell ids stored for each customization. This is needed because there might be a lot of merged cells for list customizations */ Map<Long, Integer> customizationCells = new HashMap<Long, Integer>(); if (projectId != null && projectId > 0) { Collection<Customization> customizations = customizationDAO.getCustomizations(projectId, unit); for (Customization customization : customizations) { //Checking if the user has selected this specific customization for exporting if ("on".equals(request.getParameter("cexport" + customization.getId()))) { customizationCells.put(customization.getId(), cellOffset); //Checking if the customization contains possible values and fetching them all if (customization.getType().equals(Customization.TYPE_CHECKLIST) || customization.getType().equals(Customization.TYPE_LIST)) { customization.setPossibleValues( customizationDAO.getCustomizationPossibleValues(customization.getId())); cellOffset += customization.getPossibleValues().size(); } else { cellOffset += 1; } customizationsExport.add(customization); } } } /* * Generating the Excel spreadsheet */ OutputStream outputStream = response.getOutputStream(); response.setContentType("application/ms-excel"); HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet(); HSSFFont fontHeader = workBook.createFont(); fontHeader.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontHeader.setColor(HSSFColor.WHITE.index); HSSFCellStyle columnHeaderStyle = workBook.createCellStyle(); columnHeaderStyle.setBorderTop((short) 2); columnHeaderStyle.setBorderLeft((short) 2); columnHeaderStyle.setBorderRight((short) 2); columnHeaderStyle.setBorderBottom((short) 2); columnHeaderStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); columnHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); columnHeaderStyle.setFont(fontHeader); HSSFCellStyle columnRootHeaderStyle = workBook.createCellStyle(); //columnRootHeaderStyle.cloneStyleFrom(columnHeaderStyle); columnRootHeaderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCell cell; HSSFRow headerRow = sheet.createRow(0); HSSFRow header2Row = sheet.createRow(1); sheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) 4)); cell = headerRow.createCell((short) 0); cell.setCellValue("Common"); cell.setCellStyle(columnRootHeaderStyle); cell = header2Row.createCell((short) 0); cell.setCellValue("Test"); cell.setCellStyle(columnHeaderStyle); cell = header2Row.createCell((short) 1); cell.setCellValue("Project"); cell.setCellStyle(columnHeaderStyle); cell = header2Row.createCell((short) 2); cell.setCellValue("Sub-Project"); cell.setCellStyle(columnHeaderStyle); cell = header2Row.createCell((short) 3); cell.setCellValue("Author"); cell.setCellStyle(columnHeaderStyle); cell = header2Row.createCell((short) 4); cell.setCellValue("Created"); cell.setCellStyle(columnHeaderStyle); for (Customization customization : customizationsExport) { int cellId = customizationCells.get(customization.getId()); if (customization.getPossibleValues() != null) { int size = customization.getPossibleValues().size(); if (size > 1) { sheet.addMergedRegion( new Region((short) 0, (short) 0, (short) cellId, (short) (cellId + size - 1))); } /* * Exporting possible values */ int offset = 0; for (CustomizationPossibleValue cpv : customization.getPossibleValues()) { cell = header2Row.createCell((short) (cellId + offset)); cell.setCellValue(cpv.getPossibleValue()); cell.setCellStyle(columnHeaderStyle); offset++; } } else { cell = header2Row.createCell((short) cellId); cell.setCellStyle(columnHeaderStyle); } cell = headerRow.createCell((short) cellId); cell.setCellValue(customization.getName()); cell.setCellStyle(columnRootHeaderStyle); } HSSFCellStyle cellStyle = workBook.createCellStyle(); HSSFCellStyle checkboxStyle = workBook.createCellStyle(); checkboxStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); checkboxStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); checkboxStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontCheckbox = workBook.createFont(); fontCheckbox.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontCheckbox.setColor(HSSFColor.BLACK.index); checkboxStyle.setFont(fontCheckbox); HSSFCellStyle boolYesStyle = workBook.createCellStyle(); boolYesStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); boolYesStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFCellStyle boolNoStyle = workBook.createCellStyle(); boolNoStyle.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index); boolNoStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); /* * Used in order to cache user names. Key = ID, Value = User name */ Map<Long, String> usersCache = new HashMap<Long, String>(); int j = 2; for (Object object : searchResult.getResults()) { HSSFRow row = sheet.createRow(j); String name, parentProjectName, projectName, authorName; Date date; Long objectId; if (object instanceof Test) { Test test = (Test) object; name = test.getName(); parentProjectName = test.getParentProjectName(); projectName = test.getProjectName(); authorName = test.getAuthorName(); objectId = test.getId(); date = test.getDate(); } else throw new IllegalArgumentException(object.getClass().getName()); cell = row.createCell((short) 0); cell.setCellValue(name); cell.setCellStyle(cellStyle); cell = row.createCell((short) 1); cell.setCellValue(parentProjectName); cell.setCellStyle(cellStyle); cell = row.createCell((short) 2); cell.setCellValue(projectName); cell.setCellStyle(cellStyle); cell = row.createCell((short) 3); cell.setCellValue(authorName); cell.setCellStyle(cellStyle); cell = row.createCell((short) 4); SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy"); cell.setCellValue(sdf.format(date)); cell.setCellStyle(cellStyle); int offset = 5; for (Customization customization : customizationsExport) { UnitCustomizationValue ucv = customizationDAO.getUnitCustomizationValue(customization.getId(), objectId); if (customization.getType().equals(Customization.TYPE_CHECKLIST) || customization.getType().equals(Customization.TYPE_LIST)) { for (CustomizationPossibleValue cpv : customization.getPossibleValues()) { boolean matches = false; if (customization.getType().equals(Customization.TYPE_LIST)) { if (ucv != null && ucv.getValue() != null && ucv.getValue().equals("" + cpv.getId())) { matches = true; } } else { String s = "(" + cpv.getId() + ")"; if (ucv != null && ucv.getValue() != null && ucv.getValue().contains(s)) { matches = true; } } if (matches) { cell = row.createCell((short) offset); cell.setCellValue("X"); cell.setCellStyle(checkboxStyle); } offset++; } } else { if (ucv != null) { cell = row.createCell((short) offset); cell.setCellStyle(cellStyle); if (customization.getType().equals(Customization.TYPE_ASSIGNEE)) { if (ucv.getValue() != null && !ucv.getValue().isEmpty()) { try { Long userId = Long.parseLong(ucv.getValue()); /* * Chaching user names by their ids */ String userName = null; if (!usersCache.containsKey(userId)) { User user = userDAO.getUserById(userId); if (user != null) { userName = user.getName(); } else userName = ""; usersCache.put(userId, userName); } else userName = usersCache.get(userId); cell.setCellValue(userName); } catch (Exception e) { e.printStackTrace(); } } } else if (customization.getType().equals(Customization.TYPE_CHECKBOX)) { if (ucv.getValue() != null) { if (ucv.getValue().equals("true")) { cell.setCellValue("Yes"); cell.setCellStyle(boolYesStyle); } else { cell.setCellValue("No"); cell.setCellStyle(boolNoStyle); } } } else { cell.setCellValue(ucv.getValue()); } } offset++; } } j++; } /* * Making the text to fit in all cells */ for (short i = 0; i < (short) cellOffset + 1; i++) { sheet.autoSizeColumn(i); } workBook.write(outputStream); outputStream.flush(); outputStream.close(); }
From source file:net.triptech.buildulator.view.ExcelTemplateView.java
License:Open Source License
@Override protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { DataGrid dataGrid = (DataGrid) model.get("dataGrid"); String sheetName = "Sheet 1"; if (StringUtils.isNotBlank(dataGrid.getTitle())) { sheetName = dataGrid.getTitle(); }/*from w w w . j ava2s . c om*/ HSSFSheet sheet = workbook.createSheet(sheetName); Font font = workbook.createFont(); font.setColor(HSSFColor.WHITE.index); HSSFCellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFont(font); int rowNum = 0; int maxColumnCount = 0; if (dataGrid.getHeaderFields().size() > 0) { HSSFRow header = sheet.createRow(rowNum); rowNum++; maxColumnCount = dataGrid.getHeaderFields().size(); int i = 0; for (String field : dataGrid.getHeaderFields()) { HSSFCell cell = header.createCell(i); cell.setCellValue(field); cell.setCellStyle(style); i++; } } for (int y = 0; y < dataGrid.getRowCount(); y++) { HSSFRow row = sheet.createRow(rowNum++); List<String> rowData = dataGrid.getRowFields(y); if (rowData.size() > maxColumnCount) { maxColumnCount = rowData.size(); } int x = 0; for (String data : rowData) { HSSFCell cell = row.createCell(x); try { double dbValue = Double.parseDouble(data); cell.setCellValue(dbValue); } catch (NumberFormatException nfe) { cell.setCellValue(data); } x++; } } for (int i = 0; i < maxColumnCount; i++) { sheet.autoSizeColumn(i); } }
From source file:net.vpc.app.vainruling.core.web.jsf.Vr.java
public void postProcessDataExporterXLS(Object document) { HSSFWorkbook book = (HSSFWorkbook) document; HSSFSheet sheet = book.getSheetAt(0); HSSFRow header = sheet.getRow(0);//from ww w.j a v a 2 s .c o m int rowCount = sheet.getPhysicalNumberOfRows(); HSSFCellStyle headerCellStyle = book.createCellStyle(); headerCellStyle.setFillForegroundColor(HSSFColor.AQUA.index); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); HSSFCreationHelper creationHelper = book.getCreationHelper(); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(headerCellStyle); } HSSFCellStyle intStyle = book.createCellStyle(); intStyle.setDataFormat((short) 1); HSSFCellStyle decStyle = book.createCellStyle(); decStyle.setDataFormat((short) 2); HSSFCellStyle dollarStyle = book.createCellStyle(); dollarStyle.setDataFormat((short) 5); int maxColumn = -1; Map<String, HSSFCellStyle> datFormats = new HashMap<>(); for (int rowInd = 1; rowInd < rowCount; rowInd++) { HSSFRow row = sheet.getRow(rowInd); int colCount = row.getPhysicalNumberOfCells(); if (maxColumn < colCount) { maxColumn = colCount; } for (int cellInd = 0; cellInd < colCount; cellInd++) { HSSFCell cell = row.getCell(cellInd); String strVal = cell.getStringCellValue(); if (strVal.startsWith("$")) { //do nothing } else { if (strVal.startsWith("'")) { strVal = strVal.substring(1); } if (PlatformUtils.isDouble(strVal)) { cell.setCellType(HSSFCell.CELL_TYPE_BLANK); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); if (PlatformUtils.isInteger(strVal)) { int intVal = Integer.valueOf(strVal.trim()); cell.setCellStyle(intStyle); cell.setCellValue(intVal); } else if (PlatformUtils.isDouble(strVal)) { double dblVal = Double.valueOf(strVal.trim()); cell.setCellStyle(decStyle); cell.setCellValue(dblVal); } } else { boolean isDate = false; for (String dteFormat : new String[] { "yyyy-MM-dd HH:mm:ss.SSS", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm", "yyyy-MM-dd", "HH:mm" }) { if (PlatformUtils.isDate(strVal, dteFormat)) { HSSFCellStyle dateStyle = datFormats.get(dteFormat.trim()); if (dateStyle == null) { dateStyle = book.createCellStyle(); dateStyle.setDataFormat(creationHelper.createDataFormat().getFormat(dteFormat)); datFormats.put(dteFormat, dateStyle); } cell.setCellStyle(dateStyle); try { cell.setCellValue(new SimpleDateFormat(dteFormat).parse(strVal)); } catch (ParseException e) { // } isDate = true; break; } } } } } } if (maxColumn >= 0) { for (int cellInd = 0; cellInd < maxColumn; cellInd++) { sheet.autoSizeColumn(cellInd); } } }
From source file:opisiame.controller.gestion_resultat.Choix_exportController.java
@FXML public void excel_export() { File excel_file = choix_chemin_enregistrement("Excel files (*.xls)", "*.xls"); if (onglet_actif.equals("questions")) { if (excel_file != null) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Resultat par question"); sheet.autoSizeColumn(5); create_data1(sheet, 0, "Question", "Pourcentage reponse A", "Pourcentage reponse B", "Pourcentage reponse C", "Pourcentage reponse D", "Pourcentage bonne rponse"); Row row = sheet.getRow(0);// w ww. j av a 2s. c o m HSSFCellStyle cellStyle = null; HSSFFont font = wb.createFont(); font.setBold(true); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); row.setRowStyle(cellStyle); for (int i = 0; i < reponse_questions.size(); i++) { Reponse_question rq = reponse_questions.get(i); create_data1(sheet, i + 1, rq.getQuestion(), rq.getStr_pourcentage_rep_a(), rq.getStr_pourcentage_rep_b(), rq.getStr_pourcentage_rep_c(), rq.getStr_pourcentage_rep_d(), rq.getStr_pourcentage()); } FileOutputStream fileOut; try { fileOut = new FileOutputStream(excel_file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } } else if (onglet_actif.equals("eleves")) { if (excel_file != null) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Resultat des tudiants"); sheet.autoSizeColumn(5); create_data2(sheet, 0, "Nom", "Prnom", "N tudiant", "Note", "Pourcentage"); Row row = sheet.getRow(0); HSSFCellStyle cellStyle = null; HSSFFont font = wb.createFont(); font.setBold(true); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); row.setRowStyle(cellStyle); for (int i = 0; i < resultats_eleves.size(); i++) { Rep_eleves_quiz re = resultats_eleves.get(i); create_data2(sheet, i + 1, re.getNom_eleve(), re.getPrenom_eleve(), re.getNum_eleve().toString(), re.getNote_eleve().toString(), re.getPourcent_eleve().toString()); } FileOutputStream fileOut; try { fileOut = new FileOutputStream(excel_file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } } else if (onglet_actif.equals("eleves_pas_num")) { if (excel_file != null) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Resultat des tudiants"); sheet.autoSizeColumn(4); create_data3(sheet, 0, "Nom", "Prnom", "Note", "Pourcentage"); Row row = sheet.getRow(0); HSSFCellStyle cellStyle = null; HSSFFont font = wb.createFont(); font.setBold(true); cellStyle = wb.createCellStyle(); cellStyle.setFont(font); row.setRowStyle(cellStyle); for (int i = 0; i < resultats_eleves.size(); i++) { Rep_eleves_quiz re = resultats_eleves.get(i); create_data3(sheet, i + 1, re.getNom_eleve(), re.getPrenom_eleve(), re.getNote_eleve().toString(), re.getPourcent_eleve().toString()); } FileOutputStream fileOut; try { fileOut = new FileOutputStream(excel_file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } } close_window(); }
From source file:org.adempiere.impexp.AbstractExcelExporter.java
License:Open Source License
private void fixColumnWidth(HSSFSheet sheet, int lastColumnIndex) { for (short colnum = 0; colnum < lastColumnIndex; colnum++) { sheet.autoSizeColumn(colnum); }//from w w w.j a v a 2 s . c o m }
From source file:org.clickframes.plugins.requirements.Requirement.java
License:Open Source License
public static void renderSpreadsheet(List<Requirement> requirements, File xls) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFCellStyle identifierStyle = RequirementsGenerator.createIdentifierColumnStyle(wb); HSSFCellStyle requirementStyle = RequirementsGenerator.createRequirementColumnStyle(wb); HSSFCellStyle prStyle = RequirementsGenerator.createRequirementColumnStyle(wb); HSSFCellStyle headerStyle = RequirementsGenerator.createHeaderColumnStyle(wb); short rowHeight = 480; short rowNo = 0; {//from www . j av a 2 s. c o m // header HSSFRow row = sheet.createRow(rowNo++); row.setHeight(rowHeight); HSSFCell cell1 = row.createCell((short) 0); cell1.setCellValue(new HSSFRichTextString("Requirement Identifier")); cell1.setCellStyle(headerStyle); HSSFCell cell2 = row.createCell((short) 1); cell2.setCellValue(new HSSFRichTextString("Software Requirement")); cell2.setCellStyle(headerStyle); HSSFCell cell3 = row.createCell((short) 2); cell3.setCellValue(new HSSFRichTextString("Product Requirement")); cell3.setCellStyle(headerStyle); } rowNo++; for (Requirement requirement : requirements) { HSSFRow row = sheet.createRow(rowNo++); row.setHeight(rowHeight); HSSFCell identifierCell = row.createCell((short) 0); identifierCell.setCellValue(new HSSFRichTextString(requirement.getIdentifier())); identifierCell.setCellStyle(identifierStyle); HSSFCell requirementCell = row.createCell((short) 1); requirementCell.setCellValue(new HSSFRichTextString(requirement.getTitle())); requirementCell.setCellStyle(requirementStyle); if (requirement.getPage() != null) { String pr = requirement.getPage().getProperties().get("pr"); HSSFCell prCell = row.createCell((short) 2); prCell.setCellValue(new HSSFRichTextString(pr)); prCell.setCellStyle(prStyle); } } sheet.autoSizeColumn((short) 0); sheet.autoSizeColumn((short) 1); wb.write(new FileOutputStream(xls)); }
From source file:org.cyberoam.iview.servlets.ExcelFileGenerator.java
License:Open Source License
/** * This method is used to handle HTTP POST requests. *///from w w w . j a va 2 s .c om public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { String startDate = null, endDate = null, limit = "1000", offset = null, applianceList = null, mode = null; int reportGroupID = -1; int reportID = -1; SqlReader sqlReader = new SqlReader(); ResultSetWrapper rsw = null; String query = null; IndexManager indexManager = null; HttpSession session = request.getSession(); ServletOutputStream out = response.getOutputStream(); response.setContentType("application/octet-stream"); try { // Archive Logs Exportation if (request.getParameter("archive") != null) { try { HashMap<String, String> criteriaList = new HashMap<String, String>(); String categoryID = (String) session.getAttribute("categoryid"); query = " where"; Date startDateDt = DateDifference.stringToDate((String) session.getAttribute("startdate"), "yyyy-MM-dd hh:mm:ss"); Date endDateDt = DateDifference.stringToDate((String) session.getAttribute("enddate"), "yyyy-MM-dd hh:mm:ss"); if (PrepareQuery.calculateDifference(startDateDt, endDateDt) > 0) { query += " upload_datetime >= '" + (String) (String) session.getAttribute("startdate") + "' and upload_datetime <= ' " + (new SimpleDateFormat("yyyy-MM-dd")).format(DateDifference .stringToDate((String) session.getAttribute("startdate"), "yyyy-MM-dd")) + " 23:59:59'"; criteriaList.put("upload_datetimeStart", ">=," + (String) session.getAttribute("startdate")); criteriaList.put("upload_datetimeEnd", "<=," + (new SimpleDateFormat("yyyy-MM-dd")).format(DateDifference .stringToDate((String) session.getAttribute("startdate"), "yyyy-MM-dd")) + " 23:59:59"); } else { query += " upload_datetime >= '" + (String) (String) session.getAttribute("startdate") + "' and upload_datetime <= ' " + (String) session.getAttribute("enddate") + "'"; criteriaList.put("upload_datetimeStart", ">=," + (String) session.getAttribute("startdate")); criteriaList.put("upload_datetimeEnd", "<=," + (String) session.getAttribute("enddate")); } query += " and device_name in (" + (String) session.getAttribute("appliancelist") + ") "; criteriaList.put("device_name", "=," + (String) session.getAttribute("appliancelist")); criteriaList.put("indexCriteria", request.getParameter("indexCriteria")); CyberoamLogger.appLog .info("ExcelGeneration : indexCriteria = " + request.getParameter("indexCriteria")); String intLotSize = iViewConfigBean.getValueByKey("Limit"); long longCurrentRecordPosition = Long.parseLong(request.getParameter("offset")); String tblName = request.getParameter("tblname"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet formattedLogs = wb.createSheet("Formatted Logs"); HSSFSheet rawLogs = wb.createSheet("Raw Logs"); HSSFRow row; HSSFCell cell; //Formatted logs Column Headings row = formattedLogs.createRow(0); ArrayList<IndexFieldsBean> indexFileList = IndexFieldsBean .getIndexFieldBeanListByCategoryID(categoryID); for (int n = 0; n < indexFileList.size() - 2; n++) { row.createCell(n).setCellValue(((IndexFieldsBean) indexFileList.get(n)).getGuiIndexName()); } // Raw Logs Column Heading. row = rawLogs.createRow(0); row.createCell(0).setCellValue("Raw Logs"); // For freezing first row in both sheets formattedLogs.createFreezePane(0, 1, 0, 1); rawLogs.createFreezePane(0, 1, 0, 1); criteriaList.put("limit", "=," + Integer.parseInt(intLotSize)); criteriaList.put("offset", "=," + longCurrentRecordPosition); criteriaList.put("categoryID", "=," + categoryID); ArrayList recordArray = null; if (request.getParameter("indexCriteria") == null || request.getParameter("indexCriteria").equals("null")) recordArray = SearchIndexBean.getDateRangeData(criteriaList); else recordArray = SearchIndexBean.getSearchData(criteriaList); if (recordArray.size() == Integer.parseInt(intLotSize) + 1) { recordArray.remove(recordArray.size() - 1); } //Formatted Logs data ArrayList columnArray = null; String dataValue; if (recordArray != null) { for (int recordcount = 0; recordcount < recordArray.size(); recordcount++) { columnArray = (ArrayList) recordArray.get(recordcount); row = formattedLogs.createRow(recordcount + 1); for (int columncount = 0; columncount < columnArray.size() - 1; columncount++) { dataValue = (String) columnArray.get(columncount); formattedLogs.autoSizeColumn((short) columncount); cell = row.createCell(columncount); if (dataValue != null && dataValue.length() > 0) { // 1 is for numeric data type if (Integer.parseInt((indexFileList.get(columncount)).getDataType()) == 1) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Integer.parseInt(dataValue)); } else cell.setCellValue(dataValue); } else { cell.setCellValue("-"); } } } } // Raw Logs if (recordArray != null) { for (int recordcount = 0; recordcount < recordArray.size(); recordcount++) { row = rawLogs.createRow(recordcount + 1); columnArray = (ArrayList) recordArray.get(recordcount); dataValue = (String) columnArray.get(columnArray.size() - 1); if (dataValue != null && dataValue.length() > 0) { row.createCell(0).setCellValue(dataValue); } else { row.createCell(0).setCellValue("-"); } } } wb.write(out); out.close(); } catch (Exception e) { CyberoamLogger.appLog.error("Error in exporting archive data to excel"); } } //Report data Export else { if (request.getParameter("xlsdata") != null) mode = (String) request.getParameter("xlsdata"); if (mode.equalsIgnoreCase("group")) { limit = "5"; } else { reportID = Integer.parseInt(request.getParameter("reportid")); } PrepareQuery prepareQuery = new PrepareQuery(); if (request.getParameter("reportgroupid") != null) reportGroupID = Integer.parseInt(request.getParameter("reportgroupid")); // For report group if (reportID == -1) { try { CyberoamLogger.appLog.info("Report Group id : " + reportGroupID); ReportGroupBean reportGroupBean = ReportGroupBean.getRecordbyPrimarykey(reportGroupID); ReportGroupRelationBean reportGroupRelationBean; ArrayList reportList = reportGroupBean.getReportIdByReportGroupId(reportGroupID); //For getting workbook of Excel HSSFWorkbook wb = new HSSFWorkbook(); int noReports = reportList.size(); for (int ctr = 0; ctr < noReports; ctr++) { reportGroupRelationBean = (ReportGroupRelationBean) reportList.get(ctr); reportID = reportGroupRelationBean.getReportId(); CyberoamLogger.appLog.info("ReportGroup : Report id : " + reportID); ReportBean reportBean = ReportBean.getRecordbyPrimarykey(reportID); query = prepareQuery.getQuery(reportBean, request); // Getting result set try { if (query.indexOf("select") == -1 && query.indexOf("SELECT") == -1) { indexManager = new IndexManager(); rsw = indexManager.getSearch(query); //rsw=indexManager.getResutSetFromArrayList(searchRecord); } else { rsw = sqlReader.getInstanceResultSetWrapper(query); } } catch (org.postgresql.util.PSQLException e) { if (query.indexOf("5min_ts_20") > -1) { query = query.substring(0, query.indexOf("5min_ts_20")) + "4hr" + query.substring(query.indexOf("5min_ts_20") + 16, query.length()); rsw = sqlReader.getInstanceResultSetWrapper(query); } else { CyberoamLogger.appLog.error("Exeption in AjaxController.java " + e, e); } } catch (Exception e) { CyberoamLogger.appLog.error("Exeption in AjaxController.java " + e, e); rsw.close(); } // Getting workbook with sheets & all data & chart getWorkBook(rsw, reportBean, wb); } wb.write(out); out.close(); } catch (Exception e) { CyberoamLogger.appLog.error("***Exeption in report group Excel file generation***" + e); } finally { sqlReader.close(); rsw.close(); } } else {// For single report try { ReportBean reportBean = ReportBean.getRecordbyPrimarykey(reportID); //Getting query for report query = prepareQuery.getQuery(reportBean, request); String searchQuery = request.getParameter("searchquery"); if (searchQuery != null && !"".equalsIgnoreCase(searchQuery)) { query = query.replaceAll("where", "where " + searchQuery + " and"); } try { if (query.indexOf("select") == -1 && query.indexOf("SELECT") == -1) { indexManager = new IndexManager(); rsw = indexManager.getSearch(query); //rsw=indexManager.getResutSetFromArrayList(searchRecord); } else { rsw = sqlReader.getInstanceResultSetWrapper(query); } } catch (org.postgresql.util.PSQLException e) { if (query.indexOf("5min_ts_20") > -1) { query = query.substring(0, query.indexOf("5min_ts_20")) + "4hr" + query.substring(query.indexOf("5min_ts_20") + 16, query.length()); rsw = sqlReader.getInstanceResultSetWrapper(query); } else { CyberoamLogger.appLog.error("Exeption in AjaxController.java " + e, e); } } catch (Exception e) { CyberoamLogger.appLog.error("Exeption in AjaxController.java " + e, e); rsw.close(); } HSSFWorkbook wb = new HSSFWorkbook(); getWorkBook(rsw, reportBean, wb); wb.write(out); out.close(); } catch (Exception e) { CyberoamLogger.appLog .info("***Exception during Excel Single report generation***" + e.getMessage()); } finally { sqlReader.close(); rsw.close(); } } } } catch (Exception ex) { CyberoamLogger.appLog.info("***Excel Report Generartion Exception***" + ex, ex); } }
From source file:org.cyberoam.iview.servlets.ExcelFileGenerator.java
License:Open Source License
void getWorkBook(ResultSetWrapper rsw, ReportBean reportBean, HSSFWorkbook wb) { try {/* w w w . j a va 2s . c o m*/ ReportColumnBean[] reportColumns = (ReportColumnBean[]) ReportColumnBean .getReportColumnsByReportID(reportBean.getReportId()).toArray(new ReportColumnBean[0]); HSSFSheet newSheet = wb.createSheet(reportBean.getTitle()); HSSFRow row; HSSFCell cell; HSSFCellStyle cellStyle = wb.createCellStyle(); HSSFFont fontStyle = wb.createFont(); fontStyle.setFontHeightInPoints((short) 10); fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(fontStyle); // getting number of records & fields for report rsw.last(); int rowCount = rsw.getRow(); int colCount = reportColumns.length; //For Freezing the first row newSheet.createFreezePane(0, 1, 0, 1); // Adding column headings to Excel row = newSheet.createRow((short) 0); for (int cCount = 0; cCount < colCount; cCount++) { cell = row.createCell(cCount); cell.setCellStyle(cellStyle); cell.setCellValue(reportColumns[cCount].getColumnName()); } //Adding data for each record to Excel rsw.first(); for (int rcount = 1; rcount <= rowCount; rcount++) { row = newSheet.createRow(rcount); for (int cCount = 0; cCount < colCount; cCount++) { String data = rsw.getString(reportColumns[cCount].getDbColumnName()); cell = row.createCell(cCount); newSheet.autoSizeColumn((short) cCount); if (reportColumns[cCount].getColumnFormat() == TabularReportConstants.BYTE_FORMATTING) { data = ByteInUnit.getBytesInUnit(rsw.getLong(reportColumns[cCount].getDbColumnName())); } else if (reportColumns[cCount].getColumnFormat() == TabularReportConstants.PROTOCOL_FORMATTING && data.indexOf(':') != -1) { String xdata = ProtocolBean.getProtocolNameById( Integer.parseInt(rsw.getString(reportColumns[cCount].getDbColumnName()).substring(0, data.indexOf(':')))); data = xdata + rsw.getString(reportColumns[cCount].getDbColumnName()) .substring(data.indexOf(':'), data.length()); } // Setting value to the cell if (cCount == 0 && (data == null || "".equalsIgnoreCase(data))) data = "N/A"; if (reportColumns[cCount].getColumnFormat() == TabularReportConstants.PERCENTAGE_FORMATTING) { try { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(data)); } catch (NumberFormatException e) { cell.setCellValue(data); } } else if (rsw.getMetaData().getColumnTypeName(cCount + 1).equalsIgnoreCase("numeric") && reportColumns[cCount].getColumnFormat() != TabularReportConstants.BYTE_FORMATTING) { try { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Integer.parseInt(data)); } catch (NumberFormatException e) { cell.setCellValue(data); } } else { cell.setCellValue(data); } } rsw.next(); } } catch (Exception e) { CyberoamLogger.appLog.error("***Error in getWorkbook function***" + e, e); } }
From source file:org.eclipse.scada.ae.ui.views.export.excel.impl.ExportEventsImpl.java
License:Open Source License
private IStatus storeExcel(final File file, final List<Event> events, final List<Field> columns, final IProgressMonitor monitor) throws IOException { final HSSFWorkbook workbook = new HSSFWorkbook(); final HSSFDataFormat dateFormat = workbook.createDataFormat(); final HSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat("YYYY-MM-DD hh:mm:ss.000")); try {/*from w w w .j a va2 s . c o m*/ monitor.beginTask(Messages.ExportImpl_Progress_ExportingEvents, events.size() + 3 + columns.size()); try { monitor.subTask(Messages.ExportImpl_Progress_CreateWorkbook); monitor.worked(1); final HSSFSheet sheet = createSheet(events, workbook, columns); monitor.worked(1); monitor.setTaskName(Messages.ExportImpl_Progress_ExportEvents); for (int i = 0; i < events.size(); i++) { final HSSFRow row = sheet.createRow(i + 1); final Event e = events.get(i); for (int j = 0; j < columns.size(); j++) { final Field field = columns.get(j); final ExcelCell cell = new ExcelCell(row, j, dateCellStyle); field.render(e, cell); } monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } sheet.setRepeatingRows(new CellRangeAddress(0, 1, -1, -1)); monitor.setTaskName("Auto sizing"); for (int i = 0; i < columns.size(); i++) { monitor.subTask(String.format("Auto sizing column: %s", columns.get(i).getHeader())); sheet.autoSizeColumn(i); monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } } finally { monitor.subTask(Messages.ExportImpl_Progress_CloseFile); if (workbook != null) { makeDocInfo(workbook); final FileOutputStream stream = new FileOutputStream(file); workbook.write(stream); stream.close(); } monitor.worked(1); } } finally { monitor.done(); } return Status.OK_STATUS; }
From source file:org.eclipse.titanium.markers.export.XlsProblemExporter.java
License:Open Source License
/** * Export the code smells of a project to an excel workbook. * <p>/*from w w w.java 2s .c o m*/ * The first sheet of the workbook is a summary page, showing the number of * hits for each code smell, and an expressive bar chart of these data. The * further sheets enumerate the specific code smells of each kind, including * the message of the code smell, and the file name and line where it * occurred. * <p> * Note: All code smell types are used in the analysis and are written in * the output. Some code smells use external settings, which can be fine * tuned on the preference page. * * @param filename * the file to save the xls * @param date * the time stamp to write on the summary page * * @throws IOException * when writing the file fails */ @Override // Flow analysis thinks 'sheet' may be referenced as null, but it is // guaranteed to be initialized first. public void exportMarkers(final IProgressMonitor monitor, final String filename, final Date date) throws IOException { final SubMonitor progress = SubMonitor.convert(monitor, 100); final File file = new File(filename); POIFSFileSystem fs = null; HSSFWorkbook workbook = null; try { fs = new POIFSFileSystem(XlsProblemExporter.class.getResourceAsStream("ProblemMarkers.xlt")); workbook = new HSSFWorkbook(fs, true); } catch (IOException e) { ErrorReporter.logExceptionStackTrace("Error while exporting to excel", e); // Error on opening the template xls. Create an empty // one (without the chart). if (reportDebugInformation) { TITANDebugConsole.println("Error on opening ProblemMarkers.xlt. Chartless xls will be generated"); } workbook = new HSSFWorkbook(new FileInputStream(file)); workbook.createSheet("Summary"); workbook.setSheetOrder("Summary", 0); } catch (Exception e) { ErrorReporter.logExceptionStackTrace("Error while exporting to excel", e); return; } progress.worked(10); try { final HSSFSheet summarySheet = workbook.getSheetAt(0); createTimeSheet(workbook); final Map<String, Integer> smellCount = new HashMap<String, Integer>(); int summaryRow = 4; Cell label = null; Cell numberCell = null; final Map<TaskType, List<IMarker>> markers = collectMarkers(); // export the task markers: for (final TaskType t : TaskType.values()) { createTaskSheet(workbook, t, markers.get(t)); final Row row1 = summarySheet.createRow(summaryRow++); label = row1.createCell(0); label.setCellValue(t.getHumanReadableName()); final int nofMarkers = markers.get(t).size(); numberCell = row1.createCell(1); numberCell.setCellValue(nofMarkers); // row-1 is the number of found markers smellCount.put(t.name(), nofMarkers); } progress.worked(20); final MarkerHandler mh = AnalyzerCache.withAll().analyzeProject(progress.newChild(30), project); progress.setWorkRemaining(CodeSmellType.values().length + 1); // export the semantic problem markers: for (final CodeSmellType t : CodeSmellType.values()) { createCodeSmellSheet(workbook, mh, t); final Row row1 = summarySheet.createRow(summaryRow++); label = row1.createCell(0); label.setCellValue(t.getHumanReadableName()); smellCount.put(t.name(), mh.numberOfOccurrences(t)); numberCell = row1.createCell(1); numberCell.setCellValue(mh.numberOfOccurrences(t)); progress.worked(1); } final Row row0 = summarySheet.createRow(0); row0.createCell(0).setCellValue("Project: " + project.getName()); final Row row1 = summarySheet.createRow(1); row1.createCell(0).setCellValue("Code smell \\ date"); final CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("yyyy.mm.dd")); label = row1.createCell(1); label.setCellValue(date); label.setCellStyle(cellStyle); final Row row2 = summarySheet.createRow(2); row2.createCell(0).setCellValue("Commulative Project Risk Factor"); final int riskFactor = new RiskFactorCalculator().measure(project, smellCount); row2.createCell(1).setCellValue(riskFactor); summarySheet.autoSizeColumn(0); summarySheet.autoSizeColumn(1); progress.worked(1); } catch (Exception e) { ErrorReporter.logExceptionStackTrace("Error while exporting to excel", e); } finally { FileOutputStream fileOutputStream = null; try { fileOutputStream = new FileOutputStream(file); workbook.write(fileOutputStream); } catch (Exception e) { ErrorReporter.logExceptionStackTrace("Error while closing the generated excel", e); } finally { IOUtils.closeQuietly(fileOutputStream); } } }