Hi
I am generating an excel from a dataset which has about 20000 records. Then i am applying some formatting (like Backcolors, locking, column hiding etc.) to the excel. But it seems the file is getting damaged. When I open the file I get a message that the damage was so extensive that Excel could not repair it.
Also, my code is working perfectly for 5000 records.
Can you help me on this?
Thanks
1. Which version of Aspose.Cells are you using? Have you tried latest v4.1?
2. Could you please post your whole project here?
Thank you.
I am using Aspose.Cells Version 3.7.0.0.
I am pasting my code below: (Please note that I am using my own class which calls the Aspose methods)
public void CreateTimeTypeTab(int clientID,int timeTypeID,string timeTypeName)
{
excelObj.ExcelAddRenameSheet("CSR-" + timeTypeName);
DataSet dsFeedData = feedFileBC.GetAllMetrics(clientID, timeTypeID);
excelObj.ExcelWorkSheet.Cells.ImportDataTable(dsFeedData.Tables[0], true, "A1");
if (ClientName == "Vie")
{
excelObj.ExcelProtectWorkSheet(CSRSheetPassword);
}
else
{
excelObj.ExcelProtectWorkSheetCompletely(CSRSheetPassword);
}
/* Update the index variables*/
CSREndColumnNo = dsFeedData.Tables[0].Columns.Count - 1;
CSREndRowNo = dsFeedData.Tables[0].Rows.Count+1;
//Call the Format sheet method to add formatting
FormatSheet();
}
public void FormatSheet()
{
//Change header for Root Client
excelObj.Excel_Add_Header(CSRHeaderRow, 0, "Client ID");
excelObj.Excel_Add_Header(CSRHeaderRow, 1, "Client");
DateColumnCnt = FindColumnByHeader("Date");
excelObj.ExcelAutoFitColumn(DateColumnCnt);
MetricNameColumnNo = FindColumnByHeader("Metric Name");
LevelCnt = FindColumnByHeader("ElementRefID") / 2;
MetricTypeCnt = (FindColumnByHeader("Feed Version") - MetricNameColumnNo) / 2;
DateColumnCnt = FindColumnByHeader("Date");
for (int j = 0; j < LevelCnt; j++)
{
if (j > 0)
{
excelObj.Excel_Add_Header(CSRHeaderRow, j * 2 + 1, "ReportingLevel" + (j));
excelObj.ExcelAutoFitColumn(j * 2 + 1);
excelObj.Excel_Add_Header(CSRHeaderRow, j * 2 , "ClientID");
excelObj.ExcelColoumnHide(0);
excelObj.ExcelColoumnHide(j * 2);
}
}
//Format and hide the Client ID columns
for (int i = 0; i < LevelCnt; i++)
{
excelObj.CreateNewStyle();
for (int k = CSRStartRowNo; k <= CSREndRowNo; k++)
{
excelObj.ExcelChangeBackColor(k-1, (i * 2), "Red");
excelObj.ExcelChangeBorderColor(k-1, (i * 2), "Black");
}
excelObj.CreateNewStyle();
for (int k = CSRStartRowNo; k <= CSREndRowNo; k++)
{
excelObj.ExcelChangeBackColor(k-1, (i * 2) + 1, "Yellow");
excelObj.ExcelChangeBorderColor(k-1, (i * 2) + 1, "Black");
}
excelObj.ExcelAutoFitColumn(i * 2 + 1);
}
//Formatting the ElementReferenceID column
excelObj.CreateNewStyle();
for (int k = CSRStartRowNo; k <= CSREndRowNo; k++)
{
excelObj.ExcelChangeBackColor(k-1, MetricNameColumnNo - 1, "Yellow");
excelObj.ExcelChangeBorderColor(k-1, MetricNameColumnNo - 1, "Black");
}
//Format the Metric Name column
excelObj.CreateNewStyle();
for (int i = CSRStartRowNo; i <= CSREndRowNo; i++)
{
excelObj.ExcelChangeBackColor(i-1, MetricNameColumnNo, "Aqua");
excelObj.ExcelChangeBorderColor(i-1, MetricNameColumnNo, "Black");
}
excelObj.ExcelAutoFitColumn(MetricNameColumnNo);
excelObj.ExcelDateValidation(CSRStartRowNo, CSREndRowNo, DateColumnCnt, DateColumnCnt);
// increase the width of date column
excelObj.ExcelColumnWidth(Convert.ToByte(DateColumnCnt), 10);
//Unlock the Date field
for (int i = CSRStartRowNo; i <= CSREndRowNo; i++)
{
excelObj.ExcelChangeBackColor(i - 1, DateColumnCnt, "Yellow");
excelObj.ExcelChangeBorderColor(i - 1, DateColumnCnt, "Black");
excelObj.ExcelLockCell(i - 1, DateColumnCnt, false);
}
//Hide and format ElementTypeID columns
for (int i = 0; i < MetricTypeCnt-1; i++)
{
for (int k = CSRStartRowNo; k <= CSREndRowNo; k++)
{
excelObj.ExcelChangeBackColor(k-1, DateColumnCnt + (i * 2 + 1), "Red");
excelObj.ExcelChangeBorderColor(k-1, DateColumnCnt + (i * 2 + 1), "Black");
}
//Format the ElementType Column
for (int k = CSRStartRowNo; k <= CSREndRowNo; k++)
{
excelObj.ExcelValidationForDecimal(0, CSREndRowNo, DateColumnCnt + (i * 2 + 2), DateColumnCnt + (i * 2 + 2));
if (excelObj.ExcelWorkSheet.Cells[k - 1, DateColumnCnt + (i * 2 + 2)].StringValue == "X")
{
excelObj.ExcelWorkSheet.Cells[k - 1, DateColumnCnt + (i * 2 + 2)].PutValue(string.Empty);
excelObj.ExcelLockCell(k - 1, DateColumnCnt + (i * 2 + 2), false);
excelObj.ExcelChangeBackColor(k - 1, DateColumnCnt + (i * 2 + 2), "Yellow");
}
else if (excelObj.ExcelWorkSheet.Cells[k - 1, DateColumnCnt + (i * 2 + 2)].StringValue == "")
{
// Lock
excelObj.ExcelChangeBackColor(k - 1, DateColumnCnt + (i * 2 + 2), "Yellow");
}
else if (excelObj.ExcelWorkSheet.Cells[k - 1, DateColumnCnt + (i * 2 + 2)].StringValue == "P")
{
// Lock and grey
excelObj.ExcelWorkSheet.Cells[k - 1, DateColumnCnt + (i * 2 + 2)].PutValue(string.Empty);
excelObj.ExcelChangeBackColor(k - 1, DateColumnCnt + (i * 2 + 2), "Grey");
}
excelObj.ExcelChangeBorderColor(k-1, DateColumnCnt + (i * 2 + 2), "Black");
}
excelObj.ExcelAutoFitColumn(DateColumnCnt + (i * 2 + 2));
}
// Hiding ElementRefID
excelObj.ExcelColoumnHide(LevelCnt*2);
//Colour the ElementType columns
for (int k = 0; k < MetricTypeCnt-1; k++)
{
excelObj.Excel_Add_Header(CSRHeaderRow, DateColumnCnt + k * 2 + 1, "ElementTypeID");
excelObj.ExcelColoumnHide(DateColumnCnt + k * 2 + 1);
}
//Hiding the feed version column
excelObj.ExcelColoumnHide(FindColumnByHeader("Feed Version"));
excelObj.CreateNewStyle();
//Change Header font to bold
excelObj.ExcelChangeBoldRangeCol("A1", excelArray[CSREndColumnNo] + "1", true);
}
From your code I don’t find any problems. Please try it with v4.1.
I have already bought the license for 3.7. Will i need a seperate license for that?
No, you don't need a new license. You will get free updates for a whole year after you purchase the license.
Even version 4.1 doesn't seem to work. It seems to break after some 8000 records.
If i didn't mention earlier, the excel does open with all the records (even 20000), but there is no formatting whatsover and there is the popup saying the excel is damaged.
Everything works well (formatted data worksheets) till 8000 records.
FYI: I am using ASP.Net 2.0 with C#
Please try this version. If it still doesn’t work, please send your whole project with your template file and output file to nanjing@Aspose.com. I will check it ASAP.
Could you please post your output file here?
I got some ideas from your sample code:
maybe you set too many different kind of formattings for cells.
Only about 500 different kinds of formattings can be set in an Excel file.
Another possible cause of this problem may be caused by DataValidation. What happens if you remove code about data validation?
Its working if I remove the validations.....even for 20000 records.![Big Smile [:D]](/Community/emoticons/emotion-2.gif)
But i still need the validations...
After checking your file, I have found that you may not set those validations correctly: you have set many duplicate validations on a single cell. Please check your code to remove those redudant validations.
This is my code...
I don't see the code being executed repeatedly for the same cells.
MetricTypeCnt = 4;
CSRStartRowNo = 0;
CSREndRowNo = 19000;
DateColumnCnt = 14;
for (int i = 0; i < MetricTypeCnt-1; i++)
{
for (int k = 1; k <= 19000; k++)
{
excelObj.ExcelValidationForDecimal(k-1, k-1, 14 + (i * 2 + 2), 14 + (i * 2 + 2));
}
}
Thanks
What's the code in ExcelValidationForDecimal method? From your file, I am sure that you do set several validations to a single cell.
Please debug into your program to check Validation.AreaList property before saving the file. You will easily find that different Validation objects refer to same CellArea value.
public void ExcelValidationForDecimal(int startrow, int endrow, int startcolumn, int endcolumn)
{
Validations validations = ExcelWorkSheet.Validations;
Validation validation = validations[validations.Add()];
validation.Type = ValidationType.Decimal;
validation.Operator = OperatorType.Between;
validation.Formula1 = Decimal.MinValue.ToString();
validation.Formula2 = Decimal.MaxValue.ToString();
CellArea area;
area.StartRow = startrow;
area.EndRow = endrow;
area.StartColumn = (byte)startcolumn;
area.EndColumn = (byte)endcolumn;
validation.AreaList.Add(area);
}
Could you please zip your project and post it here or send it to nanjing@aspose.com Something must be wrong for all validations in your file is set on row 8502.
Following is my test code. If validation is set correctly, it will work fine.
static void SetValidation()
{
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
int MetricTypeCnt = 4;
for (int i = 0; i < MetricTypeCnt-1; i++)
{
for (int k = 1; k <= 19000; k++)
{
ExcelValidationForDecimal(sheet, k-1, k-1, 14 + (i * 2 + 2), 14 + (i * 2 + 2));
}
}
wb.Save("d:\\test\\abc.xls");
}
public static void ExcelValidationForDecimal(Worksheet ExcelWorkSheet, int startrow, int endrow, int startcolumn, int endcolumn)
{
Validations validations = ExcelWorkSheet.Validations;
Validation validation = validations[validations.Add()];
validation.Type = Aspose.Cells.ValidationType.Decimal;
validation.Operator = OperatorType.Between;
validation.Formula1 = Decimal.MinValue.ToString();
validation.Formula2 = Decimal.MaxValue.ToString();
CellArea area;
area.StartRow = startrow;
area.EndRow = endrow;
area.StartColumn = (byte)startcolumn;
area.EndColumn = (byte)endcolumn;
validation.AreaList.Add(area);
}
And for your case, you don’t need to set validations cell by cell. It will take much more memory and make the file much larger. You can use the following code with same function and it will reduce the file size.
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
int MetricTypeCnt = 4;
for (int i = 0; i < MetricTypeCnt-1; i++)
{
ExcelValidationForDecimal(sheet, 0, 18999, 14 + (i * 2 + 2), 14 + (i * 2 + 2));
}
wb.Save(“d:\test\abc.xls”);
I also make a mistake when talking with your file. The correction information is:
There are 68009 validations in your file. Except one special validation, all other validation are similiar.
8501 validations has same settings, they all refer to range:
start row: 0,
end row: 8501
start column: 16
end column: 16
8501 validations has same settings, they all refer to range:
start row: 0,
end row: 8501
start column: 18
end column: 18
......
......
8501 validations has same settings, they all refer to range:
start row: 0,
end row: 8501
start column: 30
end column: 30
It worked. I went with the idea of validating the entire column in one go..... the size of the file was also reduced and the formattings came perfectly.
Now I am almost done with my file, except one small thing-- the numeric validation i have applied is also taking dates. Can we restrict that?
I think we cannot restrict it because MS Excel allows it. In MS Excel, date is really a numeric value.
Thanks a lot for your help.
I really appreciate the support you are providing for your products.
![Smile [:)]](/Community/emoticons/emotion-1.gif)