KalaiPondy Ответов: 2

как читать определенные столбцы в файле excel и хранить их в таблице базы данных sql с помощью приложения c# windows

Привет
В своем проекте я импортирую данные из Excel в Sql server 2008 с помощью C# Winforms 2010.
Я могу прочитать файл Excel, сопоставив столбцы в первой строке заголовка столбца, и сохранить его в таблице базы данных sql . Но проблема в том, что предположим, что в файле Excel первая строка была изменена на вторую строку или любую другую строку, тогда код C# не может сопоставить строки и из-за ошибки. Итак, как читать определенные столбцы в файле excel и хранить их в таблице sql с помощью приложения c# windows ?

Вот Мой Код .
Может Ли Кто-Нибудь Помочь Мне С Правильным Ответом

private void BtnSubmit_Click(object sender, EventArgs e)
{
    if (IcCutomerName.Text != "" && CustSalesDate.Text != "")
    {
        ImportExcel();
        PopulateListView2();
        AutoCompleteSalesNo();
        AllClear();
    }
    else
    {
        if (MessageBox.Show("Need Customer Name To Submit", "Message", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.OK)
        {
            IcCutomerName.Focus();
        }
        else
        {
            BtnSubmit.Focus();
        }
    }
}


public void ImportExcel()
{
    try
    {
        try { Connection(); }
        catch { return; }
        cmd = new SqlCommand("Delete from ExcelCustomerSales", cs);
        cmd.ExecuteNonQuery();
        cs.Close();

        string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + TbFilePath.Text + "; Extended Properties=\"Excel 12.0;HDR=Yes\"";
        using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
        {
            OleDbCommand command = new OleDbCommand
            ("Select * FROM [Sheet1$]", connection);
            connection.Open();
            using (DbDataReader dr = command.ExecuteReader())
            {
                try { Connection(); }
                catch { return; }
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cs))
                {
                    bulkCopy.DestinationTableName = "ExcelCustomerSales";
                    bulkCopy.ColumnMappings.Add("Item/Model Code", "ModelCode");
                    bulkCopy.ColumnMappings.Add("Item/Model", "ModelName");
                    bulkCopy.ColumnMappings.Add("Qty", "Quantity");
                    bulkCopy.ColumnMappings.Add("Sales Value", "SalesValue");
                    bulkCopy.ColumnMappings.Add("Discount Amt", "DiscountAmt");
                    bulkCopy.ColumnMappings.Add("Amount", "Amount");
                    bulkCopy.ColumnMappings.Add("Net Amount", "NetAmount");
                    bulkCopy.ColumnMappings.Add("SR Qty", "SRQty");
                    bulkCopy.ColumnMappings.Add("SR Value", "SRValue");
                    bulkCopy.ColumnMappings.Add("SR Discount Amt", "SRDiscountAmt");
                    bulkCopy.ColumnMappings.Add("SR Tax Amt", "SRTaxAmt");
                    bulkCopy.ColumnMappings.Add("SR Net Amt", "SRNetAmt");
                    bulkCopy.ColumnMappings.Add("Total Qty", "TotalQty");
                    bulkCopy.ColumnMappings.Add("Total Value", "TotalValue");
                    bulkCopy.ColumnMappings.Add("Total Discount", "TotalDiscount");
                    bulkCopy.ColumnMappings.Add("Total Tax", "TotalTax");
                    bulkCopy.ColumnMappings.Add("Total Net Value", "TotalNetValue");
                    bulkCopy.WriteToServer(dr);
                }
            }
            connection.Close();
            cs.Close();
            EmptyData();
            ErrorAlert();
            MessageBox.Show("Data Exported To Sql Server Successfully");
        }
    }
    catch
    {
        MessageBox.Show("Data Exported To Sql Server has been Failed", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}


private void EmptyData()
{
    try { Connection(); }
    catch { return; }
    cmd = new SqlCommand("Delete from ExcelCustomerSales Where ModelCode IS NULL AND  ModelName IS NULL AND  Quantity  IS NULL AND  SalesValue  IS NULL AND  DiscountAmt IS NULL AND  Amount  IS NULL AND  NetAmount IS NULL AND  SRQty IS NULL AND  SRValue IS NULL AND  SRDiscountAmt IS NULL AND  SRTaxAmt  IS NULL AND  SRNetAmt  IS NULL AND  TotalQty  IS NULL AND  TotalValue  IS NULL AND  TotalDiscount IS NULL AND  TotalTax  IS NULL AND  TotalNetValue IS NULL", cs);
    cmd.ExecuteNonQuery();
    cs.Close();
}

public void ErrorAlert()
{
    try { Connection(); }
    catch { return; }
    int Num1 = 0, Num2 = 0;
    cmd = new SqlCommand("Select MAX(ID) from CustomerSalesNo", cs);
    dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        Num1 = int.Parse(dr[0].ToString());
    }
    dr.Close();
    Num2 = (Num1 + 1);
    cmd = new SqlCommand("Insert Into CustomerSalesNo(OsDcNo) Values(GetDate())", cs);
    cmd.ExecuteNonQuery();

    getDate = SqlDateTime.Null;
    DateTime? ads = null;
    try
    {
        ads = Convert.ToDateTime(CustSalesDate.EditValue);
    }
    catch
    {
    }

    string CustomerCode ="", CategoryName="", MMCode = "", ModelCode="",  ModelName = "", Quantity = "", ColorName="",  ModelSize="", SalesValue = "", DiscountAmt = "", Amount = "", NetAmount = "", SRQty = "", SRValue = "", SRDiscountAmt = "", SRTaxAmt = "", SRNetAmt = "", TotalQty = "", TotalValue = "", TotalDiscount = "", TotalTax = "", TotalNetValue = "", CMID = "", SMID = "", CUSTID = "", MMID = "", Remarks = "Customer Sales Report Saved";
    string CustomerName = IcCutomerName.Text;
    string SalesNo = Num2.ToString() + "-" + CustSalesDate.EditValue;

    SqlDataAdapter da = new SqlDataAdapter("Select ModelCode, ModelName, Quantity, SalesValue, DiscountAmt, Amount, NetAmount, SRQty, SRValue, SRDiscountAmt, SRTaxAmt, SRNetAmt, TotalQty, TotalValue, TotalDiscount, TotalTax, TotalNetValue from ExcelCustomerSales", cs);
    DataTable dt = new DataTable();
    da.Fill(dt);
    foreach (DataRow Row in dt.Rows)
    {
        ModelCode = Row["ModelCode"].ToString();
        ModelName = Row["ModelName"].ToString();
        Quantity = Row["Quantity"].ToString();
        SalesValue = Row["SalesValue"].ToString();
        DiscountAmt = Row["DiscountAmt"].ToString();
        Amount = Row["Amount"].ToString();
        NetAmount = Row["NetAmount"].ToString();
        SRQty = Row["SRQty"].ToString();
        SRValue = Row["SRValue"].ToString();
        SRDiscountAmt = Row["SRDiscountAmt"].ToString();
        SRTaxAmt = Row["SRTaxAmt"].ToString();
        SRNetAmt = Row["SRNetAmt"].ToString();
        TotalQty = Row["TotalQty"].ToString();
        TotalValue = Row["TotalValue"].ToString();
        TotalDiscount = Row["TotalDiscount"].ToString();
        TotalTax = Row["TotalTax"].ToString();
        TotalNetValue = Row["TotalNetValue"].ToString();

        SqlDataAdapter da1 = new SqlDataAdapter("Select ModelCode, CategoryName, ModelSize, ID, MSID from ModelMaster where ModelCode='" + ModelCode + "'", cs);
        DataTable dt1 = new DataTable();
        da1.Fill(dt1);
        foreach (DataRow Row1 in dt1.Rows)
        {
            MMCode = Row1["ModelCode"].ToString();
            CategoryName = Row1["CategoryName"].ToString();
            ModelSize = Row1["ModelSize"].ToString();
            MMID = Row1["ID"].ToString();
            SMID = Row1["MSID"].ToString();
        }

        SqlDataAdapter da2 = new SqlDataAdapter("Select CustomerCode, ID from CustomerMaster where CustomerName='" + IcCutomerName.Text + "'", cs);
        DataTable dt2 = new DataTable();
        da2.Fill(dt2);
        foreach (DataRow Row2 in dt2.Rows)
        {
            CustomerCode = Row2["CustomerCode"].ToString();
            CUSTID = Row2["ID"].ToString();
        }

        if (ModelCode.ToUpper() == MMCode.ToUpper())
        {
            try
            {
                cmd = new SqlCommand("Insert Into CustomerSales(CustomerCode, CustomerName, SalesNo, SalesDate, CategoryName, ModelCode, ModelName, ColorName, ModelSize, Quantity, SalesValue, DiscountAmt, Amount, NetAmount, SRQty, SRValue, SRDiscountAmt, SRTaxAmt, SRNetAmt, TotalQty, TotalValue, TotalDiscount, TotalTax, TotalNetValue, CMID, SMID, CUSTID, MMID, Remarks) Values (@CustomerCode, @CustomerName, @SalesNo, @SalesDate, @CategoryName, @ModelCode, @ModelName, @ColorName, @ModelSize, @Quantity, @SalesValue, @DiscountAmt, @Amount, @NetAmount, @SRQty, @SRValue, @SRDiscountAmt, @SRTaxAmt, @SRNetAmt, @TotalQty, @TotalValue, @TotalDiscount, @TotalTax, @TotalNetValue, @CMID, @SMID, @CUSTID, @MMID, @Remarks)", cs);
                cmd.Parameters.Add(new SqlParameter("@CustomerCode", (object)CustomerCode));
                cmd.Parameters.Add(new SqlParameter("@CustomerName", (object)CustomerName));
                cmd.Parameters.Add(new SqlParameter("@SalesNo", (object)SalesNo));
                cmd.Parameters.Add(new SqlParameter("@SalesDate", (object)ads));
                cmd.Parameters.Add(new SqlParameter("@CategoryName", (object)CategoryName));
                cmd.Parameters.Add(new SqlParameter("@ModelCode", (object)ModelCode));
                cmd.Parameters.Add(new SqlParameter("@ModelName", (object)ModelName));
                cmd.Parameters.Add(new SqlParameter("@ColorName", (object)ColorName));
                cmd.Parameters.Add(new SqlParameter("@ModelSize", (object)ModelSize));
                cmd.Parameters.Add(new SqlParameter("@Quantity", (object)Quantity));
                cmd.Parameters.Add(new SqlParameter("@SalesValue", (object)SalesValue));
                cmd.Parameters.Add(new SqlParameter("@DiscountAmt", (object)DiscountAmt));
                cmd.Parameters.Add(new SqlParameter("@Amount", (object)Amount));
                cmd.Parameters.Add(new SqlParameter("@NetAmount", (object)NetAmount));
                cmd.Parameters.Add(new SqlParameter("@SRQty", (object)SRQty));
                cmd.Parameters.Add(new SqlParameter("@SRValue", (object)SRValue));
                cmd.Parameters.Add(new SqlParameter("@SRDiscountAmt", (object)SRDiscountAmt));
                cmd.Parameters.Add(new SqlParameter("@SRTaxAmt", (object)SRTaxAmt));
                cmd.Parameters.Add(new SqlParameter("@SRNetAmt", (object)SRNetAmt));
                cmd.Parameters.Add(new SqlParameter("@TotalQty", (object)TotalQty));
                cmd.Parameters.Add(new SqlParameter("@TotalValue", (object)TotalValue));
                cmd.Parameters.Add(new SqlParameter("@TotalDiscount", (object)TotalDiscount));
                cmd.Parameters.Add(new SqlParameter("@TotalTax", (object)TotalTax));
                cmd.Parameters.Add(new SqlParameter("@TotalNetValue", (object)TotalNetValue));
                cmd.Parameters.Add(new SqlParameter("@CMID", (object)CMID));
                cmd.Parameters.Add(new SqlParameter("@SMID", (object)SMID));
                cmd.Parameters.Add(new SqlParameter("@CUSTID", (object)CUSTID));
                cmd.Parameters.Add(new SqlParameter("@MMID", (object)MMID));
                cmd.Parameters.Add(new SqlParameter("@Remarks", (object)Remarks));
                cmd.ExecuteNonQuery();
            }
            catch { }

        }
        else
        {
            try
            {
                cmd = new SqlCommand("Insert Into ErrorCustomerSales(CustomerCode, CustomerName, SalesNo, SalesDate, CategoryName, ModelCode, ModelName, ColorName, ModelSize, Quantity, SalesValue, DiscountAmt, Amount, NetAmount, SRQty, SRValue, SRDiscountAmt, SRTaxAmt, SRNetAmt, TotalQty, TotalValue, TotalDiscount, TotalTax, TotalNetValue, CMID, SMID, CUSTID, MMID, Remarks) Values (@CustomerCode, @CustomerName, @SalesNo, @SalesDate, @CategoryName, @ModelCode, @ModelName, @ColorName, @ModelSize, @Quantity, @SalesValue, @DiscountAmt, @Amount, @NetAmount, @SRQty, @SRValue, @SRDiscountAmt, @SRTaxAmt, @SRNetAmt, @TotalQty, @TotalValue, @TotalDiscount, @TotalTax, @TotalNetValue, @CMID, @SMID, @CUSTID, @MMID, @Remarks)", cs);
                cmd.Parameters.Add(new SqlParameter("@CustomerCode", (object)CustomerCode));
                cmd.Parameters.Add(new SqlParameter("@CustomerName", (object)CustomerName));
                cmd.Parameters.Add(new SqlParameter("@SalesNo", (object)SalesNo));
                cmd.Parameters.Add(new SqlParameter("@SalesDate", (object)ads));
                cmd.Parameters.Add(new SqlParameter("@CategoryName", (object)CategoryName));
                cmd.Parameters.Add(new SqlParameter("@ModelCode", (object)ModelCode));
                cmd.Parameters.Add(new SqlParameter("@ModelName", (object)ModelName));
                cmd.Parameters.Add(new SqlParameter("@ColorName", (object)ColorName));
                cmd.Parameters.Add(new SqlParameter("@ModelSize", (object)ModelSize));
                cmd.Parameters.Add(new SqlParameter("@Quantity", (object)Quantity));
                cmd.Parameters.Add(new SqlParameter("@SalesValue", (object)SalesValue));
                cmd.Parameters.Add(new SqlParameter("@DiscountAmt", (object)DiscountAmt));
                cmd.Parameters.Add(new SqlParameter("@Amount", (object)Amount));
                cmd.Parameters.Add(new SqlParameter("@NetAmount", (object)NetAmount));
                cmd.Parameters.Add(new SqlParameter("@SRQty", (object)SRQty));
                cmd.Parameters.Add(new SqlParameter("@SRValue", (object)SRValue));
                cmd.Parameters.Add(new SqlParameter("@SRDiscountAmt", (object)SRDiscountAmt));
                cmd.Parameters.Add(new SqlParameter("@SRTaxAmt", (object)SRTaxAmt));
                cmd.Parameters.Add(new SqlParameter("@SRNetAmt", (object)SRNetAmt));
                cmd.Parameters.Add(new SqlParameter("@TotalQty", (object)TotalQty));
                cmd.Parameters.Add(new SqlParameter("@TotalValue", (object)TotalValue));
                cmd.Parameters.Add(new SqlParameter("@TotalDiscount", (object)TotalDiscount));
                cmd.Parameters.Add(new SqlParameter("@TotalTax", (object)TotalTax));
                cmd.Parameters.Add(new SqlParameter("@TotalNetValue", (object)TotalNetValue));
                cmd.Parameters.Add(new SqlParameter("@CMID", (object)CMID));
                cmd.Parameters.Add(new SqlParameter("@SMID", (object)SMID));
                cmd.Parameters.Add(new SqlParameter("@CUSTID", (object)CUSTID));
                cmd.Parameters.Add(new SqlParameter("@MMID", (object)MMID));
                cmd.Parameters.Add(new SqlParameter("@Remarks", (object)Remarks));
                cmd.ExecuteNonQuery();
            }
            catch { }
        }
    }
    cs.Close();
}

Richard MacCutchan

Вы можете обращаться к ячейкам по их смещениям столбцов и строк, но вам все равно нужно заранее знать, какими будут эти смещения.

KalaiPondy

можете ли вы дать мне пример кодирования ?

2 Ответов

Рейтинг:
13

KalaiPondy 

private void btnLoadData_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application excelApp;
        Microsoft.Office.Interop.Excel.Workbook workbook;
        Microsoft.Office.Interop.Excel.Worksheet worksheet;
        Microsoft.Office.Interop.Excel.Range range;
        excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

        // Opening Excel file
        workbook = excelApp.Workbooks.Open(txtPath.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

        worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);
        range = worksheet.UsedRange;

        int column = 0;
        int row = 0;

        System.Data.DataTable dt = new System.Data.DataTable();
        dt.Columns.Add("Product");
        dt.Columns.Add("Item/Model Code");
        dt.Columns.Add("Item/Model");
        dt.Columns.Add("Qty.");
        dt.Columns.Add("Sales Value");
        dt.Columns.Add("Discount Amt.");
        dt.Columns.Add("Amount");
        dt.Columns.Add("Net Amount");
        dt.Columns.Add("SR Qty");
        dt.Columns.Add("SR Value");
        dt.Columns.Add("SR Discount Amt.");
        dt.Columns.Add("SR Tax Amt.");
        dt.Columns.Add("SR Net Amt.");
        dt.Columns.Add("Total Qty");
        dt.Columns.Add("Total Value");
        dt.Columns.Add("Total Discount");
        dt.Columns.Add("Total Tax");
        dt.Columns.Add("Total Net Value");

        for (row = 4; row <= range.Rows.Count; row++)
        {
            DataRow dr = dt.NewRow();
            for (column = 1; column <= range.Columns.Count; column++)
            {
                dr[column - 1] = (range.Cells[row, column] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
            }
            dt.Rows.Add(dr);
        }

        workbook.Close();
        excelApp.Quit();

        // Binding to DataGridView
        dataGridView1.DataSource = dt;
    }

Рейтинг:
0

Srilekha Bolamoni

Привет Калайпонди

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string csv_file_path = "Give your filepath here";
DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);
DataView dv = new DataView(csvData);
DataTable dt = dv.ToTable(false, "ColumnName");//which particular columns you want to read
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = ".";
csb.InitialCatalog = "Project";
csb.IntegratedSecurity = true;
string connString = csb.ToString();
using (SqlConnection con = new SqlConnection(connString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "db TableName";
sqlBulkCopy.ColumnMappings.Add("Dealer ID", "DealerID");
sqlBulkCopy.ColumnMappings.Add("Stock", "StockNo");
sqlBulkCopy.ColumnMappings.Add("VIN", "VIN");
sqlBulkCopy.ColumnMappings.Add("Make", "Make");
sqlBulkCopy.ColumnMappings.Add("Model", "Model");
sqlBulkCopy.ColumnMappings.Add("Dealer Name", "DealerName");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
DataTable csvData = new DataTable();
try
{
using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
{
csvReader.SetDelimiters(new string[] { "," });
csvReader.HasFieldsEnclosedInQuotes = true;
string[] colFields = csvReader.ReadFields();
foreach (string column in colFields)
{
DataColumn dtcolumn = new DataColumn(column);
dtcolumn.AllowDBNull = true;
csvData.Columns.Add(dtcolumn);
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
csvData.Rows.Add(fieldData);

}
}

}
catch (Exception)
{
throw;
}
return csvData;

}
}
}

 
Thanks
Srilekha Bolamoni. 

Если это помогло вам тогда отметьте это как ответ
Определенно, это сработает.

CHill60

Калайпонды решили свой собственный запрос еще в 2013 году