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

Akram.19

SOLVED CLOSED ACTIVE ANSWERED
User: Akram.19
Posted: on Jul 26, 2022 10:57 PM
Forum: Windows Basics
Answer: 1
Views: 4216

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

PrinceG

0 2

Reply
ANSWER
User: PrinceG
Replied: on Jul 28, 2022 08:39 AM
Report

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

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

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

C#

Company

Explore

Follow us

Contact Us