Export Only Visible datagridview to Excel with Background and Font Color and Other Formatting

Introduction

Often, we are compelled to use Datagridview for displaying complex Reports data and a lot of formatting may be done to show it in a presentable format including backcolor fonts, etc. But after exporting this datagridview to Excel, all the formatting done may be gone. This tip will help you to keep the formatting even after exporting to Excel.

Background

In my application, I need to show a very complex report which for me was almost impossible to accomplish in Crystal report or RDLC so I created that report with the help of datagridview. Then the reports rows are made visible and invisible according to the privilege of the user viewing the report. So manager may be seeing the consolidated summary report with profit loss ratio while a clerk may be seeing only the details against the production and not any profit or loss, etc. But when I export this report to Excel, all the rows that are either visible or invisible are exported. So I write the below module.

Using the Code

First, I created a class called DataExporter and added the below references:

using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop;
using System.Data;
using System.Drawing;
using Excel = Microsoft.Office.Interop.Excel;

namespace Shipit.Transaction
{
    public class DataExporter
    {

    }
}

Then, I added a function ExportToExcelWithFormat() as below:

    public void ExportToExcelWithFormat(System.Windows.Forms.DataGridView dataGridView1)
{
            int rownum = 1;
            // intialize excel application
            var excelApp = new Excel.Application();
            excelApp.Visible = true;
            // creates a workbook
            Excel.Workbook excelbk = excelApp.Workbooks.Add(Type.Missing);
            //Add a Workseet named sheet1 to above workbook
            Excel.Worksheet xlWorkSheet1 = (Excel.Worksheet)excelbk.Worksheets["Sheet1"];

            //Add each column name of datagridview to the first row of Excel,
            //this will be the header text
            for (int colCount = 0; colCount < dataGridView1.Columns.Count; colCount++)
            {
                Excel.Range xlRange = (Excel.Range)xlWorkSheet1.Cells[rownum, colCount + 1];
                xlRange.Value2 = dataGridView1.Columns[colCount].Name;
            }
            // for each row in the datagridview
            for (int rowCount = 0; rowCount < dataGridView1.Rows.Count ; rowCount++)
            {
                //if the row is visible
                if (dataGridView1.Rows[rowCount].Visible == true)
                {
                    //increment the row number for excel
                    rownum = rownum + 1;
                    for (int colCount = 0; colCount < dataGridView1.Columns.Count; colCount++)
                    {
                        //create a excel range for the rownum and the columncount
                        Excel.Range xlRange = (Excel.Range)xlWorkSheet1.Cells[rownum, colCount + 1];
                        try
                        {
                            //add the gridview cell value to the cellrange
                            xlRange.Value2 = 
                            dataGridView1.Rows[rowCount].Cells[colCount].Value.ToString();
                        }
                        catch (Exception)
                        {
                            try
                            {
                                xlRange.Value2 = "";
                            }
                            catch (Exception)
                            {

                            }
                        }
                        //set the interior range of the xlrange to the defaultcell style of row  
                        xlRange.Interior.Color = System.Drawing.ColorTranslator.ToOle
                        	(dataGridView1.Rows[rowCount].DefaultCellStyle.BackColor);

                        //set the font color  of the xlrange to the styletyle.ForeColor of row  
                        xlRange.Font.Color = dataGridView1.Rows[rowCount].Cells
                        			[colCount].Style.ForeColor.ToArgb();

                        if (dataGridView1.Rows[rowCount].Cells[colCount].Style.Font != null)
                        {
                            xlRange.Font.Bold = 
                            dataGridView1.Rows[rowCount].Cells[colCount].Style.Font.Bold;
                            xlRange.Font.Italic = 
                            dataGridView1.Rows[rowCount].Cells[colCount].Style.Font.Italic;
                            xlRange.Font.Underline = 
                            dataGridView1.Rows[rowCount].Cells[colCount].Style.Font.Underline;
                            xlRange.Font.FontStyle = 
                            dataGridView1.Rows[rowCount].Cells[colCount].Style.Font.FontFamily;
                        }
                    }
                }

But make sure that the DefaultCellstyle.BackColor is set to every row of datagridview else the exported row will take Black background. So even if now Defaultcellstyle is required, set it atleast to white in the location where the datagridview is intialized.

tbl_derdata.Rows[rowcount].DefaultCellStyle.BackColor = Color.White;

Now in the Export to Excel button, pass the datagridview to the above function.

private void exportToExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Transaction.DataExporter xprtr = new Transaction.DataExporter();
            xprtr.ExportToExcelWithFormat(tbl_derdata);
            MessageBox.Show ("Report Exported")            
        }

Points of Interest

We can add other formatting like borders, etc. to the excelrange.

History

• 10^th November, 2015: Initial version