程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> .NET網頁編程 >> C# >> C#入門知識 >> C#向excel中寫入數據的三種方式

C#向excel中寫入數據的三種方式

編輯:C#入門知識

 

第一種:將DataGrid中的數據以流的形式寫到excel中,格式以html的形式存在

 

            Response.Clear();

            Response.Buffer = true;

            Response.Charset = "GB2312";

            Response.AppendHeader("Content-Disposition", "attachment;filename=DialoutTemplate.xls");

 

            // 如果設置為GetEncoding("GB2312");導出的文件將會出現亂碼!!!

            Response.ContentEncoding = System.Text.Encoding.UTF8;

            Response.ContentType = "application/ms-excel";//設置輸出文件類型為excel文件。

 

 

            //Response.ContentType = "application/vnd.ms-excel";//輸出類型

            //Response.Charset = "";

 

            //關閉ViewState

            EnableViewState = false;

            System.IO.StringWriter tw = new System.IO.StringWriter();//將信息寫入字符串

            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);//在WEB窗體頁上寫出一系列連續的HTML特定字符和文本。

            //此類提供ASP.NET服務器控件在將HTML內容呈現給客戶端時所使用的格式化功能

            //獲取control的HTML

 

            dg.RenderControl(hw);//將table中的內容輸出到HtmlTextWriter對象中

 

            // 把HTML寫回浏覽器

            Response.Write(tw.ToString());

            Response.Flush();

            Response.End();

 

 

 

第二種:將數據源中的數據以文件流的形式寫到excel中,格式以txt的形式存在

 

              FileStream fs = new FileStream(Server.MapPath("report_export/DialoutTemplate.xls"), FileMode.Create, FileAccess.Write);

            StreamWriter rw = new StreamWriter(fs, Encoding.Default);//建立StreamWriter為寫作准備;

 

            DataTable dt = GetDataTableSource();

 

            int count = dt.Columns.Count;

            string head = "";

            string values = "";

 

            for (int i = 0; i < count; i++)

            {

                string h = dt.Columns[i].ColumnName + "\t";

                string v = dt.Rows[0][i].ToString() + "\t";

 

                head += h;

                values += v;

            }

            rw.WriteLine(head);

            rw.WriteLine(values);

 

            rw.Close();

            fs.Close();

 

            Response.Redirect("report_export/DialoutTemplate.xls");

 

 

 

第三種:將數據源中的數據直接寫到excel中,格式以xls形式存在,好處導出的

 

               數據可以直接導入,可以將數字格式自動轉化為文本格式,可以減少

 

               格式轉化的繁瑣環節,還可以預留將數字轉換為文本的格式的行數,

 

               可以完全自定義

 

 

 

            Excel.Application xlApp;

            Excel.Workbook xlBook;

            Excel.Workbooks xlBooks;

            //Excel.Range xlRange;

            Excel.Sheets xlsheets;

            Excel.Worksheet xlSheet;

            int k = 0;

            try

            {

                string strCurrentPath = Server.MapPath("report_export/DialoutTemplate.xls");

                string FilePath = strCurrentPath;

 

                FileInfo fi = new FileInfo(FilePath);

                if (fi.Exists)     //判斷文件是否已經存在,如果存在就刪除!

                {

                    fi.Delete();

                }

 

                xlApp = new Excel.Application();

                xlBooks = xlApp.Workbooks;

                xlBook = xlBooks.Add(Type.Missing);

                xlsheets = xlBook.Worksheets;

                IntPtr intptr = new IntPtr(xlApp.Hwnd);

                xlSheet = (Excel.Worksheet)xlsheets.get_Item(1);

 

                DataTable dt = GetDataTableSource();

 

                int count = dt.Columns.Count;

                for (int i = 0; i < count; i++)

                {

                    string h = dt.Columns[i].ColumnName;

                    string v = dt.Rows[0][i].ToString();

 

                    ((Excel.Range)xlSheet.Cells[1, i + 1]).Value2 = h;

                    Excel.Range r1 = xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, i + 1]);

                    r1.NumberFormatLocal = "@";

                    ((Excel.Range)xlSheet.Cells[2, i + 1]).Value2 = v;

 

                    Excel.Range r2 = xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[2, i + 1]);

                    r2.NumberFormatLocal = "@";

                }

 

                for (int j = 1; j < 500; j++)

                {

                    Excel.Range r = xlSheet.get_Range(xlSheet.Cells[2 + j, 1], xlSheet.Cells[2 + j, count]);

                    r.NumberFormatLocal = "@";

                }

 

                xlBook.SaveAs(FilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                xlBook.Close(false, Type.Missing, Type.Missing);

                xlBooks.Close();

                xlApp.Quit();

                Response.Redirect("report_export/DialoutTemplate.xls");

                GetWindowThreadProcessId(intptr, out k);

                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);

                p.Kill();

 

            }

            catch (Exception ex)

            {

                Response.Write(ex.Message);

            }

            finally

            {

                //xlRange = null;

                xlSheet = null;

                xlBook = null;

                xlApp = null;

            }

 

  摘自 飛哥的專欄

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved