# 串口通信数据采集与Excel自动写入系统

**Repository Path**: zhouyong_bing/LED

## Basic Information

- **Project Name**: 串口通信数据采集与Excel自动写入系统
- **Description**: 基于 Python 开发的串口数据采集与 Excel 自动化写入工具，主要面向工业设备数据采集、实验室仪器数据记录等应用场景，实现了从串口设备到 Excel 表格的数据全流程自动化处理
- **Primary Language**: Python
- **License**: MIT
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 1
- **Created**: 2026-07-12
- **Last Updated**: 2026-07-12

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# LED Serial Data Collector / LED 串口数据采集器

[![Python](https://img.shields.io/badge/Python-3.8+-blue.svg)](https://www.python.org/)
[![Platform](https://img.shields.io/badge/Platform-Windows-lightgrey.svg)]()
[![License](https://img.shields.io/badge/License-MIT-green.svg)](LICENSE)

**English** | [中文](#中文)

A Python-based serial port data acquisition tool that reads HEX data from COM ports, parses IEEE754 floating-point and INT16 values with CRC16-MODBUS validation, and writes results to WPS/Excel spreadsheets in real time.

---

<a name="中文"></a>
## 中文

基于 Python 的串口数据采集工具——从 COM 口读取 HEX 数据，解析 IEEE754 浮点数与 INT16 整数值，并通过 CRC16-MODBUS 校验后实时写入 WPS/Excel 表格。

## Features / 功能特性

- **Serial Communication / 串口通信** — Configurable COM port, baud rate, data bits, stop bits, and parity
- **HEX Data Parsing / HEX 数据解析** — Parses IEEE754 single-precision floats (big-endian) and INT16 signed integers from raw byte streams
- **CRC16-MODBUS Validation / CRC16 校验** — MODBUS CRC16 with polynomial 0xA001 for data integrity verification
- **Real-time Excel Writing / 实时表格写入** — Writes parsed data to WPS Spreadsheet or Microsoft Excel via COM automation
- **Multi-threaded / 多线程** — Separate threads for serial reading and Excel writing to ensure real-time performance
- **Auto-reconnect & Restart / 自动重连与重启** — Automatic WPS reconnection on failure; `run.bat` provides auto-restart loop
- **Manual HEX Verifier / 手动验证工具** — `verify_hex.py` allows manual HEX string input for offline testing and CRC verification

## Data Flow / 数据流向

```
Serial Port (COMx)
      ↓  raw HEX bytes
  CRC16 MODBUS validation
      ↓  verified data
  IEEE754 float + INT16 parsing
      ↓  numerical values
  Multi-threaded data pipeline
      ↓
  WPS/Excel Spreadsheet (Column B)
```

## Requirements / 环境要求

- **OS**: Windows (COM port + WPS/Excel COM automation)
- **Python**: 3.8+
- **Office**: WPS Office or Microsoft Excel
- **Serial Device**: Any device outputting HEX-encoded sensor data

## Installation / 安装

```bash
git clone https://gitee.com/zhizhixiazh/LED.git
cd LED
pip install -r requirements.txt
```

### Dependencies / 依赖

| Package  | Version | Purpose |
|----------|---------|---------|
| pywin32  | >=305   | WPS/Excel COM automation |
| pyserial | >=3.5   | Serial port communication |

## Configuration / 配置

Edit `config/config.py`:

```python
# Excel settings
EXCEL_CONFIG = {
    "file_path": r"C:\Users\xzh\Desktop\LED.xlsx",  # Excel file path
    "sheet_name": "data",                              # Sheet name
    "data_rows": 66,                                   # Number of data rows
    "update_interval": 2.0,                            # Update interval (seconds)
}

# Serial port settings
SERIAL_CONFIG = {
    "port": "COM7",              # COM port
    "baud_rate": 115200,         # Baud rate
    "data_bits": 8,              # Data bits
    "stop_bits": 1,              # Stop bits
    "parity": "N",               # Parity (N/E/O)
    "timeout": 1.0,              # Timeout (seconds)
    "data_offset": 7,            # IEEE754 float start byte offset
    "data_offset_int16": 191,    # INT16 integer start byte offset
}
```

## Usage / 使用方法

### Main Program / 主程序

```bash
# Direct run
python wps_excel_writer.py

# Or use the auto-restart launcher
run.bat
```

The program will:
1. List available COM ports
2. Connect to the configured serial port
3. Read and parse HEX data every N seconds
4. Write parsed values to Excel column B

### Manual HEX Verification / 手动 HEX 验证

```bash
python verify_hex.py
```

Paste a HEX string (e.g., `05 10 00 00 00 70 E0 ... 8B 1F`) to verify CRC and see parsed values without a serial device.

### Serial Port Listing / 串口列表

```bash
python port.py
```

Lists all available COM ports with descriptions and hardware IDs.

## Data Format / 数据格式

The incoming HEX byte stream structure:

| Byte Range | Content | Format |
|---|---|---|
| 0 to `data_offset - 1` | Header / metadata | Raw bytes |
| `data_offset` to `data_offset_int16 - 1` | Sensor values | IEEE754 float32 (big-endian, 4 bytes each) |
| `data_offset_int16` to `end - 2` | Additional values | INT16 signed (big-endian, 2 bytes each) |
| Last 2 bytes | CRC16 MODBUS | Little-endian |

Example with `data_offset=7`, `data_offset_int16=191`:
- Bytes 7–190: 46 IEEE754 floats (184 bytes ÷ 4)
- Bytes 191 to CRC: INT16 values (2 bytes each)
- Last 2 bytes: CRC16 checksum

## Project Structure / 项目结构

```
LED/
├── port.py              # Serial port manager (read, parse, CRC)
├── wps_excel_writer.py  # Main program (serial → Excel pipeline)
├── verify_hex.py        # Manual HEX verification & CRC tool
├── run.bat              # Auto-restart launcher
├── config/
│   ├── __init__.py      # Package init
│   └── config.py        # All configuration constants
├── requirements.txt     # Python dependencies
├── LICENSE              # MIT License
└── README.md            # This file
```

## License / 许可证

[MIT License](LICENSE) — Copyright (c) 2026 夏之

---

## Notes / 注意事项

- This tool is designed for **Windows only** due to COM port and WPS/Excel COM automation dependencies.
- Ensure the target Excel file exists before running the main program.
- If WPS fails to connect, the program tries Microsoft Excel as a fallback (configured in `WPS_CONFIG`).
- CRC16 validation is **disabled by default** in `port.py` (`read_data` method) — uncomment the CRC section to enable.