/// <summary>
/// Set the cell value given the row index and column index.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="Data">The cell value data.</param>
/// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns>
public bool SetCellValue(int RowIndex, int ColumnIndex, bool Data)
{
if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
return false;
}
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLCell c;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
}
else
{
c = new SLCell();
uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex);
if (iStyleIndex != 0)
{
c.StyleIndex = iStyleIndex;
}
}
c.DataType = CellValues.Boolean;
c.NumericValue = Data ? 1 : 0;
slws.Cells[pt] = c;
return true;
}
internal static bool IsSuitableCategoryHeader(Dictionary<SLCellPoint, SLCell> Cells, int RowIndex, int ColumnIndex)
{
bool result = false;
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
if (Cells.ContainsKey(pt))
{
if (Cells[pt].DataType == CellValues.String || Cells[pt].DataType == CellValues.SharedString)
{
result = true;
}
}
return result;
}
internal static bool IsSuitableCategoryHeader(Dictionary <SLCellPoint, SLCell> Cells, int RowIndex, int ColumnIndex)
{
bool result = false;
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
if (Cells.ContainsKey(pt))
{
if (Cells[pt].DataType == CellValues.String || Cells[pt].DataType == CellValues.SharedString)
{
result = true;
}
}
return(result);
}
/// <summary>
/// Insert comment given the row index and column index of the cell it's based on. This will overwrite any existing comment.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="Comment">The cell comment.</param>
/// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns>
public bool InsertComment(int RowIndex, int ColumnIndex, SLComment Comment)
{
if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
return false;
}
if (!slws.Authors.Contains(Comment.Author))
{
slws.Authors.Add(Comment.Author);
}
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLComment comm = Comment.Clone();
slws.Comments[pt] = comm;
return true;
}
/// <summary>
/// Insert comment given the row index and column index of the cell it's based on. This will overwrite any existing
/// comment.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="Comment">The cell comment.</param>
/// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns>
public bool InsertComment(int RowIndex, int ColumnIndex, SLComment Comment)
{
if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
return(false);
}
if (!slws.Authors.Contains(Comment.Author))
{
slws.Authors.Add(Comment.Author);
}
var pt = new SLCellPoint(RowIndex, ColumnIndex);
var comm = Comment.Clone();
slws.Comments[pt] = comm;
return(true);
}
/// <summary>
/// Set the cell value given the row index and column index. Be sure to follow up with a date format style.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="Data">The cell value data.</param>
/// <param name="Format">The format string used if the given date is before the date epoch. A date before the date epoch is stored as a string, so the date precision is only as good as the format string. For example, "dd/MM/yyyy HH:mm:ss" is more precise than "dd/MM/yyyy" because the latter loses information about the hours, minutes and seconds.</param>
/// <param name="For1904Epoch">True if using 1 Jan 1904 as the date epoch. False if using 1 Jan 1900 as the date epoch. This is independent of the workbook's Date1904 property.</param>
/// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns>
public bool SetCellValue(int RowIndex, int ColumnIndex, DateTime Data, string Format, bool For1904Epoch)
{
if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
return false;
}
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLCell c;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
}
else
{
c = new SLCell();
uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex);
if (iStyleIndex != 0)
{
c.StyleIndex = iStyleIndex;
}
}
if (For1904Epoch) slwb.WorkbookProperties.Date1904 = true;
double fDateTime = SLTool.CalculateDaysFromEpoch(Data, For1904Epoch);
// see CalculateDaysFromEpoch to see why there's a difference
double fDateCheck = For1904Epoch ? 0.0 : 1.0;
if (fDateTime < fDateCheck)
{
// given datetime is earlier than epoch
// So we set date to string format
c.DataType = CellValues.SharedString;
c.NumericValue = this.DirectSaveToSharedStringTable(Data.ToString(Format));
slws.Cells[pt] = c;
}
else
{
c.DataType = CellValues.Number;
c.NumericValue = fDateTime;
slws.Cells[pt] = c;
}
return true;
}
/// <summary>
/// Copy a range of cells from another worksheet to the currently selected worksheet, given the anchor cell of the destination range (top-left cell).
/// </summary>
/// <param name="WorksheetName">The name of the source worksheet.</param>
/// <param name="StartRowIndex">The row index of the start cell of the cell range. This is typically the top-left cell.</param>
/// <param name="StartColumnIndex">The column index of the start cell of the cell range. This is typically the top-left cell.</param>
/// <param name="EndRowIndex">The row index of the end cell of the cell range. This is typically the bottom-right cell.</param>
/// <param name="EndColumnIndex">The column index of the end cell of the cell range. This is typically the bottom-right cell.</param>
/// <param name="AnchorRowIndex">The row index of the anchor cell.</param>
/// <param name="AnchorColumnIndex">The column index of the anchor cell.</param>
/// <param name="PasteOption">Paste option.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool CopyCellFromWorksheet(string WorksheetName, int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, int AnchorRowIndex, int AnchorColumnIndex, SLPasteTypeValues PasteOption)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
if (WorksheetName.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
{
return this.CopyCell(iStartRowIndex, iStartColumnIndex, iEndRowIndex, iEndColumnIndex, AnchorRowIndex, AnchorColumnIndex, false);
}
string sRelId = string.Empty;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(WorksheetName, StringComparison.OrdinalIgnoreCase))
{
sRelId = sheet.Id;
break;
}
}
// there has to be a valid existing worksheet
if (sRelId.Length == 0) return false;
bool result = false;
if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit
&& iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit
&& iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit
&& iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit
&& AnchorRowIndex >= 1 && AnchorRowIndex <= SLConstants.RowLimit
&& AnchorColumnIndex >= 1 && AnchorColumnIndex <= SLConstants.ColumnLimit)
{
result = true;
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sRelId);
int i, j, iSwap, iStyleIndex, iStyleIndexNew;
SLCell origcell, newcell;
SLCellPoint pt, newpt;
int rowdiff = AnchorRowIndex - iStartRowIndex;
int coldiff = AnchorColumnIndex - iStartColumnIndex;
Dictionary<SLCellPoint, SLCell> cells = new Dictionary<SLCellPoint, SLCell>();
Dictionary<SLCellPoint, SLCell> sourcecells = new Dictionary<SLCellPoint, SLCell>();
Dictionary<int, uint> sourcecolstyleindex = new Dictionary<int, uint>();
Dictionary<int, uint> sourcerowstyleindex = new Dictionary<int, uint>();
string sCellRef = string.Empty;
HashSet<string> hsCellRef = new HashSet<string>();
// I use a hash set on the logic that it's easier to check a string hash (of cell references)
// first, rather than load a Cell class into SLCell and then check with row/column indices.
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
sCellRef = SLTool.ToCellReference(i, j);
if (!hsCellRef.Contains(sCellRef))
{
hsCellRef.Add(sCellRef);
}
}
}
// hyperlink ID, URL
Dictionary<string, string> hlurl = new Dictionary<string, string>();
List<SLHyperlink> sourcehyperlinks = new List<SLHyperlink>();
foreach (HyperlinkRelationship hlrel in wsp.HyperlinkRelationships)
{
if (hlrel.IsExternal)
{
hlurl[hlrel.Id] = hlrel.Uri.OriginalString;
}
}
using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
{
Column col;
int iColumnMin, iColumnMax;
Row r;
Cell c;
SLHyperlink hl;
while (oxr.Read())
{
if (oxr.ElementType == typeof(Column))
{
col = (Column)oxr.LoadCurrentElement();
iColumnMin = (int)col.Min.Value;
iColumnMax = (int)col.Max.Value;
for (i = iColumnMin; i <= iColumnMax; ++i)
{
sourcecolstyleindex[i] = (col.Style != null) ? col.Style.Value : 0;
}
}
else if (oxr.ElementType == typeof(Row))
{
r = (Row)oxr.LoadCurrentElement();
if (r.RowIndex != null)
{
if (r.StyleIndex != null) sourcerowstyleindex[(int)r.RowIndex.Value] = r.StyleIndex.Value;
else sourcerowstyleindex[(int)r.RowIndex.Value] = 0;
}
using (OpenXmlReader oxrRow = OpenXmlReader.Create(r))
{
while (oxrRow.Read())
{
if (oxrRow.ElementType == typeof(Cell))
{
c = (Cell)oxrRow.LoadCurrentElement();
if (c.CellReference != null)
{
sCellRef = c.CellReference.Value;
if (hsCellRef.Contains(sCellRef))
{
origcell = new SLCell();
origcell.FromCell(c);
// this should work because hsCellRef already contains valid cell references
SLTool.FormatCellReferenceToRowColumnIndex(sCellRef, out i, out j);
pt = new SLCellPoint(i, j);
sourcecells[pt] = origcell.Clone();
}
}
}
}
}
}
else if (oxr.ElementType == typeof(Hyperlink))
{
hl = new SLHyperlink();
hl.FromHyperlink((Hyperlink)oxr.LoadCurrentElement());
sourcehyperlinks.Add(hl);
}
}
}
Dictionary<int, uint> colstyleindex = new Dictionary<int, uint>();
Dictionary<int, uint> rowstyleindex = new Dictionary<int, uint>();
List<int> rowindexkeys = slws.RowProperties.Keys.ToList<int>();
SLRowProperties rp;
foreach (int rowindex in rowindexkeys)
{
rp = slws.RowProperties[rowindex];
rowstyleindex[rowindex] = rp.StyleIndex;
}
List<int> colindexkeys = slws.ColumnProperties.Keys.ToList<int>();
SLColumnProperties cp;
foreach (int colindex in colindexkeys)
{
cp = slws.ColumnProperties[colindex];
colstyleindex[colindex] = cp.StyleIndex;
}
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
newpt = new SLCellPoint(i + rowdiff, j + coldiff);
switch (PasteOption)
{
case SLPasteTypeValues.Formatting:
if (sourcecells.ContainsKey(pt))
{
origcell = sourcecells[pt];
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = origcell.StyleIndex;
cells[newpt] = newcell.Clone();
}
else
{
if (origcell.StyleIndex != 0)
{
// if not the default style, then must create a new
// destination cell.
newcell = new SLCell();
newcell.StyleIndex = origcell.StyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
else
{
// else source cell has default style.
// Now check if destination cell lies on a row/column
// that has non-default style. Remember, we don't have
// a destination cell here.
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = 0;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
}
else
{
// else no source cell
if (slws.Cells.ContainsKey(newpt))
{
iStyleIndex = 0;
if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = (uint)iStyleIndex;
cells[newpt] = newcell.Clone();
}
else
{
// else no source and no destination, so we check for row/column
// with non-default styles.
iStyleIndex = 0;
if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndex != 0 || iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
break;
case SLPasteTypeValues.Formulas:
if (sourcecells.ContainsKey(pt))
{
origcell = sourcecells[pt];
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone();
else newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
cells[newpt] = newcell.Clone();
}
else
{
newcell = new SLCell();
if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone();
else newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew;
cells[newpt] = newcell.Clone();
}
}
else
{
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.CellText = string.Empty;
newcell.DataType = CellValues.Number;
cells[newpt] = newcell.Clone();
}
// no else because don't have to do anything
}
break;
case SLPasteTypeValues.Paste:
if (sourcecells.ContainsKey(pt))
{
origcell = sourcecells[pt].Clone();
cells[newpt] = origcell.Clone();
}
else
{
// else the source cell is empty
if (slws.Cells.ContainsKey(newpt))
{
iStyleIndex = 0;
if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];
if (iStyleIndex != 0)
{
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
else
{
// if the source cell is empty, then direct pasting
// means overwrite the existing cell, which is faster
// by just removing it.
slws.Cells.Remove(newpt);
}
}
else
{
// else no source and no destination, so we check for row/column
// with non-default styles.
iStyleIndex = 0;
if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndex != 0 || iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
break;
case SLPasteTypeValues.Transpose:
newpt = new SLCellPoint(i - iStartRowIndex, j - iStartColumnIndex);
iSwap = newpt.RowIndex;
newpt.RowIndex = newpt.ColumnIndex;
newpt.ColumnIndex = iSwap;
newpt.RowIndex = newpt.RowIndex + iStartRowIndex + rowdiff;
newpt.ColumnIndex = newpt.ColumnIndex + iStartColumnIndex + coldiff;
// in case say the millionth row is transposed, because we can't have a millionth column.
if (newpt.RowIndex <= SLConstants.RowLimit && newpt.ColumnIndex <= SLConstants.ColumnLimit)
{
// this part is identical to normal paste
if (sourcecells.ContainsKey(pt))
{
origcell = sourcecells[pt].Clone();
cells[newpt] = origcell.Clone();
}
else
{
// else the source cell is empty
if (slws.Cells.ContainsKey(newpt))
{
iStyleIndex = 0;
if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];
if (iStyleIndex != 0)
{
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
else
{
// if the source cell is empty, then direct pasting
// means overwrite the existing cell, which is faster
// by just removing it.
slws.Cells.Remove(newpt);
}
}
else
{
// else no source and no destination, so we check for row/column
// with non-default styles.
iStyleIndex = 0;
if (sourcerowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)sourcerowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && sourcecolstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)sourcecolstyleindex[pt.ColumnIndex];
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndex != 0 || iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
}
break;
case SLPasteTypeValues.Values:
// this part is identical to the formula part, except
// for assigning the cell formula part.
if (sourcecells.ContainsKey(pt))
{
origcell = sourcecells[pt];
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
cells[newpt] = newcell.Clone();
}
else
{
newcell = new SLCell();
newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew;
cells[newpt] = newcell.Clone();
}
}
else
{
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.CellFormula = null;
newcell.CellText = string.Empty;
newcell.DataType = CellValues.Number;
cells[newpt] = newcell.Clone();
}
// no else because don't have to do anything
}
break;
}
}
}
int AnchorEndRowIndex = AnchorRowIndex + iEndRowIndex - iStartRowIndex;
int AnchorEndColumnIndex = AnchorColumnIndex + iEndColumnIndex - iStartColumnIndex;
for (i = AnchorRowIndex; i <= AnchorEndRowIndex; ++i)
{
for (j = AnchorColumnIndex; j <= AnchorEndColumnIndex; ++j)
{
// any cell within destination "paste" operation is taken out
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt)) slws.Cells.Remove(pt);
}
}
foreach (SLCellPoint cellkey in cells.Keys)
{
origcell = cells[cellkey];
// the source cells are from another worksheet. Don't know how to rearrange any
// cell references in cell formulas...
slws.Cells[cellkey] = origcell.Clone();
}
// See CopyCell() for the behaviour explanation
// I'm not going to figure out how to copy merged cells from the source worksheet
// and decide under what conditions the existing merged cells in the destination
// worksheet should be removed.
// So I'm going to just remove any merged cells in the delete range.
List<SLMergeCell> mca = this.GetWorksheetMergeCells();
foreach (SLMergeCell mc in mca)
{
if (mc.StartRowIndex >= AnchorRowIndex && mc.EndRowIndex <= AnchorEndRowIndex
&& mc.StartColumnIndex >= AnchorColumnIndex && mc.EndColumnIndex <= AnchorEndColumnIndex)
{
slws.MergeCells.Remove(mc);
}
}
// TODO: conditional formatting and data validations?
#region Hyperlinks
if (sourcehyperlinks.Count > 0)
{
// we only care if normal paste or transpose paste. Just like Excel.
if (PasteOption == SLPasteTypeValues.Paste || PasteOption == SLPasteTypeValues.Transpose)
{
List<SLHyperlink> copiedhyperlinks = new List<SLHyperlink>();
SLHyperlink hlCopied;
int iOverlapStartRowIndex = 1;
int iOverlapStartColumnIndex = 1;
int iOverlapEndRowIndex = 1;
int iOverlapEndColumnIndex = 1;
foreach (SLHyperlink hl in sourcehyperlinks)
{
// this comes from the separating axis theorem.
// See merged cells for more details.
// In this case however, we're doing stuff when there's overlapping.
if (!(iEndRowIndex < hl.Reference.StartRowIndex
|| iStartRowIndex > hl.Reference.EndRowIndex
|| iEndColumnIndex < hl.Reference.StartColumnIndex
|| iStartColumnIndex > hl.Reference.EndColumnIndex))
{
// get the overlapping region
iOverlapStartRowIndex = Math.Max(iStartRowIndex, hl.Reference.StartRowIndex);
iOverlapStartColumnIndex = Math.Max(iStartColumnIndex, hl.Reference.StartColumnIndex);
iOverlapEndRowIndex = Math.Min(iEndRowIndex, hl.Reference.EndRowIndex);
iOverlapEndColumnIndex = Math.Min(iEndColumnIndex, hl.Reference.EndColumnIndex);
// offset to the correctly pasted region
if (PasteOption == SLPasteTypeValues.Paste)
{
iOverlapStartRowIndex += rowdiff;
iOverlapStartColumnIndex += coldiff;
iOverlapEndRowIndex += rowdiff;
iOverlapEndColumnIndex += coldiff;
}
else
{
// can only be transpose. See if check above.
if (iOverlapEndRowIndex > SLConstants.ColumnLimit)
{
// probably won't happen. This means that after transpose,
// the end row index will flip to exceed the column limit.
// I don't feel like testing how Excel handles this, so
// I'm going to just take it as normal paste.
iOverlapStartRowIndex += rowdiff;
iOverlapStartColumnIndex += coldiff;
iOverlapEndRowIndex += rowdiff;
iOverlapEndColumnIndex += coldiff;
}
else
{
iOverlapStartRowIndex -= iStartRowIndex;
iOverlapStartColumnIndex -= iStartColumnIndex;
iOverlapEndRowIndex -= iStartRowIndex;
iOverlapEndColumnIndex -= iStartColumnIndex;
iSwap = iOverlapStartRowIndex;
iOverlapStartRowIndex = iOverlapStartColumnIndex;
iOverlapStartColumnIndex = iSwap;
iSwap = iOverlapEndRowIndex;
iOverlapEndRowIndex = iOverlapEndColumnIndex;
iOverlapEndColumnIndex = iSwap;
iOverlapStartRowIndex += (iStartRowIndex + rowdiff);
iOverlapStartColumnIndex += (iStartColumnIndex + coldiff);
iOverlapEndRowIndex += (iStartRowIndex + rowdiff);
iOverlapEndColumnIndex += (iStartColumnIndex + coldiff);
}
}
hlCopied = new SLHyperlink();
hlCopied = hl.Clone();
hlCopied.IsNew = true;
if (hlCopied.IsExternal)
{
if (hlurl.ContainsKey(hlCopied.Id))
{
hlCopied.HyperlinkUri = hlurl[hlCopied.Id];
if (hlCopied.HyperlinkUri.StartsWith("."))
{
// assume this is a relative file path such as ../ or ./
hlCopied.HyperlinkUriKind = UriKind.Relative;
}
else
{
hlCopied.HyperlinkUriKind = UriKind.Absolute;
}
hlCopied.Id = string.Empty;
}
}
hlCopied.Reference = new SLCellPointRange(iOverlapStartRowIndex, iOverlapStartColumnIndex, iOverlapEndRowIndex, iOverlapEndColumnIndex);
copiedhyperlinks.Add(hlCopied);
}
}
if (copiedhyperlinks.Count > 0)
{
slws.Hyperlinks.AddRange(copiedhyperlinks);
}
}
}
#endregion
#region Calculation cells
if (slwb.CalculationCells.Count > 0)
{
List<int> listToDelete = new List<int>();
int iRowIndex = -1;
int iColumnIndex = -1;
for (i = 0; i < slwb.CalculationCells.Count; ++i)
{
if (slwb.CalculationCells[i].SheetId == giSelectedWorksheetID)
{
iRowIndex = slwb.CalculationCells[i].RowIndex;
iColumnIndex = slwb.CalculationCells[i].ColumnIndex;
if (iRowIndex >= AnchorRowIndex && iRowIndex <= AnchorEndRowIndex
&& iColumnIndex >= AnchorColumnIndex && iColumnIndex <= AnchorEndColumnIndex)
{
// existing calculation cell lies within destination "paste" operation
if (!listToDelete.Contains(i)) listToDelete.Add(i);
}
}
}
for (i = listToDelete.Count - 1; i >= 0; --i)
{
slwb.CalculationCells.RemoveAt(listToDelete[i]);
}
}
#endregion
}
return result;
}
private bool CopyCell(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, int AnchorRowIndex, int AnchorColumnIndex, bool ToCut, SLPasteTypeValues PasteOption)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
bool result = false;
if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit
&& iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit
&& iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit
&& iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit
&& AnchorRowIndex >= 1 && AnchorRowIndex <= SLConstants.RowLimit
&& AnchorColumnIndex >= 1 && AnchorColumnIndex <= SLConstants.ColumnLimit
&& (iStartRowIndex != AnchorRowIndex || iStartColumnIndex != AnchorColumnIndex))
{
result = true;
int i, j, iSwap, iStyleIndex, iStyleIndexNew;
SLCell origcell, newcell;
SLCellPoint pt, newpt;
int rowdiff = AnchorRowIndex - iStartRowIndex;
int coldiff = AnchorColumnIndex - iStartColumnIndex;
Dictionary<SLCellPoint, SLCell> cells = new Dictionary<SLCellPoint, SLCell>();
Dictionary<int, uint> colstyleindex = new Dictionary<int, uint>();
Dictionary<int, uint> rowstyleindex = new Dictionary<int, uint>();
List<int> rowindexkeys = slws.RowProperties.Keys.ToList<int>();
SLRowProperties rp;
foreach (int rowindex in rowindexkeys)
{
rp = slws.RowProperties[rowindex];
rowstyleindex[rowindex] = rp.StyleIndex;
}
List<int> colindexkeys = slws.ColumnProperties.Keys.ToList<int>();
SLColumnProperties cp;
foreach (int colindex in colindexkeys)
{
cp = slws.ColumnProperties[colindex];
colstyleindex[colindex] = cp.StyleIndex;
}
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
newpt = new SLCellPoint(i + rowdiff, j + coldiff);
if (ToCut)
{
if (slws.Cells.ContainsKey(pt))
{
cells[newpt] = slws.Cells[pt].Clone();
slws.Cells.Remove(pt);
}
}
else
{
switch (PasteOption)
{
case SLPasteTypeValues.Formatting:
if (slws.Cells.ContainsKey(pt))
{
origcell = slws.Cells[pt];
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = origcell.StyleIndex;
cells[newpt] = newcell.Clone();
}
else
{
if (origcell.StyleIndex != 0)
{
// if not the default style, then must create a new
// destination cell.
newcell = new SLCell();
newcell.StyleIndex = origcell.StyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
else
{
// else source cell has default style.
// Now check if destination cell lies on a row/column
// that has non-default style. Remember, we don't have
// a destination cell here.
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = 0;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
}
else
{
// else no source cell
if (slws.Cells.ContainsKey(newpt))
{
iStyleIndex = 0;
if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = (uint)iStyleIndex;
cells[newpt] = newcell.Clone();
}
else
{
// else no source and no destination, so we check for row/column
// with non-default styles.
iStyleIndex = 0;
if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndex != 0 || iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
break;
case SLPasteTypeValues.Formulas:
if (slws.Cells.ContainsKey(pt))
{
origcell = slws.Cells[pt];
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone();
else newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
cells[newpt] = newcell.Clone();
}
else
{
newcell = new SLCell();
if (origcell.CellFormula != null) newcell.CellFormula = origcell.CellFormula.Clone();
else newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew;
cells[newpt] = newcell.Clone();
}
}
else
{
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.CellText = string.Empty;
newcell.DataType = CellValues.Number;
cells[newpt] = newcell.Clone();
}
// no else because don't have to do anything
}
break;
case SLPasteTypeValues.Paste:
if (slws.Cells.ContainsKey(pt))
{
origcell = slws.Cells[pt].Clone();
cells[newpt] = origcell.Clone();
}
else
{
// else the source cell is empty
if (slws.Cells.ContainsKey(newpt))
{
iStyleIndex = 0;
if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];
if (iStyleIndex != 0)
{
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
else
{
// if the source cell is empty, then direct pasting
// means overwrite the existing cell, which is faster
// by just removing it.
slws.Cells.Remove(newpt);
}
}
else
{
// else no source and no destination, so we check for row/column
// with non-default styles.
iStyleIndex = 0;
if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndex != 0 || iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
break;
case SLPasteTypeValues.Transpose:
newpt = new SLCellPoint(i - iStartRowIndex, j - iStartColumnIndex);
iSwap = newpt.RowIndex;
newpt.RowIndex = newpt.ColumnIndex;
newpt.ColumnIndex = iSwap;
newpt.RowIndex = newpt.RowIndex + iStartRowIndex + rowdiff;
newpt.ColumnIndex = newpt.ColumnIndex + iStartColumnIndex + coldiff;
// in case say the millionth row is transposed, because we can't have a millionth column.
if (newpt.RowIndex <= SLConstants.RowLimit && newpt.ColumnIndex <= SLConstants.ColumnLimit)
{
// this part is identical to normal paste
if (slws.Cells.ContainsKey(pt))
{
origcell = slws.Cells[pt].Clone();
cells[newpt] = origcell.Clone();
}
else
{
// else the source cell is empty
if (slws.Cells.ContainsKey(newpt))
{
iStyleIndex = 0;
if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];
if (iStyleIndex != 0)
{
newcell = slws.Cells[newpt].Clone();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
else
{
// if the source cell is empty, then direct pasting
// means overwrite the existing cell, which is faster
// by just removing it.
slws.Cells.Remove(newpt);
}
}
else
{
// else no source and no destination, so we check for row/column
// with non-default styles.
iStyleIndex = 0;
if (rowstyleindex.ContainsKey(pt.RowIndex)) iStyleIndex = (int)rowstyleindex[pt.RowIndex];
if (iStyleIndex == 0 && colstyleindex.ContainsKey(pt.ColumnIndex)) iStyleIndex = (int)colstyleindex[pt.ColumnIndex];
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndex != 0 || iStyleIndexNew != 0)
{
newcell = new SLCell();
newcell.StyleIndex = (uint)iStyleIndex;
newcell.CellText = string.Empty;
cells[newpt] = newcell.Clone();
}
}
}
}
break;
case SLPasteTypeValues.Values:
// this part is identical to the formula part, except
// for assigning the cell formula part.
if (slws.Cells.ContainsKey(pt))
{
origcell = slws.Cells[pt];
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
cells[newpt] = newcell.Clone();
}
else
{
newcell = new SLCell();
newcell.CellFormula = null;
newcell.CellText = origcell.CellText;
newcell.fNumericValue = origcell.fNumericValue;
newcell.DataType = origcell.DataType;
iStyleIndexNew = 0;
if (rowstyleindex.ContainsKey(newpt.RowIndex)) iStyleIndexNew = (int)rowstyleindex[newpt.RowIndex];
if (iStyleIndexNew == 0 && colstyleindex.ContainsKey(newpt.ColumnIndex)) iStyleIndexNew = (int)colstyleindex[newpt.ColumnIndex];
if (iStyleIndexNew != 0) newcell.StyleIndex = (uint)iStyleIndexNew;
cells[newpt] = newcell.Clone();
}
}
else
{
if (slws.Cells.ContainsKey(newpt))
{
newcell = slws.Cells[newpt].Clone();
newcell.CellFormula = null;
newcell.CellText = string.Empty;
newcell.DataType = CellValues.Number;
cells[newpt] = newcell.Clone();
}
// no else because don't have to do anything
}
break;
}
}
}
}
int AnchorEndRowIndex = AnchorRowIndex + iEndRowIndex - iStartRowIndex;
int AnchorEndColumnIndex = AnchorColumnIndex + iEndColumnIndex - iStartColumnIndex;
for (i = AnchorRowIndex; i <= AnchorEndRowIndex; ++i)
{
for (j = AnchorColumnIndex; j <= AnchorEndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
// any cell within destination "paste" operation is taken out
slws.Cells.Remove(pt);
}
}
}
int iNumberOfRows = iEndRowIndex - iStartRowIndex + 1;
if (AnchorRowIndex <= iStartRowIndex) iNumberOfRows = -iNumberOfRows;
int iNumberOfColumns = iEndColumnIndex - iStartColumnIndex + 1;
if (AnchorColumnIndex <= iStartColumnIndex) iNumberOfColumns = -iNumberOfColumns;
foreach (SLCellPoint cellkey in cells.Keys)
{
origcell = cells[cellkey];
if (PasteOption != SLPasteTypeValues.Transpose)
{
this.ProcessCellFormulaDelta(ref origcell, AnchorRowIndex, iNumberOfRows, AnchorColumnIndex, iNumberOfColumns);
}
else
{
this.ProcessCellFormulaDelta(ref origcell, AnchorRowIndex, iNumberOfColumns, AnchorColumnIndex, iNumberOfRows);
}
slws.Cells[cellkey] = origcell.Clone();
}
// TODO: tables!
// cutting and pasting into a region with merged cells unmerges the existing merged cells
// copying and pasting into a region with merged cells leaves existing merged cells alone.
// Why does Excel do that? Don't know.
// Will just standardise to leaving existing merged cells alone.
List<SLMergeCell> mca = this.GetWorksheetMergeCells();
foreach (SLMergeCell mc in mca)
{
if (mc.StartRowIndex >= iStartRowIndex && mc.EndRowIndex <= iEndRowIndex
&& mc.StartColumnIndex >= iStartColumnIndex && mc.EndColumnIndex <= iEndColumnIndex)
{
if (ToCut)
{
slws.MergeCells.Remove(mc);
}
if (PasteOption == SLPasteTypeValues.Transpose)
{
pt = new SLCellPoint(mc.StartRowIndex - iStartRowIndex, mc.StartColumnIndex - iStartColumnIndex);
iSwap = pt.RowIndex;
pt.RowIndex = pt.ColumnIndex;
pt.ColumnIndex = iSwap;
pt.RowIndex = pt.RowIndex + iStartRowIndex + rowdiff;
pt.ColumnIndex = pt.ColumnIndex + iStartColumnIndex + coldiff;
newpt = new SLCellPoint(mc.EndRowIndex - iStartRowIndex, mc.EndColumnIndex - iStartColumnIndex);
iSwap = newpt.RowIndex;
newpt.RowIndex = newpt.ColumnIndex;
newpt.ColumnIndex = iSwap;
newpt.RowIndex = newpt.RowIndex + iStartRowIndex + rowdiff;
newpt.ColumnIndex = newpt.ColumnIndex + iStartColumnIndex + coldiff;
this.MergeWorksheetCells(pt.RowIndex, pt.ColumnIndex, newpt.RowIndex, newpt.ColumnIndex);
}
else
{
this.MergeWorksheetCells(mc.StartRowIndex + rowdiff, mc.StartColumnIndex + coldiff, mc.EndRowIndex + rowdiff, mc.EndColumnIndex + coldiff);
}
}
}
// TODO: conditional formatting and data validations?
#region Hyperlinks
if (slws.Hyperlinks.Count > 0)
{
if (ToCut)
{
foreach (SLHyperlink hl in slws.Hyperlinks)
{
// if hyperlink is completely within copy range
if (iStartRowIndex <= hl.Reference.StartRowIndex
&& hl.Reference.EndRowIndex <= iEndRowIndex
&& iStartColumnIndex <= hl.Reference.StartColumnIndex
&& hl.Reference.EndColumnIndex <= iEndColumnIndex)
{
hl.Reference = new SLCellPointRange(hl.Reference.StartRowIndex + rowdiff,
hl.Reference.StartColumnIndex + coldiff,
hl.Reference.EndRowIndex + rowdiff,
hl.Reference.EndColumnIndex + coldiff);
}
// else don't change anything (Excel doesn't, so we don't).
}
}
else
{
// we only care if normal paste or transpose paste. Just like Excel.
if (PasteOption == SLPasteTypeValues.Paste || PasteOption == SLPasteTypeValues.Transpose)
{
List<SLHyperlink> copiedhyperlinks = new List<SLHyperlink>();
SLHyperlink hlCopied;
// hyperlink ID, URL
Dictionary<string, string> hlurl = new Dictionary<string, string>();
if (!string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID))
{
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID);
foreach (HyperlinkRelationship hlrel in wsp.HyperlinkRelationships)
{
if (hlrel.IsExternal)
{
hlurl[hlrel.Id] = hlrel.Uri.OriginalString;
}
}
}
int iOverlapStartRowIndex = 1;
int iOverlapStartColumnIndex = 1;
int iOverlapEndRowIndex = 1;
int iOverlapEndColumnIndex = 1;
foreach (SLHyperlink hl in slws.Hyperlinks)
{
// this comes from the separating axis theorem.
// See merged cells for more details.
// In this case however, we're doing stuff when there's overlapping.
if (!(iEndRowIndex < hl.Reference.StartRowIndex
|| iStartRowIndex > hl.Reference.EndRowIndex
|| iEndColumnIndex < hl.Reference.StartColumnIndex
|| iStartColumnIndex > hl.Reference.EndColumnIndex))
{
// get the overlapping region
iOverlapStartRowIndex = Math.Max(iStartRowIndex, hl.Reference.StartRowIndex);
iOverlapStartColumnIndex = Math.Max(iStartColumnIndex, hl.Reference.StartColumnIndex);
iOverlapEndRowIndex = Math.Min(iEndRowIndex, hl.Reference.EndRowIndex);
iOverlapEndColumnIndex = Math.Min(iEndColumnIndex, hl.Reference.EndColumnIndex);
// offset to the correctly pasted region
if (PasteOption == SLPasteTypeValues.Paste)
{
iOverlapStartRowIndex += rowdiff;
iOverlapStartColumnIndex += coldiff;
iOverlapEndRowIndex += rowdiff;
iOverlapEndColumnIndex += coldiff;
}
else
{
// can only be transpose. See if check above.
if (iOverlapEndRowIndex > SLConstants.ColumnLimit)
{
// probably won't happen. This means that after transpose,
// the end row index will flip to exceed the column limit.
// I don't feel like testing how Excel handles this, so
// I'm going to just take it as normal paste.
iOverlapStartRowIndex += rowdiff;
iOverlapStartColumnIndex += coldiff;
iOverlapEndRowIndex += rowdiff;
iOverlapEndColumnIndex += coldiff;
}
else
{
iOverlapStartRowIndex -= iStartRowIndex;
iOverlapStartColumnIndex -= iStartColumnIndex;
iOverlapEndRowIndex -= iStartRowIndex;
iOverlapEndColumnIndex -= iStartColumnIndex;
iSwap = iOverlapStartRowIndex;
iOverlapStartRowIndex = iOverlapStartColumnIndex;
iOverlapStartColumnIndex = iSwap;
iSwap = iOverlapEndRowIndex;
iOverlapEndRowIndex = iOverlapEndColumnIndex;
iOverlapEndColumnIndex = iSwap;
iOverlapStartRowIndex += (iStartRowIndex + rowdiff);
iOverlapStartColumnIndex += (iStartColumnIndex + coldiff);
iOverlapEndRowIndex += (iStartRowIndex + rowdiff);
iOverlapEndColumnIndex += (iStartColumnIndex + coldiff);
}
}
hlCopied = new SLHyperlink();
hlCopied = hl.Clone();
hlCopied.IsNew = true;
if (hlCopied.IsExternal)
{
if (hlurl.ContainsKey(hlCopied.Id))
{
hlCopied.HyperlinkUri = hlurl[hlCopied.Id];
if (hlCopied.HyperlinkUri.StartsWith("."))
{
// assume this is a relative file path such as ../ or ./
hlCopied.HyperlinkUriKind = UriKind.Relative;
}
else
{
hlCopied.HyperlinkUriKind = UriKind.Absolute;
}
hlCopied.Id = string.Empty;
}
}
hlCopied.Reference = new SLCellPointRange(iOverlapStartRowIndex, iOverlapStartColumnIndex, iOverlapEndRowIndex, iOverlapEndColumnIndex);
copiedhyperlinks.Add(hlCopied);
}
}
if (copiedhyperlinks.Count > 0)
{
slws.Hyperlinks.AddRange(copiedhyperlinks);
}
}
}
}
#endregion
#region Calculation cells
if (slwb.CalculationCells.Count > 0)
{
List<int> listToDelete = new List<int>();
int iRowIndex = -1;
int iColumnIndex = -1;
for (i = 0; i < slwb.CalculationCells.Count; ++i)
{
if (slwb.CalculationCells[i].SheetId == giSelectedWorksheetID)
{
iRowIndex = slwb.CalculationCells[i].RowIndex;
iColumnIndex = slwb.CalculationCells[i].ColumnIndex;
if (ToCut && iRowIndex >= iStartRowIndex && iRowIndex <= iEndRowIndex
&& iColumnIndex >= iStartColumnIndex && iColumnIndex <= iEndColumnIndex)
{
// just remove because recalculation of cell references is too complicated...
if (!listToDelete.Contains(i)) listToDelete.Add(i);
}
if (iRowIndex >= AnchorRowIndex && iRowIndex <= AnchorEndRowIndex
&& iColumnIndex >= AnchorColumnIndex && iColumnIndex <= AnchorEndColumnIndex)
{
// existing calculation cell lies within destination "paste" operation
if (!listToDelete.Contains(i)) listToDelete.Add(i);
}
}
}
for (i = listToDelete.Count - 1; i >= 0; --i)
{
slwb.CalculationCells.RemoveAt(listToDelete[i]);
}
}
#endregion
// defined names is hard to calculate...
// need to check the row and column indices based on the cell references within.
}
return result;
}
// TODO: Hyperlink cell range
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</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(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address,
string Display, string ToolTip, bool OverwriteExistingCell)
{
if ((RowIndex < 1) || (RowIndex > SLConstants.RowLimit))
{
return(false);
}
if ((ColumnIndex < 1) || (ColumnIndex > SLConstants.ColumnLimit))
{
return(false);
}
var hl = new SLHyperlink();
hl.IsNew = true;
hl.Reference = new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex);
switch (HyperlinkType)
{
case SLHyperlinkTypeValues.EmailAddress:
hl.IsExternal = true;
hl.HyperlinkUri = string.Format("mailto:{0}", Address);
hl.HyperlinkUriKind = UriKind.Absolute;
break;
case SLHyperlinkTypeValues.FilePath:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
// assume if it starts with ../ or ./ it's a relative path.
hl.HyperlinkUriKind = Address.StartsWith(".") ? UriKind.Relative : UriKind.Absolute;
break;
case SLHyperlinkTypeValues.InternalDocumentLink:
hl.IsExternal = false;
hl.Location = Address;
break;
case SLHyperlinkTypeValues.Url:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
hl.HyperlinkUriKind = UriKind.Absolute;
break;
}
if (Display == null)
{
hl.Display = Address;
}
else
{
if (Display.Length == 0)
{
hl.Display = Address;
}
else
{
hl.Display = Display;
}
}
if ((ToolTip != null) && (ToolTip.Length > 0))
{
hl.ToolTip = ToolTip;
}
var pt = new SLCellPoint(RowIndex, ColumnIndex);
SLCell c;
SLStyle style;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
style = new SLStyle();
if (c.StyleIndex < listStyle.Count)
{
style.FromHash(listStyle[(int)c.StyleIndex]);
}
else
{
style.FromHash(listStyle[0]);
}
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)SaveToStylesheet(style.ToHash());
if (OverwriteExistingCell)
{
// in case there's a formula
c.CellFormula = null;
c.DataType = CellValues.SharedString;
c.CellText = DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
}
// else don't have to do anything
slws.Cells[pt] = c.Clone();
}
else
{
c = new SLCell();
style = new SLStyle();
style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)SaveToStylesheet(style.ToHash());
c.DataType = CellValues.SharedString;
c.CellText = DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
slws.Cells[pt] = c.Clone();
}
slws.Hyperlinks.Add(hl);
return(true);
}
/// <summary>
/// Copy the style of one row to a range of rows.
/// </summary>
/// <param name="FromRowIndex">The row index of the row to be copied from.</param>
/// <param name="ToStartRowIndex">The row index of the start row of the row range. This is typically the top row.</param>
/// <param name="ToEndRowIndex">The row index of the end row of the row range. This is typically the bottom row.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool CopyRowStyle(int FromRowIndex, int ToStartRowIndex, int ToEndRowIndex)
{
int iStartRowIndex = 1, iEndRowIndex = 1;
bool result = false;
if (ToStartRowIndex < ToEndRowIndex)
{
iStartRowIndex = ToStartRowIndex;
iEndRowIndex = ToEndRowIndex;
}
else
{
iStartRowIndex = ToEndRowIndex;
iEndRowIndex = ToStartRowIndex;
}
if (FromRowIndex >= 1 && FromRowIndex <= SLConstants.RowLimit
&& iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit
&& iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit)
{
result = true;
uint iStyleIndex = 0;
if (slws.RowProperties.ContainsKey(FromRowIndex))
{
iStyleIndex = slws.RowProperties[FromRowIndex].StyleIndex;
}
SLRowProperties rp;
int i, j;
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
if (i != FromRowIndex)
{
if (iStyleIndex == 0)
{
if (slws.RowProperties.ContainsKey(i))
{
slws.RowProperties[i].StyleIndex = 0;
}
}
else
{
if (slws.RowProperties.ContainsKey(i))
{
slws.RowProperties[i].StyleIndex = iStyleIndex;
}
else
{
rp = new SLRowProperties(SimpleTheme.ThemeRowHeight);
rp.StyleIndex = iStyleIndex;
slws.RowProperties[i] = rp;
}
slws.RowColumnStyleHistory.Add(new SLRowColumnStyleHistory(true, i));
}
}
}
#region copying cell styles
SLCell cell;
SLCellPoint pt, destpt;
for (j = 1; j <= SLConstants.ColumnLimit; ++j)
{
pt = new SLCellPoint(FromRowIndex, j);
if (slws.Cells.ContainsKey(pt))
{
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
if (i != FromRowIndex)
{
destpt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(destpt))
{
slws.Cells[destpt].StyleIndex = slws.Cells[pt].StyleIndex;
}
else
{
cell = new SLCell();
cell.CellText = string.Empty;
cell.StyleIndex = slws.Cells[pt].StyleIndex;
slws.Cells[destpt] = cell;
}
}
}
}
else
{
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
if (i != FromRowIndex)
{
destpt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(destpt))
{
slws.Cells[destpt].StyleIndex = iStyleIndex;
}
// no need else because the default style will take over
}
}
}
}
#endregion
}
return result;
}
/// <summary>
/// Set the style of a range of cells.
/// </summary>
/// <param name="StartRowIndex">The row index of the start cell of the cell range. This is typically the top-left cell.</param>
/// <param name="StartColumnIndex">The column index of the start cell of the cell range. This is typically the top-left cell.</param>
/// <param name="EndRowIndex">The row index of the end cell of the cell range. This is typically the bottom-right cell.</param>
/// <param name="EndColumnIndex">The column index of the end cell of the cell range. This is typically the bottom-right cell.</param>
/// <param name="Style">The style to set.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool SetCellStyle(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, SLStyle Style)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
bool result = false;
if (iStartRowIndex >= 1 && iStartRowIndex <= SLConstants.RowLimit
&& iEndRowIndex >= 1 && iEndRowIndex <= SLConstants.RowLimit
&& iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit
&& iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit)
{
int iStyleIndex = this.SaveToStylesheet(Style.ToHash());
// default is 0, the default style
if (iStyleIndex > 0)
{
result = true;
SLCellPoint pt;
SLCell c;
int i, j;
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
c.StyleIndex = (uint)iStyleIndex;
slws.Cells[pt] = c;
}
else
{
c = new SLCell();
c.CellText = string.Empty;
c.StyleIndex = (uint)iStyleIndex;
slws.Cells[pt] = c;
}
}
}
}
}
return result;
}
/// <summary>
/// Sort data either by column or row.
/// </summary>
/// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param>
/// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param>
/// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param>
/// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param>
/// <param name="SortByColumn">True to sort by column. False to sort by row.</param>
/// <param name="SortByIndex">The row or column index of the row or column to be sorted by, depending on <paramref name="SortByColumn"/></param>
/// <param name="SortAscending">True to sort in ascending order. False to sort in descending order.</param>
public void Sort(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool SortByColumn, int SortByIndex, bool SortAscending)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
if (iStartRowIndex < 1) iStartRowIndex = 1;
if (iStartColumnIndex < 1) iStartColumnIndex = 1;
if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit;
if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;
// if the given index is out of the data range, then don't have to sort.
if (SortByColumn)
{
if (SortByIndex < iStartColumnIndex || SortByIndex > iEndColumnIndex) return;
}
else
{
if (SortByIndex < iStartRowIndex || SortByIndex > iEndRowIndex) return;
}
Dictionary<SLCellPoint, SLCell> datacells = new Dictionary<SLCellPoint, SLCell>();
SLCellPoint pt;
int i, j;
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
datacells[pt] = slws.Cells[pt].Clone();
slws.Cells.Remove(pt);
}
}
}
List<SLSortItem> listNumbers = new List<SLSortItem>();
List<SLSortItem> listText = new List<SLSortItem>();
List<SLSortItem> listBoolean = new List<SLSortItem>();
List<SLSortItem> listEmpty = new List<SLSortItem>();
bool bValue = false;
double fValue = 0.0;
string sText = string.Empty;
SLRstType rst;
int index = 0;
int iStartIndex = -1;
int iEndIndex = -1;
if (SortByColumn)
{
iStartIndex = iStartRowIndex;
iEndIndex = iEndRowIndex;
}
else
{
iStartIndex = iStartColumnIndex;
iEndIndex = iEndColumnIndex;
}
for (i = iStartIndex; i <= iEndIndex; ++i)
{
if (SortByColumn) pt = new SLCellPoint(i, SortByIndex);
else pt = new SLCellPoint(SortByIndex, i);
if (datacells.ContainsKey(pt))
{
if (datacells[pt].DataType == CellValues.Number)
{
if (datacells[pt].CellText != null)
{
if (double.TryParse(datacells[pt].CellText, out fValue))
{
listNumbers.Add(new SLSortItem() { Number = fValue, Index = i });
}
else
{
listText.Add(new SLSortItem() { Text = datacells[pt].CellText, Index = i });
}
}
else
{
listNumbers.Add(new SLSortItem() { Number = datacells[pt].NumericValue, Index = i });
}
}
else if (datacells[pt].DataType == CellValues.SharedString)
{
index = -1;
if (datacells[pt].CellText != null)
{
if (int.TryParse(datacells[pt].CellText, out index)
&& index >= 0 && index < listSharedString.Count)
{
rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
rst.FromSharedStringItem(new SharedStringItem() { InnerXml = listSharedString[index] });
listText.Add(new SLSortItem() { Text = rst.ToPlainString(), Index = i });
}
else
{
listText.Add(new SLSortItem() { Text = datacells[pt].CellText, Index = i });
}
}
else
{
index = Convert.ToInt32(datacells[pt].NumericValue);
if (index >= 0 && index < listSharedString.Count)
{
rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
rst.FromSharedStringItem(new SharedStringItem() { InnerXml = listSharedString[index] });
listText.Add(new SLSortItem() { Text = rst.ToPlainString(), Index = i });
}
else
{
listText.Add(new SLSortItem() { Text = datacells[pt].NumericValue.ToString(CultureInfo.InvariantCulture), Index = i });
}
}
}
else if (datacells[pt].DataType == CellValues.Boolean)
{
if (datacells[pt].CellText != null)
{
if (double.TryParse(datacells[pt].CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out fValue))
{
listBoolean.Add(new SLSortItem() { Number = fValue > 0.5 ? 1.0 : 0.0, Index = i });
}
else if (bool.TryParse(datacells[pt].CellText, out bValue))
{
listBoolean.Add(new SLSortItem() { Number = bValue ? 1.0 : 0.0, Index = i });
}
else
{
listText.Add(new SLSortItem() { Text = datacells[pt].CellText, Index = i });
}
}
else
{
listBoolean.Add(new SLSortItem() { Number = datacells[pt].NumericValue > 0.5 ? 1.0 : 0.0, Index = i });
}
}
else
{
listText.Add(new SLSortItem() { Text = datacells[pt].CellText, Index = i });
}
}
else
{
listEmpty.Add(new SLSortItem() { Index = i });
}
}
listNumbers.Sort(new SLSortItemNumberComparer());
if (!SortAscending) listNumbers.Reverse();
listText.Sort(new SLSortItemTextComparer());
if (!SortAscending) listText.Reverse();
listBoolean.Sort(new SLSortItemNumberComparer());
if (!SortAscending) listBoolean.Reverse();
Dictionary<int, int> ReverseIndex = new Dictionary<int,int>();
if (SortAscending)
{
j = iStartIndex;
for (i = 0; i < listNumbers.Count; ++i)
{
ReverseIndex[listNumbers[i].Index] = j;
++j;
}
for (i = 0; i < listText.Count; ++i)
{
ReverseIndex[listText[i].Index] = j;
++j;
}
for (i = 0; i < listBoolean.Count; ++i)
{
ReverseIndex[listBoolean[i].Index] = j;
++j;
}
for (i = 0; i < listEmpty.Count; ++i)
{
ReverseIndex[listEmpty[i].Index] = j;
++j;
}
}
else
{
j = iStartIndex;
for (i = 0; i < listBoolean.Count; ++i)
{
ReverseIndex[listBoolean[i].Index] = j;
++j;
}
for (i = 0; i < listText.Count; ++i)
{
ReverseIndex[listText[i].Index] = j;
++j;
}
for (i = 0; i < listNumbers.Count; ++i)
{
ReverseIndex[listNumbers[i].Index] = j;
++j;
}
for (i = 0; i < listEmpty.Count; ++i)
{
ReverseIndex[listEmpty[i].Index] = j;
++j;
}
}
List<SLCellPoint> listCellKeys = datacells.Keys.ToList<SLCellPoint>();
SLCellPoint newpt;
for (i = 0; i < listCellKeys.Count; ++i)
{
pt = listCellKeys[i];
if (SortByColumn)
{
if (ReverseIndex.ContainsKey(pt.RowIndex))
{
newpt = new SLCellPoint(ReverseIndex[pt.RowIndex], pt.ColumnIndex);
}
else
{
// shouldn't happen, but just in case...
newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex);
}
}
else
{
if (ReverseIndex.ContainsKey(pt.ColumnIndex))
{
newpt = new SLCellPoint(pt.RowIndex, ReverseIndex[pt.ColumnIndex]);
}
else
{
// shouldn't happen, but just in case...
newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex);
}
}
slws.Cells[newpt] = datacells[pt];
}
}
/// <summary>
/// Remove an existing hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
public void RemoveHyperlink(int RowIndex, int ColumnIndex)
{
if (RowIndex < 1 || RowIndex > SLConstants.RowLimit) return;
if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit) return;
// I'm assuming hyperlinks are tied to just one cell. Apparently,
// you can assign a block of cells as the hyperlink.
// Excel removes the cells of the hyperlink that are empty. I'm not going to even try...
List<SLCellPointRange> listdelete = new List<SLCellPointRange>();
int i, j;
WorksheetPart wsp;
string sRelId;
HyperlinkRelationship hlrel;
if (!IsNewWorksheet)
{
for (i = slws.Hyperlinks.Count - 1; i >= 0; --i)
{
if (slws.Hyperlinks[i].Reference.StartRowIndex <= RowIndex
&& RowIndex <= slws.Hyperlinks[i].Reference.EndRowIndex
&& slws.Hyperlinks[i].Reference.StartColumnIndex <= ColumnIndex
&& ColumnIndex <= slws.Hyperlinks[i].Reference.EndColumnIndex)
{
if (slws.Hyperlinks[i].IsNew)
{
slws.Hyperlinks.RemoveAt(i);
}
else
{
if (slws.Hyperlinks[i].IsExternal
&& slws.Hyperlinks[i].Id != null
&& slws.Hyperlinks[i].Id.Length > 0)
{
sRelId = slws.Hyperlinks[i].Id;
if (!string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID))
{
wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID);
hlrel = wsp.HyperlinkRelationships.Where(hlid => hlid.Id == sRelId).FirstOrDefault();
if (hlrel != null)
{
wsp.DeleteReferenceRelationship(hlrel);
}
}
}
slws.Hyperlinks.RemoveAt(i);
}
listdelete.Add(new SLCellPointRange(
slws.Hyperlinks[i].Reference.StartRowIndex,
slws.Hyperlinks[i].Reference.StartColumnIndex,
slws.Hyperlinks[i].Reference.EndRowIndex,
slws.Hyperlinks[i].Reference.EndColumnIndex));
}
}
}
else
{
// if it's a new worksheet, all hyperlinks are new.
// Most importantly, no hyperlink relationships were added, so
// we can just remove from the SLWorksheet list.
// Start from the end because we'll be deleting.
for (i = slws.Hyperlinks.Count - 1; i >= 0; --i)
{
if (slws.Hyperlinks[i].Reference.StartRowIndex <= RowIndex
&& RowIndex <= slws.Hyperlinks[i].Reference.EndRowIndex
&& slws.Hyperlinks[i].Reference.StartColumnIndex <= ColumnIndex
&& ColumnIndex <= slws.Hyperlinks[i].Reference.EndColumnIndex)
{
slws.Hyperlinks.RemoveAt(i);
listdelete.Add(new SLCellPointRange(
slws.Hyperlinks[i].Reference.StartRowIndex,
slws.Hyperlinks[i].Reference.StartColumnIndex,
slws.Hyperlinks[i].Reference.EndRowIndex,
slws.Hyperlinks[i].Reference.EndColumnIndex));
}
}
}
if (listdelete.Count > 0)
{
// remove hyperlink style
SLCell c;
SLCellPoint pt;
foreach (SLCellPointRange ptrange in listdelete)
{
for (i = ptrange.StartRowIndex; i <= ptrange.EndRowIndex; ++i)
{
for (j = ptrange.StartColumnIndex; j <= ptrange.EndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
c.StyleIndex = 0;
slws.Cells[pt] = c.Clone();
}
}
}
}
}
}
/// <summary>
/// Clear all cell content within specified rows and columns. If the top-left cell of a merged cell is within specified rows and columns, the merged cell content is also cleared.
/// </summary>
/// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param>
/// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param>
/// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param>
/// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param>
/// <returns>True if content has been cleared. False otherwise. If there are no content within specified rows and columns, false is also returned.</returns>
public bool ClearCellContent(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
bool result = false;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
if (iStartRowIndex < 1) iStartRowIndex = 1;
if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit;
if (iStartColumnIndex < 1) iStartColumnIndex = 1;
if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;
long iSize = (iEndRowIndex - iStartRowIndex + 1) * (iEndColumnIndex - iStartColumnIndex + 1);
int iRowIndex = -1, iColumnIndex = -1;
if (iSize <= (long)slws.Cells.Count)
{
SLCellPoint pt;
for (iRowIndex = iStartRowIndex; iRowIndex <= iEndRowIndex; ++iRowIndex)
{
for (iColumnIndex = iStartColumnIndex; iColumnIndex <= iEndColumnIndex; ++iColumnIndex)
{
pt = new SLCellPoint(iRowIndex, iColumnIndex);
if (slws.Cells.ContainsKey(pt))
{
this.ClearCellContentData(pt);
result = true;
}
}
}
}
else
{
List<SLCellPoint> list = slws.Cells.Keys.ToList<SLCellPoint>();
foreach (SLCellPoint pt in list)
{
if (iStartRowIndex <= pt.RowIndex && pt.RowIndex <= iEndRowIndex && iStartColumnIndex <= pt.ColumnIndex && pt.ColumnIndex <= iEndColumnIndex)
{
this.ClearCellContentData(pt);
result = true;
}
}
}
List<int> listToDelete = new List<int>();
int i;
for (i = 0; i < slwb.CalculationCells.Count; ++i)
{
if (slwb.CalculationCells[i].SheetId == giSelectedWorksheetID)
{
iRowIndex = slwb.CalculationCells[i].RowIndex;
iColumnIndex = slwb.CalculationCells[i].ColumnIndex;
if (iRowIndex >= iStartRowIndex && iRowIndex <= iEndRowIndex
&& iColumnIndex >= iStartColumnIndex && iColumnIndex <= iEndColumnIndex)
{
if (!listToDelete.Contains(i)) listToDelete.Add(i);
}
}
}
for (i = listToDelete.Count - 1; i >= 0; --i)
{
slwb.CalculationCells.RemoveAt(listToDelete[i]);
}
return result;
}
/// <summary>
/// Insert a table into the currently selected worksheet.
/// </summary>
/// <param name="Table">An SLTable object with the properties already set.</param>
/// <returns>True if the table is successfully inserted. False otherwise. If it failed, check if the given table overlaps any existing tables or merged cell range.</returns>
public bool InsertTable(SLTable Table)
{
// This is the separating axis theorem. See merging cells for more details.
// We're checking if the table collides with merged cells, the worksheet's autofilter range
// and existing tables.
// Technically, Excel unmerges cells when a table overlaps a merged cell range.
// We're just going to fail that.
bool result = true;
int i, j;
for (i = 0; i < slws.MergeCells.Count; ++i)
{
if (!(Table.EndRowIndex < slws.MergeCells[i].StartRowIndex
|| Table.StartRowIndex > slws.MergeCells[i].EndRowIndex
|| Table.EndColumnIndex < slws.MergeCells[i].StartColumnIndex
|| Table.StartColumnIndex > slws.MergeCells[i].EndColumnIndex))
{
result = false;
break;
}
}
if (slws.HasAutoFilter)
{
if (!(Table.EndRowIndex < slws.AutoFilter.StartRowIndex
|| Table.StartRowIndex > slws.AutoFilter.EndRowIndex
|| Table.EndColumnIndex < slws.AutoFilter.StartColumnIndex
|| Table.StartColumnIndex > slws.AutoFilter.EndColumnIndex))
{
result = false;
}
}
if (!result) return false;
for (i = 0; i < slws.Tables.Count; ++i)
{
if (!(Table.EndRowIndex < slws.Tables[i].StartRowIndex
|| Table.StartRowIndex > slws.Tables[i].EndRowIndex
|| Table.EndColumnIndex < slws.Tables[i].StartColumnIndex
|| Table.StartColumnIndex > slws.Tables[i].EndColumnIndex))
{
result = false;
break;
}
}
if (result)
{
// sorting first!
// We'll do just one level deep sorting. Multiple level sorting is hard...
if (Table.HasSortState && Table.SortState.SortConditions.Count > 0)
{
bool bSortAscending = true;
if (Table.SortState.SortConditions[0].Descending != null)
bSortAscending = !Table.SortState.SortConditions[0].Descending.Value;
this.Sort(Table.SortState.StartRowIndex, Table.SortState.StartColumnIndex,
Table.SortState.EndRowIndex, Table.SortState.EndColumnIndex, true,
Table.SortState.SortConditions[0].StartColumnIndex, bSortAscending);
}
// filtering next! Because rows might be hidden
int iStartRowIndex = -1;
int iEndRowIndex = -1;
if (Table.HeaderRowCount > 0) iStartRowIndex = Table.StartRowIndex + 1;
else iStartRowIndex = Table.StartRowIndex;
// not inclusive of the last totals row
iEndRowIndex = Table.EndRowIndex - 1;
SLTableColumn tc;
SLCellPoint pt;
List<SLCell> cells;
SLCell c;
string sResultText = string.Empty;
SLCalculationCell cc;
uint iStyleIndex;
for (j = 0; j < Table.TableColumns.Count; ++j)
{
tc = Table.TableColumns[j];
if (tc.TotalsRowLabel != null && tc.TotalsRowLabel.Length > 0)
{
c = new SLCell();
c.DataType = CellValues.SharedString;
c.NumericValue = this.DirectSaveToSharedStringTable(SLTool.XmlWrite(tc.TotalsRowLabel));
slws.Cells[new SLCellPoint(Table.EndRowIndex, Table.StartColumnIndex + j)] = c;
}
if (tc.HasTotalsRowFunction)
{
cells = new List<SLCell>();
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
pt = new SLCellPoint(i, Table.StartColumnIndex + j);
if (slws.Cells.ContainsKey(pt))
{
cells.Add(slws.Cells[pt].Clone());
}
}
c = new SLCell();
c.CellFormula = new SLCellFormula();
c.CellFormula.FormulaText = string.Format("SUBTOTAL({0},[{1}])", this.GetFunctionNumber(tc.TotalsRowFunction), tc.Name);
if (!this.Calculate(tc.TotalsRowFunction, cells, out sResultText))
{
c.DataType = CellValues.Error;
}
c.CellText = sResultText;
pt = new SLCellPoint(Table.EndRowIndex, Table.StartColumnIndex + j);
iStyleIndex = 0;
if (slws.RowProperties.ContainsKey(pt.RowIndex)) iStyleIndex = slws.RowProperties[pt.RowIndex].StyleIndex;
if (iStyleIndex == 0 && slws.ColumnProperties.ContainsKey(pt.ColumnIndex)) iStyleIndex = slws.ColumnProperties[pt.ColumnIndex].StyleIndex;
if (iStyleIndex != 0) c.StyleIndex = (uint)iStyleIndex;
slws.Cells[pt] = c;
cc = new SLCalculationCell(SLTool.ToCellReference(Table.EndRowIndex, Table.StartColumnIndex + j));
cc.SheetId = (int)giSelectedWorksheetID;
slwb.AddCalculationCell(cc);
}
}
if (slwb.HasTableName(Table.DisplayName) || Table.DisplayName.Contains(" "))
{
slwb.RefreshPossibleTableId();
Table.Id = slwb.PossibleTableId;
Table.sDisplayName = string.Format("Table{0}", Table.Id.ToString(CultureInfo.InvariantCulture));
Table.Name = Table.sDisplayName;
}
if (!slwb.TableIds.Contains(Table.Id)) slwb.TableIds.Add(Table.Id);
if (!slwb.TableNames.Contains(Table.DisplayName)) slwb.TableNames.Add(Table.DisplayName);
slws.Tables.Add(Table.Clone());
}
return result;
}
private void ClearCellContentData(SLCellPoint pt)
{
SLCell c = slws.Cells[pt];
c.CellFormula = null;
c.DataType = CellValues.Number;
c.NumericValue = 0;
// if the cell still has attributes (say the style index), then update it
// otherwise remove the cell
if (c.StyleIndex != 0 || c.CellMetaIndex != 0 || c.ValueMetaIndex != 0 || c.ShowPhonetic != false)
{
slws.Cells[pt] = c.Clone();
}
else
{
slws.Cells.Remove(pt);
}
}
/// <summary>
/// Clear all cell content within specified rows and columns. If the top-left cell of a merged cell is within specified rows and columns, the merged cell content is also cleared.
/// </summary>
/// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param>
/// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param>
/// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param>
/// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param>
/// <returns>True if content has been cleared. False otherwise. If there are no content within specified rows and columns, false is also returned.</returns>
public bool ClearCellContent(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
bool result = false;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
if (iStartRowIndex < 1) iStartRowIndex = 1;
if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit;
if (iStartColumnIndex < 1) iStartColumnIndex = 1;
if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;
long iSize = (iEndRowIndex - iStartRowIndex + 1) * (iEndColumnIndex - iStartColumnIndex + 1);
int iRowIndex = -1, iColumnIndex = -1;
if (iSize <= (long)slws.Cells.Count)
{
SLCellPoint pt;
for (iRowIndex = iStartRowIndex; iRowIndex <= iEndRowIndex; ++iRowIndex)
{
for (iColumnIndex = iStartColumnIndex; iColumnIndex <= iEndColumnIndex; ++iColumnIndex)
{
pt = new SLCellPoint(iRowIndex, iColumnIndex);
if (slws.Cells.ContainsKey(pt))
{
this.ClearCellContentData(pt);
result = true;
}
}
}
}
else
{
foreach (SLCellPoint pt in slws.Cells.Keys)
{
if (iStartRowIndex <= pt.RowIndex && pt.RowIndex <= iEndRowIndex && iStartColumnIndex <= pt.ColumnIndex && pt.ColumnIndex <= iEndColumnIndex)
{
this.ClearCellContentData(pt);
result = true;
}
}
}
return result;
}
/// <summary>
/// Get the cell value as a string.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <returns>A string cell value.</returns>
public string GetCellValueAsString(int RowIndex, int ColumnIndex)
{
string result = string.Empty;
int index = 0;
SLRstType rst = new SLRstType(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
if (slws.Cells.ContainsKey(pt))
{
SLCell c = slws.Cells[pt];
if (c.CellText != null)
{
if (c.DataType == CellValues.String)
{
result = c.CellText;
}
else if (c.DataType == CellValues.SharedString)
{
try
{
index = int.Parse(c.CellText);
if (index >= 0 && index < listSharedString.Count)
{
rst.FromHash(listSharedString[index]);
result = rst.ToPlainString();
}
else
{
result = SLTool.XmlRead(c.CellText);
}
}
catch
{
// something terrible just happened. We'll just use whatever's in the cell...
result = SLTool.XmlRead(c.CellText);
}
}
//else if (c.DataType == CellValues.InlineString)
//{
// // there shouldn't be any inline strings
// // because they'd already be transferred to shared strings
//}
else
{
result = SLTool.XmlRead(c.CellText);
}
}
else
{
if (c.DataType == CellValues.Number)
{
result = c.NumericValue.ToString(CultureInfo.InvariantCulture);
}
else if (c.DataType == CellValues.SharedString)
{
index = Convert.ToInt32(c.NumericValue);
if (index >= 0 && index < listSharedString.Count)
{
rst.FromHash(listSharedString[index]);
result = rst.ToPlainString();
}
else
{
result = SLTool.XmlRead(c.CellText);
}
}
else if (c.DataType == CellValues.Boolean)
{
if (c.NumericValue > 0.5) result = "true";
else result = "false";
}
}
}
}
return result;
}
/// <summary>
/// Get the cell value as a System.DateTime value. If the cell value wasn't originally a date/time value, the return value is undetermined.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="Format">The format string used to parse the date value in the cell if the date is before the date epoch. A date before the date epoch is stored as a string, so the date precision is only as good as the format string. For example, "dd/MM/yyyy HH:mm:ss" is more precise than "dd/MM/yyyy" because the latter loses information about the hours, minutes and seconds.</param>
/// <param name="For1904Epoch">True if using 1 Jan 1904 as the date epoch. False if using 1 Jan 1900 as the date epoch. This is independent of the workbook's Date1904 property.</param>
/// <returns>A System.DateTime cell value.</returns>
public DateTime GetCellValueAsDateTime(int RowIndex, int ColumnIndex, string Format, bool For1904Epoch)
{
DateTime dt;
if (For1904Epoch) dt = SLConstants.Epoch1904();
else dt = SLConstants.Epoch1900();
// If the cell data type is Number, then it's on or after the epoch.
// If it's a string or a shared string, then a string representation of the date
// is stored, where the date is before the epoch. Then we parse the string to
// get the date.
double fDateOffset = 0.0;
string sDate = string.Empty;
if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
if (slws.Cells.ContainsKey(pt))
{
SLCell c = slws.Cells[pt];
if (c.DataType == CellValues.Number)
{
if (c.CellText != null)
{
if (double.TryParse(c.CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out fDateOffset))
{
dt = SLTool.CalculateDateTimeFromDaysFromEpoch(fDateOffset, For1904Epoch);
}
}
else
{
dt = SLTool.CalculateDateTimeFromDaysFromEpoch(c.NumericValue, For1904Epoch);
}
}
else if (c.DataType == CellValues.SharedString)
{
SLRstType rst = new SLRstType(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
int index = 0;
try
{
if (c.CellText != null)
{
index = int.Parse(c.CellText);
}
else
{
index = Convert.ToInt32(c.NumericValue);
}
if (index >= 0 && index < listSharedString.Count)
{
rst.FromHash(listSharedString[index]);
sDate = rst.ToPlainString();
if (Format.Length > 0)
{
dt = DateTime.ParseExact(sDate, Format, CultureInfo.InvariantCulture);
}
else
{
dt = DateTime.Parse(sDate, CultureInfo.InvariantCulture);
}
}
// no else part, because there's nothing we can do!
// Just return the default date value...
}
catch
{
// something terrible just happened. (the shared string index probably
// isn't even correct!) Don't do anything...
}
}
else if (c.DataType == CellValues.String)
{
sDate = c.CellText ?? string.Empty;
try
{
if (Format.Length > 0)
{
dt = DateTime.ParseExact(sDate, Format, CultureInfo.InvariantCulture);
}
else
{
dt = DateTime.Parse(sDate, CultureInfo.InvariantCulture);
}
}
catch
{
// don't need to do anything. Just return the default date value.
// The point is to avoid throwing exceptions.
}
}
}
}
return dt;
}
/// <summary>
/// This returns a list of index with pixel lengths. Depending on the type,
/// the pixel length is for row heights or column widths
/// </summary>
/// <param name="IsRow"></param>
/// <param name="StartIndex"></param>
/// <param name="EndIndex"></param>
/// <param name="MaxPixelLength"></param>
/// <returns></returns>
internal Dictionary<int, int> AutoFitRowColumn(bool IsRow, int StartIndex, int EndIndex, int MaxPixelLength)
{
int i;
Dictionary<int, int> pixellength = new Dictionary<int, int>();
// initialise all to zero first. This also ensures the existence of a dictionary entry.
for (i = StartIndex; i <= EndIndex; ++i)
{
pixellength[i] = 0;
}
List<SLCellPoint> ptkeys = slws.Cells.Keys.ToList<SLCellPoint>();
SLCell c;
string sAutoFitSharedStringCacheKey;
string sAutoFitCacheKey;
double fCellValue;
SLRstType rst;
Text txt;
Run run;
FontSchemeValues vFontScheme;
int index;
SLStyle style = new SLStyle();
int iStyleIndex;
string sFontName;
double fFontSize;
bool bBold;
bool bItalic;
bool bStrike;
bool bUnderline;
System.Drawing.FontStyle drawstyle;
System.Drawing.Font ftUsable;
string sFormatCode;
string sDotNetFormatCode;
int iTextRotation;
System.Drawing.SizeF szf;
string sText;
float fWidth;
float fHeight;
int iPointIndex;
bool bSkipAdjustment;
SLCellPoint ptCheck;
// remove points that are part of merged cells
// Merged cells don't factor into autofitting.
// Start from end because we will be deleting points.
if (slws.MergeCells.Count > 0)
{
for (i = ptkeys.Count - 1; i >= 0; --i)
{
ptCheck = ptkeys[i];
foreach (SLMergeCell mc in slws.MergeCells)
{
if (mc.StartRowIndex <= ptCheck.RowIndex && ptCheck.RowIndex <= mc.EndRowIndex
&& mc.StartColumnIndex <= ptCheck.ColumnIndex && ptCheck.ColumnIndex <= mc.EndColumnIndex)
{
ptkeys.RemoveAt(i);
break;
}
}
}
}
HashSet<SLCellPoint> hsFilter = new HashSet<SLCellPoint>();
if (slws.HasAutoFilter)
{
for (i = slws.AutoFilter.StartColumnIndex; i <= slws.AutoFilter.EndColumnIndex; ++i)
{
hsFilter.Add(new SLCellPoint(slws.AutoFilter.StartRowIndex, i));
}
}
if (slws.Tables.Count > 0)
{
foreach (SLTable t in slws.Tables)
{
if (t.HasAutoFilter)
{
for (i = t.AutoFilter.StartColumnIndex; i <= t.AutoFilter.EndColumnIndex; ++i)
{
ptCheck = new SLCellPoint(t.AutoFilter.StartRowIndex, i);
if (!hsFilter.Contains(ptCheck))
{
hsFilter.Add(ptCheck);
}
}
}
}
}
// Excel seems to stop the maximum column pixel width at 2300 pixels (at least at 120 DPI).
// We need a bitmap of sufficient size because we're rendering the text and measuring it.
// 4096 pixels wide should cover the 2300 pixel thing. Note that this is also wider than
// typical screen monitors.
// 2048 pixels high should also cover most screen monitors' vertical height.
// If your text fills up the entire height of your screen, I would say your font size is
// too large...
// If you're doing this in some distant future where you can do spreadsheets on the
// freaking wall with Olympic pool sized screens, feel free to increase the dimensions.
using (System.Drawing.Bitmap bm = new System.Drawing.Bitmap(4096, 2048))
{
using (System.Drawing.Graphics g = System.Drawing.Graphics.FromImage(bm))
{
foreach (SLCellPoint pt in ptkeys)
{
if (IsRow) iPointIndex = pt.RowIndex;
else iPointIndex = pt.ColumnIndex;
if (StartIndex <= iPointIndex && iPointIndex <= EndIndex)
{
c = slws.Cells[pt];
iStyleIndex = (int)c.StyleIndex;
// assume if the font cache contains the style index,
// the other caches also have it.
if (dictAutoFitFontCache.ContainsKey(iStyleIndex))
{
ftUsable = dictAutoFitFontCache[iStyleIndex];
sDotNetFormatCode = dictAutoFitFormatCodeCache[iStyleIndex];
sFormatCode = sDotNetFormatCode;
iTextRotation = dictAutoFitTextRotationCache[iStyleIndex];
}
else
{
style = new SLStyle();
style.FromHash(listStyle[iStyleIndex]);
#region Get style stuff
sFontName = SimpleTheme.MinorLatinFont;
fFontSize = SLConstants.DefaultFontSize;
bBold = false;
bItalic = false;
bStrike = false;
bUnderline = false;
drawstyle = System.Drawing.FontStyle.Regular;
if (style.HasFont)
{
if (style.fontReal.HasFontScheme)
{
if (style.fontReal.FontScheme == FontSchemeValues.Major) sFontName = SimpleTheme.MajorLatinFont;
else if (style.fontReal.FontScheme == FontSchemeValues.Minor) sFontName = SimpleTheme.MinorLatinFont;
else if (style.fontReal.FontName != null && style.fontReal.FontName.Length > 0) sFontName = style.fontReal.FontName;
}
else
{
if (style.fontReal.FontName != null && style.fontReal.FontName.Length > 0) sFontName = style.fontReal.FontName;
}
if (style.fontReal.FontSize != null) fFontSize = style.fontReal.FontSize.Value;
if (style.fontReal.Bold != null && style.fontReal.Bold.Value) bBold = true;
if (style.fontReal.Italic != null && style.fontReal.Italic.Value) bItalic = true;
if (style.fontReal.Strike != null && style.fontReal.Strike.Value) bStrike = true;
if (style.fontReal.HasUnderline) bUnderline = true;
}
if (bBold) drawstyle |= System.Drawing.FontStyle.Bold;
if (bItalic) drawstyle |= System.Drawing.FontStyle.Italic;
if (bStrike) drawstyle |= System.Drawing.FontStyle.Strikeout;
if (bUnderline) drawstyle |= System.Drawing.FontStyle.Underline;
ftUsable = SLTool.GetUsableNormalFont(sFontName, fFontSize, drawstyle);
sFormatCode = style.FormatCode;
sDotNetFormatCode = SLTool.ToDotNetFormatCode(sFormatCode);
if (style.HasAlignment && style.alignReal.TextRotation != null)
{
iTextRotation = style.alignReal.TextRotation.Value;
}
else
{
iTextRotation = 0;
}
#endregion
dictAutoFitFontCache[iStyleIndex] = (System.Drawing.Font)ftUsable.Clone();
dictAutoFitFormatCodeCache[iStyleIndex] = sDotNetFormatCode;
dictAutoFitTextRotationCache[iStyleIndex] = iTextRotation;
}
sText = string.Empty;
fWidth = 0;
fHeight = 0;
// must set empty first! Used for checking if shared string and if should set into cache.
sAutoFitSharedStringCacheKey = string.Empty;
bSkipAdjustment = false;
if (c.DataType == CellValues.SharedString)
{
index = Convert.ToInt32(c.NumericValue);
sAutoFitSharedStringCacheKey = string.Format("{0}{1}{2}",
index.ToString(CultureInfo.InvariantCulture),
SLConstants.AutoFitCacheSeparator,
c.StyleIndex.ToString(CultureInfo.InvariantCulture));
if (dictAutoFitSharedStringCache.ContainsKey(sAutoFitSharedStringCacheKey))
{
fHeight = dictAutoFitSharedStringCache[sAutoFitSharedStringCacheKey].Height;
fWidth = dictAutoFitSharedStringCache[sAutoFitSharedStringCacheKey].Width;
bSkipAdjustment = true;
}
else if (index >= 0 && index < listSharedString.Count)
{
rst = new SLRstType();
rst.FromHash(listSharedString[index]);
if (rst.istrReal.ChildElements.Count == 1 && rst.istrReal.Text != null)
{
sText = rst.istrReal.Text.Text.TrimEnd();
sAutoFitCacheKey = string.Format("{0}{1}{2}", sText, SLConstants.AutoFitCacheSeparator, iStyleIndex.ToString(CultureInfo.InvariantCulture));
if (dictAutoFitTextCache.ContainsKey(sAutoFitCacheKey))
{
szf = dictAutoFitTextCache[sAutoFitCacheKey];
fHeight = szf.Height;
fWidth = szf.Width;
}
else
{
szf = SLTool.MeasureText(bm, g, sText, ftUsable);
fHeight = szf.Height;
fWidth = szf.Width;
dictAutoFitTextCache[sAutoFitCacheKey] = new System.Drawing.SizeF(fWidth, fHeight);
}
}
else
{
i = 0;
foreach (var child in rst.istrReal.ChildElements.Reverse())
{
if (child is Text || child is Run)
{
if (child is Text)
{
txt = (Text)child;
sText = txt.Text;
// the last element has the trailing spaces ignored. Hence the Reverse() above.
if (i == 0) sText = sText.TrimEnd();
szf = SLTool.MeasureText(bm, g, sText, ftUsable);
if (szf.Height > fHeight) fHeight = szf.Height;
fWidth += szf.Width;
}
else if (child is Run)
{
sText = string.Empty;
sFontName = (ftUsable.Name != null && ftUsable.Name.Length > 0) ? ftUsable.Name : SimpleTheme.MinorLatinFont;
fFontSize = ftUsable.SizeInPoints;
bBold = ((ftUsable.Style & System.Drawing.FontStyle.Bold) > 0) ? true : false;
bItalic = ((ftUsable.Style & System.Drawing.FontStyle.Italic) > 0) ? true : false;
bStrike = ((ftUsable.Style & System.Drawing.FontStyle.Strikeout) > 0) ? true : false;
bUnderline = ((ftUsable.Style & System.Drawing.FontStyle.Underline) > 0) ? true : false;
drawstyle = System.Drawing.FontStyle.Regular;
run = (Run)child;
sText = run.Text.Text;
vFontScheme = FontSchemeValues.None;
#region Run properties
if (run.RunProperties != null)
{
foreach (var grandchild in run.RunProperties.ChildElements)
{
if (grandchild is RunFont)
{
sFontName = ((RunFont)grandchild).Val;
}
else if (grandchild is FontSize)
{
fFontSize = ((FontSize)grandchild).Val;
}
else if (grandchild is Bold)
{
Bold b = (Bold)grandchild;
if (b.Val == null) bBold = true;
else bBold = b.Val.Value;
}
else if (grandchild is Italic)
{
Italic itlc = (Italic)grandchild;
if (itlc.Val == null) bItalic = true;
else bItalic = itlc.Val.Value;
}
else if (grandchild is Strike)
{
Strike strk = (Strike)grandchild;
if (strk.Val == null) bStrike = true;
else bStrike = strk.Val.Value;
}
else if (grandchild is Underline)
{
Underline und = (Underline)grandchild;
if (und.Val == null)
{
bUnderline = true;
}
else
{
if (und.Val.Value != UnderlineValues.None) bUnderline = true;
else bUnderline = false;
}
}
else if (grandchild is FontScheme)
{
vFontScheme = ((FontScheme)grandchild).Val;
}
}
}
#endregion
if (vFontScheme == FontSchemeValues.Major) sFontName = SimpleTheme.MajorLatinFont;
else if (vFontScheme == FontSchemeValues.Minor) sFontName = SimpleTheme.MinorLatinFont;
if (bBold) drawstyle |= System.Drawing.FontStyle.Bold;
if (bItalic) drawstyle |= System.Drawing.FontStyle.Italic;
if (bStrike) drawstyle |= System.Drawing.FontStyle.Strikeout;
if (bUnderline) drawstyle |= System.Drawing.FontStyle.Underline;
// the last element has the trailing spaces ignored. Hence the Reverse() above.
if (i == 0) sText = sText.TrimEnd();
szf = SLTool.MeasureText(bm, g, sText, SLTool.GetUsableNormalFont(sFontName, fFontSize, drawstyle));
if (szf.Height > fHeight) fHeight = szf.Height;
fWidth += szf.Width;
}
++i;
}
}
}
}
}
else
{
if (c.DataType == CellValues.Number)
{
#region Numbers
if (sDotNetFormatCode.Length > 0)
{
if (c.CellText != null)
{
if (!double.TryParse(c.CellText, out fCellValue))
{
fCellValue = 0;
}
if (sFormatCode.Equals("@"))
{
sText = fCellValue.ToString(CultureInfo.InvariantCulture);
}
else
{
sText = SLTool.ToSampleDisplayFormat(fCellValue, sDotNetFormatCode);
}
}
else
{
if (sFormatCode.Equals("@"))
{
sText = c.NumericValue.ToString(CultureInfo.InvariantCulture);
}
else
{
sText = SLTool.ToSampleDisplayFormat(c.NumericValue, sDotNetFormatCode);
}
}
}
else
{
if (c.CellText != null)
{
if (!double.TryParse(c.CellText, out fCellValue))
{
fCellValue = 0;
}
sText = SLTool.ToSampleDisplayFormat(fCellValue, "G10");
}
else
{
sText = SLTool.ToSampleDisplayFormat(c.NumericValue, "G10");
}
}
#endregion
}
else if (c.DataType == CellValues.Boolean)
{
if (c.NumericValue > 0.5) sText = "TRUE";
else sText = "FALSE";
}
else
{
if (c.CellText != null) sText = c.CellText;
else sText = string.Empty;
}
sAutoFitCacheKey = string.Format("{0}{1}{2}", sText, SLConstants.AutoFitCacheSeparator, iStyleIndex.ToString(CultureInfo.InvariantCulture));
if (dictAutoFitTextCache.ContainsKey(sAutoFitCacheKey))
{
szf = dictAutoFitTextCache[sAutoFitCacheKey];
fHeight = szf.Height;
fWidth = szf.Width;
}
else
{
szf = SLTool.MeasureText(bm, g, sText, ftUsable);
fHeight = szf.Height;
fWidth = szf.Width;
dictAutoFitTextCache[sAutoFitCacheKey] = new System.Drawing.SizeF(fWidth, fHeight);
}
}
if (!bSkipAdjustment)
{
// Through empirical experimental data, it appears that there's still a bit of padding
// at the end of the column when autofitting column widths. I don't know how to
// calculate this padding. So I guess. I experimented with the widths of obvious
// characters such as a space, an exclamation mark, a period.
// Then I remember there's the documentation on the Open XML class property
// Column.Width, which says there's an extra 5 pixels, 2 pixels on the left/right
// and a pixel for the gridlines.
// Note that this padding appears to change depending on the font/typeface and
// font size used. (Haha... where have I seen this before...) So 5 pixels doesn't
// seem to work exactly. Or maybe it's wrong because the method of measuring isn't
// what Excel actually uses to measure the text.
// Since we're autofitting, it seems fitting (haha) that the column width is slightly
// larger to accomodate the text. So it's best to err on the larger side.
// Thus we add 7 instead of the "recommended" or "documented" 5 pixels, 1 extra pixel
// on the left and right.
fWidth += 7;
// I could also have used 8, but it might have been too much of an extra padding.
// The number 8 is a lucky number in Chinese culture. Goodness knows I need some
// luck figuring out what Excel is doing...
if (iTextRotation != 0)
{
szf = SLTool.CalculateOuterBoundsOfRotatedRectangle(fWidth, fHeight, iTextRotation);
fHeight = szf.Height;
fWidth = szf.Width;
}
// meaning the shared string portion was accessed (otherwise it'd be empty string)
if (sAutoFitSharedStringCacheKey.Length > 0)
{
dictAutoFitSharedStringCache[sAutoFitSharedStringCacheKey] = new System.Drawing.SizeF(fWidth, fHeight);
}
}
if (IsRow)
{
if (fHeight > MaxPixelLength) fHeight = MaxPixelLength;
if (pixellength[iPointIndex] < fHeight)
{
pixellength[iPointIndex] = Convert.ToInt32(Math.Ceiling(fHeight));
}
}
else
{
if (hsFilter.Contains(pt)) fWidth += SLConstants.AutoFilterPixelWidth;
if (fWidth > MaxPixelLength) fWidth = MaxPixelLength;
if (pixellength[iPointIndex] < fWidth)
{
pixellength[iPointIndex] = Convert.ToInt32(Math.Ceiling(fWidth));
}
}
}
}
// end of Graphics
}
}
return pixellength;
}
/// <summary>
/// Sort data either by column or row.
/// </summary>
/// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param>
/// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param>
/// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param>
/// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param>
/// <param name="SortByColumn">True to sort by column. False to sort by row.</param>
/// <param name="SortByIndex">
/// The row or column index of the row or column to be sorted by, depending on
/// <paramref name="SortByColumn" />
/// </param>
/// <param name="SortAscending">True to sort in ascending order. False to sort in descending order.</param>
public void Sort(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool SortByColumn,
int SortByIndex, bool SortAscending)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
if (iStartRowIndex < 1)
{
iStartRowIndex = 1;
}
if (iStartColumnIndex < 1)
{
iStartColumnIndex = 1;
}
if (iEndRowIndex > SLConstants.RowLimit)
{
iEndRowIndex = SLConstants.RowLimit;
}
if (iEndColumnIndex > SLConstants.ColumnLimit)
{
iEndColumnIndex = SLConstants.ColumnLimit;
}
// if the given index is out of the data range, then don't have to sort.
if (SortByColumn)
{
if ((SortByIndex < iStartColumnIndex) || (SortByIndex > iEndColumnIndex))
{
return;
}
}
else
{
if ((SortByIndex < iStartRowIndex) || (SortByIndex > iEndRowIndex))
{
return;
}
}
var datacells = new Dictionary <SLCellPoint, SLCell>();
SLCellPoint pt;
int i, j;
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
datacells[pt] = slws.Cells[pt].Clone();
slws.Cells.Remove(pt);
}
}
}
var listNumbers = new List <SLSortItem>();
var listText = new List <SLSortItem>();
var listBoolean = new List <SLSortItem>();
var listEmpty = new List <SLSortItem>();
var bValue = false;
var fValue = 0.0;
var sText = string.Empty;
SLRstType rst;
var index = 0;
var iStartIndex = -1;
var iEndIndex = -1;
if (SortByColumn)
{
iStartIndex = iStartRowIndex;
iEndIndex = iEndRowIndex;
}
else
{
iStartIndex = iStartColumnIndex;
iEndIndex = iEndColumnIndex;
}
for (i = iStartIndex; i <= iEndIndex; ++i)
{
if (SortByColumn)
{
pt = new SLCellPoint(i, SortByIndex);
}
else
{
pt = new SLCellPoint(SortByIndex, i);
}
if (datacells.ContainsKey(pt))
{
if (datacells[pt].DataType == CellValues.Number)
{
if (datacells[pt].CellText != null)
{
if (double.TryParse(datacells[pt].CellText, out fValue))
{
listNumbers.Add(new SLSortItem {
Number = fValue, Index = i
});
}
else
{
listText.Add(new SLSortItem {
Text = datacells[pt].CellText, Index = i
});
}
}
else
{
listNumbers.Add(new SLSortItem {
Number = datacells[pt].NumericValue, Index = i
});
}
}
else if (datacells[pt].DataType == CellValues.SharedString)
{
index = -1;
if (datacells[pt].CellText != null)
{
if (int.TryParse(datacells[pt].CellText, out index) &&
(index >= 0) && (index < listSharedString.Count))
{
rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont,
SLConstants.OfficeThemeMinorLatinFont, new List <Color>(), new List <Color>());
rst.FromSharedStringItem(new SharedStringItem {
InnerXml = listSharedString[index]
});
listText.Add(new SLSortItem {
Text = rst.ToPlainString(), Index = i
});
}
else
{
listText.Add(new SLSortItem {
Text = datacells[pt].CellText, Index = i
});
}
}
else
{
index = Convert.ToInt32(datacells[pt].NumericValue);
if ((index >= 0) && (index < listSharedString.Count))
{
rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont,
SLConstants.OfficeThemeMinorLatinFont, new List <Color>(), new List <Color>());
rst.FromSharedStringItem(new SharedStringItem {
InnerXml = listSharedString[index]
});
listText.Add(new SLSortItem {
Text = rst.ToPlainString(), Index = i
});
}
else
{
listText.Add(new SLSortItem
{
Text = datacells[pt].NumericValue.ToString(CultureInfo.InvariantCulture),
Index = i
});
}
}
}
else if (datacells[pt].DataType == CellValues.Boolean)
{
if (datacells[pt].CellText != null)
{
if (double.TryParse(datacells[pt].CellText, NumberStyles.Any, CultureInfo.InvariantCulture,
out fValue))
{
listBoolean.Add(new SLSortItem {
Number = fValue > 0.5 ? 1.0 : 0.0, Index = i
});
}
else if (bool.TryParse(datacells[pt].CellText, out bValue))
{
listBoolean.Add(new SLSortItem {
Number = bValue ? 1.0 : 0.0, Index = i
});
}
else
{
listText.Add(new SLSortItem {
Text = datacells[pt].CellText, Index = i
});
}
}
else
{
listBoolean.Add(new SLSortItem
{
Number = datacells[pt].NumericValue > 0.5 ? 1.0 : 0.0,
Index = i
});
}
}
else
{
listText.Add(new SLSortItem {
Text = datacells[pt].CellText, Index = i
});
}
}
else
{
listEmpty.Add(new SLSortItem {
Index = i
});
}
}
listNumbers.Sort(new SLSortItemNumberComparer());
if (!SortAscending)
{
listNumbers.Reverse();
}
listText.Sort(new SLSortItemTextComparer());
if (!SortAscending)
{
listText.Reverse();
}
listBoolean.Sort(new SLSortItemNumberComparer());
if (!SortAscending)
{
listBoolean.Reverse();
}
var ReverseIndex = new Dictionary <int, int>();
if (SortAscending)
{
j = iStartIndex;
for (i = 0; i < listNumbers.Count; ++i)
{
ReverseIndex[listNumbers[i].Index] = j;
++j;
}
for (i = 0; i < listText.Count; ++i)
{
ReverseIndex[listText[i].Index] = j;
++j;
}
for (i = 0; i < listBoolean.Count; ++i)
{
ReverseIndex[listBoolean[i].Index] = j;
++j;
}
for (i = 0; i < listEmpty.Count; ++i)
{
ReverseIndex[listEmpty[i].Index] = j;
++j;
}
}
else
{
j = iStartIndex;
for (i = 0; i < listBoolean.Count; ++i)
{
ReverseIndex[listBoolean[i].Index] = j;
++j;
}
for (i = 0; i < listText.Count; ++i)
{
ReverseIndex[listText[i].Index] = j;
++j;
}
for (i = 0; i < listNumbers.Count; ++i)
{
ReverseIndex[listNumbers[i].Index] = j;
++j;
}
for (i = 0; i < listEmpty.Count; ++i)
{
ReverseIndex[listEmpty[i].Index] = j;
++j;
}
}
var listCellKeys = datacells.Keys.ToList();
SLCellPoint newpt;
for (i = 0; i < listCellKeys.Count; ++i)
{
pt = listCellKeys[i];
if (SortByColumn)
{
if (ReverseIndex.ContainsKey(pt.RowIndex))
{
newpt = new SLCellPoint(ReverseIndex[pt.RowIndex], pt.ColumnIndex);
}
else
{
newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex);
}
}
else
{
if (ReverseIndex.ContainsKey(pt.ColumnIndex))
{
newpt = new SLCellPoint(pt.RowIndex, ReverseIndex[pt.ColumnIndex]);
}
else
{
newpt = new SLCellPoint(pt.RowIndex, pt.ColumnIndex);
}
}
slws.Cells[newpt] = datacells[pt];
}
}
private bool SetCellValueNumberFinal(int RowIndex, int ColumnIndex, bool IsNumeric, double NumericValue, string NumberData)
{
if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
return false;
}
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLCell c;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
}
else
{
c = new SLCell();
uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex);
if (iStyleIndex != 0)
{
c.StyleIndex = iStyleIndex;
}
}
c.DataType = CellValues.Number;
if (IsNumeric) c.NumericValue = NumericValue;
else c.CellText = NumberData;
slws.Cells[pt] = c;
return true;
}
// TODO: Hyperlink cell range
/// <summary>
/// Insert a hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</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(int RowIndex, int ColumnIndex, SLHyperlinkTypeValues HyperlinkType, string Address, string Display, string ToolTip, bool OverwriteExistingCell)
{
if (RowIndex < 1 || RowIndex > SLConstants.RowLimit) return false;
if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit) return false;
SLHyperlink hl = new SLHyperlink();
hl.IsNew = true;
hl.Reference = new SLCellPointRange(RowIndex, ColumnIndex, RowIndex, ColumnIndex);
switch (HyperlinkType)
{
case SLHyperlinkTypeValues.EmailAddress:
hl.IsExternal = true;
hl.HyperlinkUri = string.Format("mailto:{0}", Address);
hl.HyperlinkUriKind = UriKind.Absolute;
break;
case SLHyperlinkTypeValues.FilePath:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
// assume if it starts with ../ or ./ it's a relative path.
hl.HyperlinkUriKind = Address.StartsWith(".") ? UriKind.Relative : UriKind.Absolute;
break;
case SLHyperlinkTypeValues.InternalDocumentLink:
hl.IsExternal = false;
hl.Location = Address;
break;
case SLHyperlinkTypeValues.Url:
hl.IsExternal = true;
hl.HyperlinkUri = Address;
hl.HyperlinkUriKind = UriKind.Absolute;
break;
}
if (Display == null)
{
hl.Display = Address;
}
else
{
if (Display.Length == 0)
{
hl.Display = Address;
}
else
{
hl.Display = Display;
}
}
if (ToolTip != null && ToolTip.Length > 0) hl.ToolTip = ToolTip;
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLCell c;
SLStyle style;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
style = new SLStyle();
if (c.StyleIndex < listStyle.Count) style.FromHash(listStyle[(int)c.StyleIndex]);
else style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
if (OverwriteExistingCell)
{
// in case there's a formula
c.CellFormula = null;
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
}
// else don't have to do anything
slws.Cells[pt] = c.Clone();
}
else
{
c = new SLCell();
style = new SLStyle();
style.FromHash(listStyle[0]);
style.SetFontUnderline(UnderlineValues.Single);
style.SetFontColor(SLThemeColorIndexValues.Hyperlink);
c.StyleIndex = (uint)this.SaveToStylesheet(style.ToHash());
c.DataType = CellValues.SharedString;
c.CellText = this.DirectSaveToSharedStringTable(hl.Display).ToString(CultureInfo.InvariantCulture);
slws.Cells[pt] = c.Clone();
}
slws.Hyperlinks.Add(hl);
return true;
}
/// <summary>
/// Set the cell value given the row index and column index.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="Data">The cell value data. Try the SLRstType class for easy InlineString generation.</param>
/// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns>
public bool SetCellValue(int RowIndex, int ColumnIndex, InlineString Data)
{
if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
return false;
}
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLCell c;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
}
else
{
c = new SLCell();
uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex);
if (iStyleIndex != 0)
{
c.StyleIndex = iStyleIndex;
}
}
c.DataType = CellValues.SharedString;
c.NumericValue = this.DirectSaveToSharedStringTable(Data);
slws.Cells[pt] = c;
return true;
}
/// <summary>
/// Copy the style of one column to a range of columns.
/// </summary>
/// <param name="FromColumnIndex">The column index of the column to be copied from.</param>
/// <param name="ToStartColumnIndex">The column index of the start column of the column range. This is typically the left-most column.</param>
/// <param name="ToEndColumnIndex">The column index of the end column of the column range. This is typically the right-most column.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool CopyColumnStyle(int FromColumnIndex, int ToStartColumnIndex, int ToEndColumnIndex)
{
int iStartColumnIndex = 1, iEndColumnIndex = 1;
bool result = false;
if (ToStartColumnIndex < ToEndColumnIndex)
{
iStartColumnIndex = ToStartColumnIndex;
iEndColumnIndex = ToEndColumnIndex;
}
else
{
iStartColumnIndex = ToEndColumnIndex;
iEndColumnIndex = ToStartColumnIndex;
}
if (FromColumnIndex >= 1 && FromColumnIndex <= SLConstants.ColumnLimit
&& iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit
&& iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit)
{
result = true;
uint iStyleIndex = 0;
if (slws.ColumnProperties.ContainsKey(FromColumnIndex))
{
iStyleIndex = slws.ColumnProperties[FromColumnIndex].StyleIndex;
}
SLColumnProperties cp;
int i, j;
for (i = iStartColumnIndex; i <= iEndColumnIndex; ++i)
{
if (i != FromColumnIndex)
{
if (iStyleIndex == 0)
{
if (slws.ColumnProperties.ContainsKey(i))
{
slws.ColumnProperties[i].StyleIndex = 0;
}
}
else
{
if (slws.ColumnProperties.ContainsKey(i))
{
slws.ColumnProperties[i].StyleIndex = iStyleIndex;
}
else
{
cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
cp.StyleIndex = iStyleIndex;
slws.ColumnProperties[i] = cp;
}
slws.RowColumnStyleHistory.Add(new SLRowColumnStyleHistory(false, i));
}
}
}
#region copying cell styles
SLCell cell;
SLCellPoint pt, destpt;
for (i = 1; i <= SLConstants.RowLimit; ++i)
{
pt = new SLCellPoint(i, FromColumnIndex);
if (slws.Cells.ContainsKey(pt))
{
if (slws.Cells[pt].StyleIndex > 0)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
if (j != FromColumnIndex)
{
destpt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(destpt))
{
slws.Cells[destpt].StyleIndex = slws.Cells[pt].StyleIndex;
}
else
{
cell = new SLCell();
cell.CellText = string.Empty;
cell.StyleIndex = slws.Cells[pt].StyleIndex;
slws.Cells[destpt] = cell;
}
}
}
}
}
else
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
if (j != FromColumnIndex)
{
destpt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(destpt))
{
slws.Cells[destpt].StyleIndex = iStyleIndex;
}
// no need else because the default style will take over
}
}
}
}
#endregion
}
return result;
}
/// <summary>
/// Set the cell value given the row index and column index.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="Data">The cell value data.</param>
/// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns>
public bool SetCellValue(int RowIndex, int ColumnIndex, string Data)
{
if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
return false;
}
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLCell c;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
}
else
{
// if there's no existing cell, then we don't have to assign
// a new cell when the data string is empty
if (Data == null || Data.Length == 0) return true;
c = new SLCell();
uint iStyleIndex = slws.GetExistingRowColumnStyle(RowIndex, ColumnIndex);
if (iStyleIndex != 0)
{
c.StyleIndex = iStyleIndex;
}
}
if (Data == null || Data.Length == 0)
{
c.DataType = CellValues.Number;
c.CellText = string.Empty;
slws.Cells[pt] = c;
}
else if (Data.StartsWith("="))
{
// in case it's just one equal sign
if (Data.Equals("=", StringComparison.InvariantCultureIgnoreCase))
{
c.DataType = CellValues.SharedString;
c.NumericValue = this.DirectSaveToSharedStringTable("=");
slws.Cells[pt] = c;
}
else
{
// For simplicity, we're gonna assume that if it starts with an equal sign, it's a formula.
// TODO Formula calculation engine
c.DataType = CellValues.Number;
//c.Formula = new CellFormula(slxe.Write(Data.Substring(1)));
c.CellFormula = new SLCellFormula();
c.CellFormula.FormulaText = SLTool.XmlWrite(Data.Substring(1));
c.CellText = string.Empty;
slws.Cells[pt] = c;
}
}
else if (Data.StartsWith("'"))
{
c.DataType = CellValues.SharedString;
c.NumericValue = this.DirectSaveToSharedStringTable(SLTool.XmlWrite(Data.Substring(1)));
slws.Cells[pt] = c;
}
else
{
c.DataType = CellValues.SharedString;
c.NumericValue = this.DirectSaveToSharedStringTable(SLTool.XmlWrite(Data));
slws.Cells[pt] = c;
}
return true;
}
/// <summary>
/// Remove the style from a range of cells.
/// </summary>
/// <param name="StartRowIndex">The row index of the start cell of the cell range. This is typically the top-left cell.</param>
/// <param name="StartColumnIndex">The column index of the start cell of the cell range. This is typically the top-left cell.</param>
/// <param name="EndRowIndex">The row index of the end cell of the cell range. This is typically the bottom-right cell.</param>
/// <param name="EndColumnIndex">The column index of the end cell of the cell range. This is typically the bottom-right cell.</param>
public void RemoveCellStyle(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
SLCellPoint pt;
SLCell c;
int i, j;
for (i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
c.StyleIndex = 0;
slws.Cells[pt] = c;
}
}
}
}
/// <summary>
/// Get the cell value as a boolean. If the cell value wasn't originally a boolean value, the return value is undetermined (but is by default false).
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <returns>A boolean cell value.</returns>
public bool GetCellValueAsBoolean(int RowIndex, int ColumnIndex)
{
bool result = false;
if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
if (slws.Cells.ContainsKey(pt))
{
SLCell c = slws.Cells[pt];
if (c.DataType == CellValues.Boolean)
{
double fValue = 0;
if (c.CellText != null)
{
if (double.TryParse(c.CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out fValue))
{
if (fValue > 0.5) result = true;
else result = false;
}
else
{
bool.TryParse(c.CellText, out result);
}
}
else
{
if (c.NumericValue > 0.5) result = true;
else result = false;
}
}
}
}
return result;
}
/// <summary>
/// Copy the style of one cell to a range of cells.
/// </summary>
/// <param name="FromRowIndex">The row index of the cell to be copied from.</param>
/// <param name="FromColumnIndex">The column index of the cell to be copied from.</param>
/// <param name="ToStartRowIndex">The row index of the starting cell of the cell range. This is typically the top-left cell.</param>
/// <param name="ToStartColumnIndex">The column index of the starting cell of the cell range. This is typically the top-left cell.</param>
/// <param name="ToEndRowIndex">The row index of the ending cell of the cell range. This is typically the bottom-right cell.</param>
/// <param name="ToEndColumnIndex">The column index of the ending cell of the cell range. This is typically the bottom-right cell.</param>
/// <returns>True if successful. False otherwise.</returns>
public bool CopyCellStyle(int FromRowIndex, int FromColumnIndex, int ToStartRowIndex, int ToStartColumnIndex, int ToEndRowIndex, int ToEndColumnIndex)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
bool result = false;
if (ToStartRowIndex < ToEndRowIndex)
{
iStartRowIndex = ToStartRowIndex;
iEndRowIndex = ToEndRowIndex;
}
else
{
iStartRowIndex = ToEndRowIndex;
iEndRowIndex = ToStartRowIndex;
}
if (ToStartColumnIndex < ToEndColumnIndex)
{
iStartColumnIndex = ToStartColumnIndex;
iEndColumnIndex = ToEndColumnIndex;
}
else
{
iStartColumnIndex = ToEndColumnIndex;
iEndColumnIndex = ToStartColumnIndex;
}
if (SLTool.CheckRowColumnIndexLimit(FromRowIndex, FromColumnIndex)
&& SLTool.CheckRowColumnIndexLimit(iStartRowIndex, iStartColumnIndex)
&& SLTool.CheckRowColumnIndexLimit(iEndRowIndex, iEndColumnIndex))
{
result = true;
uint iStyleIndex = 0;
SLCellPoint pt = new SLCellPoint(FromRowIndex, FromColumnIndex);
if (slws.Cells.ContainsKey(pt))
{
iStyleIndex = slws.Cells[pt].StyleIndex;
}
SLCell c;
// we'll just overwrite any existing styles, instead of merging
// like when we're copying row/column styles.
for (int i = iStartRowIndex; i <= iEndRowIndex; ++i)
{
for (int j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
{
if (i != FromRowIndex && j != FromColumnIndex)
{
pt = new SLCellPoint(i, j);
// so default style
if (iStyleIndex == 0)
{
if (slws.Cells.ContainsKey(pt))
{
slws.Cells[pt].StyleIndex = 0;
}
}
else
{
if (slws.Cells.ContainsKey(pt))
{
slws.Cells[pt].StyleIndex = iStyleIndex;
}
else
{
c = new SLCell();
c.CellText = string.Empty;
c.StyleIndex = iStyleIndex;
slws.Cells[pt] = c;
}
}
}
}
}
}
return result;
}
/// <summary>
/// Get the cell value as a double precision floating point number. If the cell value wasn't originally a floating point number, the return value is undetermined (but is by default 0).
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <returns>A double precision floating point number cell value.</returns>
public double GetCellValueAsDouble(int RowIndex, int ColumnIndex)
{
double result = 0.0;
if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
if (slws.Cells.ContainsKey(pt))
{
SLCell c = slws.Cells[pt];
if (c.DataType == CellValues.Number)
{
if (c.CellText != null)
{
double.TryParse(c.CellText, NumberStyles.Any, CultureInfo.InvariantCulture, out result);
}
else
{
result = c.NumericValue;
}
}
}
}
return result;
}
/// <summary>
/// Remove an existing hyperlink.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
public void RemoveHyperlink(int RowIndex, int ColumnIndex)
{
if ((RowIndex < 1) || (RowIndex > SLConstants.RowLimit))
{
return;
}
if ((ColumnIndex < 1) || (ColumnIndex > SLConstants.ColumnLimit))
{
return;
}
// I'm assuming hyperlinks are tied to just one cell. Apparently,
// you can assign a block of cells as the hyperlink.
// Excel removes the cells of the hyperlink that are empty. I'm not going to even try...
var listdelete = new List <SLCellPointRange>();
int i, j;
WorksheetPart wsp;
string sRelId;
HyperlinkRelationship hlrel;
if (!IsNewWorksheet)
{
for (i = slws.Hyperlinks.Count - 1; i >= 0; --i)
{
if ((slws.Hyperlinks[i].Reference.StartRowIndex <= RowIndex) &&
(RowIndex <= slws.Hyperlinks[i].Reference.EndRowIndex) &&
(slws.Hyperlinks[i].Reference.StartColumnIndex <= ColumnIndex) &&
(ColumnIndex <= slws.Hyperlinks[i].Reference.EndColumnIndex))
{
if (slws.Hyperlinks[i].IsNew)
{
slws.Hyperlinks.RemoveAt(i);
}
else
{
if (slws.Hyperlinks[i].IsExternal &&
(slws.Hyperlinks[i].Id != null) &&
(slws.Hyperlinks[i].Id.Length > 0))
{
sRelId = slws.Hyperlinks[i].Id;
if (!string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID))
{
wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID);
hlrel = wsp.HyperlinkRelationships.Where(hlid => hlid.Id == sRelId).FirstOrDefault();
if (hlrel != null)
{
wsp.DeleteReferenceRelationship(hlrel);
}
}
}
slws.Hyperlinks.RemoveAt(i);
}
listdelete.Add(new SLCellPointRange(
slws.Hyperlinks[i].Reference.StartRowIndex,
slws.Hyperlinks[i].Reference.StartColumnIndex,
slws.Hyperlinks[i].Reference.EndRowIndex,
slws.Hyperlinks[i].Reference.EndColumnIndex));
}
else
{
for (i = slws.Hyperlinks.Count - 1; i >= 0; --i)
{
if ((slws.Hyperlinks[i].Reference.StartRowIndex <= RowIndex) &&
(RowIndex <= slws.Hyperlinks[i].Reference.EndRowIndex) &&
(slws.Hyperlinks[i].Reference.StartColumnIndex <= ColumnIndex) &&
(ColumnIndex <= slws.Hyperlinks[i].Reference.EndColumnIndex))
{
slws.Hyperlinks.RemoveAt(i);
listdelete.Add(new SLCellPointRange(
slws.Hyperlinks[i].Reference.StartRowIndex,
slws.Hyperlinks[i].Reference.StartColumnIndex,
slws.Hyperlinks[i].Reference.EndRowIndex,
slws.Hyperlinks[i].Reference.EndColumnIndex));
}
}
}
}
}
if (listdelete.Count > 0)
{
// remove hyperlink style
SLCell c;
SLCellPoint pt;
foreach (var ptrange in listdelete)
{
for (i = ptrange.StartRowIndex; i <= ptrange.EndRowIndex; ++i)
{
for (j = ptrange.StartColumnIndex; j <= ptrange.EndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
c.StyleIndex = 0;
slws.Cells[pt] = c.Clone();
}
}
}
}
}
}
/// <summary>
/// Creates an instance of SLTable, given row and column indices of opposite cells in a cell range.
/// </summary>
/// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param>
/// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param>
/// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param>
/// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param>
/// <returns>An SLTable with the required information.</returns>
public SLTable CreateTable(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
if (iStartRowIndex < 1) iStartRowIndex = 1;
if (iStartRowIndex == SLConstants.RowLimit) iStartRowIndex = SLConstants.RowLimit - 1;
if (iStartColumnIndex < 1) iStartColumnIndex = 1;
// consider minus 1 in case there's a totals row so there's less checking...
if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit;
if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;
if (iEndRowIndex <= iStartRowIndex) iEndRowIndex = iStartRowIndex + 1;
SLTable tbl = new SLTable();
tbl.SetAllNull();
slwb.RefreshPossibleTableId();
tbl.Id = slwb.PossibleTableId;
tbl.DisplayName = string.Format("Table{0}", tbl.Id);
tbl.Name = tbl.DisplayName;
tbl.StartRowIndex = iStartRowIndex;
tbl.StartColumnIndex = iStartColumnIndex;
tbl.EndRowIndex = iEndRowIndex;
tbl.EndColumnIndex = iEndColumnIndex;
tbl.AutoFilter.StartRowIndex = tbl.StartRowIndex;
tbl.AutoFilter.StartColumnIndex = tbl.StartColumnIndex;
tbl.AutoFilter.EndRowIndex = tbl.EndRowIndex;
tbl.AutoFilter.EndColumnIndex = tbl.EndColumnIndex;
tbl.HasAutoFilter = true;
SLTableColumn tc;
uint iColumnId = 1;
int i, index;
uint j;
SLCell c;
SLCellPoint pt;
string sHeaderText = string.Empty;
SharedStringItem ssi;
SLRstType rst = new SLRstType(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
for (i = tbl.StartColumnIndex; i <= tbl.EndColumnIndex; ++i)
{
pt = new SLCellPoint(StartRowIndex, i);
sHeaderText = string.Empty;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
if (c.CellText == null)
{
if (c.DataType == CellValues.Number) sHeaderText = c.NumericValue.ToString(CultureInfo.InvariantCulture);
else if (c.DataType == CellValues.Boolean) sHeaderText = c.NumericValue > 0.5 ? "TRUE" : "FALSE";
else sHeaderText = string.Empty;
}
else
{
sHeaderText = c.CellText;
}
if (c.DataType == CellValues.SharedString)
{
index = -1;
if (c.CellText != null)
{
if (int.TryParse(c.CellText, out index))
{
index = -1;
}
}
else
{
index = Convert.ToInt32(c.NumericValue);
}
if (index >= 0 && index < listSharedString.Count)
{
ssi = new SharedStringItem();
ssi.InnerXml = listSharedString[index];
rst.FromSharedStringItem(ssi);
sHeaderText = rst.ToPlainString();
}
}
}
j = iColumnId;
if (sHeaderText.Length == 0)
{
sHeaderText = string.Format("Column{0}", j);
}
while (tbl.TableNames.Contains(sHeaderText))
{
++j;
sHeaderText = string.Format("Column{0}", j);
}
tc = new SLTableColumn();
tc.Id = iColumnId;
tc.Name = sHeaderText;
tbl.TableColumns.Add(tc);
tbl.TableNames.Add(sHeaderText);
++iColumnId;
}
tbl.TableStyleInfo.ShowFirstColumn = false;
tbl.TableStyleInfo.ShowLastColumn = false;
tbl.TableStyleInfo.ShowRowStripes = true;
tbl.TableStyleInfo.ShowColumnStripes = false;
tbl.HasTableStyleInfo = true;
return tbl;
}
private List<SLC.SLDataSeries> FillChartDataSeries(string WorksheetName, int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool RowsAsDataSeries, bool ShowHiddenData)
{
List<SLC.SLDataSeries> series = new List<SLC.SLDataSeries>();
int i, j;
SLCell c;
SLCellPoint pt;
Dictionary<int, bool> HiddenRows = new Dictionary<int, bool>();
Dictionary<int, bool> HiddenColumns = new Dictionary<int, bool>();
Dictionary<SLCellPoint, SLCell> cellstore = new Dictionary<SLCellPoint, SLCell>();
#region GetCells
for (i = StartRowIndex; i <= EndRowIndex; ++i)
{
HiddenRows[i] = false;
}
for (j = StartColumnIndex; j <= EndColumnIndex; ++j)
{
HiddenColumns[j] = false;
}
bool bFound = false;
string sWorksheetRelID = string.Empty;
if (WorksheetName.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
{
bFound = false;
}
else
{
bFound = false;
foreach (SLSheet sheet in slwb.Sheets)
{
if (sheet.Name.Equals(WorksheetName, StringComparison.OrdinalIgnoreCase))
{
bFound = true;
sWorksheetRelID = sheet.Id;
break;
}
}
}
if (bFound)
{
Dictionary<string, SLCellPoint> cellref = new Dictionary<string, SLCellPoint>();
for (i = StartRowIndex; i <= EndRowIndex; ++i)
{
for (j = StartColumnIndex; j <= EndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
cellref[SLTool.ToCellReference(i, j)] = pt;
}
}
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sWorksheetRelID);
Row row;
Column column;
Cell cell;
if (!ShowHiddenData)
{
// running it twice because Row contains Cell classes and it's easier this way
using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Row))
{
row = (Row)oxr.LoadCurrentElement();
if (row.RowIndex != null)
{
foreach (var rowindex in HiddenRows.Keys)
{
if (row.RowIndex.Value == rowindex && row.Hidden != null && row.Hidden.Value)
{
HiddenRows[rowindex] = true;
}
}
}
}
}
}
}
using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Column))
{
if (!ShowHiddenData)
{
column = (Column)oxr.LoadCurrentElement();
foreach (var colindex in HiddenColumns.Keys)
{
if (column.Min <= colindex && colindex <= column.Max && column.Hidden != null && column.Hidden.Value)
{
HiddenColumns[colindex] = true;
}
}
}
}
else if (oxr.ElementType == typeof(Cell))
{
cell = (Cell)oxr.LoadCurrentElement();
if (cell.CellReference != null)
{
if (cellref.ContainsKey(cell.CellReference.Value))
{
c = new SLCell();
c.FromCell(cell);
cellstore[cellref[cell.CellReference.Value]] = c.Clone();
}
}
}
}
}
}
else
{
SLRowProperties rp;
SLColumnProperties cp;
if (!ShowHiddenData)
{
for (j = StartColumnIndex; j <= EndColumnIndex; ++j)
{
if (slws.ColumnProperties.ContainsKey(j))
{
cp = slws.ColumnProperties[j];
if (cp.Hidden) HiddenColumns[j] = true;
}
}
}
for (i = StartRowIndex; i <= EndRowIndex; ++i)
{
if (!ShowHiddenData && slws.RowProperties.ContainsKey(i))
{
rp = slws.RowProperties[i];
if (rp.Hidden) HiddenRows[i] = true;
}
for (j = StartColumnIndex; j <= EndColumnIndex; ++j)
{
pt = new SLCellPoint(i, j);
if (slws.Cells.ContainsKey(pt))
{
cellstore[pt] = slws.Cells[pt].Clone();
}
}
}
}
#endregion
int index = 0;
int index2 = 0;
string sCellValue;
string sFormatCode;
SLC.SLDataSeries ser;
SLC.SLStringReference sr;
SLC.SLNumberReference nr;
SLStyle style;
bool bIsStringReference = true;
// we're going to assume that the format code applies to all in the "category" cells.
string sAxisFormatCode = string.Empty;
SLC.SLAxisDataSourceType cat = new SLC.SLAxisDataSourceType();
if (RowsAsDataSeries)
{
bIsStringReference = true;
sAxisFormatCode = SLConstants.NumberFormatGeneral;
pt = new SLCellPoint(StartRowIndex, StartColumnIndex + 1);
if (cellstore.ContainsKey(pt))
{
// dates are also numbers, so we lump it together
if (cellstore[pt].DataType == CellValues.Number)
{
bIsStringReference = false;
style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
style.FromHash(listStyle[(int)cellstore[pt].StyleIndex]);
this.TranslateStylesToStyleIds(ref style);
sAxisFormatCode = style.FormatCode;
}
}
sr = new SLC.SLStringReference();
nr = new SLC.SLNumberReference();
if (bIsStringReference)
{
cat.UseStringReference = true;
sr = new SLC.SLStringReference();
sr.WorksheetName = WorksheetName;
sr.StartRowIndex = StartRowIndex;
sr.StartColumnIndex = StartColumnIndex + 1;
sr.EndRowIndex = StartRowIndex;
sr.EndColumnIndex = EndColumnIndex;
sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex, StartColumnIndex + 1, StartRowIndex, EndColumnIndex);
}
else
{
cat.UseNumberReference = true;
nr.WorksheetName = WorksheetName;
nr.StartRowIndex = StartRowIndex;
nr.StartColumnIndex = StartColumnIndex + 1;
nr.EndRowIndex = StartRowIndex;
nr.EndColumnIndex = EndColumnIndex;
nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex, StartColumnIndex + 1, StartRowIndex, EndColumnIndex);
nr.NumberingCache.FormatCode = sAxisFormatCode;
}
index2 = 0;
// if the header row is hidden, I don't know what to do...
for (j = StartColumnIndex + 1; j <= EndColumnIndex; ++j)
{
if (HiddenColumns.ContainsKey(j) && !HiddenColumns[j])
{
pt = new SLCellPoint(StartRowIndex, j);
sCellValue = string.Empty;
if (cellstore.ContainsKey(pt))
{
c = cellstore[pt];
sCellValue = this.GetCellTrueValue(c);
if (bIsStringReference)
{
sr.Points.Add(new SLC.SLStringPoint()
{
Index = (uint)index2,
NumericValue = sCellValue
});
}
else
{
nr.NumberingCache.Points.Add(new SLC.SLNumericPoint()
{
Index = (uint)index2,
NumericValue = sCellValue
});
}
}
++index2;
}
}
if (bIsStringReference)
{
sr.PointCount = (uint)index2;
cat.StringReference = sr;
}
else
{
nr.NumberingCache.PointCount = (uint)index2;
cat.NumberReference = nr;
}
index = 0;
for (i = StartRowIndex + 1; i <= EndRowIndex; ++i)
{
if (HiddenRows.ContainsKey(i) && !HiddenRows[i])
{
ser = new SLC.SLDataSeries(SimpleTheme.listThemeColors);
ser.Index = (uint)index;
ser.Order = (uint)index;
ser.IsStringReference = true;
sr = new SLC.SLStringReference();
pt = new SLCellPoint(i, StartColumnIndex);
sCellValue = string.Empty;
if (cellstore.ContainsKey(pt))
{
c = cellstore[pt];
sCellValue = this.GetCellTrueValue(c);
}
sr.WorksheetName = WorksheetName;
sr.StartRowIndex = i;
sr.StartColumnIndex = StartColumnIndex;
sr.EndRowIndex = i;
sr.EndColumnIndex = StartColumnIndex;
sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, i, StartColumnIndex);
sr.PointCount = 1;
sr.Points.Add(new SLC.SLStringPoint() { Index = 0, NumericValue = sCellValue });
ser.StringReference = sr;
ser.AxisData = cat.Clone();
ser.NumberData.UseNumberReference = true;
nr = new SLC.SLNumberReference();
nr.WorksheetName = WorksheetName;
nr.StartRowIndex = i;
nr.StartColumnIndex = StartColumnIndex + 1;
nr.EndRowIndex = i;
nr.EndColumnIndex = EndColumnIndex;
nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, i, StartColumnIndex + 1, i, EndColumnIndex);
nr.NumberingCache.FormatCode = SLConstants.NumberFormatGeneral;
index2 = 0;
for (j = StartColumnIndex + 1; j <= EndColumnIndex; ++j)
{
if (HiddenColumns.ContainsKey(j) && !HiddenColumns[j])
{
pt = new SLCellPoint(i, j);
sCellValue = string.Empty;
sFormatCode = string.Empty;
if (cellstore.ContainsKey(pt))
{
c = cellstore[pt];
sCellValue = this.GetCellTrueValue(c);
style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
style.FromHash(listStyle[(int)c.StyleIndex]);
this.TranslateStylesToStyleIds(ref style);
if (style.HasNumberingFormat) sFormatCode = style.FormatCode;
nr.NumberingCache.Points.Add(new SLC.SLNumericPoint()
{
FormatCode = sFormatCode,
Index = (uint)index2,
NumericValue = sCellValue
});
}
++index2;
}
}
nr.NumberingCache.PointCount = (uint)index2;
ser.NumberData.NumberReference = nr;
series.Add(ser);
++index;
}
}
// end of rows as data series
}
else
{
bIsStringReference = true;
sAxisFormatCode = SLConstants.NumberFormatGeneral;
pt = new SLCellPoint(StartRowIndex + 1, StartColumnIndex);
if (cellstore.ContainsKey(pt))
{
// dates are also numbers, so we lump it together
if (cellstore[pt].DataType == CellValues.Number)
{
bIsStringReference = false;
style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
style.FromHash(listStyle[(int)cellstore[pt].StyleIndex]);
this.TranslateStylesToStyleIds(ref style);
sAxisFormatCode = style.FormatCode;
}
}
sr = new SLC.SLStringReference();
nr = new SLC.SLNumberReference();
if (bIsStringReference)
{
cat.UseStringReference = true;
sr.WorksheetName = WorksheetName;
sr.StartRowIndex = StartRowIndex + 1;
sr.StartColumnIndex = StartColumnIndex;
sr.EndRowIndex = EndRowIndex;
sr.EndColumnIndex = StartColumnIndex;
sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex + 1, StartColumnIndex, EndRowIndex, StartColumnIndex);
}
else
{
cat.UseNumberReference = true;
nr.WorksheetName = WorksheetName;
nr.StartRowIndex = StartRowIndex + 1;
nr.StartColumnIndex = StartColumnIndex;
nr.EndRowIndex = EndRowIndex;
nr.EndColumnIndex = StartColumnIndex;
nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex + 1, StartColumnIndex, EndRowIndex, StartColumnIndex);
nr.NumberingCache.FormatCode = sAxisFormatCode;
}
index2 = 0;
// if the header column is hidden, I don't know what to do...
for (i = StartRowIndex + 1; i <= EndRowIndex; ++i)
{
if (HiddenRows.ContainsKey(i) && !HiddenRows[i])
{
pt = new SLCellPoint(i, StartColumnIndex);
sCellValue = string.Empty;
if (cellstore.ContainsKey(pt))
{
c = cellstore[pt];
sCellValue = this.GetCellTrueValue(c);
if (bIsStringReference)
{
sr.Points.Add(new SLC.SLStringPoint()
{
Index = (uint)index2,
NumericValue = sCellValue
});
}
else
{
nr.NumberingCache.Points.Add(new SLC.SLNumericPoint()
{
Index = (uint)index2,
NumericValue = sCellValue
});
}
}
++index2;
}
}
if (bIsStringReference)
{
sr.PointCount = (uint)index2;
cat.StringReference = sr;
}
else
{
nr.NumberingCache.PointCount = (uint)index2;
cat.NumberReference = nr;
}
index = 0;
for (j = StartColumnIndex + 1; j <= EndColumnIndex; ++j)
{
if (HiddenColumns.ContainsKey(j) && !HiddenColumns[j])
{
ser = new SLC.SLDataSeries(SimpleTheme.listThemeColors);
ser.Index = (uint)index;
ser.Order = (uint)index;
ser.IsStringReference = true;
sr = new SLC.SLStringReference();
pt = new SLCellPoint(StartRowIndex, j);
sCellValue = string.Empty;
if (cellstore.ContainsKey(pt))
{
c = cellstore[pt];
sCellValue = this.GetCellTrueValue(c);
}
sr.WorksheetName = WorksheetName;
sr.StartRowIndex = StartRowIndex;
sr.StartColumnIndex = j;
sr.EndRowIndex = StartRowIndex;
sr.EndColumnIndex = j;
sr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex, j);
sr.PointCount = 1;
sr.Points.Add(new SLC.SLStringPoint() { Index = 0, NumericValue = sCellValue });
ser.StringReference = sr;
ser.AxisData = cat.Clone();
ser.NumberData.UseNumberReference = true;
nr = new SLC.SLNumberReference();
nr.WorksheetName = WorksheetName;
nr.StartRowIndex = StartRowIndex + 1;
nr.StartColumnIndex = j;
nr.EndRowIndex = EndRowIndex;
nr.EndColumnIndex = j;
nr.Formula = SLC.SLChartTool.GetChartReferenceFormula(WorksheetName, StartRowIndex + 1, j, EndRowIndex, j);
nr.NumberingCache.FormatCode = SLConstants.NumberFormatGeneral;
index2 = 0;
for (i = StartRowIndex + 1; i <= EndRowIndex; ++i)
{
if (HiddenRows.ContainsKey(i) && !HiddenRows[i])
{
pt = new SLCellPoint(i, j);
sCellValue = string.Empty;
sFormatCode = string.Empty;
if (cellstore.ContainsKey(pt))
{
c = cellstore[pt];
sCellValue = this.GetCellTrueValue(c);
style = new SLStyle(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
style.FromHash(listStyle[(int)c.StyleIndex]);
this.TranslateStylesToStyleIds(ref style);
if (style.HasNumberingFormat) sFormatCode = style.FormatCode;
nr.NumberingCache.Points.Add(new SLC.SLNumericPoint()
{
FormatCode = sFormatCode,
Index = (uint)index2,
NumericValue = sCellValue
});
}
++index2;
}
}
nr.NumberingCache.PointCount = (uint)index2;
ser.NumberData.NumberReference = nr;
series.Add(ser);
++index;
}
}
// end of columns as data series
}
return series;
}
/// <summary>
/// Set the cell value given the row index and column index.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <param name="Data">The cell value data.</param>
/// <returns>False if either the row index or column index (or both) are invalid. True otherwise.</returns>
public bool SetCellValue(int RowIndex, int ColumnIndex, bool Data)
{
if (!SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
return false;
}
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
SLCell c;
if (slws.Cells.ContainsKey(pt))
{
c = slws.Cells[pt];
}
else
{
c = new SLCell();
if (slws.RowProperties.ContainsKey(RowIndex))
{
c.StyleIndex = slws.RowProperties[RowIndex].StyleIndex;
}
else if (slws.ColumnProperties.ContainsKey(ColumnIndex))
{
c.StyleIndex = slws.ColumnProperties[ColumnIndex].StyleIndex;
}
}
c.DataType = CellValues.Boolean;
c.NumericValue = Data ? 1 : 0;
slws.Cells[pt] = c;
return true;
}
/// <summary>
/// Get the cell's style. The default style is returned if cell doesn't have an existing style, or if the row or column indices are invalid.
/// </summary>
/// <param name="RowIndex">The row index.</param>
/// <param name="ColumnIndex">The column index.</param>
/// <returns>The cell's style.</returns>
public SLStyle GetCellStyle(int RowIndex, int ColumnIndex)
{
bool bFound = false;
SLStyle style = new SLStyle(SimpleTheme.MajorLatinFont, SimpleTheme.MinorLatinFont, SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
if (SLTool.CheckRowColumnIndexLimit(RowIndex, ColumnIndex))
{
SLCellPoint pt = new SLCellPoint(RowIndex, ColumnIndex);
if (slws.Cells.ContainsKey(pt))
{
SLCell c = slws.Cells[pt];
bFound = true;
style.FromHash(listStyle[(int)c.StyleIndex]);
}
}
if (!bFound)
{
style.FromHash(listStyle[0]);
}
return style;
}
