Generalized DataGridView Export to Excel with Theme and Formatting

• Download demo - 29.09 KB
• Download source - 97.78 KB

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 enums: Condition, Theme and ThemeColors.

Given below are the three enums:

public enum Condition
{
    None,			//default(no condition)
    Numeric,		//2 digits
    Numeric3,		//3 digits
    Numeric4,		//4 digits
    Percentage,		//2 digits
    Percentage4,	//4 digits
    CurrencyEuro,	//2 digits
    CurrencyEuro4,	//4 digits
    CurrencyDollar,	//2 digits
    CurrencyDollar4,//4 digits
    DateTime,		//dd/mm/yyyy
    USDateTime,		//mm/dd/yyy
    Text
}

public enum Theme
{
    BlueSky,
    ClassicGray,
    GreenIsGood,
    NiceViolet,
    DarkBlue,//new
    SweetPink,//new
    LadyInRed,//new
    OrangeWorks,//new
    CSV//no theme and a lot faster *.CSV export
}

public enum ThemeColors
{
    //first row FontColor is WHITE for all the themes
    FirstRowFontColor = 2,
    //the BlueSkyColors
    BlueSkyFirstRowInteriorColor = 11,
    BlueSkyInteriorColor = 37,
    BlueSkyFontColor = 11,
    //the ClassicGrayColors
    ClassicGrayFirstRowInteriorColor = 48,
    ClassicGrayInteriorColor = 15,
    ClasicGaryFontColor = 56,
    //the GreenIsGoodColors
    GreenIsGoodFirstRowInteriorColor = 50,
    GreenIsGoodInteriorColor = 35,
    GreenIsGoodFontColor = 10,
    //the SweetViolet/PinkColors for the ladies:)
    NiceVioletFirstRowInteriorColor = 13,
    NiceVioletInteriorColor = 39,
    NiceVioletFontColor = 13,
    //the OrangwWorks colors
    OrangeWorksFirstRowInterior = 46,
    OrangeWorksFont = 53,
    OrangeWorksInterior = 40,
    //the SweetPink colors
    SweetPinkFirstRowInterior = 7,
    SweetPinkFont = 13,
    SweetPinkInterior = 38,
    //the LadyInRed colors
    LadyInRedFirstRow = 3,
    LadyInRedFont = 3,
    LadyInRedInterior = 38,
    //the DarkBlue colors
    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.

//a dummy class to load some data into the gridview
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;

    //create the column conditions

    //no need to specify alignment
    //only if you wish/needed. It defaults to MyExcelExport.Align.None
    curr = new MyExcelExport.ColumnConditions();
    curr.Column = 2;
    curr.Cond = MyExcelExport.Conditon.CurrencyEuro;
    conds.Add(curr);

    //specify Alignment
    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:

//this is PeterMoon's idea-> to use Range.Value2 property 
//and paste a bi dimensional array of objects/datas
//creates the two dimensional object[,] from the datagridview
private object[,] CreateTwoDimensionalObject()
{
    object[,] datas = new object[dgv.Rows.Count + 1, dgv.Rows[0].Cells.Count];

    //add the first row(the column headers) to the array
    for (int col = 0; col < dgv.Columns.Count; col++)
    {
        datas[0, col] = dgv.Columns[col].HeaderText;
    }

    //copy the actual datas
    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;
}

//exports in the blue sky theme
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;
        //set the font color
        wholeThing.Font.ColorIndex = ThemeColors.BlueSkyFontColor;
        //create the borders
        wholeThing.Borders.Weight = Excel.XlBorderWeight.xlThin;
        wholeThing.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
        wholeThing.Borders.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

        //format first row
        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;
        //set the auto fit for each column
        for (int i = 1; i <= dgv.Rows[0].Cells.Count; i++)
        {
            r = (Excel.Range)ws.Cells[1, i];
            r.EntireColumn.AutoFit();
        }

        //color the 3rd row
        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;

        //get the second and third row
        r = (Excel.Range)ws.Cells[2, 1];
        bottomRight = r.get_Offset(1, 0);
        bottomRight = bottomRight.get_End
			(Microsoft.Office.Interop.Excel.XlDirection.xlToRight);
        //copy the range
        ws.get_Range(r, bottomRight).Copy(None);

        //get the whole range
        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);
        //now paste special -> formats
        wholeThing.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats,
          	Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.
		xlPasteSpecialOperationNone, false, false);

        //apply column formatting, if any
        ApplyColumnFormats(ws);
        //apply rowcolumn(Bold row) formatting, if any
        if ((this.rowConds != null) && (this.rowConds.Count > 0))
        {
            Excel.Range rrr = null;
            //pass each datagridview row only once
            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;
                    }
                }
            }
        }
        //select cell A1
        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

• 30^th October, 2009: This is the second and final update.

Happy Coding