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);