C# Winforms save to excel with formatting

Question

I need to save a DataGridView's content to Excel and be able to match the cell colours from the DataGridView's cells with cells from Excel.

If I were to save to CSV the saving of the file is instant but no styling can be applied so I need to use Microsoft.Office.Interop.Excel

That gives me the correct styling but it is so so so so slow.

Is there a workaround to that?

public static void ExportToExcel(this DataGridView Tbl, string ExcelFilePath = null)
    {
        try
        {
            if (Tbl == null || Tbl.Columns.Count == 0)
                throw new Exception("ExportToExcel: Null or empty input table!\n");

            // load excel, and create a new workbook

            Excel.Application excelApp = new Excel.Application();
            excelApp.Workbooks.Add();

            // single worksheet
            Excel._Worksheet workSheet = excelApp.ActiveSheet;

            // column headings
            for (int i = 0; i < Tbl.Columns.Count; i++)
            {
                workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].Name;
            }

            // rows
            for (int i = 0; i < Tbl.Rows.Count; i++)
            {
                // to do: format datetime values before printing
                for (int j = 0; j < Tbl.Columns.Count; j++)
                {
                    workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i].Cells[j].Value;
                    ((Excel.Range)(workSheet.Cells[(i + 2), (j + 1)])).Interior.Color = System.Drawing.Color.Orange;//System.Drawing.ColorTranslator.ToOle(Tbl.Rows[i].Cells[j].Style.BackColor);
                    //workSheet.Cells[(i + 2), (j + 1)].Interior.Color = Tbl.Rows[i].Cells[j].Style.BackColor;
                }
            }

            // check filepath
            if (ExcelFilePath != null && ExcelFilePath != "")
            {
                try
                {
                    workSheet.SaveAs(ExcelFilePath);
                    excelApp.Quit();
                    MessageBox.Show("Excel file saved!");
                }
                catch (Exception ex)
                {
                    throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                        + ex.Message);
                }
            }
            else    // no filepath is given
            {
                excelApp.Visible = true;
            }
        }
        catch (Exception ex)
        {
            throw new Exception("ExportToExcel: \n" + ex.Message);
        }
    }

http://stackoverflow.com/questions/3989122/microsoft-office-interop-excel-really-slow see that — Poomrokc The 3years, Apr 08 '14 at 11:09
You could try using a third-party library such as EPPlus (Excel 2007+) or Aspose. Or try profiling your Interop code to understand why it's "so so so slow". There may be things you can do to improve performance, e.g. see http://stackoverflow.com/questions/3989122/microsoft-office-interop-excel-really-slow/3989452#3989452 — Joe, Apr 08 '14 at 11:11
This approach is rather slower because of Interoperability. You can use Third party tools like, Flexcel Spreadsheet or Gembox Spreadsheet that provides style and formatting too. — Rohit Prakash, Apr 08 '14 at 11:14
Can I use the matrix example above with a DataTable in the same way? — sd_dracula, Apr 08 '14 at 11:17
It seems that passing in a DataTable or DataGridView to this line `range.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, indexMatrix);` causes an error. What type should be passed in? — sd_dracula, Apr 08 '14 at 13:27

score 1 · Answer 1 · answered Apr 08 '14 at 14:04

you can use this Library (opensource). the code :

//create new xls file
string file = "C:\\newdoc.xls";
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("First Sheet");
worksheet.Cells[0, 1] = new Cell((short)1);
worksheet.Cells[2, 0] = new Cell(9999999);
worksheet.Cells[3, 3] = new Cell((decimal)3.45);
worksheet.Cells[2, 2] = new Cell("Text string");
worksheet.Cells[2, 4] = new Cell("Second string");
worksheet.Cells[4, 0] = new Cell(32764.5, "#,##0.00");
worksheet.Cells[5, 1] = new Cell(DateTime.Now, @"YYYY\-MM\-DD");
worksheet.Cells.ColumnWidth[0, 1] = 3000;
workbook.Worksheets.Add(worksheet);
workbook.Save(file);

What do you mean by "this library" ? What library ? – Yox Oct 23 '21 at 22:09 — Yox, Oct 23 '21 at 22:09

score 0 · Answer 2 · answered Apr 08 '14 at 11:22

0

You should use the Open XML SDK: http://msdn.microsoft.com/en-us/library/hh180830(v=office.14).aspx

In this xml file you can also include formatting. I haven't tested the performance, but it should be a lot faster.

answered Apr 08 '14 at 11:22

David

853
8
24

C# Winforms save to excel with formatting

2 Answers2