List of usage examples for org.apache.poi.ss.usermodel Row setHeightInPoints
void setHeightInPoints(float height);
From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java
License:Open Source License
/** * // ww w . jav a 2s. com * method name : getExcelCollegeCoursesAsstDean * @param templateName * @param object * @param response * @param params * @param locale * @return * @throws DocumentException * @throws IOException * TeachingSurveyExcelImpl * return type : OutputStream * * purpose : * * Date : Jun 7, 2016 11:49:24 AM */ public OutputStream getExcelCollegeCoursesAsstDean(String templateName, Object object, ResourceResponse response, Map<String, String> params, Locale locale) throws DocumentException, IOException { int colHead = 0; int rowNum = 0; String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY); String titleRegion = null; List<StudentResponse> studentResponses = (List<StudentResponse>) object; Workbook workbook = new HSSFWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); Map<String, CellStyle> styles = createStyles(workbook); Sheet sheet = null; Cell cellSH = null; sheet = workbook .createSheet(UtilProperty.getMessage("prop.course.teaching.survey.courses.list", null, locale)); sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); /** Header Footer **/ Footer footer = sheet.getFooter(); Header header = sheet.getHeader(); footer.setRight("Page &P of &N"); footer.setLeft("&D"); header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale)); header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale)); header.setRight(paramTypeSurvey); sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2")); sheet.setDisplayGridlines(true); sheet.setPrintGridlines(true); /** Title **/ Row titleRow = sheet.createRow(rowNum); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(paramTypeSurvey); titleCell.setCellStyle(styles.get(TITLE)); ++rowNum; titleRegion = "$A$" + rowNum + ":$O$" + rowNum; sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion)); /** Header Row **/ Row rowSubHeader = sheet.createRow(rowNum++); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper .createRichTextString(UtilProperty.getMessage("prop.course.teaching.survey.course", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.committee.member.number", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.committee.member.name", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.seats.taken", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.response.students", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.include.exclude", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); /** Report details **/ for (StudentResponse studentResponse : studentResponses) { int colNum = 0; Row row = sheet.createRow((short) rowNum); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getDepartmentName())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getCourseCode())); row.createCell(colNum++).setCellValue(Integer.parseInt(studentResponse.getSectionNo())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpNumber())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpName())); row.createCell(colNum++).setCellValue(studentResponse.getSeatsTaken()); row.createCell(colNum++).setCellValue(studentResponse.getStudentResponse()); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getIncludeExclude())); rowNum++; } response.setContentType("application/vnd.ms-excel"); OutputStream outputStream = response.getPortletOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); return null; }
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 2 s .c o 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.aio.handy.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*from w w w .j a v a 2 s .c o m*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); // title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Weekly Timesheet"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); // header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { // the 10th cell contains sum over week days, e.g. // SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } // row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); // set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } // finally set column widths, the width is measured in units of 1/256th // of a character width sheet.setColumnWidth(0, 30 * 256); // 30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); // 6 characters wide } sheet.setColumnWidth(10, 10 * 256); // 10 characters wide // Write the output to a file String file = "e:/timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetSetRowWidth.java
License:Open Source License
public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = null; int rowNo, rowHeight; /*//w ww.j av a2 s .c om * Collect up the parameters */ spreadsheet = (cfSpreadSheetData) parameters.get(2); rowNo = parameters.get(1).getInt() - 1; rowHeight = parameters.get(0).getInt(); if (rowNo < 0) throwException(_session, "row must be 1 or greater (" + rowNo + ")"); Sheet sheet = spreadsheet.getActiveSheet(); Row row = sheet.getRow(rowNo); if (row == null) row = sheet.createRow(rowNo); row.setHeightInPoints(rowHeight); return cfBooleanData.TRUE; }
From source file:org.alfresco.repo.web.scripts.datalist.DataListDownloadWebScript.java
License:Open Source License
@Override protected void populateBody(Object resource, Workbook workbook, Sheet sheet, List<QName> properties) throws IOException { NodeRef list = (NodeRef) resource;//from w w w .j a va 2 s. c om List<NodeRef> items = getItems(list); // Our various formats DataFormat formatter = workbook.createDataFormat(); CellStyle styleInt = workbook.createCellStyle(); styleInt.setDataFormat(formatter.getFormat("0")); CellStyle styleDate = workbook.createCellStyle(); styleDate.setDataFormat(formatter.getFormat("yyyy-mm-dd")); CellStyle styleDouble = workbook.createCellStyle(); styleDouble.setDataFormat(formatter.getFormat("General")); CellStyle styleNewLines = workbook.createCellStyle(); styleNewLines.setWrapText(true); // Export the items int rowNum = 1, colNum = 0; for (NodeRef item : items) { Row r = sheet.createRow(rowNum); colNum = 0; for (QName prop : properties) { Cell c = r.createCell(colNum); Serializable val = nodeService.getProperty(item, prop); if (val == null) { // Is it an association, or just missing? List<AssociationRef> assocs = nodeService.getTargetAssocs(item, prop); if (assocs.size() > 0) { StringBuffer text = new StringBuffer(); int lines = 1; for (AssociationRef ref : assocs) { NodeRef child = ref.getTargetRef(); QName type = nodeService.getType(child); if (ContentModel.TYPE_PERSON.equals(type)) { if (text.length() > 0) { text.append('\n'); lines++; } text.append(nodeService.getProperty(child, ContentModel.PROP_USERNAME)); } else if (ContentModel.TYPE_CONTENT.equals(type)) { // TODO Link to the content if (text.length() > 0) { text.append('\n'); lines++; } text.append(nodeService.getProperty(child, ContentModel.PROP_TITLE)); } else { System.err.println("TODO: handle " + type + " for " + child); } } String v = text.toString(); c.setCellValue(v); if (lines > 1) { c.setCellStyle(styleNewLines); r.setHeightInPoints(lines * sheet.getDefaultRowHeightInPoints()); } } else { // This property isn't set c.setCellType(Cell.CELL_TYPE_BLANK); } } else { // Regular property, set if (val instanceof String) { c.setCellValue((String) val); } else if (val instanceof Date) { c.setCellValue((Date) val); c.setCellStyle(styleDate); } else if (val instanceof Integer || val instanceof Long) { double v = 0.0; if (val instanceof Long) v = (double) (Long) val; if (val instanceof Integer) v = (double) (Integer) val; c.setCellValue(v); c.setCellStyle(styleInt); } else if (val instanceof Float || val instanceof Double) { double v = 0.0; if (val instanceof Float) v = (double) (Float) val; if (val instanceof Double) v = (double) (Double) val; c.setCellValue(v); c.setCellStyle(styleDouble); } else { // TODO System.err.println("TODO: handle " + val.getClass().getName() + " - " + val); } } colNum++; } rowNum++; } // Sensible column widths please! colNum = 0; for (QName prop : properties) { sheet.autoSizeColumn(colNum); colNum++; } }
From source file:org.bbreak.excella.reports.tag.BlockRowRepeatParamParser.java
License:Open Source License
@Override public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { try {//ww w . j av a 2 s . c o m // ?? Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? checkParam(sheet, paramDef, tagCell); ReportsParserInfo reportsParserInfo = (ReportsParserInfo) data; ParamInfo paramInfo = reportsParserInfo.getParamInfo(); // parse? ParsedReportInfo parsedReportInfo = new ParsedReportInfo(); List<Object> resultList = new ArrayList<Object>(); // ? int finalBlockRowIndex = 0; int finalBlockColIndex = 0; String brTagName = paramDef.get(PARAM_VALUE); if (log.isDebugEnabled()) { log.debug("BR??: " + brTagName); } // ????? Object[] paramInfos = getParamData(paramInfo, brTagName); if (paramInfos == null) { return parsedReportInfo; } // ???? List<SingleParamParser> singleParsers = getSingleReplaceParsers(reportsParserInfo); // POJOParamInfo??? List<ParamInfo> paramInfoList = new ArrayList<ParamInfo>(); for (Object obj : paramInfos) { if (obj instanceof ParamInfo) { paramInfoList.add((ParamInfo) obj); continue; } ParamInfo childParamInfo = new ParamInfo(); Map<String, Object> map = PropertyUtils.describe(obj); for (Map.Entry<String, Object> entry : map.entrySet()) { for (ReportsTagParser<?> parser : singleParsers) { childParamInfo.addParam(parser.getTag(), entry.getKey(), entry.getValue()); } } paramInfoList.add(childParamInfo); } // ?? if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) { Integer minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM)); if (minRepeatNum > paramInfoList.size()) { int addEmptyRowNum = minRepeatNum - paramInfoList.size(); for (int num = 0; num < addEmptyRowNum; num++) { ParamInfo childParamInfo = new ParamInfo(); paramInfoList.add(childParamInfo); } } } paramInfos = paramInfoList.toArray(new ParamInfo[paramInfoList.size()]); // repeatNum Integer repeatNum = paramInfos.length; if (paramDef.containsKey(PARAM_REPEAT_NUM)) { if (Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM)) < repeatNum) { repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM)); } } // duplicateParams ???????? Map<String, Object> unduplicableParamMap = new HashMap<String, Object>(); if (paramDef.containsKey(PARAM_DUPLICATE)) { String[] params = paramDef.get(PARAM_DUPLICATE).split(";"); for (String param : params) { for (ReportsTagParser<?> parser : singleParsers) { param = parser.getTag() + TAG_PARAM_PREFIX + param + TAG_PARAM_SUFFIX; unduplicableParamMap.put(param, ""); } } } // removeTag boolean removeTag = false; if (paramDef.containsKey(PARAM_REMOVE_TAG)) { removeTag = Boolean.valueOf(paramDef.get(PARAM_REMOVE_TAG)); } // int tagCellRowIndex = tagCell.getRowIndex(); int tagCellColIndex = tagCell.getColumnIndex(); // fromCell String fromCellParamDef = paramDef.get(PARAM_FROM_CELL); int[] fromCellPosition = ReportsUtil.getCellIndex(fromCellParamDef, PARAM_FROM_CELL); int defaultFromCellRowIndex = tagCellRowIndex + fromCellPosition[0]; int defaultFromCellColIndex = tagCellColIndex + fromCellPosition[1]; // toCell String toCellParamDef = paramDef.get(PARAM_TO_CELL); int[] toCellPosition = ReportsUtil.getCellIndex(toCellParamDef, PARAM_TO_CELL); int defaultToCellRowIndex = tagCellRowIndex + toCellPosition[0]; int defaultToCellColIndex = tagCellColIndex + toCellPosition[1]; // Object[][] blockCellValues = ReportsUtil.getBlockCellValue(sheet, defaultFromCellRowIndex, defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex); CellStyle[][] blockCellStyles = ReportsUtil.getBlockCellStyle(sheet, defaultFromCellRowIndex, defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex); CellType[][] blockCellTypes = ReportsUtil.getBlockCellType(sheet, defaultFromCellRowIndex, defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex); float[] rowHeight = ReportsUtil.getRowHeight(sheet, defaultFromCellRowIndex, defaultToCellRowIndex); // ??? CellRangeAddress[] margedCells = ReportsUtil.getMargedCells(sheet, defaultFromCellRowIndex, defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex); // ? final int defaultBlockHeight = defaultToCellRowIndex - defaultFromCellRowIndex + 1; final int defaultBlockWidth = defaultToCellColIndex - defaultFromCellColIndex + 1; int rowlen = defaultBlockHeight; int collen = defaultBlockWidth; // ??(fromCell??, 1?????) int blockStartRowIndex = defaultFromCellRowIndex; int blockStartColIndex = defaultFromCellColIndex; int blockEndRowIndex = defaultToCellRowIndex; int blockEndColIndex = defaultToCellColIndex; int maxblockEndRowIndex = blockEndRowIndex; parsedReportInfo.setDefaultRowIndex(defaultToCellRowIndex); parsedReportInfo.setDefaultColumnIndex(defaultToCellColIndex); for (int repeatCount = 0; repeatCount < repeatNum; repeatCount++) { // ???? if (repeatCount > 0) { blockStartRowIndex = blockEndRowIndex + 1; blockEndRowIndex = blockStartRowIndex + rowlen - 1; CellRangeAddress rangeAddress = new CellRangeAddress(blockStartRowIndex, blockEndRowIndex, blockStartColIndex, PoiUtil.getLastColNum(sheet)); PoiUtil.insertRangeDown(sheet, rangeAddress); if (log.isDebugEnabled()) { log.debug(""); log.debug(" : " + blockStartRowIndex + ":" + (blockStartRowIndex + rowlen - 1) + ":" + blockStartColIndex + ":" + PoiUtil.getLastColNum(sheet)); } // ??? // ??????????? // ?????? int targetRowNum = maxblockEndRowIndex - (defaultFromCellRowIndex - 1); for (CellRangeAddress address : margedCells) { // ??? + BR?? - BR? // ??? + BR?? - BR? // ???(BR????????) // ???(BR????????) int firstRowNum = address.getFirstRow() + targetRowNum; int lastRowNum = address.getLastRow() + targetRowNum; int firstColumnNum = address.getFirstColumn(); int lastColumnNum = address.getLastColumn(); CellRangeAddress copyAddress = new CellRangeAddress(firstRowNum, lastRowNum, firstColumnNum, lastColumnNum); sheet.addMergedRegion(copyAddress); } } if (log.isDebugEnabled()) { log.debug("repeatCount = " + repeatCount); log.debug("blockStartRowIndex = " + blockStartRowIndex); log.debug("blockStartColIndex = " + blockStartColIndex); } // ?? if (log.isDebugEnabled()) { log.debug("?????? =" + repeatCount); } for (int rowIdx = 0; rowIdx < defaultBlockHeight; rowIdx++) { // ? Row row = sheet.getRow(blockStartRowIndex + rowIdx); // ??null??????????????? if (row == null && !ReportsUtil.isEmptyRow(blockCellTypes[rowIdx], blockCellValues[rowIdx], blockCellStyles[rowIdx])) { row = sheet.createRow(blockStartRowIndex + rowIdx); } if (row != null) { // ??? row.setHeightInPoints(rowHeight[rowIdx]); // ? for (int colIdx = 0; colIdx < defaultBlockWidth; colIdx++) { // ? Cell cell = row.getCell(blockStartColIndex + colIdx); // ?? CellType cellType = blockCellTypes[rowIdx][colIdx]; // ?? Object cellValue = blockCellValues[rowIdx][colIdx]; // ?? CellStyle cellStyle = blockCellStyles[rowIdx][colIdx]; // ????????????????? if (cell == null && !ReportsUtil.isEmptyCell(cellType, cellValue, cellStyle)) { cell = row.createCell(blockStartColIndex + colIdx); } // if (cell != null) { // ? cell.setCellType(cellType); // ?? PoiUtil.setCellValue(cell, cellValue); // ?? if (cellStyle == null) { log.info("Cell Style at [" + rowIdx + "," + colIdx + "] is not available. Skipping setCellValue()"); } else { cell.setCellStyle(cellStyle); } log.debug("row=" + (blockStartRowIndex + rowIdx) + " col" + (blockStartColIndex + colIdx) + ">>>>>>" + blockCellValues[rowIdx][colIdx]); } } } } int currentBlockHeight = rowlen; int currentBlockWidth = collen; // ????????? int plusRowNum = 0; // ????????? int plusColNum = 0; collen = defaultBlockWidth; // // ??? for (int targetRow = blockStartRowIndex; targetRow < blockStartRowIndex + rowlen + plusRowNum; targetRow++) { if (finalBlockRowIndex < targetRow) { finalBlockRowIndex = targetRow; } if (sheet.getRow(targetRow) == null) { if (log.isDebugEnabled()) { log.debug("row=" + targetRow + " : row is not available. continued..."); } continue; } for (int targetCol = blockStartColIndex; targetCol <= blockStartColIndex + collen + plusColNum - 1; targetCol++) { if (finalBlockColIndex < targetCol) { finalBlockColIndex = targetCol; } Cell targetCell = sheet.getRow(targetRow).getCell(targetCol); if (targetCell == null) { if (log.isDebugEnabled()) { log.debug("row=" + targetRow + " col=" + targetCol + " : cell is not available. continued..."); } continue; } // ?? TagParser<?> parser = reportsParserInfo.getMatchTagParser(sheet, targetCell); if (parser == null) { if (log.isDebugEnabled()) { log.debug("row=" + targetRow + " col=" + targetCol + " parser is not available. continued..."); } continue; } String targetCellTag = targetCell.getStringCellValue(); if (log.isDebugEnabled()) { log.debug("########## row=" + targetRow + " col=" + targetCol + " =" + targetCellTag + " ##########"); } // ParsedReportInfo result = (ParsedReportInfo) parser.parse(sheet, targetCell, reportsParserInfo.createChildParserInfo((ParamInfo) paramInfos[repeatCount])); resultList.add(result.getParsedObject()); // ??? plusRowNum += result.getRowIndex() - result.getDefaultRowIndex(); // ??? plusColNum += result.getColumnIndex() - result.getDefaultColumnIndex(); int additionalHeight = result.getRowIndex() - result.getDefaultRowIndex(); int additionalWidth = result.getColumnIndex() - result.getDefaultColumnIndex(); // ?????????? currentBlockHeight = currentBlockHeight + additionalHeight; currentBlockWidth = currentBlockWidth + additionalWidth; // ??? if (parser instanceof SingleParamParser) { if (unduplicableParamMap.containsKey(targetCellTag)) { if (unduplicableParamMap.get(targetCellTag).equals(result.getParsedObject())) { PoiUtil.setCellValue(targetCell, ""); } else { unduplicableParamMap.put(targetCellTag, result.getParsedObject()); } } } // ?????????????? if (defaultFromCellColIndex != result.getDefaultColumnIndex() && result.getRowIndex() > result.getDefaultRowIndex() && blockStartColIndex < targetCol) { CellRangeAddress preRangeAddress = new CellRangeAddress(blockEndRowIndex + 1, blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()), blockStartColIndex, targetCol - 1); PoiUtil.insertRangeDown(sheet, preRangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("1 : " + (blockEndRowIndex + 1) + ":" + (blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex())) + ":" + blockStartColIndex + ":" + (targetCol - 1)); } } // R?????? if (parser instanceof RowRepeatParamParser && result.getRowIndex() > result.getDefaultRowIndex()) { CellRangeAddress rearRangeAddress = new CellRangeAddress(blockEndRowIndex + 1, blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()), result.getDefaultColumnIndex() + 1, PoiUtil.getLastColNum(sheet)); PoiUtil.insertRangeDown(sheet, rearRangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("2 : " + (blockEndRowIndex + 1) + ":" + (blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex())) + ":" + (result.getDefaultColumnIndex() + 1) + ":" + PoiUtil.getLastColNum(sheet)); } } blockEndRowIndex += result.getRowIndex() - result.getDefaultRowIndex(); if (parser instanceof BlockColRepeatParamParser || parser instanceof BlockRowRepeatParamParser) { collen += result.getColumnIndex() - result.getDefaultColumnIndex(); plusColNum -= result.getColumnIndex() - result.getDefaultColumnIndex(); } // ???????? if (blockStartColIndex + collen + plusColNum - 1 > blockEndColIndex) { int beforeLastColIndex = blockEndColIndex; blockEndColIndex = blockStartColIndex + collen + plusColNum - 1; // ??????????? CellRangeAddress preRangeAddress = new CellRangeAddress(tagCell.getRowIndex(), targetRow - 1, beforeLastColIndex + 1, blockEndColIndex); PoiUtil.insertRangeRight(sheet, preRangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("1 : " + tagCell.getRowIndex() + ":" + (targetRow - 1) + ":" + (beforeLastColIndex + 1) + ":" + blockEndColIndex); } } // ? } // ?????? if (blockStartColIndex + collen + plusColNum - 1 < blockEndColIndex) { CellRangeAddress rearRangeAddress = new CellRangeAddress(targetRow, targetRow, blockStartColIndex + collen + plusColNum, blockEndColIndex); PoiUtil.insertRangeRight(sheet, rearRangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("2 : " + targetRow + ":" + targetRow + ":" + (blockStartColIndex + collen + plusColNum) + ":" + blockEndColIndex); } } plusColNum = 0; // ? } // ??????? int lastColNum = PoiUtil.getLastColNum(sheet); if ((maxblockEndRowIndex + 1) <= blockEndRowIndex && (blockEndColIndex + 1) <= lastColNum) { CellRangeAddress rearRangeAddress = new CellRangeAddress(maxblockEndRowIndex + 1, blockEndRowIndex, blockEndColIndex + 1, lastColNum); PoiUtil.insertRangeDown(sheet, rearRangeAddress); if (log.isDebugEnabled()) { log.debug("2 : " + (maxblockEndRowIndex + 1) + ":" + blockEndRowIndex + ":" + (blockEndColIndex + 1) + ":" + lastColNum); } } maxblockEndRowIndex = blockEndRowIndex; } // if (removeTag) { tagCell.setCellType(CellType.BLANK); } // ?? parsedReportInfo.setColumnIndex(finalBlockColIndex); parsedReportInfo.setRowIndex(finalBlockRowIndex); parsedReportInfo.setParsedObject(resultList); parsedReportInfo.setDefaultRowIndex(defaultToCellRowIndex); parsedReportInfo.setDefaultColumnIndex(defaultToCellColIndex); if (log.isDebugEnabled()) { log.debug("finalBlockRowIndex= " + finalBlockRowIndex); log.debug("finalBlockColIndex=" + finalBlockColIndex); } return parsedReportInfo; } catch (Exception e) { throw new ParseException(tagCell, e); } }
From source file:org.bonitasoft.connectors.excel.AddDimensionedImage.java
License:Apache License
/** * Determines whether the sheet's row should be re-sized to accomodate * the image, adjusts the rows height if necessary and creates then * returns a ClientAnchorDetail object that facilitates construction of * a ClientAnchor that will fix the image on the sheet and establish * it's size.// ww w. ja v a 2 s. c o m * * @param sheet A reference to the sheet that will 'contain' the image. * @param rowNumber A primtive int that contains the index number of a * row on the sheet. * @param reqImageHeightMM A primtive double that contains the required * height of the image in millimetres * @param resizeBehaviour A primitve int whose value will indicate how the * height of the row should be adjusted if the * required height of the image is greater than the * height of the row. * @return An instance of the ClientAnchorDetail class that will contain * the index number of the row containing the cell whose top * left hand corner also defines the top left hand corner of the * image, the index number of the row containing the cell whose * top left hand corner also defines the bottom right hand * corner of the image and an inset that determines how far the * bottom edge of the image can protrude into the next (lower) * row - expressed as a specific number of co-ordinate positions. */ private ClientAnchorDetail fitImageToRows(Sheet sheet, int rowNumber, double reqImageHeightMM, int resizeBehaviour) { Row row = null; double rowHeightMM = 0.0D; double rowCoordinatesPerMM = 0.0D; int pictureHeightCoordinates = 0; ClientAnchorDetail rowClientAnchorDetail = null; // Get the row and it's height row = sheet.getRow(rowNumber); if (row == null) { // Create row if it does not exist. row = sheet.createRow(rowNumber); } // Get the row's height in millimetres rowHeightMM = row.getHeightInPoints() / ConvertImageUnits.POINTS_PER_MILLIMETRE; // Check that the row's height will accomodate the image at the required // dimensions. If the height of the row is LESS than the required height // of the image, decide how the application should respond - resize the // row or overlay the image across a series of rows. if (rowHeightMM < reqImageHeightMM) { if ((resizeBehaviour == AddDimensionedImage.EXPAND_ROW) || (resizeBehaviour == AddDimensionedImage.EXPAND_ROW_AND_COLUMN)) { row.setHeightInPoints((float) (reqImageHeightMM * ConvertImageUnits.POINTS_PER_MILLIMETRE)); rowHeightMM = reqImageHeightMM; rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS / rowHeightMM; pictureHeightCoordinates = (int) (reqImageHeightMM * rowCoordinatesPerMM); rowClientAnchorDetail = new ClientAnchorDetail(rowNumber, rowNumber, pictureHeightCoordinates); } // If the user has chosen to overlay both rows and columns or just // to expand ONLY the size of the columns, then calculate how to lay // the image out ver one or more rows. else if ((resizeBehaviour == AddDimensionedImage.OVERLAY_ROW_AND_COLUMN) || (resizeBehaviour == AddDimensionedImage.EXPAND_COLUMN)) { rowClientAnchorDetail = this.calculateRowLocation(sheet, rowNumber, reqImageHeightMM); } } // Else, if the image is smaller than the space available else { rowCoordinatesPerMM = ConvertImageUnits.TOTAL_ROW_COORDINATE_POSITIONS / rowHeightMM; pictureHeightCoordinates = (int) (reqImageHeightMM * rowCoordinatesPerMM); rowClientAnchorDetail = new ClientAnchorDetail(rowNumber, rowNumber, pictureHeightCoordinates); } return (rowClientAnchorDetail); }
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.BaseXLS.java
License:Open Source License
/** * This method writes any value into a specific cell. * /*from w ww . java2 s. c o m*/ * @param sheet is the sheet where you want to add information into. * @param value is the specific information to be written. */ public void prepareCell(Sheet sheet) { Row row = sheet.getRow(rowCounter); // if there is no row index, it should create it if (row == null) { row = sheet.createRow(rowCounter); } row.setHeightInPoints((5 * sheet.getDefaultRowHeightInPoints())); cell = row.createCell(columnCounter); cell.setCellStyle(columnStyles[columnCounter - 1]); }
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.BaseXLS.java
License:Open Source License
/** * This method writes the headers into the given sheet. * //from w ww .j av a2 s .c o m * @param sheet is the sheet where you want to write the header. * @param headers is the array of headers to write. */ public void writeHeaders(Sheet sheet, String[] headers) { if (usingTemplate) { // Row Row row = sheet.createRow(rowStart - 1); row.setHeightInPoints(HEADER_ROW_HEIGHT); // Writing headers. Cell cell; int counter; for (counter = 1; counter <= headers.length; counter++) { cell = row.createCell(counter); cell.setCellStyle(styleHeader); cell.setCellValue(headers[counter - 1]); sheet.autoSizeColumn(counter); } } else { } }
From source file:org.dashbuilder.dataset.backend.DataSetBackendServicesImpl.java
License:Apache License
@Override public String exportDataSetExcel(DataSet dataSet) { // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters if (dataSet == null) throw new IllegalArgumentException("Null dataSet specified!"); int columnCount = dataSet.getColumns().size(); int rowCount = dataSet.getRowCount() + 1; //Include header row; int row = 0;//from w ww. ja v a 2s. c o m SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk Map<String, CellStyle> styles = createStyles(wb); Sheet sh = wb.createSheet("Sheet 1"); // General setup sh.setDisplayGridlines(true); sh.setPrintGridlines(false); sh.setFitToPage(true); sh.setHorizontallyCenter(true); PrintSetup printSetup = sh.getPrintSetup(); printSetup.setLandscape(true); // Create header Row header = sh.createRow(row++); header.setHeightInPoints(20f); for (int i = 0; i < columnCount; i++) { Cell cell = header.createCell(i); cell.setCellStyle(styles.get("header")); cell.setCellValue(dataSet.getColumnByIndex(i).getId()); } // Create data rows for (; row < rowCount; row++) { Row _row = sh.createRow(row); for (int cellnum = 0; cellnum < columnCount; cellnum++) { Cell cell = _row.createCell(cellnum); Object value = dataSet.getValueAt(row - 1, cellnum); if (value instanceof Short || value instanceof Long || value instanceof Integer || value instanceof BigInteger) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(styles.get("integer_number_cell")); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(styles.get("decimal_number_cell")); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("date_cell")); cell.setCellValue((Date) value); } else if (value instanceof Interval) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(((Interval) value).getName()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(value.toString()); } } } // Adjust column size for (int i = 0; i < columnCount; i++) { sh.autoSizeColumn(i); } Path tempExcelFilePath = null; try { tempExcelFilePath = ioService.createTempFile("export", "xlsx", null); OutputStream os = Files.newOutputStream(tempExcelFilePath); wb.write(os); os.flush(); os.close(); } catch (Exception e) { log.error("Error in excel export: ", e); } // Dispose of temporary files backing this workbook on disk if (!wb.dispose()) log.warn("Could not dispose of temporary file associated to data export!"); return tempExcelFilePath.toString(); }