Advanced Action Formulas and Functions: Reading and Writing Excel Files

Excel files or more commonly known as spreadsheets are used to store, manipulate, analyze and retrieve statistical data. Spreadsheets work as a down-featured version of database management systems. Common uses of spreadsheets include daily price charts and sales data, student result calculation, employee salary management and similar systems. In short, almost all the statistical data can be stored, manipulated and presented in the form of graphs, charts, and tables via spreadsheets.

The best thing about spreadsheets is that they come with built-in mathematical formulas to perform common calculations such as finding an average of all the values in the column, calculating factorial of values in a particular row or column and finding percentages etc. Apart from built-in functions, excel spreadsheets also allow users to define custom formulas. For instance, you can take a square of each term in the column, subtract the original term from the squared value and then add them via some custom formula. The possibilities are virtually limitless.

Though excel can be used as a standalone application, sometimes we need to work with excel documents via application code. Simple tasks include read from excel file, write in excel file and so on and so forth. In this article, we are going to explain how we can read Microsoft excel spreadsheet file and how to write data in excel file.

We shall explain how to interact with excel spreadsheets via Microsoft.NET programs. For this tutorial, we shall use Bytescout spreadsheet SDK. This standard development kit comes with built-in functions that can be used to read and write data from excel files. To download, Bytescout SDK, go to this link and download the SDK. If you are using Windows computer, your downloaded SDK should be located in C:\Program Files\Bytescout Spreadsheet SDK. From there you can select the .NET version of your choice for the DLL and import in the .NET program.

Creating an Excel Spreadsheet via Bytescout SDK.

Creating an Excel spreadsheet via Bytescout SDK is pretty simple. In the following code snippet, we shall create a C# console application in visual studio. Once the project is created, right-click the name of the project and click add reference. Here you should choose the dynamic link library named Bytescout.Spreadsheet.dll. The following console application is created with .NET Framework version 4.0, therefore the Bytescout library, added in this case, will be that in the 4.0 folder. Now, to create a spreadsheet, take a look at the following code. The explanation for the code is given after that.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Bytescout.Spreadsheet;
using System.IO;
using System.Diagnostics;

namespace ByteScoutApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            Spreadsheet exceldoc = new Spreadsheet();
            Worksheet excelSheet = exceldoc.Workbook.Worksheets.Add("ExcelSampleSheet");

            excelSheet.Cell("A1").Value = "Formula in Textual Form";

            excelSheet.Columns[0].Width = 200;

            excelSheet.Cell("B1").Value = "Formula1 (calculated Value)";

            excelSheet.Columns[1].Width = 200;

            excelSheet.Cell("C1").Value = "Formula2 (calculated Value)";

            excelSheet.Columns[2].Width = 200;

            excelSheet.Cell("A2").Value = "50-2-10";

            excelSheet.Cell("B2").Value = "=50*2+10";

            excelSheet.Cell("C2").Value = "=50/2-10";

            if (File.Exists("Demo.xls"))
            {
                File.Delete("Demo.xls");
            }

            exceldoc.SaveAs("Demo.xls");


            exceldoc.Close();

            Process.Start("Demo.xls");
        }
    }
}

Let’s explain the above code line by line.

The Spreadsheet class is used to create  a Spreadsheet object which is basically the parent class for all the sheets in the excel documents. Here the reference to Spreadsheet object is stored in “exceldoc” variable. Next, we use “exceldoc.Workbook.Worksheets.Add(“ExcelSampleSheet”); method to create a worksheet inside the exceldoc. The above function returns a handler for the newly created sheet. The handler name in our case is “excelSheet”.

Now to access cell within an excel sheet, we simply call “Cell” function on the excelSheet variable and pass it the name of the cell. For instance, we use excelSheet.Cell(“A1”). value in order to set the value of the column. In similar ways, we set values for the B1 and C1 columns. Similarly, we set the width of any column inside the spreadsheet via “excelSheet.Columns[1].Width” property. In the above code, we set the width of A, B and C columns to 200.

Now again we access the A2 cell and add some text to it. In the B2 and C2 cells, we specify that this should be the result of 50*2+10 and 50/2-10. Finally, we check if “demo.xls” exists, if it does exist we delete the previous file and save our new file with the name “demo.xls”. In the end, we open the newly created file. So if you did everything correctly and you run the above code, an excel file shall be opened with a sheet named “ExcelSampleSheet”. It will have three columns and two rows filled. And you shall see the result of 50*2+10 and 50/2-10 in the second and third columns of the second row respectively. This is how you basically create an excel file and add some values to it using Bytescout SDK.

Reading an Excel Sheet Via Bytescout SDK

The process of reading an excel sheet via Bytescout SDK is simple. You first have to import the corresponding Bytescout.Spreadsheet.dll into your program. The following explains how to read each value in the demo.xls that we created in last code sample.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;	
using Bytescout.Spreadsheet;
using System.IO;
using System.Diagnostics;

namespace ByteScoutApplication
{
    class Program
    {
        static void Main(string[] args)
        {

            Spreadsheet excelDoc = new Spreadsheet();
            excelDoc.LoadFromFile("demo.xls");

            Worksheet excelSheet = excelDoc.Workbook.Worksheets.ByName("ExcelSampleSheet");

 
            for (int i = 0; i < 2; i++)
            {
                

                for (int j=0; j<3;j++)
                {
                    Cell currentCell = excelSheet.Cell(i, j);
                 
                        Console.WriteLine( currentCell.Value);
                    
                }
            }
            excelDoc.Close();

            Console.ReadKey();

        }
    }
}

The above code is very straight forward, here we simply use the loadFromFile function of the Spreadsheet object to load “demo.xls” file into excelDoc object. Next, we obtained the handler for the spreadsheet whose cell we want to access which is “ExcelSampleSheet” in our case. Finally, we use two for loops to loop over each and every cell in the spreadsheet. The outer loop iterates over each row while inner loop iterates over each column, we use Cell function of the spreadsheet to access each cell. It takes two parameters the row and column. In the console output, we displayed the value for each cell in the demo.xls file.

From the above two examples, it is clear that Bytescout spreadsheet SDK is extremely handy when it comes to reading and writing excel files. Apart from Spreadsheet SDK, Bytescout provides a variety of developer tools that are used to perform different functionalities. A list of such tools is available at the following link .

About the Author

ByteScout Team of Writers

ByteScout has a team of professional writers specialized in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.