In this article, you will learn how to create Excel XLS/XLSX files programmatically in C# without installing MS Office.
Spreadsheets nowadays have become an essential part of keeping, organizing and analyzing the data. Spreadsheets, such as MS Excel, are capable of doing calculations and sorting, generating graphs or charts, creating budgets, and performing many other accounting or data analysis tasks. Since automated solutions are more in business these days, the trend of creating and manipulating Excel documents (XLS/XLSX) has emerged and growing at a huge pace. This use case has raised the need for having an Excel automation solution.
In accordance with the above-mentioned scenario, this comprehensive article aims to show you some basic features for Excel automation in C# .NET applications.
- C# Excel Automation API
- Create Excel XLS or XLSX files dynamically using C#
- Write data to the existing Excel file using C#
- Create charts or graphs in an Excel file using C#
- Create a table in an Excel file using C#
C# API to Create Excel Files
In order to work with Excel documents, we will use Aspose.Cells for .NET which is a powerful API to create, read, and manipulate spreadsheet documents including XLS and XLSX files. You can either download or install the API using one of the following ways:
Using NuGet Package Manager
Using the Package Manager Console
PM> Install-Package Aspose.CellsCreate an Excel XLS or XLSX in C#
An Excel file is also known as a Workbook which is composed of single or multiple worksheets containing the rows and columns to hold the data. Thus, a workbook acts as the container of the worksheets in an Excel file. So in order to create an Excel file, you will first create a workbook and then the worksheets within that workbook. The following are the steps to create an Excel file using Aspose.Cells for .NET.
- Create an instance of Workbook class.
- Access the first worksheet (created by default) of the workbook.
- Access the desired cell(s) of the worksheet and put the value in the cell(s).
- Save the workbook as an XLS or XLSX file.
The following code sample shows how to create an Excel XLSX file using C#.
Output
Write Data to an Excel XLSX File in C#
In case you want to edit and write data to an existing Excel file, you can also do it in a similar fashion. Simply load the source Excel spreadsheet document using the Workbook object and access the desired worksheets and cells. The following are the steps to edit an existing Excel file.
- Open Excel file in a FileStream object.
- Create an instance of Workbook and initialize it with the FileStream object.
- Access the worksheets and cells using the Worksheet and Cell classes respectively.
- Save the workbook as an Excel .xlsx file.
The following code sample shows how to edit and write data to an existing Excel XLSX file in C#.
Create Charts or Graphs in Excel XLSX File using C#
Excel spreadsheets provide a fine way of analyzing or presenting the data visually using the graphs and charts. Aspose.Cells for .NET provides a complete set of classes to create and manipulate a variety of charts in Excel spreadsheets where each class is used to perform some specific tasks.
In order to create the charts in an Excel file, you’ll have to follow the following steps:
- Add some data (to be used as a data source) to the worksheet by accessing its cells.
- Add a new chart object to the worksheet using Worksheet.Charts collection by passing the type of chart using the ChartType enumeration.
- Get the newly created chart from the collection in a Chart object.
- Specify the cells’ range to provide the data source to the chart object.
- Save the workbook as an Excel .xlsx file.
The following code sample shows how to create a chart in an Excel XLSX file in C#.
Output
Learn more about creating charts in Excel worksheets using C#.
Create a Table in Excel XLSX File in C#
You can also create a table from the range of cells in an Excel worksheet and add a row for the total (sum, count, etc.) in the table. The following are the steps to create a table in an Excel (XLSX) file using Aspose.Cells for .NET:
- Load an Excel workbook or create a new one using Workbook class.
- Add data to the cells of the worksheet.
- Add a new ListObject to the worksheet.
- Set ListObject.ShowTotals property to true.
- Calculate the total and save the workbook as an Excel .xlsx file.
The following code sample shows how to create a table in Excel worksheet in C#.
Output
Learn more about working with tables in Excel worksheets using C#.
Conclusion
In this article, you have learned how to create Excel files from scratch using C#. Furthermore, you have seen how to insert data in sheets, generate charts, and insert tables in Excel files. You may have a look at the documentation of Aspose.Cells for .NET to learn the advanced features for manipulation of Excel files in C#.
