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 " οnclick= " 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 " οnclick= " 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 }