Share an article to solve the iView table export Excel cell wrapping problem

1. The process of encountering problems

In a recent project, there was a requirement to export the table contents to Excel, and we happened to be using the iView framework. Table component in iView has its own table export function to Excel, so I am happy to directly use:

html:
<Table :columns="columns" :data="data" size="small" ref="myTable"></Table>
<Button type="primary" size="large" @click="exportData"> Export data</Button>

script:
exportData(){.
 this.$refs.myTable.exportCsv({
 filename: 'myTable'// Export Excel file name})}Copy the code

The exported table is fine when we have no newline data in the table. So when we had too much data in one of our cells and we needed a line break, the table that we exported instead of a line break in the cell went straight to the next row in the Excel table. See the following figure: Table data of the page

2. Problem-solving process

1). Find the cause of the problem through various tests, find the cause of the problem is that the server return value has a newline character “\n”. In this case, replace the character “\n” with empty:

// Filter the table dataexportData(){.
 this.$refs.myTable.exportCsv({
 filename: 'myTable',
 data: this.$formatExcelData(this.data)})} // Public method vue.prototype.$formatExcelData = function (data) {
        var arr = []
        var self = this
        var dateArr = ['createDate'.'fixedDate'.'reportDate']
        _.forEach(data, function (v) {
            for (var k in v) {
              if (typeof v[k] == 'string') {
                    v[k] = v[k].replace(/\n/g, ' ')
                }
            }
            arr.push(v)
        })
        return arr
    }
Copy the code

After that, retest it. Sure enough, there are no newlines in the table. However, the newline inside the cell is also gone. Conclusion: “\n” causes line breaks in Excel, which can be resolved by removal. But we need a way to apply “\n” to cells. 2). Find a solution to the operating system problem encountered before the newline problem is caused by different operating systems. Try replacing “\n” with “\r\n”(window newline)

v[k] = v[k].replace(/\n/g, '\r\n') / / failureCopy the code

So let’s think about it a little bit. In Excel, we have Alt + Enter for a line break cell. A search on Google shows that each key on the keyboard corresponds to an ASCLL code. What if “\n” is replaced with the corresponding ASCLL? Query the keyboard ASCALL code Alt corresponding to \0\x0A

v[k] = v[k].replace(/\n/g, '0x12 0x0A')// Change to the next column, feel closer to solving the problem (the reason is not in-depth, interested can go to research)Copy the code

Problem solving

I have been working on the problem for a day, but I still haven’t solved it. Due to the time problem, I would like to consult our group of C++ leaders (Excel core is C++) and then solve it. We need to add “to our incoming data as follows:

v[k] = '"'+ v[k].replace(/"/g, '""' + '"'/ / successCopy the code

Later I have time to think about this problem if I can better understand it with some tools, it should be more helpful for me to solve this problem. The divine editor Notepad++ comes to mind. Open Notepad++ and do the following: view = “display symbol =” display all the contents of the cell wrapped in Notepad++ to compare with our own generated line wrapped Excel:

! [line-wrapped inside a cell]