none
output template using C#.NET RRS feed

  • Question

  • I have data table like below.

    D(EmpNo)      A(EmpName)    H(Salary)

    ------------     ---------------    -----------

    AZ1001          Afsal                $1000

    BX20202         Karus              $2000

    CA2930           Rax                 $10000

    ...............

    ...............

    How to update above values into particular excel columns.

    For example, D(EmpNo) should be updated in "D12"  of the Excel Template

                        A(empName) should be updated in "A12" of the excel template. like that others..

    How to updated using reference and update in excel corresponding column in a template.


    • Changed type Gani tpt Thursday, January 23, 2020 5:06 AM General
    • Edited by Gani tpt Thursday, January 23, 2020 5:13 AM
    • Changed type Naomi NModerator Thursday, April 23, 2020 2:20 AM question
    Monday, January 20, 2020 8:31 AM

Answers

  • Hello,

    The following code sample (there is an excel file in the bin\debug folder) which I had laying around uses a DataAdapter configured with a select and update commands which finds desired data by two columns, if found will update these two values from values in TextBox controls. How would you adapt? Instead of finding by two columns find by one then set the desired column rather than the two columns searched by via a WHERE condition.

    Challenges, my connection string to the Excel file may not work for your Excel file, it's configurable. Next up, depending on the data type in the cell that might be a problem which with little effort can be remedied. Also, in your case if the column to find by has duplicates than more than one row will be updated, no way around this.

    Personally I would not be using an Excel WorkSheet like this (as a database table essentially). If I had too it would be with a third party library such as EPPlus, SpreadSheetLight or GemBox SpreadSheet.

    Connection helper

    using System;
    using System.Data.OleDb;
    using System.IO;
    
    namespace ExcelOleDbDataAdapter
    {
        internal static class ExcelExtensions
        {
            public enum UseHeader
            {
                /// <summary>
                /// Indicates that the first row contains column names, no data
                /// </summary>
                /// <remarks></remarks>
                Yes,
                /// <summary>
                /// Indicates that the first row does not contain column names
                /// </summary>
                /// <remarks></remarks>
                No
            }
            public enum ExcelImex
            {
                TryScan = 0,
                Resolve = 1
            }
    
            public static void SetExcelConnectionString(this OleDbConnection sender, string fileName, UseHeader header, ExcelImex IMEX)
            {
    
                string mode = Convert.ToInt32(IMEX).ToString();
                var builder = new OleDbConnectionStringBuilder { DataSource = fileName };
                if (Path.GetExtension(fileName).ToUpper() == ".XLSX")
                {
                    builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                    builder.Add("Extended Properties", "Excel 12.0;IMEX=" + mode + ";HDR=" + header + ";");
                }
                else
                {
                    builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                    builder.Add("Extended Properties", "Excel 8.0;IMEX=" + mode + ";HDR=" + header + ";");
                }
    
                sender.ConnectionString = builder.ConnectionString;
            }
        }
    
    }
    

    Form code

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    using System.Windows.Forms;
    
    namespace ExcelOleDbDataAdapter
    {
        public partial class Form1 : Form
        {
            private string _fileName = Path.Combine(Application.StartupPath, "File1.xls");
            private OleDbConnection _excelConnection = new OleDbConnection();
            private DataSet _dataSet1 = new DataSet();
            private OleDbDataAdapter _excelAdapter = new OleDbDataAdapter();
            private BindingSource _bindingSource = new BindingSource();
    
            public Form1()
            {
                InitializeComponent();
                
                Shown += Form1_Shown;
                _bindingSource.PositionChanged += _bindingSource_PositionChanged;
            }
    
            private void _bindingSource_PositionChanged(object sender, EventArgs e)
            {
                var dataRow = ((DataRowView)_bindingSource.Current).Row;
                if (string.IsNullOrWhiteSpace(dataRow.Field<string>("FirstName")) || string.IsNullOrWhiteSpace(dataRow.Field<string>("LastName")))
                {
                    UpdateButton.Enabled = false;
                }
                else
                {
                    UpdateButton.Enabled = true;
                }
            }
    
            private void Form1_Shown(object sender, EventArgs e)
            {
                _excelConnection.SetExcelConnectionString(
                    _fileName, 
                    ExcelExtensions.UseHeader.Yes, 
                    ExcelExtensions.ExcelImex.TryScan);
    
                var cmd = new OleDbCommand("SELECT Firstname, Lastname FROM [Sheet1$]", _excelConnection);
                _excelAdapter.SelectCommand = cmd;
                _excelConnection.Open();
                _excelAdapter.Fill(_dataSet1, "Sheet1");
                _bindingSource.DataSource = _dataSet1.Tables[0];
                dataGridView1.DataSource = _bindingSource;
    
                _excelAdapter.UpdateCommand = new OleDbCommand
                {
                    CommandText = "UPDATE [Sheet1$] " + 
                                  "SET FirstName=@P1, LastName = @P2 " + 
                                  "WHERE FirstName=@P3 AND LastName = @P4",
                    Connection = _excelConnection
                };
    
                _excelAdapter.UpdateCommand.Parameters.Add(new OleDbParameter
                {
                    ParameterName = "@P1",
                    OleDbType = OleDbType.WChar
                });
    
                _excelAdapter.UpdateCommand.Parameters.Add(new OleDbParameter
                {
                    ParameterName = "@P2",
                    OleDbType = OleDbType.WChar
                });
    
                _excelAdapter.UpdateCommand.Parameters.Add(new OleDbParameter
                {
                    ParameterName = "@P3",
                    OleDbType = OleDbType.WChar
                });
    
                _excelAdapter.UpdateCommand.Parameters.Add(new OleDbParameter
                {
                    ParameterName = "@P4",
                    OleDbType = OleDbType.WChar
                });
    
            }
    
            private void UpdateButton_Click(object sender, EventArgs e)
            {
                var dataRow = ((DataRowView) _bindingSource.Current).Row;
    
                if (string.IsNullOrWhiteSpace(FirstNameTextBox.Text) || string.IsNullOrWhiteSpace(LastNameTextBox.Text))
                {
                    MessageBox.Show("Need values");
                    return;
                }
    
                _excelAdapter.UpdateCommand.Parameters[0].Value = dataRow.Field<string>("FirstName");
                _excelAdapter.UpdateCommand.Parameters[1].Value = dataRow.Field<string>("LastName");
                _excelAdapter.UpdateCommand.Parameters[2].Value = FirstNameTextBox.Text;
                _excelAdapter.UpdateCommand.Parameters[3].Value = LastNameTextBox.Text;
    
                var affected = _excelAdapter.UpdateCommand.ExecuteNonQuery();
    
                if (affected == 1)
                {
                    dataRow.SetField("FirstName", FirstNameTextBox.Text);
                    dataRow.SetField("LastName", LastNameTextBox.Text);
                }
                else if (affected > 1)
                {
                    /*
                     * Very easy for this to happen
                     */
                    MessageBox.Show("More than one row has been updated, was this expected?");
                }
                else
                {
                    MessageBox.Show("Nothing updated");
                }
    
            }
        }
    }
    

     


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Gani tpt Thursday, January 23, 2020 5:06 AM
    • Marked as answer by Gani tpt Monday, April 27, 2020 2:38 AM
    Monday, January 20, 2020 11:54 AM
    Moderator

All replies

  • Hello,

    The following code sample (there is an excel file in the bin\debug folder) which I had laying around uses a DataAdapter configured with a select and update commands which finds desired data by two columns, if found will update these two values from values in TextBox controls. How would you adapt? Instead of finding by two columns find by one then set the desired column rather than the two columns searched by via a WHERE condition.

    Challenges, my connection string to the Excel file may not work for your Excel file, it's configurable. Next up, depending on the data type in the cell that might be a problem which with little effort can be remedied. Also, in your case if the column to find by has duplicates than more than one row will be updated, no way around this.

    Personally I would not be using an Excel WorkSheet like this (as a database table essentially). If I had too it would be with a third party library such as EPPlus, SpreadSheetLight or GemBox SpreadSheet.

    Connection helper

    using System;
    using System.Data.OleDb;
    using System.IO;
    
    namespace ExcelOleDbDataAdapter
    {
        internal static class ExcelExtensions
        {
            public enum UseHeader
            {
                /// <summary>
                /// Indicates that the first row contains column names, no data
                /// </summary>
                /// <remarks></remarks>
                Yes,
                /// <summary>
                /// Indicates that the first row does not contain column names
                /// </summary>
                /// <remarks></remarks>
                No
            }
            public enum ExcelImex
            {
                TryScan = 0,
                Resolve = 1
            }
    
            public static void SetExcelConnectionString(this OleDbConnection sender, string fileName, UseHeader header, ExcelImex IMEX)
            {
    
                string mode = Convert.ToInt32(IMEX).ToString();
                var builder = new OleDbConnectionStringBuilder { DataSource = fileName };
                if (Path.GetExtension(fileName).ToUpper() == ".XLSX")
                {
                    builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                    builder.Add("Extended Properties", "Excel 12.0;IMEX=" + mode + ";HDR=" + header + ";");
                }
                else
                {
                    builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                    builder.Add("Extended Properties", "Excel 8.0;IMEX=" + mode + ";HDR=" + header + ";");
                }
    
                sender.ConnectionString = builder.ConnectionString;
            }
        }
    
    }
    

    Form code

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    using System.Windows.Forms;
    
    namespace ExcelOleDbDataAdapter
    {
        public partial class Form1 : Form
        {
            private string _fileName = Path.Combine(Application.StartupPath, "File1.xls");
            private OleDbConnection _excelConnection = new OleDbConnection();
            private DataSet _dataSet1 = new DataSet();
            private OleDbDataAdapter _excelAdapter = new OleDbDataAdapter();
            private BindingSource _bindingSource = new BindingSource();
    
            public Form1()
            {
                InitializeComponent();
                
                Shown += Form1_Shown;
                _bindingSource.PositionChanged += _bindingSource_PositionChanged;
            }
    
            private void _bindingSource_PositionChanged(object sender, EventArgs e)
            {
                var dataRow = ((DataRowView)_bindingSource.Current).Row;
                if (string.IsNullOrWhiteSpace(dataRow.Field<string>("FirstName")) || string.IsNullOrWhiteSpace(dataRow.Field<string>("LastName")))
                {
                    UpdateButton.Enabled = false;
                }
                else
                {
                    UpdateButton.Enabled = true;
                }
            }
    
            private void Form1_Shown(object sender, EventArgs e)
            {
                _excelConnection.SetExcelConnectionString(
                    _fileName, 
                    ExcelExtensions.UseHeader.Yes, 
                    ExcelExtensions.ExcelImex.TryScan);
    
                var cmd = new OleDbCommand("SELECT Firstname, Lastname FROM [Sheet1$]", _excelConnection);
                _excelAdapter.SelectCommand = cmd;
                _excelConnection.Open();
                _excelAdapter.Fill(_dataSet1, "Sheet1");
                _bindingSource.DataSource = _dataSet1.Tables[0];
                dataGridView1.DataSource = _bindingSource;
    
                _excelAdapter.UpdateCommand = new OleDbCommand
                {
                    CommandText = "UPDATE [Sheet1$] " + 
                                  "SET FirstName=@P1, LastName = @P2 " + 
                                  "WHERE FirstName=@P3 AND LastName = @P4",
                    Connection = _excelConnection
                };
    
                _excelAdapter.UpdateCommand.Parameters.Add(new OleDbParameter
                {
                    ParameterName = "@P1",
                    OleDbType = OleDbType.WChar
                });
    
                _excelAdapter.UpdateCommand.Parameters.Add(new OleDbParameter
                {
                    ParameterName = "@P2",
                    OleDbType = OleDbType.WChar
                });
    
                _excelAdapter.UpdateCommand.Parameters.Add(new OleDbParameter
                {
                    ParameterName = "@P3",
                    OleDbType = OleDbType.WChar
                });
    
                _excelAdapter.UpdateCommand.Parameters.Add(new OleDbParameter
                {
                    ParameterName = "@P4",
                    OleDbType = OleDbType.WChar
                });
    
            }
    
            private void UpdateButton_Click(object sender, EventArgs e)
            {
                var dataRow = ((DataRowView) _bindingSource.Current).Row;
    
                if (string.IsNullOrWhiteSpace(FirstNameTextBox.Text) || string.IsNullOrWhiteSpace(LastNameTextBox.Text))
                {
                    MessageBox.Show("Need values");
                    return;
                }
    
                _excelAdapter.UpdateCommand.Parameters[0].Value = dataRow.Field<string>("FirstName");
                _excelAdapter.UpdateCommand.Parameters[1].Value = dataRow.Field<string>("LastName");
                _excelAdapter.UpdateCommand.Parameters[2].Value = FirstNameTextBox.Text;
                _excelAdapter.UpdateCommand.Parameters[3].Value = LastNameTextBox.Text;
    
                var affected = _excelAdapter.UpdateCommand.ExecuteNonQuery();
    
                if (affected == 1)
                {
                    dataRow.SetField("FirstName", FirstNameTextBox.Text);
                    dataRow.SetField("LastName", LastNameTextBox.Text);
                }
                else if (affected > 1)
                {
                    /*
                     * Very easy for this to happen
                     */
                    MessageBox.Show("More than one row has been updated, was this expected?");
                }
                else
                {
                    MessageBox.Show("Nothing updated");
                }
    
            }
        }
    }
    

     


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Gani tpt Thursday, January 23, 2020 5:06 AM
    • Marked as answer by Gani tpt Monday, April 27, 2020 2:38 AM
    Monday, January 20, 2020 11:54 AM
    Moderator
  • Thanks karen for your reply.

    Actually i already posted something related to below topic.

    How to read reference column and print output in dynamic template using windows C#.NET

    Because my output template contains "n" number of columns and dynamic template.

    my output should print where i already defined in the column template. based on that the values should reflect.

    pls. refer the again the below url.

    I am not sure how to read the reference column, and convert it to data table, then update into excel template dynamically (based on the column reference).

    Reference column

    Note : i am struggling a lot how to proceed further..?




    As I said in my initial reply that my code does not match your exact condition so this is where you learn and adapt e.g. your update statement would look like this then adjust the parameters to match. Also I have no clue is the () will be allowed, never even considered using them with a DataAdapter and if it does not work you would need to look at the libraries I mentioned.

    CommandText = "UPDATE [YourSheetNameGoeshere$] " +
                  "SET [A(EmpName)]=@P1 " +
                  "WHERE [D(EmpNo)]=@P2",

    And I will repeat it's truly not wise to treat a WorkSheet as a database table which you are currently doing.

    Lastly looking at EPPlus we can get the cell (which has row/column) to work through an update.

    var query = 
        from cell in sheet.Cells["a:a"]
        where cell.Value?.ToString() == "BX20202"
        select cell;


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Gani tpt Thursday, January 23, 2020 5:06 AM
    Monday, January 20, 2020 2:16 PM
    Moderator
  • pls. close this..this has been solved...thanks.
    Thursday, April 23, 2020 1:55 AM
  • You can post your final solution so other members may benefit.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 23, 2020 2:13 AM
    Moderator