Sometimes the need to perform calculations with data taken from other files, in this example we will see how to perform the sum of the data of a column from an excel file. Consider this code example.
- private void BtnCalcolaSommaClick(object sender, EventArgs e)
- {
- const string query = "SELECT SUM(TOTAL) FROM [Foglio1$]";
-
- using (var myConnection = new OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;data source=E:\demo.XLSx;Extended Properties=Excel 12.0;"))
- {
- myConnection.Open();
-
- using (myConnection)
- {
- object result;
- using (var myCommand = new OleDbCommand(query, myConnection))
- result = myCommand.ExecuteScalar();
-
- if (!result.Equals(DBNull.Value))
- {
- TextBox1.Text = result.ToString();
- }
- }
- }
- }
First, we create a Select query with the name of the column of the excel file in parentheses and the name of the sheet in this Excel file on which to go to pick up the information, make sure that both column name and then name the sheet are the same as those contained in excel file to avoid errors at runtime.
We will use the class OleDbConnection and passing as an argument the connection string to the data source, in this example E: \ demo.xlsx, then the class OleDbCommand which provides the method ExecuteScalar. This method returns the first column of the first row in the result set, or a null reference if the result set is empty.
Since the method can return a null value and need to make a comparison and discard all null values, and also to display the sum inside the TextBox control and required a conversion value from Object to character string, because the value returned by ExecuteScalar method and type Object.
In conclusion, assuming you have a file called demo.xlsx inside with a column called TOTAL with 10 lines each with 1 value must have coma result of adding 10 as shown.
After this sum it will instead result in the application.
