Export DataTable to Excel with EPPlus
using (ExcelPackage pck = new ExcelPackage(newFile))
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
pck.Save();
}
That should do the trick for you. If your fields are defined as int EPPlus will properly cast the columns into a number or float.
Export data table to Excel sheet
Since you haven't mentioned if excel is installed, i recommend EPPlus
to create the excel file. It has a convenient method LoadFromDataTable
:
using (var pck = new ExcelPackage())
{
var ws = pck.Workbook.Worksheets.Add("Worksheet-Name");
ws.Cells["A1"].LoadFromDataTable(dataTable1, true, OfficeOpenXml.Table.TableStyles.Medium1);
using(var fileStream = File.Create(path))
pck.SaveAs(fileStream);
}
Edit i've only just seen that you have tagged export-to-csv.
var lines = dataTable1.AsEnumerable()
.Select(r => string.Join(",", r.ItemArray));
string csv = string.Join(Environment.NewLine, lines);
File.AppendAllText(path, csv);
Excel to DataTable using EPPlus - excel locked for editing
I see, that's what i've posted recently here(now corrected). It can be improved since the ExcelPackage
and the FileStream
(from File.OpenRead
) are not disposed after using.
public static DataTable GetDataTableFromExcel(string path, bool hasHeader = true)
{
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(path))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets.First();
DataTable tbl = new DataTable();
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
var startRow = hasHeader ? 2 : 1;
for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
DataRow row = tbl.Rows.Add();
foreach (var cell in wsRow)
{
row[cell.Start.Column - 1] = cell.Text;
}
}
return tbl;
}
}
Related Topics
Reflection to Identify Extension Methods
Richtextbox Syntax Highlighting in Real Time--Disabling the Repaint
Http Error 500.30 - Ancm In-Process Start Failure
How to Ignore JSONproperty(Propertyname = "Somename") When Serializing JSON
How to Read the Color of a Screen Pixel
Changing the User Agent of the Webbrowser Control
Why Should You Remove Unnecessary C# Using Directives
Unloading the Assembly Loaded with Assembly.Loadfrom()
Why Does This Floating-Point Calculation Give Different Results on Different MAChines
Reading PDF Content with Itextsharp Dll in Vb.Net or C#
In Unity (C#), Why am I Getting a Nullreferenceexception and How to Fix It
How to Run an Exe Program from a Windows Service Using C#
Random Number Generator with No Duplicates
What Does Null! Statement Mean
Asynchronous Locking Based on a Key