Export CSV with autowidth on each cells

user_11363581 0
Viewed 995

I am having a problem in setting the auto-width. I want to fit all the text in a single cell where all values can be viewed without further clicks. My exported CSV currently looks like this :

Exported CSV

And the following is what I want to achieve:

Achieve state

What I've tried out so far is, convert this array of data (stringified)

[
  ["Play Date","Round","Result Virtual","Result Data"],
  ["=\"2020-03-04 00:48:00\"","16","\"[LEFT, LINE3, EVEN]\"","\"[LEFT, LINE3, EVEN]\""],
  ["=\"2020-03-04 00:51:00\"","17","\"[LEFT, LINE4, ODD]\"","\"[LEFT, LINE4, ODD]\""],
  ["=\"2020-03-04 00:54:00\"","18","\"[LEFT, LINE3, EVEN]\"","\"[LEFT, LINE3, EVEN]\""],
  ["=\"2020-03-04 00:57:00\"","19","\"[RIGHT, LINE4, EVEN]\"","\"[RIGHT, LINE4, EVEN]\""]
]

into CSV format and then download:

data.forEach(rowItem => {
  rowItem.forEach(colItem => {
    csvString += `${colItem},`
  })
  csvString += '\r\n'
})

csvString = 'data:application/csv,' + encodeURIComponent(csvString)
var x = document.createElement('A')
x.setAttribute('href', csvString)
x.setAttribute('download', `${new Date()}.csv`)
document.body.appendChild(x)
x.click()
export-to-csv csv excel javascript
2 Answers

Comma-Separated Values (CSV), as the name suggests, is nothing more than text. separated with commas. According to Wikipedia, CSV is

a delimited text file that uses a comma to separate values. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format. A CSV file typically stores tabular data (numbers and text) in plain text, in which case each line will have the same number of fields.

All it has is a single 2 dimensional matrix of data. What you are trying to incorporate is to incorporate some data about display format of those data in a specific application (excel as it seems). This I am afraid is not possible without adding data related to the formatting the presentation of actual data, in those application. File Content + Formatting Data -> .csv is going to violate the fundamental idea behind CSV (read this for more on CSV and this for the RFC on CSV). That's why there are application specific formats such as .xls.

I don't think that it is (and should ever be) possible to do what you try to do in .csv format. The best avenue that you can take is to choose a different file format that better suits your requirement (such as .xls).

user_31269730

You could try this API RangeFormat.autofitColumns(), here is the sample code you could try

async function main(context: Excel.RequestContext) {
  // Auto fit the columns of range: Sheet1!A:A
  let workbook = context.workbook;
  let worksheets = workbook.worksheets;
  let selectedSheet = worksheets.getActiveWorksheet();
  selectedSheet.getRange("A:A").format.autofitColumns();

The document can be found at https://docs.microsoft.com/en-us/javascript/api/excel/excel.rangeformat?view=excel-js-preview#autofitcolumns--

user_110894110
Related