将中包含页眉和子页眉的Excel文件导入数据库
本文关键字：文件 Excel 导入 数据库 包含页 | 更新日期: 2023-09-27 18:01:11
我有如下的excel表
Condom Lubricant
dic_code condom_receive lubricant_receive
3 100 50
1 150 0
2 270 78
我想导入到数据库像下面的方式
dic_code condom_receive lubricant_receive
3 100 0
3 0 50
1 150 0
1 0 0
2 270 0
2 0 78
我已经用C#和ASP.net编写了代码，但它不起作用。请帮帮我。
protected void importBtn_Click(object sender, EventArgs e)
{
var folder = Server.MapPath("~/temp/");
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
if (FileUpload1.HasFile)
{
string path = string.Concat((Server.MapPath("~/temp/" + FileUpload1.FileName)));
FileUpload1.PostedFile.SaveAs(path);
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
OleDbcon.Open();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
objAdapter1.Fill(ds);
dt = ds.Tables[0];
OleDbcon.Close();
int number_of_columns = dt.Columns.Count;
int number_of_rows = ds.Tables[0].Rows.Count;
string[,] sheetEntries = new string[number_of_rows, number_of_columns];
string[] columnNames = new string[number_of_columns];
for (int j = 0; j < number_of_columns; j++)
{
columnNames[j] = dt.Columns[j].ToString();
}
for (int i = 0; i < number_of_rows; i++)
{
for (int j = 0; j < number_of_columns; j++)
sheetEntries[i, j] = dt.Rows[i].ItemArray.GetValue(j).ToString();
}
string strSQL1 = null;
for (int i1 = 0; i1 < number_of_rows; i1++)
{
if (sheetEntries[0, 1] == "Condom")
{
strSQL1 = "INSERT INTO [import_test]([dic_code],[condom_receive],[lubricant_receive]) VALUES ("
+ sheetEntries[i1, 0] + ",'" + sheetEntries[i1, 1] + "'," + 0 + ");";
SqlCommand cmd1 = new SqlCommand(strSQL1);
dc.Open();
cmd1.Connection = dc.GetConnection();
cmd1.ExecuteNonQuery();
Array.ForEach(Directory.GetFiles((Server.MapPath("~/temp/"))), File.Delete);
Label1.ForeColor = Color.Green;
Label1.Text = "Successfully inserted";
dc.Close();
}
if (sheetEntries[0, 2] == "Lubricant")
{
strSQL1 = "INSERT INTO [import_test]([dic_code],[condom_receive],[lubricant_receive]) VALUES ("
+ sheetEntries[i1, 0] + ",'" + 0 + "'," + sheetEntries[i1, 1] + ");";
SqlCommand cmd1 = new SqlCommand(strSQL1);
dc.Open();
cmd1.Connection = dc.GetConnection();
cmd1.ExecuteNonQuery();
Array.ForEach(Directory.GetFiles((Server.MapPath("~/temp/"))), File.Delete);
Label1.ForeColor = Color.Green;
Label1.Text = "Successfully inserted";
dc.Close();
}
}
}
else
{
Label1.ForeColor = Color.Red;
Label1.Text = "Please select the File";
}
}
我已经按照以下方式更改了代码。及其工作
protected void importBtn_Click(object sender, EventArgs e)
{
var folder = Server.MapPath("~/temp/");
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
if (FileUpload1.HasFile)
{
string path = string.Concat((Server.MapPath("~/temp/" + FileUpload1.FileName)));
FileUpload1.PostedFile.SaveAs(path);
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);
//OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$a2:j]", OleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
OleDbcon.Open();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
objAdapter1.Fill(ds);
dt = ds.Tables[0];
OleDbcon.Close();
int number_of_columns = dt.Columns.Count;
string[] columnNames = new string[number_of_columns];
int k;
for (k = 0; k < number_of_columns; k++)
{
columnNames[k] = dt.Columns[k].ToString();
}
if (columnNames[0] == "v"|| columnNames[1] == "Condom" || columnNames[2] == "Lubricant")
{
OleDbCommand cl_cmd = new OleDbCommand("SELECT * FROM [Sheet1$a2:j]", OleDbcon);
OleDbDataAdapter objAdapter2 = new OleDbDataAdapter(cl_cmd);
OleDbcon.Open();
DataSet ds1 = new DataSet();
DataTable dt1 = new DataTable();
objAdapter2.Fill(ds1);
dt1 = ds1.Tables[0];
OleDbcon.Close();
int number_of_columns1 = dt1.Columns.Count;
string[] columnNames1 = new string[number_of_columns1];
int number_of_rows = ds1.Tables[0].Rows.Count;
string[,] sheetEntries = new string[number_of_rows, number_of_columns];
for (int j = 0; j < number_of_columns1; j++)
{
columnNames1[j] = dt1.Columns[j].ToString();
}
for (int i = 0; i < number_of_rows; i++)
{
for (int j = 0; j < number_of_columns1; j++)
sheetEntries[i, j] = dt1.Rows[i].ItemArray.GetValue(j).ToString();
}
string strSQL1 = null;
for (int i1 = 0; i1 < number_of_rows; i1++)
{
if (columnNames[1] == "Condom")
{
strSQL1 = "INSERT INTO [import_test]([dic_code],[condom_receive],[lubricant_receive]) VALUES ("
+ sheetEntries[i1, 0] + ",'" + sheetEntries[i1, 1] + "'," + 0 + ");";
SqlCommand cmd1 = new SqlCommand(strSQL1);
dc.Open();
cmd1.Connection = dc.GetConnection();
cmd1.ExecuteNonQuery();
Array.ForEach(Directory.GetFiles((Server.MapPath("~/temp/"))), File.Delete);
Label1.ForeColor = Color.Green;
Label1.Text = "Successfully inserted";
dc.Close();
}
if (columnNames[2] == "Lubricant")
{
strSQL1 = "INSERT INTO [import_test]([dic_code],[condom_receive],[lubricant_receive]) VALUES ("
+ sheetEntries[i1, 0] + ",'" + 0 + "'," + sheetEntries[i1, 1] + ");";
SqlCommand cmd1 = new SqlCommand(strSQL1);
dc.Open();
cmd1.Connection = dc.GetConnection();
cmd1.ExecuteNonQuery();
Array.ForEach(Directory.GetFiles((Server.MapPath("~/temp/"))), File.Delete);
Label1.ForeColor = Color.Green;
Label1.Text = "Successfully inserted";
dc.Close();
}
}
}
}
else
{
Label1.ForeColor = Color.Red;
Label1.Text = "Please select the File";
}
}