C#dataset中資料匯出到excel

標籤：

  
 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Reflection;
using Microsoft.Office;
//using Excel = Microsoft.Office.Interop.Excel;
using Excel;

/// <summary>
///InOutInfoToExcel 的摘要說明
/// </summary>
public class InOutInfoToExcel
{
 public InOutInfoToExcel()
 {
  //
  //TODO: 在此處添加建構函式邏輯
  //
 }

    /// <summary>
    /// 匯出excel單個sheet（匯出的excel檔案的名字只能為英文，中文的話名字是亂碼  且只能匯出一個sheet）
    /// </summary>
    /// <param name="ds"></param>
    /// <param name="FileName"></param>
    public void ToExcel(DataSet ds, string FileName)
    {
        //HttpContext.Current.Response.Charset = "UTF-8";
        HttpContext.Current.Response.Charset = "GB2312";
        //HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;//設定輸出資料流為簡體中文
        System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        HttpContext.Current.Response.ContentType = "application/ms-excel";//設定輸出檔案類型為excel檔案
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName);
        //設定數字為文字格式設定
        string strStyle = "<style>td{mso-number-format:\"\\@\";}</style>";
        System.IO.StringWriter tw = new System.IO.StringWriter();//定義StringWriter輸出對象
        HtmlTextWriter hw = new HtmlTextWriter(tw);//定義HtmlTextWriter對象
        //ctl.RenderControl(hw);//用RenderControl方法輸出excel
        //DataGrid dg = new DataGrid();
        //dg.DataSource = ds.Tables[0];
        //dg.DataBind();
        //dg.RenderControl(hw);
        hw.WriteLine(strStyle);
        GridView gv = new GridView();
        gv.DataSource = ds.Tables[0];
        gv.DataBind();
        gv.RenderControl(hw);
        HttpContext.Current.Response.Write(tw.ToString());//輸出
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.End();
    }
        /// <summary>
    ///匯出Excel檔案 （多個sheet）
    /// </summary>
    /// <param name="dv">用於匯出的DataSET[數組]</param>
    /// <param name="tmpExpDir">匯出的檔案夾的虛擬路徑（在程式裡建一個檔案夾匯出到此檔案夾中）/</param>
    /// <param name="refFileName">檔案名稱，例如test.xls</param>
    /// <param name="sheetName">Sheet的名稱，如果匯出多個Sheet[數租]</param>
    /// <param name="sheetSize">每個Sheet包含的資料行數，此數值不包括標題列。所以，對於65536行資料，請將此值設定為65535</param>
    /// <param name="setBorderLine">匯出完成後，是否給資料加上邊框線</param>  
    public bool WebExportToExcel_1(DataSet[] dv, string tmpExpDir, string refFileName, string[] sheetName, int sheetSize, bool setBorderLine)
    {
        int RowsToDivideSheet = sheetSize;//計算Sheet行數
        int sheetCount = dv.Length;
        GC.Collect();// 回收其他的垃圾
        Application excel; _Workbook xBk; _Worksheet xSt = null;

        excel = new Excel.ApplicationClass(); xBk = excel.Workbooks.Add(true);
        //申明迴圈中要使用的變數 
        int dvRowStart = 0;
        int dvRowEnd; int rowIndex = 0; int colIndex = 0;
        //對全部Sheet進行操作
        for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
        {
            rowIndex = 1; colIndex = 1; //設定初始化的行和列
            //計算起始行
            dvRowStart = 1; //sheetIndex * RowsToDivideSheet;
            //計算結束行
            dvRowEnd = RowsToDivideSheet; //dvRowStart + RowsToDivideSheet - 1;
            if (dvRowEnd > dv[sheetIndex].Tables[0].Rows.Count)
            { dvRowEnd = dv[sheetIndex].Tables[0].Rows.Count + 1; }
            //建立一個Sheet 
            if (null == xSt)
            { xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); }
            else { xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing); }
            //設定SheetName
            // xSt.Name = null;
            xSt.Name = sheetName[sheetIndex].ToString();
            //if (sheetCount > 1)
            //{ xSt.Name += "1"; }
            //取得標題 
            foreach (DataColumn col in dv[sheetIndex].Tables[0].Columns)
            { //設定標題格式
                xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
                //設定標題置中對齊
                xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;
                //填值，並進行下一列
                excel.Cells[rowIndex, colIndex++] = col.ColumnName;
            }
            //取得DATASET表格中資料
            //int drvIndex;
            //for (drvIndex = dvRowStart; drvIndex <= dvRowEnd-1; drvIndex++)
            //{
            //    //新起一行，目前的儲存格移至行首
            //    rowIndex++;
            //    colIndex = 1;
            //    for (int i = 1; i <= dv[sheetIndex].Tables[0].Columns.Count; i++)
            //    {
            //        string aa = dv[sheetIndex].Tables[0].Rows[0][i-1].ToString();
            //        excel.Cells[rowIndex, colIndex] = "‘" + aa + "";
            //        colIndex++;
            //    }
            //}
            //以下代碼就是經過修正後的。上面注釋的代碼有問題。
            foreach (DataRow dr in dv[sheetIndex].Tables[0].Rows)
            {
                //新起一行，目前的儲存格移至行首
                rowIndex++;
                colIndex = 1;
                for (int i = 1; i <= dv[sheetIndex].Tables[0].Columns.Count; i++)
                {
                    string aa = dr[i - 1].ToString();
                    excel.Cells[rowIndex, colIndex] = "‘" + aa + "";
                    colIndex++;
                }
            }
            Excel.Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex]);
            allDataWithTitleRange.Select();
            allDataWithTitleRange.Columns.AutoFit();
            if (setBorderLine)
            { allDataWithTitleRange.Borders.LineStyle = 1; }

        }//Sheet迴圈結束
        string absFileName = HttpContext.Current.Server.MapPath(System.IO.Path.Combine(tmpExpDir, refFileName));
        xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null);
        excel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
        xBk = null; excel = null; xSt = null; GC.Collect();
        return true;
    }
}

C#dataset中資料匯出到excel