Java Convert CSV to XLSX

Question

I am trying to create a program that would convert .csv file to .xlsx file using Apache POI. Everything works fine except that the .xlsx file generated does not display german letters with umlaut (ü,ä,ö) properly. It simply prints a question mark. Would anyone be so kind as to help me with this? Any help is much appreciated.

Here is the code that I have written. I am not an expert in Java so I apologize in advance if my code seems pretty basic.

private static void csvtoxlsx() {
    try{
        String csvfile = "test.csv";
        String xlsxfile = "test-changed.xlsx";
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("sheet1");
        XSSFFont xssfFont = workbook.createFont();
        xssfFont.setCharSet(XSSFFont.ANSI_CHARSET);
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(xssfFont);
        String currentLine;
        int RowNum = 0;
        BufferedReader br = new BufferedReader(new FileReader(csvfile));
        while((currentLine = br.readLine()) != null){
            String[] str = currentLine.split(";");
            RowNum++;
            XSSFRow currentRow = sheet.createRow(RowNum);
            for(int i=0; i< str.length; i++){
                str[i] = str[i].replaceAll("\"","");
                str[i] = str[i].replaceAll("=","");
                XSSFCell cell = currentRow.createCell(i);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(str[i].trim());
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(xlsxfile);
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        System.out.println("success");
    }catch (Exception e){
        e.printStackTrace();
    }
}

You are probably reading the CV file incorrectly. Try to pass an encoding to the FileReader — user330315
– user330315, Commented Feb 20, 2020 at 17:09

heyitsvajid · Accepted Answer · 2020-02-20 17:22:33Z

2

You could pass encoder like below in the BufferedReader and that would be able to read the characters properly as you expect.

        FileReader fileReader = new FileReader(file);
        BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream("test.csv"), "windows-1252")); // Or try "Cp1252" in place of "windows-1252"

        String lineOfText;
        while ((lineOfText = br.readLine()) != null) {
            //do something
        }
        br.close();

Reference: https://docs.oracle.com/javase/7/docs/technotes/guides/intl/encoding.doc.html

answered Feb 20, 2020 at 17:22

heyitsvajid

1,2311 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Juan Moreno Over a year ago

Consider the use of Files.newBufferedReader method (from java 8 and onwards) and close the resource properly with the try-with-resources statement or a finally block. More details here: rules.sonarsource.com/java/RSPEC-2095

Collectives™ on Stack Overflow

Java Convert CSV to XLSX

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related