Last post Jun 16, 2017 06:42 AM by aspsak
Jun 14, 2017 06:56 PM|asp.ambur|LINK
Let us assume i have one table name Payment_Details in which i have a field like this below
From the above field Invoice_No Invoice_Date Supplier_Name Amount already there
user will have one excel file data like this below he will uplaod this file using fileuplaod
I want to update this data to my table columns Cheque_No Cheque_Date Bank_Name
how to do so..?
Jun 15, 2017 05:16 AM|EvenMa|LINK
According to your description, I recommend you could use NPOI to read excel, you could use NuGet to install it.
You could download the whole example project by the following link.
See the following code:
<asp:FileUpload ID="fileuploader1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="upload" OnClick="btnUpload_Click"/>
protected void btnUpload_Click(object sender, EventArgs e)
IWorkbook workbook = null;
workbook = WorkbookFactory.Create(fileuploader1.FileContent);
//the file is not the excel file
//get first sheet
var sheet = workbook.GetSheetAt(0);
using (var sqlConnection = new SqlConnection("Data Source=.;Initial Catalog=test;Integrated Security=true"))
using (var tran = sqlConnection.BeginTransaction())
for (var i = 1; i <= sheet.LastRowNum; i++)
var row = sheet.GetRow(i);
//update by invoice no
Update(tran, row.GetCell(0).ToString(), row.GetCell(4).ToString(), row.GetCell(5).DateCellValue, row.GetCell(6).ToString());
catch (Exception ex)
private void Update(SqlTransaction tran, string invoiceno, string chequeno, DateTime chequedate, string bankname)
using (var sqlCommand = new SqlCommand())
sqlCommand.Transaction = tran;
sqlCommand.Connection = tran.Connection;
sqlCommand.CommandText = "UPDATE Payment_Details SET Cheque_No=@chequeno,Cheque_Date=@chequedate,Bank_Name=@bankname WHERE Invoice_No=@invoiceno";
sqlCommand.CommandType = System.Data.CommandType.Text;
If you have any other questions, please feel free to contact me any time.
Jun 15, 2017 10:50 PM|asp.ambur|LINK
Jun 16, 2017 01:27 AM|EvenMa|LINK
You could download the 2.0 project by the following link.
Jun 16, 2017 06:42 AM|aspsak|LINK
I think you are not asking about how to upload, since you said user upload this file using fileupload.
Based on my understating , you have to update the bank details and few other columns in existing table.
If so then follow the below steps.
Step1: upload the excel data to one table lets call ExcelTable and already you have Invoice_Table
Step2: Update the Invoice_Table with bank_Name, Cheque_No,etc for respective invoice numbers (I assumed invoice number is unique) using below query
SET t1.Bank_Name = t2.Bank_Name,t1.Cheque_Num=t2.Cheque_Num
FROM Invoice_Table AS t1
INNER JOIN ExcelTable AS t2
ON t1.Invoice_No= t2.Invoice_No