如何使用c #windows应用程序读取excel文件中的特定列并存储在sql DataBase表中 [英] how to read specific columns in excel file and store in sql DataBase table using c# windows application
本文介绍了如何使用c #windows应用程序读取excel文件中的特定列并存储在sql DataBase表中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
嗨
在我的项目中，我使用C＃Winforms 2010将数据从Excel导入到Sql server 2008.
我可以读取Excel文件通过映射列标题的第一行中的列并将其存储到SQL数据库表。但问题是在Excel文件中假设第一行被更改为第二行或任何其他行然后C＃代码无法映射行并通过错误。那么如何使用c＃windows应用程序读取excel文件中的特定列并存储在sql表中？
这是我的代码。
可以任意一个帮我正确答案
Hi
In My Project, I'm Import data from Excel to Sql server 2008 Using C# Winforms 2010.
I can read the Excel File by Mapping the Columns in First row of the Column Header and store it to sql DataBase Table . But the Problem is That Suppose in Excel File First row is Been Changed to Second Row or AnyOther Rows Then C# Code Can't Map the Rows and Through an Error. So How to Read specific columns in excel file and store in sql table using c# windows application ?
Here Is My Code .
Can Any One Help Me With Right Answer
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();
}
推荐答案
，连接）;
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。添加（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 （ 净额，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（总折扣，TotalDiscount）;
bulkCopy.ColumnMappings.Add（Total Tax，TotalTax）;
bulkCopy.ColumnMappings.Add（总净值，TotalNetValue）;
bulkCopy.WriteToServer（dr）;
}
}
connection.Close（）;
cs.Close（）;
EmptyData（）;
ErrorAlert（）;
MessageBox.Show（数据导出到Sql Server成功）;
}
}
catch
{
MessageBox.Show（导出到Sql Server的数据已失败，错误，MessageBoxButtons.OK，MessageBoxIcon.Error ）;
}
}
private void EmptyData（）
{
try {Connection（）; }
catch {return; }
cmd = new SqlCommand（从ExcelCustomerSales删除，其中ModelCode为空，ModelName为空，数量为空，SalesValue为空，DiscountAmt为空，金额为空，NetAmount为空，SRQty为空，SRValue为空AND SRDiscountAmt IS为空且SRTaxAmt为空且SRNetAmt为空且TotalQty为空且TotalValue为空且TotalDiscount为空且TotalTax为空且TotalNetValue为空，cs）;
cmd.ExecuteNonQuery（）;
cs.Close（）;
}
public void ErrorAlert（）
{
try {Connection（）; }
catch {return; }
int Num1 = 0，Num2 = 0;
cmd = new SqlCommand（从CustomerSalesNo中选择MAX（ID），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 =，备注=保存客户销售报告;
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（dt.Rows中的DataRow Row）
{
ModelCode = Row [ModelCode]。ToString（）;
ModelName = Row [ModelName]。ToString（）;
数量=行[数量] .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（从ModelMaster中选择ModelCode，CategoryName，ModelSize，ID，MSID，其中ModelCode ='+ ModelCode +'，cs）;
DataTable dt1 = new DataTable（）;
da1.Fill（dt1）;
foreach（dt1.Rows中的DataRow Row1）
{
MMCode = Row1 [ModelCode]。ToString（）;
CategoryName = Row1 [CategoryName]。ToString（）;
ModelSize = Row1 [ModelSize]。ToString（）;
MMID = Row1 [ID]。ToString（）;
SMID = Row1 [MSID]。ToString（）;
}
SqlDataAdapter da2 = new SqlDataAdapter（选择CustomerCode，来自CustomerMaster的ID，其中CustomerName ='+ IcCutomerName.Text +'，cs）;
DataTable dt2 = new DataTable（）;
da2.Fill（dt2）;
foreach（dt2.Rows中的DataRow Row2）
{
CustomerCode = Row2 [CustomerCode]。ToString（）;
CUSTID = Row2 [ID]。ToString（）;
}
if（ModelCode.ToUpper（）== MMCode.ToUpper（））
{
try
{
cmd = new SqlCommand（插入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，备注）值（@ CustomerCode，@ CustomerName，@ SaleNo，@ SaleDate，@ CategoryName，@ ModelCode，@ ModelName，@ ColorName，@ ModelSize，@ Quantant，@ SaleValue， @DiscountAmt，@ Amount，@ NetAmount，@ SRQty，@ SVValue，@ SRDiscountAmt，@ SRTaxAmt，@ SGAAmt，@ TotalQty，@ TotalValue，@ TotalDiscount，@ TotalTax，@ TotalNetValue，@ CMID，@ SMID，@ CUTID，@ MMID ，@评论），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（ 插入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，备注）值（@ CustomerCode，@ CustomerName，@ SaleNo，@ SalesDate，@ CategoryoryName，@ ModelCode，@ ModelName，@ ColorName，@ ModelSize，@ Quantity，@ SaleValue，@ DiscountAmt ，@ Amount，@ NetAmount，@ SRQty，@ SVValue，@ SRDiscountAmt，@ SRTaxAmt，@ SGAAmt，@ TotalQty，@ TotalValue，@ TotalDiscount，@ TotalTax，@ TotalNetValue，@ CWID，@ SMID，@ Customer @，@ MMID，@备注），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（）;
}
", 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(); }
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;
}
Hi KalaiPondy
Hi KalaiPondy
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.
If it helped you then mark it as an answer
Definitely it will work.
If it helped you then mark it as an answer
Definitely it will work.
这篇关于如何使用c #windows应用程序读取excel文件中的特定列并存储在sql DataBase表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
