在不保存的情况下读取上传的Excel文件

本文介绍了在不保存的情况下读取上传的Excel文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在此代码段中-我从用户处获取了上载的文件，并将其保存在我的应用程序的文件夹中，然后将OleDbConmnection设置为此Excel文件并读取数据.我的问题是-有人可以建议一种读取此excel文件的首选方法，但又不需要一次又一次地保存它，因为在我的情况下，用数据填充数据表

In this snippet of code - I get the uploaded file from the user and save it in a folder in my app and then make OleDbConmnection to this Excel File and read the data. My question is - can someone suggest a way which is preferred of reading this excel file but without saving it previously and again as it's in my case fill the datatable with the data

if (Request != null) { HttpPostedFileBase file = Request.Files[0]; if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName)) { string fileName = file.FileName; string fileContentType = file.ContentType; string fileExtension = System.IO.Path.GetExtension(Request.Files[0].FileName); if (fileExtension == ".xls" || fileExtension == ".xlsx") { string fileLocation = Server.MapPath("~/Content/") + Request.Files[0].FileName; if (System.IO.File.Exists(fileLocation)) { System.IO.File.Delete(fileLocation); } Request.Files[0].SaveAs(fileLocation); string excelConnectionString = string.Empty; excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; //connection String for xls file format. if (fileExtension == ".xls") { excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } //connection String for xlsx file format. else if (fileExtension == ".xlsx") { excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } //Create Connection to Excel work book and add oledb namespace OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", excelConnection); OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd); DataTable dt = new DataTable(); DataSet ds = new DataSet(); objAdapter1.Fill(ds); DataTable Dt = ds.Tables[0];

推荐答案

请参阅此库. Excel数据读取器

编辑 例如:

if (Request != null) { HttpPostedFileBase file = Request.Files[0]; if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName)) { string fileName = file.FileName; string fileContentType = file.ContentType; string fileExtension = System.IO.Path.GetExtension(Request.Files[0].FileName); if (fileExtension == ".xls" || fileExtension == ".xlsx") { IExcelDataReader excelReader; if (fileExtension == ".xls") excelReader = ExcelReaderFactory.CreateBinaryReader(stream); else excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); excelReader.IsFirstRowAsColumnNames = true; DataSet ds = excelReader.AsDataSet(); DataTable Dt = ds.Tables[0];