'Export DataGridView to Excel File

Can you say me, how can i save the contents of DataGridView to Exel File with the Formatting cells Backcolor/Forecolor? I have DGV like this: enter image description here

And when I try to Export Data to Excel I get the data, but without Forecolor, I have tried a several of ways, but it does not work! Beforehand Thank you!

private void btnExport_Click(object sender, EventArgs e)
    {


        SaveFileDialog sfd = new SaveFileDialog();
        sfd.Filter = "Excel Documents (*.xls)|*.xls";
        sfd.FileName = "export.xls";
        if (sfd.ShowDialog() == DialogResult.OK)
        {
            //ToCsV(dataGridView1, @"c:\export.xls");
            ToCsV1(dataGridView1, sfd.FileName);
        }  
}



private void ToCsV1(DataGridView dGV, string filename)
    {
        string stOutput = "";
        // Export titles:
        string sHeaders = "";

        for (int j = 0; j < dGV.Columns.Count; j++)
            sHeaders = sHeaders.ToString() + Convert.ToString(dGV.Columns[j].HeaderText) + "\t";
        stOutput += sHeaders + "\r\n";
        // Export data.
        for (int i = 0; i < dGV.RowCount - 1; i++)
        {
            string stLine = "";
            for (int j = 0; j < dGV.Rows[i].Cells.Count; j++)
                stLine = stLine.ToString() + Convert.ToString(dGV.Rows[i].Cells[j].Value) + "\t";
            stOutput += stLine + "\r\n";
        }
        Encoding utf16 = Encoding.GetEncoding(1254);
        byte[] output = utf16.GetBytes(stOutput);
        FileStream fs = new FileStream(filename, FileMode.Create);
        BinaryWriter bw = new BinaryWriter(fs);
        bw.Write(output, 0, output.Length); //write the encoded file
        bw.Flush();
        bw.Close();
        fs.Close();
    }
c++sharpdatagridview


Solution 1:[1]

try to export to ExcelXML

Example: 

 using (var writer = new StreamWriter(fileDialog.OpenFile(), Encoding.UTF8))
                    {
                        if (format == "XML")
                        {
                            writer.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
                            writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
                            writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\">");
                            writer.WriteLine("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
                            writer.WriteLine("<Author></Author>");
                            writer.WriteLine("<Created>" + DateTime.Now.ToLocalTime().ToLongDateString() + "</Created>");
                            writer.WriteLine("<LastSaved>" + DateTime.Now.ToLocalTime().ToLongDateString() + "</LastSaved>");
                            writer.WriteLine("<Company></Company>");
                            writer.WriteLine("<Version>12.00</Version>");
                            writer.WriteLine("</DocumentProperties>");

                            if (showGroupHeaders)
                                AddStyles(writer, groupDesc.Count());

                            writer.WriteLine(
                                "<Worksheet ss:Name=\"Silverlight Export\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");
                            writer.WriteLine("<Table>");
                        }

                        writer.Write(text.ToString());
                        if (format == "XML")
                        {
                            writer.WriteLine("</Table>");
                            writer.WriteLine("</Worksheet>");
                            writer.WriteLine("</Workbook>");
                        }

                        writer.Close();
                    }

Where AddStyle Like this:

    private static void AddStyles(StreamWriter writer, int groupCount)
            {
                writer.WriteLine("<Styles>");

                for (int i = 0; i < groupCount; i++)
                {
                    int shift = i % _headerColor.Count();
                    var color = _headerColor[shift];
                    writer.WriteLine("<Style ss:ID=\"st{0}\">", i + 1);
                    writer.WriteLine("<Interior ss:Color=\"#{0:X2}{1:X2}{2:X2}\" ss:Pattern=\"Solid\"/>", color.R, color.G, color.B);
                    writer.WriteLine("</Style>");
                }

                writer.WriteLine("</Styles>");
            }

And Set cell backcolor:

string.Format("<Cell ss:StyleID=\"st{0}\"><Data ss:Type=\"String\">{1}</Data></Cell>", style, data);

Solution 2:[2]

Here is an example using GemBox.Spreadsheet component to work with Excel files and cell formatting.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 august

Related Questions