List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:newpackage.ReadDbc.java
public void readFileByLines() { FindDBC dialog = new FindDBC(new javax.swing.JFrame(), true); dialog.pack();/* w w w . j av a 2s . c om*/ dialog.setLocationRelativeTo(null); dialog.setVisible(true); outputFile = dialog.Excelpath + dialog.ExcelName; fileName = dialog.DBCpath + "\\" + dialog.DBCFileName; // FileOpen2 file= new FileOpen2(); // // String fileName = file.getCurrentDirectory().getPath()+"\\"+file.getCurrentFile().getName(); FileOutputStream fOut = null;//this is for excle out flow File file = new File(fileName); BufferedReader reader = null; List TitleList = new ArrayList() { { add("Msg\n" + "ID\n" + "(hex)"); add("Msg\n" + "Name"); add("Msg\n" + "Length\n" + "(bytes)"); add("ECU \n" + "(Tx)"); add("SignalName:Identifier"); add("SignalOffset:Integer"); add("SignalSize:Integer"); add("ByteOrder"); add("Signed"); add("RangeScale:Integer "); add(" RangeOffset:Integer"); add("RangeLow:Float"); add("RangeHigh:Float"); add(" RangeUnit:String"); add("ReceiverNodeName:Identifier"); } }; try { // Excel HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("demo"); //System.out.println("???"); reader = new BufferedReader(new FileReader(file)); String tempString = null; String tempString1 = null; int line = 1; // null? int MessagerIdNumber = 0; int SingalNumber = 6000; String[] SingalName = new String[SingalNumber]; String[] SingalOffset = new String[SingalNumber]; String[] SingalSize = new String[SingalNumber]; String[] ByteOrder = new String[SingalNumber]; String[] Signed = new String[SingalNumber]; String[] RangeScale = new String[SingalNumber]; String[] RangeOffset = new String[SingalNumber]; String[] RangeLow = new String[SingalNumber]; String[] RangeHigh = new String[SingalNumber]; String[] RangeUnit = new String[SingalNumber]; String[] Receiver = new String[SingalNumber]; int SingalOrder = 0; int HowManySingalinOneMessage = 0; int BasicSingal = 0; boolean Ecustatus = false; String status1 = ""; String status2 = ""; String status3 = ""; String[] MessagerInfo = new String[4]; while ((tempString = reader.readLine()) != null) { // ? String[] LineData = tempString.split(" "); String[] LineUnit = tempString.split("] \""); String[] LineReceiver = tempString.split("\" "); String[] ECUroot = new String[LineData.length - 1]; if (LineData.length >= 4 && LineData[0].equals("BU_:")) { // System.out.println("line " + line + ": " + LineData[0]); int EcuNumber = LineData.length - 1; for (int i = 0; i < EcuNumber; i++) { ECUroot[i] = LineData[i + 1]; //ECU? //TitleList.add(ECUroot[i].toString()); } } if (LineData.length >= 4 && LineData[0].equals("BO_")) { // System.out.println("line " + line + ": " + LineData[0]); int EcuInfoNumber = LineData.length - 1; for (int i = 0; i < EcuInfoNumber; i++) { MessagerInfo[i] = LineData[i + 1]; //System.out.println(MessagerInfo[i]); } Ecustatus = true; MessagerIdNumber++; BasicSingal = BasicSingal + HowManySingalinOneMessage; HowManySingalinOneMessage = 0; } if (LineData.length >= 4 && LineData[1].equals("SG_")) { //??? Ecustatus = false; //?1??? HowManySingalinOneMessage++; //System.out.println("line " + line + ": " + LineData[4]); SingalName[SingalOrder] = LineData[2]; //System.out.println(SingalName[SingalOrder]); SingalOffset[SingalOrder] = LineData[4].split("\\|")[0]; //System.out.println(SingalOffset[SingalOrder]); SingalSize[SingalOrder] = LineData[4].split("@")[0].split("\\|")[1]; //System.out.println(SingalSize[SingalOrder]); ByteOrder[SingalOrder] = LineData[4].split("@")[1].substring(0, 1); // System.out.println(ByteOrder[SingalOrder]); Signed[SingalOrder] = LineData[4].split("@")[1].substring(1, 2); // System.out.println(Signed[SingalOrder]); RangeScale[SingalOrder] = LineData[5].split(",")[0].substring(1); // System.out.println(RangeScale[SingalOrder]); RangeOffset[SingalOrder] = LineData[5].split(",")[1].substring(0, (LineData[5].split(",")[1].length() - 1)); //System.out.println(RangeOffset[SingalOrder]); RangeLow[SingalOrder] = LineData[6].split("\\|")[0].substring(1); ; //System.out.println(RangeLow[SingalOrder]); RangeHigh[SingalOrder] = LineData[6].split("\\|")[1].substring(0, (LineData[6].split("\\|")[1].length() - 1)); ; //System.out.println(RangeHigh[SingalOrder]); RangeUnit[SingalOrder] = LineUnit[1].split("\"")[0]; //System.out.println( RangeUnit[SingalOrder]); Receiver[SingalOrder] = LineReceiver[1]; System.out.println(Receiver[SingalOrder]); SingalOrder++; // System.out.println((LineData.length)); } if (LineData.length > 1) { status1 = LineData[0] + LineData[1]; status3 = status2 + status1; } else { status1 = LineData[0]; status3 = status2 + status1; } status2 = status1; //System.out.println(status3); if (status3.equalsIgnoreCase("SG_")) { if (HowManySingalinOneMessage != 0) { for (int j = 0; j < MessagerIdNumber; j++) { for (int i = 0; i < 4; i++) { //Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo,(short)columnTo); Region region = new Region((short) (BasicSingal + 1), (short) i, (short) (BasicSingal + HowManySingalinOneMessage), (short) i); sheet.addMergedRegion(region); //System.out.println(MessagerInfo[i]); HSSFCell cell = sheet.createRow((short) (BasicSingal + 1)).createCell((short) i); cell.setCellValue(MessagerInfo[i]); } } } } line++; } // Object Title[] = TitleList.toArray(); for (int i = 0; i < Title.length; i++) { //0? HSSFRow row = sheet.createRow((short) 0); //0??() HSSFCell cell = row.createCell((short) i); //? cell.setCellType(HSSFCell.CELL_TYPE_STRING); //? cell.setCellValue(Title[i].toString()); } //?? for (int i = 0; i < SingalOrder; i++) { //0? HSSFRow row = sheet.createRow((short) (i + 1)); //System.out.println(SingalName[0]); for (int j = 0; j < 11; j++) { //0??() HSSFCell cell = row.createCell((short) (j + 4)); //? cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellValue(SingalName[i]); //? switch (j) { case 0: cell.setCellValue(SingalName[i]); break; case 1: cell.setCellValue(SingalOffset[i]); break; case 2: cell.setCellValue(SingalSize[i]); break; case 3: cell.setCellValue(ByteOrder[i]); break; case 4: cell.setCellValue(Signed[i]); break; case 5: cell.setCellValue(RangeScale[i]); break; case 6: cell.setCellValue(RangeOffset[i]); break; case 7: cell.setCellValue(RangeLow[i]); break; case 8: cell.setCellValue(RangeHigh[i]); break; case 9: cell.setCellValue(RangeUnit[i]); break; case 10: cell.setCellValue(Receiver[i]); break; default: cell.setCellValue(""); } } } fOut = new FileOutputStream(outputFile); //Excel workbook.write(fOut); fOut.flush(); reader.close(); } catch (IOException e) { e.printStackTrace(); } finally { if (reader != null) { try { if (fOut != null) { fOut.close(); } reader.close(); } catch (IOException e1) { e1.printStackTrace(); } } } }
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(//from w w w. ja v a 2 s . 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.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);/* w ww . 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;/*w ww .j a v a 2 s. c om*/ 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.generationcp.breeding.manager.crossingmanager.util.CrossingManagerExporter.java
License:Open Source License
private int writeStudyDetailsSection(HashMap<String, CellStyle> styles, HSSFSheet descriptionSheet, int startingRow) { CrossingManagerUploader uploader = this.crossesMade.getCrossingManagerUploader(); int actualRow = startingRow - 1; int currentRow = actualRow; int ctr = 0;//from ww w. ja v a 2 s . co m for (TemplateStudyDetails studyDetail : TemplateStudyDetails.values()) { String header = studyDetail.getValue(); //get header from enum currentRow = actualRow + ctr; HSSFRow row = descriptionSheet.createRow(currentRow); descriptionSheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 1, 7)); Cell labelCell = row.createCell(0); labelCell.setCellValue(header); labelCell.setCellStyle(styles.get(LABEL_STYLE)); Cell valueCell = row.createCell(1); setStudyDetailCellValue(uploader, studyDetail, valueCell); ctr++; } //return the next row to write to. +2 because it's decremented at start of method return currentRow + 2; }
From source file:org.generationcp.breeding.manager.nurserytemplate.util.NurseryTemplateManagerExporter.java
License:Open Source License
private int writeStudyDetailsSection(HashMap<String, CellStyle> styles, HSSFSheet descriptionSheet, int startingRow) { int actualRow = startingRow - 1; int currentRow = actualRow; int ctr = 0;/* w w w . j a v a 2 s . c o m*/ for (TemplateStudyDetails studyDetail : TemplateStudyDetails.values()) { String header = studyDetail.getValue(); //get header from enum currentRow = actualRow + ctr; HSSFRow row = descriptionSheet.createRow(currentRow); descriptionSheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 1, 7)); Cell labelCell = row.createCell(0); labelCell.setCellValue(header); labelCell.setCellStyle(styles.get(LABEL_STYLE)); Cell valueCell = row.createCell(1); setStudyDetailCellValue(nurseryTemplateData, studyDetail, valueCell); ctr++; } //return the next row to write to. +2 because it's decremented at start of method return currentRow + 2; }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row HSSFRow newRow = worksheet.getRow(destinationRowNum); HSSFRow sourceRow = worksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {/* w ww . jav a 2 s. c o m*/ newRow = worksheet.createRow(destinationRowNum); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell HSSFCell oldCell = sourceRow.getCell(i); HSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell HSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (newCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } }
From source file:org.jxstar.report.studio.ExportStatBO.java
/** * //from w w w . j a va2 s . c o m * @param fileTitle -- * @param titles -- * @param sheet -- * @return */ private HSSFSheet createTitleArea(String fileTitle, String[] titles, HSSFSheet sheet) { HSSFCell sfCell = null; int rsCnt = titles.length + 1; HSSFWorkbook wb = sheet.getWorkbook(); //? HSSFCellStyle titleStyle = expXls.createTitleStyle(wb); //? HSSFCellStyle headerStyle = expXls.createHeadStyle(wb); //1 HSSFRow hfRow = sheet.createRow(0); hfRow.setHeightInPoints(25); for (int i = 0, n = rsCnt; i < n; i++) { sfCell = hfRow.createCell(i); sheet.setColumnWidth(i, 4000);//? } sfCell = hfRow.getCell(0); sheet.setColumnWidth(0, 448); // //?? CellRangeAddress range = new CellRangeAddress(0, 0, 1, rsCnt - 1); sheet.addMergedRegion(range); //?? sfCell = hfRow.getCell(1); sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); sfCell.setCellValue(fileTitle); sfCell.setCellStyle(titleStyle); // hfRow = sheet.createRow(1); for (int i = 0, n = rsCnt; i < n; i++) { sfCell = hfRow.createCell(i); if (i == 0) continue; String colname = titles[i - 1]; sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); sfCell.setCellValue(colname); sfCell.setCellStyle(headerStyle); } return sheet; }
From source file:org.jxstar.report.studio.ExportXlsBO.java
/** * //from ww w . j av a 2 s .com * @param title -- * @param lsCol -- * @param sheet -- * @return */ private HSSFSheet createTitleArea(String title, List<Map<String, String>> lsCol, HSSFSheet sheet) { HSSFCell sfCell = null; int rsCnt = lsCol.size() + 1; // HSSFRow hfRow = sheet.createRow(0); hfRow.setHeightInPoints(10); for (int i = 0, n = rsCnt; i < n; i++) { sfCell = hfRow.createCell(i); sheet.setColumnWidth(i, 4000); } sfCell = hfRow.getCell(0); sheet.setColumnWidth(0, 448); // //? hfRow = sheet.createRow(1); hfRow.setHeightInPoints(25); for (int i = 0, n = rsCnt; i < n; i++) { sfCell = hfRow.createCell(i); } //???6? int posi = rsCnt / 2; int fromCell = (((posi - 2) < 0) ? 0 : (posi - 2)); int toCell = (((rsCnt - posi) < 0) ? 0 : (rsCnt - posi + 2)); CellRangeAddress range = new CellRangeAddress(1, 1, fromCell, toCell); sheet.addMergedRegion(range); HSSFWorkbook wb = sheet.getWorkbook(); //?? sfCell = hfRow.getCell(fromCell); sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); sfCell.setCellValue(title); sfCell.setCellStyle(createTitleStyle(wb)); // hfRow = sheet.createRow(2); for (int i = 0, n = rsCnt; i < n; i++) { sfCell = hfRow.createCell(i); if (i != 0) { String colname = lsCol.get(i - 1).get("col_name"); sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); sfCell.setCellValue(colname); sfCell.setCellStyle(createHeadStyle(wb)); } } return sheet; }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * //www. ja v a2 s . c om * @param mainSheet -- ? * @param subSheet -- ? * @param tempRow -- ????? * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet, int tempRow) { if (mainSheet == null || subSheet == null) return null; //?? if (!isAllowOut(mainSheet)) return mainSheet; //? int endRowNum = mainSheet.getPhysicalNumberOfRows(); HSSFRow sourow = null, descrow = null; HSSFCell sourcell = null, descell = null, orgcell = null; int i = 0, offsetcnt = 0; //? copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook()); //?? CellRangeAddress range = null; int mergedNum = subSheet.getNumMergedRegions(); for (i = 0; i < mergedNum; i++) { range = subSheet.getMergedRegion(i); range.setFirstRow(range.getFirstRow() + endRowNum); range.setLastRow(range.getLastRow() + endRowNum); mainSheet.addMergedRegion(range); } range = null; //int k = 0; //? mainSheet.setAlternativeExpression(subSheet.getAlternateExpression()); mainSheet.setAlternativeFormula(subSheet.getAlternateFormula()); mainSheet.setAutobreaks(subSheet.getAutobreaks()); mainSheet.setDialog(subSheet.getDialog()); mainSheet.setDisplayGuts(subSheet.getDisplayGuts()); mainSheet.setFitToPage(subSheet.getFitToPage()); for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext();) { sourow = (HSSFRow) iterow.next(); offsetcnt = sourow.getRowNum() + endRowNum; descrow = mainSheet.createRow(offsetcnt); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); /** * ??????orgcell = mainSheet.getRow(row).getCell(column); * ?? * ??orgcell.getCellStyle()????sheet?? * This Style does not belong to the supplied Workbook. * ?descell.getCellStyle().cloneStyleFrom(sourcell.getCellStyle());???excel * HSSFCellStyle cs = mainSheet.getWorkbook().createCellStyle(); * cs.cloneStyleFrom(sourcell.getCellStyle()); * descell.setCellStyle(cs);//excel? * tempRow???? */ //???????? int row = sourcell.getRowIndex(); if (tempRow > 0 && row > tempRow) { row = tempRow; } orgcell = mainSheet.getRow(row).getCell(column); if (orgcell != null) { //orgcell.getCellType()???0 descell.setCellType(HSSFCell.CELL_TYPE_STRING); //??? descell.setCellStyle(orgcell.getCellStyle()); } else { _log.showWarn("module xls [{0}, {1}] cell is null!", row, column); } if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING) descell.setCellValue(sourcell.getStringCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) descell.setCellValue(sourcell.getNumericCellValue()); } sourow = null; sourcell = null; descrow = null; orgcell = null; } return mainSheet; }