• Home
 · 
• Miscellaneous
 · 
• Export DataGridView to Excel

Export DataGridView to Excel

Export DataGridView to Excel

Rate Export DataGridView to Excel

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

        {
            System.IO.StreamWriter excelDoc;
            excelDoc = new System.IO.StreamWriter(fileName);
            const string startExcelXML = "<xml version>\r\n<Workbook " + 
                "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" + 
                " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " + 
                "xmlns:x=\"urn:schemas-    microsoft-com:office:" + 
                "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" + 
                "office:spreadsheet\">\r\n <Styles>\r\n " + 
                "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " + 
                "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" + 
                "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" + 
                "\r\n <Protection/>\r\n </Style>\r\n " + 
                "<Style ss:ID=\"BoldColumn\">\r\n <Font " + 
                "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " + 
                "<Style     ss:ID=\"StringLiteral\">\r\n <NumberFormat" + 
                " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " + 
                "ss:ID=\"Decimal\">\r\n <NumberFormat " + 
                "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " + 
                "<Style ss:ID=\"Integer\">\r\n <NumberFormat " + 
                "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " + 
                "ss:ID=\"DateLiteral\">\r\n <NumberFormat " + 
                "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " + 
                "</Styles>\r\n ";
            const string endExcelXML = "</Workbook>";

            int rowCount = 0;
            int sheetCount = 1;
             
            excelDoc.Write(startExcelXML);
            excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
            excelDoc.Write("<Table>");
            excelDoc.Write("<Row>");
            for(int x = 0; x <= dg.Columns.Count -1 ; x++)
            {
                excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                excelDoc.Write(dg.Columns[x].HeaderText);
                excelDoc.Write("</Data></Cell>");
            }
            excelDoc.Write("</Row>");
            for (int i = 0; i <= dg.Rows.Count - 1; i++)
            {
                rowCount++;
                //if the number of rows is > 64000 create a new page to continue output
                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                    excelDoc.Write("<Table>");
                }
                excelDoc.Write("<Row>"); //ID=" + rowCount + "
                for (int y = 0; y <= dg.Columns.Count - 1; y++)
                {
                    if (dg.Rows[i].Cells[y].Value != null)
                    {
                        string XMLstring = dg.Rows[i].Cells[y].Value.ToString();
                        XMLstring = XMLstring.Trim();
                        XMLstring = XMLstring.Replace("&", "&");
                        XMLstring = XMLstring.Replace(">", ">");
                        XMLstring = XMLstring.Replace("<", "<");
                        excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                       "<Data ss:Type=\"String\">");
                        excelDoc.Write(XMLstring);
                        excelDoc.Write("</Data></Cell>");
                    }
                    else
                    {
                        excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                        "<Data ss:Type=\"String\">");
                        excelDoc.Write("");
                        excelDoc.Write("</Data></Cell>");
                    }
                }
                    excelDoc.Write("</Row>");
               }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
            excelDoc.Write(endExcelXML);
            excelDoc.Close();
        }