How to scan data from an Excel worksheet

There is a basic way to read Excel spreadsheets (* .xls and * .xlsx), but provider must be installed on the machine to work. In this example of two ConnectionString one for xls and another for xlsx , depending on provider you can change some variables of that ConnectionString .

Example Worksheet

Codetoreadthisspreadsheet

usingSystem.Data.OleDB;//StringdeConexaostringConnectionString=@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\Temp\NamesV1.xls';Extended Properties=Excel 8.0;";
    //string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Temp\Names.xlsx;Extended Properties=\""Excel 12.0 Xml; HDR = YES""\";


//Código
using (OleDbConnection db = new OleDbConnection(ConnectionString))
using (OleDbCommand command = db.CreateCommand())
{
    db.Open();
    command.CommandType = System.Data.CommandType.Text;
    command.CommandText = "SELECT * FROM [Sheet1$]";

    using (OleDbDataReader reader = command.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while(reader.Read())
            {
                System.Console.WriteLine("{0} {1}", reader["Id"], reader["Name"]);
            }
        }
    }

    db.Close();
}

References:

• Read Excel file using OLEDB Data Provider in C # .Net
• Read and Import Excel File into DataSet
• How to insert data into Excel file using OLEDB
• OLE DB Tutorial

I recommend using SpreadsheetLight .

It's very simple to use and well-documented.

Just download the source on the official website and attach in your solution - unfortunately there is no NuGet official package for it.

Here's how to modify an existing spreadsheet:

    class Program
    {
        static void Main(string[] args)
        {
            // SpreadsheetLight works on the idea of a currently selected worksheet.
            // If no worksheet name is provided on opening an existing spreadsheet,
            // the first available worksheet is selected.
            SLDocument sl = new SLDocument("ModifyExistingSpreadsheetOriginal.xlsx", "Sheet2");

            sl.SetCellValue("E6", "Let's party!!!!111!!!1");

            sl.SelectWorksheet("Sheet3");
            sl.SetCellValue("E6", "Before anyone calls the popo!");

            sl.AddWorksheet("DanceFloor");
            sl.SetCellValue("B4", "Who let the dogs out?");
            sl.SetCellValue("B5", "Woof!");

            sl.SaveAs("ModifyExistingSpreadsheetModified.xlsx");

            Console.WriteLine("End of program");
            Console.ReadLine();
        }
    }

On their site they have several examples of how to perform various other tasks.

Open excel and press Alt + F11 to enter the platform to program in VBA.

Create an ODBC connection to connect to the database.

To loop. you can do the following

Create a module and use the code below

Sub CadastraBD()
Dim Planilha As Worksheet
Set Planilha = Plan1 ' Plan1 é a Planilha/Worksheet

'VARRE DA LINHA1 A ULTIMA LINHA
For x = 1 To UltimaLinha(Planilha, 1)
    ColunaA = Range("A" & x)
    ColunaB = Range("B" & x)
    ColunaC = Range("C" & x)
    ColunaD = Range("D" & x)
    ColunaE = Range("E" & x)
Next
End Sub

Public Function UltimaLinha(PLAN As Worksheet, COLUNA As Integer)
    UltimaLinha = PLAN.Cells(65000, COLUNA).End(xlUp).Row
End Function