List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setColumnWidth
@Override public void setColumnWidth(int columnIndex, int width)
The maximum column width for an individual cell is 255 characters.
From source file:neg.JRViewerInformeUnitario.java
License:Open Source License
private static void crearCabeceraHojaExcel(HSSFWorkbook libro, HSSFSheet hoja) { HSSFRow fila = null;//from w w w. j a v a 2 s .c o m HSSFCell celda = null; // Modificamos la fuente por defecto para que salga en negrita HSSFCellStyle cs = libro.createCellStyle(); HSSFFont f = libro.createFont(); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f.setColor(HSSFColor.WHITE.index); cs.setFont(f); //cs.setFillBackgroundColor(HSSFColor.GREEN.index); cs.setFillForegroundColor(HSSFColor.GREEN.index); cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cs.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); cs.setBottomBorderColor(HSSFColor.BLACK.index); cs.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); cs.setLeftBorderColor(HSSFColor.BLACK.index); cs.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); cs.setRightBorderColor(HSSFColor.BLACK.index); cs.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); cs.setTopBorderColor(HSSFColor.BLACK.index); cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // Creamos la cabecera de las columnas fila = hoja.createRow(0); celda = fila.createCell((short) 0); celda.setCellStyle(cs); HSSFRichTextString texto = new HSSFRichTextString("DATOS"); celda.setCellValue(texto); hoja.setColumnWidth((short) 0, (short) ((60 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 1); celda.setCellStyle(cs); texto = new HSSFRichTextString("ENERO"); celda.setCellValue(texto); hoja.setColumnWidth((short) 1, (short) ((60 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 2); celda.setCellStyle(cs); texto = new HSSFRichTextString("FEBRERO"); celda.setCellValue(texto); hoja.setColumnWidth((short) 2, (short) ((80 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 3); celda.setCellStyle(cs); texto = new HSSFRichTextString("MARZO"); celda.setCellValue(texto); hoja.setColumnWidth((short) 3, (short) ((200 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 4); celda.setCellStyle(cs); texto = new HSSFRichTextString("ABRIL"); celda.setCellValue(texto); hoja.setColumnWidth((short) 4, (short) ((130 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 5); celda.setCellStyle(cs); texto = new HSSFRichTextString("MAYO"); celda.setCellValue(texto); hoja.setColumnWidth((short) 5, (short) ((60 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 6); celda.setCellStyle(cs); texto = new HSSFRichTextString("JUNIO"); celda.setCellValue(texto); hoja.setColumnWidth((short) 6, (short) ((130 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 7); celda.setCellStyle(cs); texto = new HSSFRichTextString("JULIO"); celda.setCellValue(texto); hoja.setColumnWidth((short) 7, (short) ((60 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 8); celda.setCellStyle(cs); texto = new HSSFRichTextString("AGOSTO"); celda.setCellValue(texto); hoja.setColumnWidth((short) 8, (short) ((80 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 9); celda.setCellStyle(cs); texto = new HSSFRichTextString("SEPTIEMBRE"); celda.setCellValue(texto); hoja.setColumnWidth((short) 9, (short) ((40 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 10); celda.setCellStyle(cs); texto = new HSSFRichTextString("OCTUBRE"); celda.setCellValue(texto); hoja.setColumnWidth((short) 10, (short) ((120 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 11); celda.setCellStyle(cs); texto = new HSSFRichTextString("NOVIEMBRE"); celda.setCellValue(texto); hoja.setColumnWidth((short) 11, (short) ((110 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 12); celda.setCellStyle(cs); texto = new HSSFRichTextString("DICIEMBRE"); celda.setCellValue(texto); hoja.setColumnWidth((short) 12, (short) ((130 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 13); celda.setCellStyle(cs); texto = new HSSFRichTextString("M.G."); celda.setCellValue(texto); hoja.setColumnWidth((short) 13, (short) ((130 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 14); celda.setCellStyle(cs); texto = new HSSFRichTextString("M.G. PEDIDO"); celda.setCellValue(texto); hoja.setColumnWidth((short) 14, (short) ((130 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 15); celda.setCellStyle(cs); texto = new HSSFRichTextString("NUM.PEDIDOS"); celda.setCellValue(texto); hoja.setColumnWidth((short) 15, (short) ((40 * 2) / ((double) 1 / 20))); celda = fila.createCell((short) 16); celda.setCellStyle(cs); texto = new HSSFRichTextString("IMPORTE"); celda.setCellValue(texto); hoja.setColumnWidth((short) 16, (short) ((200 * 2) / ((double) 1 / 20))); }
From source file:net.chaosserver.timelord.data.ExcelDataReaderWriter.java
License:Open Source License
/** * Creates the header rows for a sheet.//from ww w .jav a 2s . co m * * @param sheet the sheet to add header rows for * @param weekStartDate the start date for the week that is used to create * the date headers on the top * @param styleMap the style map for the header styles */ protected void createHeaderRows(HSSFSheet sheet, Date weekStartDate, Map<String, HSSFCellStyle> styleMap) { HSSFRow headerRow = sheet.createRow(0); Calendar calendarDay = Calendar.getInstance(); calendarDay.setTime(weekStartDate); sheet.setDefaultColumnWidth((short) 9); HSSFCell cell = headerRow.createCell((short) 0); cell.setCellStyle((HSSFCellStyle) styleMap.get("topLeftStyle")); cell.setCellValue(""); sheet.setColumnWidth((short) 0, (short) 10000); cell = headerRow.createCell((short) 1); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Monday"); cell = headerRow.createCell((short) 2); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Tuesday"); cell = headerRow.createCell((short) 3); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Wednesday"); cell = headerRow.createCell((short) 4); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Thusday"); cell = headerRow.createCell((short) 5); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Friday"); cell = headerRow.createCell((short) 6); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Saturday"); cell = headerRow.createCell((short) 7); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRowStyle")); cell.setCellValue("Sunday"); cell = headerRow.createCell(MAX_COLUMN); cell.setCellStyle((HSSFCellStyle) styleMap.get("topRightStyle")); cell.setCellValue(""); sheet.setColumnWidth(MAX_COLUMN, (short) 1500); headerRow = sheet.createRow(1); cell = headerRow.createCell((short) 0); cell.setCellValue("Task Name"); cell.setCellStyle((HSSFCellStyle) styleMap.get("taskNameHeaderStyle")); cell = headerRow.createCell((short) 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell((short) 2); calendarDay.add(Calendar.DAY_OF_WEEK, 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell((short) 3); calendarDay.add(Calendar.DAY_OF_WEEK, 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell((short) 4); calendarDay.add(Calendar.DAY_OF_WEEK, 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell((short) 5); calendarDay.add(Calendar.DAY_OF_WEEK, 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell((short) 6); calendarDay.add(Calendar.DAY_OF_WEEK, 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell((short) 7); calendarDay.add(Calendar.DAY_OF_WEEK, 1); cell.setCellValue(calendarDay.getTime()); cell.setCellStyle((HSSFCellStyle) styleMap.get("boldDateStyle")); cell = headerRow.createCell(MAX_COLUMN); cell.setCellValue("Total"); cell.setCellStyle((HSSFCellStyle) styleMap.get("totalHeaderStyle")); }
From source file:net.chaosserver.timelord.data.ExcelUglyDataReaderWriter.java
License:Open Source License
/** * Generates the workbook that contains all of the data for the excel * document./* w w w.j ava 2 s .c o m*/ * * @param timelordData the data object to generate the workbook for * @return the workbook of data */ protected HSSFWorkbook generateWorkbook(TimelordData timelordData) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); sheet.setColumnWidth((short) 1, (short) 10000); HSSFCellStyle dateStyle = wb.createCellStyle(); dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); List<TimelordTask> taskCollection = timelordData.getTaskCollection(); Iterator<TimelordTask> taskIterator = taskCollection.iterator(); // Calendar weekStart = Calendar.getInstance(); // Make the headers HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell((short) 0); cell.setCellValue("Date"); cell = row.createCell((short) 1); cell.setCellValue("Task Name"); cell = row.createCell((short) 2); cell.setCellValue("Hours"); cell = row.createCell((short) 3); cell.setCellValue("Note"); int rowNum = 1; while (taskIterator.hasNext()) { TimelordTask timelordTask = (TimelordTask) taskIterator.next(); if (timelordTask.isExportable()) { String taskName = timelordTask.getTaskName(); List<TimelordTaskDay> taskDayList = timelordTask.getTaskDayList(); Iterator<TimelordTaskDay> taskDayIterator = taskDayList.iterator(); while (taskDayIterator.hasNext()) { TimelordTaskDay timelordTaskDay = (TimelordTaskDay) taskDayIterator.next(); if (timelordTaskDay.getHours() > 0) { row = sheet.createRow(rowNum); cell = row.createCell((short) 0); cell.setCellStyle(dateStyle); cell.setCellValue(timelordTaskDay.getDate()); cell = row.createCell((short) 1); cell.setCellValue(taskName); cell = row.createCell((short) 2); cell.setCellValue(timelordTaskDay.getHours()); cell = row.createCell((short) 3); cell.setCellValue(timelordTaskDay.getNote()); rowNum++; } } } } return wb; }
From source file:net.sf.jasperreports.engine.export.JRXlsMetadataExporter.java
License:Open Source License
protected void closeWorkbook(OutputStream os) throws JRException { try {/*w w w. j a v a 2 s .c o m*/ for (Object anchorName : anchorNames.keySet()) { HSSFName anchor = anchorNames.get(anchorName); List<Hyperlink> linkList = anchorLinks.get(anchorName); anchor.setRefersToFormula( "'" + workbook.getSheetName(anchor.getSheetIndex()) + "'!" + anchor.getRefersToFormula()); if (linkList != null && !linkList.isEmpty()) { for (Hyperlink link : linkList) { link.setAddress(anchor.getRefersToFormula()); } } } int index = 0; for (Integer linkPage : pageLinks.keySet()) { List<Hyperlink> linkList = pageLinks.get(linkPage); if (linkList != null && !linkList.isEmpty()) { for (Hyperlink link : linkList) { index = onePagePerSheetMap.get(linkPage - 1) != null ? (onePagePerSheetMap.get(linkPage - 1) ? Math.max(0, linkPage - 1) : Math.max(0, sheetsBeforeCurrentReportMap.get(linkPage))) : 0; link.setAddress("'" + workbook.getSheetName(index) + "'!$A$1"); } } } for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet currentSheet = workbook.getSheetAt(i); currentSheet.setForceFormulaRecalculation(true); for (String columnName : columnNames) { Integer columnWidth = columnWidths.get(columnName); if (columnWidth != null && columnWidth < Integer.MAX_VALUE) { currentSheet.setColumnWidth(columnNamesMap.get(columnName), Math.min(43 * columnWidth, 256 * 255)); } else { currentSheet.autoSizeColumn(columnNamesMap.get(columnName), false); } } } workbook.write(os); } catch (IOException e) { throw new JRException("Error generating XLS metadata report : " + jasperPrint.getName(), e); } }
From source file:org.activityinfo.server.endpoint.export.DbUserExport.java
License:Open Source License
private void createHeaders(HSSFSheet sheet) { // / The HEADER rows Row headerRow = sheet.createRow(0);//www . j a va 2 s.c om int column = 0; createHeaderCell(headerRow, column++, I18N.CONSTANTS.name(), CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow, column++, I18N.CONSTANTS.email(), CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow, column++, I18N.CONSTANTS.partner(), CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow, column++, I18N.CONSTANTS.allowView(), CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow, column++, I18N.CONSTANTS.allowViewAll(), CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow, column++, I18N.CONSTANTS.allowDesign(), CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow, column++, I18N.CONSTANTS.allowEdit(), CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow, column++, I18N.CONSTANTS.allowEditAll(), CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow, column++, I18N.CONSTANTS.allowManageUsers(), CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow, column++, I18N.CONSTANTS.allowManageAllUsers(), CellStyle.ALIGN_RIGHT); sheet.setColumnWidth(column, 12 * 256); sheet.setColumnWidth(column + 1, 12 * 256); }
From source file:org.activityinfo.server.endpoint.export.SiteExporter.java
License:Open Source License
private void createHeaders(ActivityFormDTO activity, HSSFSheet sheet) { // / The HEADER rows Row headerRow1 = sheet.createRow(0); Row headerRow2 = sheet.createRow(1); headerRow2.setHeightInPoints(HEADER_CELL_HEIGHT); // Create a title cell with the complete database + activity name Cell titleCell = headerRow1.createCell(0); titleCell.setCellValue(/* w w w .ja v a 2s. co m*/ creationHelper.createRichTextString(activity.getDatabaseName() + " - " + activity.getName())); titleCell.setCellStyle(titleStyle); int column = 0; createHeaderCell(headerRow2, column++, "SiteId", CellStyle.ALIGN_LEFT); createHeaderCell(headerRow2, column++, "DateCreated", CellStyle.ALIGN_RIGHT); sheet.setColumnHidden(0, true); sheet.setColumnHidden(1, true); createHeaderCell(headerRow2, column++, "Date1", CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow2, column++, "Date2", CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow2, column, "Partner"); sheet.setColumnWidth(column, characters(PARTNER_COLUMN_WIDTH)); column++; createHeaderCell(headerRow2, column, activity.getLocationType().getName()); sheet.setColumnWidth(column, characters(LOCATION_COLUMN_WIDTH)); column++; createHeaderCell(headerRow2, column++, "Axe"); indicators = new ArrayList<Integer>(activity.getIndicators().size()); if (activity.getReportingFrequency() == ActivityFormDTO.REPORT_ONCE) { for (IndicatorGroup group : activity.groupIndicators()) { if (group.getName() != null) { // create a merged cell on the top row spanning all members // of the group createHeaderCell(headerRow1, column, group.getName()); sheet.addMergedRegion( new CellRangeAddress(0, 0, column, column + group.getIndicators().size() - 1)); } for (IndicatorDTO indicator : group.getIndicators()) { indicators.add(indicator.getId()); createHeaderCell(headerRow2, column, indicator.getName(), indicatorHeaderStyle); sheet.setColumnWidth(column, characters(INDICATOR_COLUMN_WIDTH)); column++; } } } attributes = new ArrayList<>(); for (AttributeGroupDTO group : activity.getAttributeGroups()) { if (group.getAttributes().size() != 0) { createHeaderCell(headerRow1, column, group.getName(), CellStyle.ALIGN_CENTER); sheet.addMergedRegion( new CellRangeAddress(0, 0, column, column + group.getAttributes().size() - 1)); for (AttributeDTO attrib : group.getAttributes()) { attributes.add(attrib.getId()); createHeaderCell(headerRow2, column, attrib.getName(), attribHeaderStyle); sheet.setColumnWidth(column, characters(ATTRIBUTE_COLUMN_WIDTH)); column++; } } } levels = new ArrayList<>(); for (AdminLevelDTO level : activity.getAdminLevels()) { createHeaderCell(headerRow2, column++, "Code " + level.getName()); createHeaderCell(headerRow2, column++, level.getName()); levels.add(level.getId()); } int latColumn = column++; int lngColumn = column++; createHeaderCell(headerRow2, latColumn, I18N.CONSTANTS.longitude(), CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow2, lngColumn, I18N.CONSTANTS.latitude(), CellStyle.ALIGN_RIGHT); sheet.setColumnWidth(lngColumn, characters(COORD_COLUMN_WIDTH)); sheet.setColumnWidth(latColumn, characters(COORD_COLUMN_WIDTH)); createHeaderCell(headerRow2, column++, I18N.CONSTANTS.comments()); }
From source file:org.anyframe.logmanager.web.LogManagerController.java
License:Apache License
/** * log data export for excel file type/*from ww w .j a va 2 s.c om*/ * * @param searchCondition * @param model * @param request * @return * @throws Exception */ @RequestMapping(params = "method=xlsExport") public void xlsExport(LogSearchCondition searchCondition, Model model, HttpServletRequest request, HttpServletResponse response) throws Exception { searchCondition.setPageIndex(-1); searchCondition.setCollection(searchCondition.getRepositoryName()); String fileName = null; String sDate = null; SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss", new Locale("ko_KR")); sDate = sdf.format(new Date()); StringBuffer sb = new StringBuffer(); sb.append(searchCondition.getAppName().substring(searchCondition.getAppName().lastIndexOf("/") + 1)); sb.append("_").append(searchCondition.getCollection()).append("_").append(sDate).append(".xls"); fileName = sb.toString(); SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-ddHHmm"); logger.debug("from:{}", searchCondition.getFromDate() + searchCondition.getFromHour() + searchCondition.getFromMinute()); logger.debug("to:{}", searchCondition.getToDate() + searchCondition.getToHour() + searchCondition.getToMinute()); if (searchCondition.isUseFromDate()) searchCondition.setFromDateTime(dateTimeFormat.parse(searchCondition.getFromDate() + searchCondition.getFromHour() + searchCondition.getFromMinute())); if (searchCondition.isUseToDate()) searchCondition.setToDateTime(dateTimeFormat.parse( searchCondition.getToDate() + searchCondition.getToHour() + searchCondition.getToMinute())); List<LogDataMap> resultList = service.searchAnalysisLog(searchCondition); response.reset(); response.setContentType("application/x-msexcel;charset=MS949"); // response.setContentType("application/octet-stream"); String userAgent = request.getHeader("User-Agent"); if (userAgent.indexOf("MSIE 5.5") > -1) { response.setHeader("Content-Disposition", "filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\";"); } else if (userAgent.indexOf("MSIE") > -1) { response.setHeader("Content-Disposition", "attachment; filename=\"" + java.net.URLEncoder.encode(fileName, "UTF-8") + "\";"); } else { response.setHeader("Content-Disposition", "attachment; filename=\"" + new String(fileName.getBytes("euc-kr"), "latin1") + "\";"); } response.setHeader("Content-Description", "JSP Generated Data"); response.setHeader("Content-Transfer-Encoding", "binary;"); response.setHeader("Pragma", "no-cache;"); response.setHeader("Expires", "-1;"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(fileName); OutputStream fileOut = null; try { fileOut = response.getOutputStream(); HSSFRow row = null; HSSFRow headerRow = null; HSSFDataFormat df = workbook.createDataFormat(); HSSFCellStyle headerStyle = workbook.createCellStyle(); HSSFFont boldFont = workbook.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(boldFont); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(df.getFormat("yyyy-mm-dd h:mm:ss.000")); dateStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); dateStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle messageStyle = workbook.createCellStyle(); messageStyle.setWrapText(true); messageStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); messageStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCell cell; HSSFCell headerCell; short width = 265; Iterator<String> j = null; String key = null; int cellIndex = 0; int listSize = 0; String level = null; Date timestamp = null; String message = null; if (resultList != null) { listSize = resultList.size(); for (int i = 0; i < listSize; i++) { LogDataMap log = (LogDataMap) resultList.get(i); if (i == 0) { headerRow = sheet.createRow(i); // level header sheet.setColumnWidth(0, 7 * width); headerCell = headerRow.createCell(0); HSSFRichTextString headerValue = new HSSFRichTextString("level"); headerCell.setCellValue(headerValue); headerCell.setCellStyle(headerStyle); headerCell = headerRow.createCell(1); // time stamp header sheet.setColumnWidth(1, 24 * width); headerValue = new HSSFRichTextString("timestamp"); headerCell.setCellValue(headerValue); headerCell.setCellStyle(headerStyle); headerCell = headerRow.createCell(2); // message header sheet.setColumnWidth(2, 70 * width); headerValue = new HSSFRichTextString("message"); headerCell.setCellValue(headerValue); headerCell.setCellStyle(headerStyle); } row = sheet.createRow(i + 1); // level level = (String) log.get("level"); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(level); // timestamp timestamp = (Date) log.get("timestamp"); cell = row.createCell(1); cell.setCellStyle(dateStyle); cell.setCellValue(timestamp); // message message = (String) log.get("message"); HSSFRichTextString messageValue = new HSSFRichTextString(message); cell = row.createCell(2); cell.setCellStyle(messageStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(messageValue); cellIndex = 3; j = log.keySet().iterator(); while (j.hasNext()) { key = j.next(); if ("_id".equals(key) || "message".equals(key) || "timestamp".equals(key) || "level".equals(key)) { continue; } //logger.debug("key=" + key); if (i == 0) { sheet.setColumnWidth(cellIndex, 20 * width); headerCell = headerRow.createCell(cellIndex); HSSFRichTextString headerValue = new HSSFRichTextString(key); headerCell.setCellValue(headerValue); headerCell.setCellStyle(headerStyle); } cell = row.createCell(cellIndex); Object value = log.get(key); if (value instanceof Date) { cell.setCellStyle(dateStyle); cell.setCellValue((Date) value); } else { cell.setCellStyle(style); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue((String) log.get(key)); } cellIndex++; } } workbook.write(fileOut); } } catch (Exception e) { throw e; } finally { try { if (fileOut != null) { fileOut.flush(); fileOut.close(); } } catch (IOException ex) { logger.warn(ex.getMessage(), ex); } } }
From source file:org.apache.directory.studio.ldapbrowser.core.jobs.ExportXlsRunnable.java
License:Apache License
/** * {@inheritDoc}/*from ww w . ja v a 2s . c o m*/ */ public void run(StudioProgressMonitor monitor) { monitor.beginTask(BrowserCoreMessages.jobs__export_xls_task, 2); monitor.reportProgress(" "); //$NON-NLS-1$ monitor.worked(1); Preferences coreStore = BrowserCorePlugin.getDefault().getPluginPreferences(); String valueDelimiter = coreStore.getString(BrowserCoreConstants.PREFERENCE_FORMAT_XLS_VALUEDELIMITER); int binaryEncoding = coreStore.getInt(BrowserCoreConstants.PREFERENCE_FORMAT_XLS_BINARYENCODING); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Export"); //$NON-NLS-1$ // header HSSFRow headerRow = sheet.createRow(0); LinkedHashMap<String, Integer> attributeNameMap = new LinkedHashMap<String, Integer>(); if (this.exportDn) { int cellNum = 0; attributeNameMap.put("dn", cellNum); //$NON-NLS-1$ createStringCell(headerRow, cellNum).setCellValue("dn"); //$NON-NLS-1$ } // max export if (searchParameter.getCountLimit() < 1 || searchParameter.getCountLimit() > MAX_COUNT_LIMIT) { searchParameter.setCountLimit(MAX_COUNT_LIMIT); } // export try { int count = 0; exportToXls(browserConnection, searchParameter, sheet, headerRow, count, monitor, attributeNameMap, valueDelimiter, binaryEncoding, this.exportDn); } catch (Exception e) { monitor.reportError(e); } // column width for (int i = 0; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); for (short j = 0; row != null && j <= row.getLastCellNum(); j++) { HSSFCell cell = row.getCell(j); if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String value = cell.getStringCellValue(); if ((short) (value.length() * 256 * 1.1) > sheet.getColumnWidth(j)) { sheet.setColumnWidth(j, (short) (value.length() * 256 * 1.1)); } } } } try { FileOutputStream fileOut = new FileOutputStream(exportXlsFilename); wb.write(fileOut); fileOut.close(); } catch (Exception e) { monitor.reportError(e); } }
From source file:org.deployom.core.AuditService.java
License:Open Source License
public HSSFWorkbook saveAudit() { // Create book HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCreationHelper creationHelper = workbook.getCreationHelper(); // Default Style HSSFCellStyle style = workbook.createCellStyle(); style.setWrapText(true);//w w w . ja va 2s. c o m HSSFFont font = workbook.createFont(); font.setFontName("Courier New"); style.setFont(font); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // Header Style HSSFCellStyle styleHeader = workbook.createCellStyle(); styleHeader.cloneStyleFrom(style); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); styleHeader.setFillForegroundColor(IndexedColors.BLACK.getIndex()); styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleHeader.setFont(font); // Error Style HSSFCellStyle styleError = workbook.createCellStyle(); styleError.cloneStyleFrom(style); styleError.setFillForegroundColor(IndexedColors.CORAL.getIndex()); styleError.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleError.setWrapText(true); // Link Style HSSFCellStyle styleLink = workbook.createCellStyle(); styleLink.cloneStyleFrom(style); font = workbook.createFont(); font.setUnderline(HSSFFont.U_SINGLE); font.setColor(IndexedColors.BLUE.getIndex()); styleLink.setFont(font); // Create Summary HSSFSheet summarySheet = workbook.createSheet("Summary"); int summaryRownum = 0; int summaryCellnum = 0; //Create a new row in current sheet Row summaryRow = summarySheet.createRow(summaryRownum++); // 0 Cell summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue("Job"); summaryCell.setCellStyle(styleHeader); // 1 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue("Finished"); summaryCell.setCellStyle(styleHeader); // 2 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue("Errors"); summaryCell.setCellStyle(styleHeader); for (Job job : releaseService.getJobs()) { // Open Job JobService jobService = new JobService(siteService.getSiteName(), job.getJobName()); // Create Sheet HSSFSheet sheet = workbook.createSheet(job.getJobName()); int rownum = 0; int cellnum = 0; int errors = 0; //Create a new row in current sheet Row row = sheet.createRow(rownum++); // 0 Cell cell = row.createCell(cellnum++); cell.setCellValue("Host"); cell.setCellStyle(styleHeader); // 1 cell = row.createCell(cellnum++); cell.setCellValue("Service"); cell.setCellStyle(styleHeader); // 2 cell = row.createCell(cellnum++); cell.setCellValue("Command"); cell.setCellStyle(styleHeader); // 3 cell = row.createCell(cellnum++); cell.setCellValue("Executable"); cell.setCellStyle(styleHeader); // 4 cell = row.createCell(cellnum++); cell.setCellValue("Error"); cell.setCellStyle(styleHeader); // 5 cell = row.createCell(cellnum++); cell.setCellValue("Output"); cell.setCellStyle(styleHeader); // Check all hosts for (Host host : jobService.getHosts()) { // Check all services for (Service service : host.getServices()) { // Get a Commands for (Command command : service.getCommands()) { //Create a new row in current sheet row = sheet.createRow(rownum++); cellnum = 0; // 0 cell = row.createCell(cellnum++); cell.setCellValue(host.getHostName()); cell.setCellStyle(style); // 1 cell = row.createCell(cellnum++); cell.setCellValue(service.getServiceName()); cell.setCellStyle(style); // 2 cell = row.createCell(cellnum++); cell.setCellValue(command.getTitle()); cell.setCellStyle(style); // 3 cell = row.createCell(cellnum++); cell.setCellValue(command.getExec()); cell.setCellStyle(style); // 4 cell = row.createCell(cellnum++); cell.setCellValue("N"); cell.setCellStyle(style); // 5 cell = row.createCell(cellnum++); if (command.getOut().length() > 1024) { cell.setCellValue(command.getOut().substring(0, 1024) + "..."); } else { cell.setCellValue(command.getOut()); } cell.setCellStyle(style); // Error if (command.isError() == true) { row.getCell(0).setCellStyle(styleError); row.getCell(1).setCellStyle(styleError); row.getCell(2).setCellStyle(styleError); row.getCell(3).setCellStyle(styleError); row.getCell(4).setCellStyle(styleError); row.getCell(5).setCellStyle(styleError); row.getCell(4).setCellValue("Y"); errors++; } } } } // Set Size sheet.setColumnWidth(0, 6000); sheet.setColumnWidth(1, 4000); sheet.setColumnWidth(2, 8000); sheet.setColumnWidth(3, 14000); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 20000); // Summary summaryRow = summarySheet.createRow(summaryRownum++); summaryCellnum = 0; // 0 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue(job.getJobName()); summaryCell.setCellStyle(style); // Set Link HSSFHyperlink link = creationHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); link.setAddress("" + job.getJobName() + "!A1"); summaryCell.setHyperlink(link); summaryCell.setCellStyle(styleLink); // 1 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue(jobService.getJob().getFinished()); summaryCell.setCellStyle(style); // 2 summaryCell = summaryRow.createCell(summaryCellnum++); summaryCell.setCellValue(errors); summaryCell.setCellStyle(style); // If errors found if (errors > 0) { summaryRow.getCell(0).setCellStyle(styleError); summaryRow.getCell(1).setCellStyle(styleError); summaryRow.getCell(2).setCellStyle(styleError); } } // Set Summary Size summarySheet.setColumnWidth(0, 6000); summarySheet.setColumnWidth(1, 10000); summarySheet.setColumnWidth(2, 4000); // Save try { FileOutputStream out = new FileOutputStream(new File(getFileName())); workbook.write(out); out.close(); logger.log(Level.INFO, "{0} generated successfully..", getFileName()); return workbook; } catch (FileNotFoundException ex) { logger.log(Level.WARNING, "Audit: {0}", ex); } catch (IOException ex) { logger.log(Level.WARNING, "Audit: {0}", ex); } return null; }
From source file:org.forzaframework.util.XlsUtils.java
License:Apache License
public static void setColumnsWidth(HSSFSheet sheet, Map<Integer, Integer> columnWidthMap, Integer numberOfColumns) { //Colocamos las columnas con el ancho correcto for (Integer i = 0; i < numberOfColumns; i++) { //Obtenemos el maximo numero de caracteres de la columna Integer columnWidth = columnWidthMap.get(i) + 1; columnWidth = columnWidth > 100 ? 100 : columnWidth; //multiplicamos por 256 porque es lo que representa un caranter en excel sheet.setColumnWidth(i, columnWidth * 256); //TODO: Esta es otra forma dar el ancho de la columna correctamente, probar si es mas optimo // sheet.autoSizeColumn((short)i); // sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 256); }// w w w .j a v a 2 s. c om }