如何读取Excel文件（.slk）并写入DataTable

问题描述

如何阅读Excel文件（.slk）并写入数据表



帮帮我



在此先感谢

How To Read Excel File(.slk) And Write To DataTable

Help Me Out

Thanks In Advance

string path = FileUpload1.PostedFile.FileName;
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + "; Extended Properties='Excel 8.0;HDR=Yes'";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [id],[fname],[lname],[email] from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(GetConnStr());
//Give your Destination table name
sqlBulk.DestinationTableName = "Excel_table";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();

.slk格式不支持

任何其他方法到获取数据到DataTable

.slk Format Not Supporting
Any Other Methods To Get Data Into DataTable

推荐答案

.slk格式不支持

将数据导入DataTable的任何其他方法

.slk Format Not Supporting
Any Other Methods To Get Data Into DataTable

string folderpath = fileInfo.FullName.Replace(fileInfo.Name, "");
            string destinationpath = folderpath + Path.GetFileNameWithoutExtension(fileInfo.FullName) + DateTime.Now.Ticks + ".csv";
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wbWorkbook = app.Workbooks.Open(fileInfo.FullName); //@"E:\FileReadTest\TBN Result Summary.slk");
            Microsoft.Office.Interop.Excel.Sheets wsSheet = wbWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet CurSheet = (Microsoft.Office.Interop.Excel.Worksheet)wsSheet[1];
            wbWorkbook.SaveAs(@destinationpath, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            wbWorkbook.Close(false, fileInfo.FullName, false);
            app.Workbooks.Close();
            app.Quit();
            Marshal.FinalReleaseComObject(app);
            return destinationpath;

然后读取我的csv文件并存储在数据表中

and then read my csv file and stored in data table

FileInfo file = new FileInfo(path);
           DataTable tbl = null;
           if (File.Exists(file.FullName))
           {
               using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + file.DirectoryName + "\";Extended Properties=''text;HDR=Yes;FMT=Delimited(,)'';"))
               {
                   using (OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}]", file.Name), con))
                   {
                       con.Open();
                       using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                       {
                           tbl = new DataTable("MyTable");
                           adp.Fill(tbl);
                       }
                   }
               }
           }
           return tbl;

这篇关于如何读取Excel文件（.slk）并写入DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！