/// <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(); }