How to create and read CSV, Excel and PDF files in .NET for free

Published: Tuesday 24 September 2019

With the number of online businesses soaring, being able to produce reports on how a business is performing is essential. The reports could vary from a sales report, to a customers report. And businesses want to be able to create these on demand. But how can you automate these reports in .NET?

Popular File Types

A file type that is popular for business is spreadsheets. Spreadsheets can be created using Excel, or you can create a Comma Seperated Values (CSV) file. Businesses also like reports being produced and this can be done with a Portable Document Format (PDF) file. PDF files makes it easy to view and share a report. But how do you create these in .NET and how can you do it for free? I'm going to show you how with some great NuGet packages.

CSVHelper

CSVHelper does what the name suggests. It helps read and write CSV files. One of the benefits of CSVHelper is that it can create a file by reading an IEnumerable object. As a result, if your data is in a list, you can just pass the list into CSVHelper without any additional coding.

In the following .NET Core console application example, we have created a CSV file inside a MemoryStream object and stored the bytes in a ByteArray object. We then read the CSV file and output it's contents into a console application. Of course, you can store the CSV file in a physical CSV file on your hard disk.

Before you can work with this example, you need to make sure that you add the CSVHelper NuGet package to your Visual Studio project. For the purposes of this example, we are using version 12.1.2.

// CSVHelper.cs
using CsvHelper;
using System;
using System.Collections.Generic;
using System.IO;
 
namespace CSVHelper_Example
{
	class Program
	{
		static void Main(string[] args)
		{
			byte[] csvFileBytes;

			using (var memoryStream = new MemoryStream())
			{
				// That's write the CSV file
				using (var writer = new StreamWriter(memoryStream))
				{
					var records = new List<Record>
					{
						{ new Record { Id = 1, Name = "Hello" } },
						{ new Record { Id = 2, Name = "Goodbye" } }
					};

					using (var csv = new CsvWriter(writer))
					{
						csv.WriteHeader(typeof(Record));
						csv.NextRecord();
						csv.WriteRecords(records);
					}
				}

				csvFileBytes = memoryStream.ToArray(); // Stores the CSV file that we just created as a byte array
			}

			using (var memoryStream = new MemoryStream(csvFileBytes))
			{
				// Now that's read the CSV file we've just written
				using (var reader = new StreamReader(memoryStream))
				{
					using (var csvReader = new CsvReader(reader))
					{
						csvReader.Read(); // Reads the header

						while (csvReader.Read()) // Goes onto the next line.
						{
							Console.WriteLine(" --- ");
							Console.WriteLine("Id - " + csvReader.GetField(0));
							Console.WriteLine("Name - " + csvReader.GetField(1));
						}
					}
				}

				Console.ReadKey();
			}
		}

		class Record
		{
			public int Id { get; set; }
			public string Name { get; set; }
		}
	}
}

The output of the console application reads:

---

Id - 1

Name - Hello

 ---

Id - 2

Name - Goodbye

EPPlus

EPPlus allows you to manage Excel files, allowing you to read or write them. Like CSVHelper, you can create a file by reading an IEnumerable object. But, unlike CSV files, you can also add multiple worksheets, meaning you can have more than one data grid in one single Excel file.

The .NET Core console application example below shows how to write and read an Excel File. Like with our CSV example, we have written the contents of the Excel file to a MemoryStream byte array and then we are reading it to output the results. You can also save your Excel document in a physical file.

You must remember to add the EPPlus NuGet package. The example below has been written using version 4.5.3.2 of EPPlus.

// EPPlus.cs
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
 
namespace EPPlus_Example
{
	class Program
	{
		static void Main(string[] args)
		{
			byte[] excelBytes;

			var records = new List<Record>
					{
						{ new Record { Id = 1, Name = "Hello" } },
						{ new Record { Id = 2, Name = "Goodbye" } }
					};

			using (var memoryStream = new MemoryStream())
			{
				using (var excelPackage = new ExcelPackage(memoryStream))
				{
					var myRecordsWorkbook = excelPackage.Workbook.Worksheets.Add("My Records");

					myRecordsWorkbook.Cells[1, 1].LoadFromCollection(records);

					excelPackage.Save();
				}

				excelBytes = memoryStream.ToArray();
			}


			using (var memoryStream = new MemoryStream(excelBytes))
			{
				// Now that's read the CSV file we've just written    
				using (var excelPackage = new ExcelPackage(memoryStream))
				{
					var myRecordsWorkbook = excelPackage.Workbook.Worksheets["My Records"];

					Console.WriteLine("First Row, First Cell - " + myRecordsWorkbook.Cells[1, 1].Value.ToString());
					Console.WriteLine("First Row, Second Cell - " + myRecordsWorkbook.Cells[1, 2].Value.ToString());

					Console.WriteLine("Second Row, First Cell - " + myRecordsWorkbook.Cells[2, 1].Value.ToString());
					Console.WriteLine("Second Row, Second Cell - " + myRecordsWorkbook.Cells[2, 2].Value.ToString());

				}


				Console.ReadKey();
			}
		}

		class Record
		{
			public int Id { get; set; }

			public string Name { get; set; }
		}
	}
}

The output of the console application reads:

First Row, First Cell - 1

First Row, Second Cell - Hello

Second Row, First Cell - 2

Second Row, Second Cell - Goodbye

SelectPDF

PDF documents are good for sharing and viewing documents. With SelectPDF, you can create your own PDF document by passing in HTML code. You can also take advantage of CSS to style the document in the way that you want it.

And that's not all! You can also create a PDF of an active web page, just by typing in the URL of the website you wish to create. And that's what we are looking at in our .NET Core console application example below.

Just remember to add the Select.HtmlToPdf.NetCore NuGet package, and the example below is using version 19.1 of the Select.HtmlToPdf.NetCore package. Incidentally, there is also a Select.HtmlToPdf NuGet package, that supports .NET Framework.

// SelectPDF.cs
using SelectPdf;
 
namespace SelectHtmlPdf_Example
{
	class Program
	{
		static void Main(string[] args)
		{
			var htmlToPdf = new HtmlToPdf();
			
			var pdfHtmlDocument = htmlToPdf.ConvertHtmlString("<html><head><style>html { font-size: 48px; }</style></head><body>THIS IS BIG TEXT<br />THIS IS MORE TEXT<br />THIS IS MORE TEXT</body></html>");

			pdfHtmlDocument.Save("Html.pdf");

			pdfHtmlDocument.Close();

			// convert the url to pdf
			htmlToPdf = new HtmlToPdf(1920, 1080);
			var pdfUrlDocument = htmlToPdf.ConvertUrl("https://www.roundthecode.com/?notrack=1");
			
			// save pdf document
			pdfUrlDocument.Save("RoundTheCode.pdf");

			// close pdf document
			pdfUrlDocument.Close();
		}
	}
}

This example generates an example HTML page by putting in HTML code. It also shows how the homepage of the Round The Code website looks on 22nd September 2019. You can view these files below.

Conclusion

As you can see from these examples, they are not too difficult to set up these reports, and could save your organisation loads of time by automating the them. If you are currently paying someone to produce your reports manually, then you really should take advantage of these tools.