Geek Answers Handbook

Reading values ​​from an Excel file

I want to get the value from 12 excel sheets. is there any way to get values ​​without opening excel sheet? I am using vb.net. Please write an example code if there is a way to read the values ​​without opening the excel file. thanks

+5
source share
5 answers

You cannot read values ​​without opening the Excel file at all. But you can read the values ​​without having to open Excel.

If the file is saved in xml format, it will be easier. If not, the easiest way is to use Excel, but for this you need to use Office Automation. The hard way is to create an excel parser - quite difficult in the non-open xml excel format (pre Office 2003) - it's difficult, but still possible.

However, this cannot be read from an Excel spreadsheet without opening the file at all.

Here's a snippet of code that you could use to open a spreadsheet from VB.NET using Office Automation (it still opens the file, relies on Excel Automation libraries, but does not require opening Excel):

RENOUNCEMENT

The following code is not intended to be used as is, but just a sample that guides the reader to their own solution, which should be thoroughly tested.

' The code below requires you to add references to Office Interop assemblies
' into your VB.NET project  (if you don't know how to do that search Google)

xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open("<YOUR EXCEL SPREADSHEET FILE HERE")
xlWorkSheet = xlWorkBook.Worksheets("sheet1")

range = xlWorkSheet.UsedRange

For rCnt = 1 To range.Rows.Count
    For cCnt = 1 To range.Columns.Count
        Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
        ' Obj.value now contains the value in the cell.. 
    Next
Next
+5

ADO.NET Excel. . http://www.connectionstrings.com/excel-2007

<connectionStrings>
    <add name="Default"
         connectionString='Microsoft.ACE.OLEDB.12.0;Data Source=c:\your\folder\file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";'
         providerName="System.Data.OleDb" />
</connectionStrings>

System.Data.OleDb.OleDbConnection . , Excel, Users, , UserName Age.

using System.Data;
using System.Data.Common;

public int UserExists(string userName, int age)
{
    var provider = ConfigurationManager.ConnectionStrings["Default"].ProviderName;
    var factory = DbProviderFactories.GetFactory(provider);

    var connectionString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;

    using (var connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionString;

        using (DbCommand command = connection.CreateCommand())
        {
            DbParameter userNameParameter = factory.CreateParameter();
            userNameParameter.ParameterName = "@UserName";
            userNameParameter.DbType = DbType.String;
            userNameParameter.Direction = ParameterDirection.Input;
            userNameParameter.IsNullable = false;
            userNameParameter.Value = userName;


            DbParameter ageParameter = factory.CreateParameter();
            ageParameter.ParameterName = "@Age";
            ageParameter.DbType = DbType.Int32;
            ageParameter.Direction = ParameterDirection.Input;
            ageParameter.IsNullable = false;
            ageParameter.Value = age;

            command.CommandText = "SELECT COUNT(*) FROM [Users$] WHERE UserName=@UserName AND Age=@Age";
            command.Parameters.Add(userNameParameter);
            command.Parameters.Add(ageParameter);
            connection.Open();

            int usersExits = (int) command.ExecuteScalar();

            return usersExits == 1;
        }
    }
}
+3

, Excel, , Office, , . Office (. ).

0

,

DimobjEXCELCon As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=EXCLE_FILE_PATH;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
ExcelConnection.Open()

Dim objQuery As String = "SELECT * FROM [Sheet1$]" 'get values from sheet1, here you can change your sheet name

Dim objCMD As OleDbCommand = New OleDbCommand(objQuery,objEXCELCon)
Dim objDR As OleDbDataReader

Dim SQLconn As New SqlConnection()
Dim szCON As String = "Connection string for database"
SQLconn.ConnectionString = szCON
SQLconn.Open()


Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLConn)
bulkCopy.DestinationTableName = "TableToWriteToInSQLSERVER"

 Try
  objDR = objCMD.ExecuteReader
  bulCopy.WriteToServer(objDR)
  objDR.Close()
  SQLConn.Close()

 Catch ex As Exception
  MsgBox(ex.ToString)
 End Try
0

- excel visible = false, excel. , - , excel. , . ADO; , , excel ; , , ( , )

0

More articles:


All Articles