using System; using System.Collections.Generic; using System.Text; using Biff8Excel.Formulas; using Biff8Excel.Interfaces; namespace Biff8Excel.Excel { public class ExcelCell : IDisposable, Biff8Excel.Interfaces.ICell { double tmp; internal ExcelWorkbook pWorkbook; // Pointer to the Workbook (soft reference) ExcelCellStyle m_cellstyle; Biff8Excel.Records.Blank m_blank; Biff8Excel.Records.LabelSST m_label; Biff8Excel.Records.RK m_integer; Biff8Excel.Records.Number m_float; Biff8Excel.Formulas.Formula m_formula; int m_styleIndex; ushort m_row; ushort m_col; ushort m_sheetNumber; object m_value; object m_resolvedValue; // returns the value of numeric and formula cells uint m_SSTIndex; //internal double Calculate(ExcelCellLink sheetCells) internal double Calculate(ExcelCellDictionary sheetCells) { if (m_value.ToString().Length == 0) return 0; //CellCollection resutl; //Dictionary result; if (m_formula == null) m_formula = new Formula(); m_formula.ExternSheet = Workbook.ExterSheet; m_formula.FormulaString = m_value.ToString(); m_formula.ThisSheetNumber = m_sheetNumber; m_formula.SheetNames = Workbook.SheetNames; // pass in the names of each sheet m_resolvedValue = m_formula.CalculateCell(sheetCells); // may return empty if (m_value != null) { m_formula.Result = double.Parse(m_resolvedValue.ToString()); } return 0; } internal bool CalculationResolved { get { double db; if (m_resolvedValue == null) return false; else if (double.TryParse(m_resolvedValue.ToString(), out db)) return true; return false; } } internal string CellReference { get { return Globals.CellReferenceFromRowColumn((ushort)m_row, (ushort)m_col); } } internal int CellType { get { if (m_value == null || m_value is System.DBNull) return Globals.CELL_TYPE_BLANK; else if (m_value is string) { if (m_value.ToString().Length == 0) return Globals.CELL_TYPE_BLANK; else if (m_value.ToString().Substring(0, 1) == "=") return Globals.CELL_TYPE_FORMULA; else return Globals.CELL_TYPE_LABEL; } else if (double.TryParse(m_value.ToString(), out tmp)) return Globals.CELL_TYPE_NUMBER; else if (m_value is DateTime) return Globals.CELL_TYPE_DATE; else throw new Biff8ExcelException(string.Format("Unknown value type, row ={0}, col ={1}", m_row, m_col)); } } //private ushort columnNumber //{ // set { this.Column = value; } //} internal ushort Column { set { m_col = (value < 0 ? (ushort)0 : (ushort)value); } } internal ExcelCell(ExcelWorkbook pWorkbook) { this.pWorkbook = pWorkbook; m_cellstyle = pWorkbook.CreateStyle(); m_styleIndex = -1; } internal bool HasThickBorderAbove { get { if (m_cellstyle.TopLineStyle == EnumLineStyle.Double || m_cellstyle.TopLineStyle == EnumLineStyle.Thick) return true; return false; } } internal bool HasThickBorderBelow { get { if (m_cellstyle.BottomLineStyle == EnumLineStyle.Double || m_cellstyle.BottomLineStyle == EnumLineStyle.Thick) return true; return false; } } internal ushort Row { set { m_row = (value < 0 ? (ushort)0 : (ushort)value); } } //internal ushort rowNumber //{ // set { this.Row = value; } //} internal uint SSTindex { set { m_SSTIndex = value; } } public ExcelCellStyle Style { set { if (value == null) throw new ArgumentNullException("value","Style not allow null "); m_cellstyle = value; this.StyleIndex = value.GetXFIndex(); } //internal get { return m_cellstyle; } } internal ushort sheetNumber { set { m_sheetNumber = value; } //get { return m_sheetNumber; } } internal int StyleIndex { set { m_styleIndex = value; } get { return m_styleIndex; } } public override string ToString() { return string.Format("sheetNumber:{0} col:{1} row:{2}", m_sheetNumber , m_col , m_row); } public Object Value { set { if (value == null) throw new ArgumentNullException("value","Value not allow null "); m_value = value; //Ignor for strings; if (value is string && value.ToString().Length > 0) if (value.ToString().Substring(0, 1) == "=") return; if (m_value != null) m_resolvedValue = value; } get { return m_value; } } internal ExcelWorkbook Workbook { get { return pWorkbook; } set { pWorkbook = value; } } internal byte[] WriteCell() { if (m_value == null || m_value is System.DBNull) { // Blank String if (m_blank == null) m_blank = new Biff8Excel.Records.Blank(); m_blank.CellStyle = (ushort)m_styleIndex; m_blank.Row = m_row; m_blank.Column = m_col; return m_blank.GetByte(); } // String Value else if (m_value is string) { // Blank String again, (in case the user types in "") if (m_value.ToString().Length == 0) { if (m_blank == null) m_blank = new Biff8Excel.Records.Blank(); m_blank.CellStyle = (ushort)m_styleIndex; m_blank.Row = m_row; m_blank.Column = m_col; return m_blank.GetByte(); } // Formula String else if (m_value.ToString().Substring(0, 1) == "=") { if (m_formula == null) m_formula = new Formula(); m_formula.CellStyle = (ushort)m_styleIndex; m_formula.Row = m_row; m_formula.Column = m_col; return m_formula.GetByte(); } // Label String else { if (m_label == null) m_label = new Biff8Excel.Records.LabelSST(); m_label.CellStyle = (ushort)m_styleIndex; m_label.Row = m_row; m_label.Column = m_col; m_label.SSTIndex = m_SSTIndex; return m_label.GetByte(); } } // Date Value (treated as an either a number or integer else if (m_value is System.DateTime) { string s = ((DateTime)m_value).ToOADate().ToString(); if (s.IndexOf('.') > -1) // Date and Time { if (m_float == null) m_float = new Biff8Excel.Records.Number(); m_float.CellStyle = (ushort)m_styleIndex; m_float.Row = m_row; m_float.Column = m_col; m_float.Value = Convert.ToDateTime(m_value).ToOADate(); return m_float.GetByte(); } else // just a date { if (m_integer == null) m_integer = new Biff8Excel.Records.RK(); m_integer.CellStyle = (ushort)m_styleIndex; m_integer.Row = m_row; m_integer.Column = m_col; m_integer.Value = (uint)Math.Floor(((DateTime)m_value).ToOADate()); return m_integer.GetByte(); } } // Number Value else if (double.TryParse(m_value.ToString(), out tmp)) { // Integer Value or Date if ((m_value is int) || (m_value is ushort)) { if (m_integer == null) m_integer = new Biff8Excel.Records.RK(); m_integer.CellStyle = (ushort)m_styleIndex; m_integer.Row = m_row; m_integer.Column = m_col; m_integer.Value = Convert.ToInt32( m_value ); return m_integer.GetByte(); } else //// Floating Point Value //if ((m_value is decimal) || (m_value is double) || (m_value is Single)) { if (m_float == null) m_float = new Biff8Excel.Records.Number(); m_float.CellStyle = (ushort)m_styleIndex; m_float.Row = m_row; m_float.Column = m_col; m_float.Value = Convert.ToDouble(m_value); return m_float.GetByte(); } } // unknown Value else { throw new Biff8ExcelException(string.Format("Unknown value type, row ={0}, col ={1}", m_row, m_col)); } return null; } #region IDisposable ³ΙΤ± public void Dispose() { m_blank = null; m_label = null; m_integer = null; m_float = null; m_formula = null; m_cellstyle = null; } #endregion #region ICell ³ΙΤ± public object ResolvedValue { get { return m_resolvedValue; } } #endregion } }