Simplest way to convert some
large Datasets to Excel files is using OpenXML and Closed XML, this can be used
for console application as well as Web applications. To create the Excel files
(.XLSX) we need to Add the Open XML and Closed XML reference from nugget packages
as below.
OPENXML |
Closed XML |
Here am
converting Dataset into Excel file, if data set contains multiple DataTables,
it can be looped into same Excel with different sheet. Below is the sample code
to connect Generate Excel(.xlsx) and save in some application path.
To use the
OpenXML and Closed XML we need to use below namespaces
using
ClosedXML.Excel
using
DocumentFormat.OpenXml.Spreadsheet
Sample Code :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ClosedXML.Excel;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;
namespace MyAPP3
{
class Program
{
static void Main(string[] args)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Country");
dt.Rows.Add("Venkatesh", "India");
dt.Rows.Add("Santhosh", "USA");
dt.Rows.Add("Venkat Sai", "Dubai");
dt.Rows.Add("Venkat Teja", "Pakistan");
ds.Tables.Add(dt);
ExportDataSetToExcel(ds);
}
public static void ExportDataSetToExcel(DataSet ds)
{
string AppLocation = "";
AppLocation = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
AppLocation = AppLocation.Replace("file:\\", "");
string date = DateTime.Now.ToShortDateString();
date = date.Replace("/", "_");
string filepath = AppLocation + "\\ExcelFiles\\" + "RECEIPTS_COMPARISON_" + date + ".xlsx";
using (XLWorkbook wb = new XLWorkbook())
{
for (int i = 0; i < ds.Tables.Count; i++)
{
wb.Worksheets.Add(ds.Tables[i], ds.Tables[i].TableName);
}
wb.Style.Alignment.Horizontal =
XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
wb.SaveAs(filepath);
}
}
}
}
12 Comments
ZetExcel aso is very helpful
ReplyDeleteJeremy Gore
Really? I will quickly check ZetExcel very soon
ReplyDeletePeter Ferguson
Yes its working fine.......
ReplyDeleteVery helpful, thank you.
ReplyDeleteThanks for Distribution a Nice Post about Excel and I must Say it’s actually helpful for us. We Learn So Many Things through Your Post So Please Keep Going & Never Stop Your Writing
ReplyDeleteGreat post! This is very useful for me and gain more information, Thanks for sharing with us.
ReplyDeletewblogin
Article submission sites
How can we Bind Data into Excel using ClosedXML without Header ?
ReplyDeletehow can we open the excel file instead of downloading????
ReplyDeleteWonderful article, regarding this issue I can suggest you to use zetexcel.com.They develop high performance applications to Create, Edit, Convert or Print Excel spreadsheet file formats without requiring Microsoft Excel.
ReplyDeletethanks a lot this helped me i was stuck actually. i just followed the code excel file generated after the changes according to this.
ReplyDeletehow to remove the autofilters
ReplyDeleteAutofilter= false
not working in console application please help
IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Projects for CSE It gives you tips and rules that is progressively critical to consider while choosing any final year project point.
ReplyDeleteJavaScript Online Training in India
JavaScript Training in India
The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training