This project has moved. For the latest updates, please go here.

[Solved] Write password protected xlsx file

Apr 27, 2012 at 2:38 PM
Edited Apr 27, 2012 at 2:40 PM

Hi there,

i create a xlsx file, add some data, and save this file with the password.
Here is my code for testing (VB Express 2010):

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim p As ExcelPackage
Dim w As ExcelWorksheet = Nothing
If IO.File.Exists("Test.xlsx") Then
	p = New ExcelPackage(New IO.FileInfo("Test.xlsx"), True, "123456")
	w = p.Workbook.Worksheets(1)
	w.Cells(1, 2).Value = "Entry2"
	Me.Text = w.Cells(1, 2).Value.ToString

	p.Save("123456")
Else
	p = New ExcelPackage(New IO.FileInfo("Test.xlsx"))
	w = p.Workbook.Worksheets.Add("test"
	w.Cells(1, 1).Value = "Entry1"

	p.Save("123456")
End If
End Sub
What it does:
If we start the app the first time, the xlsx file is created and the value from cell 1,1 (A1) is set to "Entry1".
Now it is saved with the password 123456.
Now we close the app and start it again.
The xlsx file exist and we set cell B1 (1,2) to "Entry2". Then we read the same cell (B1) and set the window title to the cell value and save the package with the password 123456.
As we can see the window title changed to "Entry2" but if i open the xlsx file there is only "Entry1" in cell A1.



Is this a Bug ?
Editor
Apr 27, 2012 at 3:09 PM

I don't know what "useStream" in ExcelPackage constructors means, but if you change to false, works fine. I THINK, "useStream" means if it will update your template directly or not.

 

        /// <summary>
        /// Create a new instance of the ExcelPackage class based on a existing template.
        /// </summary>
        /// <param name="template">The name of the Excel template to use as the basis of the new Excel file</param>
        /// <param name="useStream">if true use a stream. If false create a file in the temp dir with a random name</param>
        /// <param name="password">Password to decrypted the template</param>
        public ExcelPackage(FileInfo template, bool useStream, string password)
        {
            Init();
            CreateFromTemplate(template, password);
            if (useStream == false)
            {
                File = new FileInfo(Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx");
            }
        }

 

May 2, 2012 at 7:38 AM
Edited May 2, 2012 at 2:48 PM

Thx for your tip mrxrsd,

i solved the problem by using the following code:

public ExcelPackage(FileInfo newFile, string password)
{
Init();
File = newFile;
ConstructNewFile(new MemoryStream(), password);
}
Jan 30, 2014 at 1:11 PM
Edited Jan 30, 2014 at 1:20 PM
HI , i am using EPPlus tool for exporting Datatable into excel sheet in C#. Following is the code that i have written for exporting table into excel sheet
using (ExcelPackage pck = new ExcelPackage())
                {
                    if (dt != null)
                    {
                        if (extension == ".xlsx")
                            fileName = "Test_" + DateTime.Now.ToString("yyyMMddHHmmss") + ".xlsx";
                        else if (extension == ".xls")
                            fileName = "Test_" + DateTime.Now.ToString("yyyMMddHHmmss") + ".xls";

                        fileName = path + fileName;


                        pck.File = (new FileInfo(fileName));


                        //Create the worksheet
                        ExcelWorksheet ws = pck.Workbook.Worksheets.Add(fileName);

                        //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                        ws.Cells["A2"].LoadFromDataTable(dt, true);
                        //Set a password for the workbookprotection
                        ws.Protection.SetPassword("11");

                        //Encrypt with no password
                        pck.Save(fileName);


                        
                    }
                    
                }
There are no errors during execution and even excel file is generating but when i am opening excel sheet a prompt message is coming requesting for 'password'.
I am giving password as 11 but still i am getting message like invalid password. Please help me ASAP.

Thanks,
bhadra