Thiết lập kết nối OLEDB cho cả office 32 bit và Office 64 bit trên Excel dùng .Net (2 người xem)

[vba_gpe]

Chào anh chị trong diễn đàn
Hiện em đang tập tành viết 1 ứng dụng Addin cho Excel trên VB.net
Em dùng truy vấn SQL để kết nối dữ liệu từ file Excel để đưa vào Table trong .Net
Máy em đang dùng:
- Win 64 bit
- Office 32 bit. (office 2016)
Sau khi buil cài đặt và kiểm tra tính năng này trên các máy thì cụ thể như sau:
Câu truy vấn Excel xls.

If fileExtension = ".xls" Then conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Import_FileName & ";" & "Extended Properties='Excel 8.0;HDR=YES;'"

Câu truy vấn ".xlsx"

If fileExtension = ".xlsx" Then conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Import_FileName & ";" & "Extended Properties='Excel 12.0 Xml;HDR=YES;'"

Kết quả:
+ Trên máy có Office 32 bit, không phân biệt windows (32, 64 bit) câu truy vấn đều hoạt động tốt.
+ Trên máy có Office 64 bit, thì câu truy vấn bị lỗi: "Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine"
Em thử tìm trên các trang mạng thì có hướng xử lý như sau:
1. Cài Microsoft Accesss data Engineer: Em thử cài trên máy 64 bit cả 2 phiên bản đều không ăn thua.
2. Đổi như sau vẫn lỗi:
Từ

Microsoft.Jet.OLEDB.4.0

thành

Provider=Microsoft.ACE.OLEDB.12.0

3. Đổi Target CPU trên máy tính trước khi build: Từ Any CPU về x86 thì câu truy vấn hoạt động được trên cả office 32 bit và 64 bit office. (Em test thử 1 ứng dụng độc lập).

Nhưng, khi đổi targetCPU như thế đối với dự án VSTO này chỉ hoạt động trên office 32, còn trên office 64 bit là bị lỗi lúc kích hoạt addin luôn.

Có cách nào:
- Vẫn chọn phương án build theo Target CPU: Any CPU, dùng câu lệnh SQL nào để xài được trên cả 64 bit office và 32 bit office (Chia làm 2 câu lệnh phân theo office của máy hiện tại).


Em cảm ơn mọi người rất nhiều.

 

[HeSanbi]

Bạn tham khảo trang này: https://www.connectionstrings.com/excel/

 

[VetMini]

Dim engineToDeploy As String = IIF( Val(DirectCast(CreateObject("Excel.Application"), Excel.Application).Version) >= 12, "ACE", "JET" )

 

[VetMini]

Chú thích: theo như ý ở bài #1 thì bạn bị lỗi ở giai đoạn kết nối
Nhưng theo lời thì bạn lại nói mình bị lỗi truy vấn (execute câu lệnh SQL, bắt đầu bằng Select...)
ConnectionString chả liên quan gì đến SQL cả. Nó chỉ là câu văn bản thủ tục để code của bạn xác định phương cách kết nối với CSDL (Excel Workbook). Sau khi kết nối được rồi thì code của bạn mới thực hiện (execute) câu lệnh SQL qua kết nối và lấy về một recordset (hoặc update, insert gì đó).

 

[Vô danh Tiểu tốt]

Nếu không nhầm thì không cần phân biệt xls hay xlsx, chỉ dùng ace12 là đủ rồi. Còn Microsoft Accesss data Engineer thực chất là ace12 đó. Nếu chưa cài office từ 2007 trở lên thì mới cần cài riêng cái đó.
Còn Jet 4.0 ra đời có lẽ cũng trên dưới 20 năm rồi nên không phù hợp với 64bit cũng không có gì là ngạc nhiên cả.

 

[vba_gpe]

Chú thích: theo như ý ở bài #1 thì bạn bị lỗi ở giai đoạn kết nối
Nhưng theo lời thì bạn lại nói mình bị lỗi truy vấn (execute câu lệnh SQL, bắt đầu bằng Select...)
ConnectionString chả liên quan gì đến SQL cả. Nó chỉ là câu văn bản thủ tục để code của bạn xác định phương cách kết nối với CSDL (Excel Workbook). Sau khi kết nối được rồi thì code của bạn mới thực hiện (execute) câu lệnh SQL qua kết nối và lấy về một recordset (hoặc update, insert gì đó).

Dạ. Em cảm ơn
Vướng của em ở đây đang là giữa số bit của VSTO addin khi build ra và câu lệnh kết nối.
1. Nếu VSTO Addin build ở dạng: Any CPU => thì chạy trên cả 32 bit và 64 bit office đều được. Tuy nhiên em bị vướng khi dùng câu lệnh kết nối cơ sở dữ liệu (02 vướng)
+ Số bit Office: Có khi người dùng sử dụng lúc thì excel 32 bit lúc thì dùng excel 64 bit
+ Loại dữ liêu: Có lúc chọn dữ liệu dạng .xls hoặc có lúc dùng .xlsx.
Mình có thể dùng câu lệnh nào để vừa dùng được trên cả 64 bit và 32 bit, vừa dùng được trên cả dữ liệu dạng xls và xlsx.
2. Nếu build addin ở dạng x86 hoặc x64 thì phải build riêng 2 bộ setup.

Hiện em muốn xử lý theo cách 1 nhưng chưa tìm được cách nào xử lý.

Bạn tham khảo trang này: https://www.connectionstrings.com/excel/

Cảm ơn anh/chị HeSanbi. Em thấy trên này nhiều phương án kết nối, em đang tìm hiểu ví dụ trên google để vận dụng vào ứng dụng của mình. Mong được anh/chị chỉ bảo thêm.

Nếu không nhầm thì không cần phân biệt xls hay xlsx, chỉ dùng ace12 là đủ rồi. Còn Microsoft Accesss data Engineer thực chất là ace12 đó. Nếu chưa cài office từ 2007 trở lên thì mới cần cài riêng cái đó.
Còn Jet 4.0 ra đời có lẽ cũng trên dưới 20 năm rồi nên không phù hợp với 64bit cũng không có gì là ngạc nhiên cả.

Dạ, em cảm ơn. Trường hợp này, chỉ tính cho Office 2007 trở lênh anh nhé. 2003 trở xuống là không sử dụng ạ.

Câu lệnh em đang dùng: Build dạng Any CPU thì chạy tốt trên office 32 bit (win 32), tốt trên office 32 bit (win 64), chạy trên Office 64 bit thì báo lỗi "Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine ".
Sửa từ: Provider=Microsoft.Jet.OLEDB.4.0 thành Provider=Microsoft.ACE.OLEDB.12.0 thì vẫn báo lỗi: "Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine "

Function ReadExcelFile(ByVal sheetName As String, ByVal pathExcel As String) As Data.DataTable
        Using conn As OleDb.OleDbConnection = New OleDb.OleDbConnection()
            Dim dt As Data.DataTable = New Data.DataTable()
            Dim Import_FileName As String = pathExcel
            Dim fileExtension As String = Path.GetExtension(Import_FileName)
            If fileExtension = ".xls" Then conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Import_FileName & ";" & "Extended Properties='Excel 8.0;HDR=YES;'"
            If fileExtension = ".xlsx" Then conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Import_FileName & ";" & "Extended Properties='Excel 12.0 Xml;HDR=YES;'"
            Using comm As OleDbCommand = New OleDbCommand()
                comm.CommandText = "Select * from [" & sheetName & "$]"
                comm.Connection = conn
                Using da As OleDbDataAdapter = New OleDbDataAdapter()
                    da.SelectCommand = comm
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Function

 

[VetMini]

...
Câu lệnh em đang dùng:

            If fileExtension = ".xls" Then conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Import_FileName & ";" & "Extended Properties='Excel 8.0;HDR=YES;'"
            If fileExtension = ".xlsx" Then conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Import_FileName & ";" & "Extended Properties='Excel 12.0 Xml;HDR=YES;'"

Trong code trên, bạn dùng cái đuôi để xác định phân loại.
Máy client vẫn có thể dùng Office 1010 (VBA7) mở file đời thượng cổ.

Trong ví dụ bài #3, tôi mách cho cách gọi thẳng Object Excel application để query trực tiếp phiên bản của nó (2003 hay 2007+). Chỉ khi nào máy client lắp nhiều phiên bản thì cách này mới thất bại.

Tuy nhiên cách query trực tiếp ấy vẫn chưa cho biết 64 hay 32 bit. Cách xét phiên bản bit rắc rối hơn nhiều. Bạn nên thử cách trên trước.
Nếu thực sự cần xét phiên bản bit thì dùng 1 trong 3 cách:
1. Ở code bài #3, thay vì đọc huộc tính Version của Excel.Application, bạn thử đọc thuộc tính ProductCode. Nếu cái ProductCode ấy có chứa một từ dạng p000 thì xét: 000 là x86 và 001 là x64
2. Viết 1 hàm trong VBA xét hằng số Win64 (phiên bản 64bit sẽ cho ra True). Ghi hàm này vào một file MeMyVBUtilities.xls nào đó. Code VB.Net gọi hàm này thì sẽ biết loại phiên bản của office. Nếu bạn dùng interop thì có thể tìm cách gọi qua interop mà khong phải lập file Utilities.
3. Viết 1 hàm VB, truy lùng cái registry key HKEY_LOCAL_MACHINE \Software\.... (bạn tự gú gồ cách này)

 

[vba_gpe]

Dạ em cảm ơn nhiều ạ.

Trong ví dụ bài #3, tôi mách cho cách gọi thẳng Object Excel application để query trực tiếp phiên bản của nó (2003 hay 2007+). Chỉ khi nào máy client lắp nhiều phiên bản thì cách này mới thất bại.

IIF( Val(DirectCast(CreateObject("Excel.Application"), Excel.Application).Version) >= 12, "ACE", "JET" )

Dạ. Cái này em đã biết được version của cái excel đang mở, trường hợp này mình chỉ xét >=12 thôi ạ. Trên VSTO addin hiện tại không support cho office 2003 đổ xuống ạ. Bây giờ mình chỉ phụ thuộc vào dạng dữ liệu cần lấy ở dạng nào ( .xls hay .xlsx.)

Tuy nhiên cách query trực tiếp ấy vẫn chưa cho biết 64 hay 32 bit. Cách xét phiên bản bit rắc rối hơn nhiều. Bạn nên thử cách trên trước.
Nếu thực sự cần xét phiên bản bit thì dùng 1 trong 3 cách:
1. Ở code bài #3, thay vì đọc huộc tính Version của Excel.Application, bạn thử đọc thuộc tính ProductCode. Nếu cái ProductCode ấy có chứa một từ dạng p000 thì xét: 000 là x86 và 001 là x64

Của em đang là office 32 bit, ra thế này:

Cái đoạn p000 hoặc 000 là nằm ở vị trí nào trong chuỗi này ạ.
Em tham khảo trên stackOverFlow, có đoạn xử lý chuỗi này cho ra kết quả office 32 bit, 64 bit nhưng chạy phải mất một ít thời gian để nó xử lý. (Em thấy hơi lâu). Không biết cả nhà mình có cách nào nhanh hơn không ạ.

Private IsExcel32Bit As Boolean = False
Private IsExcel64Bit As Boolean = False
Private ReadOnly STR_prdCodeDelimeter As Char = CChar("-")
Private Sub GetExcelBitness(ByVal exApp As Microsoft.Office.Interop.Excel.Application)
    Dim prdCode As String = exApp.ProductCode
    If Not String.IsNullOrEmpty(prdCode) AndAlso CInt(prdCode.Split(STR_prdCodeDelimeter)(3)(0).ToString) = 0 Then
        IsExcel32Bit = True
    ElseIf Not String.IsNullOrEmpty(prdCode) AndAlso CInt(prdCode.Split(STR_prdCodeDelimeter)(3)(0).ToString) = 1 Then
        IsExcel64Bit = True
    End If
End Sub

2. Viết 1 hàm trong VBA xét hằng số Win64 (phiên bản 64bit sẽ cho ra True). Ghi hàm này vào một file MeMyVBUtilities.xls nào đó. Code VB.Net gọi hàm này thì sẽ biết loại phiên bản của office. Nếu bạn dùng interop thì có thể tìm cách gọi qua interop mà khong phải lập file Utilities.
3. Viết 1 hàm VB, truy lùng cái registry key HKEY_LOCAL_MACHINE \Software\.... (bạn tự gú gồ cách này)

Em định quy trình thế này, nhờ anh/chị cho ý kiến thêm ạ:
B1. Lấy được version của Office hiện tại. (Em làm được).
B2. Lấy số bit của Office tương ứng version hiện tại căn cứ vào outlook registry theo khóa:

HKEY_LOCAL_MACHINE\Software\Microsoft\Office\"& currenversion &"\Outlook\Bitness

Link em tham khảo: https://stackoverflow.com/questions/2203980/detect-whether-office-is-32bit-or-64bit-via-the-registry
nếu x86 thì là 32, x64 là 64. Chỗ này nếu người dùng có cài outlook thì không sao, ko cài Outlook thì thế nào ạ?

3. Sau khi xác định được 32 bit office hay 64 bit offfice thì mình viết code riêng cho từng trường hợp.
Em đang bí chỗ này không biết viết sao cho từng trường hợp:

if isOff32bit then
   if dataxls then 'Data dạng .xls
    ....
  else 'xlsx
  .....
  end if
else
   if dataxls then
    ....
  else
  .....
  end if
end if