Convert csv to xls/xlsx using Apache poi?

问题:

I need to convert csv to xls/xlsx in my project? How can i do that? Can anyone post me some examples? I want to do it with Apache poi. I also need to create a cell from java side.

回答1:

You can try following method to create xlsx file using apache-poi.

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");
    }
}

回答2:

We can use SXSSF Jar in which we can parse a long file as below:

public static void main( String[] args ) {
  try {

    //   String fName = args[ 0 ];

    String csvFileAddress = "C:\\Users\\psingh\\Desktop\\test\\New folder\\GenericDealerReport - version6.txt"; //csv file address
    String xlsxFileAddress = "C:\\Users\\psingh\\Desktop\\trial\\test3.xlsx"; //xlsx file address

    SXSSFWorkbook workBook = new SXSSFWorkbook( 1000 );
    org.apache.poi.ss.usermodel.Sheet sheet = workBook.createSheet( "sheet1" );
    String currentLine = null;
    int RowNum = -1;
    BufferedReader br = new BufferedReader( new FileReader( csvFileAddress ) );
    while ( ( currentLine = br.readLine() ) != null ) {
      String str[] = currentLine.split( "\\|" );
      RowNum++;
      Row currentRow = sheet.createRow( RowNum );
      for ( int i = 0; i < str.length; i++ ) {
        currentRow.createCell( i )
                .setCellValue( str[ i ] );
      }
    }
    DateFormat df = new SimpleDateFormat( "yyyy-mm-dd-HHmmss" );
    Date today = Calendar.getInstance()
                       .getTime();
    String reportDate = df.format( today );
    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" );
  }
}

回答3:

I have found SXSSFWorkbook really faster then XSSFWorkbook. Here is the modified code:

try {
        String csvFileInput = "inputFile.csv";
        String xlsxFileOutput ="outputFile.xls";

        LOGGER.error(csvFileInput);
        LOGGER.error( xlsxFileOutput);
        SXSSFWorkbook workBook = new SXSSFWorkbook();
        Sheet sheet = workBook.createSheet(transformBean.getOutputFileName());
        String currentLine = null;
        int RowNum = 0;
        BufferedReader br = new BufferedReader(new FileReader(csvFileInput));
        while ((currentLine = br.readLine()) != null) {
            String str[] = currentLine.split(",");
            RowNum++;
            Row currentRow = sheet.createRow(RowNum);
            for (int i = 0; i < str.length; i++) {
                currentRow.createCell(i).setCellValue(str[i]);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(xlsxFileOutput);
        workBook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("Done");
    } catch (Exception ex) {
        System.out.println(ex.getMessage() + "Found Exception");
    }

回答4:

if(new File(newFileName).isFile()) return;
    @SuppressWarnings("resource")
    HSSFWorkbook wb = new HSSFWorkbook();
    Row xlsRow;
    Cell xlsCell;
    HSSFSheet sheet = wb.createSheet("sheet1");
    int rowIndex = 0;
    for(CSVRecord record : CSVFormat.EXCEL.parse(new FileReader(fileName))) {
        xlsRow = sheet.createRow(rowIndex);
        for(int i = 0; i < record.size(); i ++){
            xlsCell = xlsRow.createCell(i);
            xlsCell.setCellValue(record.get(i));
        }
        rowIndex ++;
    }
    FileOutputStream out = new FileOutputStream(newFileName);
    wb.write(out);
    out.close();

回答5:

Try this one if you have inputstream public static XSSFWorkbook csvToXLSX(InputStream inputStream) throws IOException { XSSFWorkbook workBook = new XSSFWorkbook();

    try(BufferedReader br = new BufferedReader(new InputStreamReader(inputStream))) {
        Sheet sheet = workBook.createSheet("sheet1");
        String currentLine=null;
        int rowNum=0;

        while ((currentLine = br.readLine()) != null) {
            String[] str = currentLine.split(",");
            rowNum++;
            Row currentRow=sheet.createRow(rowNum);
            for(int i=0;i<str.length;i++){
                currentRow.createCell(i).setCellValue(str[i]);
            }
        }

        log.info("CSV file converted to the workbook");
        return workBook;
    } catch (Exception ex) {
        log.error("Exception while converting csv to xls {}",ex);
    }finally {
        if (Objects.nonNull(workBook)) {
            workBook.close();
        }
    }
    return workBook;
}

回答6:

public static void convertCsvToXlsx(String xlsLocation, String csvLocation) throws Exception {
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    SXSSFSheet sheet = workbook.createSheet("Sheet");
    AtomicReference<Integer> row = new AtomicReference<>(0);
    Files.readAllLines(Paths.get(csvLocation)).forEach(line -> {
        Row currentRow = sheet.createRow(row.getAndSet(row.get() + 1));
        String[] nextLine = line.split(",");
        Stream.iterate(0, i -> i + 1).limit(nextLine.length).forEach(i -> {
            currentRow.createCell(i).setCellValue(nextLine[i]);
        });
    });
    FileOutputStream fos = new FileOutputStream(new File(xlsLocation));
    workbook.write(fos);
    fos.flush();
}