asp.net读取本地Excel 文件这个项目大家多多少少都会遇到，其方法大致分为如下三类：
1.客户端读取本地excel内容
View Code
1 <script>
2 function readThis(){
3 var tempStr = "";
4 var filePath= document.all.upfile.value;
5 var oXL = new ActiveXObject("Excel.application");
6 var oWB = oXL.Workbooks.open(filePath);
7 oWB.worksheets(1).select();
8 var oSheet = oWB.ActiveSheet;
9 try{
10 for(var i=2;i<46;i++){
11 if(oSheet.Cells(i,2).value =="null" || oSheet.Cells(i,3).value =="null" )
12 break;
13 var a = oSheet.Cells(i,2).value.toString()=="undefined"?"":oSheet.Cells(i,2).value;
14 tempStr+=(" "+oSheet.Cells(i,2).value+
15 " "+oSheet.Cells(i,3).value+
16 " "+oSheet.Cells(i,4).value+
17 " "+oSheet.Cells(i,5).value+
18 " "+oSheet.Cells(i,6).value+"\n");
19 }
20 }catch(e){
21 //alert(e);
22 document.all.txtArea.value = tempStr;
23 }
24 document.all.txtArea.value = tempStr;
25 oXL.Quit();
26 CollectGarbage();
27 }
28 </script>
29 <html>
30 <input type="file" id="upfile" /><input type="button" onclick="readThis();" value="读取">
31 <br>
32 <textarea id="txtArea" cols=50 rows=10></textarea>
33 </html>
2 function readThis(){
3 var tempStr = "";
4 var filePath= document.all.upfile.value;
5 var oXL = new ActiveXObject("Excel.application");
6 var oWB = oXL.Workbooks.open(filePath);
7 oWB.worksheets(1).select();
8 var oSheet = oWB.ActiveSheet;
9 try{
10 for(var i=2;i<46;i++){
11 if(oSheet.Cells(i,2).value =="null" || oSheet.Cells(i,3).value =="null" )
12 break;
13 var a = oSheet.Cells(i,2).value.toString()=="undefined"?"":oSheet.Cells(i,2).value;
14 tempStr+=(" "+oSheet.Cells(i,2).value+
15 " "+oSheet.Cells(i,3).value+
16 " "+oSheet.Cells(i,4).value+
17 " "+oSheet.Cells(i,5).value+
18 " "+oSheet.Cells(i,6).value+"\n");
19 }
20 }catch(e){
21 //alert(e);
22 document.all.txtArea.value = tempStr;
23 }
24 document.all.txtArea.value = tempStr;
25 oXL.Quit();
26 CollectGarbage();
27 }
28 </script>
29 <html>
30 <input type="file" id="upfile" /><input type="button" onclick="readThis();" value="读取">
31 <br>
32 <textarea id="txtArea" cols=50 rows=10></textarea>
33 </html>
以上代码在需要设置IE安全级别低才能正常运行
2.服务器端读取Excel内容，用oledb的方式读取本地Excel的数据。
方式：客户端将文件上传到服务器端的指定共享目录里，服务器端在去读取共享目录里的Excel文件
这种方式实现起来非常简单，但是前提是必须要在服务器端开放一个共享目录，这样会给服务器带来隐患的。
具体代码如下：
View Code
1 private DataTable GetDataTable(string fileName)
2 {
3 string path = "C:\\图纸清单\\";
4 string strPath = "";
5 FileInfo file = new FileInfo(path + fileName);
6 if (fileName.Substring(fileName.LastIndexOf(".")) == ".xls")
7 {
8 strPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + path + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\" ";
9 }
10 else if (fileName.Substring(fileName.LastIndexOf(".")) == ".xlsx")
11 {
12 strPath = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + fileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
13 }
14 if (file.Exists)
15 {
16 file.Delete();
17 }
18 DataSet ds = new DataSet();
19
20 OleDbConnection conn = new OleDbConnection(strPath);
21 conn.Open();
22 OleDbDataAdapter OAdapter = new OleDbDataAdapter("select * from [Sheet1$]", conn);
23 OAdapter.Fill(ds, "Drawing");
24 conn.Close();
25 return ds.Tables[0];
26 }
2 {
3 string path = "C:\\图纸清单\\";
4 string strPath = "";
5 FileInfo file = new FileInfo(path + fileName);
6 if (fileName.Substring(fileName.LastIndexOf(".")) == ".xls")
7 {
8 strPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + path + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\" ";
9 }
10 else if (fileName.Substring(fileName.LastIndexOf(".")) == ".xlsx")
11 {
12 strPath = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + fileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
13 }
14 if (file.Exists)
15 {
16 file.Delete();
17 }
18 DataSet ds = new DataSet();
19
20 OleDbConnection conn = new OleDbConnection(strPath);
21 conn.Open();
22 OleDbDataAdapter OAdapter = new OleDbDataAdapter("select * from [Sheet1$]", conn);
23 OAdapter.Fill(ds, "Drawing");
24 conn.Close();
25 return ds.Tables[0];
26 }
3.读取Excel文件流的方式读取Excel文件的内容
我在网上找了很长时间才找到这种方法，感觉很好所以分享一下。
方式：将本地Excel通过FileUpload上传获取上传的ileUpload1.PostedFile.InputStream，从而获取内容，前提是必须引用Excel.dll和ICSharpCode.SharpZipLib.dll 返回DataTable
具体代码如下：
View Code
1 public DataTable GetDataTable(Stream fileStream, string fileName)
2 {
3 IExcelDataReader excelReader;
4 if (fileName.Trim().ToUpper().EndsWith("XLS"))
5 {
6 //从Excel的二进制文件中读取('97-2003 格式: *.xls)
7 excelReader = ExcelReaderFactory.CreateBinaryReader(fileStream);
8 }
9 else if (fileName.Trim().ToUpper().EndsWith("XLSX"))
10 {
11 //从Excel的OpenXml文件中读取(2007 格式： *.xlsx)
12 excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
13 }
14 else
15 {
16 throw new Exception("无效的数据格式，请使用正确的xls或xlsx文件!");
17 }
18 //表格的第一行作为表格头
19 excelReader.IsFirstRowAsColumnNames = true;
20 // DataSet - Excel的每一个工作表（sheet)将作为一个DataTable读取取结果中。
21 DataSet result = excelReader.AsDataSet();
22 DataTable dt = result.Tables[0];
23 //5. 释放资源
24 excelReader.Close();
25 return dt;
26 }
2 {
3 IExcelDataReader excelReader;
4 if (fileName.Trim().ToUpper().EndsWith("XLS"))
5 {
6 //从Excel的二进制文件中读取('97-2003 格式: *.xls)
7 excelReader = ExcelReaderFactory.CreateBinaryReader(fileStream);
8 }
9 else if (fileName.Trim().ToUpper().EndsWith("XLSX"))
10 {
11 //从Excel的OpenXml文件中读取(2007 格式： *.xlsx)
12 excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
13 }
14 else
15 {
16 throw new Exception("无效的数据格式，请使用正确的xls或xlsx文件!");
17 }
18 //表格的第一行作为表格头
19 excelReader.IsFirstRowAsColumnNames = true;
20 // DataSet - Excel的每一个工作表（sheet)将作为一个DataTable读取取结果中。
21 DataSet result = excelReader.AsDataSet();
22 DataTable dt = result.Tables[0];
23 //5. 释放资源
24 excelReader.Close();
25 return dt;
26 }
