Java 销售数据统计分析和 Excel 导出代码示例

下面是一个示例的 Java 代码，用于实现对销售数据进行统计分析，并将统计结果导出为 Excel 文件。

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

public class SalesDataStatistics {
    public static void main(String[] args) {
        // 模拟销售数据
        List<SalesRecord> salesData = generateSalesData();

        // 年统计
        Map<Integer, Double> yearlyStatistics = calculateYearlyStatistics(salesData);
        exportStatisticsToExcel(yearlyStatistics, "YearlyStatistics.xlsx");
        
        // 月统计
        Map<String, Double> monthlyStatistics = calculateMonthlyStatistics(salesData);
        exportStatisticsToExcel(monthlyStatistics, "MonthlyStatistics.xlsx");
        
        // 周统计
        Map<String, Double> weeklyStatistics = calculateWeeklyStatistics(salesData);
        exportStatisticsToExcel(weeklyStatistics, "WeeklyStatistics.xlsx");
        
        // 日统计
        Map<String, Double> dailyStatistics = calculateDailyStatistics(salesData);
        exportStatisticsToExcel(dailyStatistics, "DailyStatistics.xlsx");
    }

    // 生成模拟销售数据
    private static List<SalesRecord> generateSalesData() {
        List<SalesRecord> salesData = new ArrayList<>();

        // 添加模拟销售记录
        salesData.add(new SalesRecord("ProductA", 2021, 1, 1, 1000));
        salesData.add(new SalesRecord("ProductA", 2021, 1, 2, 2000));
        salesData.add(new SalesRecord("ProductA", 2021, 1, 3, 1500));
        salesData.add(new SalesRecord("ProductA", 2021, 2, 1, 2500));
        salesData.add(new SalesRecord("ProductA", 2021, 2, 2, 1800));
        salesData.add(new SalesRecord("ProductA", 2021, 2, 3, 1200));
        salesData.add(new SalesRecord("ProductB", 2021, 1, 1, 500));
        salesData.add(new SalesRecord("ProductB", 2021, 1, 2, 800));
        salesData.add(new SalesRecord("ProductB", 2021, 1, 3, 700));
        salesData.add(new SalesRecord("ProductB", 2021, 2, 1, 900));
        salesData.add(new SalesRecord("ProductB", 2021, 2, 2, 1100));
        salesData.add(new SalesRecord("ProductB", 2021, 2, 3, 600));

        return salesData;
    }

    // 计算年统计
    private static Map<Integer, Double> calculateYearlyStatistics(List<SalesRecord> salesData) {
        Map<Integer, Double> yearlyStatistics = new HashMap<>();

        for (SalesRecord record : salesData) {
            int year = record.getYear();
            double salesAmount = yearlyStatistics.getOrDefault(year, 0.0);
            salesAmount += record.getSalesAmount();
            yearlyStatistics.put(year, salesAmount);
        }

        return yearlyStatistics;
    }

    // 计算月统计
    private static Map<String, Double> calculateMonthlyStatistics(List<SalesRecord> salesData) {
        Map<String, Double> monthlyStatistics = new HashMap<>();

        for (SalesRecord record : salesData) {
            String key = record.getYear() + "-" + record.getMonth();
            double salesAmount = monthlyStatistics.getOrDefault(key, 0.0);
            salesAmount += record.getSalesAmount();
            monthlyStatistics.put(key, salesAmount);
        }

        return monthlyStatistics;
    }

    // 计算周统计
    private static Map<String, Double> calculateWeeklyStatistics(List<SalesRecord> salesData) {
        Map<String, Double> weeklyStatistics = new HashMap<>();

        for (SalesRecord record : salesData) {
            Calendar cal = Calendar.getInstance();
            cal.set(Calendar.YEAR, record.getYear());
            cal.set(Calendar.MONTH, record.getMonth() - 1);
            cal.set(Calendar.DAY_OF_MONTH, record.getDay());

            int weekOfYear = cal.get(Calendar.WEEK_OF_YEAR);
            int year = cal.get(Calendar.YEAR);

            String key = year + "-" + "W" + weekOfYear;
            double salesAmount = weeklyStatistics.getOrDefault(key, 0.0);
            salesAmount += record.getSalesAmount();
            weeklyStatistics.put(key, salesAmount);
        }

        return weeklyStatistics;
    }

    // 计算日统计
    private static Map<String, Double> calculateDailyStatistics(List<SalesRecord> salesData) {
        Map<String, Double> dailyStatistics = new HashMap<>();

        for (SalesRecord record : salesData) {
            String key = record.getYear() + "-" + record.getMonth() + "-" + record.getDay();
            double salesAmount = dailyStatistics.getOrDefault(key, 0.0);
            salesAmount += record.getSalesAmount();
            dailyStatistics.put(key, salesAmount);
        }

        return dailyStatistics;
    }

    // 导出统计结果为Excel文件
    private static void exportStatisticsToExcel(Map<?, Double> statistics, String filename) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Statistics");

        int rowNum = 0;
        for (Map.Entry<?, Double> entry : statistics.entrySet()) {
            Row row = sheet.createRow(rowNum++);
            Cell keyCell = row.createCell(0);
            keyCell.setCellValue(entry.getKey().toString());
            Cell valueCell = row.createCell(1);
            valueCell.setCellValue(entry.getValue());
        }

        try (FileOutputStream outputStream = new FileOutputStream(filename)) {
            workbook.write(outputStream);
            System.out.println("统计结果已导出为Excel文件：" + filename);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

class SalesRecord {
    private String product;
    private int year;
    private int month;
    private int day;
    private double salesAmount;

    public SalesRecord(String product, int year, int month, int day, double salesAmount) {
        this.product = product;
        this.year = year;
        this.month = month;
        this.day = day;
        this.salesAmount = salesAmount;
    }

    public String getProduct() {
        return product;
    }

    public int getYear() {
        return year;
    }

    public int getMonth() {
        return month;
    }

    public int getDay() {
        return day;
    }

    public double getSalesAmount() {
        return salesAmount;
    }
}

上述代码使用了 Apache POI 库来操作 Excel 文件。请确保在编译和运行代码时已添加相关的依赖库。

在示例代码中，通过 generateSalesData 方法生成了模拟的销售数据。然后，分别实现了年统计、月统计、周统计和日统计的功能。统计结果保存在 Map 中，然后使用 exportStatisticsToExcel 方法将结果导出为 Excel 文件。

代码中的 SalesRecord 类表示一条销售记录，包含产品、年份、月份、日期和销售金额等属性。

请根据实际需求进行适当调整和修改代码。