CODEWITHSUNDEEP
c#winformsexceldatagridview
sd_dracula
sd_dracula

Reputation: 3896

C# Winforms save to excel with formatting

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);
        }
    }

Upvotes: 0

Views: 3056

Answers (2)

mister
mister

Reputation: 95

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);

Upvotes: 1

David
David

Reputation: 863

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.

Upvotes: 0

Related Questions