Excel sum function not working after download with times as hh:mm:ss

Question

After downloading the xlsx file, I need to sum time values and see the average, total, count etc in the status bar.

I have attached the screenshot of current situation is showing 0.

TS code

 public exportAsExcelFile(summary:any[], json: any[], excelFileName: string): void {
    let report = "Global Service Desk SLA";
    let ReportName = [{"Report":`Report Name : ${report}`}];
  
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(ReportName ,{skipHeader:true});
    if(worksheet['A2'] == undefined){
      worksheet['A2'] = {"t":"s","v":`Date Range : ${summary[0].FromDate +" - "+summary[0].ToDate}`};
    }
    if(worksheet['A3'] == undefined){
      worksheet['A3'] = {"t":"s","v":`Bot : ${summary[0].Bot}`}
    }
    if(worksheet['A4'] == undefined){
      worksheet['A4'] = {"t":"s","v":`Timezone : ${summary[0].timeZone}`}
    }
    const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data']};
    XLSX.utils.sheet_add_json(worksheet,json,{origin:"A7"});

    const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
    this.saveAsExcelFile(excelBuffer, excelFileName);
  }

If I select the queue time column like screenshot average total is showing 0 i need the sum of total values in Average section

Working if I change hh:mm:ss to h:mm:ss

If I remove single 0 in that column cell the average total is showing please see in my screenshot

How can I enforce this type through code using SheetJS library? Or is there some other way to let Excel calculate the sum for hh:mm:ss formatting?

Average is showing 0 because sum is showing 0. Where is the logic for adding the cell values? It is likely that you need to parse the values to Number type before adding. — Nikhil, Feb 20 '23 at 07:38
hi @janakiraman, can you post the code where you are calculating the sum and average? — Nikhil, Feb 20 '23 at 08:43
Okay, looks like these sum, count, average are calculated by Excel when you select the columns. I thought you are doing it through code. You have to change the type for sum to `hh:mm:ss` as shown in this page: https://www.wallstreetmojo.com/add-time-in-excel/ — Nikhil, Feb 20 '23 at 10:51
@Nikhil manually i need to change every time when iam downloading, but i need to done changes in my code side so it will dynamically total the average. in my code they have used npm xlsx https://www.npmjs.com/package/xlsx i don't know where to makes changes — janakiraman, Feb 20 '23 at 11:20
You should set the type of the cell to `hh:mm:ss` programmatically through that library. Check out the docs: https://docs.sheetjs.com/docs/csf/features/dates#date-and-time-number-formats I'll update the tags in your question, so someone else might help you. — Nikhil, Feb 20 '23 at 22:08

score 0 · Answer 1 · answered Mar 05 '23 at 10:23

You need to represent the time as a numeric being the number of seconds in the time / 86400.

E.g. for 10:30:00 it is (10.5 hours x 60 minutes x 60 seconds) / 86400

Then you set the z property of the cells you need to hh:mm:ss.

See this sample code below which works in an English US locale:

const XLSX = require("xlsx");
const filename = "./Book7.xlsx";

// 3 times: 00:00:00, 00:10:00, 02:30:00
const times = [0, (10 * 60) / 86400, ((2.5 * 60) * 60) / 86400];
const data = [
  ["a", times[0], times[0]],
  ["b", times[1], times[1]],
  ["c", times[2], times[2]],
];

// put array of arrays to sheet
const worksheet = XLSX.utils.aoa_to_sheet(data);

// update 'z' property to time format
for (let i=1; i<=data.length; i++) {
  worksheet[`C${i}`].z = "hh:mm:ss";
}
console.log(worksheet);

// write workbook to disk
const workbook = { Sheets: { 'data': worksheet }, SheetNames: ['data']};
XLSX.writeFile(workbook, filename, {cellNF: true});

Note that I updated column C's z property but not column B which is left as the simple numeric. The sum and average display as expected

Excel sum function not working after download with times as hh:mm:ss

1 Answers1