.net對excler 的匯入匯出功能的實現

zhaohjjq發表於2013-12-21

(沒有元件錯誤)

1.寫前臺頁面

<div id="main">
        <div class="jiu_nav">
         <ul>
            <li><a href="Default.aspx">首頁</a></li>
            <li><a href="typeadmin.aspx">分類管理</a></li>
            <li><a href="alllist.aspx">資料管理</a></li>
            <li><a href="back.aspx">回收站</a> </li>
         </ul>
       </div>
       <div>
         <div>匯入的數字：
             <asp:TextBox ID="txtname" runat="server"></asp:TextBox>
             <asp:Button ID="sub_txt"  runat="server" onclick="sub_txt_Click"/>  </div>
       
       </div>
</div>

2.寫後臺程式碼

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using COM.Excel;
using Microsoft.Office.Interop.Excel;
protected void sub_txt_Click(object sender, EventArgs e)
        {
            int csname = Convert.ToInt32(txtname.Text.ToString());
            Hashtable MyTable = new Hashtable();
            MyTable.Add("Id", "編號");
            MyTable.Add("keywork","關鍵詞");
            MyTable.Add("weburl", "網站");
            MyTable.Add("typename", "型別");
            MyTable.Add("mintime", "時間");
            MyTable.Add("Search" ,"百度收錄量");
            MyTable.Add("Included", "百度搜尋量");
            MyTable.Add("tuiguang","百度推廣");
            MyTable.Add("P_zhishu","P指數");
            //MyTable.Add("DaoChuDate", "匯出時間");
              Maticsoft.Common.DataExcle2 cs = new Maticsoft.Common.DataExcle2();
            if (csname != 0)
            {
                string sql = "select top " + csname + " keywork,typename,Search,Included,weburl,mintime,tuiguang,P_zhishu from keyworks where " + GetStrWhere();
                DataSet rs = conn.runsql(sql);
                Maticsoft.Common.DataExcle2.GridViewToExcel(rs, MyTable, "Excel報表");
            }
            else
            {
            }
        }

        /// 將DataTable的資料匯出顯示為報表
        /// </summary>
        /// <param name="dt">要匯出的資料</param>
        /// <param name="strTitle">匯出報表的標題</param>
        /// <param name="FilePath">儲存檔案的路徑</param>
        /// <returns></returns>
        //     public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath)


        private string GetStrWhere()
        {
            string strwhe = " 1=1 ";

            return strwhe;
        }

3.寫匯出類庫

using System;
using System.Diagnostics;
using System.Collections;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Office.Interop.Excel;

namespace Maticsoft.Common
{
    public class DataExcle2
    {

        public DataExcle2()
        {
        }

        #region 操作EXCEL的一個類(需要Excel.dll支援)

        private int titleColorindex = 15;
        /// <summary>
        /// 標題背景色
        /// </summary>
        public int TitleColorIndex
        {
            set { titleColorindex = value; }
            get { return titleColorindex; }
        }

        private DateTime beforeTime;   //Excel啟動之前時間
        private DateTime afterTime;    //Excel啟動之後時間

        #region 建立一個Excel示例
        /// <summary>
        /// 建立一個Excel示例
        /// </summary>
        public void CreateExcel()
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Cells[1, 1] = "第1行第1列";
            excel.Cells[1, 2] = "第1行第2列";
            excel.Cells[2, 1] = "第2行第1列";
            excel.Cells[2, 2] = "第2行第2列";
            excel.Cells[3, 1] = "第3行第1列";
            excel.Cells[3, 2] = "第3行第2列";

            //儲存
            excel.ActiveWorkbook.SaveAs("tt.xls", XlFileFormat.xlExcel9795, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            //開啟顯示
            excel.Visible = true;
            //   excel.Quit();
            //   excel=null;           
            //   GC.Collect();//垃圾回收
        }
        #endregion

        #region 將DataTable的資料匯出顯示為報表
        /// <summary>
        /// 將DataTable的資料匯出顯示為報表
        /// </summary>
        /// <param name="dt">要匯出的資料</param>
        /// <param name="strTitle">匯出報表的標題</param>
        /// <param name="FilePath">儲存檔案的路徑</param>
        /// <returns></returns>
        public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath)
        {
            beforeTime = DateTime.Now;

            Microsoft.Office.Interop.Excel.Application excel;
            Microsoft.Office.Interop.Excel._Workbook xBk;
            Microsoft.Office.Interop.Excel._Worksheet xSt;

            int rowIndex = 4;
            int colIndex = 1;

            excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;

            //取得列標題   
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                excel.Cells[4, colIndex] = col.ColumnName;

                //設定標題格式為居中對齊
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//設定為淺黃色，共計有56種
            }


            //取得表格中的資料   
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 1;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//設定日期型的欄位格式為居中對齊
                    }
                    else
                        if (col.DataType == System.Type.GetType("System.String"))
                        {
                            excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                            xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//設定字元型的欄位格式為居中對齊
                        }
                        else
                        {
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                }
            }

            //載入一個合計行   
            int rowSum = rowIndex + 1;
            int colSum = 2;
            excel.Cells[rowSum, 2] = "合計";
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            //設定選中的部分的顏色   
            xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//設定為淺黃色，共計有56種

            //取得整個報表的標題   
            excel.Cells[2, 2] = strTitle;

            //設定整個報表的標題格式   
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;

            //設定報表表格為最適應寬度   
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();

            //設定整個報表的標題為跨列居中   
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
            xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenterAcrossSelection;

            //繪製邊框   
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;//設定左邊線加粗
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;//設定上邊線加粗
            xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;//設定右邊線加粗
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;//設定下邊線加粗

 

            afterTime = DateTime.Now;

            //顯示效果   
            //excel.Visible=true;   
            //excel.Sheets[0] = "sss";

            //ClearFile(FilePath);
            string filename = DateTime.Now.ToString("key") + ".xls";
            excel.ActiveWorkbook.SaveAs(FilePath + filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;
            //excel.Save(strExcelFileName);

            #region  結束Excel程式

            //需要對Excel的DCOM物件進行配置:dcomcnfg


            //excel.Quit();
            //excel=null;           

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();


            //注意：這裡用到的所有Excel物件都要執行這個操作，否則結束不了Excel程式
            //   if(rng != null)
            //   {
            //    System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //    rng = null;
            //   }
            //   if(tb != null)
            //   {
            //    System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //    tb = null;
            //   }
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//垃圾回收
            #endregion

            return filename;

        }
        #endregion

        #region Kill Excel程式

        /// <summary>
        /// 結束Excel程式
        /// </summary>
        public void KillExcelProcess()
        {
            Process[] myProcesses;
            DateTime startTime;
            myProcesses = Process.GetProcessesByName("Excel");

            //得不到Excel程式ID，暫時只能判斷程式啟動時間
            foreach (Process myProcess in myProcesses)
            {
                startTime = myProcess.StartTime;
                if (startTime > beforeTime && startTime < afterTime)
                {
                    myProcess.Kill();
                }
            }
        }
        #endregion

        #endregion

        #region 將DataTable的資料匯出顯示為報表(不使用Excel物件，使用COM.Excel)

        #region 使用示例
        public static void GridViewToExcel(DataSet MyData, Hashtable nameList, string ReportTitle)
        {
            string FilePath = System.Web.HttpContext.Current.Server.MapPath("./");
            //利用excel物件
            DataExcle2 dte = new DataExcle2();
            int a= MyData.Tables[0].Rows.Count;
            string filename = "";
            try
            {
                if (MyData.Tables[0].Rows.Count > 0)
                {
                    filename =dte.DataExcel(MyData.Tables[0], ReportTitle, FilePath, nameList);
                }
            }
            catch (Exception e)
            {
                throw new Exception();
            }
            if (filename != "")
            {
                System.Web.HttpContext.Current.Response.Redirect(filename, true);
            }
        }

        #endregion

        /// <summary>
        /// 將DataTable的資料匯出顯示為報表(不使用Excel物件)
        /// </summary>
        /// <param name="dt">資料DataTable</param>
        /// <param name="strTitle">標題</param>
        /// <param name="FilePath">生成檔案的路徑</param>
        /// <param name="nameList"></param>
        /// <returns></returns>
        public string DataExcel(System.Data.DataTable dt, string strTitle, string FilePath, Hashtable nameList)
        {
            COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile();
            //ClearFile(FilePath);
            string filename = "key.xls";
            excel.CreateFile(FilePath + filename);
            excel.PrintGridLines = false;

            COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
            COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
            COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
            COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;

            double height = 1.5;
            excel.SetMargin(ref mt1, ref height);
            excel.SetMargin(ref mt2, ref height);
            excel.SetMargin(ref mt3, ref height);
            excel.SetMargin(ref mt4, ref height);

            COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
            string font = "宋體";
            short fontsize = 9;
            excel.SetFont(ref font, ref fontsize, ref ff);

            byte b1 = 1,
                b2 = 12;
            short s3 = 12;
            excel.SetColumnWidth(ref b1, ref b2, ref s3);

            string header = "頁首";
            string footer = "頁尾";
            excel.SetHeader(ref header);
            excel.SetFooter(ref footer);


            COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText;
            COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0;
            COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
            COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;

            // 報表標題
            int cellformat = 1;
            //   int rowindex = 1,colindex = 3;     
            //   object title = (object)strTitle;
            //   excel.WriteValue(ref vt, ref cf, ref ca, ref chl,ref rowindex,ref colindex,ref title,ref cellformat);

            int rowIndex = 1;//起始行
            int colIndex = 0;

 

            //取得列標題    
            foreach (DataColumn colhead in dt.Columns)
            {
                colIndex++;
                string name = colhead.ColumnName.Trim();
                object namestr = (object)name;
                IDictionaryEnumerator Enum = nameList.GetEnumerator();
                while (Enum.MoveNext())
                {
                    if (Enum.Key.ToString().Trim() == name)
                    {
                        namestr = Enum.Value;

                    }
                }
                excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat);
            }

            //取得表格中的資料

            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        object str = null;
                        str = (object)(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        //try
                        //{
                        //    str = (object)(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        //}
                        //catch
                        //{
                        //    str = (object)"";
                        //}


                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                    }
                    else
                    {
                        object str = null;
                        if (col.ColumnName == "TypeId" || col.ColumnName == "Type2")
                        {

                            //comm conn = new comm();
                            //str = (object)conn.runsql(" select * from keywork where Id=" + row[col.ColumnName].ToString() + "");
                        }
                        else
                        {
                            str = (object)row[col.ColumnName].ToString();
                        }
                        //object str = (object)row[col.ColumnName].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                    }

                }
                //更改狀態，說明一條資訊只能匯出一次
                // ranbing.DBUtility.DbHelperSQL.ExecuteSql("update ChanPin set Status=1,DaoChuDate='" + DateTime.Now.ToString() + "' where Id=" + row["Id"].ToString());
            }
            int ret = excel.CloseFile();

            //   if(ret!=0)
            //   {
            //    //MessageBox.Show(this,"Error!");
            //   }
            //   else
            //   {
            //    //MessageBox.Show(this,"請開啟檔案c:\\test.xls!");
            //   }
            return filename;

        }

        #endregion

        #region  清理過時的Excel檔案

        //private void ClearFile(string FilePath)
        //{
        //    String[] Files = System.IO.Directory.GetFiles(FilePath);
        //    if (Files.Length > 10)
        //    {
        //        for (int i = 0; i < 10; i++)
        //        {
        //            try
        //            {
        //                System.IO.File.Delete(Files[i]);
        //            }
        //            catch
        //            {
        //            }

        //        }
        //    }
        //}
        #endregion
    }
}

 


 

相關文章