[12074] | 1 | using System;
|
---|
| 2 | using System.Collections.Generic;
|
---|
| 3 | using System.Linq;
|
---|
| 4 | using System.Text;
|
---|
| 5 | using OfficeOpenXml.FormulaParsing;
|
---|
| 6 | using OfficeOpenXml.FormulaParsing.ExcelUtilities;
|
---|
| 7 | using OfficeOpenXml.FormulaParsing.Excel.Functions;
|
---|
| 8 | using OfficeOpenXml.FormulaParsing.LexicalAnalysis;
|
---|
| 9 | using OfficeOpenXml.Utils;
|
---|
| 10 | using OfficeOpenXml.Style.XmlAccess;
|
---|
| 11 |
|
---|
| 12 | namespace OfficeOpenXml.FormulaParsing
|
---|
| 13 | {
|
---|
| 14 | public class EpplusExcelDataProvider : ExcelDataProvider
|
---|
| 15 | {
|
---|
| 16 | public class RangeInfo : IRangeInfo
|
---|
| 17 | {
|
---|
| 18 | internal ExcelWorksheet _ws;
|
---|
| 19 | CellsStoreEnumerator<object> _values = null;
|
---|
| 20 | int _fromRow, _toRow, _fromCol, _toCol;
|
---|
| 21 | int _cellCount = 0;
|
---|
| 22 | ExcelAddressBase _address;
|
---|
| 23 | ICellInfo _cell;
|
---|
| 24 |
|
---|
| 25 | public RangeInfo(ExcelWorksheet ws, int fromRow, int fromCol, int toRow, int toCol)
|
---|
| 26 | {
|
---|
| 27 | _ws = ws;
|
---|
| 28 | _fromRow = fromRow;
|
---|
| 29 | _fromCol = fromCol;
|
---|
| 30 | _toRow = toRow;
|
---|
| 31 | _toCol = toCol;
|
---|
| 32 | _address = new ExcelAddressBase(_fromRow, _fromCol, _toRow, _toCol);
|
---|
| 33 | _values = new CellsStoreEnumerator<object>(ws._values, _fromRow, _fromCol, _toRow, _toCol);
|
---|
| 34 | _cell = new CellInfo(_ws, _values);
|
---|
| 35 | }
|
---|
| 36 |
|
---|
| 37 | public int GetNCells()
|
---|
| 38 | {
|
---|
| 39 | return ((_toRow - _fromRow) + 1) * ((_toCol - _fromCol) + 1);
|
---|
| 40 | }
|
---|
| 41 |
|
---|
| 42 | public bool IsEmpty
|
---|
| 43 | {
|
---|
| 44 | get
|
---|
| 45 | {
|
---|
| 46 | if (_cellCount > 0)
|
---|
| 47 | {
|
---|
| 48 | return false;
|
---|
| 49 | }
|
---|
| 50 | else if (_values.Next())
|
---|
| 51 | {
|
---|
| 52 | _values.Reset();
|
---|
| 53 | return false;
|
---|
| 54 | }
|
---|
| 55 | else
|
---|
| 56 | {
|
---|
| 57 | return true;
|
---|
| 58 | }
|
---|
| 59 | }
|
---|
| 60 | }
|
---|
| 61 | public bool IsMulti
|
---|
| 62 | {
|
---|
| 63 | get
|
---|
| 64 | {
|
---|
| 65 | if (_cellCount == 0)
|
---|
| 66 | {
|
---|
| 67 | if (_values.Next() && _values.Next())
|
---|
| 68 | {
|
---|
| 69 | _values.Reset();
|
---|
| 70 | return true;
|
---|
| 71 | }
|
---|
| 72 | else
|
---|
| 73 | {
|
---|
| 74 | _values.Reset();
|
---|
| 75 | return false;
|
---|
| 76 | }
|
---|
| 77 | }
|
---|
| 78 | else if (_cellCount > 1)
|
---|
| 79 | {
|
---|
| 80 | return true;
|
---|
| 81 | }
|
---|
| 82 | return false;
|
---|
| 83 | }
|
---|
| 84 | }
|
---|
| 85 |
|
---|
| 86 | public ICellInfo Current
|
---|
| 87 | {
|
---|
| 88 | get { return _cell; }
|
---|
| 89 | }
|
---|
| 90 |
|
---|
| 91 | public void Dispose()
|
---|
| 92 | {
|
---|
| 93 | //_values = null;
|
---|
| 94 | //_ws = null;
|
---|
| 95 | //_cell = null;
|
---|
| 96 | }
|
---|
| 97 |
|
---|
| 98 | object System.Collections.IEnumerator.Current
|
---|
| 99 | {
|
---|
| 100 | get
|
---|
| 101 | {
|
---|
| 102 | return this;
|
---|
| 103 | }
|
---|
| 104 | }
|
---|
| 105 |
|
---|
| 106 | public bool MoveNext()
|
---|
| 107 | {
|
---|
| 108 | _cellCount++;
|
---|
| 109 | return _values.MoveNext();
|
---|
| 110 | }
|
---|
| 111 |
|
---|
| 112 | public void Reset()
|
---|
| 113 | {
|
---|
| 114 | _values.Init();
|
---|
| 115 | }
|
---|
| 116 |
|
---|
| 117 |
|
---|
| 118 | public bool NextCell()
|
---|
| 119 | {
|
---|
| 120 | _cellCount++;
|
---|
| 121 | return _values.MoveNext();
|
---|
| 122 | }
|
---|
| 123 |
|
---|
| 124 | public IEnumerator<ICellInfo> GetEnumerator()
|
---|
| 125 | {
|
---|
| 126 | Reset();
|
---|
| 127 | return this;
|
---|
| 128 | }
|
---|
| 129 |
|
---|
| 130 | System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
|
---|
| 131 | {
|
---|
| 132 | return this;
|
---|
| 133 | }
|
---|
| 134 |
|
---|
| 135 |
|
---|
| 136 | public ExcelAddressBase Address
|
---|
| 137 | {
|
---|
| 138 | get { return _address; }
|
---|
| 139 | }
|
---|
| 140 |
|
---|
| 141 | public object GetValue(int row, int col)
|
---|
| 142 | {
|
---|
| 143 | return _ws.GetValue(row, col);
|
---|
| 144 | }
|
---|
| 145 |
|
---|
| 146 | public object GetOffset(int rowOffset, int colOffset)
|
---|
| 147 | {
|
---|
| 148 | if (_values.Row < _fromRow || _values.Column < _fromCol)
|
---|
| 149 | {
|
---|
| 150 | return _ws.GetValue(_fromCol + rowOffset, _fromCol + colOffset);
|
---|
| 151 | }
|
---|
| 152 | else
|
---|
| 153 | {
|
---|
| 154 | return _ws.GetValue(_values.Row + rowOffset, _values.Column + colOffset);
|
---|
| 155 | }
|
---|
| 156 | }
|
---|
| 157 | }
|
---|
| 158 | public class CellInfo : ICellInfo
|
---|
| 159 | {
|
---|
| 160 | ExcelWorksheet _ws;
|
---|
| 161 | CellsStoreEnumerator<object> _values;
|
---|
| 162 | internal CellInfo(ExcelWorksheet ws, CellsStoreEnumerator<object> values)
|
---|
| 163 | {
|
---|
| 164 | _ws = ws;
|
---|
| 165 | _values = values;
|
---|
| 166 | }
|
---|
| 167 | public string Address
|
---|
| 168 | {
|
---|
| 169 | get { return _values.CellAddress; }
|
---|
| 170 | }
|
---|
| 171 |
|
---|
| 172 | public int Row
|
---|
| 173 | {
|
---|
| 174 | get { return _values.Row; }
|
---|
| 175 | }
|
---|
| 176 |
|
---|
| 177 | public int Column
|
---|
| 178 | {
|
---|
| 179 | get { return _values.Column; }
|
---|
| 180 | }
|
---|
| 181 |
|
---|
| 182 | public string Formula
|
---|
| 183 | {
|
---|
| 184 | get
|
---|
| 185 | {
|
---|
| 186 | return _ws.GetFormula(_values.Row, _values.Column);
|
---|
| 187 | }
|
---|
| 188 | }
|
---|
| 189 |
|
---|
| 190 | public object Value
|
---|
| 191 | {
|
---|
| 192 | get { return _values.Value; }
|
---|
| 193 | }
|
---|
| 194 |
|
---|
| 195 | public double ValueDouble
|
---|
| 196 | {
|
---|
| 197 | get { return ConvertUtil.GetValueDouble(_values.Value, true); }
|
---|
| 198 | }
|
---|
| 199 | public double ValueDoubleLogical
|
---|
| 200 | {
|
---|
| 201 | get { return ConvertUtil.GetValueDouble(_values.Value, false); }
|
---|
| 202 | }
|
---|
| 203 | public bool IsHiddenRow
|
---|
| 204 | {
|
---|
| 205 | get
|
---|
| 206 | {
|
---|
| 207 | var row=_ws._values.GetValue(_values.Row, 0) as RowInternal;
|
---|
| 208 | if(row != null)
|
---|
| 209 | {
|
---|
| 210 | return row.Hidden || row.Height==0;
|
---|
| 211 | }
|
---|
| 212 | else
|
---|
| 213 | {
|
---|
| 214 | return false;
|
---|
| 215 | }
|
---|
| 216 | }
|
---|
| 217 | }
|
---|
| 218 |
|
---|
| 219 | public bool IsExcelError
|
---|
| 220 | {
|
---|
| 221 | get { return ExcelErrorValue.Values.IsErrorValue(_values.Value); }
|
---|
| 222 | }
|
---|
| 223 |
|
---|
| 224 | public IList<Token> Tokens
|
---|
| 225 | {
|
---|
| 226 | get
|
---|
| 227 | {
|
---|
| 228 | return _ws._formulaTokens.GetValue(_values.Row, _values.Column);
|
---|
| 229 | }
|
---|
| 230 | }
|
---|
| 231 |
|
---|
| 232 | }
|
---|
| 233 | public class NameInfo : ExcelDataProvider.INameInfo
|
---|
| 234 | {
|
---|
| 235 | public ulong Id { get; set; }
|
---|
| 236 | public string Worksheet { get; set; }
|
---|
| 237 | public string Name { get; set; }
|
---|
| 238 | public string Formula { get; set; }
|
---|
| 239 | public IList<Token> Tokens { get; internal set; }
|
---|
| 240 | public object Value { get; set; }
|
---|
| 241 | }
|
---|
| 242 |
|
---|
| 243 | private readonly ExcelPackage _package;
|
---|
| 244 | private ExcelWorksheet _currentWorksheet;
|
---|
| 245 | private RangeAddressFactory _rangeAddressFactory;
|
---|
| 246 | private Dictionary<ulong, INameInfo> _names=new Dictionary<ulong,INameInfo>();
|
---|
| 247 |
|
---|
| 248 | public EpplusExcelDataProvider(ExcelPackage package)
|
---|
| 249 | {
|
---|
| 250 | _package = package;
|
---|
| 251 |
|
---|
| 252 | _rangeAddressFactory = new RangeAddressFactory(this);
|
---|
| 253 | }
|
---|
| 254 |
|
---|
| 255 | public override ExcelNamedRangeCollection GetWorksheetNames()
|
---|
| 256 | {
|
---|
| 257 | return _package.Workbook.Worksheets.First().Names;
|
---|
| 258 | }
|
---|
| 259 |
|
---|
| 260 | public override ExcelNamedRangeCollection GetWorkbookNameValues()
|
---|
| 261 | {
|
---|
| 262 | return _package.Workbook.Names;
|
---|
| 263 | }
|
---|
| 264 | public override IRangeInfo GetRange(string worksheet, int row, int column, string address)
|
---|
| 265 | {
|
---|
| 266 | var addr = new ExcelAddress(worksheet, address);
|
---|
| 267 | if (addr.Table != null)
|
---|
| 268 | {
|
---|
| 269 | addr.SetRCFromTable(_package, new ExcelAddressBase(row, column, row, column));
|
---|
| 270 | }
|
---|
| 271 | //SetCurrentWorksheet(addr.WorkSheet);
|
---|
| 272 | var wsName = string.IsNullOrEmpty(addr.WorkSheet) ? _currentWorksheet.Name : addr.WorkSheet;
|
---|
| 273 | var ws = _package.Workbook.Worksheets[wsName];
|
---|
| 274 | //return new CellsStoreEnumerator<object>(ws._values, addr._fromRow, addr._fromCol, addr._toRow, addr._toCol);
|
---|
| 275 | return new RangeInfo(ws, addr._fromRow, addr._fromCol, addr._toRow, addr._toCol);
|
---|
| 276 | }
|
---|
| 277 | public override INameInfo GetName(string worksheet, string name)
|
---|
| 278 | {
|
---|
| 279 | ExcelNamedRange nameItem;
|
---|
| 280 | ulong id;
|
---|
| 281 | ExcelWorksheet ws;
|
---|
| 282 | if (string.IsNullOrEmpty(worksheet))
|
---|
| 283 | {
|
---|
| 284 | if(_package._workbook.Names.ContainsKey(name))
|
---|
| 285 | {
|
---|
| 286 | nameItem = _package._workbook.Names[name];
|
---|
| 287 | }
|
---|
| 288 | else
|
---|
| 289 | {
|
---|
| 290 | return null;
|
---|
| 291 | }
|
---|
| 292 | ws = null;
|
---|
| 293 | }
|
---|
| 294 | else
|
---|
| 295 | {
|
---|
| 296 | ws = _package._workbook.Worksheets[worksheet];
|
---|
| 297 | if (ws !=null && ws.Names.ContainsKey(name))
|
---|
| 298 | {
|
---|
| 299 | nameItem = _package._workbook.Names[name];
|
---|
| 300 | }
|
---|
| 301 | else if (_package._workbook.Names.ContainsKey(name))
|
---|
| 302 | {
|
---|
| 303 | nameItem = _package._workbook.Names[name];
|
---|
| 304 | }
|
---|
| 305 | else
|
---|
| 306 | {
|
---|
| 307 | return null;
|
---|
| 308 | }
|
---|
| 309 | }
|
---|
| 310 | id = ExcelAddressBase.GetCellID(nameItem.LocalSheetId, nameItem.Index, 0);
|
---|
| 311 |
|
---|
| 312 | if (_names.ContainsKey(id))
|
---|
| 313 | {
|
---|
| 314 | return _names[id];
|
---|
| 315 | }
|
---|
| 316 | else
|
---|
| 317 | {
|
---|
| 318 | var ni = new NameInfo()
|
---|
| 319 | {
|
---|
| 320 | Id = id,
|
---|
| 321 | Name = name,
|
---|
| 322 | Worksheet = nameItem.Worksheet==null ? nameItem._ws : nameItem.Worksheet.Name,
|
---|
| 323 | Formula = nameItem.Formula
|
---|
| 324 | };
|
---|
| 325 | if (nameItem._fromRow > 0)
|
---|
| 326 | {
|
---|
| 327 | ni.Value = new RangeInfo(nameItem.Worksheet ?? ws, nameItem._fromRow, nameItem._fromCol, nameItem._toRow, nameItem._toCol);
|
---|
| 328 | }
|
---|
| 329 | else
|
---|
| 330 | {
|
---|
| 331 | ni.Value = nameItem.Value;
|
---|
| 332 | }
|
---|
| 333 | _names.Add(id, ni);
|
---|
| 334 | return ni;
|
---|
| 335 | }
|
---|
| 336 | }
|
---|
| 337 | public override IEnumerable<object> GetRangeValues(string address)
|
---|
| 338 | {
|
---|
| 339 | SetCurrentWorksheet(ExcelAddressInfo.Parse(address));
|
---|
| 340 | var addr = new ExcelAddress(address);
|
---|
| 341 | var wsName = string.IsNullOrEmpty(addr.WorkSheet) ? _currentWorksheet.Name : addr.WorkSheet;
|
---|
| 342 | var ws = _package.Workbook.Worksheets[wsName];
|
---|
| 343 | return (new CellsStoreEnumerator<object>(ws._values, addr._fromRow, addr._fromCol, addr._toRow, addr._toCol));
|
---|
| 344 | }
|
---|
| 345 |
|
---|
| 346 |
|
---|
| 347 | public object GetValue(int row, int column)
|
---|
| 348 | {
|
---|
| 349 | return _currentWorksheet._values.GetValue(row, column);
|
---|
| 350 | }
|
---|
| 351 |
|
---|
| 352 | public bool IsMerged(int row, int column)
|
---|
| 353 | {
|
---|
| 354 | return _currentWorksheet._flags.GetFlagValue(row, column, CellFlags.Merged);
|
---|
| 355 | }
|
---|
| 356 |
|
---|
| 357 | public bool IsHidden(int row, int column)
|
---|
| 358 | {
|
---|
| 359 | return _currentWorksheet.Column(column).Hidden || _currentWorksheet.Column(column).Width == 0 ||
|
---|
| 360 | _currentWorksheet.Row(row).Hidden || _currentWorksheet.Row(column).Height == 0;
|
---|
| 361 | }
|
---|
| 362 |
|
---|
| 363 | public override object GetCellValue(string sheetName, int row, int col)
|
---|
| 364 | {
|
---|
| 365 | SetCurrentWorksheet(sheetName);
|
---|
| 366 | return _currentWorksheet._values.GetValue(row, col);
|
---|
| 367 | }
|
---|
| 368 |
|
---|
| 369 | private void SetCurrentWorksheet(ExcelAddressInfo addressInfo)
|
---|
| 370 | {
|
---|
| 371 | if (addressInfo.WorksheetIsSpecified)
|
---|
| 372 | {
|
---|
| 373 | _currentWorksheet = _package.Workbook.Worksheets[addressInfo.Worksheet];
|
---|
| 374 | }
|
---|
| 375 | else if (_currentWorksheet == null)
|
---|
| 376 | {
|
---|
| 377 | _currentWorksheet = _package.Workbook.Worksheets.First();
|
---|
| 378 | }
|
---|
| 379 | }
|
---|
| 380 |
|
---|
| 381 | private void SetCurrentWorksheet(string worksheetName)
|
---|
| 382 | {
|
---|
| 383 | if (!string.IsNullOrEmpty(worksheetName))
|
---|
| 384 | {
|
---|
| 385 | _currentWorksheet = _package.Workbook.Worksheets[worksheetName];
|
---|
| 386 | }
|
---|
| 387 | else
|
---|
| 388 | {
|
---|
| 389 | _currentWorksheet = _package.Workbook.Worksheets.First();
|
---|
| 390 | }
|
---|
| 391 |
|
---|
| 392 | }
|
---|
| 393 |
|
---|
| 394 | //public override void SetCellValue(string address, object value)
|
---|
| 395 | //{
|
---|
| 396 | // var addressInfo = ExcelAddressInfo.Parse(address);
|
---|
| 397 | // var ra = _rangeAddressFactory.Create(address);
|
---|
| 398 | // SetCurrentWorksheet(addressInfo);
|
---|
| 399 | // //var valueInfo = (ICalcEngineValueInfo)_currentWorksheet;
|
---|
| 400 | // //valueInfo.SetFormulaValue(ra.FromRow + 1, ra.FromCol + 1, value);
|
---|
| 401 | // _currentWorksheet.Cells[ra.FromRow + 1, ra.FromCol + 1].Value = value;
|
---|
| 402 | //}
|
---|
| 403 |
|
---|
| 404 | public override void Dispose()
|
---|
| 405 | {
|
---|
| 406 | _package.Dispose();
|
---|
| 407 | }
|
---|
| 408 |
|
---|
| 409 | public override int ExcelMaxColumns
|
---|
| 410 | {
|
---|
| 411 | get { return ExcelPackage.MaxColumns; }
|
---|
| 412 | }
|
---|
| 413 |
|
---|
| 414 | public override int ExcelMaxRows
|
---|
| 415 | {
|
---|
| 416 | get { return ExcelPackage.MaxRows; }
|
---|
| 417 | }
|
---|
| 418 |
|
---|
| 419 | public override string GetRangeFormula(string worksheetName, int row, int column)
|
---|
| 420 | {
|
---|
| 421 | SetCurrentWorksheet(worksheetName);
|
---|
| 422 | return _currentWorksheet.GetFormula(row, column);
|
---|
| 423 | }
|
---|
| 424 |
|
---|
| 425 | public override object GetRangeValue(string worksheetName, int row, int column)
|
---|
| 426 | {
|
---|
| 427 | SetCurrentWorksheet(worksheetName);
|
---|
| 428 | return _currentWorksheet.GetValue(row, column);
|
---|
| 429 | }
|
---|
| 430 | public override string GetFormat(object value, string format)
|
---|
| 431 | {
|
---|
| 432 | var styles = _package.Workbook.Styles;
|
---|
| 433 | ExcelNumberFormatXml.ExcelFormatTranslator ft=null;
|
---|
| 434 | foreach(var f in styles.NumberFormats)
|
---|
| 435 | {
|
---|
| 436 | if(f.Format==format)
|
---|
| 437 | {
|
---|
| 438 | ft=f.FormatTranslator;
|
---|
| 439 | break;
|
---|
| 440 | }
|
---|
| 441 | }
|
---|
| 442 | if(ft==null)
|
---|
| 443 | {
|
---|
| 444 | ft=new ExcelNumberFormatXml.ExcelFormatTranslator(format, -1);
|
---|
| 445 | }
|
---|
| 446 | return ExcelRangeBase.FormatValue(value, ft,format, ft.NetFormat);
|
---|
| 447 | }
|
---|
| 448 | public override List<LexicalAnalysis.Token> GetRangeFormulaTokens(string worksheetName, int row, int column)
|
---|
| 449 | {
|
---|
| 450 | return _package.Workbook.Worksheets[worksheetName]._formulaTokens.GetValue(row, column);
|
---|
| 451 | }
|
---|
| 452 |
|
---|
| 453 | public override bool IsRowHidden(string worksheetName, int row)
|
---|
| 454 | {
|
---|
| 455 | var b = _package.Workbook.Worksheets[worksheetName].Row(row).Height == 0 ||
|
---|
| 456 | _package.Workbook.Worksheets[worksheetName].Row(row).Hidden;
|
---|
| 457 |
|
---|
| 458 | return b;
|
---|
| 459 | }
|
---|
| 460 | }
|
---|
| 461 | }
|
---|
| 462 | |
---|