Creating an Excel file in C# is not as hard as many beginners believe. With no internal library support for creating Excel sheets, you are left with 3rd party libraries. Thankfully, there are plenty of free 3rd party libraries that help make it easy to create .XLS and .XLSX files.
Let’s get started with the tutorial. Before we get into the code, we need to go through the library options available when creating an Excel sheet. You can use EPPlus, a C# library for creating advanced Excel spreadsheets on the server, or you can use ExcelLibrary which can get your work done without much difficulty.
For the sake of simplicity, we will be using ExcelLibrary. However, if you need more control over your Excel sheet, it is advisable to use EEPlus which is frequently updated and comes under the GNU public license(GPL)
Alright, enough theory! Let’s get to the code below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
//Table and Dataset creation DataSet ds = new DataSet("New_DataSet"); DataTable dt = new DataTable("New_DataTable"); //Creating locale for each ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; //Creating DB connection with OleDb. OleDbConnection con = new OleDbConnection(dbConnectionString); con.Open(); //Query creation. string sql = "SELECT Whatever FROM MyDBTable;"; OleDbCommand cmd = new OleDbCommand(sql, con); OleDbDataAdapter adptr = new OleDbDataAdapter(); adptr.SelectCommand = cmd; adptr.Fill(dt); con.Close(); //Adding tables ds.Tables.Add(dt); // DataSet used in table creation.s ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds); |
Let’s go through the code to understand how it works. Most of it is self-explanatory and can be understood by the comments included within the code. The last line is what we all care about.
|
1 |
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds); |
The library lets you create an Excel file with the help of the data set. Yup, it is that easy. For more features and functionalities, you need to visit the library page and look at its documentation.
Has anything to add to the tutorial series? Comment below and let us know.
You can also check on our website videos about C#. Below are some examples:
- Steam/C# gamedev: customizing game server (part 2) – C#
- C# compiler – new feature (part 33) – C#
You can also follow some of our broadcasters who program in C#, as below:
Another cool way to find out interesting things about C# is to access our project page!
