none
Exporting data grid view to excel with saving foramt (color, font, etc) RRS feed

  • Question

  • Hi

    I have a program with DataGridView.
    The user can change datagridview style (color for odd\even row, font - effect all datagridview. I have a specific column with specifics colors - red, grren or yellow acoording to data).

    I search a lot for export data from DataGridView to excel and I found this (with little changes):

            public void ExportToExecl(DataGridView dg, string filename)
            {
                 // creating Excel Application
                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
                // creating new WorkBook within Excel application
                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
                // creating new Excelsheet in workbook
                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
                // get the reference of first sheet. By default its name is Sheet1.
                // store its reference to worksheet
                try
                {
                    worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
                    worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
                    // changing the name of active sheet
                    worksheet.Name = "Exported from History Parsing";
                    // storing header part in Excel
                    for (int i = 1; i < dg.Columns.Count + 1; i++)
                    {
                        worksheet.Cells[1, i] = dg.Columns[i - 1].HeaderText;
                        //worksheet.Cells[1, i].Style.FillPattern.SetSolid(Color.Gray);	// Doesn't work
                    }
                    // storing Each row and column value to excel sheet
                    for (int i = 0; i < dg.Rows.Count - 1; i++)
                    {
                        for (int j = 0; j < dg.Columns.Count; j++)
                        {
                            worksheet.Cells[i + 2, j + 1] = dg.Rows[i].Cells[j].Value.ToString();
                            //worksheet.Cells[i + 2, j + 1].Style.FillPattern.SetSolid(Color.Gray);	// Doesn't work
                        }
                    }
                    // save the application
                    workbook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    MessageBox.Show("Your excel file was created successfully");
                }
                catch (System.Exception ex)
                {
                }
                finally
                {
                    app.Quit();
                    workbook = null;
                    app = null;
                }
            }

    My question: How can I keep the format (= colors, font)

    Thanks!


    • Edited by AsfK Monday, April 15, 2013 10:40 AM
    • Moved by Mike Feng Wednesday, April 17, 2013 1:22 PM
    Monday, April 15, 2013 10:40 AM

Answers

  • Hi Asfk, 

    I find the solution about your question, You set Color code is error, you can replace that with the below code:

    worksheet.Cells[i + 2, j + 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
    I test it and that code can work for me, you can test on your code. Thanks.


    If my post is helpful,please help to vote as helpful, if my post solve your question, please help to make it as answer. my sample

    • Proposed as answer by Learning hard Friday, April 19, 2013 8:04 AM
    • Marked as answer by AsfK Sunday, April 21, 2013 6:54 AM
    Friday, April 19, 2013 8:04 AM

All replies