Autosum in Excel column

Aspose.Cells Product Family
1

hi

I am using Aspose.total

I exported an excel from my asp.net page using aspose.cells

one column is showing TIME (hh:mm:ss)

I formatted the time column by using


----------------------------------------------------------------------------------

Aspose.Cells.Style styleTotaal = workSheet.Cells["E" + i].GetStyle();

" styleTotaal.Number = 20; "

----------------------------------------------------------------------------------

After exporting TIME is displaying in correct format

but I cant autosum the time column

could anyone help please?

2

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v8.2.1.3 if it makes any difference.

Do you mean to say you cannot apply auto-sum in MS Excel manually on your output Excel file generated by Aspose.Cells APIs. If you still find the issue with v8.2.1.3, kindly create a sample project (runnable), zip it and post us here to reproduce the issue on our end, we will check it soon. Also provide your template files, it will help us to evaluate your issue properly to figure the issue (if found) out soon.

Thank you.

3

Thanks for the reply.


I am using workSheet.Cells.ImportDataTable(dt1, true, “A6”); function.

I am importing data from sql server database to excel.

I set Style.Custom=“h:mm”; and Style.Number=20; for that particular time field

The time is displaying in correct format h:mm, but when trying to autosum,ith is giving 0:00 result.

Any ideas?
4

Hi,


I have tested your scenario using the following sample code and it works fine. I can get the Sum of the two DateTime values dynamically and also the the Sum is displayed fine when opening the output Excel file into MS Excel.
e.g
Sample code:

Workbook excel = new Workbook();
// Create a Cells object obtaining all the cells of the first
// Worksheet.
Cells cells = excel.Worksheets[0].Cells;
// Create a Cell object and get the A1 cell.
Aspose.Cells.Cell cell = cells[0, 0];
//Create a style object.
Style style = cell.GetStyle();
style.Custom = “h:mm”;
cell.SetStyle(style);
// Put date value into it.
cell.PutValue(DateTime.Now);


// Create a Cell object and get the A2 cell.
cell = cells[1, 0];
//Create a style object.
Style style1 = cell.GetStyle();
style1.Custom = “h:mm”;
cell.SetStyle(style1);
// Put date value into it.
cell.PutValue(DateTime.Now);


// Create a Cell object and get the A3 cell.
cell = cells[2, 0];
//Create a style object.
Style style2 = cell.GetStyle();
style2.Custom = “h:mm”;
cell.SetStyle(style2);
cell.Formula = “SUM(A1:A2)”;

excel.CalculateFormula();

Debug.WriteLine("Sum: " + cell.StringValue);

excel.Worksheets[0].AutoFitColumn(0);


excel.Save(“e:\test2\outautosum1.xlsx”);


I have attached the output file for your reference. It looks like the underlying DateTime values in the SQL Server database table is strings, please check it out on your end. If you still could not evaluate, kindly create a sample project (runnable) (Please use DataTable dynamically in your codes instead), zip it and post us here to reproduce the issue on our end, we will check it soon. Also provide your template files, it will help us to evaluate your issue properly to figure the issue (if found) out soon.

Thank you.
5

Hi, Thanks again for the reply.


Unfortunately your last reply is not helpful for me. So as per your request I am sending a runable program for you.

Its a webpage, in that page, there is Generate button, please click on the generate button to create/generate an excel file(as per the datatable creating dynamically). after generating the excel you can be given a link to download the excel just below the Generate button.

Please download and try to autosum the time column.


Awaiting your reply

Thanks

Kishore R
6

Hi,


Thanks for the sample project and template files.

I have evaluated your scenario using your sample project a bit. Well, the issue is not with Aspose.Cells but with your data. Actually your data is strings and not numeric data or DateTime values, so you cannot apply DateTime formatting upon data and hence you cannot auto-sum right. For your issue, you may change the code segment:
i.e.,

…
Workbook workBook = new Workbook();
Worksheet workSheet = workBook.Worksheets[0];
workSheet.Cells.ImportDataTable(dt, true, “A1”);

int count = dt.Rows.Count;
for (int i = 1; i <= count ; i++)
{
Aspose.Cells.Style styleTime = workSheet.Cells[“A” + i].GetStyle();
styleTime.Custom = “h:mm:ss”;


workSheet.Cells[“A” + i].SetStyle(styleTime);
}
workSheet.AutoFitColumns();
workSheet.AutoFitRows();



with:
…
Workbook workBook = new Workbook();
Worksheet workSheet = workBook.Worksheets[0];

workSheet.Cells.ImportDataTable(dt, true, 0,0,true,true);

int count = dt.Rows.Count +1;
for (int i = 1; i <= count; i++)
{
Aspose.Cells.Style styleTime = workSheet.Cells[“A” + i].GetStyle();
styleTime.Custom = “h:mm:ss”;


workSheet.Cells[“A” + i].SetStyle(styleTime);
}
workSheet.AutoFitColumns();
workSheet.AutoFitRows();

it works fine as I tested. Actually you got to convert your so called DateTime data to numeric or appropriate data type, I choose the relevant ImportDataTable() method overload to fix it.


Thank you.