/* ==================================================================== Licensed to the Apache Software Foundation (ASF) Under one or more contributor license agreements. See the NOTICE file distributed with this work for Additional information regarding copyright ownership. The ASF licenses this file to You Under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed Under the License is distributed on an "AS Is" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations Under the License. ==================================================================== */ namespace HH.WMS.Utils.NPOI.HSSF.UserModel { using System; using System.Collections; using System.Collections.Generic; using System.Windows.Forms; using HH.WMS.Utils.NPOI.DDF; using HH.WMS.Utils.NPOI.HSSF.Model; using HH.WMS.Utils.NPOI.HSSF.Record; using HH.WMS.Utils.NPOI.HSSF.Record.Aggregates; using HH.WMS.Utils.NPOI.HSSF.Record.AutoFilter; using HH.WMS.Utils.NPOI.HSSF.Util; using HH.WMS.Utils.NPOI.SS; using HH.WMS.Utils.NPOI.SS.Formula; using HH.WMS.Utils.NPOI.SS.Formula.PTG; using HH.WMS.Utils.NPOI.SS.UserModel; using HH.WMS.Utils.NPOI.SS.Util; using System.Drawing; using System.Windows.Forms.VisualStyles; using System.Drawing.Drawing2D; using System.Globalization; /// /// High level representation of a worksheet. /// /// /// @author Andrew C. Oliver (acoliver at apache dot org) /// @author Glen Stampoultzis (glens at apache.org) /// @author Libin Roman (romal at vistaportal.com) /// @author Shawn Laubach (slaubach at apache dot org) (Just a little) /// @author Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a little, too) /// @author Yegor Kozlov (yegor at apache.org) (Autosizing columns) /// [Serializable] public class HSSFSheet : HH.WMS.Utils.NPOI.SS.UserModel.ISheet { /** * Used for compile-time optimization. This is the initial size for the collection of * rows. It is currently Set to 20. If you generate larger sheets you may benefit * by Setting this to a higher number and recompiling a custom edition of HSSFSheet. */ public static int INITIAL_CAPACITY = 20; /** * reference to the low level Sheet object */ private InternalSheet _sheet; private Dictionary rows; public InternalWorkbook book; protected HSSFWorkbook _workbook; private int firstrow; private int lastrow; //private static POILogger log = POILogFactory.GetLogger(typeof(HSSFSheet)); /// /// Creates new HSSFSheet - called by HSSFWorkbook to create a _sheet from /// scratch. You should not be calling this from application code (its protected anyhow). /// /// The HSSF Workbook object associated with the _sheet. /// public HSSFSheet(HSSFWorkbook workbook) { _sheet = InternalSheet.CreateSheet(); rows = new Dictionary(); this._workbook = workbook; this.book = workbook.Workbook; } /// /// Creates an HSSFSheet representing the given Sheet object. Should only be /// called by HSSFWorkbook when reading in an exisiting file. /// /// The HSSF Workbook object associated with the _sheet. /// lowlevel Sheet object this _sheet will represent /// public HSSFSheet(HSSFWorkbook workbook, InternalSheet sheet) { this._sheet = sheet; rows = new Dictionary(); this._workbook = workbook; this.book = _workbook.Workbook; SetPropertiesFromSheet(_sheet); } /// /// Clones the _sheet. /// /// The _workbook. /// the cloned sheet public HSSFSheet CloneSheet(HSSFWorkbook workbook) { return new HSSFSheet(workbook, _sheet.CloneSheet()); } /// /// used internally to Set the properties given a Sheet object /// /// The _sheet. private void SetPropertiesFromSheet(InternalSheet sheet) { RowRecord row = _sheet.NextRow; bool rowRecordsAlreadyPresent = row != null; while (row != null) { CreateRowFromRecord(row); row = sheet.NextRow; } CellValueRecordInterface[] cvals = sheet.GetValueRecords(); long timestart = DateTime.Now.Millisecond; //if (log.Check(POILogger.DEBUG)) // log.Log(DEBUG, "Time at start of cell creating in HSSF _sheet = ", // timestart); HSSFRow lastrow = null; // Add every cell to its row for (int i = 0; i < cvals.Length; i++) { CellValueRecordInterface cval = cvals[i]; long cellstart = DateTime.Now.Millisecond; HSSFRow hrow = lastrow; if ((lastrow == null) || (lastrow.RowNum != cval.Row)) { hrow = (HSSFRow)GetRow(cval.Row); if (hrow == null) { // Some tools (like Perl module SpReadsheet::WriteExcel - bug 41187) skip the RowRecords // Excel, OpenOffice.org and GoogleDocs are all OK with this, so POI should be too. if (rowRecordsAlreadyPresent) { // if at least one row record is present, all should be present. throw new Exception("Unexpected missing row when some rows already present"); } // Create the row record on the fly now. RowRecord rowRec = new RowRecord(cval.Row); _sheet.AddRow(rowRec); hrow = CreateRowFromRecord(rowRec); } } if (hrow != null) { lastrow = hrow; if (cval is Record) { //if (log.Check(POILogger.DEBUG)) // log.Log(DEBUG, "record id = " + StringUtil.ToHexString(((Record)cval).Sid)); } hrow.CreateCellFromRecord(cval); //if (log.Check(POILogger.DEBUG)) // log.Log(DEBUG, "record took ",DateTime.Now.Millisecond - cellstart); } else { cval = null; } } //if (log.Check(POILogger.DEBUG)) // log.Log(DEBUG, "total _sheet cell creation took ", // DateTime.Now.Millisecond - timestart); } /** * Gets the flag indicating whether the window should show 0 (zero) in cells containing zero value. * When false, cells with zero value appear blank instead of showing the number zero. * In Excel 2003 this option can be changed in the Options dialog on the View tab. * @return whether all zero values on the worksheet are displayed */ public bool DisplayZeros { get { return _sheet.WindowTwo.DisplayZeros; } set { _sheet.WindowTwo.DisplayZeros = value; } } /// /// Create a new row within the _sheet and return the high level representation /// /// The row number. /// /// @see org.apache.poi.hssf.usermodel.HSSFRow /// @see #RemoveRow(HSSFRow) public HH.WMS.Utils.NPOI.SS.UserModel.IRow CreateRow(int rownum) { HSSFRow row = new HSSFRow(_workbook, this, rownum); AddRow(row, true); return row; } /// /// Used internally to Create a high level Row object from a low level row object. /// USed when Reading an existing file /// /// low level record to represent as a high level Row and Add to _sheet. /// HSSFRow high level representation private HSSFRow CreateRowFromRecord(RowRecord row) { HSSFRow hrow = new HSSFRow(_workbook, this, row); AddRow(hrow, false); return hrow; } /// /// Remove a row from this _sheet. All cells contained in the row are Removed as well /// /// the row to Remove. public void RemoveRow(IRow row) { if (row.Sheet != this) { throw new ArgumentException("Specified row does not belong to this sheet"); } foreach (ICell cell in row) { HSSFCell xcell = (HSSFCell)cell; if (xcell.IsPartOfArrayFormulaGroup) { String msg = "Row[rownum=" + row.RowNum + "] contains cell(s) included in a multi-cell array formula. You cannot change part of an array."; xcell.NotifyArrayFormulaChanging(msg); } } if (rows.Count > 0) { int key = row.RowNum; HSSFRow removedRow = (HSSFRow)rows[key]; rows.Remove(key); if (removedRow != row) { if (removedRow != null) { rows[key] = removedRow; } throw new InvalidOperationException("Specified row does not belong to this _sheet"); } if (row.RowNum == LastRowNum) { lastrow = FindLastRow(lastrow); } if (row.RowNum == FirstRowNum) { firstrow = FindFirstRow(firstrow); } CellValueRecordInterface[] cellvaluerecods = _sheet.GetValueRecords(); for (int i = 0; i < cellvaluerecods.Length; i++) { if (cellvaluerecods[i].Row == key) { _sheet.RemoveValueRecord(key, cellvaluerecods[i]); } } _sheet.RemoveRow(((HSSFRow)row).RowRecord); } } /// /// used internally to refresh the "last row" when the last row is Removed. /// /// The last row. /// private int FindLastRow(int lastrow) { if (lastrow < 1) { return 0; } int rownum = lastrow - 1; HH.WMS.Utils.NPOI.SS.UserModel.IRow r = GetRow(rownum); while (r == null && rownum > 0) { r = GetRow(--rownum); } if (r == null) return 0; return rownum; } /// /// used internally to refresh the "first row" when the first row is Removed. /// /// The first row. /// private int FindFirstRow(int firstrow) { int rownum = firstrow + 1; HH.WMS.Utils.NPOI.SS.UserModel.IRow r = GetRow(rownum); while (r == null && rownum <= LastRowNum) { r = GetRow(++rownum); } if (rownum > LastRowNum) return 0; return rownum; } /** * Add a row to the _sheet * * @param AddLow whether to Add the row to the low level model - false if its already there */ private void AddRow(HSSFRow row, bool addLow) { rows[row.RowNum] = row; if (addLow) { _sheet.AddRow(row.RowRecord); } bool firstRow = rows.Count == 1; if (row.RowNum > LastRowNum || firstRow) { lastrow = row.RowNum; } if (row.RowNum < FirstRowNum || firstRow) { firstrow = row.RowNum; } } /// /// Returns the HSSFCellStyle that applies to the given /// (0 based) column, or null if no style has been /// set for that column /// /// The column. /// public HH.WMS.Utils.NPOI.SS.UserModel.ICellStyle GetColumnStyle(int column) { short styleIndex = _sheet.GetXFIndexForColAt((short)column); if (styleIndex == 0xf) { // None set return null; } ExtendedFormatRecord xf = book.GetExFormatAt(styleIndex); return new HSSFCellStyle(styleIndex, xf, book); } /// /// Returns the logical row (not physical) 0-based. If you ask for a row that is not /// defined you get a null. This is to say row 4 represents the fifth row on a _sheet. /// /// Index of the row to get. /// the row number or null if its not defined on the _sheet public HH.WMS.Utils.NPOI.SS.UserModel.IRow GetRow(int rowIndex) { if (!rows.ContainsKey(rowIndex)) return null; return (HSSFRow)rows[rowIndex]; } /// /// Returns the number of phsyically defined rows (NOT the number of rows in the _sheet) /// /// The physical number of rows. public int PhysicalNumberOfRows { get { return rows.Count; } } /// /// Gets the first row on the _sheet /// /// the number of the first logical row on the _sheet public int FirstRowNum { get { return firstrow; } } /// /// Gets the last row on the _sheet /// /// last row contained n this _sheet. public int LastRowNum { get { return lastrow; } } /// /// Creates a data validation object /// /// The data validation object settings public void AddValidationData(IDataValidation dataValidation) { if (dataValidation == null) { throw new ArgumentException("objValidation must not be null"); } HSSFDataValidation hssfDataValidation = (HSSFDataValidation)dataValidation; DataValidityTable dvt = _sheet.GetOrCreateDataValidityTable(); DVRecord dvRecord = hssfDataValidation.CreateDVRecord(this); dvt.AddDataValidation(dvRecord); } /// /// Get the visibility state for a given column.F:\Gloria\Ñо¿\Îļþ¸ñʽ\NPOI\src\NPOI\HSSF\Util\HSSFDataValidation.cs /// /// the column to Get (0-based). /// the visiblity state of the column. public void SetColumnHidden(int column, bool hidden) { _sheet.SetColumnHidden(column, hidden); } /// /// Get the hidden state for a given column. /// /// the column to Set (0-based) /// the visiblity state of the column; /// public bool IsColumnHidden(int column) { return _sheet.IsColumnHidden(column); } /// /// Set the width (in Units of 1/256th of a Char width) /// /// the column to Set (0-based) /// the width in Units of 1/256th of a Char width public void SetColumnWidth(int column, int width) { _sheet.SetColumnWidth(column, width); } /// /// Get the width (in Units of 1/256th of a Char width ) /// /// the column to Set (0-based) /// the width in Units of 1/256th of a Char width public int GetColumnWidth(int column) { return _sheet.GetColumnWidth(column); } /// /// Gets or sets the default width of the column. /// /// The default width of the column. public int DefaultColumnWidth { get { return _sheet.DefaultColumnWidth; } set { _sheet.DefaultColumnWidth = value; } } /// /// Get the default row height for the _sheet (if the rows do not define their own height) in /// twips (1/20 of a point) /// /// The default height of the row. public int DefaultRowHeight { get { return _sheet.DefaultRowHeight; } set { _sheet.DefaultRowHeight = (short)value; } } /// /// Get the default row height for the _sheet (if the rows do not define their own height) in /// points. /// /// The default row height in points. public float DefaultRowHeightInPoints { get { return (float)(_sheet.DefaultRowHeight / 20.0); } set { _sheet.DefaultRowHeight = ((short)(value * 20.0)); } } /// /// Get whether gridlines are printed. /// /// /// true if printed; otherwise, false. /// [Obsolete("Please use IsPrintGridlines instead")] public bool IsGridsPrinted { get { return _sheet.IsGridsPrinted; } set { _sheet.IsGridsPrinted = (value); } } /// /// Adds a merged region of cells (hence those cells form one) /// /// The region (rowfrom/colfrom-rowto/colto) to merge. /// index of this region [Obsolete] public int AddMergedRegion(HH.WMS.Utils.NPOI.SS.Util.Region region) { return _sheet.AddMergedRegion(region.RowFrom, region.ColumnFrom, region.RowTo, region.ColumnTo); } /// /// adds a merged region of cells (hence those cells form one) /// /// region (rowfrom/colfrom-rowto/colto) to merge /// index of this region public int AddMergedRegion(HH.WMS.Utils.NPOI.SS.Util.CellRangeAddress region) { region.Validate(SpreadsheetVersion.EXCEL97); // throw IllegalStateException if the argument CellRangeAddress intersects with // a multi-cell array formula defined in this sheet ValidateArrayFormulas(region); return _sheet.AddMergedRegion(region.FirstRow, region.FirstColumn, region.LastRow, region.LastColumn); } private void ValidateArrayFormulas(CellRangeAddress region) { int firstRow = region.FirstRow; int firstColumn = region.FirstColumn; int lastRow = region.LastRow; int lastColumn = region.LastColumn; for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) { for (int colIn = firstColumn; colIn <= lastColumn; colIn++) { HSSFRow row = (HSSFRow)GetRow(rowIn); if (row == null) continue; HSSFCell cell = (HSSFCell)row.GetCell(colIn); if (cell == null) continue; if (cell.IsPartOfArrayFormulaGroup) { CellRangeAddress arrayRange = cell.GetArrayFormulaRange(); if (arrayRange.NumberOfCells > 1 && (arrayRange.IsInRange(region.FirstRow, region.FirstColumn) || arrayRange.IsInRange(region.FirstRow, region.FirstColumn))) { String msg = "The range " + region.FormatAsString() + " intersects with a multi-cell array formula. " + "You cannot merge cells of an array."; throw new InvalidOperationException(msg); } } } } } /// /// Whether a record must be Inserted or not at generation to indicate that /// formula must be recalculated when _workbook is opened. /// /// /// true if [force formula recalculation]; otherwise, false. /// /// @return true if an Uncalced record must be Inserted or not at generation public bool ForceFormulaRecalculation { get { return _sheet.IsUncalced; } set { _sheet.IsUncalced = (value); } } /// /// Determine whether printed output for this _sheet will be vertically centered. /// /// true if [vertically center]; otherwise, false. public bool VerticallyCenter { get { return _sheet.PageSettings.VCenter.VCenter; } set { _sheet.PageSettings.VCenter.VCenter = value; } } /// /// Determine whether printed output for this _sheet will be horizontally centered. /// /// true if [horizontally center]; otherwise, false. public bool HorizontallyCenter { get { return _sheet.PageSettings.HCenter.HCenter; } set { _sheet.PageSettings.HCenter.HCenter = (value); } } /// /// Removes a merged region of cells (hence letting them free) /// /// index of the region to Unmerge public void RemoveMergedRegion(int index) { _sheet.RemoveMergedRegion(index); } /// /// returns the number of merged regions /// /// The number of merged regions public int NumMergedRegions { get { return _sheet.NumMergedRegions; } } ///// ///// Gets the region at a particular index ///// ///// of the region to fetch ///// the merged region (simple eh?) //public HH.WMS.Utils.NPOI.SS.Util.Region GetMergedRegionAt(int index) //{ // HH.WMS.Utils.NPOI.SS.Util.CellRangeAddress cra = GetMergedRegion(index); // return new HH.WMS.Utils.NPOI.SS.Util.Region(cra.FirstRow, (short)cra.FirstColumn, // cra.LastRow, (short)cra.LastColumn); //} /// /// Gets the row enumerator. /// /// /// an iterator of the PHYSICAL rows. Meaning the 3rd element may not /// be the third row if say for instance the second row is undefined. /// Call on each row /// if you care which one it is. /// public IEnumerator GetRowEnumerator() { return rows.Values.GetEnumerator(); } /// /// Alias for GetRowEnumerator() to allow foreach loops. /// /// /// an iterator of the PHYSICAL rows. Meaning the 3rd element may not /// be the third row if say for instance the second row is undefined. /// Call on each row /// if you care which one it is. /// public IEnumerator GetEnumerator() { return GetRowEnumerator(); } /// /// used internally in the API to Get the low level Sheet record represented by this /// Object. /// /// low level representation of this HSSFSheet. public InternalSheet Sheet { get { return _sheet; } } /// /// Sets the active cell. /// /// The row. /// The column. public void SetActiveCell(int row, int column) { this._sheet.SetActiveCellRange(row, row, column, column); } /// /// Sets the active cell range. /// /// The first row. /// The last row. /// The first column. /// The last column. public void SetActiveCellRange(int firstRow, int lastRow, int firstColumn, int lastColumn) { this._sheet.SetActiveCellRange(firstRow, lastRow, firstColumn, lastColumn); } /// /// Sets the active cell range. /// /// The cellranges. /// The index of the active range. /// The active row in the active range /// The active column in the active range public void SetActiveCellRange(List cellranges, int activeRange, int activeRow, int activeColumn) { this._sheet.SetActiveCellRange(cellranges, activeRange, activeRow, activeColumn); } /// /// Gets or sets whether alternate expression evaluation is on /// /// /// true if [alternative expression]; otherwise, false. /// public bool AlternativeExpression { get { return ((WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid)) .AlternateExpression; } set { WSBoolRecord record = (WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid); record.AlternateExpression = value; } } /// /// whether alternative formula entry is on /// /// true alternative formulas or not; otherwise, false. public bool AlternativeFormula { get { return ((WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid)) .AlternateFormula; } set { WSBoolRecord record = (WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid); record.AlternateFormula = value; } } /// /// show automatic page breaks or not /// /// whether to show auto page breaks public bool Autobreaks { get { return ((WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid)) .Autobreaks; } set { WSBoolRecord record = (WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid); record.Autobreaks = value; } } /// /// Gets or sets a value indicating whether _sheet is a dialog _sheet /// /// true if is dialog; otherwise, false. public bool Dialog { get { return ((WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid)) .Dialog; } set { WSBoolRecord record = (WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid); record.Dialog = (value); } } /// /// Gets or sets a value indicating whether to Display the guts or not. /// /// true if guts or no guts (or glory); otherwise, false. public bool DisplayGuts { get { return ((WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid)) .DisplayGuts; } set { WSBoolRecord record = (WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid); record.DisplayGuts = value; } } /// /// Gets or sets a value indicating whether fit to page option is on /// /// true if [fit to page]; otherwise, false. public bool FitToPage { get { return ((WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid)) .FitToPage; } set { WSBoolRecord record = (WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid); record.FitToPage = value; } } /// /// Get if row summaries appear below detail in the outline /// /// true if below or not; otherwise, false. public bool RowSumsBelow { get { return ((WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid)) .RowSumsBelow; } set { WSBoolRecord record = (WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid); record.RowSumsBelow = value; } } /// /// Get if col summaries appear right of the detail in the outline /// /// true right or not; otherwise, false. public bool RowSumsRight { get { return ((WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid)) .RowSumsRight; } set { WSBoolRecord record = (WSBoolRecord)_sheet.FindFirstRecordBySid(WSBoolRecord.sid); record.RowSumsRight = (value); } } /// /// Gets or sets whether gridlines are printed. /// /// /// true Gridlines are printed; otherwise, false. /// public bool IsPrintGridlines { get { return Sheet.PrintGridlines.PrintGridlines; } set { Sheet.PrintGridlines.PrintGridlines = (value); } } /// /// Gets the print setup object. /// /// The user model for the print setup object. public HH.WMS.Utils.NPOI.SS.UserModel.IPrintSetup PrintSetup { get { return new HSSFPrintSetup(this._sheet.PageSettings.PrintSetup); } } /// /// Gets the user model for the document header. /// /// The Document header. public HH.WMS.Utils.NPOI.SS.UserModel.IHeader Header { get { return new HSSFHeader(this._sheet.PageSettings); } } /// /// Gets the user model for the document footer. /// /// The Document footer. public HH.WMS.Utils.NPOI.SS.UserModel.IFooter Footer { get { return new HSSFFooter(this._sheet.PageSettings); } } /// /// Gets or sets whether the worksheet is displayed from right to left instead of from left to right. /// /// true for right to left, false otherwise /// poi bug 47970 public bool IsArabic { get { return _sheet.WindowTwo.Arabic; } set { _sheet.WindowTwo.Arabic = value; } } /// /// Note - this is not the same as whether the _sheet is focused (isActive) /// /// /// true if this _sheet is currently selected; otherwise, false. /// public bool IsSelected { get { return Sheet.GetWindowTwo().IsSelected; } set { Sheet.GetWindowTwo().IsSelected = (value); } } /// /// Gets or sets a value indicating if this _sheet is currently focused. /// /// true if this _sheet is currently focused; otherwise, false. public bool IsActive { get { return Sheet.GetWindowTwo().IsActive; } set { Sheet.GetWindowTwo().IsActive = (value); } } /// /// Sets whether sheet is selected. /// /// Whether to select the sheet or deselect the sheet. public void SetActive(bool sel) { this.Sheet.WindowTwo.IsActive = sel; } private WorksheetProtectionBlock ProtectionBlock { get { return _sheet.ProtectionBlock; } } /// /// Answer whether protection is enabled or disabled /// /// true if protection enabled; otherwise, false. public bool Protect { get { return ProtectionBlock.IsSheetProtected; } } /// /// Gets the hashed password /// /// The password. public int Password { get { return ProtectionBlock.PasswordHash; } } /// /// Answer whether object protection is enabled or disabled /// /// true if protection enabled; otherwise, false. public bool ObjectProtect { get { return ProtectionBlock.IsObjectProtected; } } /// /// Answer whether scenario protection is enabled or disabled /// /// true if protection enabled; otherwise, false. public bool ScenarioProtect { get { return ProtectionBlock.IsScenarioProtected; } } /// /// Sets the protection enabled as well as the password /// /// password to set for protection, pass null to remove protection public void ProtectSheet(String password) { ProtectionBlock.ProtectSheet(password, true, true); //protect objs&scenarios(normal) } /// /// Sets the zoom magnication for the _sheet. The zoom is expressed as a /// fraction. For example to express a zoom of 75% use 3 for the numerator /// and 4 for the denominator. /// /// The numerator for the zoom magnification. /// The denominator for the zoom magnification. public void SetZoom(int numerator, int denominator) { if (numerator < 1 || numerator > 65535) throw new ArgumentException("Numerator must be greater than 1 and less than 65536"); if (denominator < 1 || denominator > 65535) throw new ArgumentException("Denominator must be greater than 1 and less than 65536"); SCLRecord sclRecord = new SCLRecord(); sclRecord.Numerator = ((short)numerator); sclRecord.Denominator = ((short)denominator); Sheet.SetSCLRecord(sclRecord); } /// /// Sets the enclosed border of region. /// /// The region. /// Type of the border. /// The color. public void SetEnclosedBorderOfRegion(CellRangeAddress region, HH.WMS.Utils.NPOI.SS.UserModel.BorderStyle borderType, short color) { HSSFRegionUtil.SetRightBorderColor(color, region, this, _workbook); HSSFRegionUtil.SetBorderRight(borderType, region, this, _workbook); HSSFRegionUtil.SetLeftBorderColor(color, region, this, _workbook); HSSFRegionUtil.SetBorderLeft(borderType, region, this, _workbook); HSSFRegionUtil.SetTopBorderColor(color, region, this, _workbook); HSSFRegionUtil.SetBorderTop(borderType, region, this, _workbook); HSSFRegionUtil.SetBottomBorderColor(color, region, this, _workbook); HSSFRegionUtil.SetBorderBottom(borderType, region, this, _workbook); } /// /// Sets the right border of region. /// /// The region. /// Type of the border. /// The color. public void SetBorderRightOfRegion(CellRangeAddress region, HH.WMS.Utils.NPOI.SS.UserModel.BorderStyle borderType, short color) { HSSFRegionUtil.SetRightBorderColor(color, region, this, _workbook); HSSFRegionUtil.SetBorderRight(borderType, region, this, _workbook); } /// /// Sets the left border of region. /// /// The region. /// Type of the border. /// The color. public void SetBorderLeftOfRegion(CellRangeAddress region, HH.WMS.Utils.NPOI.SS.UserModel.BorderStyle borderType, short color) { HSSFRegionUtil.SetLeftBorderColor(color, region, this, _workbook); HSSFRegionUtil.SetBorderLeft(borderType, region, this, _workbook); } /// /// Sets the top border of region. /// /// The region. /// Type of the border. /// The color. public void SetBorderTopOfRegion(CellRangeAddress region, HH.WMS.Utils.NPOI.SS.UserModel.BorderStyle borderType, short color) { HSSFRegionUtil.SetTopBorderColor(color, region, this, _workbook); HSSFRegionUtil.SetBorderTop(borderType, region, this, _workbook); } /// /// Sets the bottom border of region. /// /// The region. /// Type of the border. /// The color. public void SetBorderBottomOfRegion(CellRangeAddress region, HH.WMS.Utils.NPOI.SS.UserModel.BorderStyle borderType, short color) { HSSFRegionUtil.SetBottomBorderColor(color, region, this, _workbook); HSSFRegionUtil.SetBorderBottom(borderType, region, this, _workbook); } /// /// The top row in the visible view when the _sheet is /// first viewed after opening it in a viewer /// /// the rownum (0 based) of the top row public short TopRow { get { return _sheet.TopRow; } set { _sheet.TopRow = value; } } /// /// The left col in the visible view when the _sheet Is /// first viewed after opening it in a viewer /// /// the rownum (0 based) of the top row public short LeftCol { get { return _sheet.LeftCol; } set { _sheet.LeftCol = value; } } /// /// Sets desktop window pane display area, when the /// file is first opened in a viewer. /// /// the top row to show in desktop window pane /// the left column to show in desktop window pane public void ShowInPane(short toprow, short leftcol) { this._sheet.TopRow = (toprow); this._sheet.LeftCol = (leftcol); } /// /// Shifts the merged regions left or right depending on mode /// TODO: MODE , this is only row specific /// /// The start row. /// The end row. /// The n. /// if set to true [is row]. protected void ShiftMerged(int startRow, int endRow, int n, bool IsRow) { List ShiftedRegions = new List(); //move merged regions completely if they fall within the new region boundaries when they are Shifted for (int i = 0; i < this.NumMergedRegions; i++) { HH.WMS.Utils.NPOI.SS.Util.CellRangeAddress merged = this.GetMergedRegion(i); bool inStart = (merged.FirstRow >= startRow || merged.LastRow >= startRow); bool inEnd = (merged.FirstRow <= endRow || merged.LastRow <= endRow); //dont Check if it's not within the Shifted area if (!(inStart && inEnd)) continue; //only Shift if the region outside the Shifted rows is not merged too //if (!ContainsCell(merged, startRow - 1, 0) && !ContainsCell(merged, endRow + 1, 0)) if(!SheetUtil.ContainsCell(merged, startRow-1, 0) && !SheetUtil.ContainsCell(merged, endRow+1, 0)) { merged.FirstRow = (merged.FirstRow + n); merged.LastRow = (merged.LastRow + n); //have to Remove/Add it back ShiftedRegions.Add(merged); this.RemoveMergedRegion(i); i = i - 1; // we have to back up now since we Removed one } } //Readd so it doesn't Get Shifted again IEnumerator iterator = ShiftedRegions.GetEnumerator(); while (iterator.MoveNext()) { HH.WMS.Utils.NPOI.SS.Util.CellRangeAddress region = (HH.WMS.Utils.NPOI.SS.Util.CellRangeAddress)iterator.Current; this.AddMergedRegion(region); } } [Obsolete] private static bool ContainsCell(HH.WMS.Utils.NPOI.SS.Util.CellRangeAddress cr, int rowIx, int colIx) { if (cr.FirstRow <= rowIx && cr.LastRow >= rowIx && cr.FirstColumn <= colIx && cr.LastColumn >= colIx) { return true; } return false; } /// /// Shifts rows between startRow and endRow n number of rows. /// If you use a negative number, it will Shift rows up. /// Code Ensures that rows don't wrap around. /// Calls ShiftRows(startRow, endRow, n, false, false); /// Additionally Shifts merged regions that are completely defined in these /// rows (ie. merged 2 cells on a row to be Shifted). /// /// the row to start Shifting /// the row to end Shifting /// the number of rows to Shift public void ShiftRows(int startRow, int endRow, int n) { ShiftRows(startRow, endRow, n, false, false); } /// /// Shifts rows between startRow and endRow n number of rows. /// If you use a negative number, it will shift rows up. /// Code ensures that rows don't wrap around /// Additionally shifts merged regions that are completely defined in these /// rows (ie. merged 2 cells on a row to be shifted). /// TODO Might want to add bounds checking here /// /// the row to start shifting /// the row to end shifting /// the number of rows to shift /// whether to copy the row height during the shift /// whether to set the original row's height to the default public void ShiftRows(int startRow, int endRow, int n, bool copyRowHeight, bool resetOriginalRowHeight) { ShiftRows(startRow, endRow, n, copyRowHeight, resetOriginalRowHeight, true); } /// /// Shifts rows between startRow and endRow n number of rows. /// If you use a negative number, it will Shift rows up. /// Code Ensures that rows don't wrap around /// Additionally Shifts merged regions that are completely defined in these /// rows (ie. merged 2 cells on a row to be Shifted). /// TODO Might want to Add bounds Checking here /// /// the row to start Shifting /// the row to end Shifting /// the number of rows to Shift /// whether to copy the row height during the Shift /// whether to Set the original row's height to the default /// if set to true [move comments]. public void ShiftRows(int startRow, int endRow, int n, bool copyRowHeight, bool resetOriginalRowHeight, bool moveComments) { int s, inc; if (n < 0) { s = startRow; inc = 1; } else if (n > 0) { s = endRow; inc = -1; } else { // Nothing to do return; } NoteRecord[] noteRecs; if (moveComments) { noteRecs = _sheet.GetNoteRecords(); } else { noteRecs = NoteRecord.EMPTY_ARRAY; } ShiftMerged(startRow, endRow, n, true); _sheet.PageSettings.ShiftRowBreaks(startRow, endRow, n); for (int rowNum = s; rowNum >= startRow && rowNum <= endRow && rowNum >= 0 && rowNum < 65536; rowNum += inc) { HSSFRow row = (HSSFRow)GetRow(rowNum); // notify all cells in this row that we are going to shift them, // it can throw IllegalStateException if the operation is not allowed, for example, // if the row contains cells included in a multi-cell array formula if (row != null) NotifyRowShifting(row); HSSFRow row2Replace = (HSSFRow)GetRow(rowNum + n); if (row2Replace == null) row2Replace = (HSSFRow)CreateRow(rowNum + n); // Remove all the old cells from the row we'll // be writing too, before we start overwriting // any cells. This avoids issues with cells // changing type, and records not being correctly // overwritten row2Replace.RemoveAllCells(); // If this row doesn't exist, nothing needs to // be done for the now empty destination row if (row == null) continue; // Nothing to do for this row // Fix up row heights if required if (copyRowHeight) { row2Replace.Height = (row.Height); } if (resetOriginalRowHeight) { row.Height = ((short)0xff); } // Copy each cell from the source row to // the destination row List cells = row.Cells; foreach (ICell cell in cells) { row.RemoveCell(cell); CellValueRecordInterface cellRecord = ((HSSFCell)cell).CellValueRecord; cellRecord.Row = (rowNum + n); row2Replace.CreateCellFromRecord(cellRecord); _sheet.AddValueRecord(rowNum + n, cellRecord); HH.WMS.Utils.NPOI.SS.UserModel.IHyperlink link = cell.Hyperlink; if (link != null) { link.FirstRow = (link.FirstRow + n); link.LastRow = (link.LastRow + n); } } // Now zap all the cells in the source row row.RemoveAllCells(); // Move comments from the source row to the // destination row. Note that comments can // exist for cells which are null if (moveComments) { // This code would get simpler if NoteRecords could be organised by HSSFRow. for (int i = noteRecs.Length - 1; i >= 0; i--) { NoteRecord nr = noteRecs[i]; if (nr.Row != rowNum) { continue; } IComment comment = GetCellComment(rowNum, nr.Column); if (comment != null) { comment.Row = (rowNum + n); } } } } // Re-compute the first and last rows of the sheet as needed if (n > 0) { // Rows are moving down if (startRow == firstrow) { // Need to walk forward to find the first non-blank row firstrow = Math.Max(startRow + n, 0); for (int i = startRow + 1; i < startRow + n; i++) { if (GetRow(i) != null) { firstrow = i; break; } } } if (endRow + n > lastrow) { lastrow = Math.Min(endRow + n, SpreadsheetVersion.EXCEL97.LastRowIndex); } } else { // Rows are moving up if (startRow + n < firstrow) { firstrow = Math.Max(startRow + n, 0); } if (endRow == lastrow) { // Need to walk backward to find the last non-blank row lastrow = Math.Min(endRow + n, SpreadsheetVersion.EXCEL97.LastRowIndex); for (int i = endRow - 1; i > endRow + n; i++) { if (GetRow(i) != null) { lastrow = i; break; } } } } //if (endRow == lastrow || endRow + n > lastrow) lastrow = Math.Min(endRow + n, SpreadsheetVersion.EXCEL97.LastRowIndex); //if (startRow == firstrow || startRow + n < firstrow) firstrow = Math.Max(startRow + n, 0); // Update any formulas on this _sheet that point to // rows which have been moved int sheetIndex = _workbook.GetSheetIndex(this); int externSheetIndex = book.CheckExternSheet(sheetIndex); FormulaShifter shifter = FormulaShifter.CreateForRowShift(externSheetIndex, startRow, endRow, n); _sheet.UpdateFormulasAfterCellShift(shifter, externSheetIndex); int nSheets = _workbook.NumberOfSheets; for (int i = 0; i < nSheets; i++) { InternalSheet otherSheet = ((HSSFSheet)_workbook.GetSheetAt(i)).Sheet; if (otherSheet == this._sheet) { continue; } int otherExtSheetIx = book.CheckExternSheet(i); otherSheet.UpdateFormulasAfterCellShift(shifter, otherExtSheetIx); } _workbook.Workbook.UpdateNamesAfterCellShift(shifter); } /// /// Inserts the chart records. /// /// The records. public void InsertChartRecords(IList records) { int window2Loc = _sheet.FindFirstRecordLocBySid(WindowTwoRecord.sid); int i = 0; foreach (Record rec in records) { _sheet.Records.Insert(window2Loc + i, records); i++; } } private void NotifyRowShifting(HSSFRow row) { String msg = "Row[rownum=" + row.RowNum + "] contains cell(s) included in a multi-cell array formula. " + "You cannot change part of an array."; foreach (ICell cell in row.Cells) { HSSFCell hcell = (HSSFCell)cell; if (hcell.IsPartOfArrayFormulaGroup) { hcell.NotifyArrayFormulaChanging(msg); } } } /// /// Creates a split (freezepane). Any existing freezepane or split pane is overwritten. /// /// Horizonatal position of split. /// Vertical position of split. /// Top row visible in bottom pane /// Left column visible in right pane. public void CreateFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow) { ValidateColumn(colSplit); ValidateRow(rowSplit); if (leftmostColumn < colSplit) throw new ArgumentException("leftmostColumn parameter must not be less than colSplit parameter"); if (topRow < rowSplit) throw new ArgumentException("topRow parameter must not be less than leftmostColumn parameter"); Sheet.CreateFreezePane(colSplit, rowSplit, topRow, leftmostColumn); } /// /// Creates a split (freezepane). Any existing freezepane or split pane is overwritten. /// /// Horizonatal position of split. /// Vertical position of split. public void CreateFreezePane(int colSplit, int rowSplit) { CreateFreezePane(colSplit, rowSplit, colSplit, rowSplit); } /// /// Creates a split pane. Any existing freezepane or split pane is overwritten. /// /// Horizonatal position of split (in 1/20th of a point). /// Vertical position of split (in 1/20th of a point). /// Left column visible in right pane. /// Top row visible in bottom pane. /// Active pane. One of: PANE_LOWER_RIGHT,PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT public void CreateSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, HH.WMS.Utils.NPOI.SS.UserModel.PanePosition activePane) //this would have the same parameter sequence as the internal method and matches the content of the description above, // if this signature changed do it in ISheet, too. // public void CreateSplitPane(int xSplitPos, int ySplitPos, int topRow, int leftmostColumn, HH.WMS.Utils.NPOI.SS.UserModel.PanePosition activePane) { Sheet.CreateSplitPane(xSplitPos, ySplitPos, topRow, leftmostColumn, activePane); } /// /// Returns the information regarding the currently configured pane (split or freeze). /// /// null if no pane configured, or the pane information. public HH.WMS.Utils.NPOI.SS.Util.PaneInformation PaneInformation { get { return Sheet.PaneInformation; } } /// /// Gets or sets if gridlines are Displayed. /// /// whether gridlines are Displayed public bool DisplayGridlines { get { return _sheet.DisplayGridlines; } set { _sheet.DisplayGridlines = (value); } } /// /// Gets or sets a value indicating whether formulas are displayed. /// /// whether formulas are Displayed public bool DisplayFormulas { get { return _sheet.DisplayFormulas; } set { _sheet.DisplayFormulas = (value); } } /// /// Gets or sets a value indicating whether RowColHeadings are displayed. /// /// /// whether RowColHeadings are displayed /// public bool DisplayRowColHeadings { get { return _sheet.DisplayRowColHeadings; } set { _sheet.DisplayRowColHeadings = (value); } } /// /// Gets the size of the margin in inches. /// /// which margin to get. /// the size of the margin public double GetMargin(HH.WMS.Utils.NPOI.SS.UserModel.MarginType margin) { return _sheet.PageSettings.GetMargin(margin); } /// /// Sets the size of the margin in inches. /// /// which margin to get. /// the size of the margin public void SetMargin(HH.WMS.Utils.NPOI.SS.UserModel.MarginType margin, double size) { _sheet.PageSettings.SetMargin(margin, size); } /// /// Sets a page break at the indicated row /// /// The row. public void SetRowBreak(int row) { ValidateRow(row); _sheet.PageSettings.SetRowBreak(row, (short)0, (short)255); } /// /// Determines if there is a page break at the indicated row /// /// The row. /// /// true if [is row broken] [the specified row]; otherwise, false. /// public bool IsRowBroken(int row) { return _sheet.PageSettings.IsRowBroken(row); } /// /// Removes the page break at the indicated row /// /// The row. public void RemoveRowBreak(int row) { _sheet.PageSettings.RemoveRowBreak(row); } /// /// Retrieves all the horizontal page breaks /// /// all the horizontal page breaks, or null if there are no row page breaks public int[] RowBreaks { get { //we can probably cache this information, but this should be a sparsely used function return _sheet.PageSettings.RowBreaks; } } /// /// Retrieves all the vertical page breaks /// /// all the vertical page breaks, or null if there are no column page breaks public int[] ColumnBreaks { get { //we can probably cache this information, but this should be a sparsely used function return _sheet.PageSettings.ColumnBreaks; } } /// /// Sets a page break at the indicated column /// /// The column. public void SetColumnBreak(int column) { ValidateColumn(column); _sheet.PageSettings.SetColumnBreak(column, (short)0, unchecked((short)65535)); } /// /// Determines if there is a page break at the indicated column /// /// The column. /// /// true if [is column broken] [the specified column]; otherwise, false. /// public bool IsColumnBroken(int column) { return _sheet.PageSettings.IsColumnBroken(column); } /// /// Removes a page break at the indicated column /// /// The column. public void RemoveColumnBreak(int column) { _sheet.PageSettings.RemoveColumnBreak(column); } /// /// Runs a bounds Check for row numbers /// /// The row. protected void ValidateRow(int row) { int maxrow = SpreadsheetVersion.EXCEL97.LastRowIndex; if (row > maxrow) throw new ArgumentException("Maximum row number is " + maxrow.ToString(CultureInfo.CurrentCulture)); if (row < 0) throw new ArgumentException("Minumum row number is 0"); } /// /// Runs a bounds Check for column numbers /// /// The column. protected void ValidateColumn(int column) { int maxcol = SpreadsheetVersion.EXCEL97.LastColumnIndex; if (column > maxcol) throw new ArgumentException("Maximum column number is " + maxcol.ToString(CultureInfo.CurrentCulture)); if (column < 0) throw new ArgumentException("Minimum column number is 0"); } /// /// Aggregates the drawing records and dumps the escher record hierarchy /// to the standard output. /// /// if set to true [fat]. public void DumpDrawingRecords(bool fat) { _sheet.AggregateDrawingRecords(book.DrawingManager, false); EscherAggregate r = (EscherAggregate)Sheet.FindFirstRecordBySid(EscherAggregate.sid); IList escherRecords = r.EscherRecords; for (IEnumerator iterator = escherRecords.GetEnumerator(); iterator.MoveNext(); ) { EscherRecord escherRecord = (EscherRecord)iterator.Current; if (fat) Console.WriteLine(escherRecord.ToString()); else escherRecord.Display(0); } } [NonSerialized] private HSSFPatriarch _patriarch; /// /// Creates the top-level drawing patriarch. This will have /// the effect of removing any existing drawings on this /// _sheet. /// This may then be used to Add graphics or charts /// /// The new patriarch. public HH.WMS.Utils.NPOI.SS.UserModel.IDrawing CreateDrawingPatriarch() { if (_patriarch == null) { // Create the drawing group if it doesn't already exist. _workbook.InitDrawings(); if (_patriarch == null) { _sheet.AggregateDrawingRecords(book.DrawingManager, true); EscherAggregate agg = (EscherAggregate)_sheet.FindFirstRecordBySid(EscherAggregate.sid); _patriarch = new HSSFPatriarch(this, agg); agg.Patriarch=(_patriarch); } } return _patriarch; } /// /// Returns the agregate escher records for this _sheet, /// it there is one. /// WARNING - calling this will trigger a parsing of the /// associated escher records. Any that aren't supported /// (such as charts and complex drawing types) will almost /// certainly be lost or corrupted when written out. /// /// The drawing escher aggregate. public EscherAggregate DrawingEscherAggregate { get { book.FindDrawingGroup(); // If there's now no drawing manager, then there's // no drawing escher records on the _workbook if (book.DrawingManager == null) { return null; } int found = _sheet.AggregateDrawingRecords( book.DrawingManager, false ); if (found == -1) { // Workbook has drawing stuff, but this _sheet doesn't return null; } // Grab our aggregate record, and wire it up EscherAggregate agg = (EscherAggregate)_sheet.FindFirstRecordBySid(EscherAggregate.sid); return agg; } } /// /// Returns the top-level drawing patriach, if there is one. /// This will hold any graphics or charts for the _sheet. /// WARNING - calling this will trigger a parsing of the /// associated escher records. Any that aren't supported /// (such as charts and complex drawing types) will almost /// certainly be lost or corrupted when written out. Only /// use this with simple drawings, otherwise call /// HSSFSheet#CreateDrawingPatriarch() and /// start from scratch! /// /// The drawing patriarch. public IDrawing DrawingPatriarch { get { if (_patriarch != null) return _patriarch; EscherAggregate agg = this.DrawingEscherAggregate; if (agg == null) return null; _patriarch = new HSSFPatriarch(this, agg); agg.Patriarch = _patriarch; // Have it Process the records into high level objects // as best it can do (this step may eat anything // that Isn't supported, you were warned...) agg.ConvertRecordsToUserModel(); // Return what we could cope with return _patriarch; } } /// /// Gets or sets the tab color of the _sheet /// public short TabColorIndex { get { return _sheet.TabColorIndex; } set { _sheet.TabColorIndex = value; } } /// /// Gets or sets whether the tab color of _sheet is automatic /// public bool IsAutoTabColor { get { return _sheet.IsAutoTabColor; } set { _sheet.IsAutoTabColor = value; } } /// /// Expands or collapses a column Group. /// /// One of the columns in the Group. /// true = collapse Group, false = expand Group. public void SetColumnGroupCollapsed(int columnNumber, bool collapsed) { _sheet.SetColumnGroupCollapsed(columnNumber, collapsed); } /// /// Create an outline for the provided column range. /// /// beginning of the column range. /// end of the column range. public void GroupColumn(int fromColumn, int toColumn) { _sheet.GroupColumnRange(fromColumn, toColumn, true); } /// /// Ungroups the column. /// /// From column. /// To column. public void UngroupColumn(int fromColumn, int toColumn) { _sheet.GroupColumnRange(fromColumn, toColumn, false); } /// /// Groups the row. /// /// From row. /// To row. public void GroupRow(int fromRow, int toRow) { _sheet.GroupRowRange(fromRow, toRow, true); } /// /// Remove a Array Formula from this sheet. All cells contained in the Array Formula range are removed as well /// /// any cell within Array Formula range /// the of cells affected by this change public ICellRange RemoveArrayFormula(ICell cell) { if (cell.Sheet != this) { throw new ArgumentException("Specified cell does not belong to this sheet."); } CellValueRecordInterface rec = ((HSSFCell)cell).CellValueRecord; if (!(rec is FormulaRecordAggregate)) { String ref1 = new CellReference(cell).FormatAsString(); throw new ArgumentException("Cell " + ref1 + " is not part of an array formula."); } FormulaRecordAggregate fra = (FormulaRecordAggregate)rec; CellRangeAddress range = fra.RemoveArrayFormula(cell.RowIndex, cell.ColumnIndex); ICellRange result = GetCellRange(range); // clear all cells in the range foreach (ICell c in result) { c.SetCellType(CellType.BLANK); } return result; } /// /// Also creates cells if they don't exist. /// private ICellRange GetCellRange(CellRangeAddress range) { int firstRow = range.FirstRow; int firstColumn = range.FirstColumn; int lastRow = range.LastRow; int lastColumn = range.LastColumn; int height = lastRow - firstRow + 1; int width = lastColumn - firstColumn + 1; List temp = new List(height * width); for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) { for (int colIn = firstColumn; colIn <= lastColumn; colIn++) { IRow row = GetRow(rowIn); if (row == null) { row = CreateRow(rowIn); } ICell cell = row.GetCell(colIn); if (cell == null) { cell = row.CreateCell(colIn); } temp.Add(cell); } } return SSCellRange.Create(firstRow, firstColumn, height, width, temp, typeof(HSSFCell)); } /// /// Sets array formula to specified region for result. /// /// text representation of the formula /// Region of array formula for result /// the of cells affected by this change public ICellRange SetArrayFormula(String formula, CellRangeAddress range) { // make sure the formula parses OK first int sheetIndex = _workbook.GetSheetIndex(this); Ptg[] ptgs = HSSFFormulaParser.Parse(formula, _workbook, FormulaType.ARRAY, sheetIndex); ICellRange cells = GetCellRange(range); foreach (HSSFCell c in cells) { c.SetCellArrayFormula(range); } HSSFCell mainArrayFormulaCell = (HSSFCell)cells.TopLeftCell; FormulaRecordAggregate agg = (FormulaRecordAggregate)mainArrayFormulaCell.CellValueRecord; agg.SetArrayFormula(range, ptgs); return cells; } /// /// Ungroups the row. /// /// From row. /// To row. public void UngroupRow(int fromRow, int toRow) { _sheet.GroupRowRange(fromRow, toRow, false); } /// /// Sets the row group collapsed. /// /// The row. /// if set to true [collapse]. public void SetRowGroupCollapsed(int row, bool collapse) { if (collapse) { _sheet.RowsAggregate.CollapseRow(row); } else { _sheet.RowsAggregate.ExpandRow(row); } } /// /// Sets the default column style for a given column. POI will only apply this style to new cells Added to the _sheet. /// /// the column index /// the style to set public void SetDefaultColumnStyle(int column, HH.WMS.Utils.NPOI.SS.UserModel.ICellStyle style) { _sheet.SetDefaultColumnStyle(column, style.Index); } /// /// Adjusts the column width to fit the contents. /// This Process can be relatively slow on large sheets, so this should /// normally only be called once per column, at the end of your /// Processing. /// /// the column index. public void AutoSizeColumn(int column) { AutoSizeColumn(column, false); } /// /// Adjusts the column width to fit the contents. /// This Process can be relatively slow on large sheets, so this should /// normally only be called once per column, at the end of your /// Processing. /// You can specify whether the content of merged cells should be considered or ignored. /// Default is to ignore merged cells. /// /// the column index /// whether to use the contents of merged cells when calculating the width of the column public void AutoSizeColumn(int column, bool useMergedCells) { double width = SheetUtil.GetColumnWidth(this, column, useMergedCells); if (width != -1) { width *= 256; int maxColumnWidth = 255 * 256; // The maximum column width for an individual cell is 255 characters if (width > maxColumnWidth) { width = maxColumnWidth; } SetColumnWidth(column, (int)width); } } /// /// Checks if the provided region is part of the merged regions. /// /// Region searched in the merged regions /// true, when the region is contained in at least one of the merged regions public bool IsMergedRegion(CellRangeAddress mergedRegion) { foreach (CellRangeAddress range in _sheet.MergedRecords.MergedRegions) { if (range.FirstColumn <= mergedRegion.FirstColumn && range.LastColumn >= mergedRegion.LastColumn && range.FirstRow <= mergedRegion.FirstRow && range.LastRow >= mergedRegion.LastRow) { return true; } } return false; } /// /// Gets the merged region at the specified index /// /// The index. /// public CellRangeAddress GetMergedRegion(int index) { return _sheet.GetMergedRegionAt(index); } /// /// Convert HSSFFont to Font. /// /// The font. /// public System.Drawing.Font HSSFFont2Font(HSSFFont font1) { return new System.Drawing.Font(font1.FontName, font1.FontHeightInPoints); } /// /// Returns cell comment for the specified row and column /// /// The row. /// The column. /// cell comment or null if not found public IComment GetCellComment(int row, int column) { // Don't call FindCellComment directly, otherwise // two calls to this method will result in two // new HSSFComment instances, which is bad HSSFRow r = (HSSFRow)GetRow(row); if (r != null) { HH.WMS.Utils.NPOI.SS.UserModel.ICell c = r.GetCell(column); if (c != null) { return c.CellComment; } else { // No cell, so you will Get new // objects every time, sorry... return HSSFCell.FindCellComment(_sheet, row, column); } } return null; } /// /// Gets the sheet conditional formatting. /// /// The sheet conditional formatting. public ISheetConditionalFormatting SheetConditionalFormatting { get { return new HSSFSheetConditionalFormatting(this); } } /// /// Get the DVRecords objects that are associated to this _sheet /// /// a list of DVRecord instances public IList DVRecords { get { IList dvRecords = new ArrayList(); IList records = _sheet.Records; for (int index = 0; index < records.Count; index++) { if (records[index] is DVRecord) { dvRecords.Add(records[index]); } } return dvRecords; } } /// /// Provide a reference to the parent workbook. /// public HH.WMS.Utils.NPOI.SS.UserModel.IWorkbook Workbook { get { return _workbook; } } /// /// Returns the name of this _sheet /// public String SheetName { get { HH.WMS.Utils.NPOI.SS.UserModel.IWorkbook wb = Workbook; int idx = wb.GetSheetIndex(this); return wb.GetSheetName(idx); } } /// /// Create an instance of a DataValidationHelper. /// /// Instance of a DataValidationHelper public IDataValidationHelper GetDataValidationHelper() { return new HSSFDataValidationHelper(this); } /// /// Enable filtering for a range of cells /// /// the range of cells to filter public IAutoFilter SetAutoFilter(CellRangeAddress range) { InternalWorkbook workbook = _workbook.Workbook; int sheetIndex = _workbook.GetSheetIndex(this); NameRecord name = workbook.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1); if (name == null) { name = workbook.CreateBuiltInName(NameRecord.BUILTIN_FILTER_DB, sheetIndex + 1); } // The built-in name must consist of a single Area3d Ptg. Area3DPtg ptg = new Area3DPtg(range.FirstRow, range.LastRow, range.FirstColumn, range.LastColumn, false, false, false, false, sheetIndex); name.NameDefinition = (new Ptg[] { ptg }); AutoFilterInfoRecord r = new AutoFilterInfoRecord(); // the number of columns that have AutoFilter enabled. int numcols = 1 + range.LastColumn - range.FirstColumn; r.NumEntries = (short)numcols; int idx = _sheet.FindFirstRecordLocBySid(DimensionsRecord.sid); _sheet.Records.Insert(idx, r); //create a combobox control for each column HSSFPatriarch p = (HSSFPatriarch)CreateDrawingPatriarch(); for (int col = range.FirstColumn; col <= range.LastColumn; col++) { p.CreateComboBox(new HSSFClientAnchor(0, 0, 0, 0, (short)col, range.FirstRow, (short)(col + 1), range.FirstRow + 1)); } return new HSSFAutoFilter(this); } } }