How to Protect Excel (C#) Without Using Interop. (using NPOI - OfficeOpenXmlCrypto.dll)

The eaziest way to protect excel file with open source library is by using OfficeOpenXmlCrypto.dll

you can download the lib on https://code.google.com/archive/p/ooxmlcrypto/

The advantage of using it is you do not need to install ms office on your work. no license, no ms office.! :)

In other word, if you have ms office installed already. you just use the below code to protect the excel.

 using Microsoft.Office.Interop.Excel  
 //create your spreadsheet here...  
 WorkbookObject.Password = password;  
 WorkbookObject.SaveAs("spreadsheet.xls")  

Well, What I have done in my project using the OfficeOpenXmlCrypto.dll to protect excel file report is I made it as simple as using interop. I made a function in order to be reuseable. here it is :

     public static byte[] SetPassword(Stream str)  
     {  
       MemoryStream ms = new MemoryStream();  
       byte[] buffer = new byte[16 * 1024];  
       int iRead;  
       str.Position = 0;  
       OfficeCryptoStream ocs = new OfficeCryptoStream(str, "");  
       ocs.Password = "your password here";  
       ocs.Save();  
       ocs.WriteTo(str);  
       str.Position = 0;  
       while ((iRead = str.Read(buffer, 0, buffer.Length)) > 0)  
       {  
         ms.Write(buffer, 0, iRead);  
       }  
       return ms.ToArray();  
     }  

The function above has parameter stream, where it is taken from workbook on NPOI object. Then it return byte[] value. it can be directly render/generate to excel file. encrypted excel file.

Here I also write function byte to excel file (excel generator). check it out :

     public static void GenerateByteToExcel(HttpResponse Response, byte[] FileContent, string sFileName, string sContentType)  
     {  
       Response.Clear();  
       Response.ClearHeaders();  
       Response.ClearContent();  
       Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", sFileName));  
       Response.ContentType = sContentType;  
       Response.BinaryWrite(FileContent);  
       Response.Flush();  
       HttpContext.Current.ApplicationInstance.CompleteRequest();  
     }  

And here the complete use :

         NPOI.XSSF.UserModel.XSSFWorkbook xssfworkbook = new NPOI.XSSF.UserModel.XSSFWorkbook();  
         // fill the variable xssfworkbook with the data  
         // then finally, make it as byte[]  
         MemoryStream msBuffer = new MemoryStream();  
         xssfworkbook.Write(msBuffer);  
         Stream ms = new MemoryStream(); //to ensure memory stream can be expandable  
         ms.Write(msBuffer.ToArray(), 0, msBuffer.ToArray().Length);  
         byte [] byteReturn = SetPassword(ms);  
         GenerateByteToExcel(Response , byteReturn , "excel_report_name.xlsx" , "application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");