/// <summary>
/// Import a text file as a data source, with the first data row and first data column at a specific cell.
/// </summary>
/// <param name="FileName">The file name.</param>
/// <param name="AnchorCellReference">The anchor cell reference, such as "A1".</param>
public void ImportText(string FileName, string AnchorCellReference)
{
var iRowIndex = -1;
var iColumnIndex = -1;
if (SLTool.FormatCellReferenceToRowColumnIndex(AnchorCellReference, out iRowIndex, out iColumnIndex))
{
ImportText(FileName, iRowIndex, iColumnIndex, null);
}
}
/// <summary>
/// Insert comment given the cell reference of the cell it's based on. This will overwrite any existing comment.
/// </summary>
/// <param name="CellReference">The cell reference, such as "A1".</param>
/// <param name="Comment">The cell comment.</param>
/// <returns>False if the cell reference is invalid. True otherwise.</returns>
public bool InsertComment(string CellReference, SLComment Comment)
{
var iRowIndex = -1;
var iColumnIndex = -1;
if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
{
return(false);
}
return(InsertComment(iRowIndex, iColumnIndex, Comment));
}
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="CellReference">The cell reference, such as "A1".</param>
/// <param name="HyperlinkType">The type of hyperlink.</param>
/// <param name="Address">
/// The URL for web pages, the file path for existing files, a cell reference (such as Sheet1!A1 or
/// Sheet1!A1:B5), a defined name or an email address. NOTE: Do NOT include the "mailto:" portion for email addresses.
/// </param>
/// <returns>True if successful. False otherwise.</returns>
public bool InsertHyperlink(string CellReference, SLHyperlinkTypeValues HyperlinkType, string Address)
{
var iRowIndex = -1;
var iColumnIndex = -1;
if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
{
return(false);
}
return(InsertHyperlink(iRowIndex, iColumnIndex, HyperlinkType, Address, null, null, false));
}
/// <summary>
/// Adds a print area to the existing print area on the currently selected worksheet given a corner cell of the print
/// area and the opposite corner cell.
/// </summary>
/// <param name="StartCellReference">The cell reference of the corner cell, such as "A1".</param>
/// <param name="EndCellReference">The cell reference of the opposite corner cell, such as "A1".</param>
public void AddToPrintArea(string StartCellReference, string EndCellReference)
{
var iStartRowIndex = -1;
var iStartColumnIndex = -1;
var iEndRowIndex = -1;
var iEndColumnIndex = -1;
SLTool.FormatCellReferenceToRowColumnIndex(StartCellReference, out iStartRowIndex, out iStartColumnIndex);
SLTool.FormatCellReferenceToRowColumnIndex(EndCellReference, out iEndRowIndex, out iEndColumnIndex);
SetAddPrintArea(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, true);
}
// merging spreadsheets is kinda like importing data, right? So it's here then.
// public void MergeSpreadsheet(string SpreadsheetFileName)
// {
// this.MergeSpreadsheet(true, SpreadsheetFileName, null);
// }
// public void MergeSpreadsheet(Stream SpreadsheetStream)
// {
// this.MergeSpreadsheet(false, null, SpreadsheetStream);
// }
// private void MergeSpreadsheet(bool IsFile, string SpreadsheetFileName, Stream SpreadsheetStream)
// {
// using (MemoryStream msAnother = new MemoryStream())
// {
// if (IsFile)
// {
// byte[] baData = File.ReadAllBytes(SpreadsheetFileName);
// msAnother.Write(baData, 0, baData.Length);
// }
// else
// {
// SpreadsheetStream.Position = 0;
// byte[] baData = new byte[SpreadsheetStream.Length];
// SpreadsheetStream.Read(baData, 0, baData.Length);
// msAnother.Write(baData, 0, baData.Length);
// }
// using (SpreadsheetDocument xlAnother = SpreadsheetDocument.Open(msAnother, false))
// {
// HashSet<string> hsCurrentSheetNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
// foreach (SLSheet sheet in slwb.Sheets)
// {
// // current sheet names supposed to be unique, so I'm not checking for collisions.
// hsCurrentSheetNames.Add(sheet.SheetName);
// }
// HashSet<string> hsAnotherSheetNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
// List<string> listAnotherSheetNames = new List<string>();
// using (OpenXmlReader oxr = OpenXmlReader.Create(xlAnother.WorkbookPart.Workbook.Sheets))
// {
// string sSheetName;
// while (oxr.Read())
// {
// if (oxr.ElementType == typeof(Sheet))
// {
// sSheetName = ((Sheet)oxr.LoadCurrentElement()).SheetName.Value;
// hsAnotherSheetNames.Add(sSheetName);
// listAnotherSheetNames.Add(sSheetName);
// }
// }
// }
////Sheet1
////Sheet2
////Sheet3
////Sheet1 -> Sheet7 -> Sheet8
////Sheet6
////Sheet7
// Dictionary<string, string> dictAnotherNewSheetNames = new Dictionary<string, string>();
// foreach (string s in listAnotherSheetNames)
// {
// }
// }
// // end of using SpreadsheetDocument
// }
// }
/// <summary>
/// Import a System.Data.DataTable as a data source, with the first data row and first data column at a specific cell.
/// </summary>
/// <param name="CellReference">The cell reference, such as "A1".</param>
/// <param name="Data">The data table.</param>
/// <param name="IncludeHeader">
/// True if the data table's column names are to be used in the first row as a header row.
/// False otherwise.
/// </param>
public void ImportDataTable(string CellReference, DataTable Data, bool IncludeHeader)
{
var iRowIndex = -1;
var iColumnIndex = -1;
if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
{
return;
}
ImportDataTable(iRowIndex, iColumnIndex, Data, IncludeHeader);
}
internal SLCalculationCell(string CellReference)
{
SetAllNull();
var iRowIndex = -1;
var iColumnIndex = -1;
if (SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
{
RowIndex = iRowIndex;
ColumnIndex = iColumnIndex;
}
}
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="CellReference">The cell reference, such as "A1".</param>
/// <param name="HyperlinkType">The type of hyperlink.</param>
/// <param name="Address">
/// The URL for web pages, the file path for existing files, a cell reference (such as Sheet1!A1 or
/// Sheet1!A1:B5), a defined name or an email address. NOTE: Do NOT include the "mailto:" portion for email addresses.
/// </param>
/// <param name="Display">The display text. Set null or an empty string to use the default.</param>
/// <param name="ToolTip">The tooltip (or screentip) text. Set null or an empty string to ignore this.</param>
/// <param name="OverwriteExistingCell">
/// True to overwrite the existing cell value with the hyperlink display text. False
/// otherwise.
/// </param>
/// <returns>True if successful. False otherwise.</returns>
public bool InsertHyperlink(string CellReference, SLHyperlinkTypeValues HyperlinkType, string Address,
string Display, string ToolTip, bool OverwriteExistingCell)
{
var iRowIndex = -1;
var iColumnIndex = -1;
if (!SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out iRowIndex, out iColumnIndex))
{
return(false);
}
return(InsertHyperlink(iRowIndex, iColumnIndex, HyperlinkType, Address, Display, ToolTip,
OverwriteExistingCell));
}
/// <summary>
/// Sort data by row.
/// </summary>
/// <param name="StartCellReference">
/// The cell reference of the start cell of the cell range to be sorted, such as "A1".
/// This is typically the top-left cell.
/// </param>
/// <param name="EndCellReference">
/// The cell reference of the end cell of the cell range to be sorted, such as "A1". This is
/// typically the bottom-right cell.
/// </param>
/// <param name="SortByRowIndex">The row index of the row to be sorted by.</param>
/// <param name="SortAscending">True to sort in ascending order. False to sort in descending order.</param>
public void Sort(string StartCellReference, string EndCellReference, int SortByRowIndex, bool SortAscending)
{
var iStartRowIndex = -1;
var iStartColumnIndex = -1;
var iEndRowIndex = -1;
var iEndColumnIndex = -1;
if (SLTool.FormatCellReferenceToRowColumnIndex(StartCellReference, out iStartRowIndex, out iStartColumnIndex) &&
SLTool.FormatCellReferenceToRowColumnIndex(EndCellReference, out iEndRowIndex, out iEndColumnIndex))
{
Sort(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, false, SortByRowIndex,
SortAscending);
}
}
/// <summary>
/// Sort data by column.
/// </summary>
/// <param name="StartCellReference">
/// The cell reference of the start cell of the cell range to be sorted, such as "A1".
/// This is typically the top-left cell.
/// </param>
/// <param name="EndCellReference">
/// The cell reference of the end cell of the cell range to be sorted, such as "A1". This is
/// typically the bottom-right cell.
/// </param>
/// <param name="SortByColumnName">The column name of the column to be sorted by, such as "AA".</param>
/// <param name="SortAscending">True to sort in ascending order. False to sort in descending order.</param>
public void Sort(string StartCellReference, string EndCellReference, string SortByColumnName, bool SortAscending)
{
var iStartRowIndex = -1;
var iStartColumnIndex = -1;
var iEndRowIndex = -1;
var iEndColumnIndex = -1;
var iSortByColumnIndex = -1;
if (SLTool.FormatCellReferenceToRowColumnIndex(StartCellReference, out iStartRowIndex, out iStartColumnIndex) &&
SLTool.FormatCellReferenceToRowColumnIndex(EndCellReference, out iEndRowIndex, out iEndColumnIndex))
{
iSortByColumnIndex = SLTool.ToColumnIndex(SortByColumnName);
Sort(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, true, iSortByColumnIndex,
SortAscending);
}
}
/// <summary>
/// Get existing comments in the currently selected worksheet. WARNING: This is only a snapshot. Any changes made to
/// the returned result are not used.
/// </summary>
/// <returns>A Dictionary of existing comments.</returns>
public Dictionary <SLCellPoint, SLRstType> GetCommentText()
{
var result = new Dictionary <SLCellPoint, SLRstType>();
// we don't add to existing comments, so it's either get existing comments
// or use the newly inserted comments.
if (!string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID))
{
var wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID);
if (wsp.WorksheetCommentsPart != null)
{
Comment comm;
int iRowIndex, iColumnIndex;
var rst = new SLRstType();
using (var oxr = OpenXmlReader.Create(wsp.WorksheetCommentsPart.Comments.CommentList))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Comment))
{
comm = (Comment)oxr.LoadCurrentElement();
SLTool.FormatCellReferenceToRowColumnIndex(comm.Reference.Value, out iRowIndex,
out iColumnIndex);
rst.FromCommentText(comm.CommentText);
result[new SLCellPoint(iRowIndex, iColumnIndex)] = rst.Clone();
}
}
}
}
else
{
var pts = slws.Comments.Keys.ToList();
foreach (var pt in pts)
{
result[pt] = slws.Comments[pt].rst.Clone();
}
}
}
return(result);
}
/// <summary>
/// Get the row and column indices given a cell reference such as "C5". A return value indicates whether the conversion
/// succeeded.
/// </summary>
/// <param name="CellReference">The cell reference in A1 format, such as "C5".</param>
/// <param name="RowIndex">
/// When this method returns, this contains the row index of the given cell reference if the
/// conversion succeeded.
/// </param>
/// <param name="ColumnIndex">
/// When this method returns, this contains the column index of the given cell reference if the
/// conversion succeeded.
/// </param>
/// <returns>True if the conversion succeeded. False otherwise.</returns>
public static bool WhatIsRowColumnIndex(string CellReference, out int RowIndex, out int ColumnIndex)
{
RowIndex = -1;
ColumnIndex = -1;
return(SLTool.FormatCellReferenceToRowColumnIndex(CellReference, out RowIndex, out ColumnIndex));
}
internal void FromTable(Table t)
{
SetAllNull();
if (t.AutoFilter != null)
{
AutoFilter.FromAutoFilter(t.AutoFilter);
HasAutoFilter = true;
}
if (t.SortState != null)
{
SortState.FromSortState(t.SortState);
HasSortState = true;
}
using (var oxr = OpenXmlReader.Create(t.TableColumns))
{
SLTableColumn tc;
while (oxr.Read())
{
if (oxr.ElementType == typeof(TableColumn))
{
tc = new SLTableColumn();
tc.FromTableColumn((TableColumn)oxr.LoadCurrentElement());
TableColumns.Add(tc);
}
}
}
if (t.TableStyleInfo != null)
{
TableStyleInfo.FromTableStyleInfo(t.TableStyleInfo);
HasTableStyleInfo = true;
}
Id = t.Id.Value;
if (t.Name != null)
{
Name = t.Name.Value;
}
sDisplayName = t.DisplayName.Value;
if (t.Comment != null)
{
Comment = t.Comment.Value;
}
var iStartRowIndex = 1;
var iStartColumnIndex = 1;
var iEndRowIndex = 1;
var iEndColumnIndex = 1;
var sRef = t.Reference.Value;
if (sRef.IndexOf(":") > 0)
{
if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex,
out iEndRowIndex, out iEndColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iEndRowIndex;
EndColumnIndex = iEndColumnIndex;
}
}
else
{
if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iStartRowIndex;
EndColumnIndex = iStartColumnIndex;
}
}
if (t.TableType != null)
{
TableType = t.TableType.Value;
}
if ((t.HeaderRowCount != null) && (t.HeaderRowCount.Value != 1))
{
HeaderRowCount = t.HeaderRowCount.Value;
}
if ((t.InsertRow != null) && t.InsertRow.Value)
{
InsertRow = t.InsertRow.Value;
}
if ((t.InsertRowShift != null) && t.InsertRowShift.Value)
{
InsertRowShift = t.InsertRowShift.Value;
}
if ((t.TotalsRowCount != null) && (t.TotalsRowCount.Value != 0))
{
TotalsRowCount = t.TotalsRowCount.Value;
}
if ((t.TotalsRowShown != null) && !t.TotalsRowShown.Value)
{
TotalsRowShown = t.TotalsRowShown.Value;
}
if ((t.Published != null) && t.Published.Value)
{
Published = t.Published.Value;
}
if (t.HeaderRowFormatId != null)
{
HeaderRowFormatId = t.HeaderRowFormatId.Value;
}
if (t.DataFormatId != null)
{
DataFormatId = t.DataFormatId.Value;
}
if (t.TotalsRowFormatId != null)
{
TotalsRowFormatId = t.TotalsRowFormatId.Value;
}
if (t.HeaderRowBorderFormatId != null)
{
HeaderRowBorderFormatId = t.HeaderRowBorderFormatId.Value;
}
if (t.BorderFormatId != null)
{
BorderFormatId = t.BorderFormatId.Value;
}
if (t.TotalsRowBorderFormatId != null)
{
TotalsRowBorderFormatId = t.TotalsRowBorderFormatId.Value;
}
if (t.HeaderRowCellStyle != null)
{
HeaderRowCellStyle = t.HeaderRowCellStyle.Value;
}
if (t.DataCellStyle != null)
{
DataCellStyle = t.DataCellStyle.Value;
}
if (t.TotalsRowCellStyle != null)
{
TotalsRowCellStyle = t.TotalsRowCellStyle.Value;
}
if (t.ConnectionId != null)
{
ConnectionId = t.ConnectionId.Value;
}
}