List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createFreezePane
@Override public void createFreezePane(int colSplit, int rowSplit)
From source file:havocx42.ExcelFile.java
License:Open Source License
private void setupSheet(HSSFSheet sheet) { sheet.createFreezePane(1, 1); int cellNumber = 0; HSSFRow rowhead = sheet.createRow(0); rowhead.createCell(cellNumber).setCellValue("Weapon Name"); sheet.setColumnWidth(cellNumber++, 30 * 280); rowhead.createCell(cellNumber).setCellValue("magSize"); sheet.setColumnWidth(cellNumber++, "magSize".length() * 280); rowhead.createCell(cellNumber).setCellValue("reloadTime"); sheet.setColumnWidth(cellNumber++, "reloadTime".length() * 280); rowhead.createCell(cellNumber).setCellValue("recoilForceUp"); sheet.setColumnWidth(cellNumber++, "recoilForceUp".length() * 280); rowhead.createCell(cellNumber).setCellValue("recoilForceLeftRight"); sheet.setColumnWidth(cellNumber++, "recoilForceLeftRight".length() * 280); rowhead.createCell(cellNumber).setCellValue("Velocity"); sheet.setColumnWidth(cellNumber++, "Velocity".length() * 280); rowhead.createCell(cellNumber).setCellValue("Rounds Per Minute"); sheet.setColumnWidth(cellNumber++, "Rounds Per Minute".length() * 280); rowhead.createCell(cellNumber).setCellValue("Ammo Name"); sheet.setColumnWidth(cellNumber++, "Ammo Name".length() * 280); rowhead.createCell(cellNumber).setCellValue("Ammo Max Damage"); sheet.setColumnWidth(cellNumber++, "Ammo Max Damage".length() * 280); rowhead.createCell(cellNumber).setCellValue("Ammo Min Damage"); sheet.setColumnWidth(cellNumber++, "Ammo Min Damage".length() * 280); rowhead.createCell(cellNumber).setCellValue("Ammo Max Dist"); sheet.setColumnWidth(cellNumber++, "Ammo Max Dist".length() * 280); rowhead.createCell(cellNumber).setCellValue("Ammo Min Dist"); sheet.setColumnWidth(cellNumber++, "Ammo Min Dist".length() * 280); rowhead.createCell(cellNumber).setCellValue("Ammo GravityModifier"); sheet.setColumnWidth(cellNumber++, "Ammo GravityModifier".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation devModStand"); sheet.setColumnWidth(cellNumber++, "Deviation devModStand".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation devModCrouch"); sheet.setColumnWidth(cellNumber++, "Deviation devModCrouch".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation devModLie"); sheet.setColumnWidth(cellNumber++, "Deviation devModLie".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation devModZoom"); sheet.setColumnWidth(cellNumber++, "Deviation devModZoom".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation minDev"); sheet.setColumnWidth(cellNumber++, "Deviation minDev".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setFireDevMax"); sheet.setColumnWidth(cellNumber++, "Deviation setFireDevMax".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setFireDevAdd"); sheet.setColumnWidth(cellNumber++, "Deviation setFireDevAdd".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setFireDevCool"); sheet.setColumnWidth(cellNumber++, "Deviation setFireDevCool".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setTurnDevMax"); sheet.setColumnWidth(cellNumber++, "Deviation setTurnDevMax".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setTurnDevLeft"); sheet.setColumnWidth(cellNumber++, "Deviation setTurnDevLeft".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setTurnDevRight"); sheet.setColumnWidth(cellNumber++, "Deviation setTurnDevRight".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setTurnDevCool"); sheet.setColumnWidth(cellNumber++, "Deviation setTurnDevCool".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setSpeedDevMax"); sheet.setColumnWidth(cellNumber++, "Deviation setSpeedDevMax".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setSpeedDevMove"); sheet.setColumnWidth(cellNumber++, "Deviation setSpeedDevMove".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setSpeedDevStrafe"); sheet.setColumnWidth(cellNumber++, "Deviation setSpeedDevStrafe".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setSpeedDevCool"); sheet.setColumnWidth(cellNumber++, "Deviation setSpeedDevCool".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setMiscDevMax"); sheet.setColumnWidth(cellNumber++, "Deviation setMiscDevMax".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setMiscDevAdd"); sheet.setColumnWidth(cellNumber++, "Deviation setMiscDevAdd".length() * 280); rowhead.createCell(cellNumber).setCellValue("Deviation setMiscDevCool"); sheet.setColumnWidth(cellNumber++, "Deviation setMiscDevCool".length() * 280); rowhead.createCell(cellNumber).setCellValue("Single Shot Settle Time"); sheet.setColumnWidth(cellNumber++, "Single Shot Settle Time".length() * 280); rowhead.createCell(cellNumber).setCellValue("Maximum Movement Settle Time"); sheet.setColumnWidth(cellNumber++, "Maximum Movement Settle Time".length() * 280); rowhead.createCell(cellNumber).setCellValue("Prone Settle Time"); sheet.setColumnWidth(cellNumber++, "Prone Settle Time".length() * 280); }
From source file:org.activityinfo.server.endpoint.export.DbUserExport.java
License:Open Source License
public void createSheet() { HSSFSheet sheet = book.createSheet(composeUniqueSheetName("db-users-list")); sheet.createFreezePane(4, 2); // initConditionalFormatting(sheet); createHeaders(sheet);/* w w w . ja v a2s. com*/ createDataRows(sheet); }
From source file:org.activityinfo.server.endpoint.export.SiteExporter.java
License:Open Source License
public void export(ActivityFormDTO activity, Filter filter) { HSSFSheet sheet = book.createSheet(composeUniqueSheetName(activity)); sheet.createFreezePane(4, 2); // initConditionalFormatting(sheet); createHeaders(activity, sheet);/* ww w . j ava 2 s . c om*/ createDataRows(activity, filter, sheet); }
From source file:org.adempiere.impexp.AbstractExcelExporter.java
License:Open Source License
private void closeTableSheet(HSSFSheet prevSheet, String prevSheetName, int colCount) { if (prevSheet == null) return;// w w w .j a v a2 s . c o m // fixColumnWidth(prevSheet, colCount); if (m_colSplit >= 0 || m_rowSplit >= 0) prevSheet.createFreezePane(m_colSplit >= 0 ? m_colSplit : 0, m_rowSplit >= 0 ? m_rowSplit : 0); if (!Util.isEmpty(prevSheetName, true) && m_sheetCount > 0) { int prevSheetIndex = m_sheetCount - 1; try { m_workbook.setSheetName(prevSheetIndex, prevSheetName); } catch (Exception e) { log.log(Level.WARNING, "Error setting sheet " + prevSheetIndex + " name to " + prevSheetName, e); } } }
From source file:org.eclipse.scada.ae.ui.views.export.excel.impl.ExportEventsImpl.java
License:Open Source License
private HSSFSheet createSheet(final List<Event> events, final HSSFWorkbook workbook, final List<Field> columns) { final HSSFSheet sheet = workbook.createSheet(Messages.ExportImpl_ExcelSheet_Name); final HSSFHeader header = sheet.getHeader(); header.setLeft(Messages.ExportImpl_ExcelSheet_Header); header.setRight(HeaderFooter.date() + " " + HeaderFooter.time());//$NON-NLS-1$ final HSSFFooter footer = sheet.getFooter(); footer.setLeft(String.format(Messages.ExportImpl_ExcelSheet_Footer_1, events.size())); footer.setRight(Messages.ExportImpl_ExcelSheet_Footer_2 + HeaderFooter.page() + Messages.ExportImpl_ExcelSheet_Footer_3 + HeaderFooter.numPages()); makeHeader(columns, sheet);/*from w w w .j a v a 2 s . c o m*/ final HSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); printSetup.setFitWidth((short) 1); printSetup.setFitHeight((short) 0); printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE); sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, columns.size() - 1)); sheet.createFreezePane(0, 1); sheet.setFitToPage(true); sheet.setAutobreaks(true); printSetup.setFooterMargin(0.25); sheet.setMargin(Sheet.LeftMargin, 0.25); sheet.setMargin(Sheet.RightMargin, 0.25); sheet.setMargin(Sheet.TopMargin, 0.25); sheet.setMargin(Sheet.BottomMargin, 0.5); return sheet; }
From source file:org.forzaframework.util.XlsUtils.java
License:Apache License
static public void modelToExcelSheet(HSSFWorkbook wb, String sheetName, List<Map<String, Object>> headers, List<Map<String, Object>> data, List<Map<String, Object>> footers, Integer freezePane, Boolean defaultFormat, Boolean createNewSheet, Integer indexSheet, Integer startInRow, Boolean printHeader, Boolean autoSizeColumns) { HSSFSheet sheet = getSheet(wb, sheetName, createNewSheet, indexSheet); HSSFCellStyle headerCellStyle = getDefaultHeaderCellStyle(wb, defaultFormat); HSSFCellStyle titlesCellStyle = null; if (defaultFormat != null && defaultFormat) { titlesCellStyle = wb.createCellStyle(); //Creamos el tipo de fuente HSSFFont titleFont = wb.createFont(); // headerFont.setFontName(HSSFFont.FONT_ARIAL); titleFont.setBold(Boolean.TRUE); titleFont.setColor(HSSFFont.COLOR_NORMAL); titleFont.setFontHeightInPoints((short) 8); titlesCellStyle.setFont(titleFont); }/* w ww. j av a 2s. c o m*/ Integer col = 0; Integer row = 0; if (startInRow != null) { row = startInRow; } Map<Integer, Integer> columnWidthMap = new HashMap<Integer, Integer>(); //Indice de la fila donde empieza los encabezados de titulo de cada columna Integer principalHeaderIndex = headers.size() - 1; if (printHeader != null && printHeader) { //Armamos el encabezado for (Map<String, Object> header : headers) { for (Map.Entry<String, Object> entry : header.entrySet()) { HSSFCell cell = getCell(sheet, row, col); if (defaultFormat != null && defaultFormat) { if (principalHeaderIndex.equals(row)) { //Colocamos el formato de la celda cell.setCellStyle(headerCellStyle); } else { cell.setCellStyle(titlesCellStyle); } } setValue(cell, entry.getValue()); //Especificamos el ancho que tendra la columna if (autoSizeColumns != null && autoSizeColumns) { columnWidthMap.put(col, entry.getValue().toString().length()); } col++; } row++; col = 0; } //Ponemos la altura del encabezado setRowHeight(sheet, row - 1, (short) 420); } HSSFCellStyle detailCellStyle = getDefaultDetailCellStyle(wb, defaultFormat); Map<String, Object> principalHeader = headers.get(principalHeaderIndex); // datos for (Map<String, Object> map : data) { for (Map.Entry<String, Object> entry : principalHeader.entrySet()) { Object value = map.get(entry.getKey()); buildCellAndCalculateColumnWidth(sheet, value, col, row, detailCellStyle, columnWidthMap, autoSizeColumns); col++; } col = 0; row++; } HSSFCellStyle totalCellStyle = null; if (defaultFormat != null && defaultFormat) { //Armamos el formato los totales totalCellStyle = wb.createCellStyle(); HSSFFont totalFont = wb.createFont(); totalFont.setBold(Boolean.TRUE); totalFont.setColor(HSSFFont.COLOR_NORMAL); totalFont.setFontHeightInPoints((short) 8); totalCellStyle.setFont(totalFont); } if (footers != null) { for (Map<String, Object> footer : footers) { for (Map.Entry<String, Object> entry : principalHeader.entrySet()) { HSSFCell cell = getCell(sheet, row, col++); if (totalCellStyle != null) { //Colocamos el formato de la celda cell.setCellStyle(totalCellStyle); } Object object = footer.get(entry.getKey()); if (object != null) { setValue(cell, object); } else { setText(cell, ""); } } } } if (autoSizeColumns != null && autoSizeColumns) { setColumnsWidth(sheet, columnWidthMap, principalHeader.size()); } if (freezePane != null && freezePane > 0) { //Colocamos la columna estatica y las filas del encabezado estaticas sheet.createFreezePane(freezePane, headers.size()); } }
From source file:org.forzaframework.util.XlsUtils.java
License:Apache License
static public void jsonToExcelSheet(HSSFWorkbook wb, List<JSONObject> jsonHeaderList, List<JSONObject> jsonRecordList, String sheetName, Integer freezePane) { HSSFSheet sheet = getSheet(wb, sheetName, true, 0); HSSFCellStyle headerCellStyle = getDefaultHeaderCellStyle(wb, true); Integer col = 0;/*from w w w . j a va 2 s .com*/ Integer row = 0; Map<Integer, Integer> columnWidthMap = new HashMap<Integer, Integer>(); JSONObject principalHeader = null; if (jsonHeaderList != null && !jsonHeaderList.isEmpty()) { LinkedList<JSONObject> headerList = new LinkedList<JSONObject>(jsonHeaderList); principalHeader = headerList.getLast(); headerList.removeLast(); for (JSONObject header : headerList) { Collection headerValues = header.values(); for (Object o : headerValues) { HSSFCell cell = getCell(sheet, row, col); setValue(cell, o); col++; } col = 0; row++; } } List<JSONObject> datas; if (principalHeader == null) { principalHeader = jsonRecordList.get(0); datas = jsonRecordList.subList(1, jsonRecordList.size()); } else { datas = jsonRecordList; } Collection headerValues = principalHeader.values(); for (Object o : headerValues) { HSSFCell cell = getCell(sheet, row, col); cell.setCellStyle(headerCellStyle); setValue(cell, o); columnWidthMap.put(col, o.toString().length()); col++; } col = 0; row++; setRowHeight(sheet, row - 1, (short) 420); HSSFCellStyle detailCellStyle = getDefaultDetailCellStyle(wb, true); for (JSONObject jsonObject : datas) { for (Object value : jsonObject.values()) { buildCellAndCalculateColumnWidth(sheet, value, col, row, detailCellStyle, columnWidthMap, true); col++; } row++; col = 0; } setColumnsWidth(sheet, columnWidthMap, headerValues.size()); if (freezePane != null && freezePane > 0) { //Colocamos la columna estatica y las filas del encabezado estaticas if (jsonHeaderList != null) { sheet.createFreezePane(freezePane, jsonHeaderList.size()); } else { sheet.createFreezePane(freezePane, 1); } } }
From source file:org.fourfive.engine2.util.ReportServletWorker.java
License:Mozilla Public License
public void writeExcelToOut(ReportData data, ServletOutputStream out, ArrayList paramList, EasyReport component, HttpServletRequest req) throws Exception { HttpSession session = req.getSession(); HSSFWorkbook wb = new HSSFWorkbook(); ExcelUtil excelUtil = new ExcelUtil(wb); excelUtil.setSession(session);/*from ww w . j ava 2 s . co m*/ // Murali C HashMap dataformat = null; ArrayList crosstabColumnIds = null; // // ArrayList nonCurrItems = ReportDataUtil.getNotCurrency(data,component); Columns columns = component.getDefinition().getSelect().getColumns(); Hashtable ht = com.manthan.promax.report.ReportUtil.getLabels(session); String reportType = ReportDataUtil.getReportType(component); //component.getTitle().getValue() //excelUtil.getSheetTitle(component.getTitle().getValue()) //String title = (String) com.manthan.promax.report.ReportUtil.getLabels(session).get(component.getTitle().getValue()); String title = component.getTitle().getValue(); HSSFSheet sheet1 = wb.createSheet(excelUtil.getSheetTitle(title)); excelUtil.setSheetStyle(); excelUtil.isExpanded(true); excelUtil.getLogo(session.getServletContext()); int rowStart = 4; int colStart = 0; HSSFRow titleRow = sheet1.createRow((short) (rowStart)); HSSFCell titleCell = titleRow.createCell((short) (colStart)); titleCell.setCellStyle(excelUtil.getTitleStyle()); titleCell.setCellValue(new HSSFRichTextString(data.getTitle())); sheet1.addMergedRegion(new Region(rowStart, (short) (colStart), rowStart, (short) (colStart + 10))); rowStart = rowStart + 2; HSSFRow selectionRow = sheet1.createRow((short) rowStart); selectionRow.setHeightInPoints(40); HSSFCell selectionCell = selectionRow.createCell((short) colStart); selectionCell.setCellStyle(excelUtil.getSelectionStyle()); selectionCell.setCellValue(new HSSFRichTextString(excelUtil.getCurrentSelection(req).toString())); sheet1.addMergedRegion(new Region(rowStart, (short) (colStart), rowStart, (short) (colStart + 10))); rowStart = rowStart + 1; int colWidth = 10; excelUtil.setPrintAndPageSettings(rowStart + 1, rowStart + 2, title, ((ArrayList) data.getRows().get(0)).size()); excelUtil.setStartWidthColumn(rowStart + 2); ArrayList row = (ArrayList) data.getRows().get(0); ReportCell rcell = null; ArrayList nonVisibleCol = new ArrayList(); for (int j = 0; j < row.size(); j++) { rcell = (ReportCell) row.get(j); if (!rcell.getVisible()) { nonVisibleCol.add(new Integer(j)); } } HSSFCell excel_cell = null; HSSFRow excelRow = sheet1.createRow((short) rowStart); HashMap crossTabDetails = data.getCrossTabReportDetails(); if (reportType != null && reportType.equalsIgnoreCase(DefaultKernel.CROSSTAB_REPORT_TYPE)) { ArrayList columnList = (ArrayList) crossTabDetails.get("COLUMN_LIST"); ArrayList growingColumns = (ArrayList) crossTabDetails.get(DefaultKernel.XTAB_DYNAMIC); ArrayList staticColumns = (ArrayList) crossTabDetails.get(DefaultKernel.XTAB_STATIC); // Murali C if (crossTabDetails.get("COLUMN_IDS_LIST") != null) { crosstabColumnIds = (ArrayList) crossTabDetails.get("COLUMN_IDS_LIST"); } if (crossTabDetails.get("CROSSTABDATAFORMAT") != null) { dataformat = (HashMap) crossTabDetails.get("CROSSTABDATAFORMAT"); } // int noGrowingColumns = growingColumns.size(); int noStaticColumns = staticColumns.size(); int staticColSpan = noStaticColumns; int dynamicColSpan = noGrowingColumns; for (int colCount = 0; colCount < staticColumns.size(); colCount++) { if (nonVisibleCol.contains(new Integer(colCount))) { staticColSpan--; } } for (int colCount = 0; colCount < growingColumns.size(); colCount++) { if (nonVisibleCol.contains(new Integer(colCount + noStaticColumns))) { dynamicColSpan--; } } for (int i = 0; i < columnList.size(); i++) { short cellIndex = (short) ((staticColSpan) + (dynamicColSpan * i)); excel_cell = excelRow.createCell(cellIndex); excel_cell.setCellStyle(excelUtil.getHeaderStyle()); excel_cell.setCellValue(new HSSFRichTextString((String) columnList.get(i))); for (short index = (short) (cellIndex + 1); index < cellIndex + dynamicColSpan; index++) { excel_cell = excelRow.createCell(index); excel_cell.setCellStyle(excelUtil.getHeaderStyle()); } sheet1.addMergedRegion(new Region(rowStart, (short) (cellIndex), rowStart, (short) (cellIndex + dynamicColSpan - 1))); } // we are getting first row for column headers or column titles } rowStart++; sheet1.createFreezePane(2, 10); for (int i = 0; i < data.getRows().size(); i++) { row = (ArrayList) data.getRows().get(i); excelRow = sheet1.createRow((short) (i + rowStart)); if (i == 0) excelRow.setHeightInPoints(45); else excelRow.setHeightInPoints(20); int incr = 0; //String dataInString = null; for (int j = 0; j < row.size(); j++) { ReportCell cell = (ReportCell) row.get(j); if (!cell.getName().startsWith("<img") && cell.getVisible()) { String columnTitle = (String) cell.getName(); String columnTitleFromProp = ""; String datatype = ""; try { // Murali C if (null != component.getDefinition().getReportType() && component.getDefinition().getReportType().equalsIgnoreCase("crosstab")) { datatype = dataformat.get(crosstabColumnIds.get(j).toString()).toString(); } else { datatype = ((Column) component.getDefinition().getSelect().getColumns().getColumn(j)) .getDataFormat(); } double val = Double.parseDouble(cell.getName()); HSSFCell dataCell = excelRow.createCell((short) (j + colStart + incr)); dataCell.setCellValue(val); if (i == 1) { if (datatype.equalsIgnoreCase("currency")) { dataCell.setCellStyle(excelUtil.getTotalStyle(true)); } else if (datatype.equalsIgnoreCase("number")) { dataCell.setCellStyle(excelUtil.getTotalStyle(false)); } } else { if (datatype.equalsIgnoreCase("currency")) { dataCell.setCellStyle(excelUtil.getDataStyleFormat()); } else if (datatype.equalsIgnoreCase("number")) { dataCell.setCellStyle(excelUtil.getDataStyleFormatLess()); } else { dataCell.setCellValue(new HSSFRichTextString(cell.getName())); dataCell.setCellStyle(excelUtil.firstColStyle()); } } //dataInString = ""+val; } catch (NumberFormatException ee) { /*if (i==0) { if (reportType == null || !reportType.equalsIgnoreCase(DefaultKernel.CROSSTAB_REPORT_TYPE)) { if( columns.getColumn(j).getId() != null && null != ht.get(columns.getColumn(j).getId().toLowerCase())) { columnTitleFromProp = (String) (ht.get(columns.getColumn(j).getId().toLowerCase())); }else{ columnTitleFromProp = columnTitle; } }else{ ArrayList idsList = (ArrayList) crossTabDetails.get("COLUMN_IDS_LIST"); if(idsList.get(j)!=null && ht.get(((String)idsList.get(j)).toLowerCase()) != null){ columnTitleFromProp = (String)ht.get(((String)idsList.get(j)).toLowerCase()); }else{ columnTitleFromProp = columnTitle; } } } else { columnTitleFromProp = columnTitle; }*/ columnTitleFromProp = columnTitle; HSSFCell headerCell = excelRow.createCell((short) (j + colStart + incr)); if (i == 1) { headerCell.setCellStyle(excelUtil.getTotalStyle(false)); } else { if (i == 0) headerCell.setCellStyle(excelUtil.getHeaderStyle()); else headerCell.setCellStyle(excelUtil.getDataTextStyle()); } headerCell.setCellValue(new HSSFRichTextString(columnTitleFromProp)); //dataInString = ""+columnTitleFromProp; } } else { incr = incr - 1; } /*if(dataInString.length()>colWidth) { colWidth = dataInString.length(); }*/ } } excelUtil.setWidth(); //sheet1.setDefaultColumnWidth((short)(10)); try { wb.write(out); out.flush(); out.close(); } catch (IOException e) { } }
From source file:org.fourfive.servlets.ArcTreeServlet.java
License:Mozilla Public License
private void writeExcelToOut(ArcTreeReport root, ServletOutputStream out, HttpSession session, HttpServletRequest req) throws MarshalException, ValidationException { EasyReport component = root.getEasyReport(); ArrayList rows = (ArrayList) root.getTreeRows(); TreeRow row = (TreeRow) rows.get(0); ReportCell cell = null;/* www .j a v a 2 s . co m*/ ArrayList rowCells = row.getRowCells(); ArrayList nonVisibleCol = new ArrayList(); for (int j = 0; j < rowCells.size(); j++) { cell = (ReportCell) rowCells.get(j); if (!cell.getVisible()) { nonVisibleCol.add(new Integer(j)); } } nonCurrItems = root.getNonCurrencyColumns(); HSSFWorkbook wb = new HSSFWorkbook(); // title of the report Hashtable ht = (Hashtable) com.manthan.promax.report.ReportUtil.getLabels(session); String repGenTime = "Report Generated Time : "; if (ht != null && ht.get("export.reportgenerated.time") != null && ht.get("export.reportgenerated.time").toString().trim().length() > 0) { repGenTime = (String) ht.get("export.reportgenerated.time"); } String nameKey = (String) session.getAttribute("NAME_KEY"); int rowStart = 4; int colStart = 0; int colWidth = 10; //String dataInString = null; ExcelUtil excelUtil = new ExcelUtil(wb); excelUtil.setSession(session); excelUtil.getCurrentSelection(req); HSSFSheet sheet1 = wb.createSheet(excelUtil.getSheetTitle(nameKey)); excelUtil.setSheetStyle(); HSSFRow showTimeRow = sheet1.createRow((short) (rowStart)); HSSFCell showTimeCell = showTimeRow.createCell((short) (colStart)); showTimeCell.setCellStyle(excelUtil.getSelectionStyle()); showTimeCell.setCellValue(new HSSFRichTextString(repGenTime + new Date().toString())); sheet1.addMergedRegion(new Region(rowStart, (short) (colStart), rowStart, (short) (colStart + 7))); rowStart = rowStart + 2; HSSFRow titleRow = sheet1.createRow((short) rowStart); HSSFCell titleCell = titleRow.createCell((short) colStart); titleCell.setCellStyle(excelUtil.getTitleStyle()); titleCell.setCellValue(new HSSFRichTextString(nameKey)); sheet1.addMergedRegion(new Region(rowStart, (short) (colStart), rowStart, (short) (colStart + 10))); rowStart = rowStart + 2; HSSFRow selectionRow = sheet1.createRow((short) rowStart); //selectionRow.setHeightInPoints(40); HSSFCell selectionCell = selectionRow.createCell((short) colStart); selectionCell.setCellStyle(excelUtil.getSelectionStyle()); java.util.ArrayList summaryData = (java.util.ArrayList) session.getAttribute("FILTER_SUMMARY_DATA"); selectionCell.setCellValue(new HSSFRichTextString((String) ht.get("arc.current.selection"))); sheet1.addMergedRegion(new Region(rowStart, (short) (colStart), rowStart, (short) (colStart + 5))); int filterSize = 1; if (summaryData != null) { for (int sumCnt = 0; sumCnt < summaryData.size(); sumCnt++) { java.util.ArrayList sumdata = (java.util.ArrayList) summaryData.get(sumCnt); rowStart++; selectionRow = sheet1.createRow((short) rowStart); selectionCell = selectionRow.createCell((short) (colStart)); selectionCell.setCellStyle(excelUtil.getSelectionStyle()); String value = (String) sumdata.get(1); value = value.replaceAll("<span>", ""); value = value.replaceAll("</span>", ""); selectionCell .setCellValue(new HSSFRichTextString(" " + (String) sumdata.get(0) + " -- " + value)); sheet1.addMergedRegion(new Region(rowStart, (short) (colStart), rowStart, (short) (colStart + 10))); } //sheet1.addMergedRegion(new Region(rowStart-summaryData.size(),(short)(colStart),rowStart,(short)(colStart))); filterSize = summaryData.size(); } rowStart++; excelUtil.setPrintAndPageSettings(rowStart + 1, rowStart + 2, (String) ReportUtil.getLabels(session).get(nameKey), rowCells.size() + 3); excelUtil.setStartWidthColumn(rowStart + 1); HSSFCell excel_cell = null; HSSFRow excelRow = sheet1.createRow((short) rowStart); String reportType = ReportDataUtil.getReportType(component); HashMap crossTabDetails = root.getCrossTabReportDetails(); //----- for displaying unit measure by Gowri ----------------- Columns columns = component.getDefinition().getSelect().getColumns(); ArrayList crosstabColumnIds = new ArrayList(); for (int colCnt = 0; colCnt < columns.getColumnCount(); colCnt++) { Column column = columns.getColumn(colCnt); crosstabColumnIds.add(column.getId()); } //------------------------------------------------------------- if (reportType != null && reportType.equalsIgnoreCase(DefaultKernel.CROSSTAB_REPORT_TYPE)) { crosstabColumnIds = (ArrayList) crossTabDetails.get("COLUMN_IDS_LIST"); ArrayList columnList = (ArrayList) crossTabDetails.get("COLUMN_LIST"); ArrayList growingColumns = (ArrayList) crossTabDetails.get(DefaultKernel.XTAB_DYNAMIC); ArrayList staticColumns = (ArrayList) crossTabDetails.get(DefaultKernel.XTAB_STATIC); int noGrowingColumns = growingColumns.size(); int noStaticColumns = staticColumns.size(); int staticColSpan = noStaticColumns; int dynamicColSpan = noGrowingColumns; for (int colCount = 0; colCount < staticColumns.size(); colCount++) { if (nonVisibleCol.contains(new Integer(colCount))) { staticColSpan--; } } for (int colCount = 0; colCount < growingColumns.size(); colCount++) { if (nonVisibleCol.contains(new Integer(colCount + noStaticColumns))) { dynamicColSpan--; } } for (int i = 0; i < columnList.size(); i++) { short cellIndex = (short) (1 + (staticColSpan - 1) + (dynamicColSpan * i)); excel_cell = excelRow.createCell(cellIndex); excel_cell.setCellStyle(excelUtil.getHeaderStyle()); excel_cell.setCellValue(new HSSFRichTextString((String) columnList.get(i))); for (short index = (short) (cellIndex + 1); index < cellIndex + dynamicColSpan; index++) { excel_cell = excelRow.createCell(index); excel_cell.setCellStyle(excelUtil.getHeaderStyle()); } sheet1.addMergedRegion(new Region(rowStart, (short) (cellIndex), rowStart, (short) (cellIndex + dynamicColSpan - 1))); } } rowStart++; sheet1.createFreezePane(2, 12 + filterSize); excelRow = sheet1.createRow((short) rowStart); HashMap hierarchyLevels = root.getHierarchyLevels(); row = (TreeRow) root.getTreeRows().get(0); int colCout = colStart; //Columns columns = component.getDefinition().getSelect().getColumns(); Hashtable measures = com.manthan.promax.db.ApplicationConfig.getAllMeasures(); for (int cellCount = 0; cellCount < row.getRowCells().size(); cellCount++) { ReportCell rCell = (ReportCell) row.getRowCells().get(cellCount); String colId = crosstabColumnIds.get(cellCount).toString(); if (rCell.getVisible()) { String cellValue = rCell.getName(); //----- for displaying unit measure by Gowri ----------------- String unitMeasures = ""; HashMap measure = (HashMap) measures.get(colId); if (null != measure && null != measure.get("COLUMN_TYPE") && measure.get("COLUMN_TYPE").toString().equalsIgnoreCase("currency") && ((measure.get("RULE") != null && !measure.get("RULE").toString().trim().equals("") && measure.get("RULE").toString().indexOf("100") == -1) || (measure.get("DB_COLUMN_NAME") != null && !measure.get("DB_COLUMN_NAME").toString().trim().equals("")) || (measure.get("MEASURE_EXPRESSION") != null && !measure.get("MEASURE_EXPRESSION").toString().trim().equals("")))) { unitMeasures = (session.getAttribute("unitMeasureInDigits") != null && !session.getAttribute("unitMeasureInDigits").toString().equals("1")) ? "[in " + ((String) session.getAttribute("unitMeasureInDigits")).substring(1) + "]" : ""; } //------------------------------------------------------------- excel_cell = excelRow.createCell((short) (colCout)); excel_cell.setCellStyle(excelUtil.getHeaderStyle()); excel_cell.setCellValue(new HSSFRichTextString(cellValue + "\n" + unitMeasures)); colCout++; } } rowStart++; excelRow = sheet1.createRow((short) rowStart); excelRow.setHeightInPoints(20); row = (TreeRow) root.getTreeRows().get(1); HSSFCell totCell = excelRow.createCell((short) colStart); totCell.setCellStyle(excelUtil.getTotalStyle(excelUtil.format(nonCurrItems, 0))); totCell.setCellValue(new HSSFRichTextString(((ReportCell) row.getRowCells().get(0)).getName())); cell = null; colCout = colStart + 1; for (int cellCount = 1; cellCount < row.getRowCells().size(); cellCount++) { cell = (ReportCell) row.getRowCells().get(cellCount); if (cell.getVisible()) { String cellValue = cell.getName(); excel_cell = excelRow.createCell((short) (colCout)); try { double val = Double.parseDouble(cellValue); excel_cell.setCellStyle(excelUtil.getTotalStyle(excelUtil.format(nonCurrItems, cellCount))); excel_cell.setCellValue(val); } catch (NumberFormatException e) { excel_cell.setCellStyle(excelUtil.getTotalStyle(excelUtil.format(nonCurrItems, cellCount))); excel_cell.setCellValue(new HSSFRichTextString(cellValue)); } colCout++; } } count_row = rowStart + 1; writeArcTreeElementToExcel(root, sheet1, excelUtil); excelUtil.getLogo(session.getServletContext()); excelUtil.setWidth(); excelUtil.removeCell(sheet1); HSSFRow compConfidentialRow = sheet1.createRow((short) (count_row + 1)); HSSFCell compConfidentialCell = compConfidentialRow.createCell((short) (0)); compConfidentialCell.setCellStyle(excelUtil.getSelectionStyle()); compConfidentialCell.setCellValue(new HSSFRichTextString((String) ht.get("arc.company.confidential"))); sheet1.addMergedRegion(new Region(count_row + 1, (short) (0), rowStart, (short) (5))); try { wb.write(out); out.flush(); out.close(); } catch (IOException e) { } }
From source file:org.goobi.production.flow.helper.SearchResultHelper.java
License:Open Source License
@SuppressWarnings("deprecation") public HSSFWorkbook getResult(List<SearchColumn> columnList, String filter, String order, boolean showClosedProcesses, boolean showArchivedProjects) { List<SearchColumn> sortedList = new ArrayList<>(columnList.size()); for (SearchColumn sc : columnList) { if (!sc.getTableName().startsWith("metadata")) { sortedList.add(sc);/*from w w w. j ava 2 s. com*/ } } for (SearchColumn sc : columnList) { if (sc.getTableName().startsWith("metadata")) { sortedList.add(sc); } } columnList = sortedList; @SuppressWarnings("rawtypes") List list = search(columnList, filter, order, showClosedProcesses, showArchivedProjects); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Search results"); // create title row int titleColumnNumber = 0; HSSFRow title = sheet.createRow(0); int columnNumber = 0; for (SearchColumn sc : columnList) { HSSFCell titleCell = title.createCell(titleColumnNumber++); titleCell.setCellValue(Helper.getTranslation(sc.getValue())); HSSFCellStyle cellStyle = wb.createCellStyle(); HSSFFont cellFont = wb.createFont(); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(cellFont); titleCell.setCellStyle(cellStyle); } int rowNumber = 1; for (Object obj : list) { Object[] objArr = (Object[]) obj; HSSFRow row = sheet.createRow(rowNumber++); columnNumber = 0; for (Object entry : objArr) { HSSFCell cell = row.createCell(columnNumber++); if (entry != null) { cell.setCellValue(((String) entry).replace("\"", "")); } else { cell.setCellValue(""); } } } sheet.createFreezePane(0, 1); for (int i = 0; i < columnList.size(); i++) { sheet.autoSizeColumn(i); if (sheet.getColumnWidth(i) > 15000) { sheet.setColumnWidth(i, 15000); } } return wb; }