Windows应用中NPOI实现多记录Excel(XLS)转SQL的循环取值问题

首先，我注意到你的现有代码存在两个核心问题：一是硬编码了行和列索引，只能处理单条记录；二是直接拼接SQL字符串，存在严重的SQL注入风险，而且遇到空值或特殊字符（比如单引号）时极易出错。下面给你一步步的解决方案：

1. 明确Excel结构逻辑 #

从你的代码推测，Excel的结构应该是这样的：

• 左侧行对应字段名（比如第1行是CaseNo，第2行是Customer，以此类推）
• 每一条CaseInfo记录对应右侧的一列，列之间用空列分隔（比如第1列是第一条记录，第3列是第二条，第5列是第三条...）

我们需要遍历所有包含有效数据的列，为每一列构建一条插入数据。

2. 重构代码：循环处理多条记录 + 参数化SQL #

下面是修改后的完整代码，我标注了关键改进点：

using NLog;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;

namespace Excel_to_SQL
{
    public partial class Excel_to_SQL : Form
    {
        public Excel_to_SQL()
        {
            InitializeComponent();
        }

        private readonly static NLog.Logger Logger = NLog.LogManager.GetCurrentClassLogger();

        private void Excel_to_SQL_Load(object sender, EventArgs e)
        {
            HSSFWorkbook workbook;
            using (FileStream file = new FileStream(@"c:\CaseInfo.xls", FileMode.Open, FileAccess.Read))
            {
                workbook = new HSSFWorkbook(file);
            }

            ISheet sheet = workbook.GetSheet("CaseInfo");
            if (sheet == null)
            {
                Logger.Error("Sheet 'CaseInfo' not found in the Excel file.");
                Application.Exit();
                return;
            }

            // 开始遍历所有有效记录列（初始从第1列开始，步长2适配空列分隔）
            int currentColumn = 1;
            while (true)
            {
                // 检查当前列是否有有效数据（取第一个字段的单元格判断）
                var firstCell = sheet.GetRow(1)?.GetCell(currentColumn);
                if (firstCell == null || string.IsNullOrWhiteSpace(GetCellValue(firstCell)))
                {
                    break; // 无有效数据，退出循环
                }

                // 构建参数化SQL语句（避免注入风险）
                string insertSql = @"INSERT INTO [test].[dbo].[CaseInfo] 
                                    (CaseNo,Customer,DisassembleType,ProjectNo,DispatchedVendor,
                                    AuthStoreNo,AuthStoreName,AuthStoreArea,AuthStoreContact,AuthStoreContactCell,
                                    BusinessTime,BusinessAddress,ContractAddress,PDQType,DualModuleMode,
                                    ECRConn,ConnType,LOGO,ElectronicInvoice,Broadband,
                                    AddInstructions,RequireNo,ContractNo,DTID,ProjectName,
                                    DispatchedDept,OldAuthStoreNo,Header) 
                                    VALUES 
                                    (@CaseNo,@Customer,@DisassembleType,@ProjectNo,@DispatchedVendor,
                                    @AuthStoreNo,@AuthStoreName,@AuthStoreArea,@AuthStoreContact,@AuthStoreContactCell,
                                    @BusinessTime,@BusinessAddress,@ContractAddress,@PDQType,@DualModuleMode,
                                    @ECRConn,@ConnType,@LOGO,@ElectronicInvoice,@Broadband,
                                    @AddInstructions,@RequireNo,@ContractNo,@DTID,@ProjectName,
                                    @DispatchedDept,@OldAuthStoreNo,@Header)";

                using (SqlConnection sqlConn = new SqlConnection(@"Data Source=testsvr;Initial Catalog=test;Trusted_Connection=true"))
                {
                    sqlConn.Open();
                    using (SqlTransaction transaction = sqlConn.BeginTransaction("CaseInfoTransaction"))
                    {
                        try
                        {
                            using (SqlCommand cmd = new SqlCommand(insertSql, sqlConn, transaction))
                            {
                                // 第一组参数：从当前列取值
                                cmd.Parameters.AddWithValue("@CaseNo", GetCellValue(sheet.GetRow(1).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@Customer", GetCellValue(sheet.GetRow(2).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@DisassembleType", GetCellValue(sheet.GetRow(3).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@ProjectNo", GetCellValue(sheet.GetRow(4).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@DispatchedVendor", GetCellValue(sheet.GetRow(5).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@AuthStoreNo", GetCellValue(sheet.GetRow(6).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@AuthStoreName", GetCellValue(sheet.GetRow(7).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@AuthStoreArea", GetCellValue(sheet.GetRow(8).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@AuthStoreContact", GetCellValue(sheet.GetRow(9).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@AuthStoreContactCell", GetCellValue(sheet.GetRow(10).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@BusinessTime", GetCellValue(sheet.GetRow(11).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@BusinessAddress", GetCellValue(sheet.GetRow(12).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@ContractAddress", GetCellValue(sheet.GetRow(13).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@PDQType", GetCellValue(sheet.GetRow(14).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@DualModuleMode", GetCellValue(sheet.GetRow(15).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@ECRConn", GetCellValue(sheet.GetRow(16).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@ConnType", GetCellValue(sheet.GetRow(17).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@LOGO", GetCellValue(sheet.GetRow(18).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@ElectronicInvoice", GetCellValue(sheet.GetRow(19).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@Broadband", GetCellValue(sheet.GetRow(20).GetCell(currentColumn)));
                                cmd.Parameters.AddWithValue("@AddInstructions", GetCellValue(sheet.GetRow(21).GetCell(currentColumn)));

                                // 第二组参数：从当前列+2的位置取值（适配你的原代码逻辑）
                                int secondColumn = currentColumn + 2;
                                cmd.Parameters.AddWithValue("@RequireNo", GetCellValue(sheet.GetRow(1).GetCell(secondColumn)));
                                cmd.Parameters.AddWithValue("@ContractNo", GetCellValue(sheet.GetRow(2).GetCell(secondColumn)));
                                cmd.Parameters.AddWithValue("@DTID", GetCellValue(sheet.GetRow(3).GetCell(secondColumn)));
                                cmd.Parameters.AddWithValue("@ProjectName", GetCellValue(sheet.GetRow(4).GetCell(secondColumn)));
                                cmd.Parameters.AddWithValue("@DispatchedDept", GetCellValue(sheet.GetRow(5).GetCell(secondColumn)));
                                cmd.Parameters.AddWithValue("@OldAuthStoreNo", GetCellValue(sheet.GetRow(6).GetCell(secondColumn)));
                                cmd.Parameters.AddWithValue("@Header", GetCellValue(sheet.GetRow(7).GetCell(secondColumn)));

                                // 执行插入
                                int rowsAffected = cmd.ExecuteNonQuery();
                                Logger.Debug($"Inserted {rowsAffected} record(s) for column {currentColumn}");
                            }

                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            Logger.Error(ex, $"Failed to insert record for column {currentColumn}");
                            throw; // 可根据需求选择是否抛出异常，或继续处理下一条
                        }
                    }
                }

                // 跳到下一个记录列（步长2适配空列分隔）
                currentColumn += 2;
            }

            MessageBox.Show("所有记录导入完成！");
            Application.Exit();
        }

        // 辅助方法：安全获取单元格值，处理空值和不同单元格类型
        private string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;

            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue.Trim();
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                        return cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                    else
                        return cell.NumericCellValue.ToString();
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Formula:
                    // 处理公式计算结果
                    using (var evaluator = new HSSFFormulaEvaluator(cell.Sheet.Workbook))
                    {
                        var cellValue = evaluator.Evaluate(cell);
                        switch (cellValue.CellType)
                        {
                            case CellType.String:
                                return cellValue.StringValue.Trim();
                            case CellType.Numeric:
                                if (DateUtil.IsCellDateFormatted(cell))
                                    return cellValue.NumberValue.ToString("yyyy-MM-dd HH:mm:ss");
                                else
                                    return cellValue.NumberValue.ToString();
                            case CellType.Boolean:
                                return cellValue.BooleanValue.ToString();
                            default:
                                return string.Empty;
                        }
                    }
                default:
                    return string.Empty;
            }
        }
    }
}

3. 关键改进点说明 #

• 循环处理多记录：通过遍历列索引（步长为2适配空列分隔），自动处理所有有效记录，直到无数据为止。
• 参数化SQL：彻底消除SQL注入风险，同时自动处理特殊字符，比字符串拼接更安全可靠。
• 通用单元格取值：GetCellValue方法兼容字符串、数字、日期、布尔值、公式等多种单元格类型，避免类型错误异常。
• 事务与错误处理：每条记录插入都在事务中，失败时回滚并记录日志，便于排查问题。
• 空值校验：遍历前检查列是否有有效数据，避免无效循环。

4. 适配Excel结构调整 #

如果你的Excel列分隔逻辑不是步长2，或者字段对应的行索引有变化，只需调整：

• currentColumn的初始值和步长
• 参数添加部分对应的行、列索引

内容的提问来源于stack exchange，提问作者Kliff