将中包含页眉和子页眉的Excel文件导入数据库

Question

我有如下的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";
        }
    }

Answer 1

我已经按照以下方式更改了代码。及其工作

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";
    }
}