Click here to Skip to main content
15,116,815 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
So I have a small app that exports Data from a Mysql page to a Excel file.

But I have a problem that the file must be protected with a password, since it has sensitive data that not everyone can see, but every time I save a new instance of the file it removes the password, so I have to force the App to implement a password but I doesn't work.

I adapted the code that creates the Excel file from a colleague that do not work in my office anymore and it is like this:

<pre>Imports System.Data.OleDb
Imports System.IO
Imports System.Text

Module XlssHelper

    Event OnTotalRowsDetected(RowCount As Integer)
    Event OnRowExported(RowIndex As Integer)

    Const CON_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}'; Extended Properties='Excel 8.0;HDR=YES'"

    Function GetConnectionString(fileName As String, mode As String) As String
        Dim imex As String = CType(IIf(mode.ToLower = "write", "2", "1"), String)
        Return String.Format(CON_STRING, fileName, mode, imex)
    End Function

    Private Structure ExcelDataTypes
        Public Const NUMBER As String = "NUMBER"
        Public Const DATETIME As String = "DATETIME"
        Public Const TEXT As String = "TEXT"
    End Structure

    Private Structure NETDataTypes
        Public Const _Short As String = "Int16"
        Public Const _Int As String = "Int32"
        Public Const _Long As String = "Int64"
        Public Const _String As String = "String"
        Public Const _Date As String = "DateTime"
        Public Const _Bool As String = "Boolean"
        Public Const _Decimal As String = "Decimal"
        Public Const _Double As String = "Double"
        Public Const _Float As String = "Float"
    End Structure

    '-----

    Sub ExportDataSetToExcel(ByVal dataSet As DataSet, filePath As String)

        Dim result() As Byte = Nothing

        Try

            Dim fileTemp As String = My.Computer.FileSystem.GetTempFileName
            If IsNothing(fileTemp) OrElse fileTemp.Length = 0 Then
                Throw New Exception("Connot write on temp folder. Check folder permissions")
            End If

            fileTemp &= ".xls"

            If dataSet IsNot Nothing AndAlso dataSet.Tables.Count > 0 Then
                Dim sConn As String = GetConnectionString(fileTemp, "Write")

                Using connection As OleDbConnection = New OleDbConnection(sConn)

                    connection.Open()

                    For Each dt As DataTable In dataSet.Tables

                        Dim strCreateTableStruct As String = BuildCreateTableCommand(dt)

                        If String.IsNullOrEmpty(strCreateTableStruct) Then Return

                        Using command As OleDbCommand = New OleDbCommand(strCreateTableStruct, connection)
                            command.ExecuteNonQuery()
                            Dim totRows As Integer = dt.Rows.Count - 1

                            RaiseEvent OnTotalRowsDetected(totRows)

                            For rowIndex As Integer = 0 To dt.Rows.Count - 1
                                Using command1 As OleDbCommand = New OleDbCommand(BuildInsertCommand(dt, rowIndex), connection)
                                    command1.ExecuteNonQuery()
                                End Using
                                RaiseEvent OnRowExported(rowIndex)
                            Next
                        End Using

                    Next

                End Using

            End If


            If IO.File.Exists(fileTemp) Then
                result = IO.File.ReadAllBytes(fileTemp)
                IO.File.Delete(fileTemp)
            End If

            If result IsNot Nothing AndAlso result.Length > 0 Then
                Using Fs As FileStream = New FileStream(filePath, FileMode.OpenOrCreate)
                    Fs.Write(result, 0, result.Length)
                    'OrCreate
                End Using
            End If

        Catch eX As Exception
            MsgBox(eX.ToString)

        End Try

    End Sub

    '-----

    Private Function BuildCreateTableCommand(ByVal dataTable As DataTable) As String
        Dim sb As StringBuilder = New StringBuilder()
        Dim dataTypeList As Dictionary(Of String, String) = BuildExcelDataTypes()
        If dataTable.Columns.Count <= 0 Then Return Nothing
        sb.AppendFormat("CREATE TABLE [{0}] (", BuildExcelSheetName(dataTable))

        For Each col As DataColumn In dataTable.Columns
            Dim type As String = ExcelDataTypes.TEXT

            If dataTypeList.ContainsKey(col.DataType.Name.ToString().ToLower()) Then
                type = dataTypeList(col.DataType.Name.ToString().ToLower())
            End If

            sb.AppendFormat("[{0}] {1},", col.Caption.Replace(" "c, "_"c), type)
        Next

        sb = sb.Replace(","c, ")"c, sb.ToString().LastIndexOf(","c), 1)
        Return sb.ToString()
    End Function

    '-----

    Private Function BuildInsertCommand(ByVal dataTable As DataTable, ByVal rowIndex As Integer) As String
        Dim sb As StringBuilder = New StringBuilder()
        sb.AppendFormat("INSERT INTO [{0}$](", BuildExcelSheetName(dataTable))

        For Each col As DataColumn In dataTable.Columns
            sb.AppendFormat("[{0}],", col.Caption.Replace(" "c, "_"c))
        Next

        sb = sb.Replace(","c, ")"c, sb.ToString().LastIndexOf(","c), 1)
        sb.Append("VALUES (")

        For Each col As DataColumn In dataTable.Columns
            Dim type As String = col.DataType.ToString()
            Dim strToInsert As String = dataTable.Rows(rowIndex)(col).ToString().Replace("'", "''")
            '
            sb.AppendFormat("'{0}',", strToInsert)
        Next

        sb = sb.Replace(","c, ")"c, sb.ToString().LastIndexOf(","c), 1)
        Return sb.ToString()
    End Function

    '-----

    Private Function BuildExcelSheetName(ByVal dataTable As DataTable) As String
        Dim retVal As String = dataTable.TableName
        If dataTable.ExtendedProperties.ContainsKey("test") Then retVal = dataTable.ExtendedProperties("test").ToString()
        Return retVal.Replace(" "c, "_"c)
    End Function

    '-----

    Private Function BuildExcelDataTypes() As Dictionary(Of String, String)
        Dim dataTypeLookUp As Dictionary(Of String, String) = New Dictionary(Of String, String) From {
                {NETDataTypes._Short, ExcelDataTypes.NUMBER},
                {NETDataTypes._Int, ExcelDataTypes.NUMBER},
                {NETDataTypes._Long, ExcelDataTypes.NUMBER},
                {NETDataTypes._String, ExcelDataTypes.TEXT},
                {NETDataTypes._Date, ExcelDataTypes.DATETIME},
                {NETDataTypes._Bool, ExcelDataTypes.TEXT},
                {NETDataTypes._Decimal, ExcelDataTypes.NUMBER},
                {NETDataTypes._Double, ExcelDataTypes.NUMBER},
                {NETDataTypes._Float, ExcelDataTypes.NUMBER}
            }
        Return dataTypeLookUp

    End Function

End Module


To be honest I don't fully understands what every part of the code does, but does the trick of creating a Excel file.

What I have tried:

Since I don't know how to adapt the code to create the file with a password I've tried a small snipped of code that calls and "edits" the file, like this:

Imports Spire.Xls
Module Excel_Security

    Sub SecureExcel(filePath As String)

        Dim book As New Workbook()
        book.LoadFromFile(filePath)
        'Protect Workbook
        book.Protect("****")
        'Save and Launch
        book.SaveToFile("BDP.xlsx", ExcelVersion.Version2010)
    End Sub

End Module


But the code compiles without error, but doesn't implement the password and everyone can see the file.
What should I do? I only have 2 months of VB .Net on me so I'm new to this!

Another bonus question is that the first code only creates .xls and whent i change to .xlsx it doesnt compile.
Posted
Updated 9-Jun-21 2:48am
Comments
Richard Deeming 9-Jun-21 6:03am
   
Spire.Xls is a commercial product. If you have a license for it, then ask their support team for assistance.

NB: It's a product which is on the "banned list" here, since they keep posting spam promoting it, rather than paying the site for advertising.
Rwolf27 9-Jun-21 6:23am
   
Oh sorry,I didn't know really. Well i don't have a license since i though this was a free product (it was on a recommended list in my office from the same colleague), so that explains the reason it doesn't work, maybe.
Rwolf27 9-Jun-21 6:35am
   
Is there an alternative way to modify the Excel file to incorporate a Password ?
CHill60 9-Jun-21 6:18am
   
Regarding your "bonus question" - what is the compile error and on which line?
Rwolf27 9-Jun-21 6:28am
   
Its an extense error but its something like this: "System.Data.OleDb.OleDbException (0x80004005): Is not possible to update. Data base or Object ist only for reading, at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Manage_prod_priac.XlssHelper.ExportDataSetToExcel(DataSet dataSet, String filePath) in .vb:line 55

This StackOverflow thread suggests you can use the OpenOffice XML SDK[^] to create a password protected Excel workbook:
Excel File Password Protection with Open XML SDK - Stack Overflow[^]
   
Comments
Rwolf27 9-Jun-21 6:46am
   
That code is in C# ?
Richard Deeming 9-Jun-21 6:54am
   
Yes, the code in that solution is in C#.
Rwolf27 9-Jun-21 6:47am
   
In the FileStream (in the code) is not possible to incorporate a password?
Richard Deeming 9-Jun-21 6:57am
   
I've not seen any way to create a password-protected Excel file via OLEDB.
VB
If IO.File.Exists(fileTemp) Then
    result = IO.File.ReadAllBytes(fileTemp)
    IO.File.Delete(fileTemp)
End If

If result IsNot Nothing AndAlso result.Length > 0 Then
    Using Fs As FileStream = New FileStream(filePath, FileMode.OpenOrCreate)
        Fs.Write(result, 0, result.Length)
        'OrCreate
    End Using
End If

You could replace this with a simple File.Copy statement. But why not just write to the correct Excel file in the first place?
   
Comments
Rwolf27 9-Jun-21 9:33am
   
Thansk for the reply, but I'm sorry, I didn't understand the question you made.
Richard MacCutchan 9-Jun-21 9:57am
   
You save the updated Excel file to a temporary file, and then use a streamreader to read it back into memory, and a streamwriter to write that memory data to the output file. Why not just use the File.Copy method provided? Or save the updated information direct to the output file.
Richard MacCutchan 9-Jun-21 10:00am
   
Incidentally if you really need password protection on the file then you must use the Excel Interop classes.
Rwolf27 9-Jun-21 10:02am
   
Every Time I use the Interop I got errors of System.Runtime.InteropServices.COMException: 'Retrieving the COM class factory for component with CLSID {00020819-0000-0000-C000-000000000046} failed due to the following error: 80040154 Classe não registada (0x80040154 (REGDB_E_CLASSNOTREG)).'
Richard MacCutchan 9-Jun-21 10:17am
   
That tells you that you need to install Office Excel in your system.
Rwolf27 9-Jun-21 10:18am
   
Yes I know, but I have the Office installed, its a basic office procedure so I don't know what's the reason.
Richard MacCutchan 9-Jun-21 10:49am
   
That CLSID is the same one that I have. As far as I am aware that is installed automatically with Office.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900