Google Git
Sign in
appsheet-third-party / epplus / ea4a69b16c0474e1487c414b16243a91eb1fe4fe / . / EPPlus / ExcelWorksheet.cs
blob: 02fe01066dd41144aa955dbf796f226bf1137260 [file] [log] [blame]
/*******************************************************************************
* You may amend and distribute as you like, but don't remove this header!
*
* EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
* See http://www.codeplex.com/EPPlus for details.
*
* Copyright (C) 2011 Jan Källman
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU Lesser General Public License for more details.
*
* The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
* If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
*
* All code and executables are provided "as is" with no warranty either express or implied.
* The author accepts no liability for any damage or loss of business that this product may cause.
*
* Code change notes:
*
* Author Change Date
* ******************************************************************************
* Jan Källman Initial Release 2011-11-02
* Jan Källman Total rewrite 2010-03-01
* Jan Källman License changed GPL-->LGPL 2011-12-27
*******************************************************************************/
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Immutable;
using System.ComponentModel;
using System.Globalization;
using System.IO;
using System.Security;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;
using OfficeOpenXml.ConditionalFormatting;
using OfficeOpenXml.DataValidation;
using OfficeOpenXml.Drawing.Vml;
using OfficeOpenXml.FormulaParsing.LexicalAnalysis;
using OfficeOpenXml.Packaging;
using OfficeOpenXml.Style.XmlAccess;
using OfficeOpenXml.Table;
using OfficeOpenXml.Table.PivotTable;
using OfficeOpenXml.Utils;
namespace OfficeOpenXml;
/// <summary>
/// Worksheet hidden enumeration
/// </summary>
public enum eWorkSheetHidden {
/// <summary>
/// The worksheet is visible
/// </summary>
Visible,
/// <summary>
/// The worksheet is hidden but can be shown by the user via the user interface
/// </summary>
Hidden,
/// <summary>
/// The worksheet is hidden and cannot be shown by the user via the user interface
/// </summary>
VeryHidden,
}
[Flags]
internal enum CellFlags {
//Merged = 0x1,
RichText = 0x2,
SharedFormula = 0x4,
ArrayFormula = 0x8,
}
/// <summary>
/// Represents an Excel Chartsheet and provides access to its properties and methods
/// </summary>
public class ExcelChartsheet : ExcelWorksheet {
public ExcelChartsheet(
XmlNamespaceManager ns,
ExcelPackage pck,
ExcelWorkbook workbook,
Uri worksheetUri,
string name,
int sheetId,
int positionId,
eWorkSheetHidden hidden)
: base(ns, pck, workbook, worksheetUri, name, sheetId, positionId, hidden) {}
}
/// <summary>
/// Represents an Excel worksheet and provides access to its properties and methods
/// </summary>
public class ExcelWorksheet : XmlHelper, IEqualityComparer<ExcelWorksheet> {
internal class Formulas {
public Formulas(ISourceCodeTokenizer tokenizer) {
_tokenizer = tokenizer;
}
public static string RemoveDummyFunction(string formula) {
const string dummyFunctionConcatenate = "\"&\"";
const string dummyFunctionPrefix = "IFERROR(__xludf.DUMMYFUNCTION(\"";
const string dummyFunctionSuffix = "\"),";
if (string.IsNullOrEmpty(formula)) {
return formula;
}
// Look for Prefix
if (!formula.StartsWith(dummyFunctionPrefix)) {
return formula;
}
// Look for Suffix
int index = formula.LastIndexOf(dummyFunctionSuffix);
if (index < 0) {
return formula;
}
// Trim Suffix
formula = formula.Substring(0, index);
// Trim Prefix
formula = formula.Replace(dummyFunctionPrefix, "");
// Remove string concatentations from long formulas.
// Google break the quoted string into 254 character segments which are concatenated.
if (formula.Length >= 254) {
formula = formula.Replace(dummyFunctionConcatenate, "");
}
// Replace doubled quotes with single quote
formula = formula.Replace("\"\"", "\"");
// Return formula
return formula;
}
private readonly ISourceCodeTokenizer _tokenizer;
internal int Index { get; set; }
internal string Address { get; set; }
internal bool IsArray { get; set; }
public string Formula { get; set; }
public int StartRow { get; set; }
public int StartCol { get; set; }
private IEnumerable<Token> Tokens { get; set; }
internal string GetFormula(int row, int column, string worksheet) {
if ((StartRow == row && StartCol == column) || IsArray) {
return RemoveDummyFunction(Formula);
}
if (Tokens == null) {
Tokens = _tokenizer.Tokenize(RemoveDummyFunction(Formula), worksheet);
}
string f = "";
foreach (var token in Tokens) {
if (token.TokenType == TokenType.ExcelAddress) {
var a = new ExcelFormulaAddress(token.Value);
f += a.GetOffset(row - StartRow, column - StartCol);
} else {
f += token.Value;
}
}
return f;
}
}
/// <summary>
/// Collection containing merged cell addresses
/// </summary>
public class MergeCellsCollection : IEnumerable<string> {
internal MergeCellsCollection() {}
internal readonly CellStore<int> _cells = new();
private readonly List<string> _list = new();
internal List<string> List => _list;
public string this[int row, int column] {
get {
int ix = -1;
if (_cells.Exists(row, column, ref ix)
&& ix >= 0
&& ix
< List.Count) //Fixes issue 15075
{
return List[ix];
}
return null;
}
}
public string this[int index] => _list[index];
internal void Add(ExcelAddressBase address, bool doValidate) {
//Validate
if (doValidate && Validate(address) == false) {
throw (new ArgumentException("Can't merge and already merged range"));
}
var ix = _list.Count;
_list.Add(address.Address);
SetIndex(address, ix);
}
private bool Validate(ExcelAddressBase address) {
int ix = 0;
if (_cells.Exists(address._fromRow, address._fromCol, ref ix)) {
if (ix >= 0 && ix < _list.Count && _list[ix] != null && address.Address == _list[ix]) {
return true;
}
return false;
}
var cse = new CellsStoreEnumerator<int>(
_cells,
address._fromRow,
address._fromCol,
address._toRow,
address._toCol);
//cells
while (cse.Next()) {
return false;
}
//Entire column
cse = new(_cells, 0, address._fromCol, 0, address._toCol);
while (cse.Next()) {
return false;
}
//Entire row
cse = new(_cells, address._fromRow, 0, address._toRow, 0);
while (cse.Next()) {
return false;
}
return true;
}
internal void SetIndex(ExcelAddressBase address, int ix) {
if (address._fromRow == 1
&& address._toRow
== ExcelPackage.MaxRows) //Entire row
{
for (int col = address._fromCol; col <= address._toCol; col++) {
_cells.SetValue(0, col, ix);
}
} else if (address._fromCol == 1
&& address._toCol
== ExcelPackage.MaxColumns) //Entire row
{
for (int row = address._fromRow; row <= address._toRow; row++) {
_cells.SetValue(row, 0, ix);
}
} else {
for (int col = address._fromCol; col <= address._toCol; col++) {
for (int row = address._fromRow; row <= address._toRow; row++) {
_cells.SetValue(row, col, ix);
}
}
}
}
public int Count => _list.Count;
internal void Remove(string item) {
_list.Remove(item);
}
public IEnumerator<string> GetEnumerator() {
return _list.GetEnumerator();
}
IEnumerator IEnumerable.GetEnumerator() {
return _list.GetEnumerator();
}
internal void Clear(ExcelAddressBase destination) {
var cse = new CellsStoreEnumerator<int>(
_cells,
destination._fromRow,
destination._fromCol,
destination._toRow,
destination._toCol);
var used = new HashSet<int>();
while (cse.Next()) {
var v = cse.Value;
if (!used.Contains(v) && _list[v] != null) {
var adr = new ExcelAddressBase(_list[v]);
if (!(destination.Collide(adr) == ExcelAddressBase.eAddressCollition.Inside
|| destination.Collide(adr) == ExcelAddressBase.eAddressCollition.Equal)) {
throw (new InvalidOperationException(
string.Format(
"Can't delete merged cells. A range is partly merged with the deleted range. {0}",
adr._address)));
}
used.Add(v);
}
}
_cells.Clear(
destination._fromRow,
destination._fromCol,
destination._toRow - destination._fromRow + 1,
destination._toCol - destination._fromCol + 1);
foreach (var i in used) {
_list[i] = null;
}
}
}
internal readonly CellStore<object> _values = new();
internal readonly CellStore<string> _types = new();
internal readonly CellStore<int> _styles = new();
internal readonly CellStore<object> _formulas = new();
internal readonly FlagCellStore _flags = new();
internal CellStore<List<Token>> _formulaTokens;
internal readonly CellStore<Uri> _hyperLinks = new();
internal readonly CellStore<ExcelComment> _commentsStore = new();
internal readonly Dictionary<int, Formulas> _sharedFormulas = new();
internal readonly ExcelPackage _package;
private readonly ExcelWorkbook _workbook;
private ExcelWorksheetView _sheetView;
internal static ImmutableArray<string> WorksheetSchemaNodeOrder = [
"sheetPr",
"tabColor",
"outlinePr",
"pageSetUpPr",
"dimension",
"sheetViews",
"sheetFormatPr",
"cols",
"sheetData",
"sheetProtection",
"protectedRanges",
"scenarios",
"autoFilter",
"sortState",
"dataConsolidate",
"customSheetViews",
"customSheetViews",
"mergeCells",
"phoneticPr",
"conditionalFormatting",
"dataValidations",
"hyperlinks",
"printOptions",
"pageMargins",
"pageSetup",
"headerFooter",
"linePrint",
"rowBreaks",
"colBreaks",
"customProperties",
"cellWatches",
"ignoredErrors",
"smartTags",
"drawing",
"legacyDrawing",
"legacyDrawingHF",
"picture",
"oleObjects",
"activeXControls",
"webPublishItems",
"tableParts",
"extLst",
];
protected override ImmutableArray<string> SchemaNodeOrder => WorksheetSchemaNodeOrder;
internal ExcelWorksheet(
XmlNamespaceManager ns,
ExcelPackage excelPackage,
ExcelWorkbook workbook,
Uri worksheetUri,
string name,
int sheetId,
int positionId,
eWorkSheetHidden hidden)
: base(ns, null) {
_workbook = workbook;
_package = excelPackage;
_names = new(Workbook, this);
Hidden = hidden;
Name = name;
PositionID = positionId;
SheetID = sheetId;
Part = _package.Package.GetPart(worksheetUri);
Part.SaveHandler = SaveHandler;
// First Columns, rows, cells, mergecells, hyperlinks and pagebreakes are loaded from an
// XmlTextReader to optimize speed.
using var stream = Part.GetStream();
var xr = new XmlTextReader(stream);
xr.ProhibitDtd = true;
xr.WhitespaceHandling = WhitespaceHandling.None;
LoadColumns(xr); //columnXml
long start = stream.Position;
LoadCells(xr);
var nextElementLength = GetAttributeLength(xr);
long end = stream.Position - nextElementLength;
LoadMergeCells(xr);
LoadHyperLinks(xr);
LoadRowPageBreakes(xr);
LoadColPageBreakes(xr);
// Then the rest of the XML is extracted and loaded into the WorksheetXml document.
stream.Seek(0, SeekOrigin.Begin);
var xml = GetWorkSheetXml(stream, start, end, out var encoding);
//first char is invalid sometimes??
if (xml[0] != '<') {
LoadXmlSafe(WorksheetXml, xml.Substring(1, xml.Length - 1), encoding);
} else {
LoadXmlSafe(WorksheetXml, xml, encoding);
}
ClearNodes();
TopNode = WorksheetXml.DocumentElement;
}
/// <summary>
/// The Zip.ZipPackagePart for the worksheet within the package
/// </summary>
internal ZipPackagePart Part { get; }
/// <summary>
/// The unique identifier for the worksheet.
/// </summary>
internal int SheetID { get; }
/// <summary>
/// The position of the worksheet.
/// </summary>
internal int PositionID { get; }
/// <summary>
/// Address for autofilter
/// <seealso cref="ExcelRangeBase.AutoFilter" />
/// </summary>
public ExcelAddressBase AutoFilterAddress {
get {
CheckSheetType();
string address = GetXmlNodeString("d:autoFilter/@ref");
if (address == "") {
return null;
}
return new(address);
}
internal set {
CheckSheetType();
SetXmlNodeString("d:autoFilter/@ref", value.Address);
}
}
internal void CheckSheetType() {
if (this is ExcelChartsheet) {
throw (new NotSupportedException(
"This property or method is not supported for a Chartsheet"));
}
}
/// <summary>
/// Returns a ExcelWorksheetView object that allows you to set the view state properties of the worksheet
/// </summary>
public ExcelWorksheetView View {
get {
if (_sheetView == null) {
XmlNode node = TopNode.SelectSingleNode("d:sheetViews/d:sheetView", NameSpaceManager);
if (node == null) {
CreateNode("d:sheetViews/d:sheetView"); //this one shouls always exist. but check anyway
node = TopNode.SelectSingleNode("d:sheetViews/d:sheetView", NameSpaceManager);
}
_sheetView = new(NameSpaceManager, node, this);
}
return (_sheetView);
}
}
/// <summary>
/// The worksheet's display name as it appears on the tab
/// </summary>
public string Name { get; }
private readonly ExcelNamedRangeCollection _names;
/// <summary>
/// Provides access to named ranges
/// </summary>
public ExcelNamedRangeCollection Names {
get {
CheckSheetType();
return _names;
}
}
/// <summary>
/// Indicates if the worksheet is hidden in the workbook
/// </summary>
public eWorkSheetHidden Hidden {
get {
string state = _workbook.GetXmlNodeString(
string.Format("d:sheets/d:sheet[@sheetId={0}]/@state", SheetID));
if (state == "hidden") {
return eWorkSheetHidden.Hidden;
}
if (state == "veryHidden") {
return eWorkSheetHidden.VeryHidden;
}
return eWorkSheetHidden.Visible;
}
set {
if (value == eWorkSheetHidden.Visible) {
_workbook.DeleteNode(string.Format("d:sheets/d:sheet[@sheetId={0}]/@state", SheetID));
} else {
string v;
v = value.ToString();
v = v.Substring(0, 1).ToLower(CultureInfo.InvariantCulture) + v.Substring(1);
_workbook.SetXmlNodeString(
string.Format("d:sheets/d:sheet[@sheetId={0}]/@state", SheetID),
v);
}
}
}
private double _defaultRowHeight = double.NaN;
/// <summary>
/// Get/set the default height of all rows in the worksheet
/// </summary>
public double DefaultRowHeight {
get {
CheckSheetType();
if (double.IsNaN(_defaultRowHeight)) {
_defaultRowHeight = GetXmlNodeDouble("d:sheetFormatPr/@defaultRowHeight");
if (double.IsNaN(_defaultRowHeight)) {
_defaultRowHeight = 15; // Excel default height
}
}
return _defaultRowHeight;
}
set {
CheckSheetType();
_defaultRowHeight = value;
SetXmlNodeString(
"d:sheetFormatPr/@defaultRowHeight",
value.ToString(CultureInfo.InvariantCulture));
SetXmlNodeBool("d:sheetFormatPr/@customHeight", value != 15);
if (double.IsNaN(GetXmlNodeDouble("d:sheetFormatPr/@defaultColWidth"))) {
DefaultColWidth = 9.140625;
}
}
}
/// <summary>
/// Get/set the default width of all rows in the worksheet
/// </summary>
public double DefaultColWidth {
get {
CheckSheetType();
double ret = GetXmlNodeDouble("d:sheetFormatPr/@defaultColWidth");
if (double.IsNaN(ret)) {
ret = 9.140625; // Excel's default width
}
return ret;
}
set {
CheckSheetType();
SetXmlNodeString(
"d:sheetFormatPr/@defaultColWidth",
value.ToString(CultureInfo.InvariantCulture));
if (double.IsNaN(GetXmlNodeDouble("d:sheetFormatPr/@defaultRowHeight"))) {
DefaultRowHeight = 15;
}
}
}
/** <outlinePr applyStyles="1" summaryBelow="0" summaryRight="0" /> **/
private const string _outLineSummaryBelowPath = "d:sheetPr/d:outlinePr/@summaryBelow";
/// <summary>
/// Summary rows below details
/// </summary>
public bool OutLineSummaryBelow {
get {
CheckSheetType();
return GetXmlNodeBool(_outLineSummaryBelowPath);
}
set {
CheckSheetType();
SetXmlNodeString(_outLineSummaryBelowPath, value ? "1" : "0");
}
}
private const string _outLineSummaryRightPath = "d:sheetPr/d:outlinePr/@summaryRight";
/// <summary>
/// Summary rows to right of details
/// </summary>
public bool OutLineSummaryRight {
get {
CheckSheetType();
return GetXmlNodeBool(_outLineSummaryRightPath);
}
set {
CheckSheetType();
SetXmlNodeString(_outLineSummaryRightPath, value ? "1" : "0");
}
}
private const string _outLineApplyStylePath = "d:sheetPr/d:outlinePr/@applyStyles";
/// <summary>
/// Automatic styles
/// </summary>
public bool OutLineApplyStyle {
get {
CheckSheetType();
return GetXmlNodeBool(_outLineApplyStylePath);
}
set {
CheckSheetType();
SetXmlNodeString(_outLineApplyStylePath, value ? "1" : "0");
}
}
private const string _codeModuleNamePath = "d:sheetPr/@codeName";
internal string CodeModuleName {
get => GetXmlNodeString(_codeModuleNamePath);
set => SetXmlNodeString(_codeModuleNamePath, value);
}
/// <summary>
/// The XML document holding the worksheet data.
/// All column, row, cell, pagebreak, merged cell and hyperlink-data are loaded into memory and removed from the document when loading the document.
/// </summary>
internal XmlDocument WorksheetXml { get; } = new();
internal ExcelVmlDrawingCommentCollection _vmlDrawings;
/// <summary>
/// Vml drawings. underlaying object for comments
/// </summary>
internal ExcelVmlDrawingCommentCollection VmlDrawingsComments {
get {
if (_vmlDrawings == null) {
CreateVmlCollection();
}
return _vmlDrawings;
}
}
internal ExcelCommentCollection _comments;
/// <summary>
/// Collection of comments
/// </summary>
public ExcelCommentCollection Comments {
get {
CheckSheetType();
if (_comments == null) {
CreateVmlCollection();
_comments = new(_package, this, NameSpaceManager);
}
return _comments;
}
}
private void CreateVmlCollection() {
var vmlNode = WorksheetXml.DocumentElement.SelectSingleNode(
"d:legacyDrawing/@r:id",
NameSpaceManager);
if (vmlNode == null) {
_vmlDrawings = new(_package, this, null);
} else {
if (Part.RelationshipExists(vmlNode.Value)) {
var rel = Part.GetRelationship(vmlNode.Value);
var vmlUri = UriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri);
_vmlDrawings = new(_package, this, vmlUri);
_vmlDrawings.RelId = rel.Id;
}
}
}
/// <summary>
/// Get the lenth of the attributes
/// Conditional formatting attributes can be extremly long som get length of the attributes to finetune position.
/// </summary>
/// <param name="xr"></param>
/// <returns></returns>
private int GetAttributeLength(XmlTextReader xr) {
if (xr.NodeType != XmlNodeType.Element) {
return 0;
}
var length = 0;
for (int i = 0; i < xr.AttributeCount; i++) {
var a = xr.GetAttribute(i);
length += string.IsNullOrEmpty(a) ? 0 : a.Length;
}
return length;
}
private void LoadRowPageBreakes(XmlTextReader xr) {
if (!ReadUntil(xr, "rowBreaks", "colBreaks")) {
return;
}
while (xr.Read()) {
if (xr.LocalName == "brk") {
if (xr.NodeType == XmlNodeType.Element) {
if (int.TryParse(xr.GetAttribute("id"), out var id)) {
Row(id).PageBreak = true;
}
}
} else {
break;
}
}
}
private void LoadColPageBreakes(XmlTextReader xr) {
if (!ReadUntil(xr, "colBreaks")) {
return;
}
while (xr.Read()) {
if (xr.LocalName == "brk") {
if (xr.NodeType == XmlNodeType.Element) {
if (int.TryParse(xr.GetAttribute("id"), out var id)) {
Column(id).PageBreak = true;
}
}
} else {
break;
}
}
}
private void ClearNodes() {
if (WorksheetXml.SelectSingleNode("//d:cols", NameSpaceManager) != null) {
WorksheetXml.SelectSingleNode("//d:cols", NameSpaceManager).RemoveAll();
}
if (WorksheetXml.SelectSingleNode("//d:mergeCells", NameSpaceManager) != null) {
WorksheetXml.SelectSingleNode("//d:mergeCells", NameSpaceManager).RemoveAll();
}
if (WorksheetXml.SelectSingleNode("//d:hyperlinks", NameSpaceManager) != null) {
WorksheetXml.SelectSingleNode("//d:hyperlinks", NameSpaceManager).RemoveAll();
}
if (WorksheetXml.SelectSingleNode("//d:rowBreaks", NameSpaceManager) != null) {
WorksheetXml.SelectSingleNode("//d:rowBreaks", NameSpaceManager).RemoveAll();
}
if (WorksheetXml.SelectSingleNode("//d:colBreaks", NameSpaceManager) != null) {
WorksheetXml.SelectSingleNode("//d:colBreaks", NameSpaceManager).RemoveAll();
}
}
private const int _blocksize = 8192;
private string GetWorkSheetXml(Stream stream, long start, long end, out Encoding encoding) {
StreamReader sr = new StreamReader(stream);
int length = 0;
char[] block;
int pos;
StringBuilder sb = new StringBuilder();
Match startmMatch,
endMatch;
do {
int size = stream.Length < _blocksize ? (int)stream.Length : _blocksize;
block = new char[size];
pos = sr.ReadBlock(block, 0, size);
sb.Append(block, 0, pos);
length += size;
} while (length < start + 20 && length < end);
startmMatch = Regex.Match(sb.ToString(), string.Format("(<[^>]*{0}[^>]*>)", "sheetData"));
if (!startmMatch.Success) //Not found
{
encoding = sr.CurrentEncoding;
return sb.ToString();
}
string s = sb.ToString();
string xml = s.Substring(0, startmMatch.Index);
if (startmMatch.Value.EndsWith("/>")) {
xml += s.Substring(startmMatch.Index, s.Length - startmMatch.Index);
} else {
if (sr.Peek() != -1) {
/**** Fixes issue 14788. Fix by Philip Garrett ****/
long endSeekStart = end;
while (endSeekStart >= 0) {
endSeekStart = Math.Max(endSeekStart - _blocksize, 0);
int size = (int)(end - endSeekStart);
stream.Seek(endSeekStart, SeekOrigin.Begin);
block = new char[size];
sr = new(stream);
pos = sr.ReadBlock(block, 0, size);
sb = new();
sb.Append(block, 0, pos);
s = sb.ToString();
endMatch = Regex.Match(s, string.Format("(</[^>]*{0}[^>]*>)", "sheetData"));
if (endMatch.Success) {
break;
}
}
}
endMatch = Regex.Match(s, string.Format("(</[^>]*{0}[^>]*>)", "sheetData"));
xml +=
"<sheetData/>"
+ s.Substring(
endMatch.Index + endMatch.Length,
s.Length - (endMatch.Index + endMatch.Length));
}
if (sr.Peek() > -1) {
xml += sr.ReadToEnd();
}
encoding = sr.CurrentEncoding;
return xml;
}
private void GetBlockPos(string xml, string tag, ref int start, ref int end) {
Match startmMatch,
endMatch;
startmMatch = Regex.Match(xml.Substring(start), string.Format("(<[^>]*{0}[^>]*>)", tag)); //"<[a-zA-Z:]*" + tag + "[?]*>");
if (!startmMatch.Success) //Not found
{
start = -1;
end = -1;
return;
}
var startPos = startmMatch.Index + start;
if (startmMatch.Value.Substring(startmMatch.Value.Length - 2, 1) == "/") {
end = startPos + startmMatch.Length;
} else {
endMatch = Regex.Match(xml.Substring(start), string.Format("(</[^>]*{0}[^>]*>)", tag));
if (endMatch.Success) {
end = endMatch.Index + endMatch.Length + start;
}
}
start = startPos;
}
private bool ReadUntil(XmlTextReader xr, params string[] tagName) {
if (xr.EOF) {
return false;
}
while (!Array.Exists(tagName, tag => xr.LocalName.EndsWith(tag))) {
xr.Read();
if (xr.EOF) {
return false;
}
}
return (xr.LocalName.EndsWith(tagName[0]));
}
private void LoadColumns(
XmlTextReader xr) //(string xml)
{
var colList = new List<IRangeId>();
if (ReadUntil(xr, "cols", "sheetData")) {
//if (xml != "")
//{
//var xr=new XmlTextReader(new StringReader(xml));
while (xr.Read()) {
if (xr.NodeType == XmlNodeType.Whitespace) {
continue;
}
if (xr.LocalName != "col") {
break;
}
if (xr.NodeType == XmlNodeType.Element) {
int min = int.Parse(xr.GetAttribute("min"));
ExcelColumn col = new ExcelColumn(this, min);
col.ColumnMax = int.Parse(xr.GetAttribute("max"));
col.Width =
xr.GetAttribute("width") == null
? 0
: double.Parse(xr.GetAttribute("width"), CultureInfo.InvariantCulture);
col.BestFit =
xr.GetAttribute("bestFit") != null && xr.GetAttribute("bestFit") == "1"
? true
: false;
col.Collapsed =
xr.GetAttribute("collapsed") != null && xr.GetAttribute("collapsed") == "1"
? true
: false;
col.Phonetic =
xr.GetAttribute("phonetic") != null && xr.GetAttribute("phonetic") == "1"
? true
: false;
col.OutlineLevel = (short)(xr.GetAttribute("outlineLevel") == null
? 0
: int.Parse(xr.GetAttribute("outlineLevel"), CultureInfo.InvariantCulture));
col.Hidden =
xr.GetAttribute("hidden") != null && xr.GetAttribute("hidden") == "1" ? true : false;
_values.SetValue(0, min, col);
if (!(xr.GetAttribute("style") == null
|| !int.TryParse(xr.GetAttribute("style"), out var style))) {
_styles.SetValue(0, min, style);
}
}
}
}
}
/// <summary>
/// Load Hyperlinks
/// </summary>
/// <param name="xr">The reader</param>
private void LoadHyperLinks(XmlTextReader xr) {
if (!ReadUntil(xr, "hyperlinks", "rowBreaks", "colBreaks")) {
return;
}
while (xr.Read()) {
if (xr.LocalName == "hyperlink") {
ExcelCellBase.GetRowColFromAddress(
xr.GetAttribute("ref"),
out var fromRow,
out var fromCol,
out int toRow,
out var toCol);
ExcelHyperLink hl = null;
if (xr.GetAttribute("id", ExcelPackage._schemaRelationships) != null) {
var rId = xr.GetAttribute("id", ExcelPackage._schemaRelationships);
var uri = Part.GetRelationship(rId).TargetUri;
// Get Location, if any. EPPlus Bug 15517
var location = xr.GetAttribute("location");
location = (string.IsNullOrEmpty(location)) ? "" : "#" + location;
if (uri.IsAbsoluteUri) {
try {
hl = new(uri.AbsoluteUri + location);
} catch {
hl = new(uri.OriginalString + location, UriKind.Absolute);
}
} else {
hl = new(uri.OriginalString + location, UriKind.Relative);
}
hl.RId = rId;
Part.DeleteRelationship(rId); //Delete the relationship, it is recreated when we save the package.
} else if (xr.GetAttribute("location") != null) {
hl = new(xr.GetAttribute("location"), xr.GetAttribute("display"));
hl.RowSpann = toRow - fromRow;
hl.ColSpann = toCol - fromCol;
}
string tt = xr.GetAttribute("tooltip");
if (!string.IsNullOrEmpty(tt)) {
hl.ToolTip = tt;
}
_hyperLinks.SetValue(fromRow, fromCol, hl);
} else {
break;
}
}
}
/// <summary>
/// Load cells
/// </summary>
/// <param name="xr">The reader</param>
private void LoadCells(XmlTextReader xr) {
//var cellList=new List<IRangeID>();
//var rowList = new List<IRangeID>();
//var formulaList = new List<IRangeID>();
ReadUntil(xr, "sheetData", "mergeCells", "hyperlinks", "rowBreaks", "colBreaks");
ExcelAddressBase address = null;
string type = "";
int style = 0;
int row = 0;
int col = 0;
xr.Read();
while (!xr.EOF) {
while (xr.NodeType == XmlNodeType.EndElement) {
xr.Read();
}
if (xr.LocalName == "row") {
var r = xr.GetAttribute("r");
if (r == null) {
row++;
} else {
row = Convert.ToInt32(r);
}
if (DoAddRow(xr)) {
_values.SetValue(row, 0, AddRow(xr, row));
if (xr.GetAttribute("s") != null) {
_styles.SetValue(row, 0, int.Parse(xr.GetAttribute("s"), CultureInfo.InvariantCulture));
}
}
xr.Read();
} else if (xr.LocalName == "c") {
//if (cell != null) cellList.Add(cell);
//cell = new ExcelCell(this, xr.GetAttribute("r"));
var r = xr.GetAttribute("r");
if (r == null) {
//Handle cells with no reference
col++;
address = new(row, col, row, col);
} else {
address = new(r);
col = address._fromCol;
}
//Datetype
if (xr.GetAttribute("t") != null) {
type = xr.GetAttribute("t");
_types.SetValue(address._fromRow, address._fromCol, type);
} else {
type = "";
}
//Style
if (xr.GetAttribute("s") != null) {
style = int.Parse(xr.GetAttribute("s"));
_styles.SetValue(address._fromRow, address._fromCol, style);
_values.SetValue(address._fromRow, address._fromCol, null); //TODO:Better Performance ??
} else {
style = 0;
}
xr.Read();
} else if (xr.LocalName == "v") {
SetValueFromXml(xr, type, style, address._fromRow, address._fromCol);
xr.Read();
} else if (xr.LocalName == "f") {
string t = xr.GetAttribute("t");
if (t == null) {
_formulas.SetValue(address._fromRow, address._fromCol, xr.ReadElementContentAsString());
_values.SetValue(address._fromRow, address._fromCol, null);
//formulaList.Add(cell);
} else if (t == "shared") {
string si = xr.GetAttribute("si");
if (si != null) {
var sfIndex = int.Parse(si);
_formulas.SetValue(address._fromRow, address._fromCol, sfIndex);
_values.SetValue(address._fromRow, address._fromCol, null);
string fAddress = xr.GetAttribute("ref");
string formula = ConvertUtil.ExcelDecodeString(xr.ReadElementContentAsString());
if (formula != "") {
_sharedFormulas.Add(
sfIndex,
new(SourceCodeTokenizer.Default) {
Index = sfIndex,
Formula = formula,
Address = fAddress,
StartRow = address._fromRow,
StartCol = address._fromCol,
});
}
} else {
xr.Read(); //Something is wrong in the sheet, read next
}
} else if (t
== "array") //TODO: Array functions are not support yet. Read the formula for the start cell only.
{
string aAddress = xr.GetAttribute("ref");
ExcelRange addressRange = new ExcelRange(this, aAddress);
string formula = xr.ReadElementContentAsString();
bool isIndexMatchFormula =
Regex.IsMatch(formula, @"INDEX\(", RegexOptions.IgnoreCase)
&& Regex.IsMatch(formula, @"MATCH\(", RegexOptions.IgnoreCase)
&& !aAddress.Contains(":");
if (isIndexMatchFormula) {
addressRange.IsArrayFormula = false;
for (int colIndex = addressRange.Start.Column;
colIndex <= addressRange.End.Column;
colIndex++) {
for (int rowIndex = addressRange.Start.Row;
rowIndex <= addressRange.End.Row;
rowIndex++) {
var afIndex = GetMaxShareFunctionIndex(true);
_formulas.SetValue(rowIndex, colIndex, afIndex);
_values.SetValue(rowIndex, colIndex, null);
_sharedFormulas.Add(
afIndex,
new(SourceCodeTokenizer.Default) {
Index = afIndex,
Formula = formula,
Address = aAddress,
StartRow = address._fromRow,
StartCol = address._fromCol,
IsArray = false,
});
}
}
} else {
addressRange.IsArrayFormula = true;
var afIndex = GetMaxShareFunctionIndex(true);
for (int colIndex = addressRange.Start.Column;
colIndex <= addressRange.End.Column;
colIndex++) {
for (int rowIndex = addressRange.Start.Row;
rowIndex <= addressRange.End.Row;
rowIndex++) {
_formulas.SetValue(rowIndex, colIndex, afIndex);
_values.SetValue(rowIndex, colIndex, null);
}
}
_sharedFormulas.Add(
afIndex,
new(SourceCodeTokenizer.Default) {
Index = afIndex,
Formula = formula,
Address = aAddress,
StartRow = address._fromRow,
StartCol = address._fromCol,
IsArray = true,
});
}
} else // ??? some other type
{
xr.Read(); //Something is wrong in the sheet, read next
}
} else if (xr.LocalName
== "is") //Inline string
{
xr.Read();
if (xr.LocalName == "t") {
_values.SetValue(
address._fromRow,
address._fromCol,
ConvertUtil.ExcelDecodeString(xr.ReadElementContentAsString()));
//cell._value = xr.ReadInnerXml();
} else {
_values.SetValue(address._fromRow, address._fromCol, xr.ReadOuterXml());
_types.SetValue(address._fromRow, address._fromCol, "rt");
_flags.SetFlagValue(address._fromRow, address._fromCol, true, CellFlags.RichText);
//cell.IsRichText = true;
}
} else {
break;
}
}
}
private bool DoAddRow(XmlTextReader xr) {
var c = xr.GetAttribute("r") == null ? 0 : 1;
if (xr.GetAttribute("spans") != null) {
c++;
}
return xr.AttributeCount > c;
}
/// <summary>
/// Load merged cells
/// </summary>
/// <param name="xr"></param>
private void LoadMergeCells(XmlTextReader xr) {
if (ReadUntil(xr, "mergeCells", "hyperlinks", "rowBreaks", "colBreaks") && !xr.EOF) {
while (xr.Read()) {
if (xr.LocalName != "mergeCell") {
break;
}
if (xr.NodeType == XmlNodeType.Element) {
string address = xr.GetAttribute("ref");
_mergedCells.Add(new ExcelAddress(address), false);
}
}
}
}
/// <summary>
/// Update merged cells
/// </summary>
/// <param name="sw">The writer</param>
private void UpdateMergedCells(StreamWriter sw) {
sw.Write("<mergeCells>");
foreach (string address in _mergedCells) {
sw.Write("<mergeCell ref=\"{0}\" />", address);
}
sw.Write("</mergeCells>");
}
/// <summary>
/// Reads a row from the XML reader
/// </summary>
/// <param name="xr">The reader</param>
/// <param name="row">The row number</param>
/// <returns></returns>
private RowInternal AddRow(XmlTextReader xr, int row) {
return new() {
Collapsed =
(xr.GetAttribute("collapsed") != null && xr.GetAttribute("collapsed") == "1"
? true
: false),
OutlineLevel =
(xr.GetAttribute("outlineLevel") == null
? (short)0
: short.Parse(xr.GetAttribute("outlineLevel"), CultureInfo.InvariantCulture)),
Height =
(xr.GetAttribute("ht") == null
? -1
: double.Parse(xr.GetAttribute("ht"), CultureInfo.InvariantCulture)),
Hidden =
(xr.GetAttribute("hidden") != null && xr.GetAttribute("hidden") == "1" ? true : false),
Phonetic = xr.GetAttribute("ph") != null && xr.GetAttribute("ph") == "1" ? true : false,
CustomHeight =
xr.GetAttribute("customHeight") == null ? false : xr.GetAttribute("customHeight") == "1",
};
}
private static readonly DateTime _excelEpoch = new(1899, 12, 30);
public static DateTime IncorrectDurationFromOaDate(double value) {
// This behavior is wrong. Real OADate values have a discontinuity on 30 December 1899.
// For real OADate values, the negative sign applies only to the integer portion of
// the float, *not* to the decimal portion. For example, -0.5 and 0.5 both refer to the
// same date, and -1.5 is actually 1899-12-29 12:00 (1 day before 1899-12-30 00:00
// plus 0.5 days), *not* 1899-12-28 12:00 (1.5 days before 1899-12-30 00:00).
//
// Unfortunately, AppSheet's duration-handling code gets this very wrong, and treats the
// duration as the offset from 1899-12-30 00:00. This is correct for positive durations,
// but it's wrong for negative durations. This code tries to fix the bug that exists in
// AppSheet's duration-handling code here, and it succeeds in some cases and fails in
// others.
//
// This code also breaks date/time handling for dates before 1899-12-30 00:00 in some
// cases. Specifically, dates end up being offset by one day.
//
// Regardless, changing this behavior is risky, so this code simply replicates the
// existing behavior for
if (value >= 0) {
return DateTime.FromOADate(value);
}
// This looks like a very complicated way to call TimeSpan.FromDays(value), but
// TimeSpan.FromDays actually only guarantees millisecond precision, and critically
// rounding is different on .NET Core, resulting in values like (e.g.) 3:15:00 being
// incorrectly rounded.
var offset = DateTime.FromOADate(-value) - _excelEpoch;
return _excelEpoch - offset;
}
private void SetValueFromXml(XmlTextReader xr, string type, int styleId, int row, int col) {
//XmlNode vnode = colNode.SelectSingleNode("d:v", NameSpaceManager);
//if (vnode == null) return null;
if (type == "s") {
int ix = xr.ReadElementContentAsInt();
// Temporary debugging code to locate intermittent 'Index was out of range' exception.
if (ix < 0) {
throw new(
string.Format(
"ReadElementContentAsInt returned value '{0}' which is less than zero.",
ix));
}
if (ix >= _workbook._sharedStringsList.Count) {
throw new(
string.Format(
"ReadElementContentAsInt returned index value '{0}' which is greater than _sharedStringsList count of {1}.",
ix,
_workbook._sharedStringsList.Count));
}
_values.SetValue(row, col, _workbook._sharedStringsList[ix].Text);
if (_workbook._sharedStringsList[ix].isRichText) {
_flags.SetFlagValue(row, col, true, CellFlags.RichText);
}
} else if (type == "str") {
_values.SetValue(row, col, ConvertUtil.ExcelDecodeString(xr.ReadElementContentAsString()));
} else if (type == "b") {
_values.SetValue(row, col, (xr.ReadElementContentAsString() != "0"));
} else if (type == "e") {
_values.SetValue(row, col, GetErrorType(xr.ReadElementContentAsString()));
} else {
string v = xr.ReadElementContentAsString();
var nf = Workbook.Styles.CellXfs[styleId].NumberFormatId;
if ((nf >= 20 && nf <= 21)
|| (nf >= 45
&& nf
<= 47)) // Duration
{
if (double.TryParse(v, NumberStyles.Any, CultureInfo.InvariantCulture, out var res)) {
if (Workbook.Date1904) {
res += ExcelWorkbook._date1904Offset;
}
if (res >= -657435.0 && res < 2958465.9999999) {
// Get the Duration value expressed as a DateTime.
_values.SetValue(row, col, IncorrectDurationFromOaDate(res));
} else {
// Cope with Google Sheets export of cells having a formula.
// Rather than exporting the native value, they export the formatted value.
_values.SetValue(row, col, v);
}
} else {
// Cope with Google Sheets export of cells having a formula.
// Rather than exporting the native value, they export the formatted value.
_values.SetValue(row, col, v);
}
} else if ((nf >= 14 && nf <= 19)
|| (nf
== 22)) // DateTime
{
if (double.TryParse(v, NumberStyles.Any, CultureInfo.InvariantCulture, out var res)) {
if (Workbook.Date1904) {
res += ExcelWorkbook._date1904Offset;
}
if (res >= -657435.0 && res < 2958465.9999999) {
_values.SetValue(row, col, DateTime.FromOADate(res));
} else {
// Cope with Google Sheets export of cells having a formula.
// Rather than exporting the native value, they export the formatted value.
_values.SetValue(row, col, v);
}
} else {
// Cope with Google Sheets export of cells having a formula.
// Rather than exporting the native value, they export the formatted value.
_values.SetValue(row, col, v);
}
} else {
if (double.TryParse(v, NumberStyles.Any, CultureInfo.InvariantCulture, out var d)) {
_values.SetValue(row, col, d);
} else {
// Cope with Google Sheets export of cells having a formula.
// Rather than exporting the native value, they export the formatted value.
_values.SetValue(row, col, v);
//_values.SetValue(row, col, double.NaN);
}
}
}
}
private object GetErrorType(string v) {
return ExcelErrorValue.Parse(v.ToUpper(CultureInfo.InvariantCulture));
//switch(v.ToUpper())
//{
// case "#DIV/0!":
// return new ExcelErrorValue.cre(eErrorType.Div0);
// case "#REF!":
// return new ExcelErrorValue(eErrorType.Ref);
// case "#N/A":
// return new ExcelErrorValue(eErrorType.NA);
// case "#NAME?":
// return new ExcelErrorValue(eErrorType.Name);
// case "#NULL!":
// return new ExcelErrorValue(eErrorType.Null);
// case "#NUM!":
// return new ExcelErrorValue(eErrorType.Num);
// default:
// return new ExcelErrorValue(eErrorType.Value);
//}
}
///// <summary>
///// Provides access to an individual cell within the worksheet.
///// </summary>
///// <param name="row">The row number in the worksheet</param>
///// <param name="col">The column number in the worksheet</param>
///// <returns></returns>
//internal ExcelCell Cell(int row, int col)
//{
// return new ExcelCell(_values, row, col);
//}
/// <summary>
/// Provides access to a range of cells
/// </summary>
public ExcelRange Cells {
get {
CheckSheetType();
return new(this, 1, 1, ExcelPackage.MaxRows, ExcelPackage.MaxColumns);
}
}
/// <summary>
/// Provides access to the selected range of cells
/// </summary>
public ExcelRange SelectedRange {
get {
CheckSheetType();
return new(this, View.SelectedRange);
}
}
private readonly MergeCellsCollection _mergedCells = new();
/// <summary>
/// Addresses to merged ranges
/// </summary>
public MergeCellsCollection MergedCells {
get {
CheckSheetType();
return _mergedCells;
}
}
/// <summary>
/// Provides access to an individual row within the worksheet so you can set its properties.
/// </summary>
/// <param name="row">The row number in the worksheet</param>
/// <returns></returns>
public ExcelRow Row(int row) {
//ExcelRow r;
//ulong id = ExcelRow.GetRowID(_sheetID, row);
//TODO: Fixa.
//var v = _values.GetValue(row, 0);
//if (v!=null)
//{
// var ri=(RowInternal)v;
// r = new ExcelRow(this, row)
//}
//else
//{
//r = new ExcelRow(this, row);
//_values.SetValue(row, 0, r);
//_rows.Add(r);
//}
CheckSheetType();
if (row < 1 || row > ExcelPackage.MaxRows) {
throw (new ArgumentException("Row number out of bounds"));
}
return new(this, row);
//return r;
}
/// <summary>
/// Provides access to an individual column within the worksheet so you can set its properties.
/// </summary>
/// <param name="col">The column number in the worksheet</param>
/// <returns></returns>
public ExcelColumn Column(int col) {
CheckSheetType();
if (col < 1 || col > ExcelPackage.MaxColumns) {
throw (new ArgumentException("Column number out of bounds"));
}
var column = _values.GetValue(0, col) as ExcelColumn;
if (column != null) {
if (column.ColumnMin != column.ColumnMax) {
int maxCol = column.ColumnMax;
column.ColumnMax = col;
ExcelColumn copy = CopyColumn(column, col + 1, maxCol);
}
} else {
int r = 0,
c = col;
if (_values.PrevCell(ref r, ref c)) {
column = _values.GetValue(0, c) as ExcelColumn;
int maxCol = column.ColumnMax;
if (maxCol >= col) {
column.ColumnMax = col - 1;
if (maxCol > col) {
ExcelColumn newC = CopyColumn(column, col + 1, maxCol);
}
return CopyColumn(column, col, col);
}
}
//foreach (ExcelColumn checkColumn in _columns)
//{
// if (col > checkColumn.ColumnMin && col <= checkColumn.ColumnMax)
// {
// int maxCol = checkColumn.ColumnMax;
// checkColumn.ColumnMax = col - 1;
// if (maxCol > col)
// {
// ExcelColumn newC = CopyColumn(checkColumn, col + 1, maxCol);
// }
// return CopyColumn(checkColumn, col,col);
// }
//}
column = new(this, col);
_values.SetValue(0, col, column);
//_columns.Add(column);
}
return column;
}
/// <summary>
/// Returns the name of the worksheet
/// </summary>
/// <returns>The name of the worksheet</returns>
public override string ToString() {
return Name;
}
internal ExcelColumn CopyColumn(ExcelColumn c, int col, int maxCol) {
ExcelColumn newC = new ExcelColumn(this, col);
newC.ColumnMax = maxCol < ExcelPackage.MaxColumns ? maxCol : ExcelPackage.MaxColumns;
if (c.StyleName != "") {
newC.StyleName = c.StyleName;
} else {
newC.StyleID = c.StyleID;
}
newC.OutlineLevel = c.OutlineLevel;
newC.Phonetic = c.Phonetic;
newC.BestFit = c.BestFit;
//_columns.Add(newC);
_values.SetValue(0, col, newC);
newC._width = c._width;
newC._hidden = c._hidden;
return newC;
}
/// <summary>
/// Make the current worksheet active.
/// </summary>
public void Select() {
View.TabSelected = true;
//Select(Address, true);
}
/// <summary>
/// Selects a range in the worksheet. The active cell is the topmost cell.
/// Make the current worksheet active.
/// </summary>
/// <param name="address">An address range</param>
public void Select(string address) {
Select(address, true);
}
/// <summary>
/// Selects a range in the worksheet. The actice cell is the topmost cell.
/// </summary>
/// <param name="address">A range of cells</param>
/// <param name="selectSheet">Make the sheet active</param>
public void Select(string address, bool selectSheet) {
CheckSheetType();
int toCol,
toRow;
//Get rows and columns and validate as well
ExcelCellBase.GetRowColFromAddress(
address,
out var fromRow,
out var fromCol,
out toRow,
out toCol);
if (selectSheet) {
View.TabSelected = true;
}
View.SelectedRange = address;
View.ActiveCell = ExcelCellBase.GetAddress(fromRow, fromCol);
}
/// <summary>
/// Selects a range in the worksheet. The active cell is the topmost cell of the first address.
/// Make the current worksheet active.
/// </summary>
/// <param name="address">An address range</param>
public void Select(ExcelAddress address) {
CheckSheetType();
Select(address, true);
}
/// <summary>
/// Selects a range in the worksheet. The active cell is the topmost cell of the first address.
/// </summary>
/// <param name="address">A range of cells</param>
/// <param name="selectSheet">Make the sheet active</param>
public void Select(ExcelAddress address, bool selectSheet) {
CheckSheetType();
if (selectSheet) {
View.TabSelected = true;
}
string selAddress =
ExcelCellBase.GetAddress(address.Start.Row, address.Start.Column)
+ ":"
+ ExcelCellBase.GetAddress(address.End.Row, address.End.Column);
if (address.Addresses != null) {
foreach (var a in address.Addresses) {
selAddress +=
" "
+ ExcelCellBase.GetAddress(a.Start.Row, a.Start.Column)
+ ":"
+ ExcelCellBase.GetAddress(a.End.Row, a.End.Column);
}
}
View.SelectedRange = selAddress;
View.ActiveCell = ExcelCellBase.GetAddress(address.Start.Row, address.Start.Column);
}
/// <summary>
/// Inserts a new row into the spreadsheet. Existing rows below the position are
/// shifted down. All formula are updated to take account of the new row.
/// </summary>
/// <param name="rowFrom">The position of the new row</param>
/// <param name="rows">Number of rows to insert</param>
public void InsertRow(int rowFrom, int rows) {
InsertRow(rowFrom, rows, 0);
}
/// <summary>
/// Inserts a new row into the spreadsheet. Existing rows below the position are
/// shifted down. All formula are updated to take account of the new row.
/// </summary>
/// <param name="rowFrom">The position of the new row</param>
/// <param name="rows">Number of rows to insert.</param>
/// <param name="copyStylesFromRow">Copy Styles from this row. Applied to all inserted rows</param>
public void InsertRow(int rowFrom, int rows, int copyStylesFromRow) {
CheckSheetType();
var d = Dimension;
if (rowFrom < 1) {
throw (new ArgumentOutOfRangeException("rowFrom can't be lesser that 1"));
}
//Check that cells aren't shifted outside the boundries
if (d != null && d.End.Row > rowFrom && d.End.Row + rows > ExcelPackage.MaxRows) {
throw (new ArgumentOutOfRangeException(
"Can't insert. Rows will be shifted outside the boundries of the worksheet."));
}
_values.Insert(rowFrom, 0, rows, 0);
_formulas.Insert(rowFrom, 0, rows, 0);
_styles.Insert(rowFrom, 0, rows, 0);
_types.Insert(rowFrom, 0, rows, 0);
_commentsStore.Insert(rowFrom, 0, rows, 0);
_hyperLinks.Insert(rowFrom, 0, rows, 0);
_flags.Insert(rowFrom, 0, rows, 0);
foreach (var f in _sharedFormulas.Values) {
if (f.StartRow >= rowFrom) {
f.StartRow += rows;
}
var a = new ExcelAddressBase(f.Address);
if (a._fromRow >= rowFrom) {
a._fromRow += rows;
a._toRow += rows;
} else if (a._toRow >= rowFrom) {
a._toRow += rows;
}
f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol);
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, rows, 0, rowFrom, 0);
}
var cse = new CellsStoreEnumerator<object>(_formulas);
while (cse.Next()) {
if (cse.Value is string) {
cse.Value = ExcelCellBase.UpdateFormulaReferences(
cse.Value.ToString(),
rows,
0,
rowFrom,
0);
}
}
FixMergedCellsRow(rowFrom, rows, false);
if (copyStylesFromRow > 0) {
var cseS = new CellsStoreEnumerator<int>(
_styles,
copyStylesFromRow,
0,
copyStylesFromRow,
ExcelPackage.MaxColumns); //Fixes issue 15068 , 15090
while (cseS.Next()) {
for (var r = 0; r < rows; r++) {
_styles.SetValue(rowFrom + r, cseS.Column, cseS.Value);
}
}
}
foreach (var tbl in Tables) {
tbl.Address = tbl.Address.AddRow(rowFrom, rows);
}
}
/// <summary>
/// Inserts a new column into the spreadsheet. Existing columns below the position are
/// shifted down. All formula are updated to take account of the new column.
/// </summary>
/// <param name="columnFrom">The position of the new column</param>
/// <param name="columns">Number of columns to insert</param>
public void InsertColumn(int columnFrom, int columns) {
InsertColumn(columnFrom, columns, 0);
}
///<summary>
/// Inserts a new column into the spreadsheet. Existing column to the left are
/// shifted. All formula are updated to take account of the new column.
/// </summary>
/// <param name="columnFrom">The position of the new column</param>
/// <param name="columns">Number of columns to insert.</param>
/// <param name="copyStylesFromColumn">Copy Styles from this column. Applied to all inserted columns</param>
public void InsertColumn(int columnFrom, int columns, int copyStylesFromColumn) {
CheckSheetType();
var d = Dimension;
if (columnFrom < 1) {
throw (new ArgumentOutOfRangeException("columnFrom can't be lesser that 1"));
}
//Check that cells aren't shifted outside the boundries
if (d != null
&& d.End.Column > columnFrom
&& d.End.Column + columns > ExcelPackage.MaxColumns) {
throw (new ArgumentOutOfRangeException(
"Can't insert. Columns will be shifted outside the boundries of the worksheet."));
}
_values.Insert(0, columnFrom, 0, columns);
_formulas.Insert(0, columnFrom, 0, columns);
_styles.Insert(0, columnFrom, 0, columns);
_types.Insert(0, columnFrom, 0, columns);
_commentsStore.Insert(0, columnFrom, 0, columns);
_hyperLinks.Insert(0, columnFrom, 0, columns);
_flags.Insert(0, columnFrom, 0, columns);
foreach (var f in _sharedFormulas.Values) {
if (f.StartCol >= columnFrom) {
f.StartCol += columns;
}
var a = new ExcelAddressBase(f.Address);
if (a._fromCol >= columnFrom) {
a._fromCol += columns;
a._toCol += columns;
} else if (a._toCol >= columnFrom) {
a._toCol += columns;
}
f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol);
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, 0, columns, 0, columnFrom);
}
var cse = new CellsStoreEnumerator<object>(_formulas);
while (cse.Next()) {
if (cse.Value is string) {
cse.Value = ExcelCellBase.UpdateFormulaReferences(
cse.Value.ToString(),
0,
columns,
0,
columnFrom);
}
}
FixMergedCellsColumn(columnFrom, columns, false);
var csec = new CellsStoreEnumerator<object>(_values, 0, 1, 0, ExcelPackage.MaxColumns);
var lst = new List<ExcelColumn>();
foreach (var col in csec) {
if (col is ExcelColumn column) {
lst.Add(column);
}
}
for (int i = lst.Count - 1; i >= 0; i--) {
var c = lst[i];
if (c._columnMin >= columnFrom) {
if (c._columnMin + columns <= ExcelPackage.MaxColumns) {
c._columnMin += columns;
} else {
c._columnMin = ExcelPackage.MaxColumns;
}
if (c._columnMax + columns <= ExcelPackage.MaxColumns) {
c._columnMax += columns;
} else {
c._columnMax = ExcelPackage.MaxColumns;
}
} else if (c._columnMax >= columnFrom) {
var cc = c._columnMax - columnFrom;
c._columnMax = columnFrom - 1;
CopyColumn(c, columnFrom + columns, columnFrom + columns + cc);
}
}
if (copyStylesFromColumn > 0) {
for (var c = 0; c < columns; c++) {
var col = Column(columnFrom + c);
col.StyleID = Column(copyStylesFromColumn).StyleID;
}
}
//Adjust tables
foreach (var tbl in Tables) {
if (columnFrom > tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column) {
InsertTableColumns(columnFrom, columns, tbl);
}
tbl.Address = tbl.Address.AddColumn(columnFrom, columns);
}
}
private static void InsertTableColumns(int columnFrom, int columns, ExcelTable tbl) {
var node = tbl.Columns[0].TopNode.ParentNode;
var ix = columnFrom - tbl.Address.Start.Column - 1;
var insPos = node.ChildNodes[ix];
ix += 2;
for (int i = 0; i < columns; i++) {
var name = tbl.Columns.GetUniqueName(
string.Format("Column{0}", (ix++).ToString(CultureInfo.InvariantCulture)));
XmlElement tableColumn = (XmlElement)
tbl.TableXml.CreateNode(XmlNodeType.Element, "tableColumn", ExcelPackage._schemaMain);
tableColumn.SetAttribute(
"id",
(tbl.Columns.Count + i + 1).ToString(CultureInfo.InvariantCulture));
tableColumn.SetAttribute("name", name);
insPos = node.InsertAfter(tableColumn, insPos);
} //Create tbl Column
tbl._cols = new(tbl);
}
/// <summary>
/// Adds a value to the row of merged cells to fix for inserts or deletes
/// </summary>
/// <param name="row"></param>
/// <param name="rows"></param>
/// <param name="delete"></param>
private void FixMergedCellsRow(int row, int rows, bool delete) {
if (delete) {
_mergedCells._cells.Delete(row, 0, rows, 0);
} else {
_mergedCells._cells.Insert(row, 0, rows, 0);
}
List<int> removeIndex = new List<int>();
for (int i = 0; i < _mergedCells.Count; i++) {
if (!string.IsNullOrEmpty(_mergedCells[i])) {
ExcelAddressBase addr = new(_mergedCells[i]),
newAddr;
if (delete) {
newAddr = addr.DeleteRow(row, rows);
if (newAddr == null) {
removeIndex.Add(i);
continue;
}
} else {
newAddr = addr.AddRow(row, rows);
if (newAddr.Address != addr.Address) {
// _mergedCells._cells.Insert(row, 0, rows, 0);
_mergedCells.SetIndex(newAddr, i);
}
}
if (newAddr.Address != addr.Address) {
_mergedCells.List[i] = newAddr._address;
}
}
}
for (int i = removeIndex.Count - 1; i >= 0; i--) {
_mergedCells.List.RemoveAt(removeIndex[i]);
}
}
/// <summary>
/// Adds a value to the row of merged cells to fix for inserts or deletes
/// </summary>
/// <param name="column"></param>
/// <param name="columns"></param>
/// <param name="delete"></param>
private void FixMergedCellsColumn(int column, int columns, bool delete) {
if (delete) {
_mergedCells._cells.Delete(0, column, 0, columns);
} else {
_mergedCells._cells.Insert(0, column, 0, columns);
}
List<int> removeIndex = new List<int>();
for (int i = 0; i < _mergedCells.Count; i++) {
if (!string.IsNullOrEmpty(_mergedCells[i])) {
ExcelAddressBase addr = new(_mergedCells[i]),
newAddr;
if (delete) {
newAddr = addr.DeleteColumn(column, columns);
if (newAddr == null) {
removeIndex.Add(i);
continue;
}
} else {
newAddr = addr.AddColumn(column, columns);
if (newAddr.Address != addr.Address) {
_mergedCells.SetIndex(newAddr, i);
}
}
if (newAddr.Address != addr.Address) {
_mergedCells.List[i] = newAddr._address;
}
}
}
for (int i = removeIndex.Count - 1; i >= 0; i--) {
_mergedCells.List.RemoveAt(removeIndex[i]);
}
}
/// <summary>
/// Delete the specified row from the worksheet.
/// </summary>
/// <param name="row">A row to be deleted</param>
public void DeleteRow(int row) {
DeleteRow(row, 1);
}
/// <summary>
/// Delete the specified row from the worksheet.
/// </summary>
/// <param name="rowFrom">The start row</param>
/// <param name="rows">Number of rows to delete</param>
public void DeleteRow(int rowFrom, int rows) {
CheckSheetType();
if (rowFrom < 1 || rowFrom + rows > ExcelPackage.MaxRows) {
throw (new ArgumentException(
"Row out of range. Spans from 1 to "
+ ExcelPackage.MaxRows.ToString(CultureInfo.InvariantCulture)));
}
_values.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
_types.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
_formulas.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
_styles.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
_flags.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
_commentsStore.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
_hyperLinks.Delete(rowFrom, 0, rows, ExcelPackage.MaxColumns);
AdjustFormulasRow(rowFrom, rows);
FixMergedCellsRow(rowFrom, rows, true);
foreach (var tbl in Tables) {
tbl.Address = tbl.Address.DeleteRow(rowFrom, rows);
}
}
/// <summary>
/// Delete the specified column from the worksheet.
/// </summary>
/// <param name="column">The column to be deleted</param>
public void DeleteColumn(int column) {
DeleteColumn(column, 1);
}
/// <summary>
/// Delete the specified column from the worksheet.
/// </summary>
/// <param name="columnFrom">The start column</param>
/// <param name="columns">Number of columns to delete</param>
public void DeleteColumn(int columnFrom, int columns) {
if (columnFrom < 1 || columnFrom + columns > ExcelPackage.MaxColumns) {
throw (new ArgumentException(
"Column out of range. Spans from 1 to "
+ ExcelPackage.MaxColumns.ToString(CultureInfo.InvariantCulture)));
}
var col = _values.GetValue(0, columnFrom) as ExcelColumn;
if (col == null) {
var r = 0;
var c = columnFrom;
if (_values.PrevCell(ref r, ref c)) {
col = _values.GetValue(0, c) as ExcelColumn;
if (col._columnMax >= columnFrom) {
col.ColumnMax = columnFrom - 1;
}
}
}
_values.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
_types.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
_formulas.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
_styles.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
_flags.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
_commentsStore.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
_hyperLinks.Delete(0, columnFrom, ExcelPackage.MaxRows, columns);
AdjustFormulasColumn(columnFrom, columns);
FixMergedCellsColumn(columnFrom, columns, true);
var csec = new CellsStoreEnumerator<object>(_values, 0, columnFrom, 0, ExcelPackage.MaxColumns);
foreach (var column in csec) {
if (column is ExcelColumn excelColumn) {
if (excelColumn._columnMin >= columnFrom) {
excelColumn._columnMin -= columns;
excelColumn._columnMax -= columns;
}
}
}
foreach (var tbl in Tables) {
if (columnFrom >= tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column) {
var node = tbl.Columns[0].TopNode.ParentNode;
var ix = columnFrom - tbl.Address.Start.Column;
for (int i = 0; i < columns; i++) {
if (node.ChildNodes.Count > ix) {
node.RemoveChild(node.ChildNodes[ix]);
}
}
tbl._cols = new(tbl);
}
tbl.Address = tbl.Address.DeleteColumn(columnFrom, columns);
}
}
internal void AdjustFormulasRow(int rowFrom, int rows) {
var delSf = new List<int>();
foreach (var sf in _sharedFormulas.Values) {
var a = new ExcelAddress(sf.Address).DeleteRow(rowFrom, rows);
if (a == null) {
delSf.Add(sf.Index);
} else {
sf.Address = a.Address;
if (sf.StartRow > rowFrom) {
var r = Math.Min(sf.StartRow - rowFrom, rows);
sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, -r, 0, rowFrom, 0);
sf.StartRow -= r;
}
}
}
foreach (var ix in delSf) {
_sharedFormulas.Remove(ix);
}
var cse = new CellsStoreEnumerator<object>(
_formulas,
1,
1,
ExcelPackage.MaxRows,
ExcelPackage.MaxColumns);
while (cse.Next()) {
if (cse.Value is string) {
cse.Value = ExcelCellBase.UpdateFormulaReferences(
cse.Value.ToString(),
-rows,
0,
rowFrom,
0);
}
}
}
internal void AdjustFormulasColumn(int columnFrom, int columns) {
var delSf = new List<int>();
foreach (var sf in _sharedFormulas.Values) {
var a = new ExcelAddress(sf.Address).DeleteColumn(columnFrom, columns);
if (a == null) {
delSf.Add(sf.Index);
} else {
sf.Address = a.Address;
if (sf.StartCol > columnFrom) {
var c = Math.Min(sf.StartCol - columnFrom, columns);
sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, 0, -c, 0, 1);
sf.StartCol -= c;
}
}
}
foreach (var ix in delSf) {
_sharedFormulas.Remove(ix);
}
var cse = new CellsStoreEnumerator<object>(
_formulas,
1,
1,
ExcelPackage.MaxRows,
ExcelPackage.MaxColumns);
while (cse.Next()) {
if (cse.Value is string) {
cse.Value = ExcelCellBase.UpdateFormulaReferences(
cse.Value.ToString(),
0,
-columns,
0,
columnFrom);
}
}
}
/// <summary>
/// Deletes the specified row from the worksheet.
/// </summary>
/// <param name="rowFrom">The number of the start row to be deleted</param>
/// <param name="rows">Number of rows to delete</param>
/// <param name="shiftOtherRowsUp">Not used. Rows are always shifted</param>
public void DeleteRow(int rowFrom, int rows, bool shiftOtherRowsUp) {
DeleteRow(rowFrom, rows);
}
/// <summary>
/// Get the cell value from thw worksheet
/// </summary>
/// <param name="row">The row number</param>
/// <param name="column">The row number</param>
/// <returns>The value</returns>
public object GetValue(int row, int column) {
CheckSheetType();
var v = _values.GetValue(row, column);
if (v != null) {
if (_flags.GetFlagValue(row, column, CellFlags.RichText)) {
return Cells[row, column].RichText.Text;
}
return v;
}
return null;
}
/// <summary>
/// Get a strongly typed cell value from the worksheet
/// </summary>
/// <typeparam name="T">The type</typeparam>
/// <param name="row">The row number</param>
/// <param name="column">The row number</param>
/// <returns>The value. If the value can't be converted to the specified type, the default value will be returned</returns>
public T GetValue<T>(int row, int column) {
CheckSheetType();
//ulong cellID=ExcelCellBase.GetCellID(SheetID, Row, Column);
var v = _values.GetValue(row, column);
if (v == null) {
return default(T);
}
//var cell=((ExcelCell)_cells[cellID]);
if (_flags.GetFlagValue(row, column, CellFlags.RichText)) {
return (T)(object)Cells[row, column].RichText.Text;
}
return GetTypedValue<T>(v);
}
//Thanks to Michael Tran for parts of this method
internal T GetTypedValue<T>(object v) {
if (v == null) {
return default(T);
}
Type fromType = v.GetType();
Type toType = typeof(T);
if (fromType == toType) {
return (T)v;
}
var cnv = TypeDescriptor.GetConverter(fromType);
if (toType
== typeof(DateTime)) //Handle dates
{
if (fromType == typeof(TimeSpan)) {
return ((T)(object)(new DateTime(((TimeSpan)v).Ticks)));
}
if (fromType == typeof(string)) {
if (DateTime.TryParse(v.ToString(), out var dt)) {
return (T)(object)(dt);
}
return default(T);
}
if (cnv.CanConvertTo(typeof(double))) {
return (T)(object)(DateTime.FromOADate((double)cnv.ConvertTo(v, typeof(double))));
}
return default(T);
}
if (toType
== typeof(TimeSpan)) //Handle timespan
{
if (fromType == typeof(DateTime)) {
return ((T)(object)(new TimeSpan(((DateTime)v).Ticks)));
}
if (fromType == typeof(string)) {
if (TimeSpan.TryParse(v.ToString(), out var ts)) {
return (T)(object)(ts);
}
return default(T);
}
if (cnv.CanConvertTo(typeof(double))) {
return (T)
(object)(new TimeSpan(
DateTime.FromOADate((double)cnv.ConvertTo(v, typeof(double))).Ticks));
}
try {
// Issue 14682 -- "GetValue<decimal>() won't convert strings"
// As suggested, after all special cases, all .NET to do it's
// preferred conversion rather than simply returning the default
return (T)Convert.ChangeType(v, typeof(T));
} catch (Exception) {
// This was the previous behaviour -- no conversion is available.
return default(T);
}
}
if (cnv.CanConvertTo(toType)) {
return (T)cnv.ConvertTo(v, typeof(T));
}
if (toType.IsGenericType && toType.GetGenericTypeDefinition().Equals(typeof(Nullable<>))) {
toType = Nullable.GetUnderlyingType(toType);
if (cnv.CanConvertTo(toType)) {
return (T)cnv.ConvertTo(v, typeof(T));
}
}
if (fromType == typeof(double) && toType == typeof(decimal)) {
return (T)(object)Convert.ToDecimal(v);
}
if (fromType == typeof(decimal) && toType == typeof(double)) {
return (T)(object)Convert.ToDouble(v);
}
return default(T);
}
/// <summary>
/// Set the value of a cell
/// </summary>
/// <param name="row">The row number</param>
/// <param name="column">The column number</param>
/// <param name="value">The value</param>
public void SetValue(int row, int column, object value) {
CheckSheetType();
if (row < 1 || column < 1 || row > ExcelPackage.MaxRows && column > ExcelPackage.MaxColumns) {
throw new ArgumentOutOfRangeException("Row or Column out of range");
}
_values.SetValue(row, column, value);
}
/// <summary>
/// Set the value of a cell
/// </summary>
/// <param name="address">The Excel address</param>
/// <param name="value">The value</param>
public void SetValue(string address, object value) {
CheckSheetType();
ExcelCellBase.GetRowCol(address, out var row, out var col, true);
if (row < 1 || col < 1 || row > ExcelPackage.MaxRows && col > ExcelPackage.MaxColumns) {
throw new ArgumentOutOfRangeException("Address is invalid or out of range");
}
_values.SetValue(row, col, value);
}
/// <summary>
/// Get MergeCell Index No
/// </summary>
/// <param name="row"></param>
/// <param name="column"></param>
/// <returns></returns>
public int GetMergeCellId(int row, int column) {
for (int i = 0; i < _mergedCells.Count; i++) {
if (!string.IsNullOrEmpty(_mergedCells[i])) {
ExcelRange range = Cells[_mergedCells[i]];
if (range.Start.Row <= row && row <= range.End.Row) {
if (range.Start.Column <= column && column <= range.End.Column) {
return i + 1;
}
}
}
}
return 0;
}
internal void Save() {
DeletePrinterSettings();
if (!(this is ExcelChartsheet)) {
var d = Dimension;
if (d == null) {
DeleteAllNode("d:dimension/@ref");
} else {
SetXmlNodeString("d:dimension/@ref", d.Address);
}
SaveTables();
SavePivotTables();
}
}
internal void SaveHandler(StreamWriter streamWriter) {
//Create the nodes if they do not exist.
if (this is ExcelChartsheet) {
streamWriter.Write(WorksheetXml.OuterXml);
} else {
CreateNode("d:cols");
CreateNode("d:sheetData");
CreateNode("d:mergeCells");
CreateNode("d:hyperlinks");
CreateNode("d:rowBreaks");
CreateNode("d:colBreaks");
//StreamWriter sw=new StreamWriter(Part.GetStream(FileMode.Create, FileAccess.Write));
var xml = WorksheetXml.OuterXml;
int colStart = 0,
colEnd = 0;
GetBlockPos(xml, "cols", ref colStart, ref colEnd);
streamWriter.Write(xml.Substring(0, colStart));
UpdateColumnData(streamWriter);
int cellStart = colEnd,
cellEnd = colEnd;
GetBlockPos(xml, "sheetData", ref cellStart, ref cellEnd);
streamWriter.Write(xml.Substring(colEnd, cellStart - colEnd));
UpdateRowCellData(streamWriter);
int mergeStart = cellEnd,
mergeEnd = cellEnd;
GetBlockPos(xml, "mergeCells", ref mergeStart, ref mergeEnd);
streamWriter.Write(xml.Substring(cellEnd, mergeStart - cellEnd));
CleanupMergedCells(_mergedCells);
if (_mergedCells.Count > 0) {
UpdateMergedCells(streamWriter);
}
int hyperStart = mergeEnd,
hyperEnd = mergeEnd;
GetBlockPos(xml, "hyperlinks", ref hyperStart, ref hyperEnd);
streamWriter.Write(xml.Substring(mergeEnd, hyperStart - mergeEnd));
UpdateHyperLinks(streamWriter);
int rowBreakStart = hyperEnd,
rowBreakEnd = hyperEnd;
GetBlockPos(xml, "rowBreaks", ref rowBreakStart, ref rowBreakEnd);
streamWriter.Write(xml.Substring(hyperEnd, rowBreakStart - hyperEnd));
UpdateRowBreaks(streamWriter);
int colBreakStart = rowBreakEnd,
colBreakEnd = rowBreakEnd;
GetBlockPos(xml, "colBreaks", ref colBreakStart, ref colBreakEnd);
streamWriter.Write(xml.Substring(rowBreakEnd, colBreakStart - rowBreakEnd));
UpdateColBreaks(streamWriter);
streamWriter.Write(xml.Substring(colBreakEnd, xml.Length - colBreakEnd));
}
}
/// <summary>
/// Delete the printersettings relationship and part.
/// </summary>
private void DeletePrinterSettings() {
//Delete the relationship from the pageSetup tag
XmlAttribute attr = (XmlAttribute)
WorksheetXml.SelectSingleNode("//d:pageSetup/@r:id", NameSpaceManager);
if (attr != null) {
string relId = attr.Value;
//First delete the attribute from the XML
attr.OwnerElement.Attributes.Remove(attr);
if (Part.RelationshipExists(relId)) {
var rel = Part.GetRelationship(relId);
Uri printerSettingsUri = UriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri);
Part.DeleteRelationship(rel.Id);
//Delete the part from the package
if (_package.Package.PartExists(printerSettingsUri)) {
_package.Package.DeletePart(printerSettingsUri);
}
}
}
}
/// <summary>
/// Save all table data
/// </summary>
private void SaveTables() {
foreach (var tbl in Tables) {
if (tbl.ShowHeader || tbl.ShowTotal) {
int colNum = tbl.Address._fromCol;
var colVal = new HashSet<string>();
foreach (var col in tbl.Columns) {
string n = col.Name.ToLower(CultureInfo.InvariantCulture);
if (tbl.ShowHeader) {
n = tbl.WorkSheet.GetValue<string>(
tbl.Address._fromRow,
tbl.Address._fromCol + col.Position);
if (string.IsNullOrEmpty(n)) {
n = col.Name.ToLower(CultureInfo.InvariantCulture);
} else {
col.Name = n;
}
} else {
n = col.Name.ToLower(CultureInfo.InvariantCulture);
}
if (colVal.Contains(n)) {
throw (new InvalidDataException(
string.Format(
"Table {0} Column {1} does not have a unique name.",
tbl.Name,
col.Name)));
}
colVal.Add(n);
col.Name = ConvertUtil.ExcelEncodeString(col.Name);
if (tbl.ShowHeader) {
_values.SetValue(tbl.Address._fromRow, colNum, col.Name);
}
if (tbl.ShowTotal) {
SetTableTotalFunction(tbl, col, colNum);
}
if (!string.IsNullOrEmpty(col.CalculatedColumnFormula)) {
int fromRow = tbl.ShowHeader ? tbl.Address._fromRow + 1 : tbl.Address._fromRow;
int toRow = tbl.ShowTotal ? tbl.Address._toRow - 1 : tbl.Address._toRow;
for (int row = fromRow; row <= toRow; row++) {
//Cell(row, colNum).Formula = col.CalculatedColumnFormula;
SetFormula(row, colNum, col.CalculatedColumnFormula);
}
}
colNum++;
}
}
}
}
internal void SetTableTotalFunction(ExcelTable tbl, ExcelTableColumn col, int colNum = -1) {
if (tbl.ShowTotal == false) {
return;
}
if (colNum == -1) {
for (int i = 0; i < tbl.Columns.Count; i++) {
if (tbl.Columns[i].Name == col.Name) {
colNum = tbl.Address._fromCol + i;
}
}
}
if (col.TotalsRowFunction == RowFunctions.Custom) {
SetFormula(tbl.Address._toRow, colNum, col.TotalsRowFormula);
} else if (col.TotalsRowFunction != RowFunctions.None) {
switch (col.TotalsRowFunction) {
case RowFunctions.Average:
SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "101"));
break;
case RowFunctions.Count:
SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "102"));
break;
case RowFunctions.CountNums:
SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "103"));
break;
case RowFunctions.Max:
SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "104"));
break;
case RowFunctions.Min:
SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "105"));
break;
case RowFunctions.StdDev:
SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "107"));
break;
case RowFunctions.Var:
SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "110"));
break;
case RowFunctions.Sum:
SetFormula(tbl.Address._toRow, colNum, GetTotalFunction(col, "109"));
break;
default:
throw (new("Unknown RowFunction enum"));
}
} else {
_values.SetValue(tbl.Address._toRow, colNum, col.TotalsRowLabel);
}
}
internal void SetFormula(int row, int col, object value) {
_formulas.SetValue(row, col, value);
if (!_values.Exists(row, col)) {
_values.SetValue(row, col, null);
}
}
internal void SetStyle(int row, int col, int value) {
_styles.SetValue(row, col, value);
if (!_values.Exists(row, col)) {
_values.SetValue(row, col, null);
}
}
private void SavePivotTables() {
foreach (var pt in PivotTables) {
if (pt.DataFields.Count > 1) {
XmlElement parentNode;
if (pt.DataOnRows) {
parentNode =
pt.PivotTableXml.SelectSingleNode("//d:rowFields", pt.NameSpaceManager) as XmlElement;
if (parentNode == null) {
pt.CreateNode("d:rowFields");
parentNode =
pt.PivotTableXml.SelectSingleNode("//d:rowFields", pt.NameSpaceManager)
as XmlElement;
}
} else {
parentNode =
pt.PivotTableXml.SelectSingleNode("//d:colFields", pt.NameSpaceManager) as XmlElement;
if (parentNode == null) {
pt.CreateNode("d:colFields");
parentNode =
pt.PivotTableXml.SelectSingleNode("//d:colFields", pt.NameSpaceManager)
as XmlElement;
}
}
if (parentNode.SelectSingleNode("d:field[@ x= \"-2\"]", pt.NameSpaceManager) == null) {
XmlElement fieldNode = pt.PivotTableXml.CreateElement("field", ExcelPackage._schemaMain);
fieldNode.SetAttribute("x", "-2");
parentNode.AppendChild(fieldNode);
}
}
var ws = Workbook.Worksheets[pt.CacheDefinition.SourceRange.WorkSheet];
var t = ws.Tables.GetFromRange(pt.CacheDefinition.SourceRange);
var fields = pt.CacheDefinition.CacheDefinitionXml.SelectNodes(
"d:pivotCacheDefinition/d:cacheFields/d:cacheField",
NameSpaceManager);
int ix = 0;
if (fields != null) {
var flds = new HashSet<string>();
foreach (XmlElement node in fields) {
if (ix >= pt.CacheDefinition.SourceRange.Columns) {
break;
}
var fldName = node.GetAttribute("name"); //Fixes issue 15295 dup name error
if (string.IsNullOrEmpty(fldName)) {
fldName =
(t == null
? pt.CacheDefinition.SourceRange.Offset(0, ix++, 1, 1).Value.ToString()
: t.Columns[ix++].Name);
}
if (flds.Contains(fldName)) {
fldName = GetNewName(flds, fldName);
}
flds.Add(fldName);
node.SetAttribute("name", fldName);
}
foreach (var df in pt.DataFields) {
if (string.IsNullOrEmpty(df.Name)) {
string name;
if (df.Function == DataFieldFunctions.None) {
name = df.Field.Name; //Name must be set or Excel will crash on rename.
} else {
name = df.Function + " of " + df.Field.Name; //Name must be set or Excel will crash on rename.
}
//Make sure name is unique
var newName = name;
var i = 2;
while (pt.DataFields.ExistsDfName(newName, df)) {
newName = name + (i++).ToString(CultureInfo.InvariantCulture);
}
df.Name = newName;
}
}
}
}
}
private string GetNewName(HashSet<string> flds, string fldName) {
int ix = 2;
while (flds.Contains(fldName + ix.ToString(CultureInfo.InvariantCulture))) {
ix++;
}
return fldName + ix.ToString(CultureInfo.InvariantCulture);
}
private static string GetTotalFunction(ExcelTableColumn col, string functionNum) {
return string.Format("SUBTOTAL({0},{1}[{2}])", functionNum, col._tbl.Name, col.Name);
}
private void CleanupMergedCells(MergeCellsCollection mergedCells) {
int i = 0;
while (i < mergedCells.List.Count) {
if (mergedCells[i] == null) {
mergedCells.List.RemoveAt(i);
} else {
i++;
}
}
}
private void UpdateColBreaks(StreamWriter sw) {
StringBuilder breaks = new StringBuilder();
int count = 0;
var cse = new CellsStoreEnumerator<object>(_values, 0, 0, 0, ExcelPackage.MaxColumns);
while (cse.Next()) {
var col = cse.Value as ExcelColumn;
if (col != null && col.PageBreak) {
breaks.AppendFormat("<brk id=\"{0}\" max=\"16383\" man=\"1\" />", cse.Column);
count++;
}
}
if (count > 0) {
sw.Write("<colBreaks count=\"{0}\" manualBreakCount=\"{0}\">{1}</colBreaks>", count, breaks);
}
}
private void UpdateRowBreaks(StreamWriter sw) {
StringBuilder breaks = new StringBuilder();
int count = 0;
var cse = new CellsStoreEnumerator<object>(_values, 0, 0, ExcelPackage.MaxRows, 0);
//foreach(ExcelRow row in _rows)
while (cse.Next()) {
var row = cse.Value as RowInternal;
if (row != null && row.PageBreak) {
breaks.AppendFormat("<brk id=\"{0}\" max=\"1048575\" man=\"1\" />", cse.Row);
count++;
}
}
if (count > 0) {
sw.Write("<rowBreaks count=\"{0}\" manualBreakCount=\"{0}\">{1}</rowBreaks>", count, breaks);
}
}
/// <summary>
/// Inserts the cols collection into the XML document
/// </summary>
private void UpdateColumnData(StreamWriter sw) {
var cse = new CellsStoreEnumerator<object>(_values, 0, 1, 0, ExcelPackage.MaxColumns);
bool first = true;
while (cse.Next()) {
if (first) {
sw.Write("<cols>");
first = false;
}
var col = cse.Value as ExcelColumn;
ExcelStyleCollection<ExcelXfs> cellXfs = _workbook.Styles.CellXfs;
sw.Write("<col min=\"{0}\" max=\"{1}\"", col.ColumnMin, col.ColumnMax);
if (col.Hidden) {
//sbXml.Append(" width=\"0\" hidden=\"1\" customWidth=\"1\"");
sw.Write(" hidden=\"1\"");
} else if (col.BestFit) {
sw.Write(" bestFit=\"1\"");
}
sw.Write(
string.Format(
CultureInfo.InvariantCulture,
" width=\"{0}\" customWidth=\"1\"",
col.Width));
if (col.OutlineLevel > 0) {
sw.Write(" outlineLevel=\"{0}\" ", col.OutlineLevel);
if (col.Collapsed) {
if (col.Hidden) {
sw.Write(" collapsed=\"1\"");
} else {
sw.Write(" collapsed=\"1\" hidden=\"1\""); //Always hidden
}
}
}
if (col.Phonetic) {
sw.Write(" phonetic=\"1\"");
}
var styleId = col.StyleID >= 0 ? cellXfs[col.StyleID].newID : col.StyleID;
if (styleId > 0) {
sw.Write(" style=\"{0}\"", styleId);
}
sw.Write(" />");
}
if (!first) {
sw.Write("</cols>");
}
}
/// <summary>
/// Insert row and cells into the XML document
/// </summary>
private void UpdateRowCellData(StreamWriter sw) {
ExcelStyleCollection<ExcelXfs> cellXfs = _workbook.Styles.CellXfs;
int row = -1;
var ss = _workbook._sharedStrings;
var styles = _workbook.Styles;
var cache = new StringBuilder();
cache.Append("<sheetData>");
//Set a value for cells with style and no value set.
var cseStyle = new CellsStoreEnumerator<int>(
_styles,
0,
0,
ExcelPackage.MaxRows,
ExcelPackage.MaxColumns);
foreach (var s in cseStyle) {
if (!_values.Exists(cseStyle.Row, cseStyle.Column)) {
_values.SetValue(cseStyle.Row, cseStyle.Column, null);
}
}
var cse = new CellsStoreEnumerator<object>(
_values,
1,
0,
ExcelPackage.MaxRows,
ExcelPackage.MaxColumns);
//foreach (IRangeID r in _cells)
while (cse.Next()) {
if (cse.Column > 0) {
int styleId = cellXfs[styles.GetStyleId(this, cse.Row, cse.Column)].newID;
//Add the row element if it's a new row
if (cse.Row != row) {
WriteRow(cache, cellXfs, row, cse.Row);
row = cse.Row;
}
object v = cse.Value;
object formula = _formulas.GetValue(cse.Row, cse.Column);
if (formula is int sfId) {
var f = _sharedFormulas[sfId];
if (f.Address.IndexOf(':') > 0) {
if (f.StartCol == cse.Column && f.StartRow == cse.Row) {
if (f.IsArray) {
cache.AppendFormat(
"<c r=\"{0}\" s=\"{1}\"{5}><f ref=\"{2}\" t=\"array\">{3}</f>{4}</c>",
cse.CellAddress,
styleId < 0 ? 0 : styleId,
f.Address,
SecurityElement.Escape(f.Formula),
GetFormulaValue(v),
GetCellType(v, true));
} else {
cache.AppendFormat(
"<c r=\"{0}\" s=\"{1}\"{6}><f ref=\"{2}\" t=\"shared\" si=\"{3}\">{4}</f>{5}</c>",
cse.CellAddress,
styleId < 0 ? 0 : styleId,
f.Address,
sfId,
SecurityElement.Escape(f.Formula),
GetFormulaValue(v),
GetCellType(v, true));
}
} else if (f.IsArray) {
cache.AppendFormat(
"<c r=\"{0}\" s=\"{1}\" />",
cse.CellAddress,
styleId < 0 ? 0 : styleId);
} else {
cache.AppendFormat(
"<c r=\"{0}\" s=\"{1}\"{4}><f t=\"shared\" si=\"{2}\" />{3}</c>",
cse.CellAddress,
styleId < 0 ? 0 : styleId,
sfId,
GetFormulaValue(v),
GetCellType(v, true));
}
} else {
// We can also have a single cell array formula
if (f.IsArray) {
cache.AppendFormat(
"<c r=\"{0}\" s=\"{1}\"{5}><f ref=\"{2}\" t=\"array\">{3}</f>{4}</c>",
cse.CellAddress,
styleId < 0 ? 0 : styleId,
string.Format("{0}:{1}", f.Address, f.Address),
SecurityElement.Escape(f.Formula),
GetFormulaValue(v),
GetCellType(v, true));
} else {
cache.AppendFormat("<c r=\"{0}\" s=\"{1}\">", f.Address, styleId < 0 ? 0 : styleId);
cache.AppendFormat(
"<f>{0}</f>{1}</c>",
SecurityElement.Escape(f.Formula),
GetFormulaValue(v));
}
}
} else if (formula != null && formula.ToString() != "") {
cache.AppendFormat(
"<c r=\"{0}\" s=\"{1}\"{2}>",
cse.CellAddress,
styleId < 0 ? 0 : styleId,
GetCellType(v, true));
cache.AppendFormat(
"<f>{0}</f>{1}</c>",
SecurityElement.Escape(formula.ToString()),
GetFormulaValue(v));
} else {
if (v == null && styleId > 0) {
cache.AppendFormat(
"<c r=\"{0}\" s=\"{1}\" />",
cse.CellAddress,
styleId < 0 ? 0 : styleId);
} else if (v != null) {
if ((v.GetType().IsPrimitive
|| v is double
|| v is decimal
|| v is DateTime
|| v is TimeSpan)) {
//string sv = GetValueForXml(v);
cache.AppendFormat(
"<c r=\"{0}\" s=\"{1}\" {2}>",
cse.CellAddress,
styleId < 0 ? 0 : styleId,
GetCellType(v));
cache.AppendFormat("{0}</c>", GetFormulaValue(v));
} else {
int ix;
if (!ss.ContainsKey(v.ToString())) {
ix = ss.Count;
ss.Add(
v.ToString(),
new() {
isRichText = _flags.GetFlagValue(cse.Row, cse.Column, CellFlags.RichText),
pos = ix,
});
} else {
ix = ss[v.ToString()].pos;
}
cache.AppendFormat(
"<c r=\"{0}\" s=\"{1}\" t=\"s\">",
cse.CellAddress,
styleId < 0 ? 0 : styleId);
cache.AppendFormat("<v>{0}</v></c>", ix);
}
}
}
} else //ExcelRow
{
WriteRow(cache, cellXfs, row, cse.Row);
row = cse.Row;
}
if (cache.Length > 0x600000) {
sw.Write(cache.ToString());
cache = new();
}
}
if (row != -1) {
cache.Append("</row>");
}
cache.Append("</sheetData>");
sw.Write(cache.ToString());
sw.Flush();
}
private object GetFormulaValue(object v) {
if (v != null && v.ToString() != "") {
return "<v>" + SecurityElement.Escape(GetValueForXml(v)) + "</v>"; //Fixes issue 15071
}
return "";
}
private string GetCellType(object v, bool allowStr = false) {
if (v is bool) {
return " t=\"b\"";
}
if ((v is double d && double.IsInfinity(d)) || v is ExcelErrorValue) {
return " t=\"e\"";
}
if (allowStr
&& v != null
&& !(v.GetType().IsPrimitive
|| v is double
|| v is decimal
|| v is DateTime
|| v is TimeSpan)) {
return " t=\"str\"";
}
return "";
}
private string GetValueForXml(object v) {
string s;
try {
if (v is DateTime time) {
double sdv = time.ToOADate();
if (Workbook.Date1904) {
sdv -= ExcelWorkbook._date1904Offset;
}
s = sdv.ToString(CultureInfo.InvariantCulture);
} else if (v is TimeSpan span) {
s = new DateTime(span.Ticks).ToOADate().ToString(CultureInfo.InvariantCulture);
;
} else if (v.GetType().IsPrimitive || v is double || v is decimal) {
if (v is double d && double.IsNaN(d)) {
s = "";
} else if (v is double d1 && double.IsInfinity(d1)) {
s = "#NUM!";
} else {
s = Convert
.ToDouble(v, CultureInfo.InvariantCulture)
.ToString("R15", CultureInfo.InvariantCulture);
}
} else {
s = v.ToString();
}
} catch {
s = "0";
}
return s;
}
private void WriteRow(
StringBuilder cache,
ExcelStyleCollection<ExcelXfs> cellXfs,
int prevRow,
int row) {
if (prevRow != -1) {
cache.Append("</row>");
}
//ulong rowID = ExcelRow.GetRowID(SheetID, row);
cache.AppendFormat("<row r=\"{0}\" ", row);
RowInternal currRow = _values.GetValue(row, 0) as RowInternal;
if (currRow != null) {
if (currRow.Hidden) {
cache.Append("ht=\"0\" hidden=\"1\" ");
} else if (currRow.Height != DefaultRowHeight && currRow.Height >= 0) {
cache.AppendFormat(CultureInfo.InvariantCulture, "ht=\"{0}\" ", currRow.Height);
if (currRow.CustomHeight) {
cache.Append("customHeight=\"1\" ");
}
}
if (currRow.OutlineLevel > 0) {
cache.AppendFormat("outlineLevel =\"{0}\" ", currRow.OutlineLevel);
if (currRow.Collapsed) {
if (currRow.Hidden) {
cache.Append(" collapsed=\"1\" ");
} else {
cache.Append(" collapsed=\"1\" hidden=\"1\" "); //Always hidden
}
}
}
if (currRow.Phonetic) {
cache.Append("ph=\"1\" ");
}
}
var s = _styles.GetValue(row, 0);
if (s > 0) {
cache.AppendFormat("s=\"{0}\" customFormat=\"1\"", cellXfs[s].newID);
}
cache.Append(">");
}
/// <summary>
/// Update xml with hyperlinks
/// </summary>
/// <param name="sw">The stream</param>
private void UpdateHyperLinks(StreamWriter sw) {
Dictionary<string, string> hyps = new Dictionary<string, string>();
var cse = new CellsStoreEnumerator<Uri>(_hyperLinks);
bool first = true;
//foreach (ulong cell in _hyperLinks)
while (cse.Next()) {
if (first) {
sw.Write("<hyperlinks>");
first = false;
}
//int row, col;
var uri = _hyperLinks.GetValue(cse.Row, cse.Column);
//ExcelCell cell = _cells[cellId] as ExcelCell;
if (uri is ExcelHyperLink link && !string.IsNullOrEmpty(link.ReferenceAddress)) {
sw.Write(
"<hyperlink ref=\"{0}\" location=\"{1}\" {2}{3}/>",
Cells[cse.Row, cse.Column, cse.Row + link.RowSpann, cse.Column + link.ColSpann].Address,
ExcelCellBase.GetFullAddress(
SecurityElement.Escape(Name),
SecurityElement.Escape(link.ReferenceAddress)),
string.IsNullOrEmpty(link.Display)
? ""
: "display=\"" + SecurityElement.Escape(link.Display) + "\" ",
string.IsNullOrEmpty(link.ToolTip)
? ""
: "tooltip=\"" + SecurityElement.Escape(link.ToolTip) + "\" ");
} else if (uri != null) {
Uri hyp;
if (uri is ExcelHyperLink hyperLink) {
hyp = hyperLink.OriginalUri;
} else {
hyp = uri;
}
if (!hyps.ContainsKey(hyp.OriginalString)) {
var relationship = Part.CreateRelationship(
hyp,
TargetMode.External,
ExcelPackage._schemaHyperlink);
if (uri is ExcelHyperLink hl) {
sw.Write(
"<hyperlink ref=\"{0}\" {2}{3}r:id=\"{1}\" />",
ExcelCellBase.GetAddress(cse.Row, cse.Column),
relationship.Id,
string.IsNullOrEmpty(hl.Display)
? ""
: "display=\"" + SecurityElement.Escape(hl.Display) + "\" ",
string.IsNullOrEmpty(hl.ToolTip)
? ""
: "tooltip=\"" + SecurityElement.Escape(hl.ToolTip) + "\" ");
} else {
sw.Write(
"<hyperlink ref=\"{0}\" r:id=\"{1}\" />",
ExcelCellBase.GetAddress(cse.Row, cse.Column),
relationship.Id);
}
}
}
}
if (!first) {
sw.Write("</hyperlinks>");
}
}
/// <summary>
/// Dimension address for the worksheet.
/// Top left cell to Bottom right.
/// If the worksheet has no cells, null is returned
/// </summary>
public ExcelAddressBase Dimension {
get {
CheckSheetType();
if (_values.GetDimension(out var fromRow, out var fromCol, out var toRow, out var toCol)) {
var addr = new ExcelAddressBase(fromRow, fromCol, toRow, toCol);
addr._ws = Name;
return addr;
}
return null;
}
}
private ExcelSheetProtection _protection;
/// <summary>
/// Access to sheet protection properties
/// </summary>
public ExcelSheetProtection Protection {
get {
if (_protection == null) {
_protection = new(NameSpaceManager, TopNode);
}
return _protection;
}
}
private ExcelProtectedRangeCollection _protectedRanges;
public ExcelProtectedRangeCollection ProtectedRanges {
get {
if (_protectedRanges == null) {
_protectedRanges = new(NameSpaceManager, TopNode);
}
return _protectedRanges;
}
}
private ExcelTableCollection _tables;
/// <summary>
/// Tables defined in the worksheet.
/// </summary>
public ExcelTableCollection Tables {
get {
CheckSheetType();
if (Workbook._nextTableID == int.MinValue) {
Workbook.ReadAllTables();
}
if (_tables == null) {
_tables = new(this);
}
return _tables;
}
}
private ExcelPivotTableCollection _pivotTables;
/// <summary>
/// Pivottables defined in the worksheet.
/// </summary>
public ExcelPivotTableCollection PivotTables {
get {
CheckSheetType();
if (_pivotTables == null) {
if (Workbook._nextPivotTableID == int.MinValue) {
Workbook.ReadAllTables();
}
_pivotTables = new(this);
}
return _pivotTables;
}
}
private ExcelConditionalFormattingCollection _conditionalFormatting;
/// <summary>
/// ConditionalFormatting defined in the worksheet. Use the Add methods to create ConditionalFormatting and add them to the worksheet. Then
/// set the properties on the instance returned.
/// </summary>
/// <seealso cref="ExcelConditionalFormattingCollection"/>
public ExcelConditionalFormattingCollection ConditionalFormatting {
get {
CheckSheetType();
if (_conditionalFormatting == null) {
_conditionalFormatting = new(this);
}
return _conditionalFormatting;
}
}
private ExcelDataValidationCollection _dataValidation;
/// <summary>
/// DataValidation defined in the worksheet. Use the Add methods to create DataValidations and add them to the worksheet. Then
/// set the properties on the instance returned.
/// </summary>
/// <seealso cref="ExcelDataValidationCollection"/>
public ExcelDataValidationCollection DataValidations {
get {
CheckSheetType();
if (_dataValidation == null) {
_dataValidation = new(this);
}
return _dataValidation;
}
}
/// <summary>
/// Returns the style ID given a style name.
/// The style ID will be created if not found, but only if the style name exists!
/// </summary>
/// <param name="styleName"></param>
/// <returns></returns>
internal int GetStyleId(string styleName) {
ExcelNamedStyleXml namedStyle = null;
Workbook.Styles.NamedStyles.FindById(styleName, ref namedStyle);
if (namedStyle.XfId == int.MinValue) {
namedStyle.XfId = Workbook.Styles.CellXfs.FindIndexById(namedStyle.Style.Id);
}
return namedStyle.XfId;
}
/// <summary>
/// The workbook object
/// </summary>
public ExcelWorkbook Workbook => _workbook;
/// <summary>
/// Get the next ID from a shared formula or an Array formula
/// Sharedforumlas will have an id from 0-x. Array formula ids start from 0x4000001-.
/// </summary>
/// <param name="isArray">If the formula is an array formula</param>
/// <returns></returns>
internal int GetMaxShareFunctionIndex(bool isArray) {
int i = _sharedFormulas.Count + 1;
if (isArray) {
i |= 0x40000000;
}
while (_sharedFormulas.ContainsKey(i)) {
i++;
}
return i;
}
internal void UpdateCellsWithDate1904Setting() {
var cse = new CellsStoreEnumerator<object>(_values);
var offset = Workbook.Date1904 ? -ExcelWorkbook._date1904Offset : ExcelWorkbook._date1904Offset;
while (cse.MoveNext()) {
if (cse.Value is DateTime time) {
try {
double sdv = time.ToOADate();
sdv += offset;
cse.Value = DateTime.FromOADate(sdv);
} catch {}
}
}
}
public string GetFormula(int row, int col) {
var v = _formulas.GetValue(row, col);
if (v is int i) {
return _sharedFormulas[i].GetFormula(row, col, Name);
}
if (v != null) {
return v.ToString();
}
return "";
}
public string GetFormulaR1C1(int row, int col) {
var v = _formulas.GetValue(row, col);
if (v is int i) {
var sf = _sharedFormulas[i];
return ExcelCellBase.TranslateToR1C1(
Formulas.RemoveDummyFunction(sf.Formula),
sf.StartRow,
sf.StartCol);
}
if (v != null) {
return ExcelCellBase.TranslateToR1C1(Formulas.RemoveDummyFunction(v.ToString()), row, col);
}
return "";
}
public string GetFormulaR1C1_V1(int row, int col) {
var v = _formulas.GetValue(row, col);
if (v is int i) {
var sf = _sharedFormulas[i];
return ExcelCellBase.TranslateToR1C1_V1(
Formulas.RemoveDummyFunction(sf.Formula),
sf.StartRow,
sf.StartCol);
}
if (v != null) {
return ExcelCellBase.TranslateToR1C1_V1(Formulas.RemoveDummyFunction(v.ToString()), row, col);
}
return "";
}
public bool IsArrayFormula(int row, int col) =>
_flags.GetFlagValue(row, col, CellFlags.ArrayFormula);
public string GetArrayFormulaAddress(int row, int col) {
var v = _formulas.GetValue(row, col);
if ((v is int i) && (_sharedFormulas[i].IsArray)) {
return _sharedFormulas[i].Address;
}
return "";
}
public int GetStyleId(int row, int col) {
int styleId = 0;
if (!_styles.Exists(row, col, ref styleId) && !_styles.Exists(row, 0, ref styleId)) {
styleId = _styles.GetValue(0, col);
}
return styleId;
}
/// <summary>
/// Get the ExcelColumn for column (span ColumnMin and ColumnMax)
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
internal ExcelColumn GetColumn(int column) {
var c = _values.GetValue(0, column) as ExcelColumn;
if (c == null) {
int row = 0,
col = column;
if (_values.PrevCell(ref row, ref col)) {
c = _values.GetValue(0, col) as ExcelColumn;
if (c != null && c.ColumnMax >= column) {
return c;
}
return null;
}
}
return c;
}
public bool Equals(ExcelWorksheet x, ExcelWorksheet y) {
return x.Name == y.Name
&& x.SheetID == y.SheetID
&& x.WorksheetXml.OuterXml == y.WorksheetXml.OuterXml;
}
public int GetHashCode(ExcelWorksheet obj) {
return obj.WorksheetXml.OuterXml.GetHashCode();
}
}