public void Offset(int offset) { foreach (LinkedDataRecord link in this) { Ptg[] ptgs = link.FormulaOfLink; foreach (Ptg ptg in ptgs) { if (ptg is RefPtgBase) { RefPtgBase refPtg = ptg as RefPtgBase; refPtg.Row += offset; link.FormulaOfLink = ptgs; } if (ptg is AreaPtgBase) { AreaPtgBase areaPtg = ptg as AreaPtgBase; areaPtg.FirstRow += offset; areaPtg.LastRow += offset; link.FormulaOfLink = ptgs; } } } }
private Ptg RowMoveRefPtg(RefPtgBase rptg) { int refRow = rptg.Row; if (_firstMovedIndex <= refRow && refRow <= _lastMovedIndex) { // Rows being moved completely enclose the ref. // - move the area ref along with the rows regardless of destination rptg.Row = (refRow + _amountToMove); return(rptg); } // else rules for adjusting area may also depend on the destination of the moved rows int destFirstRowIndex = _firstMovedIndex + _amountToMove; int destLastRowIndex = _lastMovedIndex + _amountToMove; // ref is outside source rows // check for clashes with destination if (destLastRowIndex < refRow || refRow < destFirstRowIndex) { // destination rows are completely outside ref return(null); } if (destFirstRowIndex <= refRow && refRow <= destLastRowIndex) { // destination rows enclose the area (possibly exactly) return(CreateDeletedRef(rptg)); } throw new InvalidOperationException("Situation not covered: (" + _firstMovedIndex + ", " + _lastMovedIndex + ", " + _amountToMove + ", " + refRow + ", " + refRow + ")"); }
public bool InRange(HSSFSheet ptgsheet, HSSFSheet testSheet, int startRowIndex, int endRowIndex) { foreach (Ptg ptg in this) { HSSFSheet sheet = PtgCollection.GetPtgSheet(ptgsheet, ptg); if (sheet != testSheet) { return(false); } if (ptg is RefPtgBase) { RefPtgBase refPtg = ptg as RefPtgBase; if (refPtg.Row < startRowIndex || refPtg.Row > endRowIndex) { return(false); } } if (ptg is AreaPtgBase) { AreaPtgBase areaPtg = ptg as AreaPtgBase; if (areaPtg.FirstRow < startRowIndex || areaPtg.LastRow > endRowIndex) { return(false); } } } return(true); }
/** * Modifies rptg in-place and return a reference to rptg if the cell reference * would move due to a row copy operation * Returns <code>null</code> or {@link #RefErrorPtg} if no change was made * * @param aptg * @return */ private Ptg RowCopyRefPtg(RefPtgBase rptg) { int refRow = rptg.Row; if (rptg.IsRowRelative) { int destRowIndex = _firstMovedIndex + _amountToMove; if (destRowIndex < 0 || _version.LastRowIndex < destRowIndex) { return(CreateDeletedRef(rptg)); } rptg.Row = (refRow + _amountToMove); return(rptg); } return(null); }
/** * Creates a non shared formula from the shared formula counterpart, i.e. * Converts the shared formula into the equivalent {@link org.apache.poi.ss.formula.ptg.Ptg} array that it would have, * were it not shared. * * @param ptgs parsed tokens of the shared formula * @param formulaRow * @param formulaColumn */ public Ptg[] ConvertSharedFormulas(Ptg[] ptgs, int formulaRow, int formulaColumn) { Ptg[] newPtgStack = new Ptg[ptgs.Length]; for (int k = 0; k < ptgs.Length; k++) { Ptg ptg = ptgs[k]; byte originalOperandClass = unchecked ((byte)-1); if (!ptg.IsBaseToken) { originalOperandClass = ptg.PtgClass; } if (ptg is RefPtgBase) { RefPtgBase refNPtg = (RefPtgBase)ptg; ptg = new RefPtg(FixupRelativeRow(formulaRow, refNPtg.Row, refNPtg.IsRowRelative), FixupRelativeColumn(formulaColumn, refNPtg.Column, refNPtg.IsColRelative), refNPtg.IsRowRelative, refNPtg.IsColRelative); ptg.PtgClass = (originalOperandClass); } else if (ptg is AreaPtgBase) { AreaPtgBase areaNPtg = (AreaPtgBase)ptg; ptg = new AreaPtg(FixupRelativeRow(formulaRow, areaNPtg.FirstRow, areaNPtg.IsFirstRowRelative), FixupRelativeRow(formulaRow, areaNPtg.LastRow, areaNPtg.IsLastRowRelative), FixupRelativeColumn(formulaColumn, areaNPtg.FirstColumn, areaNPtg.IsFirstColRelative), FixupRelativeColumn(formulaColumn, areaNPtg.LastColumn, areaNPtg.IsLastColRelative), areaNPtg.IsFirstRowRelative, areaNPtg.IsLastRowRelative, areaNPtg.IsFirstColRelative, areaNPtg.IsLastColRelative); ptg.PtgClass = (originalOperandClass); } else if (ptg is OperandPtg) { // Any subclass of OperandPtg is mutable, so it's safest to not share these instances. ptg = ((OperandPtg)ptg).Copy(); } else { // all other Ptgs are immutable and can be shared } newPtgStack[k] = ptg; } return(newPtgStack); }
protected bool AdjustRegionRelativeReference(Ptg[] ptgs, CellReference target, CellRangeAddressBase region) { if (!region.IsInRange(target)) { throw new ArgumentException(target + " is not within " + region); } //return adjustRegionRelativeReference(ptgs, target.getRow() - region.getFirstRow(), target.getCol() - region.getFirstColumn()); int deltaRow = target.Row; int deltaColumn = target.Col; bool shifted = false; foreach (Ptg ptg in ptgs) { // base class for cell reference "things" if (ptg is RefPtgBase) { RefPtgBase reference = (RefPtgBase)ptg; // re-calculate cell references SpreadsheetVersion version = _workbook.GetSpreadsheetVersion(); if (reference.IsRowRelative) { int rowIndex = reference.Row + deltaRow; if (rowIndex > version.MaxRows) { throw new IndexOutOfRangeException(version.Name + " files can only have " + version.MaxRows + " rows, but row " + rowIndex + " was requested."); } reference.Row = rowIndex; shifted = true; } if (reference.IsColRelative) { int colIndex = reference.Column + deltaColumn; if (colIndex > version.MaxColumns) { throw new IndexOutOfRangeException(version.Name + " files can only have " + version.MaxColumns + " columns, but column " + colIndex + " was requested."); } reference.Column = colIndex; shifted = true; } } } return(shifted); }
protected static void CopyRow(int blockRowCount, int tagRowBlockIndex, HSSFRow srcRow, HSSFRow tagRow) { tagRow.Height = srcRow.Height; //tagRow.RowStyle = srcRow.RowStyle; for (int i = 0; i < srcRow.LastCellNum; i++) { // Grab a copy of the old/new cell HSSFCell srcCell = (HSSFCell)srcRow.GetCell(i); HSSFCell tagCell = (HSSFCell)tagRow.CreateCell(i); // If the old cell is null jump to next cell if (srcCell == null) { continue; } // Copy style from old cell and apply to new cell tagCell.CellStyle = srcCell.CellStyle; // If there is a cell comment, copy if (tagCell.CellComment != null) { tagCell.CellComment = srcCell.CellComment; } // If there is a cell hyperlink, copy if (srcCell.Hyperlink != null) { tagCell.Hyperlink = srcCell.Hyperlink; } // Set the cell data type tagCell.SetCellType(srcCell.CellType); // Set the cell data value switch (srcCell.CellType) { case CellType.BLANK: tagCell.SetCellValue(srcCell.StringCellValue); break; case CellType.BOOLEAN: tagCell.SetCellValue(srcCell.BooleanCellValue); break; case CellType.ERROR: tagCell.SetCellErrorValue(srcCell.ErrorCellValue); break; case CellType.FORMULA: int sheetIndex = srcRow.Sheet.Workbook.GetSheetIndex(srcRow.Sheet); Ptg[] ptgs = HSSFFormulaParser.Parse(srcCell.CellFormula, srcRow.Sheet.Workbook as HSSFWorkbook, FormulaType.CELL, sheetIndex); foreach (Ptg ptg in ptgs) { if (ptg is RefPtgBase) { RefPtgBase refptg = ptg as RefPtgBase; if (refptg.Row >= srcRow.RowNum - tagRowBlockIndex && refptg.Row <= srcRow.RowNum - tagRowBlockIndex + blockRowCount) { refptg.Row += tagRow.RowNum - srcRow.RowNum; } } else if (ptg is AreaPtgBase) { AreaPtgBase aptg = ptg as AreaPtgBase; if (aptg.FirstRow >= srcRow.RowNum - tagRowBlockIndex && aptg.FirstRow <= srcRow.RowNum - tagRowBlockIndex + blockRowCount) { aptg.FirstRow += tagRow.RowNum - srcRow.RowNum; aptg.LastRow += tagRow.RowNum - srcRow.RowNum; } } } tagCell.CellFormula = HSSFFormulaParser.ToFormulaString(srcRow.Sheet.Workbook as HSSFWorkbook, ptgs); break; case CellType.NUMERIC: tagCell.SetCellValue(srcCell.NumericCellValue); break; case CellType.STRING: tagCell.SetCellValue(srcCell.RichStringCellValue); break; case CellType.Unknown: tagCell.SetCellValue(srcCell.StringCellValue); break; } } }
//Grid填充 protected static void DoGridFill(HSSFWorkbook book, DataSet dataset) { HSSFSheet sheetFillDefine = (HSSFSheet)book.GetSheet("GridFill"); if (sheetFillDefine == null) { return; } GridDefineCollection gridDefines = new GridDefineCollection(); GridDefine gridDefine = null; object curSection = null; for (int r = 1; r <= sheetFillDefine.LastRowNum; r++) { HSSFRow excelRow = (HSSFRow)sheetFillDefine.GetRow(r); if (excelRow == null) { continue; } //获得FixFill中的一设定行 string[] values = new string[12]; for (int i = 0; i <= values.Length - 1 && i <= excelRow.LastCellNum; i++) { HSSFCell cell = (HSSFCell)excelRow.GetCell(i); if (cell == null) { continue; } string value; if (cell.CellType == CellType.NUMERIC) { value = cell.NumericCellValue.ToString(); } else { value = cell.StringCellValue; } if (value != null) { value = value.Trim(); } values[i] = value; } string sheetName = values[0]; //填充到的sheet string startRow = values[1]; //填充到的Cell string blockRows = values[2]; //替换Cell中的一部分 string tableName = values[3]; //用那个表中的数据填充 string sectionName = values[4]; //用那列数据填充 string minRows = values[11]; //至少几行 //应用缺省值 if (String.IsNullOrEmpty(sheetName)) { sheetName = book.GetSheetName(0); } if (!String.IsNullOrEmpty(startRow)) { gridDefine = new GridDefine(); gridDefines.Add(gridDefine); gridDefine.SheetName = sheetName; gridDefine.Sheet = (HSSFSheet)book.GetSheet(sheetName); gridDefine.StartRow = Int32.Parse(startRow) - 1; gridDefine.BlockRowCount = Int32.Parse(blockRows); gridDefine.FillTableName = tableName; gridDefine.MinRows = 0; Int32.TryParse(minRows, out gridDefine.MinRows); } if (gridDefine == null) { continue; } if (!String.IsNullOrEmpty(sectionName)) { if (String.Compare(sectionName, "Fill", true) == 0) { curSection = gridDefine.GridCellFills; } else if (String.Compare(sectionName, "Template", true) == 0) { curSection = gridDefine.GridBlockTemplates; } else if (String.Compare(sectionName, "Group", true) == 0) { curSection = gridDefine.Groups; } else { curSection = null; } } else { if (curSection is GridCellFillCollection) { GridCellFillCollection fills = curSection as GridCellFillCollection; GridCellFill fill = new GridCellFill(); fills.Add(fill); string cellName = values[5]; string cellVarName = values[6]; string fillColumnName = values[7]; string renderFunction = values[8]; string emptyText = values[9]; //应用缺省值 if (String.IsNullOrEmpty(renderFunction)) { renderFunction = "DefaultRender"; } Point cellPos = YZExcelHelper.CellNameToIndex(cellName); fill.RowOffset = cellPos.Y - gridDefine.StartRow; fill.ColumnIndex = cellPos.X; fill.CellVarName = cellVarName; fill.FillColumnName = fillColumnName; fill.RenderFunction = renderFunction; fill.EmptyText = emptyText; } else if (curSection is GridBlockTemplateCollection) { string templateName = values[5]; string startRowIndexStr = values[6]; string condition = values[7]; int startRowIndex = 0; if (Int32.TryParse(startRowIndexStr, out startRowIndex)) { GridBlockTemplateCollection templates = curSection as GridBlockTemplateCollection; GridBlockTemplate template = new GridBlockTemplate(gridDefine); templates.Add(template); template.Name = templateName; template.StartRow = startRowIndex - 1; template.Condition = condition; } } else if (curSection is GroupCollection) { string columnName = values[5]; if (!String.IsNullOrEmpty(columnName)) { GroupCollection groups = curSection as GroupCollection; Group group = new Group(); groups.Add(group); group.ColumnName = columnName; } } else { } } } gridDefines.Sort(); foreach (GridDefine grid in gridDefines) { HSSFSheet sheetfill = grid.Sheet; foreach (GridBlockTemplate template in grid.GridBlockTemplates) { for (int i = 0; i < grid.BlockRowCount; i++) { HSSFRow row = (HSSFRow)sheetfill.GetRow(template.StartRow + i); template.Rows.Add(row); } for (int i = 0; i < sheetfill.NumMergedRegions; i++) { CellRangeAddress region = sheetfill.GetMergedRegion(i); if (region.FirstRow >= template.StartRow && region.LastRow <= template.StartRow + grid.BlockRowCount - 1) { region = region.Copy(); region.FirstRow -= template.StartRow; region.LastRow -= template.StartRow; template.MergedRegions.Add(region); } } for (int i = 0; i < book.NumberOfSheets; i++) { HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet; if (IsSystemSheet(sheet)) { continue; } foreach (HSSFChart chart in HSSFChart.GetSheetCharts(sheet)) { foreach (HSSFChart.HSSFSeries series in chart.Series) { if (template.Contains(sheet, series)) { SeriesTemplate seriesTemplate = new SeriesTemplate(); template.SeriesTemplates.Add(seriesTemplate); seriesTemplate.Chart = chart; seriesTemplate.Series = series; } } } } } if (grid.GridBlockTemplates.Count != 0) { grid.GridBlockTemplates[grid.GridBlockTemplates.Count - 1].Condition = null; } } for (int i = 0; i < book.NumberOfSheets; i++) { HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet; if (IsSystemSheet(sheet)) { continue; } foreach (CellValueRecordInterface recInferface in sheet.Sheet.RowsAggregate.GetValueRecords()) { if (recInferface is FormulaRecordAggregate) { FormulaRecordAggregate fraInterface = recInferface as FormulaRecordAggregate; FormulaRecord formulaRecord = fraInterface.FormulaRecord; if (formulaRecord.IsSharedFormula) { fraInterface.UnlinkSharedFormula(); } } } } foreach (GridDefine grid in gridDefines) { //创建空Grid HSSFSheet sheetfill = grid.Sheet; DataTable dataTable = dataset.Tables[grid.FillTableName]; int gridTagBlockCount = Math.Max(dataTable.Rows.Count, grid.MinRows); int gridTagRowCount = grid.BlockRowCount * gridTagBlockCount; //GroupData(dataTable); //DataView view1 = new DataView(dataTable); //view1.Sort = "City asc,Shop asc"; //dataTable = view1.ToTable(); if (dataTable != null && dataTable.Rows.Count != 0) { if (dataTable.Rows.Count != 0 && sheetfill.LastRowNum >= grid.TemplateEndRow + 1) { sheetfill.ShiftRows(grid.TemplateEndRow + 1, sheetfill.LastRowNum, gridTagRowCount, true, false); } for (int i = 0; i < sheetfill.NumMergedRegions; i++) { CellRangeAddress region = sheetfill.GetMergedRegion(i); if (region.FirstRow <= grid.StartRow && region.LastRow >= grid.TemplateEndRow) { region.LastRow += Math.Max(dataTable.Rows.Count, grid.MinRows); } } HSSFSheet hssfsheet = sheetfill as HSSFSheet; foreach (CellValueRecordInterface recInferface in hssfsheet.Sheet.RowsAggregate.GetValueRecords()) { if (recInferface is FormulaRecordAggregate) { FormulaRecord formulaRecord = ((FormulaRecordAggregate)recInferface).FormulaRecord; Ptg[] ptgs = formulaRecord.ParsedExpression; foreach (Ptg ptg in ptgs) { List <int> rowIndexs = new List <int>(); List <int> newRowIndexs = new List <int>(); if (ptg is RefPtgBase) { RefPtgBase refPtg = ptg as RefPtgBase; rowIndexs.Add(refPtg.Row); } else if (ptg is AreaPtgBase) { AreaPtgBase aptg = ptg as AreaPtgBase; rowIndexs.Add(aptg.FirstRow); rowIndexs.Add(aptg.LastRow); } foreach (int rowIndex in rowIndexs) { int newRowIndex = rowIndex; if (formulaRecord.Row < grid.StartRow || formulaRecord.Row > grid.TemplateEndRow) { if (rowIndex == grid.StartRow) { newRowIndex = grid.TemplateEndRow + 1; } if (rowIndex == grid.TemplateEndRow) { newRowIndex = grid.TemplateEndRow + gridTagRowCount; } } newRowIndexs.Add(newRowIndex); } for (int i = 0; i < rowIndexs.Count; i++) { int rowIndex = rowIndexs[i]; int newRowIndex = newRowIndexs[i]; if (newRowIndex != rowIndex) { if (ptg is RefPtgBase) { RefPtgBase refPtg = ptg as RefPtgBase; refPtg.Row = newRowIndex; } else if (ptg is AreaPtgBase) { AreaPtgBase aptg = ptg as AreaPtgBase; if (i == 0) { aptg.FirstRow = newRowIndex; } else { aptg.LastRow = newRowIndex; } } formulaRecord.ParsedExpression = ptgs; } } } } } for (int i = 0; i < book.NumberOfSheets; i++) { HSSFSheet sheet = book.GetSheetAt(i) as HSSFSheet; if (IsSystemSheet(sheet)) { continue; } foreach (RecordBase recbase in sheet.Sheet.Records) { if (recbase is LinkedDataRecord) { LinkedDataRecord link = recbase as LinkedDataRecord; Ptg[] ptgs = ptgs = link.FormulaOfLink; foreach (Ptg ptg in ptgs) { HSSFSheet sheetptg = PtgCollection.GetPtgSheet(sheet, ptg); if (sheetptg == sheetfill) { if (ptg is RefPtgBase) { RefPtgBase refPtg = ptg as RefPtgBase; if (refPtg.Row > grid.TemplateEndRow) { refPtg.Row += gridTagRowCount - (grid.TemplateEndRow - grid.StartRow + 1); link.FormulaOfLink = ptgs; } } if (ptg is AreaPtgBase) { AreaPtgBase areaPtg = ptg as AreaPtgBase; if (areaPtg.FirstRow <= grid.StartRow && areaPtg.LastRow >= grid.TemplateEndRow) { areaPtg.LastRow += gridTagRowCount - (grid.TemplateEndRow - grid.StartRow + 1); link.FormulaOfLink = ptgs; } else if (areaPtg.FirstRow > grid.TemplateEndRow) { areaPtg.FirstRow += gridTagRowCount - (grid.TemplateEndRow - grid.StartRow + 1); areaPtg.LastRow += gridTagRowCount - (grid.TemplateEndRow - grid.StartRow + 1); link.FormulaOfLink = ptgs; } } } } } } } dataTable.Columns.Add("_gridTemplateIndex", typeof(object)); foreach (GridBlockTemplate template in grid.GridBlockTemplates) { if (String.IsNullOrEmpty(template.Condition)) { foreach (DataRow dataRow in dataTable.Rows) { if (Convert.IsDBNull(dataRow["_gridTemplateIndex"])) { dataRow["_gridTemplateIndex"] = template; } } break; } DataView view = new DataView(dataTable); view.RowFilter = template.Condition.Replace("$totalRow", dataTable.Rows.Count.ToString()); DataTable rvTable = view.ToTable(); foreach (DataRow dataRow in rvTable.Rows) { int rowIndex = Convert.ToInt32(dataRow["RowNum"]); DataRow dataRowSrc = dataTable.Rows[rowIndex - 1]; dataRowSrc["_gridTemplateIndex"] = template; } } for (int i = 0; i < gridTagBlockCount; i++) { GridBlockTemplate template; if (i >= dataTable.Rows.Count) { template = grid.GridBlockTemplates[grid.GridBlockTemplates.Count - 1]; } else { template = dataTable.Rows[i]["_gridTemplateIndex"] as GridBlockTemplate; } int startRowIndex = grid.TemplateEndRow + 1 + i * grid.BlockRowCount; for (int j = 0; j < grid.BlockRowCount; j++) { int newRowIndex = startRowIndex + j; HSSFRow newRow = (HSSFRow)sheetfill.GetRow(newRowIndex); if (newRow == null) { newRow = (HSSFRow)sheetfill.CreateRow(newRowIndex); } if (template != null) { HSSFRow srcRow = template.Rows[j]; CopyRow(grid.BlockRowCount, j, srcRow, newRow); } } foreach (CellRangeAddress region in template.MergedRegions) { sheetfill.AddMergedRegion(new CellRangeAddress(startRowIndex + region.FirstRow, startRowIndex + region.FirstRow, region.FirstColumn, region.LastColumn)); } foreach (SeriesTemplate seriesTemplate in template.SeriesTemplates) { seriesTemplate.CloneSeries(sheetfill, i * grid.BlockRowCount - (template.StartRow - grid.StartRow)); } } foreach (GridBlockTemplate template in grid.GridBlockTemplates) { foreach (SeriesTemplate seriesTemplate in template.SeriesTemplates) { seriesTemplate.Chart.RemoveSeries(seriesTemplate.Series); } } } //删除模板 int addedExcelRowCount = 0; if (dataTable != null) { addedExcelRowCount = dataTable.Rows.Count * grid.BlockRowCount; } for (int i = grid.StartRow; i <= grid.TemplateEndRow; i++) { sheetfill.CreateRow(i); } int firstRow = grid.StartRow; int lastRow = grid.TemplateEndRow; ShiftRows(sheetfill, grid.TemplateEndRow + 1, -(grid.TemplateEndRow - grid.StartRow + 1)); //填充数据 if (dataTable != null) { for (int i = 0; i < dataTable.Rows.Count; i++) { DataRow row = dataTable.Rows[i]; foreach (GridCellFill fill in grid.GridCellFills) { Point cellPos = new Point(); cellPos.X = fill.ColumnIndex; cellPos.Y = grid.StartRow + fill.RowOffset + i * grid.BlockRowCount; //获得要填充的cell的行列序号 HSSFRow rowfill = (HSSFRow)sheetfill.GetRow(cellPos.Y); if (rowfill == null) { continue; } //获得要填充的cell HSSFCell cellfill = (HSSFCell)rowfill.GetCell(cellPos.X); if (cellfill == null) { cellfill = (HSSFCell)rowfill.CreateCell(cellPos.X); } //获得填充值 object valuefill = null; if (dataTable.Columns.Contains(fill.FillColumnName)) { valuefill = row[fill.FillColumnName]; } //执行填充 DoRender(fill.RenderFunction, cellfill, fill.CellVarName, dataset, valuefill, fill.EmptyText); } } for (int i = dataTable.Rows.Count; i < grid.MinRows; i++) { foreach (GridCellFill fill in grid.GridCellFills) { Point cellPos = new Point(); cellPos.X = fill.ColumnIndex; cellPos.Y = grid.StartRow + fill.RowOffset + i * grid.BlockRowCount; //获得要填充的cell的行列序号 HSSFRow rowfill = (HSSFRow)sheetfill.GetRow(cellPos.Y); if (rowfill == null) { continue; } //获得要填充的cell HSSFCell cellfill = (HSSFCell)rowfill.GetCell(cellPos.X); if (cellfill == null) { cellfill = (HSSFCell)rowfill.CreateCell(cellPos.X); } //获得填充值 cellfill.SetCellValue(""); } } MergeCells(sheetfill, grid, grid.Groups, 0, grid.StartRow, gridTagRowCount); } } }