如何在将Excel文件导入SQL数据库之前转换数据类型

public static void ImportToSql(string excelfilepath)
    {
        string myexceldataquery = "select LocalSKU,ItemName, QOH,Price,Discontinued,Barcode,Integer2,Integer3,SalePrice,SaleOn,Price2 from [sheet1$]";
        try
        {
            string sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + excelfilepath + "; Extended Properties='"Excel 12.0; HDR=Yes; IMEX=2'"";
           string ssqlconnectionstring = "Data Source=DELL''SQLSERVER1;Trusted_Connection=True;DATABASE=Test;CONNECTION RESET=FALSE";
            SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
            //series of commands to bulk copy data from the excel file into our sql table
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();
            SqlCommand sqlcmd = new SqlCommand(@"MERGE Inventory AS target
                          USING (select LocalSKU,ItemName, QOH,Price,Discontinued,Barcode,Integer2,Integer3,SalePrice,SaleOn,Price2 from @source)  as source
                          ON (source.LocalSKU = target.LocalSKU)
                         WHEN MATCHED THEN
                          UPDATE SET ItemName=source.ItemName,Price=source.Price,Discontinued=source.Discontinued,Barcode=source.Barcode,Integer2=source.Integer2,Integer3 = source.QOH,SalePrice=source.SalePrice,SaleOn=source.SaleOn,Price2=source.Price2;", sqlconn);

            SqlParameter param;
            param = sqlcmd.Parameters.AddWithValue("@source",dr);
            param.SqlDbType = SqlDbType.Structured;
            param.TypeName = "dbo.InventoryType";
            sqlconn.Open();
            sqlcmd.ExecuteNonQuery();
            sqlconn.Close();
            while (dr.Read())
            {
            }
            oledbconn.Close();
            Console.WriteLine(".xlsx file imported succssessfully into database.");
        }

最简单的方法是使用 SQL 语句中的CAST转换它们：

SqlCommand sqlcmd = new SqlCommand(
  @"MERGE Inventory AS target
    USING (select LocalSKU, ItemName, QOH = CAST(QOH AS int)
    , Price = CAST(Price AS decimal(10,2)), Discontinued = CAST(Discontinued AS bit)
    , Barcode, Integer2 = CAST(Integer2 AS int)
    , Integer3 = CAST(Integer3 AS int), SalePrice = CAST(SalePrice AS decimal(10,2))
    , SaleOn, Price2 = CAST(Price2 AS decimal(10,2)) from @source)  as source
    ON (source.LocalSKU = target.LocalSKU)
    WHEN MATCHED THEN
    UPDATE (. . . )

我正在猜测一些转换，但你明白了。您需要确保电子表格中的数据都与要转换为的数据类型匹配，因为一个错误将导致整个语句失败。更健壮的东西将需要更多的代码。

首先浏览 excel 文件并将数据放入数据网格中，然后逐行读取数据网格。我给你2个功能。一种是浏览Excel文件并将数据放入数据网格，另一种是读取数据网格并将记录放入数据库

Excel导出功能

private void export_btn_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application ExcelApp =
                      new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook ExcelBook;
            Microsoft.Office.Interop.Excel._Worksheet ExcelSheet;
            int i = 0;
            int j = 0;
            //create object of excel
            ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)ExcelApp.Workbooks.Add(1);
            ExcelSheet = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet;
            //export header
            for (i = 1; i <= this.dataGridView1.Columns.Count; i++)
            {
                ExcelSheet.Cells[1, i] = this.dataGridView1.Columns[i - 1].HeaderText;
            }
            //export data
            for (i = 1; i <= this.dataGridView1.RowCount; i++)
            {
                for (j = 1; j <= dataGridView1.Columns.Count; j++)
                {
                    ExcelSheet.Cells[i + 1, j] = dataGridView1.Rows[i - 1].Cells[j - 1].Value;
                }
            }
            ExcelApp.Visible = true;
            //set font Khmer OS System to data range
            Microsoft.Office.Interop.Excel.Range myRange = ExcelSheet.get_Range(
                                      ExcelSheet.Cells[1, 1],
                                      ExcelSheet.Cells[this.dataGridView1.RowCount + 1,
                                      this.dataGridView1.Columns.Count]);
            Microsoft.Office.Interop.Excel.Font x = myRange.Font;
            x.Name = "Arial";
            x.Size = 10;
            //set bold font to column header
            myRange = ExcelSheet.get_Range(ExcelSheet.Cells[1, 1],
                                     ExcelSheet.Cells[1, this.dataGridView1.Columns.Count]);
            x = myRange.Font;
            x.Bold = true;
            //autofit all columns
            myRange.EntireColumn.AutoFit();

            ExcelApp.ActiveWorkbook.SaveCopyAs("E:''reports.xlsx");
            ExcelApp.ActiveWorkbook.Saved = true;
            ExcelApp.Quit();
            MessageBox.Show("Excel file created,you can find the file E:''reports.xlsx");
            //
            ExcelSheet = null;
            ExcelBook = null;
            ExcelApp = null;            
        }

---

1. 读取数据网格

   public void readDataGrid()
   {
   for (int i = 0; i < dataGridView1.Rows.Count; i++)
   {
            try
   {
   //Here read one by one cell and convert it into your required datatype and store it in 
    String rowcell1 = dataGridView1.Rows[i].Cells[0].Value.ToString();
   }
   catch (Exception err)
   {
   }
   count++;
    }
   }
   

我这是在帮你。