Subversion Repositories SmartDukaan

package com.spice.profitmandi.common.util;

import org.springframework.beans.factory.annotation.Value;

import java.io.File;

import java.nio.charset.StandardCharsets;

import java.nio.file.*;

import java.sql.*;

import java.util.List;

import java.util.Map;

import java.util.Set;

public class CsvUtil {

    private CsvUtil() {

    }

    private static final String connURL = "jdbc:mysql://192.168.142.141:3306/auth";

    private static final String username = "root";

    private static final String password = "shop2020";

    public static File generateCsvFromQuery(String query, String headers) throws Exception {

        Connection conn = DriverManager.getConnection(connURL, username, password);

        PreparedStatement stmt = conn.prepareStatement(query);

        ResultSet rs = stmt.executeQuery();

        ResultSetMetaData metaData = rs.getMetaData();

        int columnCount = metaData.getColumnCount();

        StringBuilder csvBuilder = new StringBuilder();

        // Headers

        if (headers != null && !headers.trim().isEmpty()) {

            csvBuilder.append(headers.trim()).append("\n");

        } else {

            for (int i = 1; i <= columnCount; i++) {

                csvBuilder.append(metaData.getColumnLabel(i));

                if (i < columnCount) csvBuilder.append(",");

            }

            csvBuilder.append("\n");

        }

        // Rows

        while (rs.next()) {

            for (int i = 1; i <= columnCount; i++) {

                String value = rs.getString(i);

                csvBuilder.append(value != null ? value.replaceAll(",", " ") : "");

                if (i < columnCount) csvBuilder.append(",");

            }

            csvBuilder.append("\n");

        }

        rs.close();

        stmt.close();

        conn.close();

        Path tempFile = Files.createTempFile("query-result-", ".csv");

        Files.write(tempFile, csvBuilder.toString().getBytes());

        return tempFile.toFile();

    }

    public static File generateCsvFromListMap(List<Map<String, Object>> data, String headers) throws Exception {

        StringBuilder csvBuilder = new StringBuilder();

        if (headers != null && !headers.trim().isEmpty()) {

            csvBuilder.append(headers.trim()).append("\n");

        } else if (!data.isEmpty()) {

            Set<String> keys = data.get(0).keySet();

            csvBuilder.append(String.join(",", keys)).append("\n");

        }

        for (Map<String, Object> row : data) {

            int i = 0;

            for (Object value : row.values()) {

                String cell = value != null ? value.toString().replaceAll(",", " ") : "";

                csvBuilder.append(cell);

                if (++i < row.size()) csvBuilder.append(",");

            }

            csvBuilder.append("\n");

        }

        Path tempFile = Files.createTempFile("query-result-", ".csv");

        Files.write(tempFile, csvBuilder.toString().getBytes(StandardCharsets.UTF_8));

        return tempFile.toFile();

    }

}