CODEWITHSUNDEEP
javascriptangularjsnode.jsexcelcsv
Mitaksh Gupta
Mitaksh Gupta

Reputation: 1029

Set column-width and word wrap properties while exporting data to excel/csv angularjs

I have created a ng-directive which parses the data and export the data to excel on click of a button on which the directive is applied. Here is the code to export the data to the csv format. 

angular.module('employeesApp')
    .directive('exportdetailsCsv', ['$parse', 'DetailsManagerService', function ( $parse, DetailsManagerService) {
        return {
            restrict: 'A',
            scope: false,
            link: function(scope, element, attrs) {
                var data = '';
                var csv = {
                    stringify: function(str) {
                        return '"' +
                            str.replace(/^\s\s*/, '').replace(/\s*\s$/, '') // trim spaces
                                .replace(/"/g,'""') + // replace quotes with double quotes
                            '"';
                    },
                    generate: function(claimDetails, employeeCode, employeeName) {
                        var dataToExport = claimDetails;
                        var dataLength = Object.keys(dataToExport).length;
                        var tableColumnsCount = Object.keys(dataToExport).length;

                        var row1 = [];
                        row1.push("Title");
                        row1.push(dataToExport["title"]);

                        var row2 = [];
                        row2.push("Employee Name");
                        row2.push(employeeName);

                        var row3 = [];
                        row3.push("Employee Code");
                        row3.push(employeeCode);

                        var row4 = [];
                        row4.push("Project");
                        row4.push(dataToExport["project"]);


                        data = row1 + "\n" + row2 + "\n" + row3 + "\n" + row4;

                    },
                    link: function(fileName, inData, fileType) {
                        var bData = inData ? inData : data;
                        if (bData && bData.length > 0) {
                            var blob = new Blob([bData], {type: (fileType || "text/csv") + ";charset=utf-8"});
                            saveAs(blob, fileName || 'Details.csv');
                            data = [];
                        }
                    }
                };
                $parse(attrs.exportdetailsCsv).assign(scope.$parent, csv);
            }
        };
    }]);

The issue that I am facing is that the data that is being exported is longer than the default column width of excel sheet, which results in the data being shown truncated in the print out of the excel. The problem here is that as I am building the entire data in the JS, thus I cannot use the CSS properties of the HTM page. So I want to know if it is possible to set the option like word wrap and column width through the code? Or, if there is any alternative to what I am trying to do here.

Upvotes: 1

Views: 10017

Answers (2)

Jorg
Jorg

Reputation: 7250

Check this codepen. It will try to download a file as soon as it loads. It's based on the answer I linked to in a comment earlier. It also includes the minified version of the filesaver script. Have a look at the TableToExcel variable, in particular the template set up in there.

For your understanding, Excel files are XML based, so you can use simple HTML style tables to increase the width, set colors or borders. From the codepen, this is the relevant bit of table formatting:

<table 
    cellspacing="0" 
    rules="rows" 
    border="1" 
    style="color:Black;background-color:White;border-color:#CCCCCC;border-width:1px;border-style:None;width:100%;border-collapse:collapse;font-size:9pt;text-align:center;"
>
</table>

This might get complicated if you have complicated requirements. In that case, Sathish' suggestion of using Excelbuilderjs is not bad.

The codepen needs tweaking, depending on the versions of excel etc. I hope this will get you started.

Upvotes: 1

Sathish
Sathish

Reputation: 116

Using CSV, you cannot able to format data or Excel Styling. it's only to export data.

May you try some external tools like: Excelbuilderjs

Upvotes: 0

Related Questions