数据导出excel文件

1、Response输出文件。

Feli.Data.Stock.CheckStockSubDAO objCheckStockSubDAO = new Feli.Data.Stock.CheckStockSubDAO();
Feli.Data.Stock.CheckStockSubCollection objCheckStockSubCol = objCheckStockSubDAO.GetList();

string strPath = Server.MapPath("..//Excel//CheckStock");
string strName = strPath + "//" + "CheckStock" + "_" + strChkNo + ".xls";

System.IO.FileStream  fs = new System.IO.FileStream( strName, System.IO.FileMode.Create,System.IO.FileAccess.Write );   
System.IO.StreamWriter sw = new System.IO.StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312"));

sw.WriteLine(@"商品编号" + "/t" + "商品名称" + "/t" + "可出库数" + "/t" + "单价");   

foreach(Feli.Data.Stock.CheckStockSub objCheckStockSub in objCheckStockSubCol)
{
    sw.WriteLine( objCheckStockSub.GdCd + "/t" + objCheckStockSub.GdName + "/t" +
                  objCheckStockSub.StkQty.ToString() + objCheckStockSub.SalePrc.ToString());   
}

sw.Close();   
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(strName));     
Response.ContentType = "application/msexcel";//指定返回的是一个不能被客户端读取的流，必须被下载   
Response.WriteFile(strName);  //把文件流发送到客户端   
Response.End();

 

2、使用MS的组件和服务（导出真正的excel文件）

System.Data.DataTable dt = CreateExcelTable();
            if (dt.Rows.Count <= 0)
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script language=javascript>alert('没有任何可导出的内容')</script>");
                return;
            }

            Application Excel;
            int rowIndex = 2;
            int colIndex = 0;

            _Workbook xBk;
            _Worksheet xSt;

            Excel = new ApplicationClass();

            xBk = Excel.Workbooks.Add(true);

            xSt = (_Worksheet)xBk.ActiveSheet;

            //打印横向
            xSt.PageSetup.Orientation = XlPageOrientation.xlLandscape;
            //第一行特殊样式
            xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[1, 26]).MergeCells = true;//合并单元格
            xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[3, 26]).Borders.LineStyle = 1;//加边框
            xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[1, 26]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置居中对齐
            xSt.get_Range(Excel.Cells[1, 1], Excel.Cells[1, 26]).Font.Size = 18;//设置字体

            Excel.Cells[1, 1] = "人才夹：" + ddlHeroFolder.SelectedItem.Text;

            //取得标题
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                Excel.Cells[2, colIndex] = col.ColumnName;
                xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignLeft;//设置标题格式为居中对齐
                xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Bold = true;
                //xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Italic = Convert.ToBoolean(this.chkIta.Checked);
                xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Size = 12;
                //xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Font.Name = this.ddlFont.SelectedValue;
                //xSt.get_Range(Excel.Cells[2, colIndex], Excel.Cells[2, colIndex]).Width = 150;
            }
            //取得表格中的数据 
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;

                    Excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                    xSt.get_Range(Excel.Cells[rowIndex, colIndex], Excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐

                    Range tempRange = xSt.get_Range(xSt.Cells[rowIndex, 26], xSt.Cells[rowIndex, 27]);
                    string strHyperlinks = row["查看简历"].ToString();
                    xSt.Hyperlinks.Add(tempRange, strHyperlinks, "", "", "查看简历");
                }
            }
            xSt.Columns.AutoFit();
            Excel.Visible = true;

            //IO
            xBk.SaveCopyAs(Server.MapPath(".") + "//" + "FileName" + ".xls");

            xBk.Close(false, null, null);

            Excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel);

            xBk = null;
            xSt = null;
            Excel = null;
            GC.Collect();

            string path = Server.MapPath("FileName.xls");

            System.IO.FileInfo file = new System.IO.FileInfo(path);
            Response.Clear();
            Response.Charset = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
            // 添加头信息，指定文件大小，让浏览器能够显示下载进度
            Response.AddHeader("Content-Length", file.Length.ToString());

            // 指定返回的是一个不能被客户端读取的流，必须被下载
            Response.ContentType = "application/ms-Excel";

            // 把文件流发送到客户端
            Response.WriteFile(file.FullName);
            // 停止页面的执行

            Response.End();

 

3、Excel的XML格式（导出真正的excel文件）

using System;
using System.Text;
using System.IO;

namespace Test
{
    public partial class XmlExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //存放模板文件内容
            string fileContent = string.Empty;
            //模板文件位置
            string modleFileName = Server.MapPath(".") + "//ExcelModleFile.xls";
            //生成文件位置
            string renderFileName = Server.MapPath(".") + "//ExcelFile.xls";

            //读出并保存模板文件内容
            StreamReader sr = new StreamReader(modleFileName, System.Text.Encoding.GetEncoding("gb2312"));
            fileContent = sr.ReadToEnd();
            sr.Close();

            //循环生成数据行
            StringBuilder sbRowsText = new StringBuilder(1024);
            sbRowsText.Append("<Row ss:AutoFitHeight=/"0/">");
            sbRowsText.Append("<Cell ss:StyleID=/"s24/" ss:HRef=/"");
            //设置超链接地址
            sbRowsText.Append("http://www.126.com/");
            sbRowsText.Append("/"><Data ss:Type=/"String/">View</Data></Cell>");
            sbRowsText.Append("<Cell ss:StyleID=/"s22/"><Data ss:Type=/"String/">");
            //设置内容
            sbRowsText.Append("Content");
            sbRowsText.Append("</Data></Cell>");
            sbRowsText.Append("</Row>");

            //保存完整Excel内容的字符串
            StringBuilder sbRender = new StringBuilder();
            //获得模板内容
            sbRender.Append(fileContent);
            //设置Excel数据行
            sbRender.Replace(@"[RowCount]", "3");
            //设置Excel标题
            sbRender.Replace(@"[Header]", "Title");
            //添加数据行
            sbRender.Replace(@"[DataRows]", sbRowsText.ToString());

            lblXml.Text = sbRender.ToString();

            //将内容写入文件
            StreamWriter sw = new StreamWriter(renderFileName);
            sw.Write(sbRender.ToString());
            sw.Close();

            //将文件输出到客户端
            Response.Charset = "GB2312";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(renderFileName));
            // 指定返回的是一个不能被客户端读取的流，必须被下载
            Response.ContentType = "application/ms-excel";
            // 把文件流发送到客户端
            Response.WriteFile(renderFileName);
            Response.End();
        }
    }
}

 

4、直接插入到excel模板文件

        string strValueText = "INSERT INTO [SystemData$](省份标示,月份)VALUES('";

        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection oleExcelConn = new OleDbConnection(strConn);
        System.Data.OleDb.OleDbCommand oleExcelCmd = new OleDbCommand();

        try
        {
            oleExcelConn.Open();
            oleExcelCmd.Connection = oleExcelConn;

            for (int i = 0; i <= intRowCount; i++)
            {
                string strCommandText = "";
                if (i != intRowCount)
                {
                    strCommandText += spid.ToString() + "','" + dt.Rows[i]["sc_Date"].ToString() + "')";
                }

                oleExcelCmd.CommandText = strValueText + strCommandText;
                oleExcelCmd.ExecuteNonQuery();
            }

            return "";
        }
        catch (System.Data.OleDb.OleDbException ex)
        {
            return ex.Message.Replace("'", "");
        }
        finally
        {
            oleExcelConn.Close();
            oleExcelCmd.Dispose();
        }