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.