There are several problems with the code above. String text = formatter.formatCellValue(cell) PrintStream out = new PrintStream(new FileOutputStream(csvFile), Workbook wb = new XSSFWorkbook(new File(xlsxFile)) ĭataFormatter formatter = new DataFormatter() The first cut of the approach to convert Excel spreadsheet data to CSV is shown in the block below. Building the program requires this dependency to be declared in pom.xml (assuming Maven for the build) as follows: We use Apache POI to read the Excel spreadsheet. Joel penned the article in 2003, long after leaving the Excel group. Ironically, Joel was (till 1995?) the Project Manager for the Excel team. Maybe the Excel developers have not read this article by Joel Spolsky stressing the need for programmers to be aware of Unicode. It is indeed a surprise that the Excel team has not yet figured out in 2017 that people may have Unicode data that need to be exported properly to CSV. The proper solution is to use Unicode (specifically UTF-8) for encoding the CSV file so all your data is preserved. This leaves your CSV output from Excel severely broken if it contains such characters. This character set is a very limited set and cannot represent characters from most foreign languages. It appears that Excel uses Windows default character set Windows-1252 (or cp-1252) to perform the export. Specifically, if your spreadsheet contains Unicode data, you are out of luck. While it gets the job done in most cases, it leaves something to be desired. But Why?Įxcel directly provides for exporting CSV data using the Save As functionality in the File menu. We use the Apache POI library for the purpose. In this article, we show you a way of exporting the data in an Excel spreadsheet to CSV. While the Excel Spreadsheet file format is complex (since it has to accommodate a lot more!), CSV is a simpler format representing just tabular data. Let us look into how to convert Excel to CSV.ĬSV stands for Comma-Separated-Values and is a very common format used for exchanging data between diverse applications.
0 Comments
Leave a Reply. |