ASP.NET GridView to Excel(GridView輸出到Excel)

ASP.NET GridView to Excel(GridView輸出到Excel)

C#:

protected void Page_Load(object sender, EventArgs e)
{
        DataTable dt = new DataTable();
        GridView1.DataSource = dt;
        GridView1.DataBind();

        #region HMTL Response
        Response.Clear();
        Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
        Response.AddHeader("content-disposition", "attachment;filename=" + strExportFilename + ".xls");
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = "application/vnd.xls";
        //Response.Charset = "big5";  //utf-8
        Response.Charset = "utf-8";  //utf-8
        //Response.ContentEncoding = System.Text.Encoding.GetEncoding(950); //65001
        Response.ContentEncoding = System.Text.Encoding.GetEncoding(65001); //65001

        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        GridView1.RenderControl(htmlWrite); //將物件GridView1輸出
        //Table1.RenderControl(htmlWrite); //將物件Table1輸出
        Response.Write(stringWrite.ToString().Replace("<div>", "").Replace("</div>", ""));
        Response.End();
        #endregion
}

ASP.NET DataTable轉成Excel實體檔案

ASP.NET DataTable轉成Excel實體檔案


C#:

    protected void Button1_Click(object sender, EventArgs e)
    {
        #region 建立虛擬表格
        DataTable dt = new DataTable();
        DataColumn column;
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "company_name";
        dt.Columns.Add(column);
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "tel";
        dt.Columns.Add(column);
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "fax";
        dt.Columns.Add(column);
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "email";
        dt.Columns.Add(column);
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "contact";
        dt.Columns.Add(column);
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "company_address";
        dt.Columns.Add(column);
        #endregion

        //輸出
        ExcelFileStream(dt, @"D:\", "abc.xls");
    }

    private void ExcelFileStream(DataTable dt, string savepath, string filename)
    {
        try
        {
            Stream fs = RenderDataTableToExcel(dt);
            SaveStreamToFile(savepath + filename, fs);
        }
        catch (Exception)
        {
            throw;
        }
    }

    public static Stream RenderDataTableToExcel(DataTable srcTable)
    {
        try
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
            foreach (DataColumn column in srcTable.Columns)
            {
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            }
            int rowIndex = 1;
            foreach (DataRow row in srcTable.Rows)
            {
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in srcTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }
                rowIndex++;
            }

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            stream.Flush();
            stream.Position = 0;
            sheet = null;
            headerRow = null;
            workbook = null;
            return stream;
        }
        catch (Exception)
        {

            throw;
        }

    }

    public void SaveStreamToFile(string fileFullPath, Stream stream)
    {
        try
        {
            if (stream.Length == 0) return;

            // Create a FileStream object to write a stream to a file
            using (FileStream fileStream = System.IO.File.Create(fileFullPath, (int)stream.Length))
            {
                // Fill the bytes[] array with the stream data
                byte[] bytesInStream = new byte[stream.Length];
                stream.Read(bytesInStream, 0, (int)bytesInStream.Length);

                // Use FileStream object to write to the specified file
                fileStream.Write(bytesInStream, 0, bytesInStream.Length);
            }
        }
        catch (Exception)
        {
            throw;
        }

    }