Formatting Columns as Dates #977

New Issue

2018-01-31T07:14:11Z

singiankay commented

2018-01-31 07:14:11 +00:00

(Migrated from github.com)

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.

Sample file generated from code below

production_schedule_example.xlsx

Code

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

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. **Sample file generated from code below** [production_schedule_example.xlsx](https://github.com/SheetJS/js-xlsx/files/1680718/production_schedule_example.xlsx) **Code** ``` 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) }) }, ```

👍 1

malek-itani commented

2019-02-08 13:11:03 +00:00

(Migrated from github.com)

@reviewher @SheetJSDev actually, there is no bug in the package, but it's about that we haven't caught the correct way to make it right.
our problem is that we have in our JSON data string dates, that are in the format of 'mm-dd-yyyy',
when we export those data to excel to client, the cell format is text by default for all fields and field date also, so we need to change the cell format for the date string field to be date in order to allow client to make sort upon it.

@reviewher @SheetJSDev actually, there is no bug in the package, but it's about that we haven't caught the correct way to make it right. our problem is that we have in our JSON data string dates, that are in the format of 'mm-dd-yyyy', when we export those data to excel to client, the **cell format is text by default for all fields** and field date also, so **we need to change the cell format for the date string field to be date** in order to allow client to make sort upon it.

FathimaFirdous commented

2020-04-27 06:43:35 +00:00

(Migrated from github.com)

I have same issue as mentioned by @malek-itani . Is there any solution ?

SheetJSDev commented

2021-09-15 22:43:59 +00:00

(Migrated from github.com)

The utilities like json_to_sheet will convert actual JS Date to Excel datecode values with the appropriate format. It will not automagically convert date strings to dates.

If you know which fields are supposed to be dates, you can just replace their values with the parsed equivalent e.g.

var fixed_data = data.map(row => {
  var o = {};
  if(row.SalesOrderDate) o.SalesOrderDate = new Date(row.SalesOrderDate);
  return Object.assign({}, row, o);
});

The utilities like `json_to_sheet` will convert actual JS `Date` to Excel datecode values with the appropriate format. It will not automagically convert date strings to dates. If you know which fields are supposed to be dates, you can just replace their values with the parsed equivalent e.g. ```js var fixed_data = data.map(row => { var o = {}; if(row.SalesOrderDate) o.SalesOrderDate = new Date(row.SalesOrderDate); return Object.assign({}, row, o); }); ```

Sign in to join this conversation.