/// <summary>
/// Automatically fit column width according to cell contents.
/// </summary>
/// <param name="StartColumnIndex">The column index of the start column.</param>
/// <param name="EndColumnIndex">The column index of the end column.</param>
public void AutoFitColumn(int StartColumnIndex, int EndColumnIndex)
{
int iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
if (iStartColumnIndex < 1) iStartColumnIndex = 1;
if (iStartColumnIndex > SLConstants.ColumnLimit) iStartColumnIndex = SLConstants.ColumnLimit;
if (iEndColumnIndex < 1) iEndColumnIndex = 1;
if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;
Dictionary<int, int> pixellength = this.AutoFitRowColumn(false, iStartColumnIndex, iEndColumnIndex);
SLColumnProperties cp;
double fColumnWidth;
int iPixelLength;
double fWholeNumber;
double fRemainder;
foreach (int pixlenpt in pixellength.Keys)
{
iPixelLength = pixellength[pixlenpt];
if (iPixelLength > 0)
{
fWholeNumber = (double)(iPixelLength / (SimpleTheme.ThemeMaxDigitWidth - 1));
fRemainder = (double)(iPixelLength % (SimpleTheme.ThemeMaxDigitWidth - 1));
fRemainder = fRemainder / (double)(SimpleTheme.ThemeMaxDigitWidth - 1);
// we'll leave it to the algorithm within SLColumnProperties.Width to handle
// the actual column width refitting.
fColumnWidth = fWholeNumber + fRemainder;
if (slws.ColumnProperties.ContainsKey(pixlenpt))
{
cp = slws.ColumnProperties[pixlenpt];
cp.Width = fColumnWidth;
cp.BestFit = true;
slws.ColumnProperties[pixlenpt] = cp.Clone();
}
else
{
cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
cp.Width = fColumnWidth;
cp.BestFit = true;
slws.ColumnProperties[pixlenpt] = cp.Clone();
}
}
// else we don't have to do anything
}
}
/// <summary>
/// Group columns.
/// </summary>
/// <param name="StartColumnIndex">The column index of the start column.</param>
/// <param name="EndColumnIndex">The column index of the end column.</param>
public void GroupColumns(int StartColumnIndex, int EndColumnIndex)
{
int iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
// I haven't personally checked this, but there's a collapsing -/+ box on the column
// just right of the grouped columns. This implies the very very last column that can be
// grouped is the (column limit - 1)th column, because (column limit)th column will have that
// collapsing box.
if (iEndColumnIndex >= SLConstants.RowLimit) iEndColumnIndex = SLConstants.ColumnLimit - 1;
// there's nothing to group...
if (iStartColumnIndex > iEndColumnIndex) return;
if (iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit)
{
int i = 0;
SLColumnProperties cp;
for (i = iStartColumnIndex; i <= iEndColumnIndex; ++i)
{
if (slws.ColumnProperties.ContainsKey(i))
{
cp = slws.ColumnProperties[i];
// Excel supports only 8 levels
if (cp.OutlineLevel < 8) ++cp.OutlineLevel;
slws.ColumnProperties[i] = cp.Clone();
}
else
{
cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
cp.OutlineLevel = 1;
slws.ColumnProperties[i] = cp.Clone();
}
}
}
}
/// <summary>
/// Collapse a group of columns.
/// </summary>
/// <param name="ColumnIndex">The column index of the column just after the group of columns you want to collapse. For example, this will be column 5 if columns 2 to 4 are grouped.</param>
public void CollapseColumns(int ColumnIndex)
{
if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit) return;
// the following algorithm is not guaranteed to work in all cases.
// The data is sort of loosely linked together with no guarantee that they
// all make sense together. If you use Excel, then the internal data is sort of
// guaranteed to make sense together, but anyone can make an Open XML spreadsheet
// with possibly invalid-looking data. Maybe Excel will accept it, maybe not.
SLColumnProperties cp;
byte byCurrentOutlineLevel = 0;
if (slws.ColumnProperties.ContainsKey(ColumnIndex))
{
cp = slws.ColumnProperties[ColumnIndex];
byCurrentOutlineLevel = cp.OutlineLevel;
}
bool bFound = false;
int i;
for (i = ColumnIndex - 1; i >= 1; --i)
{
if (slws.ColumnProperties.ContainsKey(i))
{
cp = slws.ColumnProperties[i];
if (cp.OutlineLevel > byCurrentOutlineLevel)
{
bFound = true;
cp.Hidden = true;
slws.ColumnProperties[i] = cp.Clone();
}
else break;
}
else break;
}
if (bFound)
{
if (slws.ColumnProperties.ContainsKey(ColumnIndex))
{
cp = slws.ColumnProperties[ColumnIndex];
cp.Collapsed = true;
slws.ColumnProperties[ColumnIndex] = cp.Clone();
}
else
{
cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
cp.Collapsed = true;
slws.ColumnProperties[ColumnIndex] = cp.Clone();
}
}
}
/// <summary>
/// Automatically fit column width according to cell contents.
/// </summary>
/// <param name="StartColumnIndex">The column index of the start column.</param>
/// <param name="EndColumnIndex">The column index of the end column.</param>
/// <param name="MaximumColumnWidth">The maximum column width in number of characters.</param>
public void AutoFitColumn(int StartColumnIndex, int EndColumnIndex, double MaximumColumnWidth)
{
int iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
if (iStartColumnIndex < 1) iStartColumnIndex = 1;
if (iStartColumnIndex > SLConstants.ColumnLimit) iStartColumnIndex = SLConstants.ColumnLimit;
if (iEndColumnIndex < 1) iEndColumnIndex = 1;
if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;
if (MaximumColumnWidth > SLConstants.MaximumColumnWidth) MaximumColumnWidth = SLConstants.MaximumColumnWidth;
// this is taken from SLColumnProperties...
int iWholeNumber = Convert.ToInt32(Math.Truncate(MaximumColumnWidth));
double fStepRemainder = MaximumColumnWidth - (double)iWholeNumber;
int iStep = 0;
for (iStep = SimpleTheme.listColumnStepSize.Count - 1; iStep >= 0; --iStep)
{
if (fStepRemainder > SimpleTheme.listColumnStepSize[iStep]) break;
}
if (iStep < 0) iStep = 0;
int iMaximumPixelLength = iWholeNumber * (SimpleTheme.ThemeMaxDigitWidth - 1) + iStep;
Dictionary<int, int> pixellength = this.AutoFitRowColumn(false, iStartColumnIndex, iEndColumnIndex, iMaximumPixelLength);
SLColumnProperties cp;
double fColumnWidth;
int iPixelLength;
double fWholeNumber;
double fRemainder;
foreach (int pixlenpt in pixellength.Keys)
{
iPixelLength = pixellength[pixlenpt];
if (iPixelLength > 0)
{
fWholeNumber = (double)(iPixelLength / (SimpleTheme.ThemeMaxDigitWidth - 1));
fRemainder = (double)(iPixelLength % (SimpleTheme.ThemeMaxDigitWidth - 1));
fRemainder = fRemainder / (double)(SimpleTheme.ThemeMaxDigitWidth - 1);
// we'll leave it to the algorithm within SLColumnProperties.Width to handle
// the actual column width refitting.
fColumnWidth = fWholeNumber + fRemainder;
if (slws.ColumnProperties.ContainsKey(pixlenpt))
{
cp = slws.ColumnProperties[pixlenpt];
cp.Width = fColumnWidth;
cp.BestFit = true;
slws.ColumnProperties[pixlenpt] = cp.Clone();
}
else
{
cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
cp.Width = fColumnWidth;
cp.BestFit = true;
slws.ColumnProperties[pixlenpt] = cp.Clone();
}
}
// else we don't have to do anything
}
}