How to read Password protected excel file in asp.net with C#

Go through this CP Article


Automating MS Excel Using Visual Studio .NET[^]

Hi,
You might want to look at this .Net Excel component[^]. This C# code example shows how to use it to read password protected Excel files:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Elerium.Excel;

namespace ReadPasswordProtection
{
    class Program
    {
        static void Main(string[] args)
        {
            // Read .xls file with password encription.
            ExcelWorkbook wbook = ExcelWorkbook.ReadXLS(@"..\..\..\PasswordExcel.xls", "qwerty");

            // Set new password encription.
            wbook.Options.Password = "asdfgh";

            // Write .xls file.
            wbook.WriteXLS(@"..\..\..\NewPassword.xls");
            //Open specified file in MS Excel.
            System.Diagnostics.Process.Start(@"..\..\..\NewPassword.xls");
        }
    }
}

The information here is incorrect in some cases. I am reading a password protected file without having the password, as the protection is ReadOnly for the worksheets. If you have a read only password protected Excel file, you can read it without having the password. The trick is that you cannot use some of the methods that you can if the file was not proected... namly .SpecialCells(). .Net will tell you when various methods cannot be used on a protect sheet. In that case, there are other options.

I have a web service where users upload an Excel file and the app picks it up and reads the data into the database. There are half a dozen different formats and the users are inconsitent in having titles, headers, extra columns, etc. and some of them protect the worksheets so they are read-only. I had to create a reader that could read it without regard to the column location, where the header row shows up, and regardless if the worksheet was protected or not.

Here's a function I setup to capture the range of the sheet and read the values into an array. I am using this with files that are password protected and files that are not. But the key here is that I am NOT used OLEDB, as that was intended to allow you to work with the Excel application directly and not when you are using Excel to read from such as on a server. So I abandoned that very early on and went with Microsoft.Office.Interop.Excel. It has been working very nicely once I got over the initial issues with the protected and unprotected differences.

If you use this method, note that the final values are stored in an array... object[,] sheetvalues. The values must be stored in a 2d object array.

public void InitializeWorksheetArray(string startingCellId)
        {

            // Verify the starting cell on range was provided. If not default to A1. This allows you to pass in the line that contains the headers if you wish. I created a loop that reads up to and finds the header line, as it may not be the first line, then pass that line/row id into this method.
            if (String.IsNullOrEmpty(startingCellId)) startingCellId = "A1";

            // Load Worksheet by Index (the index is provided by a property in the class and from a using loop to capture the worksheets by name and pass the index in here.  This allows me to set an appSetting that contains the worksheet names for ease of modification if anyone renames the sheet/tabs)
            Excel.Sheets sheets = excelApp.Worksheets;
            Excel.Worksheet sheet = sheets.get_Item(_currentWorksheetIndex + 1); // Convert to non-0 based worksheet index

            // Set Range. (Using the starting cell Id parameter let the Excel API pick the ending column and row.)

            // Protected Sheets
            // Find the last real row and column to set the range-last value.
            // The following two lines are the key that will work for protected sheets.
            var lastRow = sheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
            var lastCol = sheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column;
            Excel.Range last = sheet.UsedRange[lastRow, lastCol];

            // This is what doesn't work if the sheets are protected, but I think it is what most people are using and it will fail if the sheet is protected.
            // Non-Protected Sheets can use the SpecialCells() method. However, some of the sheets are protected, so we cannot use this. Left in for reference.
            // If the sheet is not protected, you can use the following...
            //Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
            
            // Set the range
            Excel.Range range = sheet.get_Range(startingCellId, last);

            // Capture workseet values as 2d arrary stored in the sheetValues global variable. (object[,] sheetvalues)
            sheetValues = range.Value2;

            // Set column and row counts properties.
            _rowColumnCount = sheetValues.GetLength(1);
            _rowCount = sheetValues.GetLength(0);
        }

You can also set the Excel file to readonly when you open it. It worked either way, but I set it just in case.

public void OpenExcelFile(string filename)
       {
           Filename = filename;
           excelWorkbook = excelApp.Workbooks.Open(filename, Type.Missing, true);
       }