Free cookie consent management tool by TermsFeed Policy Generator

source: branches/Async/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/4.0.3/EPPlus-4.0.3/Table/ExcelTableColumn.cs @ 13401

Last change on this file since 13401 was 12074, checked in by sraggl, 10 years ago

#2341: Added EPPlus-4.0.3 to ExtLibs

File size: 7.8 KB
Line 
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    Added   30-AUG-2010
30 * Jan Källman    License changed GPL-->LGPL 2011-12-16
31 *******************************************************************************/
32using System;
33using System.Collections.Generic;
34using System.Globalization;
35using System.Text;
36using System.Xml;
37
38namespace OfficeOpenXml.Table
39{
40    /// <summary>
41    /// Build-in table row functions
42    /// </summary>
43    public enum RowFunctions
44    {
45        Average,       
46        Count,
47        CountNums,
48        Custom,
49        Max,
50        Min,
51        None,
52        StdDev,
53        Sum,
54        Var
55    }
56
57    /// <summary>
58    /// A table column
59    /// </summary>
60    public class ExcelTableColumn : XmlHelper
61    {
62        internal ExcelTable _tbl;
63        internal ExcelTableColumn(XmlNamespaceManager ns, XmlNode topNode, ExcelTable tbl, int pos) :
64            base(ns, topNode)
65        {
66            _tbl = tbl;
67            Position = pos;
68        }
69        /// <summary>
70        /// The column id
71        /// </summary>
72        public int Id
73        {
74            get
75            {
76                return GetXmlNodeInt("@id");
77            }
78            set
79            {
80                SetXmlNodeString("@id", value.ToString());
81            }
82        }
83        /// <summary>
84        /// The position of the column
85        /// </summary>
86        public int Position
87        {
88            get;
89            private set;
90        }
91        /// <summary>
92        /// The name of the column
93        /// </summary>
94        public string Name
95        {
96            get
97            {
98                var n=GetXmlNodeString("@name");
99                if (string.IsNullOrEmpty(n))
100                {
101                    if (_tbl.ShowHeader)
102                    {
103                        n = _tbl.WorkSheet.GetValue<string>(_tbl.Address._fromRow, _tbl.Address._fromCol + this.Position);
104                    }
105                    else
106                    {
107                        n = "Column" + (this.Position+1).ToString();
108                    }
109                }
110                return n;
111            }
112            set
113            {
114                SetXmlNodeString("@name", value);
115                _tbl.WorkSheet.SetTableTotalFunction(_tbl, this);
116            }
117        }
118        /// <summary>
119        /// A string text in the total row
120        /// </summary>
121        public string TotalsRowLabel
122        {
123            get
124            {
125                return GetXmlNodeString("@totalsRowLabel");
126            }
127            set
128            {
129                SetXmlNodeString("@totalsRowLabel", value);
130            }
131        }
132        /// <summary>
133        /// Build-in total row functions.
134        /// To set a custom Total row formula use the TotalsRowFormula property
135        /// <seealso cref="TotalsRowFormula"/>
136        /// </summary>
137        public RowFunctions TotalsRowFunction
138        {
139            get
140            {
141                if (GetXmlNodeString("@totalsRowFunction") == "")
142                {
143                    return RowFunctions.None;
144                }
145                else
146                {
147                    return (RowFunctions)Enum.Parse(typeof(RowFunctions), GetXmlNodeString("@totalsRowFunction"), true);
148                }
149            }
150            set
151            {
152                if (value == RowFunctions.Custom)
153                {
154                    throw(new Exception("Use the TotalsRowFormula-property to set a custom table formula"));
155                }
156                string s = value.ToString();
157                s = s.Substring(0, 1).ToLower(CultureInfo.InvariantCulture) + s.Substring(1, s.Length - 1);
158                SetXmlNodeString("@totalsRowFunction", s);
159                _tbl.WorkSheet.SetTableTotalFunction(_tbl, this);
160            }
161        }
162        const string TOTALSROWFORMULA_PATH = "d:totalsRowFormula";
163        /// <summary>
164        /// Sets a custom Totals row Formula.
165        /// Be carefull with this property since it is not validated.
166        /// <example>
167        /// tbl.Columns[9].TotalsRowFormula = string.Format("SUM([{0}])",tbl.Columns[9].Name);
168        /// </example>
169        /// </summary>
170        public string TotalsRowFormula
171        {
172            get
173            {
174                return GetXmlNodeString(TOTALSROWFORMULA_PATH);
175            }
176            set
177            {
178                if (value.StartsWith("=")) value = value.Substring(1, value.Length - 1);
179                SetXmlNodeString("@totalsRowFunction", "custom");               
180                SetXmlNodeString(TOTALSROWFORMULA_PATH, value);
181                _tbl.WorkSheet.SetTableTotalFunction(_tbl, this);
182            }
183        }
184        const string DATACELLSTYLE_PATH = "@dataCellStyle";
185        /// <summary>
186        /// The named style for datacells in the column
187        /// </summary>
188        public string DataCellStyleName
189        {
190            get
191            {
192                return GetXmlNodeString(DATACELLSTYLE_PATH);
193            }
194            set
195            {
196                if(_tbl.WorkSheet.Workbook.Styles.NamedStyles.FindIndexByID(value)<0)
197                {
198                    throw(new Exception(string.Format("Named style {0} does not exist.",value)));
199                }
200                SetXmlNodeString(TopNode, DATACELLSTYLE_PATH, value,true);
201               
202                int fromRow=_tbl.Address._fromRow + (_tbl.ShowHeader?1:0),
203                    toRow=_tbl.Address._toRow - (_tbl.ShowTotal?1:0),
204                    col=_tbl.Address._fromCol+Position;
205
206                if (fromRow < toRow)
207                {
208                    _tbl.WorkSheet.Cells[fromRow, col, toRow, col].StyleName = value;
209                }
210            }
211        }
212      const string CALCULATEDCOLUMNFORMULA_PATH = "d:calculatedColumnFormula";
213    /// <summary>
214    /// Sets a calculated column Formula.
215    /// Be carefull with this property since it is not validated.
216    /// <example>
217    /// tbl.Columns[9].CalculatedColumnFormula = string.Format("SUM(MyDataTable[[#This Row],[{0}]])",tbl.Columns[9].Name);
218    /// </example>
219    /// </summary>
220    public string CalculatedColumnFormula
221    {
222      get
223      {
224        return GetXmlNodeString(CALCULATEDCOLUMNFORMULA_PATH);
225      }
226      set
227      {
228        if (value.StartsWith("=")) value = value.Substring(1, value.Length - 1);
229        SetXmlNodeString(CALCULATEDCOLUMNFORMULA_PATH, value);
230      }
231    }
232
233    }
234}
Note: See TracBrowser for help on using the repository browser.