How to scan data from an Excel spreadsheet

Question

I have a Mysql database and I want to take the data from an excel spreadsheet and send it to him.

I already know how to save the data in the table, just need to know how to scan the spreadsheet and get the data.

Answer 1

I recommend using the Spreadsheetlight.

It is very simple to use and rasoably well documented.

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

Follows the exeplo of 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 website there several examples how to perform several other tasks.

Answer 2

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 as follows

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

Answer 3

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

Spreadsheet Example:

Code for reading this spreadsheet

using System.Data.OleDB;

    //String de Conexao
    string ConnectionString = @"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 to Excel file using OLEDB
• OLE DB Tutorial