internal SLDefinedName Clone()
{
SLDefinedName dn = new SLDefinedName(this.Name);
dn.Text = this.Text;
dn.Name = this.Name;
dn.Comment = this.Comment;
dn.CustomMenu = this.CustomMenu;
dn.Description = this.Description;
dn.Help = this.Help;
dn.StatusBar = this.StatusBar;
dn.LocalSheetId = this.LocalSheetId;
dn.Hidden = this.Hidden;
dn.Function = this.Function;
dn.VbProcedure = this.VbProcedure;
dn.Xlm = this.Xlm;
dn.FunctionGroupId = this.FunctionGroupId;
dn.ShortcutKey = this.ShortcutKey;
dn.PublishToServer = this.PublishToServer;
dn.WorkbookParameter = this.WorkbookParameter;
return(dn);
}
/// <summary>
/// Set a given defined name. If it doesn't exist, a new defined name is created. If it exists, then the existing defined name is overwritten.
/// </summary>
/// <param name="Name">Name of defined name. Note that it cannot be a valid cell reference such as A1. It also cannot start with "_xlnm" because it's reserved.</param>
/// <param name="Text">The reference/content text of the defined name. For example, Sheet1!$A$1:$C$3</param>
/// <param name="Comment">Comment for the defined name.</param>
/// <param name="Scope">The name of the worksheet that the defined name is effective in.</param>
/// <returns>True if the given defined name is created or an existing defined name is overwritten. False otherwise.</returns>
public bool SetDefinedName(string Name, string Text, string Comment, string Scope)
{
Name = Name.Trim();
if (SLTool.IsCellReference(Name))
{
return(false);
}
// these are reserved names
if (Name.StartsWith("_xlnm"))
{
return(false);
}
if (Text.StartsWith("="))
{
if (Text.Length > 1)
{
Text = Text.Substring(1);
}
else
{
Text = "\"=\"";
}
}
uint?iLocalSheetId = null;
for (int i = 0; i < slwb.Sheets.Count; ++i)
{
if (slwb.Sheets[i].Name.Equals(Scope, StringComparison.OrdinalIgnoreCase))
{
iLocalSheetId = (uint)i;
break;
}
}
bool bFound = false;
SLDefinedName dn = new SLDefinedName(Name);
dn.Text = Text;
if (Comment != null && Comment.Length > 0)
{
dn.Comment = Comment;
}
if (iLocalSheetId != null)
{
dn.LocalSheetId = iLocalSheetId.Value;
}
foreach (SLDefinedName d in slwb.DefinedNames)
{
if (d.Name.Equals(Name, StringComparison.OrdinalIgnoreCase))
{
bFound = true;
d.Text = Text;
if (Comment != null && Comment.Length > 0)
{
d.Comment = Comment;
}
break;
}
}
if (!bFound)
{
slwb.DefinedNames.Add(dn);
}
return(true);
}
private void SetAddPrintArea(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool ToAdd)
{
if (StartRowIndex < 1)
{
StartRowIndex = 1;
}
if (StartRowIndex > SLConstants.RowLimit)
{
StartRowIndex = SLConstants.RowLimit;
}
if (StartColumnIndex < 1)
{
StartColumnIndex = 1;
}
if (StartColumnIndex > SLConstants.ColumnLimit)
{
StartColumnIndex = SLConstants.ColumnLimit;
}
if (EndRowIndex < 1)
{
EndRowIndex = 1;
}
if (EndRowIndex > SLConstants.RowLimit)
{
EndRowIndex = SLConstants.RowLimit;
}
if (EndColumnIndex < 1)
{
EndColumnIndex = 1;
}
if (EndColumnIndex > SLConstants.ColumnLimit)
{
EndColumnIndex = SLConstants.ColumnLimit;
}
// no overlapping checked.
int iSheetPosition = 0;
int i;
for (i = 0; i < slwb.Sheets.Count; ++i)
{
if (slwb.Sheets[i].Name.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
{
iSheetPosition = i;
break;
}
}
string sPrintArea = string.Empty;
if (StartRowIndex == EndRowIndex && StartColumnIndex == EndColumnIndex)
{
// why would you print just one cell? Even Excel questions this with a message box...
sPrintArea = SLTool.ToCellReference(gsSelectedWorksheetName, StartRowIndex, StartColumnIndex, true);
}
else
{
sPrintArea = SLTool.ToCellRange(gsSelectedWorksheetName, StartRowIndex, StartColumnIndex, EndRowIndex, EndColumnIndex, true);
}
bool bFound = false;
for (i = 0; i < slwb.DefinedNames.Count; ++i)
{
if (slwb.DefinedNames[i].Name.Equals(SLConstants.PrintAreaDefinedName, StringComparison.OrdinalIgnoreCase) &&
slwb.DefinedNames[i].LocalSheetId != null &&
slwb.DefinedNames[i].LocalSheetId.Value == iSheetPosition)
{
bFound = true;
if (ToAdd)
{
slwb.DefinedNames[i].Text = string.Format("{0},{1}", slwb.DefinedNames[i].Text, sPrintArea);
}
else
{
slwb.DefinedNames[i].Text = sPrintArea;
}
}
}
if (!bFound)
{
SLDefinedName dn = new SLDefinedName(SLConstants.PrintAreaDefinedName);
dn.LocalSheetId = (uint)iSheetPosition;
dn.Text = sPrintArea;
slwb.DefinedNames.Add(dn);
}
}
internal SLDefinedName Clone()
{
SLDefinedName dn = new SLDefinedName(this.Name);
dn.Text = this.Text;
dn.Name = this.Name;
dn.Comment = this.Comment;
dn.CustomMenu = this.CustomMenu;
dn.Description = this.Description;
dn.Help = this.Help;
dn.StatusBar = this.StatusBar;
dn.LocalSheetId = this.LocalSheetId;
dn.Hidden = this.Hidden;
dn.Function = this.Function;
dn.VbProcedure = this.VbProcedure;
dn.Xlm = this.Xlm;
dn.FunctionGroupId = this.FunctionGroupId;
dn.ShortcutKey = this.ShortcutKey;
dn.PublishToServer = this.PublishToServer;
dn.WorkbookParameter = this.WorkbookParameter;
return dn;
}
private void SetAddPrintArea(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool ToAdd)
{
if (StartRowIndex < 1) StartRowIndex = 1;
if (StartRowIndex > SLConstants.RowLimit) StartRowIndex = SLConstants.RowLimit;
if (StartColumnIndex < 1) StartColumnIndex = 1;
if (StartColumnIndex > SLConstants.ColumnLimit) StartColumnIndex = SLConstants.ColumnLimit;
if (EndRowIndex < 1) EndRowIndex = 1;
if (EndRowIndex > SLConstants.RowLimit) EndRowIndex = SLConstants.RowLimit;
if (EndColumnIndex < 1) EndColumnIndex = 1;
if (EndColumnIndex > SLConstants.ColumnLimit) EndColumnIndex = SLConstants.ColumnLimit;
// no overlapping checked.
int iSheetPosition = 0;
int i;
for (i = 0; i < slwb.Sheets.Count; ++i)
{
if (slwb.Sheets[i].Name.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
{
iSheetPosition = i;
break;
}
}
string sPrintArea = string.Empty;
if (StartRowIndex == EndRowIndex && StartColumnIndex == EndColumnIndex)
{
// why would you print just one cell? Even Excel questions this with a message box...
sPrintArea = SLTool.ToCellReference(gsSelectedWorksheetName, StartRowIndex, StartColumnIndex, true);
}
else
{
sPrintArea = SLTool.ToCellRange(gsSelectedWorksheetName, StartRowIndex, StartColumnIndex, EndRowIndex, EndColumnIndex, true);
}
bool bFound = false;
for (i = 0; i < slwb.DefinedNames.Count; ++i)
{
if (slwb.DefinedNames[i].Name.Equals(SLConstants.PrintAreaDefinedName, StringComparison.OrdinalIgnoreCase)
&& slwb.DefinedNames[i].LocalSheetId != null
&& slwb.DefinedNames[i].LocalSheetId.Value == iSheetPosition)
{
bFound = true;
if (ToAdd)
{
slwb.DefinedNames[i].Text = string.Format("{0},{1}", slwb.DefinedNames[i].Text, sPrintArea);
}
else
{
slwb.DefinedNames[i].Text = sPrintArea;
}
}
}
if (!bFound)
{
SLDefinedName dn = new SLDefinedName(SLConstants.PrintAreaDefinedName);
dn.LocalSheetId = (uint)iSheetPosition;
dn.Text = sPrintArea;
slwb.DefinedNames.Add(dn);
}
}
/// <summary>
/// Set a given defined name. If it doesn't exist, a new defined name is created. If it exists, then the existing defined name is overwritten.
/// </summary>
/// <param name="Name">Name of defined name. Note that it cannot be a valid cell reference such as A1. It also cannot start with "_xlnm" because it's reserved.</param>
/// <param name="Text">The reference/content text of the defined name. For example, Sheet1!$A$1:$C$3</param>
/// <param name="Comment">Comment for the defined name.</param>
/// <param name="Scope">The name of the worksheet that the defined name is effective in.</param>
/// <returns>True if the given defined name is created or an existing defined name is overwritten. False otherwise.</returns>
public bool SetDefinedName(string Name, string Text, string Comment, string Scope)
{
Name = Name.Trim();
if (SLTool.IsCellReference(Name))
{
return false;
}
// these are reserved names
if (Name.StartsWith("_xlnm")) return false;
if (Text.StartsWith("="))
{
if (Text.Length > 1) Text = Text.Substring(1);
else Text = "\"=\"";
}
uint? iLocalSheetId = null;
for (int i = 0; i < slwb.Sheets.Count; ++i)
{
if (slwb.Sheets[i].Name.Equals(Scope, StringComparison.OrdinalIgnoreCase))
{
iLocalSheetId = (uint)i;
break;
}
}
bool bFound = false;
SLDefinedName dn = new SLDefinedName(Name);
dn.Text = Text;
if (Comment != null && Comment.Length > 0) dn.Comment = Comment;
if (iLocalSheetId != null) dn.LocalSheetId = iLocalSheetId.Value;
foreach (SLDefinedName d in slwb.DefinedNames)
{
if (d.Name.Equals(Name, StringComparison.OrdinalIgnoreCase))
{
bFound = true;
d.Text = Text;
if (Comment != null && Comment.Length > 0) d.Comment = Comment;
break;
}
}
if (!bFound)
{
slwb.DefinedNames.Add(dn);
}
return true;
}
private void OpenExistingSpreadsheet(string SheetNameOnOpen)
{
xl = SpreadsheetDocument.Open(memstream, true);
wbp = xl.WorkbookPart;
IsNewSpreadsheet = false;
slwb = new SLWorkbook();
this.DocumentProperties = new SLDocumentProperties();
this.LoadDocumentProperties();
InitialiseAutoFitCache();
LoadBuiltInNumberingFormats();
InitialiseStylesheetWhatNots(SLThemeTypeValues.Office);
LoadSharedStringTable();
giWorksheetIdCounter = 0;
using (OpenXmlReader oxr = OpenXmlReader.Create(wbp))
{
SLWorkbookView wv;
Sheet s;
SLSheet sheet;
DefinedName dn;
SLDefinedName sldn;
while (oxr.Read())
{
if (oxr.ElementType == typeof(WorkbookView))
{
wv = new SLWorkbookView();
wv.FromWorkbookView((WorkbookView)oxr.LoadCurrentElement());
slwb.WorkbookViews.Add(wv);
}
else if (oxr.ElementType == typeof(Sheet))
{
s = (Sheet)oxr.LoadCurrentElement();
sheet = new SLSheet(s.Name.Value, s.SheetId.Value, s.Id.Value, SLSheetType.Unknown);
if (s.State != null) sheet.State = s.State.Value;
slwb.Sheets.Add(sheet);
if (sheet.SheetId > giWorksheetIdCounter)
{
giWorksheetIdCounter = (int)sheet.SheetId;
}
}
else if (oxr.ElementType == typeof(DefinedName))
{
dn = (DefinedName)oxr.LoadCurrentElement();
sldn = new SLDefinedName(dn.Name.Value);
sldn.FromDefinedName(dn);
slwb.DefinedNames.Add(sldn);
}
else if (oxr.ElementType == typeof(PivotCache))
{
// cache IDs supposed to be unique, so I'm not gonna check for the hash set
slwb.PivotTableCacheIds.Add(((PivotCache)oxr.LoadCurrentElement()).CacheId.Value);
}
}
}
if (wbp.Workbook.WorkbookProperties != null)
{
slwb.WorkbookProperties.FromWorkbookProperties(wbp.Workbook.WorkbookProperties);
}
if (wbp.CalculationChainPart != null)
{
int iCurrentSheetId = 0;
SLCalculationCell slcc = new SLCalculationCell(string.Empty);
CalculationCell cc;
using (OpenXmlReader oxr = OpenXmlReader.Create(wbp.CalculationChainPart))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(CalculationCell))
{
cc = (CalculationCell)oxr.LoadCurrentElement();
if (cc.SheetId == null)
{
cc.SheetId = iCurrentSheetId;
}
else
{
if (cc.SheetId.Value != iCurrentSheetId)
iCurrentSheetId = cc.SheetId.Value;
}
slcc.FromCalculationCell(cc);
slwb.CalculationCells.Add(slcc.Clone());
}
}
}
}
// To determine the type of sheet. Do this before the part
// where the table and pivot table parts are set.
bool bFound = false;
string sRelID = string.Empty;
foreach (SLSheet sheet in slwb.Sheets)
{
bFound = false;
foreach (WorksheetPart wspFound in wbp.WorksheetParts)
{
sRelID = wbp.GetIdOfPart(wspFound);
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
sheet.SheetType = SLSheetType.Worksheet;
bFound = true;
break;
}
}
if (!bFound)
{
foreach (ChartsheetPart csp in wbp.ChartsheetParts)
{
sRelID = wbp.GetIdOfPart(csp);
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
sheet.SheetType = SLSheetType.Chartsheet;
bFound = true;
break;
}
}
}
if (!bFound)
{
foreach (DialogsheetPart dsp in wbp.DialogsheetParts)
{
sRelID = wbp.GetIdOfPart(dsp);
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
sheet.SheetType = SLSheetType.DialogSheet;
bFound = true;
break;
}
}
}
if (!bFound)
{
foreach (MacroSheetPart msp in wbp.MacroSheetParts)
{
sRelID = wbp.GetIdOfPart(msp);
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
sheet.SheetType = SLSheetType.Macrosheet;
bFound = true;
break;
}
}
}
}
WorksheetPart wsp;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.SheetType == SLSheetType.Worksheet)
{
wsp = (WorksheetPart)wbp.GetPartById(sheet.Id);
foreach (TableDefinitionPart tdp in wsp.TableDefinitionParts)
{
if (tdp.Table.Id != null && !slwb.TableIds.Contains(tdp.Table.Id.Value))
slwb.TableIds.Add(tdp.Table.Id.Value);
if (tdp.Table.Name != null && !slwb.TableNames.Contains(tdp.Table.Name.Value))
slwb.TableNames.Add(tdp.Table.Name.Value);
}
foreach (PivotTablePart ptp in wsp.PivotTableParts)
{
if (ptp.PivotTableDefinition.Name != null
&& !slwb.PivotTableNames.Contains(ptp.PivotTableDefinition.Name.Value))
slwb.PivotTableNames.Add(ptp.PivotTableDefinition.Name.Value);
// the cache ID should already be added, from the workbook part above.
// But we check again just to be sure. Cache IDs have to be unique throughout
// the workbook.
if (ptp.PivotTableDefinition.CacheId != null
&& !slwb.PivotTableCacheIds.Contains(ptp.PivotTableDefinition.CacheId.Value))
slwb.PivotTableCacheIds.Add(ptp.PivotTableDefinition.CacheId.Value);
}
}
}
string sWorksheetName = SLConstants.DefaultFirstSheetName;
int i = 1;
bool bCannotFind = true;
bool bIsLegit = true;
if (wbp.WorksheetParts.Count() == 0)
{
// no worksheets! Apparently an Excel file with only 1 dialog sheet is perfectly legit...
// come up with a legit worksheet name that's not already taken...
i = 1;
bCannotFind = true;
while (bCannotFind)
{
sWorksheetName = string.Format("Sheet{0}", i);
bIsLegit = true;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(sWorksheetName, StringComparison.OrdinalIgnoreCase))
{
bIsLegit = false;
break;
}
}
++i;
if (bIsLegit) bCannotFind = false;
}
AddWorksheet(sWorksheetName);
}
else
{
bFound = false;
// there's a given worksheet name
if (SheetNameOnOpen.Length > 0)
{
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(SheetNameOnOpen, StringComparison.OrdinalIgnoreCase)
&& sheet.SheetType == SLSheetType.Worksheet)
{
giSelectedWorksheetID = sheet.SheetId;
gsSelectedWorksheetName = sheet.Name;
gsSelectedWorksheetRelationshipID = sheet.Id;
bFound = true;
break;
}
}
}
else
{
// we try to get the "actively selected" worksheet already selected.
uint iActiveTab = 0;
if (slwb.WorkbookViews.Count > 0)
{
iActiveTab = slwb.WorkbookViews[0].ActiveTab;
}
// there should be at least *this* number of sheets (whether it's a worksheet
// chartsheet or whatnot).
if (slwb.Sheets.Count > iActiveTab
&& slwb.Sheets[(int)iActiveTab].SheetType == SLSheetType.Worksheet)
{
giSelectedWorksheetID = slwb.Sheets[(int)iActiveTab].SheetId;
gsSelectedWorksheetName = slwb.Sheets[(int)iActiveTab].Name;
gsSelectedWorksheetRelationshipID = slwb.Sheets[(int)iActiveTab].Id;
bFound = true;
}
}
if (!bFound)
{
// we get here either if there's no given worksheet name (bFound is still false),
// or there's a given worksheet name but corresponding values weren't found.
// The given worksheet name must be that of a worksheet. A chartsheet name is
// considered "invalid".
// Either way, we use the first available worksheet as the selected worksheet.
wsp = wbp.WorksheetParts.First();
sRelID = wbp.GetIdOfPart(wsp);
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
{
giSelectedWorksheetID = sheet.SheetId;
gsSelectedWorksheetName = sheet.Name;
gsSelectedWorksheetRelationshipID = sheet.Id;
bFound = true;
break;
}
}
}
if (bFound)
{
// A viable worksheet should be found by now. Otherwise, it's probably
// a corrupted spreadsheet...
LoadSelectedWorksheet();
IsNewWorksheet = false;
}
else
{
// why is it not found!?! The file is corrupted somehow... we'll try to recover
// by adding a new worksheet and selecting it. Same algorithm as above.
i = 1;
bCannotFind = true;
while (bCannotFind)
{
sWorksheetName = string.Format("Sheet{0}", i);
bIsLegit = true;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(sWorksheetName, StringComparison.OrdinalIgnoreCase))
{
bIsLegit = false;
break;
}
}
++i;
if (bIsLegit) bCannotFind = false;
}
AddWorksheet(sWorksheetName);
}
}
}
/// <summary>
/// Set a given defined name. If it doesn't exist, a new defined name is created. If it exists, then the existing defined name is overwritten.
/// </summary>
/// <param name="Name">Name of defined name. Note that it cannot be a valid cell reference such as A1.</param>
/// <param name="Text">The reference/content text of the defined name. For example, Sheet1!$A$1:$C$3</param>
/// <param name="Comment">Comment for the defined name.</param>
/// <returns>True if the given defined name is created or an existing defined name is overwritten. False otherwise.</returns>
public bool SetDefinedName(string Name, string Text, string Comment)
{
Name = Name.Trim();
if (SLTool.IsCellReference(Name))
{
return false;
}
bool bFound = false;
SLDefinedName dn = new SLDefinedName(Name);
dn.Text = Text;
if (Comment != null && Comment.Length > 0) dn.Comment = Comment;
foreach (SLDefinedName d in slwb.DefinedNames)
{
if (d.Name.Equals(Name, StringComparison.InvariantCultureIgnoreCase))
{
bFound = true;
d.Text = Text;
if (Comment != null && Comment.Length > 0) d.Comment = Comment;
break;
}
}
if (!bFound)
{
slwb.DefinedNames.Add(dn);
}
return true;
}