将Excel文件导入网格 [英] Import Excel File into Grid

问题描述

大家好，

当我将此代码用于从Excel工作表导入数据时，出现错误无法解密文件". Excel工作表具有密码保护.一个没有密码保护的简单Excel工作表已成功导入.

Hi All,

I get error " could not decrypt file" when i use this code for import data from Excel sheet. The excel sheet have password protected. A simple Excel sheet which not have a password protected is import successful.

Try
            Dim ImportCon As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data source=''" & txtFilePath.Text.Trim & "'';Extended Properties=Excel 8.0")
            Dim ImportCmd As New OleDbCommand("select * from [sheet1$]", ImportCon)
            ''Dim ImportCmd As New OleDbCommand("SELECT * INTO [MS Access;DATABASE=" & txtFilePath.Text & ";PWD=theagereport].[tblNEW] FROM [Sheet1$]", ImportCon)

            Dim ImportAdp As New OleDbDataAdapter(ImportCmd)
            ImportAdp.TableMappings.Add("Table", "InvoiceDue")
            Dim ImportDS As New DataSet
            ImportAdp.Fill(ImportDS)
            grdInvoiceDueData.DataSource = ImportDS.Tables(0)
        Catch ex As Exception
            KryptonMessageBox.Show("Import Error:-" + ex.Message, "Update Invoice Due", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

请帮帮我.

在此先感谢.

Please help me.

Thanks in Advance.

推荐答案

，ImportCon) "将ImportCmd设置为新的OleDbCommand("SELECT * INTO [MS Access; DATABASE ="& txtFilePath.Text&"PWD = theagereport].[tblNEW]来自[Sheet1

", ImportCon) ''Dim ImportCmd As New OleDbCommand("SELECT * INTO [MS Access;DATABASE=" & txtFilePath.Text & ";PWD=theagereport].[tblNEW] FROM [Sheet1

，ImportCon) 昏暗的ImportAdp作为新的OleDbDataAdapter(ImportCmd) ImportAdp.TableMappings.Add("Table"，"InvoiceDue") 昏暗的ImportDS作为新数据集 ImportAdp.Fill(ImportDS) grdInvoiceDueData.DataSource = ImportDS.Tables(0) 异常捕获 KryptonMessageBox.Show(导入错误:-" +例如消息，更新发票到期"，MessageBoxButtons.OK，MessageBoxIcon.Error) 结束尝试

", ImportCon) Dim ImportAdp As New OleDbDataAdapter(ImportCmd) ImportAdp.TableMappings.Add("Table", "InvoiceDue") Dim ImportDS As New DataSet ImportAdp.Fill(ImportDS) grdInvoiceDueData.DataSource = ImportDS.Tables(0) Catch ex As Exception KryptonMessageBox.Show("Import Error:-" + ex.Message, "Update Invoice Due", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try

请帮帮我.

在此先谢谢您.

Please help me.

Thanks in Advance.

如果OleDb类不适用于受密码保护的Excel文件，则可以使用代码作为其解决方案.

在模块顶部导入Excel = Microsoft.Office.Interop.Excel''
''为excel 11.0对象libray添加引用
''向Office 11.0女士libray对象添加引用

If the OleDb classes won''t work with password protected Excel files u can use the code for its solution.

Imports Excel = Microsoft.Office.Interop.Excel '' at the top of the module
'' add refference to Ms excel 11.0 object libray
'' add refference to Ms office 11.0 object libray

Private Sub Button3_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

       Dim xlsApp As Excel.Application
       Dim xlsbook As Excel.Workbook
       Dim xlsSheet As Excel.Worksheet
       Dim m_Rw As Integer
       Dim i As Integer
       Dim j As Integer

       xlsApp = CreateObject("Excel.Application")
       xlsApp.Workbooks.Open("c:\Myfile.xls", , , ,My excelfile Password)
       xlsbook = xlsApp.ActiveWorkbook
       xlsSheet = xlsbook.Sheets(1)

       ' insert column headers...
       dataGridview1.ColumnCount = xlsSheet.UsedRange.Columns.Count
       dataGridview1.ColumnHeadersVisible = True
       For i = 0 To xlsSheet.UsedRange.Columns.Count - 1
           dataGridview1.Columns(i).Name = xlsSheet.Cells(1, i + 1).Value.ToString
       Next

       ' insert rows and data to its cells....
       Dim var1(xlsSheet.UsedRange.Columns.Count) As String ' array for consisting cell value
       m_Rw = 0
       For i = 2 To xlsSheet.UsedRange.Rows.Count - 1
           dataGridview1.Rows.Add()
           For j = 0 To xlsSheet.UsedRange.Columns.Count - 1

               If xlsSheet.Cells(i, j + 1).Value = Nothing Then
                   var1(j) = ""
               Else
                   var1(j) = xlsSheet.Cells(i, j + 1).Value.ToString
               End If

               dataGridview1.Item(j, m_Rw).Value = var1(j)

           Next j
           m_Rw = m_Rw + 1
       Next i

       xlsSheet = Nothing  'disconnect from the Worksheet
       xlsbook = Nothing
       xlsApp.Quit()       'Close (and disconnect from) Excel
       xlsApp = Nothing

       Me.Cursor = Cursors.Default

   End Sub

''请回复.是否工作，谢谢...

'' please reply.Working or not, Thanks...

这篇关于将Excel文件导入网格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！