I am trying to export Excel and make it password protected. My code is below. But I get an error

Question

I am trying to export excel and make it password protected.

My code is below.

But I get the error:

Excel has completed checking and restoring the file level.

Some parts of this book may have been repaired or discarded.

I DO NOT KNOW THAT I AM WRONG.

In case I do this without saving the As string for the package, this error does not appear.

In my controller:

[HttpGet] public FileStreamResult ExportToExcel() { _objService = new ServiceBAL(); List<ReconcilationEntity> Objmodel = new List<ReconcilationEntity>(); Objmodel = _objService.GetCreditsudharLeads(); String URL = string.Empty; if (!Directory.Exists(Server.MapPath("~/TempExcel"))) { System.IO.Directory.CreateDirectory(Server.MapPath("~/TempExcel")); } String Filepath = Server.MapPath("~/TempExcel"); string date = DateTime.Now.ToShortDateString().Replace("/", "_") + "_" + DateTime.Now.ToShortTimeString().Replace(" ", "_").Replace(":", "_").Trim(); String FileName = "Creditsudhar_" + date + ".xlsx"; Filepath = Filepath + "\\" + FileName; string[] columns = { "AffName", "AffPhone", "AffEmail", "ProductName", "ContactName", "Status", "CreatedOn", "Commission", "IsCommissionPaid", "Accountname", "AccountNumber", "BankName", "BankBranch", "IFSCCode", "PanNumber" }; var file = ExcelExportHelper.ExportExcel(ExcelExportHelper.ListToDataTable(Objmodel), Filepath, "Creditsudhar Reconcillation Sheet " + DateTime.Now.ToShortDateString(), true, columns); var memStream = new MemoryStream(file); return this.File(memStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", FileName); } public static string ExcelContentType { get { return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; } } public static DataTable ListToDataTable<T>(List<T> data) { PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); DataTable dataTable = new DataTable(); for (int i = 0; i < properties.Count; i++) { PropertyDescriptor property = properties[i]; dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType); } object[] values = new object[properties.Count]; foreach (T item in data) { for (int i = 0; i < values.Length; i++) { values[i] = properties[i].GetValue(item); } dataTable.Rows.Add(values); } return dataTable; } public static byte[] ExportExcel(DataTable dataTable, String Filepath, string heading = "", bool showSrNo = false, params string[] columnsToTake) { string fullPath = string.Empty; byte[] ret; DeleteUploadedFile(Filepath); String result = String.Empty; using (ExcelPackage package = new ExcelPackage()) { ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(String.Format("{0} Data", heading)); int startRowFrom = String.IsNullOrEmpty(heading) ? 1 : 3; if (showSrNo) { DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int)); dataColumn.SetOrdinal(0); int index = 1; foreach (DataRow item in dataTable.Rows) { item[0] = index; index++; } } // add the content into the Excel file workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true); // autofit width of cells with small content int columnIndex = 1; foreach (DataColumn column in dataTable.Columns) { try { ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex]; int maxLength = columnCells.Max(cell => cell.Value.ToString().Count()); if (maxLength < 150) { workSheet.Column(columnIndex).AutoFit(); } columnIndex++; } catch (Exception ex) { if (!(ex is System.Threading.ThreadAbortException)) { //Log other errors here } } } // format header - bold, yellow on black using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count]) { r.Style.Font.Color.SetColor(System.Drawing.Color.White); r.Style.Font.Bold = true; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad")); } // format cells - add borders using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count]) { r.Style.Border.Top.Style = ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; r.Style.Border.Left.Style = ExcelBorderStyle.Thin; r.Style.Border.Right.Style = ExcelBorderStyle.Thin; r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black); } // removed ignored columns for (int i = dataTable.Columns.Count - 1; i >= 0; i--) { if (i == 0 && showSrNo) { continue; } if (!columnsToTake.Contains(dataTable.Columns[i].ColumnName)) { workSheet.DeleteColumn(i + 1); } } if (!String.IsNullOrEmpty(heading)) { workSheet.Cells["A1"].Value = heading; workSheet.Cells["A1"].Style.Font.Size = 20; workSheet.InsertColumn(1, 1); workSheet.InsertRow(1, 1); workSheet.Column(1).Width = 5; } System.IO.FileInfo fileinfo2 = new System.IO.FileInfo(Filepath); DeleteUploadedFile(Filepath); workSheet.Protection.SetPassword("myPassword"); workSheet.Protection.IsProtected = true; workSheet.Protection.AllowSelectUnlockedCells = false; workSheet.Protection.AllowSelectLockedCells = false; package.SaveAs(fileinfo2, "myPassword"); ret = package.GetAsByteArray(); return ret; } } public static void DeleteUploadedFile(String filePath) { try { if (System.IO.File.Exists(filePath)) { System.IO.File.Delete(filePath); } } catch (Exception ex) { } } public static byte[] ExportExcel<T>(List<T> data, String Filepath, string Heading = "", bool showSlno = false, params string[] ColumnsToTake) { return ExportExcel(ListToDataTable<T>(data), Filepath, Heading, showSlno, ColumnsToTake); } 

Answer 1

The answer specified in SaveAs closes the package, so the correct steps will return the saved file as an array instead of using GetAsByteArray. Or just use GetAsByteArray (passwords) without SaveAs.