Save Excel file as Read Only with Password in Windows Application using C# and VB.Net

Akram.19
 
on Jul 26, 2022 10:57 PM
440 Views

I want to save excel as read only and it must have a password credential.

Imports System.IO
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Imports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat

Public Class Form1
    Private SQL As New SQLControl
    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        TextBox1.Text = TextBox1.Text + 1
        If TextBox1.Text = 10 Then
            Dim constr As String = "Server=SDVSDVSDVSDV-9EKQ5QI;Database=DVDVSDVSDV;User=DVSDVDV;Pwd=123456"
            Using con As SqlConnection = New SqlConnection(constr)
                Using cmd As SqlCommand = New SqlCommand("SELECT *FROM Report1")
                    Dim sda As SqlDataAdapter = New SqlDataAdapter
                            cmd.Connection = con : con.Open()
                            sda.SelectCommand = cmd
                            Dim dt As DataTable = New DataTable
                            sda.Fill(dt)

                    If dt.Rows.Count > 0 Then

                        Dim path As String = "C:\Users\AKRAM\Desktop\Reporting\"
                        If Not Directory.Exists(path) Then
                            Directory.CreateDirectory(path)
                        End If

                        Dim xlAppToExport As New Excel.Application
                        xlAppToExport.Workbooks.Add()
                        Dim xlWorkSheetToExport As Excel.Worksheet
                        xlWorkSheetToExport = CType(xlAppToExport.Sheets("Sheet1"), Excel.Worksheet)


                        Dim path1 As String = "C:\Users\AKRAM\Desktop\Reporting\"
                        If Not Directory.Exists(path1) Then
                            Directory.CreateDirectory(path1)
                        End If
                        Dim xlAppToExport1 As New Excel.Application
                        xlAppToExport1.Workbooks.Add()
                        Dim xlWorkSheetToExport1 As Excel.Worksheet
                        xlWorkSheetToExport1 = CType(xlAppToExport1.Sheets("Sheet1"), Excel.Worksheet)


                        Dim iRowCnt As Integer = 4
                        With xlWorkSheetToExport
                            .Cells(1, 1).value = "Raw_Tank_A"
                            .Cells(1, 1).FONT.NAME = "Calibri"
                            .Cells(1, 1).Font.Bold = True
                            .Cells(1, 1).Font.Size = 20
                            .Range("A1:G1").MergeCells = True
                            .Cells(iRowCnt - 1, 2).value = "id"
                            .Cells(iRowCnt - 1, 3).value = "username"
                            .Cells(iRowCnt - 1, 4).value = "tank_name"
                            .Cells(iRowCnt - 1, 5).value = "material_name"
                            .Cells(iRowCnt - 1, 6).value = "Transaction_Type"
                            .Cells(iRowCnt - 1, 7).value = "Transaction_Value"
                            .Cells(iRowCnt - 1, 8).value = "Balance"
                            .Cells(iRowCnt - 1, 9).value = "created_at"

                            For i = 0 To dt.Rows.Count - 1
                                .Cells(iRowCnt, 2).value = dt.Rows(i).Item("id")
                                .Cells(iRowCnt, 3).value = dt.Rows(i).Item("username")
                                .Cells(iRowCnt, 4).value = dt.Rows(i).Item("tank_name")
                                .Cells(iRowCnt, 5).value = dt.Rows(i).Item("material_name")
                                .Cells(iRowCnt, 6).value = dt.Rows(i).Item("Transaction_Type")
                                .Cells(iRowCnt, 7).value = dt.Rows(i).Item("Transaction_Value")
                                .Cells(iRowCnt, 8).value = dt.Rows(i).Item("Balance")
                                .Cells(iRowCnt, 9).value = dt.Rows(i).Item("created_at")

                                iRowCnt = iRowCnt + 1
                            Next

                            xlAppToExport.ActiveCell.Worksheet.Cells(4, 1).AutoFormat( _
                                ExcelAutoFormat.xlRangeAutoFormatList3)
                        End With


                        xlWorkSheetToExport.SaveAs(path & "Raw.xlsx")

                        xlAppToExport.Workbooks.Close() : xlAppToExport.Quit()
                        xlAppToExport = Nothing : xlWorkSheetToExport = Nothing


                    End If
                        End Using
                    End Using
        End If
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Timer1.Enabled = True
        Timer2.Enabled = True
    End Sub

    Private Sub Timer2_Tick(sender As Object, e As EventArgs) Handles Timer2.Tick
        Label1.Text = DateTime.Now.ToString("dd-MMM-yyyy  hh:mm:ss tt")
    End Sub    
End Class

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Jul 28, 2022 08:39 AM

Hi Akram.19

Please refer below sample. You need to set the Password to the workbook.

For readonly use the SaveAs method and set the readonly to true.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Namespace 

C#

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using ClosedXML.Excel;

VB.Net

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Data
Imports System.Data.SqlClient

Code

C#

public Form1()
{
    InitializeComponent();
    this.BindDataGridView();
}
private void BindDataGridView()
{
    string str = "data source=.\\SQL2019;initial catalog=Northwind;user id=sa;password=pass@123;";
    DataTable dt = new DataTable();
    using (SqlConnection con = new SqlConnection(str))
    {
        SqlDataAdapter sda = new SqlDataAdapter("SELECT TOP 5 CustomerID,ContactName,Address,City,Country FROM Customers", con);
        sda.Fill(dt);
        this.dataGridView1.DataSource = dt;
    }
}

private void btnExportExcel_Click(object sender, EventArgs e)
{
    string str = "data source=.\\SQL2019;initial catalog=Northwind;user id=sa;password=pass@123;";
    DataTable dt = new DataTable();
    using (SqlConnection con = new SqlConnection(str))
    {
        SqlDataAdapter sda = new SqlDataAdapter("SELECT TOP 5 CustomerID,ContactName,Address,City,Country FROM Customers", con);
        sda.Fill(dt);
    }

    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    string sourcePath = "D:\\Raw.xlsx";
    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(sourcePath);

    string datestr = DateTime.Now.ToShortDateString();
    string destinationPath = "D:\\" + "Name_" + datestr.Replace('/', '_') + ".xls";
    string quote = "Test Name" + "_" + datestr;
    string salesman = "test@test.com".ToUpper();
    string customer = "Test Name".ToUpper();

    int currentSheet = 1;
    int cell = 0;
    int rowsToStart = 19;
    for (int row = 0; row < dt.Rows.Count; row++)
    {
        if (currentSheet <= xlWorkBook.Sheets.Count)
        {
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[1];
        }
        else
        {
            var xlSheets = xlWorkBook.Sheets as Excel.Sheets;
            xlWorkSheet = (Excel.Worksheet)xlSheets.Add(misValue, xlSheets[xlWorkBook.Sheets.Count]);
            xlWorkSheet.Name = "Sheet" + currentSheet.ToString();
        }
        xlWorkSheet.get_Range("C10", "E10").Value2 = salesman;
        xlWorkSheet.get_Range("C12", "E12").Value2 = customer;
        xlWorkSheet.get_Range("C12", "E12").Font.Bold = true;
        xlWorkSheet.get_Range("C12", "E12").Font.Bold = true;
        xlWorkSheet.get_Range("H12", "I12").Value2 = DateTime.Now;
        xlWorkSheet.get_Range("H10", "I10").Value2 = quote;

        for (int column = 0; column < dt.Columns.Count; column++)
        {
            xlWorkSheet.Cells[cell + rowsToStart, column + 1] = "'" + dt.Rows[row][column].ToString();
        }
        cell++;
    }
    if (System.IO.File.Exists(destinationPath))
    {
        System.IO.File.Delete(destinationPath);
    }
    xlWorkBook.Password = "123";
    xlWorkBook.SaveAs(destinationPath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, true, misValue,
        Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();
    MessageBox.Show("Excel file created , you can find the file in " + destinationPath);
}

VB.Net

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim str As String = "data source=.\SQL2019;initial catalog=Northwind;user id=sa;password=pass@123;"
    Dim dt As DataTable = New DataTable()

    Using con As SqlConnection = New SqlConnection(str)
        Dim sda As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 5 CustomerID,ContactName,Address,City,Country FROM Customers", con)
        sda.Fill(dt)
    End Using

    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim sourcePath As String = "D:\Raw.xlsx"
    xlApp = New Excel.Application()
    xlWorkBook = xlApp.Workbooks.Add(sourcePath)
    Dim datestr As String = DateTime.Now.ToShortDateString()
    Dim destinationPath As String = "D:\" & "Name_" & datestr.Replace("/"c, "_"c) & ".xls"
    Dim quote As String = "Test Name" & "_" & datestr
    Dim salesman As String = "test@test.com".ToUpper()
    Dim customer As String = "Test Name".ToUpper()
    Dim currentSheet As Integer = 1
    Dim cell As Integer = 0
    Dim rowsToStart As Integer = 19

    For row As Integer = 0 To dt.Rows.Count - 1
        If currentSheet <= xlWorkBook.Sheets.Count Then
            xlWorkSheet = CType(xlWorkBook.Worksheets(1), Excel.Worksheet)
        Else
            Dim xlSheets = TryCast(xlWorkBook.Sheets, Excel.Sheets)
            xlWorkSheet = CType(xlSheets.Add(misValue, xlSheets(xlWorkBook.Sheets.Count)), Excel.Worksheet)
            xlWorkSheet.Name = "Sheet" & currentSheet.ToString()
        End If

        xlWorkSheet.get_Range("C10", "E10").Value2 = salesman
        xlWorkSheet.get_Range("C12", "E12").Value2 = customer
        xlWorkSheet.get_Range("C12", "E12").Font.Bold = True
        xlWorkSheet.get_Range("C12", "E12").Font.Bold = True
        xlWorkSheet.get_Range("H12", "I12").Value2 = DateTime.Now
        xlWorkSheet.get_Range("H10", "I10").Value2 = quote

        For column As Integer = 0 To dt.Columns.Count - 1
            xlWorkSheet.Cells(cell + rowsToStart, column + 1) = "'" & dt.Rows(row)(column).ToString()
        Next

        cell += 1
    Next

    If System.IO.File.Exists(destinationPath) Then
        System.IO.File.Delete(destinationPath)
    End If

    xlWorkBook.SaveAs(destinationPath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, True, misValue, _
                      Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
    xlWorkBook.Password = "123"
    xlWorkBook.Close(True, misValue, misValue)
    xlApp.Quit()
    MessageBox.Show("Excel file created , you can find the file in " & destinationPath)
End Sub

Private Sub BindDataGridView()
    Dim str As String = "data source=.\SQL2019;initial catalog=Northwind;user id=sa;password=pass@123;"
    Dim dt As DataTable = New DataTable()

    Using con As SqlConnection = New SqlConnection(str)
        Dim sda As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 5 CustomerID,ContactName,Address,City,Country FROM Customers", con)
        sda.Fill(dt)
        DataGridView1.DataSource = dt
    End Using
End Sub

Screenshot