Hello!
I`m trying to export data from grid to Excel. Some of cells store the tabulation symbols (see 'grid.png'). After export I open the excel-file and get the following error (see 'excelError.png'). If you choose any of options, file is openning without data (see 'excelFile.png'). How can I export data with this symbol to Excel?
Thanks.
Hi Elena,
We researched this scenario and found that the ''\u0007' symbol is not a valid format for XML documents by default. Please review Why are “control” characters illegal in XML 1.0? and Invalid Characters in XML for more details. Currently, our exporting mechanism (it internally creates an .xml document that is exported as an Excel file) doesn't have a specific logic to process such symbols. So, this case requires us to implement a custom processing mechanism that will check each cell and parse a string in a different manner if it contains specific symbols. This is not a quick task, and requires a significant amount of time and resources. While I cannot promise any estimates for this improvement, we'll definitely take this case into account for further development.
Currently, the only solution is to manually remove such symbols before export using the customizeExportData callback. For example, you can use the approaches described at Avoid illegal xml characters with javascript and Detect non valid XML characters (javascript) to check if a cell value contains special symbols.
Apart from this, we also noticed that the '\u0007' symbol equals ' - BEL' (see https://en.wikipedia.org/wiki/ASCII ). The 'Tab' symbol has the '\u0009' code and works fine according to my tests. For instance, check the following example - https://codepen.io/ArtemDevExpress/pen/NLgqJG
Anyway, at the moment, the most reliable approach is to remove such symbols before export to avoid errors when a document is opened in Excel.
Let us know if you have additional questions.
See also:
Hi,
Another approach is to call exportDataGrid instead of the default export. exportDataGrid uses ExcelJS. ExcelJS converts 0x0007/0x000b symbols (invalid XML symbols) to ' ' (space, 0x20) and encodes xml symbols like '<'/'>' to </>
To cancel the default export, use onExporting and turn on cancel:
JavaScriptonExporting: e => {
var workbook = new ExcelJS.Workbook();
var worksheet = workbook.addWorksheet('Main sheet');
/*
The 'DevExpress.excelExporter.exportDataGrid' function uses the ExcelJS library.
For more information about ExcelJS, see:
- https://github.com/exceljs/exceljs#contents
- https://github.com/exceljs/exceljs#browser
*/
DevExpress.excelExporter.exportDataGrid({
component: e.component,
worksheet: worksheet,
}).then(function() {
workbook.xlsx.writeBuffer().then(function(buffer) {
saveAs(new Blob([buffer], { type: "application/octet-stream" }), "DataGrid.xlsx");
});
});
e.cancel = true;
},
Here is a jQuery based sample that illustrates this approach.
Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.
Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.
Hi Elena,
Please accept my apologies for the delayed response. Our built-in exporting mechanism doesn't have specific processing for these symbols. Would you please modify my example to illustrate what symbolscannot be exported? Use the "Fork" button to save changes and post the updated link here for further research.
Your time and cooperation are greatly appreciated.
Hi Artem,
I added the link with my changes (see first user Name).
Link: https://codepen.io/anon/pen/eLWJbJ?editors=1010
Hi Elena,
Thank you for the updated example. We need more time to research this behavior. Please bear with us.
I checked this example and see that this symbol is not displayed in the grid out of the box (see the screenshot). At the same time, I see that all symbols are visible in your screenshots.
If I paste the same symbol ("middle dot") directly from Excel to the previous array and display it in the grid, our export is working fine on my side. Please review the attached screencast and the modified example - https://codepen.io/ArtemDevExpress/pen/QVvMNV?editors=1010
Would you please clarify where these symbols are originally created and what code is used to display them in the grid?
Hi Artem,
In your screenshot this symbol is displaying like a square (quadrangle) in the grid. It`s a tab character, I paste it from my project to this example (if you move on middle dot in this example: https://codepen.io/anon/pen/eLWJbJ?editors=1010 you see "\u7" - tab character). After that I exporting data from grid and appears the same problem with Excel file. Can you copy this symbol from the example above to your example or modify this example and research it?
Thank you for your help!
Thank you for the clarification. I forwarded this issue to our R&D team for further research. Please bear with us.
Thank you, waiting for your answer.