AutoFitColumn - Takes too long

Aspose.Cells Product Family
1

hi,

I have generated an excel with approximately 900 rows and 90 columns. The resize takes about 16 seconds, that is too much time. How can I enter values into the excel file, without an automatic resize of the columns?

That means, I can change the column size in the resource file I dump data into. But after the dump the columns have been resized. Is there a way to prevent the PutValue from resizing the column?

Thanks,
michael

2

Hi Michael,

Could you please post your sample code here? I think this problem is caused by too many calls to AutoFitColumn method. PutValue method won't change the column width.

3

PutValue:

foreach(DataRow dataRow in dataRows)
{
int index = 0;
foreach(string columnHeader in headers)
{
if(dataRow[columnHeader] != DBNull.Value)
{
file.Worksheets[1].Cells[row, index].PutValue(dataRow[columnHeader]);
}
index++;
}
row++;
}

AutoFitColumn: (headers is my column array)

// auto column fit
for(int i = 0; i < headers.Length; i++)
{
file.Worksheets[1].AutoFitColumn(i);
}

Could it be that it comes from the following code that the styles get lost?
Aspose.Cells.Workbook file = new Aspose.Cells.Workbook();
file.Copy(template);


4

Are you sure that you only calls AutoFitColumn method once for each column?

If yes, could you please post your output file and more sample code here? I will check it.

5

Hi,

yes, you are right. I made a rebuild of the library, seems to me that I have forgotten the rebuild before the first check (embedded resource). Right now, the file looks good.

But just for the completeness: Do I something wrong with the column fit? It needs 16 sec. for the attached report.

I have attached the files. ExportHandler.GenerateExcel(…) does all the stuff.

Thanks
Michael

6

Dear Michael,

In your zip file, there is a file starting with "SA100....xls". Winzip prompts it's an invalid file name. Is it your output file?

Please run this piece of code and see the elapse time.

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
for(int i = 0; i < 900; i ++)
for(int j = 0; j < 90; j ++)
{
cells[i, j].PutValue("hello" + i.ToString() + "world" + j.ToString());
}

DateTime start = DateTime.Now;
for(int i = 0; i < 90; i ++)
sheet.AutoFitColumn(i);
DateTime end = DateTime.Now;
Console.WriteLine(end.Subtract(start).TotalSeconds);

7

yes, I attached another zip file without the strange characters…

I ran the code right behing my code and both took 20 seconds. How long does it take within your code?


8

My piece of code runs 4s in my laptop.

WinXP

PentiumM 1.7G

512MB RAM