把Excel表数据导入数据库

 导入格式

 

//导入Excel（导入资产信息）
private void button2_Click(object sender, EventArgs e)
{
OpenFileDialog openfilediaglog = new OpenFileDialog();
openfilediaglog.Filter = "xls文件|*.xls";
if (openfilediaglog.ShowDialog() == DialogResult.OK)
{
string str = Import(System.IO.Path.GetFullPath(openfilediaglog.FileName)).ToString();

//插入数据库

DataTable dt = Help.SqlHelper.ExecuteDataTable(Config.Connection.ConnectionStringOAYS,
CommandType.Text, str);
if (dt == null)
{
MessageBox.Show("数据导入失败");
}
else
{
MessageBox.Show("数据导入成功");
}
}
}

 

//导入函数
public StringBuilder Import(string filePath)
{
StringBuilder sqlSB = new StringBuilder();
try
{

//这里是Excel2003版本连接字符串，2007版本及以上请更改连接字符串
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection con = new OleDbConnection(strConn);
con.Open();
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = string.Format("select * from [Sheet1$]");//[sheetName$]要如此格式
OleDbDataReader odr = cmd.ExecuteReader();
while (odr.Read())
{
int isIntnet = 0;//是否可以上网
int isuseusb = 0;//是否禁用USB
if (odr[9].ToString() == "是")
{
isIntnet = 1;
}
if (odr[15].ToString() == "是")
{
isuseusb = 1;
}
string str = odr[14].ToString();
sqlSB.AppendLine(@"INSERT INTO InventoryAssetstest
( Barcode ,--条码1
No,--自定义编号2
CACID ,--固定资产编号3
UpdateDate ,--更新时间4
NowUser ,--使用用户5
NowDept ,--使用部门6
IP ,--IP地址7
ComputerName ,--计算机名8
EquipmentDesc ,--设备名称9
Brand ,--牌子型号10
Remark ,--备注11
ISIntnet,--12
CPU,--14
Memory,--15内存
ComputeDisk,--16硬盘
OperatingSystem,--17操作系统
ServiceNum,--18服务编号
IsUseUSB--19
)
VALUES ((SELECT TOP 1 CAST(CAST(rtrim(MAX(Barcode)) AS DECIMAL(18,0))+1 AS VARCHAR(100)) FROM InventoryAssetstest WHERE Barcode LIKE '1%') , -- Barcode - char(13)
'" + odr[0].ToString() +
"','" + odr[1].ToString() +
"',GETDATE(),'" +
odr[5].ToString() +
"','" + odr[4].ToString() +
"','" + odr[6].ToString() +
"' ,'" + odr[7].ToString() +
"' ,'" + odr[3].ToString() +
"' ,'" + odr[2].ToString() +
"' ,'" + odr[8].ToString() +
"' ," + isIntnet +
",'" + odr[10].ToString() +
"','" + odr[11].ToString() +
"','" + odr[12].ToString() +
"','" + odr[13].ToString() +
"','" + odr[14].ToString() +
"'," + isuseusb + ")");

}
odr.Close();
return sqlSB;
}
catch (Exception e)
{
return sqlSB;
}

}

转载于:https://www.cnblogs.com/miku-lai/p/5933863.html