ASP.NET读写Excel数据

闲话少说，上代码。

DataTable导出成Excel文件：

 1public static void DataSetToExcel(DataSet p_dsExport,string p_strFileName,bool p_blnHaveHeaderText)
 2  {
 3   if (p_dsExport == null)
 4   {
 5    return ;
 6   }
 7   string strContext=GenerateWorkSheet(p_dsExport);
 8
 9   DownloadExcelFile(strContext,p_strFileName);
10
11  }
12
13public static string GenerateWorkSheet(DataSet p_dsExport)
14        {
15            System.Text.StringBuilder   strExcelXml=new System.Text.StringBuilder ();
16            strExcelXml.Append(ExcelHeader());
17            strExcelXml.Append(ExcelWorkSheetOptions()); 
18            
19
20            foreach(DataTable dt in p_dsExport.Tables)
21            {
22                // Create First Worksheet tag
23                strExcelXml.Append("<Worksheet ss:Name=\""+ dt.TableName +"\">");
24                // Then Table Tag
25                strExcelXml.Append("<Table>");
26                strExcelXml.Append(GetHeaderText(dt));
27                int intColCount=dt.Columns.Count;
28                foreach(DataRow dr in dt.Rows)
29                {
30                    // Row Tag
31                    strExcelXml.Append("<Row>\r\n");
32                    for(int j=0;j<intColCount;j++)
33                    {
34                        // Cell Tags
35                        strExcelXml.Append("<Cell ss:Index=\""+(j+1).ToString()+"\"><Data ss:Type=\"String\">");
36                        strExcelXml.Append(System.Web.HttpUtility.HtmlEncode( ObjectToNullStr(dr[j]))); 
37                        strExcelXml.Append("</Data></Cell>\r\n");
38                    }
39                    strExcelXml.Append("</Row>\r\n");
40                    
41                }
42                strExcelXml.Append("</Table>");
43                strExcelXml.Append("</Worksheet>"); 
44            }
45            strExcelXml.Append("</Workbook>\r\n");
46            return strExcelXml.ToString();
47        }
48
49private static void DownloadExcelFile(string p_strFileContext,string p_strFileName)
50        {
51            // Appending Headers            
52            if (IsNullString(p_strFileName))
53            {
54                p_strFileName="Excel.xls";
55            }
56            
57            if (!p_strFileName.Trim().ToLower().EndsWith(".xls"))
58            {
59                p_strFileName += ".xls";
60            }
61
62            try
63            {
64                HttpContext.Current.Response.Clear();
65                HttpContext.Current.Response.Buffer= true;
66                p_strFileName = UrlEncode(p_strFileName);
67                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
68                HttpContext.Current.Response.AppendHeader("content-disposition", "attachment; filename=" + p_strFileName);
69            }
70            catch
71            {
72            }
73            
74                                    
75            //Writeout the Content                
76            HttpContext.Current.Response.Write(p_strFileContext);
77            try
78            {
79                HttpContext.Current.Response.End();
80            }
81            catch
82            {
83            }
84            
85        }

将Excel文件中的数据导入到DatSet

1private static string GetExcelConnectString(string p_strFileName,bool p_blnHaveHeaderText)
2        {
3            string strHDR=p_blnHaveHeaderText?"Yes":"No";
4            string strRtn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
5                + p_strFileName 
6                +"; Extended Properties=\"Excel 8.0; HDR="+strHDR+"\";";
7            return strRtn;            
8        }

 1private void Button2_Click(object sender, System.EventArgs e)
 2        {
 3
 4            //也可以使用右侧的路径    string filename = @"e:\Book1.xls";
 5            string filename = Request.PhysicalApplicationPath + @"excel\Book1.xls";
 6
 7            System.Data.DataTable  dt = new DataTable();
 8
 9            //第二个参数若为false，则第一行数据做为datatable的数据存在。反之，则不。
10            dt = GYRTExcel.ExcelToDataTable(filename,false);
11            this.DataGrid1.DataSource = dt;
12            this.DataGrid1.DataBind();
13        }

 1/**//// <summary>
 2        /// 把 Excel 文件的数据导入到 DataTable 中
 3        /// </summary>
 4        /// <param name="p_strFileName">服务器上 Excel 文件的全路径</param>
 5        /// <param name="p_blnHaveHeaderText">true　标示第一行是否是列名，默认值为 true </param>
 6        /// <returns></returns>
 7        public static DataTable ExcelToDataTable(string p_strFileName,bool p_blnHaveHeaderText)
 8        {
 9            return ExcelToDataTable(p_strFileName,p_blnHaveHeaderText,null);
10        }
11
12/**//// <summary>
13        /// 把 Excel 文件的数据导入到 DataSet 中
14        /// </summary>
15        /// <param name="p_strFileName">服务器上 Excel 文件的全路径</param>
16        /// <param name="p_blnHaveHeaderText">true　标示第一行是否是列名，默认值为 true</param>
17        /// <returns></returns>
18        public static DataSet ExcelToDataSet(string p_strFileName,bool p_blnHaveHeaderText)
19        {
20            using(OleDbConnection conn=
21                      new OleDbConnection(GetExcelConnectString(p_strFileName,p_blnHaveHeaderText)))
22            {
23                DataSet dsRtn=new DataSet();
24                try
25                {
26                    //如果不是标准的 Excel 文件则当作是 Xml 文件读取
                           //如果Excel文件的路径不对的话，通常会跳到catch中，并返回对文件无读写权限或文件已经被占用。
27                    conn.Open();
28                }
29                catch(Exception ex)
30                {
31                    return ReadXmlFile(p_strFileName,p_blnHaveHeaderText);
32                }
33                
34                DataTable dtExcelTable=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
35                if (dtExcelTable == null)
36                {
37                    return null;
38                }
39                foreach (DataRow dr in dtExcelTable.Rows)
40                {
41                    string strTableName=ObjectToNullStr(dr["TABLE_NAME"]);
42                    if (IsNullString(strTableName))
43                    {
44                        continue ;
45                    }
46                    string strSheetName = strTableName.Substring(0,strTableName.Length-1); 
47                    string strCommandText="select * from " + "[" + strTableName + "]";
48                    
49
50                    OleDbDataAdapter daAdapter=new OleDbDataAdapter(strCommandText,conn);
51                    DataTable dt=new DataTable(strSheetName);
52                    daAdapter.FillSchema(dt,SchemaType.Source);
53                    daAdapter.Fill(dt); 
54                    dsRtn.Tables.Add(dt);                    
55                }
56                conn.Close();
57                return dsRtn;
58            }
59        }