Python处理Excel文件常见问题解析与解决方案

pip install openpyxl

df = pd.read_excel(file_path, engine='openpyxl')

import os

if not os.path.exists(file_path):
    raise FileNotFoundError(f"文件不存在: {file_path}")

pip install pandas openpyxl

import pandas as pd

def read_excel_safely(file_path):
    try:
        return pd.read_excel(file_path, engine='openpyxl')
    except ImportError:
        return pd.read_excel(file_path)  # 回退到默认引擎

import os

def validate_file_path(file_path):
    if not os.path.exists(file_path):
        raise FileNotFoundError(f"文件不存在: {file_path}")
    if not file_path.endswith(('.xlsx', '.xls')):
        raise ValueError("仅支持 .xlsx 或 .xls 文件")

def check_required_columns(df, required_columns):
    missing_columns = [col for col in required_columns if col not in df.columns]
    if missing_columns:
        raise ValueError(f"缺少必要列: {missing_columns}")

import re

def clean_text(text):
    return text.strip() if text else ""

def extract_province_city(address):
    province_pattern = r'(北京市|天津市|...|澳门特别行政区)'
    match = re.search(province_pattern, address)
    province = match.group(1) if match else ""
    
    if province:
        remaining = address[match.end():]
        city_match = re.search(r'([^市]+市)', remaining)
        city = city_match.group(1) if city_match else ""
    return province, city

import pandas as pd
import os
import re

def process_recipient_info(file_path):
    try:
        validate_file_path(file_path)
        df = read_excel_safely(file_path)
        check_required_columns(df, ['收件人姓名', '运单号', '系统订单号', '收件人手机', '收件人详细地址'])
        
        processed_data = []
        for _, row in df.iterrows():
            name = clean_text(str(row['收件人姓名']))
            phone = re.sub(r'\D', '', str(row['收件人手机']))
            province, city = extract_province_city(str(row['收件人详细地址']))
            
            processed_data.append({
                'name': name,
                'phone': phone,
                'province': province,
                'city': city
            })
        return processed_data
    except Exception as e:
        print(f"处理失败: {e}")
        return []

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

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

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelReader {
    public static List<Recipient> readRecipients(String filePath) {
        List<Recipient> recipients = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(fis)) {
            
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                String name = row.getCell(0).getStringCellValue();
                String phone = row.getCell(1).getStringCellValue();
                String address = row.getCell(2).getStringCellValue();
                
                recipients.add(new Recipient(name, phone, address));
            }
        } catch (Exception e) {
            System.err.println("读取Excel失败: " + e.getMessage());
        }
        return recipients;
    }
}

class Recipient {
    private String name;
    private String phone;
    private String address;
    
    // Constructor, Getters, Setters...
}