Create Excel Files in C#
Creating report files in Excel format is a feature that many users need out of their software applications.
In this article. we'll cover creating an Excel spreadsheet in C# using a third-party library called EPPlus.
EPPlus is a .NET library that reads and writes Excel files using the Open Office XML format (*.xlsx). It is licensed under GNU Library General Public License (LGPL) so it can be freely used in commercial software.
Installation
In Visual Studio 2017, go to Tools -> NuGet Package Manager -> Package Manager Console
In the console window that appears, type the following at the prompt to install it into the current project:
PM > Install-Package EPPlus
After it is finished, check the References under Solution Explorer and verify that the EPPlus assembly is listed.
Add to Project
We add line 7 below so we can use the types inside the namespace:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;
namespace ConsoleApp2
{
class Program
{
static void Main(string[] args)
{
}
}
}
Create a Workbook
We call the ExcelPackage class constructor to create a workbook. In the below code, we do this within a using block so we don't have to explicitly dispose of the object.
We add 3 blank worksheets to the workbook by using the Add() method in the Worksheets class. Finally, we save the file to a path using the SaveAs() method.
using (ExcelPackage excel = new ExcelPackage())
{
excel.Workbook.Worksheets.Add("Worksheet1");
excel.Workbook.Worksheets.Add("Worksheet2");
excel.Workbook.Worksheets.Add("Worksheet3");
FileInfo excelFile = new FileInfo(@"C:\Users\amir\Desktop\test.xlsx");
excel.SaveAs(excelFile);
}
The SaveAs method will throw an exception if there are no worksheets defined.
Select a Worksheet
Let's say we want to add some data to Worksheet1. How can we target it?
var excelWorksheet = excel.Workbook.Worksheets["Worksheet1"];
Add a Row
Spreadsheets typically have header rows. Let's add a header row to Worksheet1.
For this, we create a List containing an array of strings:
List<string[]> headerRow = new List<string[]>()
{
new string[] { "ID", "First Name", "Last Name", "DOB" }
};
Before we can pass this object into our worksheet, we must figure out the cell range for the header row:
// Determine the header range (e.g. A1:E1)
string headerRange = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";
Here's how we load the data into the worksheet:
// Popular header row data
excelWorksheet.Cells[headerRange].LoadFromArrays(headerRow);
Putting it all together here is the code block:
using (ExcelPackage excel = new ExcelPackage())
{
excel.Workbook.Worksheets.Add("Worksheet1");
excel.Workbook.Worksheets.Add("Worksheet2");
excel.Workbook.Worksheets.Add("Worksheet3");
var headerRow = new List<string[]>()
{
new string[] { "ID", "First Name", "Last Name", "DOB" }
};
// Determine the header range (e.g. A1:D1)
string headerRange = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";
// Target a worksheet
var worksheet = excel.Workbook.Worksheets["Worksheet1"];
// Popular header row data
worksheet.Cells[headerRange].LoadFromArrays(headerRow);
FileInfo excelFile = new FileInfo(@"C:\Users\amir\Desktop\test.xlsx");
excel.SaveAs(excelFile);
}
If we open the file in Excel, we see that the header row appears in the first worksheet:
Row Styling
We can easily change the font size, color, and weight of any row.
worksheet.Cells[headerRange].Style.Font.Bold = true;
worksheet.Cells[headerRange].Style.Font.Size = 14;
worksheet.Cells[headerRange].Style.Font.Color.SetColor(System.Drawing.Color.Blue);
Add Data to Specific Cell
We can easily add data to a cell by setting the Value property of the Cell element.
worksheet.Cells["A1"].Value = "Hello World!";
Add Data to Multiple Cells
Inserting data into multiple cells is also straightforward. We can populate a list containing an array that corresponds to each row.
var cellData= new List()
{
new object[] {0, 0, 0, 1},
new object[] {10,7.32,7.42,1},
new object[] {20,5.01,5.24,1},
new object[] {30,3.97,4.16,1},
new object[] {40,3.97,4.16,1},
new object[] {50,3.97,4.16,1},
new object[] {60,3.97,4.16,1},
new object[] {70,3.97,4.16,1},
new object[] {80,3.97,4.16,1},
new object[] {90,3.97,4.16,1},
new object[] {100,3.97,4.16,1}
};
excelWorksheet.Cells[2, 1].LoadFromArrays(cellData);
Here we add the data from cellData into the worksheet starting on row 2, column 1. Remember that we already inserted a header row on row 1.
Check if Excel is installed
We can use this line of code to determine if Excel is installed:
bool isExcelInstalled = Type.GetTypeFromProgID("Excel.Application") != null ? true : false;
If the expression evaluates to true, then we can safely open the file using the below code:
if (isExcelInstalled)
{
System.Diagnostics.Process.Start(excelFile.ToString());
}
What about Microsoft.Office.Interop.Excel?
If the user has Excel installed on their machine, we could tap into the Microsoft.Office.Interop.Excel assembly which instructs .NET on how to call the Excel COM libraries.
This approach has a couple flaws though. Excel file generation would fail if the user didn't have Excel installed. Also, what happens when the version of the assembly we're using is incompatible with the latest version of Excel?
A software application shouldn't rely on an Excel installation in order to create a spreadsheet which is why I discourage using Microsoft.Office.Interop.Excel.
'Knowledge' 카테고리의 다른 글
[리눅스] 스크린 명령어 (0) | 2018.05.01 |
---|---|
[ASP.NET] [EPPlus] Create/Read/Edit Advance Excel 2007/2010 (0) | 2018.05.01 |
[ASP.NET] 파일 다운로드를 구현하는 방법 (0) | 2018.05.01 |
[.NET] SFTP (0) | 2018.04.27 |
Using AWS KMS Master Keys with the AmazonS3EncryptionClient in the AWS SDK for .NET (0) | 2018.04.27 |