今天下午，突然许久未闪动过的QQ头像突然跳了出来，她说：我想请你帮个忙。面对美女的述求，我怎么能拒绝呢？当然，这可能也和大龄未婚有关系。其实她找我，就是让帮她写个简单的数据导入程序。

由于之前学过C#,而且现在.net core也不错，所以就尝试着用.net core来写这个小程序。 首先我们需要把VS升级到16.5+,然后按照传统方式创建一个WinForm程序 创建好后大概是这个样子 这里，我们先通过NeGet来安装Oracle驱动包 有了Oracle.ManagedDataAccess.Client后，我们可以不用安装Oracle客户端，现在，我们来尝试一下是否能够正常连接到Oracle数据

private void InitOracleConnection()
        {
            string dbConnectionString = "User Id=eisp_test;Password=Abcd123#;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.190.59)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=eisp)))";
            OracleConnection oracleConnection = new OracleConnection(dbConnectionString);
            oracleConnection.Open();
            MessageBox.Show("OK");
        }

运行起来后，会看到这样的效果 继续进入下一步，通过NeGet安装EPPlus 现在我们就新建一个ExeclHelper类

public class ExeclHelper
    {
        public static DataTable ReadExecl2DataTable(string filePath, int titleIndex, int dataStartIndex)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            //选定 指定页
            using (var package = new ExcelPackage(new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite)))
            {
                if (package.Workbook.Worksheets.Count >= 1)
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                    return Execl2DataTable(worksheet, titleIndex, dataStartIndex);
                }
                return null;
            }


        }

        public static DataTable Execl2DataTable(ExcelWorksheet excelWorksheet, int titleIndex, int dataStartIndex)
        {
            int rowsTotal = excelWorksheet.Dimension.End.Row;
            int cellsTotal = excelWorksheet.Dimension.End.Column;
            DataTable dataTable = new DataTable(excelWorksheet.Name);  //table名称
            DataRow dr = null;

            //处理表头
            for (int j = 1; j <= cellsTotal; j++)
            {
                dataTable.Columns.Add(GetValue(excelWorksheet.Cells[titleIndex, j].Value));
            }
            for (int i = dataStartIndex; i <= rowsTotal; i++)
            {
                dr = dataTable.Rows.Add();

                for (int j = 1; j <= cellsTotal; j++)
                {
                    dr[j - 1] = GetValue(excelWorksheet.Cells[i, j].Value);
                }
            }
            return dataTable;

        }

        public static string GetValue(object data)
        {
            if (null != data)
            {
                return data.ToString();
            }
            return "";
        }


    }

把Execl转换为DataTable，并拼接Sql语句(因为没有OracleBulkCopy)

  public string Getconfig(string name)
        {
            IConfiguration config = new ConfigurationBuilder().Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true }).Build();

            return config.GetSection(name).Value;

        }

        private OracleConnection GetOracleConnection()
        {
            string dbConnectionString = "User Id=eisp_test;Password=Abcd123#;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.190.59)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=eisp)))";
            return new OracleConnection(dbConnectionString);

        }

        private void btn_select_file_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFile = new OpenFileDialog();
            openFile.Filter = "Execl2003|*.xls|Execl2007|*.xlsx";
            if (openFile.ShowDialog() == DialogResult.OK)
            {
                fileName = openFile.FileName;
                lb_filename.Text = fileName;
                int titleIndex = 0;
                int.TryParse(txt_start.Text, out titleIndex);
                DataTable dt = ExeclHelper.ReadExecl2DataTable(fileName, titleIndex, titleIndex + 1);

                InsertToDatabase(dt);
            }
        }

        private void btn_import_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(fileName))
            {

                MessageBox.Show("请选择要导入的数据文件哟");
                return;
            }
        }

        private void InsertToDatabase(DataTable dt)
        {
            if (dt == null)
            {
                return;
            }
            lb_table.Text = dt.TableName;
            string sql = "", execSql = string.Empty;
            DataColumnCollection dcc = dt.Columns;
            List<string> colNames = new List<string>(dcc.Count);
            for (int colIndex = 0; colIndex <= dcc.Count - 1; colIndex++)
            {
                colNames.Add(dcc[colIndex].ColumnName);
            }

            sql += $"insert into {dt.TableName} ({string.Join(",", colNames)})";

            List<string> selects = new List<string>();
            OracleConnection oracleConnection = GetOracleConnection();

            oracleConnection.Open();
            OracleCommand oracleCommand = null;
            oracleCommand = oracleConnection.CreateCommand();
            if (rb_cover.Checked)
            {

                oracleCommand.CommandText = $"truncate table {dt.TableName}";
                oracleCommand.ExecuteNonQuery();
            }
            for (int i = 0; i < dt.Rows.Count - 1; i++)
            {
                selects.Add($"select '{string.Join("','", dt.Rows[i].ItemArray)}'  from dual");
                if (selects.Count == 500)
                {
                    execSql = $"{sql}  {string.Join(" union all ", selects)}";

                    oracleCommand.CommandText = execSql;
                    oracleCommand.ExecuteNonQuery();
                    selects.Clear();
                }
            }
            if (selects.Count >= 1)
            {
                execSql = $"{sql}  {string.Join(" union all ", selects)}";

                oracleCommand.CommandText = execSql;
                oracleCommand.ExecuteNonQuery();
                selects.Clear();
            }

            MessageBox.Show("导入完成");


        }

来看看最后的初步效果 大概就是这几段代码，但是还需要处理很多细节问题，最后，我发了美女一个最终版，却只换来了一句非常感谢。

我只是记录我的学习过程，由于书读的少，可能很多地方表述或者是理解得不对，请轻喷并指正。