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