C# winform框架根据选择的Excel文件创建SQL server的数据表,并且每个字段都是nvarchart(255)的类型和长度
时间: 2024-03-15 18:47:09 浏览: 18
好的,我了解你的问题,以下是基于C# Winform框架实现根据选择的Excel文件创建SQL Server的数据表,每个字段都是nvarchar(255)类型和长度的示例代码:
```csharp
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace ExcelToSqlServer
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
// 选择Excel文件
OpenFileDialog openFileDialog1 = new OpenFileDialog
{
Filter = "Excel文件|*.xls;*.xlsx",
Title = "选择Excel文件"
};
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
string filePath = openFileDialog1.FileName;
textBox1.Text = filePath;
// 读取Excel文件
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(filePath);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
Excel.Range range = worksheet.UsedRange;
// 创建SQL Server的数据表
string tableName = worksheet.Name.Replace("$", "");
string connectionString = "Data Source=(local);Initial Catalog=YourDatabase;Integrated Security=True";
string createTableQuery = $"CREATE TABLE {tableName} (";
for (int i = 1; i <= range.Columns.Count; i++)
{
string columnName = ((Excel.Range)range.Cells[1, i]).Value.ToString();
createTableQuery += $"{columnName} nvarchar(255),";
}
createTableQuery = createTableQuery.TrimEnd(',') + ")";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(createTableQuery, connection);
command.ExecuteNonQuery();
}
// 关闭Excel应用程序
workbook.Close(false);
app.Quit();
MessageBox.Show($"已成功创建表格 {tableName}");
}
}
}
}
```
注意,上述代码中的`YourDatabase`应替换为你所使用的数据库名称。此外,由于该程序使用Interop.Excel库,因此需要在Visual Studio中的“工具”>“NuGet包管理器”>“程序包管理器控制台”中运行以下命令来安装Interop.Excel:`Install-Package Microsoft.Office.Interop.Excel -Version 15.0.4795.1000`。
希望这可以帮助到你!