Click here to Skip to main content
13,293,101 members (90,218 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Dear Code Project Team,
i have major use when i read password protected excel file . Error are occur (Could not decrypt file) can you please help about this topic .

i can read Password Protected excel file in with C# code..

Warm regards
Rohit Singh
Posted 15-Jan-12 17:40pm
RohitSingh_icfai 15-Jan-12 23:14pm
dear Mark,
That is my code can you please help about this code how to read file password protected file and return read data in dataset...

Please solve this puzzle in excel upload in sql server live project.

Give me actual code for reading excel to upload in sql server .

string status = excel.OpenFile("DPLINSERT1.xls", "12345");
if (status.Equals("OK"))
string[] all = excel.GetRange("A1:D1");
int a = all.Length;
string StrDateTime = DateTime.Now.ToString("yyyy") + DateTime.Now.ToString("MM") + DateTime.Now.ToString("dd") + DateTime.Now.ToString("hh") + DateTime.Now.ToString("mm") + Request.Cookies["Emp_Code"].Value.ToString();

string StrFileName = "~/SDFR/tmp" + StrDateTime + ".xls";

DataSet ds = XlsToDataset(Server.MapPath(StrFileName), "table1");

int z = upd1.PostedFile.FileName.LastIndexOf(@"\");

string actualfilename = upd1.PostedFile.FileName.Substring(z + 1);
DataTable dt = ds.Tables[0];
Member 10045821 9-Aug-13 3:11am
Enter your comment below and click the Submit Comment button.
Member 11220674 15-Dec-14 0:57am
How to Protect the Excel file with password By using the C# or,Please help me..
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Rate this: bad
Please Sign up or sign in to vote.

Solution 3

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.
            //Open specified file in MS Excel.
joe_j 15-Feb-13 4:18am
this question is a year old.
Rate this: bad
Please Sign up or sign in to vote.

Solution 4

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.171207.1 | Last Updated 23 Aug 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100