how to give password to excel file in mvc.net

Question

when excel generated in visual studio using mvc . generated excel file does not open without enterd password

Bhuvanesh Mohankumar · Answer

This code must helpful for you. protected void Page_Load(object sender, EventArgs e) { DataSetToExcel(GetDataSet(), false ); } private static DataSet GetDataSet() { DataSet ds = new DataSet(); System.Data.DataTable dt = new System.Data.DataTable( "Table" ); dt.Columns.Add( "Name" , Type.GetType( "System.String" )); dt.Columns.Add( "Address" , Type.GetType( "System.String" )); dt.Columns.Add( "Phone" , Type.GetType( "System.String" )); DataRow dr = dt.NewRow(); dr[ "Name" ] = "Balaji Selvarajan" ; dr[ "Address" ] = "Reddiyur" ; dr[ "Phone" ] = "000-000-0000" ; dt.Rows.Add(dr); dr = dt.NewRow(); dr[ "Name" ] = "Balaji S" ; dr[ "Address" ] = "Kattumannar Koil" ; dr[ "Phone" ] = "000-000-0000" ; dt.Rows.Add(dr); ds.Tables.Add(dt); return ds; } private static void DataSetToExcel(DataSet ds, Boolean generateIdentity) { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); xlApp.Visible = false ; Microsoft.Office.Interop.Excel.Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); for ( int k = 0; k { System.Data.DataTable dt = ds.Tables[k]; Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); ws.Name = dt.TableName; for ( int i = 0; i { for ( int j = 0; j { if (i == 0) ws.Cells[1, j + 1] = dt.Columns[j].ColumnName; ws.Cells[i + 2, j + 1] = (j == 0 && generateIdentity) ? (i + 1).ToString() : dt.Rows[i][j].ToString(); } } ws.Protect( "1" , true , true , true , true , true , true , true , true , true , true , true , true , true , true , true ); } wb.Protect( "my" , true , true ); wb.Password = "Test" ; wb.Close(); xlApp.Visible = true ; } Mark as answer, if helpful

Bikesh Srivastava · Answer

yes alice yang is correct

alice yang · Answer

Hi, You can use Spire.XLS. It allows generating excel from scratch, generating excel with data from database, datatable etc. You can also use it to open an ecrypted excel document with correct password like the following code: //Open password protected workbook Workbook wb = new Workbook(); wb.OpenPassword = "oldpassword" ; wb.LoadFromFile(@ "C:\Users\Administrator\Desktop\Encrypted.xlsx" ); //unprotect workbook //wb.UnProtect(); //reset password //wb.Protect("newpassword"); //Save wb.SaveToFile(@ "C:\Users\Administrator\Desktop\Encrypted.xlsx" , ExcelVersion.Version2010);