//[Obsolete] //public static void SetBorderLeft(LF.Utils.NPOI.SS.UserModel.CellBorderType border, Region region, HSSFSheet sheet, // HSSFWorkbook workbook) //{ // SetBorderLeft(border, toCRA(region), sheet, workbook); //} /// <summary> /// Sets the left border for a region of cells by manipulating the cell style /// of the individual cells on the left /// </summary> /// <param name="border">The new border</param> /// <param name="region">The region that should have the border</param> /// <param name="sheet">The sheet that the region is on.</param> /// <param name="workbook">The workbook that the region is on.</param> public static void SetBorderLeft(LF.Utils.NPOI.SS.UserModel.BorderStyle border, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook workbook) { int rowStart = region.FirstRow; int rowEnd = region.LastRow; int column = region.FirstColumn; CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.BORDER_LEFT, (int)border); for (int i = rowStart; i <= rowEnd; i++) { cps.SetProperty(HSSFCellUtil.GetRow(i, sheet), column); } }
private void CreateFilterModeRecord(HSSFSheet sheet,int insertPos) { //look for the FilterModeRecord LF.Utils.NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(FilterModeRecord.sid); // this local variable hides the class one: FilterModeRecord filtermode; //if not found, add a new one if (record == null) { filtermode = new FilterModeRecord(); sheet.Sheet.Records.Insert(insertPos, filtermode); } }
public CellEvaluationFrame(HSSFWorkbook workbook, HSSFSheet sheet, int srcRowNum, int srcColNum) { if (workbook == null) { throw new ArgumentException("workbook must not be null"); } if (sheet == null) { throw new ArgumentException("sheet must not be null"); } _workbook = workbook; _sheet = sheet; _srcRowNum = srcRowNum; _srcColNum = srcColNum; }
private void CreateAutoFilterInfoRecord(HSSFSheet sheet, int insertPos, Area3DPtg ptg) { //look for the AutoFilterInfo Record LF.Utils.NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(AutoFilterInfoRecord.sid); AutoFilterInfoRecord info; if (record == null) { info = new AutoFilterInfoRecord(); sheet.Sheet.Records.Insert(insertPos, info); } else { info = record as AutoFilterInfoRecord; } info.NumEntries = (short)(ptg.LastColumn - ptg.FirstColumn + 1); }
public HSSFAutoFilter(HSSFSheet sheet) { _sheet = sheet; }
/// <summary> /// Creates new Cell - Should only be called by HSSFRow. This Creates a cell /// from scratch. /// When the cell is initially Created it is Set to CellType.BLANK. Cell types /// can be Changed/overwritten by calling SetCellValue with the appropriate /// type as a parameter although conversions from one type to another may be /// prohibited. /// </summary> /// <param name="book">Workbook record of the workbook containing this cell</param> /// <param name="sheet">Sheet record of the sheet containing this cell</param> /// <param name="row">the row of this cell</param> /// <param name="col">the column for this cell</param> public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, int row, short col) : this(book, sheet, row, col, CellType.BLANK) { }
//[Obsolete] //public static void SetRightBorderColor(short color, Region region, HSSFSheet sheet, // HSSFWorkbook workbook) //{ // SetRightBorderColor(color, toCRA(region), sheet, workbook); //} /// <summary> /// Sets the rightBorderColor attribute of the HSSFRegionUtil object /// </summary> /// <param name="color">The color of the border</param> /// <param name="region">The region that should have the border</param> /// <param name="sheet">The workbook that the region is on.</param> /// <param name="workbook">The sheet that the region is on.</param> public static void SetRightBorderColor(int color, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook workbook) { int rowStart = region.FirstRow; int rowEnd = region.LastRow; int column = region.LastColumn; CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.RIGHT_BORDER_COLOR, color); for (int i = rowStart; i <= rowEnd; i++) { cps.SetProperty(HSSFCellUtil.GetRow(i, sheet), column); } }
/// <summary> /// Creates an HSSFRow from a low level RowRecord object. Only HSSFSheet should do /// this. HSSFSheet uses this when an existing file is Read in. /// </summary> /// <param name="book">low-level Workbook object containing the sheet that Contains this row</param> /// <param name="sheet"> low-level Sheet object that Contains this Row</param> /// <param name="record">the low level api object this row should represent</param> ///<see cref="LF.Utils.NPOI.HSSF.UserModel.HSSFSheet.CreateRow(int)"/> public HSSFRow(HSSFWorkbook book, HSSFSheet sheet, RowRecord record) { this.book = book; this.sheet = sheet; row = record; RowNum=(record.RowNumber); // Don't trust colIx boundaries as read by other apps // set the RowRecord empty for the moment record.SetEmpty(); }
public DVRecord CreateDVRecord(HSSFSheet sheet) { LF.Utils.NPOI.HSSF.UserModel.DVConstraint.FormulaPair fp = _constraint.CreateFormulas(sheet); return new DVRecord(_constraint.GetValidationType(), _constraint.Operator, _errorStyle, _emptyCellAllowed, SuppressDropDownArrow, _constraint.GetValidationType() == ValidationType.LIST && _constraint.ExplicitListValues != null, _ShowPromptBox, _prompt_title, _prompt_text, _ShowErrorBox, _error_title, _error_text, fp.Formula1, fp.Formula2, _regions); }
/** * @return The Parsed token array representing the formula or value specified. * Empty array if both formula and value are <code>null</code> */ private static Ptg[] ConvertDoubleFormula(String formula, Double value, HSSFSheet sheet) { if (formula == null) { if (double.IsNaN(value)) { return Ptg.EMPTY_PTG_ARRAY; } return new Ptg[] { new NumberPtg(value), }; } if (!double.IsNaN(value)) { throw new InvalidOperationException("Both formula and value cannot be present"); } IWorkbook wb = sheet.Workbook; return HSSFFormulaParser.Parse(formula, (HSSFWorkbook)wb, FormulaType.CELL, wb.GetSheetIndex(sheet)); }
/// <summary> /// Get a row from the spreadsheet, and Create it if it doesn't exist. /// </summary> /// <param name="rowCounter">The 0 based row number</param> /// <param name="sheet">The sheet that the row is part of.</param> /// <returns>The row indicated by the rowCounter</returns> public static LF.Utils.NPOI.SS.UserModel.IRow GetRow(int rowCounter, HSSFSheet sheet) { LF.Utils.NPOI.SS.UserModel.IRow row = sheet.GetRow(rowCounter); if (row == null) { row = sheet.CreateRow(rowCounter); } return row; }
/// <summary> /// Creates the patriarch. /// </summary> /// <param name="sheet">the sheet this patriarch is stored in.</param> /// <param name="boundAggregate">The bound aggregate.</param> public HSSFPatriarch(HSSFSheet sheet, EscherAggregate boundAggregate) { this._boundAggregate = boundAggregate; this._sheet = sheet; }
/// <summary> /// Create an HSSFSheet for this HSSFWorkbook, Adds it to the sheets and /// returns the high level representation. Use this to Create new sheets. /// </summary> /// <param name="sheetname">sheetname to set for the sheet.</param> /// <returns>HSSFSheet representing the new sheet.</returns> public ISheet CreateSheet(String sheetname) { if (sheetname == null) { throw new ArgumentException("sheetName must not be null"); } if (workbook.ContainsSheetName(sheetname, _sheets.Count)) throw new ArgumentException("The workbook already contains a sheet of this name"); HSSFSheet sheet = new HSSFSheet(this); workbook.SetSheetName(_sheets.Count, sheetname); _sheets.Add(sheet); bool isOnlySheet = _sheets.Count == 1; sheet.IsSelected = isOnlySheet; sheet.IsActive = isOnlySheet; return sheet; }
/// <summary> /// Create an HSSFSheet for this HSSFWorkbook, Adds it to the sheets and returns /// the high level representation. Use this to Create new sheets. /// </summary> /// <returns>HSSFSheet representing the new sheet.</returns> public ISheet CreateSheet() { HSSFSheet sheet = new HSSFSheet(this); _sheets.Add(sheet); workbook.SetSheetName(_sheets.Count - 1, "Sheet" + (_sheets.Count - 1)); bool IsOnlySheet = _sheets.Count == 1; sheet.IsSelected = (IsOnlySheet); sheet.IsActive = (IsOnlySheet); return sheet; }
private void RemoveFilterModeRecord(HSSFSheet sheet) { if(filtermode!=null) sheet.Sheet.Records.Remove(filtermode); }
/** * @return both Parsed formulas (for expression 1 and 2). */ /* package */ public FormulaPair CreateFormulas(HSSFSheet sheet) { Ptg[] formula1; Ptg[] formula2; if (IsListValidationType) { formula1 = CreateListFormula(sheet); formula2 = Ptg.EMPTY_PTG_ARRAY; } else { formula1 = ConvertDoubleFormula(_formula1, _value1, sheet); formula2 = ConvertDoubleFormula(_formula2, _value2, sheet); } return new FormulaPair(formula1, formula2); }
private Ptg[] CreateListFormula(HSSFSheet sheet) { if (_explicitListValues == null) { IWorkbook wb = sheet.Workbook; // formula is Parsed with slightly different RVA rules: (root node type must be 'reference') return HSSFFormulaParser.Parse(_formula1, (HSSFWorkbook)wb, FormulaType.DATAVALIDATION_LIST, wb.GetSheetIndex(sheet)); // To do: Excel places restrictions on the available operations within a list formula. // Some things like union and intersection are not allowed. } // explicit list was provided StringBuilder sb = new StringBuilder(_explicitListValues.Length * 16); for (int i = 0; i < _explicitListValues.Length; i++) { if (i > 0) { sb.Append('\0'); // list delimiter is the nul char } sb.Append(_explicitListValues[i]); } return new Ptg[] { new StringPtg(sb.ToString()), }; }
/** * Returns all the charts for the given sheet. * * NOTE: You won't be able to do very much with * these charts yet, as this is very limited support */ public static HSSFChart[] GetSheetCharts(HSSFSheet sheet) { List<HSSFChart> charts = new List<HSSFChart>(); HSSFChart lastChart = null; HSSFSeries lastSeries = null; // Find records of interest IList records = sheet.Sheet.Records; foreach (RecordBase r in records) { if (r is ChartRecord) { lastSeries = null; lastChart = new HSSFChart(sheet, (ChartRecord)r); charts.Add(lastChart); } else if (r is LegendRecord) { lastChart.legendRecord = (LegendRecord)r; } else if (r is SeriesRecord) { HSSFSeries series = new HSSFSeries((SeriesRecord)r); lastChart.series.Add(series); lastSeries = series; } else if (r is ChartTitleFormatRecord) { lastChart.chartTitleFormat = (ChartTitleFormatRecord)r; } else if (r is SeriesTextRecord) { // Applies to a series, unless we've seen // a legend already SeriesTextRecord str = (SeriesTextRecord)r; if (lastChart.legendRecord == null && lastChart.series.Count > 0) { HSSFSeries series = (HSSFSeries) lastChart.series[lastChart.series.Count - 1]; series.seriesTitleText = str; } else { lastChart.chartTitleText = str; } } else if (r is LinkedDataRecord) { LinkedDataRecord linkedDataRecord = (LinkedDataRecord)r; if (lastSeries != null) { lastSeries.InsertData(linkedDataRecord); } } else if (r is ValueRangeRecord) { lastChart.valueRanges.Add((ValueRangeRecord)r); } else if (r is Record) { if (lastChart != null) { Record record = (Record)r; foreach (int type in Enum.GetValues(typeof(HSSFChartType))) { if (type == 0) { continue; } if (record.Sid == type) { lastChart.type = (HSSFChartType)type; break; } } } } } return (HSSFChart[]) charts.ToArray(); }
/* package */ //public HSSFSheetConditionalFormatting(HSSFWorkbook workbook, InternalSheet sheet) //{ // _workbook = workbook; // _conditionalFormattingTable = sheet.ConditionalFormattingTable; //} public HSSFSheetConditionalFormatting(HSSFSheet sheet) { _sheet = sheet; _conditionalFormattingTable = sheet.Sheet.ConditionalFormattingTable; }
private HSSFChart(HSSFSheet sheet, ChartRecord chartRecord) { this.chartRecord = chartRecord; this.sheet = sheet; }
/// <summary> /// Creates new HSSFRow from scratch. Only HSSFSheet should do this. /// </summary> /// <param name="book">low-level Workbook object containing the sheet that Contains this row</param> /// <param name="sheet">low-level Sheet object that Contains this Row</param> /// <param name="rowNum">the row number of this row (0 based)</param> ///<see cref="LF.Utils.NPOI.HSSF.UserModel.HSSFSheet.CreateRow(int)"/> public HSSFRow(HSSFWorkbook book, HSSFSheet sheet, int rowNum):this(book, sheet, new RowRecord(rowNum)) { }
/** * Creates a bar chart. API needs some work. :) * * NOTE: Does not yet work... checking it in just so others * can take a look. */ public void CreateBarChart(HSSFWorkbook workbook, HSSFSheet sheet) { List<Record> records = new List<Record>(); records.Add(CreateMSDrawingObjectRecord()); records.Add(CreateOBJRecord()); records.Add(CreateBOFRecord()); records.Add(new HeaderRecord(string.Empty)); records.Add(new FooterRecord(string.Empty)); records.Add(CreateHCenterRecord()); records.Add(CreateVCenterRecord()); records.Add(CreatePrintSetupRecord()); // unknown 33 records.Add(CreateFontBasisRecord1()); records.Add(CreateFontBasisRecord2()); records.Add(new ProtectRecord(false)); records.Add(CreateUnitsRecord()); records.Add(CreateChartRecord(0, 0, 30434904, 19031616)); records.Add(CreateBeginRecord()); records.Add(CreateSCLRecord((short)1, (short)1)); records.Add(CreatePlotGrowthRecord(65536, 65536)); records.Add(CreateFrameRecord1()); records.Add(CreateBeginRecord()); records.Add(CreateLineFormatRecord(true)); records.Add(CreateAreaFormatRecord1()); records.Add(CreateEndRecord()); records.Add(CreateSeriesRecord()); records.Add(CreateBeginRecord()); records.Add(CreateTitleLinkedDataRecord()); records.Add(CreateValuesLinkedDataRecord()); records.Add(CreateCategoriesLinkedDataRecord()); records.Add(CreateDataFormatRecord()); // records.Add(CreateBeginRecord()); // unknown // records.Add(CreateEndRecord()); records.Add(CreateSeriesToChartGroupRecord()); records.Add(CreateEndRecord()); records.Add(CreateSheetPropsRecord()); records.Add(CreateDefaultTextRecord((short)CategoryDataType.ALL_TEXT_CHARACTERISTIC0)); records.Add(CreateAllTextRecord()); records.Add(CreateBeginRecord()); // unknown records.Add(CreateFontIndexRecord(5)); records.Add(CreateDirectLinkRecord()); records.Add(CreateEndRecord()); records.Add(CreateDefaultTextRecord((short)3)); // eek, undocumented text type records.Add(CreateUnknownTextRecord()); records.Add(CreateBeginRecord()); records.Add(CreateFontIndexRecord((short)6)); records.Add(CreateDirectLinkRecord()); records.Add(CreateEndRecord()); records.Add(CreateAxisUsedRecord((short)1)); CreateAxisRecords(records); records.Add(CreateEndRecord()); records.Add(CreateDimensionsRecord()); records.Add(CreateSeriesIndexRecord(2)); records.Add(CreateSeriesIndexRecord(1)); records.Add(CreateSeriesIndexRecord(3)); records.Add(EOFRecord.instance); sheet.InsertChartRecords(records); workbook.InsertChartRecord(); }
public HSSFEvaluationSheet(HSSFSheet hs) { _hs = hs; }
/** * Notifies this evaluation tracker that evaluation of the specified cell Is * about to start.<br/> * * In the case of a <c>true</c> return code, the caller should * continue evaluation of the specified cell, and also be sure to call * <c>endEvaluate()</c> when complete.<br/> * * In the case of a <c>false</c> return code, the caller should * return an evaluation result of * <c>ErrorEval.CIRCULAR_REF_ERROR</c>, and not call <c>endEvaluate()</c>. * <br/> * @return <c>true</c> if the specified cell has not been visited yet in the current * evaluation. <c>false</c> if the specified cell Is alReady being evaluated. */ public bool StartEvaluate(HSSFWorkbook workbook, HSSFSheet sheet, int srcRowNum, int srcColNum) { CellEvaluationFrame cef = new CellEvaluationFrame(workbook, sheet, srcRowNum, srcColNum); if (_evaluationFrames.Contains(cef)) { return false; } _evaluationFrames.Add(cef); return true; }
/// <summary> /// Sets the topBorderColor attribute of the HSSFRegionUtil object /// </summary> /// <param name="color">The color of the border</param> /// <param name="region">The region that should have the border</param> /// <param name="sheet">The sheet that the region is on.</param> /// <param name="workbook">The workbook that the region is on.</param> public static void SetTopBorderColor(int color, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook workbook) { int colStart = region.FirstColumn; int colEnd = region.LastColumn; int rowIndex = region.FirstRow; CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.TOP_BORDER_COLOR, color); LF.Utils.NPOI.SS.UserModel.IRow row = HSSFCellUtil.GetRow(rowIndex, sheet); for (int i = colStart; i <= colEnd; i++) { cps.SetProperty(row, i); } }
/** * Notifies this evaluation tracker that the evaluation of the specified * cell Is complete. <p/> * * Every successful call to <c>startEvaluate</c> must be followed by a * call to <c>endEvaluate</c> (recommended in a finally block) to enable * proper tracking of which cells are being evaluated at any point in time.<p/> * * Assuming a well behaved client, parameters to this method would not be * required. However, they have been included to assert correct behaviour, * and form more meaningful error messages. */ public void EndEvaluate(HSSFWorkbook workbook, HSSFSheet sheet, int srcRowNum, int srcColNum) { int nFrames = _evaluationFrames.Count; if (nFrames < 1) { throw new InvalidOperationException("Call to endEvaluate without matching call to startEvaluate"); } nFrames--; CellEvaluationFrame cefExpected = (CellEvaluationFrame)_evaluationFrames[nFrames]; CellEvaluationFrame cefActual = new CellEvaluationFrame(workbook, sheet, srcRowNum, srcColNum); if (!cefActual.Equals(cefExpected)) { throw new Exception("Wrong cell specified. " + "Corresponding startEvaluate() call was for cell {" + cefExpected.FormatAsString() + "} this endEvaluate() call Is for cell {" + cefActual.FormatAsString() + "}"); } // else - no problems so pop current frame _evaluationFrames.Remove(nFrames); }
/// <summary> /// Creates an Cell from a CellValueRecordInterface. HSSFSheet uses this when /// reading in cells from an existing sheet. /// </summary> /// <param name="book">Workbook record of the workbook containing this cell</param> /// <param name="sheet">Sheet record of the sheet containing this cell</param> /// <param name="cval">the Cell Value Record we wish to represent</param> public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, CellValueRecordInterface cval) { record = cval; cellType = DetermineType(cval); stringValue = null; this.book = book; this.sheet = sheet; switch (cellType) { case CellType.STRING: stringValue = new HSSFRichTextString(book.Workbook, (LabelSSTRecord)cval); break; case CellType.BLANK: break; case CellType.FORMULA: stringValue = new HSSFRichTextString(((FormulaRecordAggregate)cval).StringValue); break; } ExtendedFormatRecord xf = book.Workbook.GetExFormatAt(cval.XFIndex); CellStyle = new HSSFCellStyle((short)cval.XFIndex, xf, book); }
public HSSFDataValidationHelper(HSSFSheet sheet) : base() { this.sheet = sheet; }
/// <summary> /// Creates new Cell - Should only be called by HSSFRow. This Creates a cell /// from scratch. /// </summary> /// <param name="book">Workbook record of the workbook containing this cell</param> /// <param name="sheet">Sheet record of the sheet containing this cell</param> /// <param name="row">the row of this cell</param> /// <param name="col">the column for this cell</param> /// <param name="type">CellType.NUMERIC, CellType.STRING, CellType.FORMULA, CellType.BLANK, /// CellType.BOOLEAN, CellType.ERROR</param> public HSSFCell(HSSFWorkbook book, HSSFSheet sheet, int row, short col, CellType type) { CheckBounds(col); cellType = CellType.Unknown; // Force 'SetCellType' to Create a first Record stringValue = null; this.book = book; this.sheet = sheet; short xfindex = sheet.Sheet.GetXFIndexForColAt(col); SetCellType(type, false, row, col, xfindex); }
/** * TODO - parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea * this call will produce the wrong results if the formula contains any cell references * One approach might be to apply the inverse of SharedFormulaRecord.convertSharedFormulas(Stack, int, int) * Note - two extra parameters (rowIx & colIx) will be required. They probably come from one of the Region objects. * * @return <code>null</code> if <c>formula</c> was null. */ private static Ptg[] ParseFormula(String formula, HSSFSheet sheet) { if (formula == null) { return null; } int sheetIndex = sheet.Workbook.GetSheetIndex(sheet); return HSSFFormulaParser.Parse(formula, (HSSFWorkbook)sheet.Workbook, FormulaType.CELL, sheetIndex); }