Introduction
This is my second and final update to the Generalized DataGridView Export to Excel with Theme and Formatting.
To recap, its focus is on creating a dynamic and generic export solution that supports some formatting.
I added 4 more extra themes, some extra conditions, and alignment feature. I also added a direct export to Excel, no CSV in between. I'll only focus on what's new.
Fixed problem with non EU/US kind of cultures for the CSV export. Now it uses System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator instead of "," in the CSVToExcel()
method. Also fixed the specific number format rrr.NumberFormat = "#,##0.00 [$€-407]"; with the more generalized rrr.NumberFormat = String.Format("#,##0.00 [$€]");
For these fine observations, you can thank Mike.
Now, it has 4 new themes making a total of eight:
- Blue Sky -old
- Classic Gray -old
- GreenIsGood - old
- NiceViolet - old (renamed form SweetViolet)
- SweetPink - new
- LadyInRed - new
- OrangeWorks - new
- DarkBlue - new
Added one extra condition -> TEXT useful for columns with both numeric and text/string data. Also added a new Enum Align.
- 4 Currency formats (Euro and USD with 2 and 4 digits)
- 2 Percentage (with the default 2 decimals and 4 decimals)
- 3 Numeric conditions (with 2,3 or 4 digits)
- 2 DateTime conditions (one in the mm/dd/yy and the other in the dd/mm/yy format)
- The Text format
- 4 (actually 3) Alignment conditions(Left, Right, Center, and the default None)
In order to run the app, you need to add a reference to the Microsoft Excel 11.0 or 12.0 Object Library from the COM tab. Warning: In the 12.0 (2007) version, the colors differ so the output might not be what you want. You need to test it first.
Special thanks to PeterMoon for his idea in the Fast Exporting from DataSet to Excel article.
Using the Code
Using the code is quite straightforward and simple. The export Utility has three classes: the main class GenericFormattedExcel2003Export
and the helper classes ColumnCondition
and ColumnRowConditon
. The utility also has 4 enum
s: Condition
, Theme
and ThemeColors
.
Given below are the three enum
s:
public enum Condition
{
None,
Numeric,
Numeric3,
Numeric4,
Percentage,
Percentage4,
CurrencyEuro,
CurrencyEuro4,
CurrencyDollar,
CurrencyDollar4,
DateTime,
USDateTime,
Text
}
public enum Theme
{
BlueSky,
ClassicGray,
GreenIsGood,
NiceViolet,
DarkBlue,
SweetPink,
LadyInRed,
OrangeWorks,
CSV
}
public enum ThemeColors
{
FirstRowFontColor = 2,
BlueSkyFirstRowInteriorColor = 11,
BlueSkyInteriorColor = 37,
BlueSkyFontColor = 11,
ClassicGrayFirstRowInteriorColor = 48,
ClassicGrayInteriorColor = 15,
ClasicGaryFontColor = 56,
GreenIsGoodFirstRowInteriorColor = 50,
GreenIsGoodInteriorColor = 35,
GreenIsGoodFontColor = 10,
NiceVioletFirstRowInteriorColor = 13,
NiceVioletInteriorColor = 39,
NiceVioletFontColor = 13,
OrangeWorksFirstRowInterior = 46,
OrangeWorksFont = 53,
OrangeWorksInterior = 40,
SweetPinkFirstRowInterior = 7,
SweetPinkFont = 13,
SweetPinkInterior = 38,
LadyInRedFirstRow = 3,
LadyInRedFont = 3,
LadyInRedInterior = 38,
DarkBlueFirstRow = 55,
DarkBlueFont = 11,
DarkBlueInterior = 47
}
Using the code in a small test app. First, we need to add some data to the datagridview
. For this task, I created a dummy sample class named SomethingReallyReallyUseless
.
public class SomethingReallyReallyUseless
{
private string name;
private double sum = 0.00d;
private double performance = 0.00d;
public string Name
{
get { return name; }
set { name = value; }
}
public double Sum
{
get { return sum; }
set { sum = value; }
}
public double Performance
{
get { return performance; }
set { performance = value; }
}
}
The ColumnConditions
class:
public class ColumnConditions
{
private int column = 0;
private Conditon condition = Conditon.None;
private Align align = Align.None;
public int Column
{
get { return column; }
set { column = value; }
}
public Conditon Cond
{
get { return condition; }
set { condition = value; }
}
public Align Alignment
{
get { return align; }
set { align = value; }
}
}
Using the DarkBlue Theme with both column and row extra conditions. First we must create the conditions and then pass them to the GenericFormattedExcel2003Export
:
private void button1_Click(object sender, EventArgs e)
{
string mode = rCsv.Checked ? "CSV" : "XLS";
List<MyExcelExport.ColumnConditions> conds =
new List<MyExcelExport.ColumnConditions>();
MyExcelExport.ColumnConditions curr = null;
curr = new MyExcelExport.ColumnConditions();
curr.Column = 2;
curr.Cond = MyExcelExport.Conditon.CurrencyEuro;
conds.Add(curr);
curr = new MyExcelExport.ColumnConditions();
curr.Column = 3;
curr.Cond = MyExcelExport.Conditon.Percentage;
curr.Alignment = MyExcelExport.Align.Right;
conds.Add(curr);
List<MyExcelExport.ColumnRowConditon> rowConds =
new List<MyExcelExport.ColumnRowConditon>();
MyExcelExport.ColumnRowConditon row = new MyExcelExport.ColumnRowConditon();
row.Column = 1;
row.ConditionValue = "Total";
rowConds.Add(row);
try
{
MyExcelExport.GenericFormattedExcel2003Export gExp =
new MyExcelExport.GenericFormattedExcel2003Export(mode, dataGridView1,
MyExcelExport.Theme.DarkBlue, conds, rowConds, null);
}
catch (COMException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Using the DarkBlue Theme with no extra conditions.
private void button1_Click(object sender, EventArgs e)
{
string mode = rCsv.Checked ? "CSV" : "XLS";
try
{
MyExcelExport.GenericFormattedExcel2003Export gExp =
new MyExcelExport.GenericFormattedExcel2003Export(dataGridView1,
MyExcelExport.Theme.BlueSky, conds, rowConds);
}
catch (COMException ex)
{
MessageBox.Show(ex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Finally an updated code example of exporting in the Blue Sky Theme from the GenericFormattedExcel2003Export
class using PeterMoon's idea:
private object[,] CreateTwoDimensionalObject()
{
object[,] datas = new object[dgv.Rows.Count + 1, dgv.Rows[0].Cells.Count];
for (int col = 0; col < dgv.Columns.Count; col++)
{
datas[0, col] = dgv.Columns[col].HeaderText;
}
for (int col = 0; col < dgv.Rows[0].Cells.Count; col++)
{
for (int row = 0; row < dgv.Rows.Count; row++)
{
datas[row + 1, col] = dgv.Rows[row].Cells[col].Value.ToString();
}
}
return datas;
}
private void BlueSky()
{
try
{
object[,] datas = CreateTwoDimensionalObject();
object None = Type.Missing;
Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
Excel.Workbook wk = app.Workbooks.Add(None);
Excel.Worksheet ws = (Excel.Worksheet)wk.ActiveSheet;
Excel.Range upperLeft = (Excel.Range)ws.Cells[1, 1];
Excel.Range rightLimit = upperLeft.get_Offset(0, dgv.Columns.Count - 1);
Excel.Range bottomRight = rightLimit.get_Offset(dgv.Rows.Count, 0);
Excel.Range wholeThing = ws.get_Range(upperLeft, bottomRight);
wholeThing.Value2 = datas;
wholeThing.Font.ColorIndex = ThemeColors.BlueSkyFontColor;
wholeThing.Borders.Weight = Excel.XlBorderWeight.xlThin;
wholeThing.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
wholeThing.Borders.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
upperLeft.EntireRow.Font.Bold = true;
upperLeft.EntireRow.Font.Italic = true;
upperLeft.EntireRow.Font.ColorIndex = ThemeColors.FirstRowFontColor;
ws.get_Range(upperLeft, rightLimit).Interior.ColorIndex =
ThemeColors.BlueSkyFirstRowInteriorColor;
Excel.Range r;
for (int i = 1; i <= dgv.Rows[0].Cells.Count; i++)
{
r = (Excel.Range)ws.Cells[1, i];
r.EntireColumn.AutoFit();
}
r = (Excel.Range)ws.Cells[3, 1];
rightLimit = r.get_End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight);
ws.get_Range(r, rightLimit).Interior.ColorIndex =
ThemeColors.BlueSkyInteriorColor;
r = (Excel.Range)ws.Cells[2, 1];
bottomRight = r.get_Offset(1, 0);
bottomRight = bottomRight.get_End
(Microsoft.Office.Interop.Excel.XlDirection.xlToRight);
ws.get_Range(r, bottomRight).Copy(None);
r = (Excel.Range)ws.Cells[4, 1];
bottomRight = r.get_End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight);
bottomRight = bottomRight.get_End
(Microsoft.Office.Interop.Excel.XlDirection.xlDown);
wholeThing = ws.get_Range(r, bottomRight);
wholeThing.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.
xlPasteSpecialOperationNone, false, false);
ApplyColumnFormats(ws);
if ((this.rowConds != null) && (this.rowConds.Count > 0))
{
Excel.Range rrr = null;
foreach (DataGridViewRow dgvItem in dgv.Rows)
{
foreach (var rowCon in rowConds)
{
if (dgvItem.Cells[rowCon.Column - 1].Value.ToString().Equals
(rowCon.ConditionValue))
{
rrr = (Excel.Range)ws.Cells[dgvItem.Cells[0].RowIndex + 2,
dgvItem.Cells[rowCon.Column - 1].ColumnIndex + 1];
rrr.EntireRow.Font.Bold = true;
}
}
}
}
upperLeft.Select();
}
catch (COMException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
Points of Interest
It's been nice writing this article. I'll stop modifying/adding features to the article. I could add tons of new features but that's not the purpose of this article. You can modify and add your own extra conditions/formatting/whatever. Hope it helps someone out there. BRB with the 2007(Excel) extra cool themes/colors and other stuff.
History
- 30th October, 2009: This is the second and final update.
Happy Coding