[12074] | 1 | /*******************************************************************************
|
---|
| 2 | * You may amend and distribute as you like, but don't remove this header!
|
---|
| 3 | *
|
---|
| 4 | * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
|
---|
| 5 | * See http://www.codeplex.com/EPPlus for details.
|
---|
| 6 | *
|
---|
| 7 | * Copyright (C) 2011 Jan Källman
|
---|
| 8 | *
|
---|
| 9 | * This library is free software; you can redistribute it and/or
|
---|
| 10 | * modify it under the terms of the GNU Lesser General Public
|
---|
| 11 | * License as published by the Free Software Foundation; either
|
---|
| 12 | * version 2.1 of the License, or (at your option) any later version.
|
---|
| 13 |
|
---|
| 14 | * This library is distributed in the hope that it will be useful,
|
---|
| 15 | * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
---|
| 16 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
|
---|
| 17 | * See the GNU Lesser General Public License for more details.
|
---|
| 18 | *
|
---|
| 19 | * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
|
---|
| 20 | * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
|
---|
| 21 | *
|
---|
| 22 | * All code and executables are provided "as is" with no warranty either express or implied.
|
---|
| 23 | * The author accepts no liability for any damage or loss of business that this product may cause.
|
---|
| 24 | *
|
---|
| 25 | * Code change notes:
|
---|
| 26 | *
|
---|
| 27 | * Author Change Date
|
---|
| 28 | *******************************************************************************
|
---|
| 29 | * Jan Källman Initial Release 2009-10-01
|
---|
| 30 | * Jan Källman License changed GPL-->LGPL 2011-12-27
|
---|
| 31 | *******************************************************************************/
|
---|
| 32 | using System;
|
---|
| 33 | using System.Xml;
|
---|
| 34 | using OfficeOpenXml.Style;
|
---|
| 35 | namespace OfficeOpenXml
|
---|
| 36 | {
|
---|
| 37 | /// <summary>
|
---|
| 38 | /// Represents one or more columns within the worksheet
|
---|
| 39 | /// </summary>
|
---|
| 40 | public class ExcelColumn : IRangeID
|
---|
| 41 | {
|
---|
| 42 | private ExcelWorksheet _worksheet;
|
---|
| 43 | private XmlElement _colElement = null;
|
---|
| 44 |
|
---|
| 45 | #region ExcelColumn Constructor
|
---|
| 46 | /// <summary>
|
---|
| 47 | /// Creates a new instance of the ExcelColumn class.
|
---|
| 48 | /// For internal use only!
|
---|
| 49 | /// </summary>
|
---|
| 50 | /// <param name="Worksheet"></param>
|
---|
| 51 | /// <param name="col"></param>
|
---|
| 52 | protected internal ExcelColumn(ExcelWorksheet Worksheet, int col)
|
---|
| 53 | {
|
---|
| 54 | _worksheet = Worksheet;
|
---|
| 55 | _columnMin = col;
|
---|
| 56 | _columnMax = col;
|
---|
| 57 | _width = _worksheet.DefaultColWidth;
|
---|
| 58 | }
|
---|
| 59 | #endregion
|
---|
| 60 | internal int _columnMin;
|
---|
| 61 | /// <summary>
|
---|
| 62 | /// Sets the first column the definition refers to.
|
---|
| 63 | /// </summary>
|
---|
| 64 | public int ColumnMin
|
---|
| 65 | {
|
---|
| 66 | get { return _columnMin; }
|
---|
| 67 | //set { _columnMin=value; }
|
---|
| 68 | }
|
---|
| 69 |
|
---|
| 70 | internal int _columnMax;
|
---|
| 71 | /// <summary>
|
---|
| 72 | /// Sets the last column the definition refers to.
|
---|
| 73 | /// </summary>
|
---|
| 74 | public int ColumnMax
|
---|
| 75 | {
|
---|
| 76 | get { return _columnMax; }
|
---|
| 77 | set
|
---|
| 78 | {
|
---|
| 79 | if (value < _columnMin && value > ExcelPackage.MaxColumns)
|
---|
| 80 | {
|
---|
| 81 | throw new Exception("ColumnMax out of range");
|
---|
| 82 | }
|
---|
| 83 |
|
---|
| 84 | var cse = new CellsStoreEnumerator<object>(_worksheet._values, 0, 0, 0, ExcelPackage.MaxColumns);
|
---|
| 85 | while(cse.Next())
|
---|
| 86 | {
|
---|
| 87 | var c = cse.Value as ExcelColumn;
|
---|
| 88 | if (cse.Column > _columnMin && c.ColumnMax <= value && cse.Column!=_columnMin)
|
---|
| 89 | {
|
---|
| 90 | throw new Exception(string.Format("ColumnMax can not span over existing column {0}.",c.ColumnMin));
|
---|
| 91 | }
|
---|
| 92 | }
|
---|
| 93 | _columnMax = value;
|
---|
| 94 | }
|
---|
| 95 | }
|
---|
| 96 | /// <summary>
|
---|
| 97 | /// Internal range id for the column
|
---|
| 98 | /// </summary>
|
---|
| 99 | internal ulong ColumnID
|
---|
| 100 | {
|
---|
| 101 | get
|
---|
| 102 | {
|
---|
| 103 | return ExcelColumn.GetColumnID(_worksheet.SheetID, ColumnMin);
|
---|
| 104 | }
|
---|
| 105 | }
|
---|
| 106 | #region ExcelColumn Hidden
|
---|
| 107 | /// <summary>
|
---|
| 108 | /// Allows the column to be hidden in the worksheet
|
---|
| 109 | /// </summary>
|
---|
| 110 | internal bool _hidden=false;
|
---|
| 111 | public bool Hidden
|
---|
| 112 | {
|
---|
| 113 | get
|
---|
| 114 | {
|
---|
| 115 | return _hidden;
|
---|
| 116 | }
|
---|
| 117 | set
|
---|
| 118 | {
|
---|
| 119 | if (_worksheet._package.DoAdjustDrawings)
|
---|
| 120 | {
|
---|
| 121 | var pos = _worksheet.Drawings.GetDrawingWidths();
|
---|
| 122 | _hidden = value;
|
---|
| 123 | _worksheet.Drawings.AdjustWidth(pos);
|
---|
| 124 | }
|
---|
| 125 | else
|
---|
| 126 | {
|
---|
| 127 | _hidden = value;
|
---|
| 128 | }
|
---|
| 129 | }
|
---|
| 130 | }
|
---|
| 131 | #endregion
|
---|
| 132 |
|
---|
| 133 | #region ExcelColumn Width
|
---|
| 134 | internal double VisualWidth
|
---|
| 135 | {
|
---|
| 136 | get
|
---|
| 137 | {
|
---|
| 138 | if (_hidden || (Collapsed && OutlineLevel>0))
|
---|
| 139 | {
|
---|
| 140 | return 0;
|
---|
| 141 | }
|
---|
| 142 | else
|
---|
| 143 | {
|
---|
| 144 | return _width;
|
---|
| 145 | }
|
---|
| 146 | }
|
---|
| 147 | }
|
---|
| 148 | internal double _width;
|
---|
| 149 | /// <summary>
|
---|
| 150 | /// Sets the width of the column in the worksheet
|
---|
| 151 | /// </summary>
|
---|
| 152 | public double Width
|
---|
| 153 | {
|
---|
| 154 | get
|
---|
| 155 | {
|
---|
| 156 | return _width;
|
---|
| 157 | }
|
---|
| 158 | set
|
---|
| 159 | {
|
---|
| 160 | if (_worksheet._package.DoAdjustDrawings)
|
---|
| 161 | {
|
---|
| 162 | var pos = _worksheet.Drawings.GetDrawingWidths();
|
---|
| 163 | _width = value;
|
---|
| 164 | _worksheet.Drawings.AdjustWidth(pos);
|
---|
| 165 | }
|
---|
| 166 | else
|
---|
| 167 | {
|
---|
| 168 | _width = value;
|
---|
| 169 | }
|
---|
| 170 |
|
---|
| 171 | if (_hidden && value!=0)
|
---|
| 172 | {
|
---|
| 173 | _hidden = false;
|
---|
| 174 | }
|
---|
| 175 | }
|
---|
| 176 | }
|
---|
| 177 | /// <summary>
|
---|
| 178 | /// If set to true a column automaticlly resize(grow wider) when a user inputs numbers in a cell.
|
---|
| 179 | /// </summary>
|
---|
| 180 | public bool BestFit
|
---|
| 181 | {
|
---|
| 182 | get;
|
---|
| 183 | set;
|
---|
| 184 | }
|
---|
| 185 | /// <summary>
|
---|
| 186 | /// If the column is collapsed in outline mode
|
---|
| 187 | /// </summary>
|
---|
| 188 | public bool Collapsed { get; set; }
|
---|
| 189 | /// <summary>
|
---|
| 190 | /// Outline level. Zero if no outline
|
---|
| 191 | /// </summary>
|
---|
| 192 | public int OutlineLevel { get; set; }
|
---|
| 193 | /// <summary>
|
---|
| 194 | /// Phonetic
|
---|
| 195 | /// </summary>
|
---|
| 196 | public bool Phonetic { get; set; }
|
---|
| 197 | #endregion
|
---|
| 198 |
|
---|
| 199 | #region ExcelColumn Style
|
---|
| 200 | /// <summary>
|
---|
| 201 | /// The Style applied to the whole column. Only effects cells with no individual style set.
|
---|
| 202 | /// Use Range object if you want to set specific styles.
|
---|
| 203 | /// </summary>
|
---|
| 204 | public ExcelStyle Style
|
---|
| 205 | {
|
---|
| 206 | get
|
---|
| 207 | {
|
---|
| 208 | string letter = ExcelCellBase.GetColumnLetter(ColumnMin);
|
---|
| 209 | string endLetter = ExcelCellBase.GetColumnLetter(ColumnMax);
|
---|
| 210 | return _worksheet.Workbook.Styles.GetStyleObject(StyleID, _worksheet.PositionID, letter + ":" + endLetter);
|
---|
| 211 | }
|
---|
| 212 | }
|
---|
| 213 | internal string _styleName="";
|
---|
| 214 | /// <summary>
|
---|
| 215 | /// Sets the style for the entire column using a style name.
|
---|
| 216 | /// </summary>
|
---|
| 217 | public string StyleName
|
---|
| 218 | {
|
---|
| 219 | get
|
---|
| 220 | {
|
---|
| 221 | return _styleName;
|
---|
| 222 | }
|
---|
| 223 | set
|
---|
| 224 | {
|
---|
| 225 | StyleID = _worksheet.Workbook.Styles.GetStyleIdFromName(value);
|
---|
| 226 | _styleName = value;
|
---|
| 227 | }
|
---|
| 228 | }
|
---|
| 229 | //internal int _styleID = 0;
|
---|
| 230 | /// <summary>
|
---|
| 231 | /// Sets the style for the entire column using the style ID.
|
---|
| 232 | /// </summary>
|
---|
| 233 | public int StyleID
|
---|
| 234 | {
|
---|
| 235 | get
|
---|
| 236 | {
|
---|
| 237 | return _worksheet._styles.GetValue(0, ColumnMin);
|
---|
| 238 | }
|
---|
| 239 | set
|
---|
| 240 | {
|
---|
| 241 | _worksheet._styles.SetValue(0, ColumnMin, value);
|
---|
| 242 | }
|
---|
| 243 | }
|
---|
| 244 | /// <summary>
|
---|
| 245 | /// Adds a manual page break after the column.
|
---|
| 246 | /// </summary>
|
---|
| 247 | public bool PageBreak
|
---|
| 248 | {
|
---|
| 249 | get;
|
---|
| 250 | set;
|
---|
| 251 | }
|
---|
| 252 | public bool Merged
|
---|
| 253 | {
|
---|
| 254 | get
|
---|
| 255 | {
|
---|
| 256 | return _worksheet.MergedCells[ColumnMin, 0] != null;
|
---|
| 257 | }
|
---|
| 258 | set
|
---|
| 259 | {
|
---|
| 260 | _worksheet.MergedCells.Add(new ExcelAddressBase(1, ColumnMin, ExcelPackage.MaxRows, ColumnMax), true);
|
---|
| 261 | }
|
---|
| 262 | }
|
---|
| 263 | #endregion
|
---|
| 264 |
|
---|
| 265 | /// <summary>
|
---|
| 266 | /// Returns the range of columns covered by the column definition.
|
---|
| 267 | /// </summary>
|
---|
| 268 | /// <returns>A string describing the range of columns covered by the column definition.</returns>
|
---|
| 269 | public override string ToString()
|
---|
| 270 | {
|
---|
| 271 | return string.Format("Column Range: {0} to {1}", _colElement.GetAttribute("min"), _colElement.GetAttribute("min"));
|
---|
| 272 | }
|
---|
| 273 | /// <summary>
|
---|
| 274 | /// Set the column width from the content of the range. The minimum width is the value of the ExcelWorksheet.defaultColumnWidth property.
|
---|
| 275 | /// Note: Cells containing formulas are ignored since EPPlus don't have a calculation engine.
|
---|
| 276 | /// Wrapped and merged cells are also ignored.
|
---|
| 277 | /// </summary>
|
---|
| 278 | public void AutoFit()
|
---|
| 279 | {
|
---|
| 280 | _worksheet.Cells[1, _columnMin, ExcelPackage.MaxRows, _columnMax].AutoFitColumns();
|
---|
| 281 | }
|
---|
| 282 |
|
---|
| 283 | /// <summary>
|
---|
| 284 | /// Set the column width from the content.
|
---|
| 285 | /// Note: Cells containing formulas are ignored since EPPlus don't have a calculation engine.
|
---|
| 286 | /// Wrapped and merged cells are also ignored.
|
---|
| 287 | /// </summary>
|
---|
| 288 | /// <param name="MinimumWidth">Minimum column width</param>
|
---|
| 289 | public void AutoFit(double MinimumWidth)
|
---|
| 290 | {
|
---|
| 291 | _worksheet.Cells[1, _columnMin, ExcelPackage.MaxRows, _columnMax].AutoFitColumns(MinimumWidth);
|
---|
| 292 | }
|
---|
| 293 |
|
---|
| 294 | /// <summary>
|
---|
| 295 | /// Set the column width from the content.
|
---|
| 296 | /// Note: Cells containing formulas are ignored since EPPlus don't have a calculation engine.
|
---|
| 297 | /// Wrapped and merged cells are also ignored.
|
---|
| 298 | /// </summary>
|
---|
| 299 | /// <param name="MinimumWidth">Minimum column width</param>
|
---|
| 300 | /// <param name="MaximumWidth">Maximum column width</param>
|
---|
| 301 | public void AutoFit(double MinimumWidth, double MaximumWidth)
|
---|
| 302 | {
|
---|
| 303 | _worksheet.Cells[1, _columnMin, ExcelPackage.MaxRows, _columnMax].AutoFitColumns(MinimumWidth, MaximumWidth);
|
---|
| 304 | }
|
---|
| 305 |
|
---|
| 306 | /// <summary>
|
---|
| 307 | /// Get the internal RangeID
|
---|
| 308 | /// </summary>
|
---|
| 309 | /// <param name="sheetID">Sheet no</param>
|
---|
| 310 | /// <param name="column">Column</param>
|
---|
| 311 | /// <returns></returns>
|
---|
| 312 | internal static ulong GetColumnID(int sheetID, int column)
|
---|
| 313 | {
|
---|
| 314 | return ((ulong)sheetID) + (((ulong)column) << 15);
|
---|
| 315 | }
|
---|
| 316 |
|
---|
| 317 | #region IRangeID Members
|
---|
| 318 |
|
---|
| 319 | ulong IRangeID.RangeID
|
---|
| 320 | {
|
---|
| 321 | get
|
---|
| 322 | {
|
---|
| 323 | return ColumnID;
|
---|
| 324 | }
|
---|
| 325 | set
|
---|
| 326 | {
|
---|
| 327 | int prevColMin = _columnMin;
|
---|
| 328 | _columnMin = ((int)(value >> 15) & 0x3FF);
|
---|
| 329 | _columnMax += prevColMin - ColumnMin;
|
---|
| 330 | //Todo:More Validation
|
---|
| 331 | if (_columnMax > ExcelPackage.MaxColumns) _columnMax = ExcelPackage.MaxColumns;
|
---|
| 332 | }
|
---|
| 333 | }
|
---|
| 334 |
|
---|
| 335 | #endregion
|
---|
| 336 |
|
---|
| 337 | /// <summary>
|
---|
| 338 | /// Copies the current column to a new worksheet
|
---|
| 339 | /// </summary>
|
---|
| 340 | /// <param name="added">The worksheet where the copy will be created</param>
|
---|
| 341 | internal ExcelColumn Clone(ExcelWorksheet added)
|
---|
| 342 | {
|
---|
| 343 | return Clone(added, ColumnMin);
|
---|
| 344 | }
|
---|
| 345 | internal ExcelColumn Clone(ExcelWorksheet added, int col)
|
---|
| 346 | {
|
---|
| 347 | ExcelColumn newCol = added.Column(col);
|
---|
| 348 | newCol.ColumnMax = ColumnMax;
|
---|
| 349 | newCol.BestFit = BestFit;
|
---|
| 350 | newCol.Collapsed = Collapsed;
|
---|
| 351 | newCol.OutlineLevel = OutlineLevel;
|
---|
| 352 | newCol.PageBreak = PageBreak;
|
---|
| 353 | newCol.Phonetic = Phonetic;
|
---|
| 354 | newCol._styleName = _styleName;
|
---|
| 355 | newCol.StyleID = StyleID;
|
---|
| 356 | newCol.Width = Width;
|
---|
| 357 | newCol.Hidden = Hidden;
|
---|
| 358 | return newCol;
|
---|
| 359 | }
|
---|
| 360 | }
|
---|
| 361 | }
|
---|