/// <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 } }