We have come across a problem with one of our larger files. This Excel file contains just over 39,000 rows and is 18 columns wide. File size is roughly 10MB.
Here are some additional notes regarding how we are using the product:
- Aspose is using the PutValue() method to populate the file.
- We are running AutoFitColumn() on each column before calling Save()
- We are using Microsoft Office Excel 2003 to open these files.
Here is how we reproduce the error:
1) Our application uses Aspose.Excel to generate the Excel file and save it to disk.
2) We open the new file in Excel, preview the data, and then close it without making any changes.
3) We are prompted to "Save Changes....[since] Excel recalculates formulas when opening files last saved by an earlier version of Excel...", but we say NO.
4) We try to open the file again, but we get the following error:
'[Filename]' cannot be accessed. The file may be read-only, or you may be trying to access a read-only location.
At this point, the file appears corrupt and will not open.
Now, if we choose to Save the file after opening it for the first time, then everything works fine and we are able to open it again & again without issue.
It seems this only happens with large files. The smaller files (several thousand rows) can be opened, closed without saving even though it still prompts you to Save with the same message I posted above, and then opened again without issue.
Our question, do we need to manually open and re-save these larger files so we don't run into the problem of a customer opening the file for the first time, closing without saving, and then being unable to open it again? If so, at what point [filesize/rowcount] do we need to consider doing this?
Regards,
C.R.
And does your file contain pivot table/chart? Do you use OpenInExcel option to send file to client browser? What if you directly save the file on disk with Aspose.Excel?
Aspose.Excel version is: 3.4.2.0. I can't send the file (contains SSN's). And, I can't remove that data without opening it up, removing it, and saving it which would cause the problem to go away.
No pivot tables or charts. Just raw data. We don’t use OpenInExcel. No files are sent to the browser. The app saves the file to disk, and then we are opening it up by browsing to it in Windows Explorer.
Do you use a template file? Could you please post your sample code here?
We do use a template file. It is attached.
Here is the code. NOTE: the object being looped through in the foreach statement (detailData) is a custom Strongly Typed DataSet.
|
|
///
/// Creates an eBill (Excel document) and saves it to disk.
///
/// The eBills period
/// The billId of the eBill that will be created
public static void CreateEbill(int period, int billId) { // Get Bill
Bill b = BillDAO.GetBill(billId);
// Get eBill
Ebill ebill = EbillDAO.GetEbill(billId, period);
// Get data (returns EbillData object with EbillDetailDataSet & EbillAgingDataSet)
EbillData data = EbillDataDAO.GetData(billId);
// Set output .xls file path & file name
string filePath = GetEbillFilePath(period);
string filePathAndName = GetEbillFilePathAndFileName(b.billName, period);
string templateFile = ConfigurationSettings.AppSettings[“ExcelTemplateFilePath”];
// Load Aspose License
string key = ConfigurationSettings.AppSettings[“AsposeExcelLicense”];
if (key != null && key.Length > 0) {
Aspose.Excel.License license = new Aspose.Excel.License();
license.SetLicense(key);
}
// Create new Aspose.Excel object, and open the template file
Excel excel = new Excel();
excel.Open(templateFile);
// Declare Cells class
Aspose.Excel.Cells cells;
///////////////////////////////////////////////////////////////////
// Create worksheet
///////////////////////////////////////////////////////////////////
Worksheet wsDetail = excel.Worksheets[0];
cells = wsDetail.Cells;
// Declare counter
i = 1;
int detailColumns = 18;
foreach(EbillDetailDataSet.DetailDataItem item in data.detailData.DetailDataItems) {
cells[i,0].PutValue(item.ExternalAcctnum);
cells[i,1].PutValue(item.RefAcctNum);
cells[i,2].PutValue(item.AcctName);
cells[i,3].PutValue(item.FileNumber);
cells[i,4].PutValue(item.ReferenceNum);
cells[i,5].PutValue(item.TrxTimeStamp);
cells[i,6].PutValue(item.AppFirstName);
cells[i,7].PutValue(item.CoAppFirstName);
cells[i,8].PutValue(item.AppLastName);
cells[i,9].PutValue(item.Charge);
cells[i,10].PutValue(item.Surcharge);
cells[i,11].PutValue(item.TaxAmount);
cells[i,12].PutValue(item.TotalSale);
cells[i,13].PutValue(item.Product);
cells[i,14].PutValue(item.Attention);
cells[i,15].PutValue(item.AdditionalInfo);
cells[i,16].PutValue(item.Notes);
cells[i,17].PutValue(item.ExtAcctNum);
cells[i,18].PutValue(item.ExtLnOrgID);
i++;
}
// Autofit all columns
for (int k=0; k<=detailColumns ; k++) {
wsDetail.AutoFitColumn(k);
}
// Make sure directory exists, if not create it
if (!Directory.Exists(filePath))
Directory.CreateDirectory(filePath);
// Save file
excel.Save(filePathAndName);
}
|
It seems a problem of file size. We will check and fix it ASAP. Thanks for your patience.
It’s fixed. Please download and try v3.4.4.
Excellent. It worked!
Thanks for the excellent support. Great product.