using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
using System.Collections;
using framework.Common; namespace winform
{
public partial class excelTOsql : Form
{
public excelTOsql()
{
InitializeComponent();
}
OleDbConnection oledbcConnection;
string connString = "server=localhost;uid=sa;pwd=123456;database=dbtest";//这里是自己的数据库信息，根据自己的情况修改
string strFileType = "";
/*打开excel文件并选择表单*/
private void button1_Click(object sender, EventArgs e)
{
try
{
OpenFileDialog openDG = new OpenFileDialog();
openDG.Title = "打开Excel表格";
openDG.Filter = "Excel表格(*.xlsx)|*.xlsx|CSV格式(*.csv)|*.csv|所有文件(*.*)|*.*";
openDG.ShowDialog();
string filename;//文件路径
filename = openDG.FileName;
filenamebox.Text = filename;//显示文件路径
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=False;IMEX=1'";//此处为对excel的读取设置，不同的excel版本有不同的设定。 strFileType = System.IO.Path.GetExtension(filename);
if (strFileType == ".xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
}
else
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filename + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
} //@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0";
/*读取excel数据到内存中*/
oledbcConnection = new OleDbConnection(strConn);
oledbcConnection.Open();
DataTable table = new DataTable();
table = oledbcConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
/*获取此文件中的所有表单*/
excelBookComboBoxEx.Items.Clear();
foreach (DataRow dr in table.Rows)
{
excelBookComboBoxEx.Items.Add((String)dr["TABLE_NAME"]);
}
excelBookComboBoxEx.Text = excelBookComboBoxEx.Items[].ToString(); DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection(connString); string strConn1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filenamebox.Text + ";Extended Properties='Excel 12.0;HDR=False;IMEX=1'"; if (strFileType == ".xls")
{
strConn1 = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filenamebox.Text + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
}
else
{
strConn1 = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filenamebox.Text + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
} // "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=False;IMEX=1'";
//"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection comm = new OleDbConnection(strConn1);
comm.Open(); string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}]", excelBookComboBoxEx.Text);
myCommand = new OleDbDataAdapter(strExcel, strConn1);
myCommand.Fill(ds, excelBookComboBoxEx.Text);
//filenamebox.Text = ds.Tables[0].Rows.Count.ToString();/*显示导入数据的总条数*/
comm.Close();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
} private void Nbutton1_Click(object sender, EventArgs e)
{
try
{
if (string.IsNullOrEmpty(txt_constr.Text.Trim()))
{
connString = txt_constr.Text.Trim();
}
string tbname = txt_tablename.Text.Trim();
if (string.IsNullOrEmpty(tbname))
{
MessageBox.Show("表名称必须填写");
}
//初始化一个OpenFileDialog类
OpenFileDialog fileDialog = new OpenFileDialog();
//判断用户是否正确的选择了文件
if (fileDialog.ShowDialog() == DialogResult.OK)
{
//获取用户选择文件的后缀名
string extension = Path.GetExtension(fileDialog.FileName).ToLower();
//声明允许的后缀名
string[] str = new string[] { ".xls", ".xlsx", ".csv" };
if (!((IList)str).Contains(extension))
{
MessageBox.Show("仅能导入xls,xlsx,csv文件！");
//lbl_msg.Text = "提示信息:仅能导入xls,xlsx,csv文件！";
}
else
{
DataTable dt = new DataTable();
switch (extension)
{
case ".xlsx":
dt = ExcelHelper.ExcelToTableForXLSX(fileDialog.FileName);
break;
case ".xls":
dt = ExcelHelper.GetExcelDataAsTableNPOI(fileDialog.FileName);
break;
case ".csv":
dt = CSVUtil.getCsvDataByTitle(fileDialog.FileName, "业务单号", null);
break;
default:
break;
}
if (dt.Rows.Count == )
{
MessageBox.Show("未读取到数据");
return;
}
StringBuilder sb = new StringBuilder(""); //如果目标表不存在则创建
string strSql = ""; if (ck_recovry.Checked)
{
strSql = string.Format("if object_id('{0}') is not null drop table {0} ;", tbname);
}
strSql += string.Format("create table {0}(", tbname);
if (ck_setautoid.Checked) //设置主键， 默认 约束
{
strSql += "[id] [int] IDENTITY(1,1) NOT NULL,";
}
foreach (System.Data.DataColumn c in dt.Columns)
{
strSql += string.Format("[{0}] varchar(500),", c.ColumnName);
} if (ck_setautoid.Checked) //设置主键， 默认 约束
{
// strSql += "[id] [int] IDENTITY(1,1) NOT NULL,";
strSql += string.Format(@" CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ", tbname);
} strSql = strSql.Trim(',') + ")"; if (ck_2rowtoDesc.Checked && dt.Rows.Count > )
{
for (int i = ; i < dt.Rows[].ItemArray.Length; i++)
{
sb.AppendLine(string.Format(@"EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{0}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{1}', @level2type=N'COLUMN',@level2name=N'{2}';", dt.Rows[].ItemArray[i].ToString(), tbname, dt.Columns[i].ColumnName));
sb.AppendLine(""); }
} strSql += sb.ToString(); using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connString))
{
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandText = strSql;
command.ExecuteNonQuery();
sqlconn.Close();
}
MessageBox.Show(string.Format("表({0})创建成功！", tbname)); }
} }
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
} public void TransferData(string excelFile, string sheetName, string connectionString)
{
DataSet ds = new DataSet();
try
{
//获取全部数据
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=False;IMEX=1'";// "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=False;IMEX=1'";
//"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;"; if (strFileType == ".xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
}
else
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
} OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}]", sheetName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
//如果目标表不存在则创建
string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
if (ck_setautoid.Checked)
{
strSql += "[id] [int] IDENTITY(1,1) NOT NULL,";
}
foreach (System.Data.DataColumn c in ds.Tables[].Columns)
{
strSql += string.Format("[{0}] varchar(500),", c.ColumnName);
}
strSql = strSql.Trim(',') + ")";
using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
{
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandText = strSql;
command.ExecuteNonQuery();
sqlconn.Close();
}
//用bcp导入数据
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
bcp.BatchSize = ;//每次传输的行数
bcp.NotifyAfter = ;//进度提示的行数
bcp.DestinationTableName = sheetName;//目标表
bcp.WriteToServer(ds.Tables[]);
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
} } private void button2_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txt_constr.Text.Trim()))
{
connString = txt_constr.Text.Trim();
}
string FILE_NAME = filenamebox.Text;
string ST = excelBookComboBoxEx.Text;
TransferData(FILE_NAME, ST, connString);
MessageBox.Show("导入成功!");
}
}
}
界面