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)
})
},
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;
}