Make me headache an afternoon of Excel merge cells

Excel Export FAQ

Excel export is really not a difficult thing

Copy the template code on the Web, populate your own business data, and provide an HTTP interface that basically gives you the data you want to export.

However, there are exceptions to all things, as of today, Excel exports I have encountered two major categories of problems

1, large data volume of Excel data, such as hundreds of thousands of or more data export

2, because of the contents of Excel problems, resulting in the export of Excel can not be opened directly, error "because some content is not desirable, Excel cannot open xxx.xlsx. Do you want to open and repair this workbook?

For the first big data volume problem, the main problem I encountered was the record caps and export timeouts for Excel storage

The workaround is to upgrade the export format to XLS to Xlsx,xls for up to 65,536 records per sheet, xlsx supports up to 1,048,576 records, time-outs can take the front-end direct return, and the backend asynchronously takes the data and exports the way to avoid timeouts.

This situation is not the focus of today's introduction, the second situation to be introduced today to solve the idea.

Requirements Description

1, hierarchical relationship of up to four levels

2, for the same level, if the same content needs to be vertically merged cells, blank lines do not need to merge

3. Sample data is shown below

一级目录1,二级目录1,三级目录1,四级目录2,一级目录1,二级目录1,三级目录3,一级目录1,二级目录1,三级目录5,一级目录1,二级目录3,一级目录1,二级目录5,三级目录5,一级目录2,二级目录2,三级目录2,一级目录2,二级目录2,三级目录3,一级目录2,二级目录4,三级目录4,一级目录2,二级目录7,一级目录3,二级目录6,三级目录4,一级目录3,二级目录6,三级目录10,一级目录4,一级目录5,二级目录8,三级目录6,

Solution Ideas

The above sample data is stored in a collection, traversing each record and placing it in the appropriate cell.

If you don't need to merge the cells, you can provide the exported Excel here.

But the focus is on merging the cells.

Preliminary troubleshooting of problems encountered

Since the code is already in place, the calling interface, the Excel file has also been successfully downloaded, the result of the opening of the moment a dialog box gave me a headache for the afternoon.

The error message is as follows

The first reaction is that the data is confused, it is estimated that the cells are crowding each other, the data is certainly offensive.

But I follow the intelligent Excel prompt, click "Open and Repair" found that the data is not as bad as I thought, even look carefully, found that there is no problem.

A little excited at the same time, the heart is still a bit uncomfortable, always can not let others use this smart "open and repair" function to see the exported data.

But the light from this error message does not really have any clue, so the internet to find a pass with "because some content is not desirable, Excel cannot open xxx.xlsx." Do you want to open and repair this workbook? About the workaround. Although many people have encountered such problems, but the cause of the problem is not the same, some because the name of sheet contains special characters, some of the exported Excel content has illegal characters, but also said in response header to add Content-length field.

Further troubleshooting

A search, no progress, this time remember to just click on "Open and repair", but also pop up a dialog box, and then click on the dialog box to view

Get clues as follows

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>修复结果到 xxx.xml</logFileName><summary>在文件“/Users/jackie/Downloads/xxx.xlsx”中检测到错误</summary><removedRecords summary="以下是已删除记录的列表:"><removedRecord>已删除的记录: /xl/worksheets/sheet1.xml 的 合并单元格</removedRecord></removedRecords></recoveryLog>

The reasons for the above-mentioned illegal characters are ruled out, and the "merged cells" in the clues can basically be concluded because there is a problem in merging the cells.

Finding the root cause of the problem

The symptoms of an Excel table that cannot be opened by combining merged cells Online search for a pass

Http://www.360doc.com/content/14/0107/11/14931240_343269914.shtml This article gives a solution to the idea

I change the suffix of the downloaded Excel table from xlsx to zip and open

Open the Sheet1.xml file, locate the mergecells tag, and copy its contents to the XML Online format tool to view

After the human eye search, finally found the problem

    ...    <mergeCell ref="B175:B189"/>    <mergeCell ref="B176:B190"/>    ...

There is obviously a case of overlay merging , which in turn results in an error in Excel (the same error message will appear in repeating merged cells after the test is found)

Follow this line of thinking, troubleshoot code, debug tests, consider merging cell scenarios in a variety of situations, and finally finalize the Excel export feature of this slightly complex merged cell.

A little thought

Although it is known that the merge cell is causing the problem, it took almost an afternoon to actually adjust the code and was once too big to think.

Think back, there are two big gains on this issue.

1. The idea of troubleshooting is very important

The problem is already in front of you, and you should look for other reasons to find out why it is not an illegal character.

Take advantage of all the information you can use, such as the short and critical error log information above.

The use of search engines, this problem must have been the predecessors have stepped on thunder, to see how they are mine, and do not have to study the specific methods of demining.

2, write the code before you think well

Now think about whether the code for this merged cell can be more beautiful to write, I think it is possible, but can be reduced from 30 lines to 10 rows or even 5 lines, I think this is not possible.

Because this export merge will encounter a variety of situations, such as the continuous same cells when merging, how to guarantee the blank line does not merge, a blank line area before and after the implementation of the merger and so on.

So, before writing this code, you should sort out all the possible scenarios, including some special cases, and do whatever it takes to make sure that the data is properly exported in response to all situations.

I'll put the code in the project Rome later.

Project Address: Https://github.com/DMinerJackie/rome

Yes, the export is presented

If you feel that reading this article is helpful to you, please click " recommend " button, your " recommendation " will be my biggest writing motivation! If you want to keep an eye on my article, please scan the QR code, follow Jackiezheng's public number, I will push my article to you and share with you the high-quality articles I have read every day.

Make me headache an afternoon of Excel merge cells