I have done some further investigation that may be useful to you or anyone else experiencing the issue.
This is a longer post, but I hope it is useful.
What I have discovered is that when an incorrectly extensioned and password-protected file is loaded, it will have 0 sheets, this is obviously not usual so this can be used to detect and resolve the issue.
The version number reported by Spire seems to be correct when the file extension is incorrect at least for non-passworded files.
If a password-protected file with the wrong file extension is reloaded, this time specifying the version information from the previous attempt, the file will correctly report if the file is password-protected or at least give a password related exception.
.\XLSX with XLS Extension.xls
IsPasswordProtected Reports: True
Workbook has opened successfully, and reports version as Version2016
Sheet Count:2
.\XLS with XLSX Extension.xlsx
IsPasswordProtected Reports: False
Workbook has opened successfully, and reports version as Version97to2003
Sheet Count:2
.\XLSX with XLS Extension with Password.xls
IsPasswordProtected Reports: False
Workbook has opened successfully, and reports version as Version97to2003
Sheet Count:0
Attempting Reload
sprᵷ: Invalid password.
at sprᶺ.ᜀ(sprᦫ A_0)
at sprᶺ.ᜀ(String A_0, sprᵁ A_1)
at sprᶺ..ctor(String A_0, sprᵁ A_1)
at Spire.Xls.Core.Spreadsheet.XlsWorkbook..ctor(Object A_0, String A_1, ExcelVersion A_2)
at Spire.Xls.Workbook.LoadFromFile(String fileName, ExcelVersion version)
at SpireCurrentTests.UnitTest1.WorkbookLockedTest() in UnitTest1.cs:line 139
.\XLS with XLS Extension with Password.xls
IsPasswordProtected Reports: True
sprᵷ: Please provide password for the Workbook file.
at sprℂ.(spr A_0)
at sprℂ.ᜀ(MemoryStream A_0)
at sprℂ.ᜀ(sprᦫ A_0)
at sprᶺ.ᜀ(String A_0, sprᵁ A_1)
at sprᶺ..ctor(String A_0, sprᵁ A_1)
at Spire.Xls.Core.Spreadsheet.XlsWorkbook..ctor(Object A_0, String A_1, ExcelVersion A_2)
at Spire.Xls.Workbook.LoadFromFile(String fileName, ExcelVersion version)
at SpireCurrentTests.UnitTest1.WorkbookLockedTest() in UnitTest1.cs:line 124
.\XLSX with XLSX Extension with Password.xlsx
IsPasswordProtected Reports: True
sprᵷ: Invalid password.
at sprᶺ.ᜀ(sprᦫ A_0)
at sprᶺ.ᜀ(String A_0, sprᵁ A_1)
at sprᶺ..ctor(String A_0, sprᵁ A_1)
at Spire.Xls.Core.Spreadsheet.XlsWorkbook..ctor(Object A_0, String A_1, ExcelVersion A_2)
at Spire.Xls.Workbook.LoadFromFile(String fileName, ExcelVersion version)
at SpireCurrentTests.UnitTest1.WorkbookLockedTest() in UnitTest1.cs:line 124
.\XLS with XLS Extension.xls
IsPasswordProtected Reports: False
Workbook has opened successfully, and reports version as Version97to2003
Sheet Count:2
.\XLSX with XLSX Extension.xlsx
IsPasswordProtected Reports: False
Workbook has opened successfully, and reports version as Version2016
Sheet Count:2
Here is the test code.
- Code: Select all
public void WorkbookLockedTest()
{
string[] strFalseXLS =
{
@".\XLSX with XLS Extension.xls",
@".\XLS with XLSX Extension.xlsx",
@".\XLSX with XLS Extension with Password.xls",
@".\XLS with XLS Extension with Password.xls",
@".\XLSX with XLSX Extension with Password.xlsx",
@".\XLS with XLS Extension.xls",
@".\XLSX with XLSX Extension.xlsx"
};
foreach (string strCurrent in strFalseXLS)
{
bool blPasswordProtectedStatus = Workbook.IsPasswordProtected(strCurrent);
Console.WriteLine($"\n\n{strCurrent}\nIsPasswordProtected Reports: {blPasswordProtectedStatus}");
try
{
Workbook wbWorkbook = new Workbook();
wbWorkbook.LoadFromFile(strCurrent);
wbWorkbook.UnProtectWorkbook("");
Console.WriteLine($"Workbook has opened successfully, and reports version as {wbWorkbook.Version}");
Console.WriteLine($"Sheet Count:{wbWorkbook.Worksheets.Count}");
int iWorksheetCount = wbWorkbook.Worksheets.Count;
ExcelVersion evExcelVersion = wbWorkbook.Version;
wbWorkbook.Dispose();
if (iWorksheetCount == 0)
{
wbWorkbook = new Workbook();
Console.WriteLine("\tAttempting Reload");
wbWorkbook.LoadFromFile(strCurrent, wbWorkbook.Version == ExcelVersion.Version97to2003 ? ExcelVersion.Version2016 : ExcelVersion.Version97to2003);
Console.WriteLine($"\tWorkbook has opened successfully, and reports version as {wbWorkbook.Version}");
Console.WriteLine($"\tSheet Count:{wbWorkbook.Worksheets.Count}");
wbWorkbook.Dispose();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
