C# Excel - Manage column properties
Scenario is to generate an excel report that has ~ 150 data columns. Now I need to manage the column properties like Width, BackgroundColor, Font etc.
The approach that I am using relies on reflection. I have a class that has ~ 150 constants for column header text. Another custom attribute class to store column properties. These attributes are applied to the constants.
During column creation using reflection I am accessing all the constants to create the header text(Constant ordering in class defines column ordering) and the attribute for column properties.
private void CreateHeader(Excel.Worksheet xl_WorkSheet, FieldInfo[] fi_Header)
{
ColumnProperties c;
System.Attribute[] customAttributes;
for (int i = 0; i < fi_Header.GetLength(0); i++)
{
xl_WorkSheet.get_Range(xl_WorkSheet.Cells[1, i+1], xl_WorkSheet.Cells[2, i+1]).Merge(false);
//Set the header text.
xl_WorkSheet.get_Range(xl_WorkSheet.Cells[1, i + 1], xl_WorkSheet.Cells[2, i + 1]).FormulaR1C1 =
fi_Header[i].GetValue(null).ToString();
//Set cell border.
xl_WorkSheet.get_Range(xl_WorkSheet.Cells[1, i + 1],
xl_WorkSheet.Cells[2, i + 1]).BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Missing.Value);
//Get custom attribute ~ Column attribute.
customAttributes = (System.Attribute[])fi_Header[i].GetCustomAttributes(typeof(ColumnProperties), false);
if (customAttributes.Length > 0)
{
c = (ColumnProperties)customAttributes[0];
//Set开发者_运维百科 column properties.
xl_WorkSheet.get_Range(xl_WorkSheet.Cells[1, i + 1],
xl_WorkSheet.Cells[2, i + 1]).Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName(c.Color));
xl_WorkSheet.get_Range(xl_WorkSheet.Cells[1, i + 1],
xl_WorkSheet.Cells[2, i + 1]).ColumnWidth = c.Width;
}
}
}
EDIT: Code to get constants
private FieldInfo[] GetHeaderConstants(System.Type type)
{
ArrayList constants = new ArrayList();
FieldInfo[] fieldInfos = type.GetFields(BindingFlags.Public | BindingFlags.Static | BindingFlags.FlattenHierarchy);
foreach (FieldInfo fi in fieldInfos)
{
if (fi.IsLiteral && !fi.IsInitOnly)
constants.Add(fi);
}
return (FieldInfo[])constants.ToArray(typeof(FieldInfo));
}
Main objective is to make the excel file generation generic/less maintainable. Is the approach fine or there are any other better alternatives.
EDIT 2: Constants class
public class ExcelHeaders
{
[ColumnProperties(Width=10, Color="LemonChiffon")]
public const string S_NO = "S.No";
[ColumnProperties(Width = 20, Color = "WhiteSmoke")]
public const string COLUMN_HEADER = "Header Text";
}
One characteristic of your approach is that you will need to change your source if you want to change a column's appearance. I would prefer storing the appearance data in some kind of XML configuration. You could load the configuration from an external configuration file if available, else from a default configuration that's embedded in the executable as a resource. This gives you the flexibility to change the configuration at runtime simply by adding a configuration file.
Your XML document might look like:
<Appearance>
<!-- Defaults to use for attributes not explicitly specified -->
<Defaults HeaderText="" Width="10" Color="White" />
<Columns>
<Column HeaderText="S.No" Width="10" Color="LemonChiffon" />
<Column HeaderText="Header Text" Width="20" Color="WhiteSmoke" />
</Columns>
</Appearance>
- Where does the need for reflection come into play?
- I don't think you can rely on reflection to return your constants in the order in which you declared them in your source code.
Edit:
Well, I must be missing something -- I really don't get what you're reflecting on, if not something of your own creating, and of which you can readily be passed a reference.
It seems like you can create a class like so:
public class ColumnProperties
{
readonly string m_HeaderText;
public ColumnProperties(string headerText, Color color, int width) { ... }
public string HeaderText { get { return m_HeaderText; }
public Color FontColor { get; set; }
public int Width { get; set; }
...
}
Then if you have 150 datapoints, create 150 ColumnProperties
objects. Pass a collection of these into your CreateHeader()
method, and forget about all that gratuitous reflection and those custom attributes.
精彩评论