I am trying to export an excel and make it password protected. My code is given below.But i am getting error

Question

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

My code is given below.

But i am getting error:

Excel completed file level validation and repair.

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

I DON'T KNOW WHAT I AM DOING WRONG .

In-case i do it without the save As line for package then this error doesn't 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

An answer mentioned SaveAs close the package, so the correct steps will be returning the saved file as array instead of using GetAsByteArray afterwards. Or simply use GetAsByteArray(passwords) without SaveAs.