ReportViewer export to Excel with password

Question

I had a code in MVC which successfully exports out the Excel file but how do I add the password into the Excel file?

My code:

public ActionResult Report(string id)

        {

            LocalReport lr = new LocalReport();
            string path = Path.Combine(Server.MapPath("~/Report"), "ReportStateArea.rdlc");
            if (System.IO.File.Exists(path))
            {
                lr.ReportPath = path;
            }
            else
            {
                return View("Index");
            }
            List<StateArea> cm = new List<StateArea>();
            using (myDatabaseEntities dc = new myDatabaseEntities())
            {
                cm = dc.StateAreas.ToList();
            }
            ReportDataSource rd = new ReportDataSource("MyDataset", cm);
            lr.DataSources.Add(rd);
            string reportType = id ;
            string mimeType;
            string encoding;
            string fileNameExtension;



            string deviceInfo =

            "<DeviceInfo>" +
            "  <OutputFormat>" + id + "</OutputFormat>" +
            "  <PageWidth>8.5in</PageWidth>" +
            "  <PageHeight>11in</PageHeight>" +
            "  <MarginTop>0.5in</MarginTop>" +
            "  <MarginLeft>1in</MarginLeft>" +
            "  <MarginRight>1in</MarginRight>" +
            "  <MarginBottom>0.5in</MarginBottom>" +
            "</DeviceInfo>";

            Warning[] warnings;

            string[] streams;
            byte[] renderedBytes;

            renderedBytes = lr.Render(
                reportType,
                deviceInfo,
                out mimeType,
                out encoding,
                out fileNameExtension,
                out streams,
                out warnings);





            return File(renderedBytes, mimeType);
        }

tezzo · Answer 1 · 2015-09-21T15:37:09.620

0

You can use FileStream Class to write renderedBytes on a file.

Dim strExcelFile As String = "C:\YourPathTo\ExcelFile.xls"

Dim fs As New FileStream(strExcelFile, FileMode.Create)
fs.Write(renderedBytes, 0, renderedBytes.Length)
fs.Close()
fs.Dispose()

Then use Microsoft.Office.Interop.Excel.dll to add a password to an existing workbook:

Dim appExcel As Excel.Application
Dim wbExcel As Excel.Workbook

appExcel = New Excel.Application
wbExcel = appExcel.Workbooks.Open(strExcelFile)

wbExcel.Password = "YourPassword"

appExcel.DisplayAlerts = False
wbExcel.SaveAs(strExcelFile)
appExcel.DisplayAlerts = True

wbExcel.Close()

I used DisplayAlerts to suppress prompts and alert messages while using SaveAs to overwrite existing file; when a message requires a response, Microsoft Excel chooses the default response.

My code is in VB.NET but you can simply translate it in C#.

edited Sep 21 '15 at 15:37

answered Sep 21 '15 at 15:30

tezzo

10,858
1
25
48

But when i create it is byte[] then only return to file. once return to view it will start download. What can i do ? – Howard Oi Sep 22 '15 at 03:15
so that i dun hv the path. Pls help – Howard Oi Sep 22 '15 at 03:16
Not tested but I think you need to save your file on a virtual path (use MapPath to obtain the physical file path when use FileStream), modify your file adding a password and then return this virtual path to client instead of return File(renderedBytes, mimeType). – tezzo Sep 22 '15 at 10:10
May i have the sample code for the return? Sorry because i am beginner. Thanks – Howard Oi Sep 22 '15 at 10:29
You can also reconvert to byte your password-protected excel file and continue to use `return File(excelPasswordProtectedBytes, mimeType)`. – tezzo Sep 22 '15 at 12:28
do you have any other soluion. Because i din hv excel which mean i cant use interop – Howard Oi Sep 23 '15 at 02:06
give a look at the accepted answer: http://stackoverflow.com/questions/11448197/how-to-use-microsoft-office-interop-excel-on-a-machine-without-installed-ms-offi – tezzo Sep 23 '15 at 06:52

ReportViewer export to Excel with password

1 Answers1