Biegral Blog

    /// <summary>
    /// 从Excel中导出数据到DataSet中
    /// </summary>
    /// <param name="filepath">Excel文件的绝对路径</param>
    /// <param name="sheetname">excel文件中的表名</param>
    /// <returns></returns>
    public DataSet ExcelDataSource(string filepath, string sheetname)
    {
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
        DataSet ds = new DataSet();
        oada.Fill(ds);
        conn.Close();
        return ds;
    }

    /// <summary>
    /// 获得Excel中的所有sheetname
    /// </summary>
    /// <param name="filepath">Excel文件的绝对路径</param>
    /// <returns></returns>
    public ArrayList ExcelSheetName(string filepath)
    {
        ArrayList al = new ArrayList();
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        DataTable sheetNames = conn.GetOleDbSchemaTable
        (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        conn.Close();
        foreach (DataRow dr in sheetNames.Rows)
        {
            al.Add(dr[2]);
        }
        return al;
    }

    /// <summary>
    /// 将DataSet中的数据导出Execl文件
    /// </summary>
    /// <param name="ds">DataSet</param>
    /// <param name="FileName">Execl文件名 123.xls</param>
    public void CreateExcel(DataSet ds, string FileName)
    {
        HttpResponse resp;
        resp = Page.Response;
        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
        string colHeaders = "", ls_item = "";

        //定义表对象与行对象，同时用DataSet对其值进行初始化 
        DataTable dt = ds.Tables[0];
        DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
        int i = 0;
        int cl = dt.Columns.Count;

        //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符 
        for (i = 0; i < cl; i++)
        {
            if (i == (cl - 1))//最后一列，加n
            {
                colHeaders += dt.Columns[i].Caption.ToString() + "n";
            }
            else
            {
                colHeaders += dt.Columns[i].Caption.ToString() + "t";
            }
        }
        //向HTTP输出流中写入取得的数据信息 
        resp.Write(colHeaders);
        //逐行处理数据   
        foreach (DataRow row in myRow)
        {
            //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据     
            for (i = 0; i < cl; i++)
            {
                if (i == (cl - 1))//最后一列，加n
                {
                    ls_item += row[i].ToString() + "n";
                }
                else
                {
                    ls_item += row[i].ToString() + "t";
                }
            }
            resp.Write(ls_item);
            ls_item = "";
        }
        resp.End();
    }