使用C#实现Excel实时读取并导入SQL数据库

using system.io;

public class excelmonitor {
    private filesystemwatcher _watcher;
    private string _filepath = @"c:\data\input.xlsx";
    private string _connectionstring = "your sql connection string";

    public event eventhandler<filechangedeventargs> filechanged;

    public excelmonitor() {
        initializewatcher();
    }

    private void initializewatcher() {
        _watcher = new filesystemwatcher();
        _watcher.path = path.getdirectoryname(_filepath);
        _watcher.filter = path.getfilename(_filepath);
        _watcher.notifyfilter = notifyfilters.lastwrite | notifyfilters.filename;
        _watcher.changed += onfilechanged;
        _watcher.created += onfilechanged;
        _watcher.enableraisingevents = true;
    }

    private void onfilechanged(object sender, filesystemeventargs e) {
        if (path.getextension(e.fullpath).tolower() == ".xlsx") {
            filechanged?.invoke(this, new filechangedeventargs(e.fullpath));
        }
    }
}

public class filechangedeventargs : eventargs {
    public string filepath { get; }
    public filechangedeventargs(string path) => filepath = path;
}

using officeopenxml;
using system.data;

public class excelreader {
    public datatable readexcel(string filepath) {
        var datatable = new datatable();
        using (var package = new excelpackage(new fileinfo(filepath))) {
            var worksheet = package.workbook.worksheets[0];
            worksheet.cells["a1"].loadfromcollection(getdatafromsheet(worksheet));
            datatable = worksheet.cells["a1"].getdatatable();
        }
        return datatable;
    }

    private ienumerable<object[]> getdatafromsheet(excelworksheet sheet) {
        var data = new list<object[]>();
        for (int row = 2; row <= sheet.dimension.end.row; row++) { // 跳过标题行
            var rowdata = new object[sheet.dimension.end.column];
            for (int col = 1; col <= sheet.dimension.end.column; col++) {
                var cell = sheet.cells[row, col];
                rowdata[col-1] = cell.text;
            }
            data.add(rowdata);
        }
        return data;
    }
}

using system.data.sqlclient;

public class databaseimporter {
    private readonly string _connectionstring = "your sql connection string";
    private const int batchsize = 1000;

    public void bulkinsert(datatable datatable, string destinationtable) {
        using (var connection = new sqlconnection(_connectionstring))
        using (var bulkcopy = new sqlbulkcopy(connection)) {
            connection.open();
            bulkcopy.destinationtablename = destinationtable;
            bulkcopy.batchsize = batchsize;
            
            // 自动映射列
            foreach (datacolumn column in datatable.columns) {
                bulkcopy.columnmappings.add(column.columnname, column.columnname);
            }
            
            bulkcopy.writetoserver(datatable);
        }
    }
}

public class exceltosqlprocessor {
    private excelmonitor _monitor;
    private excelreader _reader;
    private databaseimporter _importer;

    public exceltosqlprocessor() {
        _monitor = new excelmonitor();
        _reader = new excelreader();
        _importer = new databaseimporter();
        
        _monitor.filechanged += async (s, e) => {
            try {
                await processfileasync(e.filepath);
            } catch (exception ex) {
                logerror($"处理失败: {ex.message}");
            }
        };
    }

    private async task processfileasync(string filepath) {
        log($"开始处理文件: {filepath}");
        
        // 读取excel数据
        var datatable = _reader.readexcel(filepath);
        
        // 数据清洗
        validatedata(datatable);
        
        // 执行批量插入
        _importer.bulkinsert(datatable, "targettable");
        
        log($"文件处理完成，耗时: {stopwatch.elapsedmilliseconds}ms");
    }

    private void validatedata(datatable table) {
        // 实现数据验证逻辑
        foreach (datarow row in table.rows) {
            if (row.isnull("id")) throw new invaliddataexception("id列不能为空");
        }
    }

    private static void log(string message) {
        console.writeline($"[{datetime.now:hh:mm:ss}] {message}");
    }
}

public class excelprocessorexceptionhandler {
    public void handle(exception ex) {
        if (ex is excelreaderexception) {
            log($"excel解析错误: {ex.innerexception?.message}");
        }
        else if (ex is sqlbulkcopyexception) {
            log($"数据库插入错误: {ex.innerexception?.message}");
        }
        else {
            log($"未知错误: {ex.stacktrace}");
        }
        
        // 发送错误通知
        sendalertemail($"excel导入失败: {ex.message}");
    }
}

public class appconfig {
    public static string excelpath => configurationmanager.appsettings["excelpath"];
    public static string dbconnectionstring => configurationmanager.connectionstrings["defaultdb"].connectionstring;
    public static int batchsize => int.parse(configurationmanager.appsettings["batchsize"]);
}

// app.config 配置示例
<appsettings>
    <add key="excelpath" value="c:\data\input.xlsx"/>
    <add key="dbconnectionstring" value="data source=.;initial catalog=testdb;integrated security=true;pooling=true;max pool size=50;"/>
    <add key="batchsize" value="5000"/>
</appsettings>

using (var transaction = connection.begintransaction()) {
    try {
        bulkcopy.destinationtablename = destinationtable;
        bulkcopy.sqlrowscopied += (s, e) => updateprogress(e.rowscopied);
        bulkcopy.writetoserver(datatable);
        transaction.commit();
    } catch {
        transaction.rollback();
        throw;
    }
}

private int _lastprocessedrow = 1;
var rows = worksheet.dimension.rows;
for (int row = _lastprocessedrow; row <= rows; row++) {
    // 处理数据
}

datatable.columns.add("price", typeof(decimal));
foreach (datarow row in datatable.rows) {
    row["price"] = decimal.parse(row["价格"].tostring());
}

public event progresschangedeventhandler progresschanged;
private void updateprogress(int currentrow) {
    progresschanged?.invoke(this, new progresschangedeventargs(currentrow, null));
}

<packagereference include="epplus" version="5.8.3" />
<packagereference include="system.data.sqlclient" version="4.8.3" />