Excel批量插入的SQL Server

ゝ一纸荒年。 2023-08-17 16:58 54阅读 0赞

首先新建一个WPF的项目，名为ExcelToServerDemo

![723924-20190902152211526-1389866506.png][]

到Nuget去安装NPOI的Nuget包，点击安装即可，会自动引用项目。

![723924-20190902152221432-581212204.png][]

新建一个Student的表格，有名字，年龄，性别，地址，以及民族，以及出生日期

![723924-20190902152253668-1461518057.png][]

查看Sudent 表格的数据为空

![723924-20190902152317765-1233537312.png][]

新建个Excel表格数据如下，这里的表头我用了英文，不影响。

![723924-20190902152349510-693201774.png][]

再来设计一下WPF的界面，我们就用一个按钮和一个按钮事件处理就好了，如下图

![723924-20190902152601334-408246200.png][]

后台代码如下

![723924-20190902152619630-1155910119.png][]

var datatble = new DataTable();
                var connectionsting = "Server=DESKTOP-GBT0AFP;Initial Catalog=Lexan;Integrated Security=SSPI;";
                var sqldataadapter = new SqlDataAdapter("select * from Student", connectionsting);
                var sqlbulkcopy = new SqlBulkCopy(connectionsting) { DestinationTableName = "Student" };
                sqldataadapter.FillSchema(datatble, SchemaType.Source);
                var xssfworkbook = new XSSFWorkbook(File.OpenRead(@"C:\Users\News\Desktop\Student.xlsx"));
                var sheetrow= xssfworkbook.GetSheetAt(0);
                for (int i = 1; i < sheetrow.LastRowNum; i++)
                {
                    var tablenewrow = datatble.NewRow();
                    var temprow = sheetrow.GetRow(i);
                    tablenewrow[0] = temprow.Cells[0].StringCellValue;
                    tablenewrow[1] = temprow.Cells[1].NumericCellValue;
                    tablenewrow[2] = temprow.Cells[2].StringCellValue;
                    tablenewrow[3] = temprow.Cells[3].StringCellValue;
                    tablenewrow[4] = temprow.Cells[4].StringCellValue;
                    tablenewrow[5] = temprow.Cells[5].DateCellValue;
                    datatble.Rows.Add(tablenewrow);
                }
                sqlbulkcopy.WriteToServer(datatble);
                MessageBox.Show("写入完成！");

然后运行一下

![723924-20190902152908358-1662025433.png][]

再来查看一下数据库

![723924-20190902152928255-1924296896.png][]

感谢观看。

转载于:https://www.cnblogs.com/R00R/p/11446911.html

[723924-20190902152211526-1389866506.png]: /images/20230809/16074d5206604478829de1a08cc3e7f2.png
[723924-20190902152221432-581212204.png]: /images/20230809/b1a461a7879e442bb108e48ea49dcef9.png
[723924-20190902152253668-1461518057.png]: /images/20230809/dc0379ec18224d118c1a7a24c2d10f98.png
[723924-20190902152317765-1233537312.png]: /images/20230809/4ba2b222fb7c4c4080ef25c4622a7f8d.png
[723924-20190902152349510-693201774.png]: /images/20230809/5f06571703ab4abebc1b211d0fbfff81.png
[723924-20190902152601334-408246200.png]: /images/20230809/1143800569a943779ca1f094c4babb5e.png
[723924-20190902152619630-1155910119.png]: /images/20230809/64272290b2d44413afe7d68e96046882.png
[723924-20190902152908358-1662025433.png]: /images/20230809/8026ef202b8c4668b194ef68c3a816ba.png
[723924-20190902152928255-1924296896.png]: /images/20230809/4c01cf762a46477f96aa24f611f7f0e7.png