-1

I have the data that has to be loaded to an excel file in a byte array and I need to apply password protection on that. I have tried converting byte[] to datatable/list and tried applying password protection using Excelpackage but I am not able to correctly convert data in byte[] array to any form.My file is getting downloaded but with some weired data. Can anyone please share your knowledge?

            response.Clear();
            response.Buffer = true;
            response.ContentEncoding = System.Text.Encoding.UTF8;
            response.ContentType = mimeType; 
            response.AddHeader("content-disposition", "attachment;filename=" 
            + Uri.EscapeDataString(fileName));
            response.Charset = "";
            response.Cache.SetCacheability(HttpCacheability.NoCache);
            DataTable dt;
            MemoryStream stream;
            using (stream = new MemoryStream(fileBytes))
            {

               BinaryFormatter bin = new BinaryFormatter();
               stream.Seek(0, SeekOrigin.Begin);
               dt = (DataTable)formatter.Deserialize(stream);
               stream.Close();
            }
            using (ExcelPackage pack = new ExcelPackage())
            {

                ExcelWorksheet ws = pack.Workbook.Worksheets.Add("heelo");
                ws.Cells["A1"].LoadFromDataTable(dt, true);
                pack.Save("123");
                var ms = new System.IO.MemoryStream();
                pack.SaveAs(ms);
                ms.WriteTo(HttpContext.Current.Response.OutputStream);
                ms.Close();

            }
            response.Flush();
            response.End();
Aparna
  • 17
  • 3

1 Answers1

0

Having a little trouble following you code. Why is response mime type a PDF? I think you would want that to be

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Does response actually have anything to do with the MemoryStreams since I see no reference from one to the other?

In any event, if you have the ExcelPackage available and you want to write to a Stream with a password, you can just call the overload:

pack.SaveAs(ms, "MyPassword")

Here is some more info: Password Protected Excel Download using EPPLUS

Ernie S
  • 13,902
  • 4
  • 52
  • 79
  • I have already tried this but still not working. I think the issue is with the data generated from the conversion of byte[] to datatable (Actually 'dt' is not holding meaningful data).If I am directly downloading the byte[] in an excel file using 'Binary Writer' without password protection ,the data getting generated is in proper form. – Aparna Mar 11 '19 at 05:52
  • @Aparna Its still not entirely clear where `fileBytes` is coming in. If it is a a saved excel file with a password, could you use one of the other overloads for like `public ExcelPackage(FileInfo newFile, string password)` or `public ExcelPackage(Stream newStream, string Password)`? You can see them here: https://github.com/JanKallman/EPPlus/blob/master/EPPlus/ExcelPackage.cs#L281 – Ernie S Mar 11 '19 at 12:21
  • Here the filebytes(generated from SSRS reports) contain data to be dumped into the excel file being generated.It is not a saved file, but we have to generate the file from the byte array(named filebytes) with password protection.I have already tried your suggestion but it is not working in my case. – Aparna Mar 15 '19 at 12:08