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

CodeN

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

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

I am trying to write a simple smart contract in remix ide and getting encountered by a generic error again and again. Below is my code and error

i am trying to create foreign keys but i got error 1822 .. please see my code below

I am trying to make simple game Tower of Hanoi in android with andengine.And i am getting below Exception

i am not getting an output of reversed array from my code below

i am getting index out of range error on running the below code

Why I am not getting any error in below code?

I am trying to add one to each digit in my problem but I am getting a syntax error that doesn't make sense?

What is wrong with my code? I am stuck. I am trying to export some promise results

I am trying to connect my web page to my local server database but I am getting error ECONNREFUSED

I am getting this error when trying to run my bot commands

I am getting error while trying to run my flutter project

I am trying to make a discord bot in java script on repl.it but it gives me an error error above code below

I am trying to make an API call to SpaceX, but I keep getting an error on my response. Is my POJO the issue?

I am trying to use "sort" method but I am getting an error

I am trying to fit an CuDNNLSTM model and I am getting an error

I am getting a type error when I am trying to replace

I am new to r. I am trying to make my code less complicated by using a for loop

When i am trying to run this code i am getting the following error

I am getting an error in my code in python i am new to it what should i do?

i am getting an error "Code is unreachable Pylance" what that mean or am i doing any mistake in my code?

Why am I getting a CS1001 error in my code?

Why am I getting an Undifined Index error in my php code?

Why am I getting an error in my Java code?

Why am I getting internal server error with my python code?

Why am I getting a Type Error on my code?

I am trying to import csv file into mysql, but I am getting below error. Can anyone help me?

I am getting the error below every time I run the code and I do not know what is wrong

Why am I getting a syntax error when I check my interface? Trying to make a MAC changer for Linux with Python/argparse

While i am running the code below i am getting the adapter error,i tried solving it by seeing yt videos ....still its not working