I have a Vue project that writes an excel file using SheetJS.

How do I set the format of the columns in my generated excel file?

I need to set the SalesOrderDate, CustomerRequestedDeliveryDate, ConfirmedDate, and _ProductionDate to date format.

generateExcel() {
    axios.get('generateExcel?format=json', {
        params: {
            division: this.auth.user.current_division,
            area: this.form.area,
            month: this.form.month
        }
    })
    .then(response => {
            let structArray = []
            for (let [index, value] of response.data.entries()) {
                structArray.push({
                    SalesOrderNumber: value.so_id,
                    SalesOrderDate: (value.so_date.trim().length ? moment(value.so_date, 'MMMM, DD YYYY HH:mm:ss', true).format('MM/DD/YYYY'): ''),
                    ShipmentStatus: value.shipment_status,
                    Remarks: value.remarks,
                    ID: value.id,
                    ModelName: value.model_name,
                    ModelNumber: value.model_id,
                    Qty: value.qty,
                    CustomerRequestedDeliveryDate: (value.requested_delivery_date.trim().length ? moment(value.requested_delivery_date, 'MMMM, DD YYYY HH:mm:ss', true).format('MM/DD/YYYY'): ''),
                    ConfirmedDate: (value.confirmed_date.trim().length ? moment(value.confirmed_date, 'MMMM, DD YYYY HH:mm:ss', true).format('MM/DD/YYYY'): ''),
                    'ProductionDateBy': value.production_date_by,
                    '_ProductionDate': (value.production_date.trim().length ? moment(value.production_date, 'MMMM, DD YYYY HH:mm:ss', true).format('MM/DD/YYYY'): ''),
                    '_ProductionRemarks': value.production_remarks,
                })
            }
            this.sheet.jsondata = structArray
            let ws = XLSX.utils.json_to_sheet(this.sheet.jsondata)
            ws['!autofilter'] = { ref: `A1:L${response.data.length+1}` }
            ws.columns = [

            ]
            let wb = XLSX.utils.book_new()
            wb.Props = {
                Title: "Production Schedule Template",
                Author: "Admin"
            }
            XLSX.utils.book_append_sheet(wb, ws, "Schedule")
            let wbout = XLSX.write(wb, {type:"array", bookType:"xlsx"})
            saveAs(
                new Blob([wbout],
                {type:"application/octet-stream"}
            ), "production_schedule.xlsx")
        })
    .catch(error => {
        this.$storemit('SET_ALERT',{type:'error', message:[error]})
        console.log(error)
    })
},

I have a Vue project that writes an excel file using SheetJS.

How do I set the format of the columns in my generated excel file?

I need to set the SalesOrderDate, CustomerRequestedDeliveryDate, ConfirmedDate, and _ProductionDate to date format.

generateExcel() {
    axios.get('generateExcel?format=json', {
        params: {
            division: this.auth.user.current_division,
            area: this.form.area,
            month: this.form.month
        }
    })
    .then(response => {
            let structArray = []
            for (let [index, value] of response.data.entries()) {
                structArray.push({
                    SalesOrderNumber: value.so_id,
                    SalesOrderDate: (value.so_date.trim().length ? moment(value.so_date, 'MMMM, DD YYYY HH:mm:ss', true).format('MM/DD/YYYY'): ''),
                    ShipmentStatus: value.shipment_status,
                    Remarks: value.remarks,
                    ID: value.id,
                    ModelName: value.model_name,
                    ModelNumber: value.model_id,
                    Qty: value.qty,
                    CustomerRequestedDeliveryDate: (value.requested_delivery_date.trim().length ? moment(value.requested_delivery_date, 'MMMM, DD YYYY HH:mm:ss', true).format('MM/DD/YYYY'): ''),
                    ConfirmedDate: (value.confirmed_date.trim().length ? moment(value.confirmed_date, 'MMMM, DD YYYY HH:mm:ss', true).format('MM/DD/YYYY'): ''),
                    'ProductionDateBy': value.production_date_by,
                    '_ProductionDate': (value.production_date.trim().length ? moment(value.production_date, 'MMMM, DD YYYY HH:mm:ss', true).format('MM/DD/YYYY'): ''),
                    '_ProductionRemarks': value.production_remarks,
                })
            }
            this.sheet.jsondata = structArray
            let ws = XLSX.utils.json_to_sheet(this.sheet.jsondata)
            ws['!autofilter'] = { ref: `A1:L${response.data.length+1}` }
            ws.columns = [

            ]
            let wb = XLSX.utils.book_new()
            wb.Props = {
                Title: "Production Schedule Template",
                Author: "Admin"
            }
            XLSX.utils.book_append_sheet(wb, ws, "Schedule")
            let wbout = XLSX.write(wb, {type:"array", bookType:"xlsx"})
            saveAs(
                new Blob([wbout],
                {type:"application/octet-stream"}
            ), "production_schedule.xlsx")
        })
    .catch(error => {
        this.$store.commit('SET_ALERT',{type:'error', message:[error]})
        console.log(error)
    })
},

• javascript
• excel
• vue.js
• sheetjs

5 Answers 5

Unfortunately, there is no feature to format a column easily in SheetJS. As per this issue, here's a way to do this:

var colNum = XLSX.utils.decode_col("B"); //decode_col converts Excel col name to an integer for col #
var fmt = '$0.00'; // or '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' or any Excel number format

/* get worksheet range */
var range = XLSX.utils.decode_range(ws['!ref']);
for(var i = range.s.r + 1; i <= range.e.r; ++i) {
  /* find the data cell (range.s.r + 1 skips the header row of the worksheet) */
  var ref = XLSX.utils.encode_cell({r:i, c:colNum});
  /* if the particular row did not contain data for the column, the cell will not be generated */
  if(!ws[ref]) continue;
  /* `.t == "n"` for number cells */
  if(ws[ref].t != 'n') continue;
  /* assign the `.z` number format */
  ws[ref].z = fmt;
}

Here's a function for Andrew's SheetJS answer -

function formatColumn(worksheet, col, fmt) {
  const range = XLSX.utils.decode_range(worksheet['!ref'])
  // note: range.s.r + 1 skips the header row
  for (let row = range.s.r + 1; row <= range.e.r; ++row) {
    const ref = XLSX.utils.encode_cell({ r: row, c: col })
    if (worksheet[ref] && worksheet[ref].t === 'n') {
      worksheet[ref].z = fmt
    }
  }
}

Example:

const rows = [
  ['name', 'cost', 'price'], 
  ['dino', 3.45, 7.95]
]

const workbook = XLSX.utils.book_new()
const worksheet = XLSX.utils.aoa_to_sheet(rows) // array of arrays

const currency = '$0.00'
for (let col of [1, 2]) {
  formatColumn(worksheet, col, currency)
}

XLSX.utils.book_append_sheet(workbook, worksheet, 'Details')

The above code was not working for me. I think sheetJS changed the way they do cell references. Anyways, for those of you that are dealing with the issue of sheetJS converting your numbers into text. The below function can fix that for you. It functions pretty much the same way as Brian's answer above however it actually changes the underlying format of the cell ( as opposed to just formatting text in a certain way ).

The parameters are

• A worksheet object created using XLSX.utils.aoa_to_sheet(ws_data)
• A string name of a column which is present on the worksheet object
• A format which you want this column to be. Ex: "n" = number. This can be found on npm XLSX docs.

function formatColumn(ws, col, fmt) {

  var range = XLSX.utils.decode_range(ws['!ref']);
  for(var R = range.s.r; R <= range.e.r; ++R) {
  for(var C = range.s.c; C <= range.e.c; ++C) {
    var cell_address = {c:C, r:R};
    /* if an A1-style address is needed, encode the address */
    var cell_ref = XLSX.utils.encode_cell(cell_address);

    if(!ws[cell_ref]) continue;
  /* `.t == "n"` for number cells */
    ws[cell_ref].t = fmt
  }
}

}

Its quite ugly but I solve my problem by doing a simple loop. So what I want to achieve is this:

worksheet["E2"].z = "m/d/yyyy h:mm";
worksheet["E3"].z = "m/d/yyyy h:mm";
worksheet["E4"].z = "m/d/yyyy h:mm";
// so on

Why I ended up doing:

for (let i = 0; i <= data.length; i++) {
  worksheet[`E${i + 1}`].z = "m/d/yyyy h:mm";
}

Excel is doing it self if is a iso Date, im using sheetjs also,