Bagaimana membaca data dari excel menggunakan ole db?

Apakah Anda bekerja di Windows atau di Web atau di aplikasi Console, pada satu tahap kita perlu memproses data dan file excel banyak digunakan untuk itu, jadi di artikel sebelumnya, kami telah menjelaskan tentang membuat excel di C# tanpa interop tetapi di artikel ini . xls atau. xlsx) di C# baris demi baris di aplikasi Konsol menggunakan OLEDB atau EPPlus atau Interop (semua 3 metode), Anda dapat menggunakan kode yang sama kode C# untuk mengisi ASP. Tabel aplikasi NET GridView atau MVC

Jadi, mari kita mulai dengan itu

Langkah 1. Buat aplikasi Konsol baru di Visual Studio Anda, dengan menavigasi ke File->New->Project-> Pilih "Windows Classic dekstop" dari panel kiri & "Console-App" dari panel kanan -> Berikan nama untuk aplikasi Anda

Langkah 2. Sekarang kita memiliki aplikasi Console dan kita perlu menambahkan kode C# menggunakan OLEDB untuk membaca file excel, untuk itu kita memerlukan string koneksi dengan URL sumber file excel

Dalam contoh yang diberikan seperti yang saya gunakan. File excel XLS, ini string koneksi saya

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Sample1.xls; Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'";

Untuk Format Excel 97-2003 kita bisa menggunakan “Microsoft Jet OLEDB Driver 4. 0”, sedangkan untuk Connection String for Excel 2007 Format (. XLSX), kita bisa menggunakan “Microsoft Ace OLEDB Driver 12. 0” dan string koneksinya akan seperti di bawah ini

 string connString = "Provider= Microsoft.ACE.OLEDB.12.0;" + "Data Source=Sample1.xlsx" + ";Extended Properties='Excel 8.0;HDR=Yes'";

Di string Koneksi di atas, Anda mungkin melihat properti yang diperluas HDR=Ya & HDR=Tidak

Gunakan HDR=YES jika baris excel pertama berisi header, alternatifnya, gunakan HDR=NO jika baris pertama excel Anda bukan header dan itu adalah data

Sekarang, kita memiliki connection string , kita perlu membuat koneksi menggunakan OLEDB dan membukanya

             // Create the connection object
            OleDbConnection oledbConn = new OleDbConnection(connString);
           
            // Open connection
            oledbConn.Open();

Baca file excel menggunakan koneksi OLEDB dan isi dengan dataset

  //here sheet name is Sample-spreadsheet-file, usually it is Sheet1, Sheet2 etc..
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sample-spreadsheet-file$]", oledbConn);

                // Create new OleDbDataAdapter
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                DataSet ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.Fill(ds, "Employees");

pada kode diatas Sample-spreadsheet-file adalah nama Sheet

Catatan. Dengan bantuan nama sheet, Anda bisa merujuk ke data Excel, Anda perlu menggunakan '$' dengan nama sheet, mis. g. Pilih * dari [Sheet1$]

Sekarang lewati setiap baris lembar excel dan cetak di aplikasi Console

 //loop through each row
                foreach(var m in ds.Tables[0].DefaultView)
                {
                    Console.WriteLine(((System.Data.DataRowView)m).Row.ItemArray[0] +" "+((System.Data.DataRowView)m).Row.ItemArray[1] +" "+((System.Data.DataRowView)m).Row.ItemArray[2]);

                }

Jika Anda menggunakan ASP. NET, Anda dapat mengikatnya dengan Grid View menggunakan kode di bawah ini alih-alih mencetaknya

            // Bind the data to the GridView
            GridView1.DataSource = ds.Tables[0].DefaultView;
            GridView1.DataBind();

Sekarang, kita telah membahas setiap langkah, misalkan ini adalah file Excel

dan lihat kode di bawah ini di aplikasi Konsol Anda, bangun dan jalankan

using System;
using System.Data;
using System.Data.OleDb;


namespace CSharpReadExcelFile
{
    class Program
    {
        static void Main(string[] args)
        {
            //this is the connection string which has OLDB 4.0 Connection and Source URL of file
            //use HDR=YES if first excel row contains headers, HDR=NO means your excel's first row is not headers and it's data.
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Sample1.xls; Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'";
          

            // Create the connection object
            OleDbConnection oledbConn = new OleDbConnection(connString);
            try
            {
                // Open connection
                oledbConn.Open();

                // Create OleDbCommand object and select data from worksheet Sample-spreadsheet-file
                //here sheet name is Sample-spreadsheet-file, usually it is Sheet1, Sheet2 etc..
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sample-spreadsheet-file$]", oledbConn);

                // Create new OleDbDataAdapter
                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Create a DataSet which will hold the data extracted from the worksheet.
                DataSet ds = new DataSet();

                // Fill the DataSet from the data extracted from the worksheet.
                oleda.Fill(ds, "Employees");

                //loop through each row
                foreach(var m in ds.Tables[0].DefaultView)
                {
                    Console.WriteLine(((System.Data.DataRowView)m).Row.ItemArray[0] +" "+((System.Data.DataRowView)m).Row.ItemArray[1] +" "+((System.Data.DataRowView)m).Row.ItemArray[2]);

                }
           
            }
            catch (Exception e)
            {
                Console.WriteLine("Error :" + e.Message);
            }
            finally
            {
                // Close connection
                oledbConn.Close();
            }
        }
    }
}

Output dari kode di atas akan menjadi seperti di bawah ini

Sekarang, jika Anda bekerja pada sistem operasi 64 bit, Anda mungkin mendapatkan kesalahan ini "The 'Microsoft. Jet. OLEDB. 4. Penyedia 0' tidak terdaftar di mesin lokal. "

Mengatasi kesalahan ini. Jika aplikasi Anda berbasis Desktop, kompilasi EXE Anda dengan CPU x86 (Menu Tools, Options, pilih Projects And Solutions, centang show advanced build configurations. Sekarang di Build Menu Anda akan dapat pergi ke Config Manager dan mengatur output ke x86. )

Jika aplikasi Anda berbasis web, maka Aktifkan 'Aplikasi 32-Bit' di kumpulan aplikasi

Di IIS, ubah pengaturan "Aktifkan Aplikasi 32-bit" ke True, di Pengaturan Lanjutan untuk Kumpulan Aplikasi

Kerugian menggunakan OLEDB untuk Excel

Dengan OLEDB, Anda tidak dapat memformat data yang Anda sisipkan/perbarui di lembar EXCEL tetapi Interop dapat melakukannya dengan efisien. Anda tidak dapat melakukan operasi matematika apa pun atau mengerjakan grafik menggunakan OLEDB, tetapi ini benar-benar cara yang baik untuk memasukkan/memperbarui data di EXCEL di mana tidak ada aplikasi Excel yang diinstal

Membaca file Excel menggunakan EPPlus

Jika Anda tidak ingin menggunakan OleDb, Anda dapat mencoba menggunakan solusi berbasis paket EPPlus Nuget

Untuk ini, Anda harus menginstal EPPlus, jadi buka "Tools"-> "Nuget package manager"-> "Kelola Nuget untuk solusi ini" -> Pilih tab "Browse" dan cari "EPPlus", lalu instal nuget

Setelah Anda menginstal paket, di aplikasi Konsol Anda "Program. cs", Anda dapat menggunakan kode di bawah ini

using OfficeOpenXml;
using System;
using System.IO;


namespace ReadExcelInCsharp
{
    class Program
    {
        static void Main(string[] args)
        {
            //provide file path
            FileInfo existingFile = new FileInfo(@"D:\sample_XLSX.xlsx");
            //use EPPlus
            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                //get the first worksheet in the workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                int colCount = worksheet.Dimension.End.Column;  //get Column Count
                int rowCount = worksheet.Dimension.End.Row;     //get row count
                for (int row = 1; row <= rowCount; row++)
                {
                    for (int col = 1; col <= colCount; col++)
                    {
                        //Print data, based on row and columns position
                        Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells[row, col].Value?.ToString().Trim());
                    }
                }
            }
        }
    }
}

Berikut adalah gambar yang menampilkan output aplikasi konsol dengan contoh file excel (. xlsx), kami menggunakan. xlsx di sini untuk dibaca dalam C# menggunakan EPPlus

Anda dapat menggunakan contoh EPPlus di atas untuk bekerja. NET Core C# juga, untuk membaca file excel. Inti BERSIH

Baca Excel menggunakan MS Office Interop

Anda juga dapat membaca file Excel di C# menggunakan MS Office Interop dengan mudah

Pertama Anda harus menambahkan referensi untuk "Microsoft.Office.Interop.Excel", jadi di Konsol, Windows atau Aplikasi Web Anda, klik kanan pada "Referensi" dan kemudian di "Majelis" cari "Microsoft. Kantor. Interop. Unggul"

Jadi, inilah Kode C# untuk itu, mengingat kami memiliki contoh file XLSX seperti di atas

using Microsoft.Office.Interop.Excel;
using System;

namespace ReadExcelCSharp
{
    class Program
    {
        static void Main(string[] args)
        {
            Application excelApp = new Application();
            if (excelApp != null)
            {
                Workbook excelWorkbook = excelApp.Workbooks.Open(@"D:\sample_XLSX.xlsx", 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                Worksheet excelWorksheet = (Worksheet)excelWorkbook.Sheets[1];

                Range excelRange = excelWorksheet.UsedRange;
                int rowCount = excelRange.Rows.Count;
                int colCount = excelRange.Columns.Count;
                //get an object array of all of the cells in the worksheet (their values)
                object[,] valueArray = (object[,])excelRange.get_Value(
                            XlRangeValueDataType.xlRangeValueDefault);

                for (int i = 1; i <= rowCount; i++)
                {
                    for (int j = 1; j <= colCount; j++)
                    {
                     

                        Console.WriteLine(valueArray[i, j].ToString());
                    }
                }

                excelWorkbook.Close();
                excelApp.Quit();
            }
        }
    }
}

Keluaran

Seperti yang Anda lihat, kami telah menggunakan Interop untuk membuka dan membaca baris/kolom excel, salah satu kelemahan menggunakan Interop MS office adalah Anda perlu menginstalnya di Server juga

Bagaimana cara membaca file Excel menggunakan OLE DB?

Bagaimana mengambil data dari database di Excel?

Bagaimana cara saya terhubung ke database ODBC di Excel?

Bagaimana cara membaca file Excel menggunakan ODBC di C#?