Java实现Excel数据处理：KNN算法填充、数据补充及折线图展示

本文将介绍如何使用Java处理Excel文件，具体步骤如下：

使用名为knn的Java程序处理Excel文件。2. 读取Excel文件中每个月的数据量。3. 将每个月少于五组数据的情况补充至五组。4. 将原数据和新补充的数据在最后的Excel文件中用折线图展示。

knn.java 程序java// 导入必要的库package org.example;

import java.io.FileInputStream;import java.io.FileOutputStream;import java.text.DecimalFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.*;

import org.apache.poi.ss.usermodel.;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFChart;import org.apache.poi.xssf.usermodel.XSSFDrawing;import org.openxmlformats.schemas.drawingml.x2006.chart.;

public class knn {

public static void main(String[] args) {        // 定义输入文件和输出文件的路径        String inputFile = 'input.xlsx';        String outputFile = 'output.xlsx';        try (Workbook workbook = WorkbookFactory.create(new FileInputStream(inputFile));             FileOutputStream outputStream = new FileOutputStream(outputFile)) {            Sheet sheet = workbook.getSheetAt(0);            DecimalFormat df = new DecimalFormat('#.##');

        // 读取Excel文件中每个月的数据量            List<Integer> monthDataCount = new ArrayList<>();            for (int i = 1; i <= sheet.getLastRowNum(); i++) {                Row row = sheet.getRow(i);                if (row != null) {                    Cell cell = row.getCell(0);                     if (cell != null && cell.getCellType() == CellType.NUMERIC) {                        Date date = cell.getDateCellValue();                        int month = date.getMonth() + 1;                        if (monthDataCount.size() < month) {                            monthDataCount.add(1);                        } else {                            monthDataCount.set(month - 1, monthDataCount.get(month - 1) + 1);                        }                    }                }            }

        // 对每一行进行处理，填充缺失值            for (int i = 1; i <= sheet.getLastRowNum(); i++) {                Row row = sheet.getRow(i);                if (row != null) {                    Cell cell = row.getCell(1);                    if (cell == null || cell.getCellType() == CellType.BLANK) {                        double avg = calculateKNN(sheet, i, 1);                        if (avg > 0) {                            if(cell == null){                                cell = row.createCell(1);                            }                            cell.setCellValue(Double.parseDouble(df.format(avg)));                         }                    }                }            }

        // 补充缺失的数据            for (int i = 0; i < monthDataCount.size(); i++) {                int count = monthDataCount.get(i);                if (count < 5) {                    for (int j = count; j < 5; j++) {                        Row lastRow = sheet.getRow(sheet.getLastRowNum());                        Row newRow = sheet.createRow(sheet.getLastRowNum() + 1);                        for (int k = 0; k < lastRow.getLastCellNum(); k++) {                            Cell lastCell = lastRow.getCell(k);                            Cell newCell = newRow.createCell(k);                            if (lastCell != null) {                                newCell.setCellStyle(lastCell.getCellStyle());                            }                            if (k == 0) {                                Calendar calendar = Calendar.getInstance();                                calendar.set(Calendar.MONTH, i);                                calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));                                Date date = calendar.getTime();                                newCell.setCellValue(date);                            } else {                                newCell.setCellValue(Math.random() * 100);                            }                        }                    }                }            }                        // 创建折线图            createLineChart(sheet, monthDataCount);

        workbook.write(outputStream);             System.out.println('Data processing and chart creation completed.');         } catch (Exception e) {            e.printStackTrace();        }    }

// 计算KNN邻近算法填充的值    private static double calculateKNN(Sheet sheet, int rowIndex, int columnIndex) {        List<Double> data = new ArrayList<>();         for (int i = 0; i <= sheet.getLastRowNum(); i++) {             Row row = sheet.getRow(i);             if (row != null) {                Cell cell = row.getCell(columnIndex);                if (cell != null && cell.getCellType() == CellType.NUMERIC) {                    data.add(cell.getNumericCellValue());                }            }        }        if (data.size() > 0) {             double missingValue = 0;            Row row = sheet.getRow(rowIndex);             if (row != null) {                Cell cell = row.getCell(columnIndex);                 if (cell == null || cell.getCellType() == CellType.BLANK) {                     missingValue = 0;                 } else if (cell.getCellType() == CellType.NUMERIC) {                    missingValue = cell.getNumericCellValue();                 } else if (cell.getCellType() == CellType.STRING) {                    try {                        missingValue = Double.parseDouble(cell.getStringCellValue());                     } catch (NumberFormatException e) {                        missingValue = 0;                    }                }            }            if (missingValue > 0) {                return missingValue;             } else {                List<Double> distances = new ArrayList<>();                for (double value : data) {                     double distance = Math.abs(value - missingValue);                    distances.add(distance);                 }                Collections.sort(distances, new Comparator<Double>() {                    @Override                    public int compare(Double o1, Double o2) {                        return Double.compare(o1, o2);                    }                });                int k = 3;                 double sum = 0;                int count = 0;                 for (int i = 0; i < k && i < distances.size(); i++) {                     double value = data.get(distances.indexOf(distances.get(i)));                    sum += value;                     count++;                 }                if (count > 0) {                     return sum / count;                } else {                    return 0;                 }            }        } else {            return 0;         }    }        private static void createLineChart(Sheet sheet, List<Integer> monthDataCount) {        // ... (创建折线图的代码，与之前提供的代码相同) ...    }

// 解析日期    private static Date parseDate(String dateString) {        SimpleDateFormat dateFormat = new SimpleDateFormat('yyyy/MM/dd HH:mm');        try {            return dateFormat.parse(dateString);        } catch (ParseException e) {            e.printStackTrace();            return null;         }