I downloaded the data and read by StringBuilder and achieved this result (The content of my StringBuilder)

User Manager_ID Date Humidity Temperature Pressure Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8
dt-001 N5_220 09/19/2021 20.0 20.0 1000.0 1 2 3 4 5 1 2 3
dt-002 N5_220 09/25/2021 80.0 30.0 1200.0 4 5 1 2 3 4 5 1

Is there any method, library, or someone else has a solution for specifying semicolons between data? Of course, except at the end of the line. I will be grateful for your help :) What I'd like to achieve:

User; Manager_ID; Date; Humidity; Temperature; Pressure; Q1; Q2; Q3; Q4; Q5; Q6; Q7; Q8
dt-001; N5_220; 09/19/2021; 20.0; 20.0; 1000.0; 1; 2; 3; 4; 5; 1; 2; 3
dt-002; N5_220; 09/25/2021; 80.0; 30.0; 1200.0; 4; 5; 1; 2; 3; 4; 5; 1

Code:

public void download() throws IOException {
        List<FileDTO> tests = testService.getTestFileDtoList(); // from repo
        XSLXManagerTestExporter excelExporter = new XSLXManagerTestExporter(tests );
        XSSFWorkbook workBook = excelExporter.export(); // get XSSFWorkbook
        XSSFSheet selSheet = workBook.getSheet("sheet");
        StringBuilder sb = new StringBuilder();

        for (int i = 0; i <= selSheet.getLastRowNum(); i  ) {
            Iterator<Cell> cellIterator = selSheet.getRow(i).cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                if (sb.length() != 0)
                    sb.append(" ");
                sb.append(cell.getStringCellValue());
            }
        }
        workBook.close();
        writeBytesToFileApache(sb.toString().getBytes());
    }


 private static void writeBytesToFileApache(byte[] bytes)
            throws IOException {
        FileUtils.writeByteArrayToFile(new File(".....\\file.csv"), bytes);

    }

This is my XLSX converter to StringBuilder. I'd like to heve this as .CSV

CodePudding user response：

Have you considered using a StringJoiner?

public void download() throws IOException {
        List<FileDTO> tests = testService.getTestFileDtoList(); // from repo
        XSLXManagerTestExporter excelExporter = new XSLXManagerTestExporter(tests );
        XSSFWorkbook workBook = excelExporter.export(); // get XSSFWorkbook
        XSSFSheet selSheet = workBook.getSheet("sheet");

        List<String> csvLines = new ArrayList<>();
        for (Row row : selSheet) {
            StringJoiner sj = new StringJoiner(";");
            for (Cell cell : row) {
                sj.add(cell.getStringCellValue());
            }
            csvLines.add(sj.toString());
        }
        workBook.close();
        writeBytesToFileApache(csvLines().stream().collect(Collectors.joining("\n")).getBytes());
    }

You may also want to consider a FileOutputStream instead of a List (or String Joiner or any other solution which essentially builds the entire CSV in memory and flushes it in a single go). It should be much more memory efficient, allowing you to deal with (potentially very) large excel files.

CodePudding user response：

Try the following:

    public void download() throws IOException {
        List<FileDTO> tests = testService.getTestFileDtoList(); // from repo
        XSLXManagerTestExporter excelExporter = new XSLXManagerTestExporter(tests);
        XSSFWorkbook workBook = excelExporter.export(); // get XSSFWorkbook
        XSSFSheet selSheet = workBook.getSheet("sheet");
        StringBuilder sb = new StringBuilder();
        final String LINESEP = System.getProperty("line.separator");

        for (int i = 0; i <= selSheet.getLastRowNum(); i  ) {
            Iterator<Cell> cellIterator = selSheet.getRow(i).cellIterator();
            String sep = "";
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                sb.append(sep).sb.append(cell.getStringCellValue());
                sep = ";";
            }
            sb.append(LINESEP);
        }
        workBook.close();
        writeBytesToFileApache(sb.toString().getBytes());
    }