自定义样式导出EXCEL

 

 void ExportExcel()
        {
            DataGrid outGrid = new DataGrid();

            outGrid.DataSource = ExportTable();
            outGrid.DataBind();

            outGrid.HeaderStyle.BackColor = Color.Yellow;
            outGrid.HeaderStyle.Font.Bold = true;
            outGrid.ItemStyle.HorizontalAlign = HorizontalAlign.Right;

            //格式可以通过Excel另存html的style获取
            foreach (DataGridItem item in outGrid.Items)
            {
                item.Cells[0].Style.Add("mso-number-format", "\\[ENG\\]\\[$-409\\]dd\\\\-mmm");//m/d/yyyy date format
                item.Cells[6].Style.Add("mso-number-format", "\\@");//text format
            }

            DataGridToExcel(outGrid, Response);
        }

        DataTable ExportTable()
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("Date");
            dt.Columns.Add("Court");
            dt.Columns.Add("Serial");
            dt.Columns.Add("Inventory");
            dt.Columns.Add("Initials");
            dt.Columns.Add("Type");
            dt.Columns.Add("Page");
            dt.Columns.Add("Error");
            dt.Columns.Add("Comments");

            foreach (DataRow r in ds_.Tables[0].Rows)
            {
                DataRow dn = dt.NewRow();

                dn[0] = r[0].ToString();
                dn[1] = r[1].ToString();
                dn[2] = r[2].ToString();
                dn[3] = r[3].ToString();
                dn[4] = r[4].ToString();
                dn[5] = r[5].ToString();
                dn[6] = r[6].ToString();
                dn[7] = r[7].ToString();
                dn[8] = r[8].ToString();

                dt.Rows.Add(dn);
            }

            return dt;
        }

        //从IE上导出保存的方式
        void DataGridToExcel(DataGrid grd, HttpResponse response)
        {
            foreach (DataGridItem item in grd.Items)
            {
                foreach (TableCell cell in item.Cells)
                    PrepareControlForExport(cell);
            }



            response.Clear();
            response.Buffer = true;
            response.Charset = "utf-8";
            response.AppendHeader("Content-Disposition", "attachment;filename=NewFileName.xls");
            response.ContentEncoding = System.Text.Encoding.Default;
            response.ContentType = "application/ms-excel";


            System.Globalization.CultureInfo AreaInfo = new System.Globalization.CultureInfo("en-US", true);
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter(AreaInfo);
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            grd.GridLines = GridLines.Both;
            grd.Font.Name = "Arial";
            grd.Font.Size = 10;
            grd.HeaderStyle.Font.Bold = true;
            grd.RenderControl(oHtmlTextWriter);
            response.Write(oStringWriter.ToString());


            response.End();
        }


        //保存为本地文件的方式（这个自由发挥的空间较大）
        void SaveGridViewToExcel(GridView grd, string filename)
        {

            System.IO.StringWriter sw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
            grd.RenderControl(htw);
            File.WriteAllText(filename, sw.ToString());

        }

 

private static void PrepareControlForExport(Control control)
        {
            for (int i = 0; i < control.Controls.Count; i++)
            {
                Control current = control.Controls[i];
                if (current is LinkButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                }
                else if (current is ImageButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                }
                else if (current is HyperLink)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
                }
                else if (current is DropDownList)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                }
                else if (current is CheckBox)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
                }

                if (current.HasControls())
                {
                    PrepareControlForExport(current);
                }
            }
        }