Alternative to MS Interop to Export DataTable to Excel
This article helps you build an Excel file in C# without using MS OFfice Excel Library
Ah no, I just built a whole code to export the data into Excel but the client does not have MS Office on their computer.
Without wasting your time let me share with you why I am writing this article for you. There are scenarios when you will use this block of code given below.
- You wrote a block of code in C# WinForms or WPF to Export the data into Excel, but the client does not have MS Office installed on their computer.
- You wrote the same using MS Interop but the client machine has a different MS OFfice version
- Client Computer does not have InterOP installed
- You want to use OpenSource Library to export to Excel
I had almost all the challenges in different cases during my 15 years of experience with C#
Mostly I use ClosedXML library which is OpenSource LIbrary. I have always had the best experience playing around with large excel files for better performance and easy implementation
ClosedXML in Nuget
Just easy to implement using nuget package
PM> Install-Package ClosedXML
Here is a simple block of code to get it done easily.
using (var workbook = new XLWorkbook())
{
var worksheet = workbook.Worksheets.Add("Sample Sheet");
worksheet.Cell("A1").Value = "Hello World!";
worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)";
workbook.SaveAs("HelloWorld.xlsx");
}
Convert DataTable to Excel Sheet using ClosedXML
I wanted to give you a just a ready method that will help you create an Excel File from the DataTable without Looping through the rows.
public static void ConverTableToExcel(DataTable tblObject, string SheetName, string FilePath, bool OpenAfterSave)
{
try
{
string PathToSave = FilePath;
if (PathToSave.Trim().Length == 0)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel Worksheets|*.xlsx";
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
PathToSave = sfd.FileName;
}
else
{
return;
}
}
if (PathToSave.Length > 0)
{
XLWorkbook workBook = new XLWorkbook();
if (File.Exists(FilePath))
{
workBook = new XLWorkbook(FilePath);
}
IXLWorksheet ws = workBook.Worksheets.Add(tblObject, SheetName.Length > 0 ? SheetName : "Sheet1");
ws.Unprotect();
Application.DoEvents();
workBook.SaveAs(PathToSave);
if (OpenAfterSave)
{
var p = new Process();
p.StartInfo = new ProcessStartInfo(PathToSave)
{
UseShellExecute = true
};
p.Start();
}
else
{
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
How to Export a DataTable into Excel in One Line?
ConverTableToExcel(tblConvertedForMatirixify, "Products", TempFilePath, false);
Bingo that's simple, easy, and a time saver. That's why I have kept in my code library which is used in all my Data Analysis tools development.
Soon I will share a similar thing to Export DataTable to CSV
Thanks.
Did you find this article valuable?
Support Ishrar G by becoming a sponsor. Any amount is appreciated!