//You have to specify the full file path: https://stackoverflow.com/questions/10350467/system-io-writeallbytes-access-to-path-denied-error
string _tempDirectory = Path.Combine(Utl.CreateDirectory(_record));
//BatchInfoTempDirectory.SetFolderPermissions();
using (ExcelEngine WorkbookEngine = new ExcelEngine())
{
#region Initialize Excel Engine
IApplication _excelApplication = WorkbookEngine.Excel;
_excelApplication.DefaultVersion = ExcelVersion.Excel2016;
//Create a new workbook
IWorkbook _workbook = _excelApplication.Workbooks.Create();
#endregion
//Does directory exist
if (!Directory.Exists(_tempDirectory)) Utl.CreateDirectory(_record);
#region Foreach Table Loop
foreach (DataTable table in _dataset.Tables)
{
WriteLog($"::::Found {table.Rows.Count} in {table.TableName}");
//Create Worksheet
IWorksheet _worksheet = _workbook.Worksheets.Create(table.TableName);
//Adding a Picture
_worksheet.Pictures.AddPicture(1, 2, 10, 6, $"{Path.Combine(_directory, "Assets", "bank_banner.png")}");
//Import data from the data table with column header, at first row and first column, and by its column type.
_worksheet.ImportDataTable(table, true, 11, 1, true);
//Creating Excel table or list object and apply style to the table
IListObject _excelTable = _worksheet.ListObjects.Create(table.TableName, _worksheet.UsedRange);
//Apply Table Style
_excelTable.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium13;
//Auto fit the columns
_worksheet.UsedRange.AutofitColumns();
//Protecting the Worksheet by using a Password
_worksheet.Protect(_record, ExcelSheetProtection.All);
}
#region Cleanup Workbook
if (_workbook.Worksheets.Count > 3)
{
_workbook.Worksheets.Remove("Sheet1");
_workbook.Worksheets.Remove("Sheet2");
_workbook.Worksheets.Remove("Sheet3");
}
//Encrypt the workbook with password
_workbook.PasswordToOpen = DateTime.Now.ToString("yyyy/MM/dd");
//Set the password to modify the workbook
_workbook.SetWriteProtectionPassword(_record);
//Set the workbook as read-only
_workbook.ReadOnlyRecommended = true;
#endregion
#region Save File
string excelFilePath = Path.Combine(_tempDirectory, $"{_record}.xlsx");
using (Stream _stream = File.Create(excelFilePath))
{
//got these 2 lines from https://www.syncfusion.com/forums/143545/saveas-corrupt-excel-file
//File still corrupted with or without them
_stream.Position = 0;
_stream.SetLength(0);
_workbook.SaveAs(_stream);
}
}
}
I am using the above code to create an excel file, but after successful generation, it pops an message saying it's corrupt when i open the file. Attached is the file generated. The password to open is 2020/03/06 and the file name is the content protection password. Any help will be greatly appreciated
