converting csv to excel through java

Question

I have to convert CSV to XLS format through Java POI since I am doing some manipulations with XLS sheets through POI. Below is my code:

File file = new File("C:\\abc.csv");
FileInputStream fin = null;
fin = new FileInputStream(file);
HSSFWorkbook workbook = new HSSFWorkbook(fin); 
HSSFSheet firstSheet1 = workbook.getSheetAt(0);

Now I want to write a fuctions, lets say method name is convertcsvtoexcel which will accept the file obj and in return it will be give me converted XLS file that file will be stored in my C: drive with the name abcout.xls and later on I will be passing it to workbook as shown. I have tried the following code. Please advise how I can custoise it to make it fittable for my piece of code.

ArrayList arList = null;
ArrayList al = null;
String fName = "test.csv";
String thisLine;
int count = 0;

FileInputStream file = null;
file = new FileInputStream(new File("C:\\abc.csv"));

//FileInputStream fis = new FileInputStream(file);
DataInputStream myInput = new DataInputStream(file);
int i = 0;
arList = new ArrayList();
while ((thisLine = myInput.readLine()) != null) {
    al = new ArrayList();
    String strar[] = thisLine.split(",");
    for (int j = 0; j < strar.length; j++) {
        al.add(strar[j]);
    }
    arList.add(al);
    System.out.println();
    i++;
}

try {
    HSSFWorkbook hwb = new HSSFWorkbook();
    HSSFSheet sheet = hwb.createSheet("new sheet");
    for (int k = 0; k < arList.size(); k++) {
        ArrayList ardata = (ArrayList) arList.get(k);
        HSSFRow row = sheet.createRow((short) 0 + k);
        for (int p = 0; p < ardata.size(); p++) {
            HSSFCell cell = row.createCell((short) p);
            String data = ardata.get(p).toString();
            if (data.startsWith("=")) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                data = data.replaceAll("\"", "");
                data = data.replaceAll("=", "");
                cell.setCellValue(data);
            } else if (data.startsWith("\"")) {
                data = data.replaceAll("\"", "");
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(data);
            } else {
                data = data.replaceAll("\"", "");
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellValue(data);
            }
            //*/
            //   cell.setCellValue(ardata.get(p).toString());
        }
        System.out.println();
    }
    FileOutputStream fileOut = new FileOutputStream("C:\\abcout.xls");
    hwb.write(fileOut);
    fileOut.close();
    System.out.println("Your excel file has been generated");
} catch (Exception ex) {
    ex.printStackTrace();
} //main method end

Lots of duplicates: http://stackoverflow.com/questions/18077264/convert-csv-to-xls-xlsx-using-apache-poi, http://stackoverflow.com/questions/3189308/convert-csv-to-xls-in-java, etc. — Lydia Ralph, Jun 23 '15 at 14:34
@Alex Thanks but i want to develop a separate function please — user1906154, Jun 23 '15 at 14:40

score 0 · Answer 1 · answered Feb 02 '21 at 07:25

public static void csvToXLSX() {
    try {
        String csvFileAddress = "test.csv"; //csv file address
        String xlsxFileAddress = "test.xlsx"; //xlsx file address
        XSSFWorkbook workBook = new XSSFWorkbook();
        XSSFSheet sheet = workBook.createSheet("sheet1");
        String currentLine=null;
        int RowNum=0;
        BufferedReader br = new BufferedReader(new FileReader(csvFileAddress));
        while ((currentLine = br.readLine()) != null) {
            String str[] = currentLine.split(",");
            RowNum++;
            XSSFRow currentRow=sheet.createRow(RowNum);
            for(int i=0;i<str.length;i++){
                currentRow.createCell(i).setCellValue(str[i]);
            }
        }

        FileOutputStream fileOutputStream =  new FileOutputStream(xlsxFileAddress);
        workBook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("Done");
    } catch (Exception ex) {
        System.out.println(ex.getMessage()+"Exception in try");
    }
}

converting csv to excel through java

1 Answers1