導出excel的方法匯總「winform導出excel設置格式」

1. 服務端輸出方式

1.1 html 輸出方式最常用方式

優點：不需要任何插件

缺點：不是真正excel文件，做不了太複雜的格式，偶爾會出現打不開的情況

代碼示例：

            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "GB2312";
            //設置了類型為中文防止亂碼的出現
            string danwei = Session["danWeiMingCheng"].ToString();
            //2011.07.14 luochengxin
            Response.AddHeader("Content-Disposition", "inline;filename=手術登記查詢.xls", System.Text.Encoding.UTF8));
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            //設置輸出流為簡體中文
            Response.ContentType = "application/ms-excel";
            //設置輸出文件類型為excel文件。
            System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            Response.Write(Sbr.ToString());
            Response.Write("<meta http-equiv=Content-Type content=text/html;charset=gb2312>");
            Response.Write(oStringWriter.ToString());
            Response.End();

1.2 office com組件 輸出方式

優點：生成真正的Excel文件，強類型編碼

缺點：服務器需要安裝office且與版本綁定太深（不推薦使用），常用cs類型程序開發

代碼示例：

protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                ApplicationClass app = new ApplicationClass();
                Workbook workBook = app.Workbooks.Open(@"e:\tt.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                Worksheet sheet = (Worksheet)workBook.Worksheets[1];
                Response.Write(sheet.Name + "<br/>");
                Range range = sheet.get_Range("E7:J112", Type.Missing);
                Range item = null;
                for (int row = 0; row < range.Rows.Count; row++)
                {
                    for (int col = 0; col < range.Columns.Count; col++)
                    {
                        item = (Range)range.get_Item(row, col);
                        Response.Write(item.Value2);
                        Response.Write(" | ");
                    }
                    Response.Write("<br/>");
                }
                //關閉
                app.Workbooks.Close();
                app.Quit();
                //釋放
                System.Runtime.InteropServices.Marshal.ReleaseComObject(item);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                item = null;
                app = null;
                workBook = null;
                range = null;

                GC.Collect();
                //殺進程
                try{
                KillExcelProcess();
                }catch{}
            }
        }

        public void KillExcelProcess()
        {
            Process[] myProcesses;

            myProcesses = Process.GetProcessesByName("Excel");

            foreach (Process myProcess in myProcesses)
            {
                myProcess.Kill();
            }
        }

1.3 NPOI 輸出方式

優點：生成真正的Excel文件，可做特殊複雜操作，且為獨立第三方DLL，不需要依然office組件解決了1.2的問題，

缺點：生成時佔用內存資源大，CPU耗時大，如果生成一萬條以上excel極其容易出現內存溢出

代碼示例：

            private void button1_Click(object sender, EventArgs e)
            {
            	//創建工作薄
            	HSSFWorkbook wk = new HSSFWorkbook();
            	//創建一個名稱為mySheet的表
            	ISheet tb = wk.CreateSheet("mySheet"); 
            	//創建一行，此行為第二行
            	IRow row = tb.CreateRow(1);
            	for (int i = 0; i < 20; i++)    
            	{
            		ICell cell = row.CreateCell(i);  //在第二行中創建單元格
            		cell.SetCellValue(i);//循環往第二行的單元格中添加數據
            	}
            	//打開一個xls文件，如果沒有則自行創建，如果存在myxls.xls文件則在創建是不要打開該文件！
            	using (FileStream fs = File.OpenWrite(@"c:/myxls.xls")) 
            	{
            		wk.Write(fs);   //向打開的這個xls文件中寫入mySheet表並保存。
            		MessageBox.Show("提示：創建成功！");
            	}
            }

2. 客戶端輸出方式

2.1 OCX組件導出

優點：生成真正的Excel文件

缺點：僅IE下可用，需要開啟activeX權限

代碼示例：

       function AllAreaExcel(content) {
            var oXL = new ActiveXObject("Excel.Application");
            var oWB = oXL.Workbooks.Add();
            var oSheet = oWB.ActiveSheet;
            var div_all = content;
            var sel = document.body.createTextRange();
            sel.moveToElementText(div_all);
            sel.select();
            sel.execCommand("Copy");
            oSheet.Paste();
            oXL.Visible = true;
        }

2.2 第三方JS插件

優點：

缺點：僅標準H5可用，IE兼容模式下不可能用