Gridview导出excel

Gridview导出excel，在office 2003时代普遍用的都是

public void exportToExcel(string sFileName)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "utf-8";

            Response.AppendHeader("Content-Disposition", "attachment;filename=" + sFileName);
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");

            Response.ContentType = "application/ms-excel";
            this.EnableViewState = false;

            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

            this.GridView1.EnableViewState = false;

            GridView1.RenderControl(oHtmlTextWriter);

            Response.Write(oStringWriter.ToString());

            Response.End();

        }

 

导出html，但是以excel形式打开。

 

但是在客户端升级到office 2007以后，继续采用这种方法导出的文件，打开时会有如下提示

 

 

 

试了一下其它方式，ms改成csv格式导出是最省事的，但是格式完全丢失。最后自己写了段代码，把html导出到server端然后转存成xls，格式和各类信息都没有问题。代码量也不是太大。不过需要在server端安装office 2003，性能也正在确认中。先放代码记录一个。

 

using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

  protected void Button2_Click(object sender, EventArgs e)
        {
            gen_svr_file();

        }

        public void gen_svr_file()
        {
            //Get random number for file name
            System.Random a = new Random(System.DateTime.Now.Millisecond);
     int RandKey = a.Next(100000);

            //Generate html file
            string str_gridview = readgridview();

            string strfolderpath = Server.MapPath("//");
        
            // Create the FileStream and StreamWriter object to write
            // Create html and excel files
            string strFileName_html = strfolderpath+"//temp "+ RandKey.ToString()+".html";
            string strFileName_xls = strfolderpath+"//export "+ RandKey.ToString()+".xls";

            System.IO.FileStream fs = new System.IO.FileStream(
                strFileName_html, System.IO.FileMode.Create);
            System.IO.StreamWriter sw = new System.IO.StreamWriter(
                fs, System.Text.Encoding.Unicode);

            sw.WriteLine(str_gridview);

            sw.Flush(); // Write the buffered data to the filestream.

            // Close the FileStream.
            fs.Close();

            Microsoft.Office.Interop.Excel.Application m_objExcel = null;
            Workbooks m_objBooks = null;
            _Workbook m_objBook = null;
            Sheets m_objSheets = null;
            _Worksheet m_objSheet = null;

            // Frequenty-used variable for optional arguments.
            object m_objOpt = System.Reflection.Missing.Value;

            // Open the text file in Excel.
            m_objExcel = new Microsoft.Office.Interop.Excel.Application();
            m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;

            m_objBooks.OpenText(strFileName_html, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 1,
                Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, true, false, false, false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

            m_objBook = m_objExcel.ActiveWorkbook;

            m_objSheets = (Sheets)m_objBook.Worksheets;
            m_objSheet = (_Worksheet)(m_objSheets.get_Item(1));
            m_objSheet.Name = "testing";

            // Save the text file in the typical workbook format and quit Excel.
            m_objBook.SaveAs(strFileName_xls, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                m_objOpt, m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt,
                m_objOpt, m_objOpt, m_objOpt);

            System.IO.File.Delete(strFileName_html);
            m_objBook.Close(false, m_objOpt, m_objOpt);
            //m_objExcel.Quit();
            //m_objExcel = null;
           
            //kill excel process
            KillSpecialExcel(m_objExcel);

            GC.Collect();
            GC.WaitForPendingFinalizers();  
           
            //Export file stream to end user
            exp_excel_toUser(strFileName_xls);
        }

 

        public string readgridview()
        {
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            GridView1.RenderControl(oHtmlTextWriter);
            string strhtml = oStringWriter.ToString();

            return strhtml;
        }

 

        public void del_old_file(string strFileName_html, string strFileName_xls)
        {
           
            string strhtmlpath = strFileName_html;
            string strxlspath = strFileName_xls;

            if(File.Exists(strhtmlpath))
            {
                File.Delete(strhtmlpath);
            }

            if (File.Exists(strxlspath))
            {
                File.Delete(strxlspath);
            }
        }

        public void exp_excel_toUser(string path)
        {
            try
            {
                Response.ContentType = "application/octet-stream";

                Response.AppendHeader("Content-Disposition", "attachment;filename=" +
                HttpUtility.UrlEncode("Export.xls", System.Text.Encoding.UTF8) + ";charset=GB2312");
                Response.ContentType = "application/ms-excel";
                System.IO.FileStream fs = System.IO.File.OpenRead(path);
                long fLen = fs.Length;
                int size = 102400     
                byte[] readData = new byte[size];     
                if (size > fLen) size = Convert.ToInt32(fLen);
                long fPos = 0;
                bool isEnd = false;
                while (!isEnd)
                {
                    if ((fPos + size) > fLen)
                    {
                        size = Convert.ToInt32(fLen - fPos);
                        readData = new byte[size];
                        isEnd = true;
                    }
                    fs.Read(readData, 0, size);   
                    Response.BinaryWrite(readData);
                    fPos += size;
                }
                fs.Close();
                //Delete html and excel files
                del_old_file(strFileName_html, strFileName_xls);
                Response.End();
            }
            catch
            {
               
            }

        }

        [DllImport("user32.dll", SetLastError = true)]
        static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);

        public void KillSpecialExcel(Microsoft.Office.Interop.Excel.Application m_objExcel)
        {
            try
            {
                if (m_objExcel != null)
                {
                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(m_objExcel.Hwnd), out lpdwProcessId);

                    System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Delete Excel Process Error:" + ex.Message);
            }
        }