如何读取Excel文件（.slk）并写入DataTable [英] How To Read Excel File(.slk) And Write To 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

推荐答案

，excelConnection）;
excelConnection.Open（）;
OleDbDataReader dReader;
dReader = cmd.ExecuteReader（）;
SqlBulkCopy sqlBulk = new SqlBulkCopy（GetConnStr（））;
// 提供目的地表名称
sqlBulk.DestinationTableName = Excel_table;
sqlBulk.WriteToServer（dReader）;
excelConnection.Close（）;

", 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文件转换为csv文件

I Converted My .slk file to csv File

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;

阅读以下内容：使用OleDb导入文本文件（选项卡，CSV，自定义） [ ^ ]

Read the following : Using OleDb to Import Text Files (tab, CSV, custom)[^]

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