Import format
Import Excel (Import asset information)
private void Button2_Click (object sender, EventArgs e)
{
OpenFileDialog Openfilediaglog = new OpenFileDialog ();
Openfilediaglog. Filter = "xls file |*.xls";
if (Openfilediaglog. ShowDialog () = = DialogResult.OK)
{
String str = Import (System.IO.Path.GetFullPath (Openfilediaglog). FileName)). ToString ();
Insert Database
DataTable dt = Help.SqlHelper.ExecuteDataTable (Config.Connection.ConnectionStringOAYS,
CommandType.Text, str);
if (dt = = null)
{
MessageBox.Show ("Data import Failed");
}
Else
{
MessageBox.Show ("Data import success");
}
}
}
Import function
Public StringBuilder Import (string filePath)
{
StringBuilder sqlsb = new StringBuilder ();
Try
{
Here is the Excel2003 version connection string, version 2007 and above please change the connection string
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$] to do so
OleDbDataReader ODR = cmd. ExecuteReader ();
while (ODR. Read ())
{
int isintnet = 0;//is available online
int ISUSEUSB = 0;//whether to disable USB
if (odr[9]. ToString () = = "Yes")
{
Isintnet = 1;
}
if (odr[15]. ToString () = = "Yes")
{
ISUSEUSB = 1;
}
String str = odr[14]. ToString ();
Sqlsb.appendline (@ "INSERT into inventoryassetstest
(Barcode,--Barcode 1
No,--Custom No. 2
CACID,--Fixed Asset number 3
Updatedate,--Update Time 4
Nowuser,--Using User 5
Nowdept,--Use Department 6
IP,--IP Address 7
ComputerName,--Computer name 8
Equipmentdesc,--Device Name 9
Brand,--brand Model 10
Remark,--Notes 11
Isintnet,--12
CPU,--14
Memory,--15 RAM
Computedisk,--16 HDD
OperatingSystem,--17 Operating system
Servicenum,--18 Service number
Isuseusb--19
)
VALUES ((SELECT TOP 1 cast (CAST (RTrim (MAX (Barcode) as DECIMAL (18,0)) +1 as VARCHAR) from Inventoryassetstest WHERE Ba Rcode 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;
}
}
Import Excel table data into the database