C#中，EXCEL与表列顺序完全一致情况的导入处理（BeginBinaryImport）

使用：BeginBinaryImport能快速的导入临时表

      //读取datagridview的值 
        private string GetCellValue(DataGridViewRow row, string columnName, string defaultValue = "0")
        {
            var value = row.Cells[columnName].Value;
            return (value == null || string.IsNullOrEmpty(value.ToString().Trim())) ?
                   defaultValue : value.ToString().Trim();
        }

 

            string sql, clientName, goodsId, departmentid, departmentName, account_id, account_name, xynr, qty, amount, sfzx, year;
            string type = "";
            string m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11, m12;
            string tmpTabelName = "xls" + DateTime.Now.ToFileTimeUtc().ToString();
            lbRate.Text = "正在导入，请稍等";
            try
            {
                sql = "create unlogged table " + tmpTabelName + "(";
                sql += @" 
                    clientname   varchar(120) not null  ,
                    departmentname   varchar(50)  not null default  ''  )";
                DBHelperPg.ExecuteNonQuery(sql);
                DataGridViewRow drvRow;
                // 使用COPY命令（PostgreSQL高效批量插入）
                NpgsqlConnection conn = new NpgsqlConnection(DB.DBHelperPg.ConnectionString);
                conn.Open();
                using (var writer = conn.BeginBinaryImport($"COPY {tmpTabelName} FROM STDIN (FORMAT BINARY)"))
                {
                    for (int i = 0; i < dataGridView1.RowCount; i++)
                    {
                        drvRow = dataGridView1.Rows[i];
                        clientName = GetCellValue(drvRow, "客户名称");
                        departmentName = GetCellValue(drvRow, "部门名称");


                        writer.StartRow();
                        writer.Write(clientName);
                        writer.Write(departmentName);

                        // ... 其他字段
                    }
                    writer.Complete();
                }
                conn.Close();