[SPRING] Apache poi를 사용하여 csv를 xls / xlsx로 변환 하시겠습니까?

Apache poi를 사용하여 csv를 xls / xlsx로 변환 하시겠습니까?

내 프로젝트에서 csv를 xls / xlsx로 변환해야합니까? 내가 어떻게 할 수 있니? 누구든지 몇 가지 예를 게시 할 수 있습니까? 나는 아파치 포이 (poi poi)와 함께하고 싶다. 또한 자바 쪽에서 셀을 만들어야합니다.

해결법

1. ==============================

   1.apache-poi를 사용하여 xlsx 파일을 만드는 방법을 시도해 볼 수 있습니다.

   apache-poi를 사용하여 xlsx 파일을 만드는 방법을 시도해 볼 수 있습니다.

   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. ==============================

   2.우리는 SXSSF Jar를 사용하여 아래와 같이 긴 파일을 파싱 할 수 있습니다 :

   우리는 SXSSF Jar를 사용하여 아래와 같이 긴 파일을 파싱 할 수 있습니다 :

   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. ==============================

   3.나는 SXSSFWorkbook이 XSSFWorkbook보다 정말로 빠르다는 것을 발견했다. 다음은 수정 된 코드입니다.

   나는 SXSSFWorkbook이 XSSFWorkbook보다 정말로 빠르다는 것을 발견했다. 다음은 수정 된 코드입니다.

   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. ==============================

   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. ==============================

   5.입력 스트림이있는 경우이 방법을 사용해보십시오. public static XSSFWorkbook csvToXLSX (InputStream inputStream) throws IOException {         XSSFWorkbook workBook = 새 XSSFWorkbook ();

   입력 스트림이있는 경우이 방법을 사용해보십시오. public static XSSFWorkbook csvToXLSX (InputStream inputStream) throws IOException {         XSSFWorkbook workBook = 새 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. ==============================

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

from https://stackoverflow.com/questions/18077264/convert-csv-to-xls-xlsx-using-apache-poi by cc-by-sa and MIT license