Как я могу экспортировать DataTable в Excel в С#? Я использую Windows Forms. Элемент DataTable связан с элементом управления DataGridView. Мне нужно экспортировать записи DataTable в Excel.
Как экспортировать DataTable в Excel
Ответ 1
Я бы порекомендовал ClosedXML -
Вы можете превратить DataTable в рабочий лист Excel с некоторым очень читаемым кодом:
XLWorkbook wb = new XLWorkbook();
DataTable dt = GetDataTableOrWhatever();
wb.Worksheets.Add(dt,"WorksheetName");
Разработчик реагирует и помогает. Проект активно развивается, и документация превосходна.
Ответ 2
Попробуйте простой код, чтобы преобразовать DataTable в файл excel как csv:
var lines = new List<string>();
string[] columnNames = dataTable.Columns.Cast<DataColumn>().
Select(column => column.ColumnName).
ToArray();
var header = string.Join(",", columnNames);
lines.Add(header);
var valueLines = dataTable.AsEnumerable()
.Select(row => string.Join(",", row.ItemArray));
lines.AddRange(valueLines);
File.WriteAllLines("excel.csv",lines);
Это напишет новый файл excel.csv в "текущий рабочий каталог", который обычно находится либо там, где находится .exe, либо откуда вы его запускаете.
Обратите внимание, что вывод помещает запятые (",") между данными, уже содержащимися в dataTable. Так как он не пропускает запятые в данных, запятые в данных будут неправильно интерпретированы программой, читающей файл.
Ответ 3
Элегантный вариант заключается в написании метода расширения (см. ниже) для класса DataTable.net framework.
Этот способ расширения может быть вызван следующим образом:
using System;
using System.Collections.Generic;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;
DataTable dt;
// fill table data in dt here
...
// export DataTable to excel
// save excel file without ever making it visible if filepath is given
// don't save excel file, just make it visible if no filepath is given
dt.ExportToExcel(ExcelFilePath);
Метод расширения для класса DataTable:
public static class My_DataTable_Extensions
{
// Export DataTable into an excel file with field names in the header line
// - Save excel file without ever making it visible if filepath is given
// - Don't save excel file, just make it visible if no filepath is given
public static void ExportToExcel(this DataTable tbl, string excelFilePath = null) {
try {
if (tbl == null || tbl.Columns.Count == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");
// load excel, and create a new workbook
var excelApp = new Excel.Application();
excelApp.Workbooks.Add();
// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;
// column headings
for (var i = 0; i < tbl.Columns.Count; i++) {
workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;
}
// rows
for (var i = 0; i < tbl.Rows.Count; i++) {
// to do: format datetime values before printing
for (var j = 0; j < tbl.Columns.Count; j++) {
workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];
}
}
// check file path
if (!string.IsNullOrEmpty(excelFilePath)) {
try {
workSheet.SaveAs(excelFilePath);
excelApp.Quit();
MessageBox.Show("Excel file saved!");
}
catch (Exception ex) {
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
} else { // no file path is given
excelApp.Visible = true;
}
}
catch (Exception ex) {
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
}
Ответ 4
Решение основано на статье tuncalik (спасибо за идею), но в случае больших таблиц работает намного быстрее (и немного менее понятно).
public static class My_DataTable_Extensions
{
/// <summary>
/// Export DataTable to Excel file
/// </summary>
/// <param name="DataTable">Source DataTable</param>
/// <param name="ExcelFilePath">Path to result file name</param>
public static void ExportToExcel(this System.Data.DataTable DataTable, string ExcelFilePath = null)
{
try
{
int ColumnsCount;
if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");
// load excel, and create a new workbook
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbooks.Add();
// single worksheet
Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;
object[] Header = new object[ColumnsCount];
// column headings
for (int i = 0; i < ColumnsCount; i++)
Header[i] = DataTable.Columns[i].ColumnName;
Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
HeaderRange.Value = Header;
HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
HeaderRange.Font.Bold = true;
// DataCells
int RowsCount = DataTable.Rows.Count;
object[,] Cells = new object[RowsCount, ColumnsCount];
for (int j = 0; j < RowsCount; j++)
for (int i = 0; i < ColumnsCount; i++)
Cells[j, i] = DataTable.Rows[j][i];
Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;
// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
Worksheet.SaveAs(ExcelFilePath);
Excel.Quit();
System.Windows.MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
Excel.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
}
Ответ 5
Попробуйте эту функцию передать путь к данным и файлам, куда вы хотите экспортировать
public void CreateCSVFile(ref DataTable dt, string strFilePath)
{
try
{
// Create the CSV file to which grid data will be exported.
StreamWriter sw = new StreamWriter(strFilePath, false);
// First we will write the headers.
//DataTable dt = m_dsProducts.Tables[0];
int iColCount = dt.Columns.Count;
for (int i = 0; i < iColCount; i++)
{
sw.Write(dt.Columns[i]);
if (i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
// Now write all the rows.
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < iColCount; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
sw.Write(dr[i].ToString());
}
if (i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
}
sw.Close();
}
catch (Exception ex)
{
throw ex;
}
}
Ответ 6
Самый лучший и простой способ
private void exportToExcel(DataTable dt)
{
/*Set up work book, work sheets, and excel application*/
Microsoft.Office.Interop.Excel.Application oexcel = new Microsoft.Office.Interop.Excel.Application();
try
{
string path = AppDomain.CurrentDomain.BaseDirectory;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook obook = oexcel.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel.Worksheet osheet = new Microsoft.Office.Interop.Excel.Worksheet();
// obook.Worksheets.Add(misValue);
osheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Sheets["Sheet1"];
int colIndex = 0;
int rowIndex = 1;
foreach (DataColumn dc in dt.Columns)
{
colIndex++;
osheet.Cells[1, colIndex] = dc.ColumnName;
}
foreach (DataRow dr in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn dc in dt.Columns)
{
colIndex++;
osheet.Cells[rowIndex, colIndex] = dr[dc.ColumnName];
}
}
osheet.Columns.AutoFit();
string filepath = "C:\\Temp\\Book1";
//Release and terminate excel
obook.SaveAs(filepath);
obook.Close();
oexcel.Quit();
releaseObject(osheet);
releaseObject(obook);
releaseObject(oexcel);
GC.Collect();
}
catch (Exception ex)
{
oexcel.Quit();
log.AddToErrorLog(ex, this.Name);
}
}
Ответ 7
Вы можете использовать EasyXLS, который является библиотекой для экспорта файлов Excel.
Проверьте этот код:
DataSet ds = new DataSet();
ds.Tables.Add(dataTable);
ExcelDocument xls = new ExcelDocument();
xls.easy_WriteXLSFile_FromDataSet("datatable.xls", ds,
new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "DataTable");
См. также этот пример о том, как экспортировать datatable в excel на С#.
Ответ 8
ПРИМЕЧАНИЕ. Если вы пытаетесь передать значения из таблицы данных в объект, а затем в Excel, вы должны также выполнять обработку ошибок типа данных. Например, Гиды убьют ваше задание с помощью исключения HRESULT: 0x800A03EC. Одна работа без тестирования типов данных заключается в использовании "ToString()" при заполнении вашего объекта. Excel будет преобразовывать числа обратно в формат номера самостоятельно. FlashTrev как рассмотренный вопрос о дате/времени.
Ответ 9
В отношении ответа tuncalik, что отлично, особенно если вы хотите немного поиграть с кодом:), но он помещает мои даты в Excel в американском формате, то есть 2 марта 2014 года в Великобритания - 02/03/2014, но в США - 03/02/2014 с 1-м месяцем, затем днем недели. Мне нужно иметь его в британском формате, любые идеи, пожалуйста?
Я проверил, что он хранится в британском формате в моем DataTable, а также мой Excel установлен в Великобританию, но по какой-то причине, когда он делает документ Excel, он думает о своих США (это потому, что Microsoft - компания в США:)
Я попробую экспериментировать с кодами культуры, но не знаю, где это сделать. Пробовал, но это не имело никакого эффекта.
p.s.
Мне пришлось изменить одну строку, чтобы заставить ее работать, добавив "cast", как показано ниже
// single worksheet
Excel._Worksheet workSheet = (Excel._Worksheet)excelApp.ActiveSheet;
Обновление. Я добился британского форматирования дат, перейдя в формат LongDateTime, но это только работа.
DateTime startDate = Convert.ToDateTime(myList[0].ToString());
string strStartDate = startDate.ToLongDateString();
DateTime endDate = Convert.ToDateTime(myList[myListTotalRows].ToString());
string strEndDate = endDate.ToLongDateString();
приветствий.
Ответ 10
Старый поток - но думал, что я брошу свой код здесь. Я написал небольшую функцию для записи таблицы данных в новый лист excel по указанному пути (местоположению). Также вам нужно будет добавить ссылку на библиотеку microsoft excel 14.0.
Я вытащил из этой темы письмо о написании чего-нибудь, чтобы excel - Как написать некоторые данные в файл excel (.xlsx)
Я использовал это, чтобы экстраполировать, как писать datatable
* note в операциях catch. У меня есть ссылка на статический класс обработчика ошибок (вы можете игнорировать их)
using excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Data;
using System.Runtime.InteropServices;
//class and namespace wrapper is not shown in this example
private void WriteToExcel(System.Data.DataTable dt, string location)
{
//instantiate excel objects (application, workbook, worksheets)
excel.Application XlObj = new excel.Application();
XlObj.Visible = false;
excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add(""));
excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet;
//run through datatable and assign cells to values of datatable
try
{
int row = 1; int col = 1;
foreach (DataColumn column in dt.Columns)
{
//adding columns
WsObj.Cells[row, col] = column.ColumnName;
col++;
}
//reset column and row variables
col = 1;
row++;
for (int i = 0; i < dt.Rows.Count; i++)
{
//adding data
foreach (var cell in dt.Rows[i].ItemArray)
{
WsObj.Cells[row, col] = cell;
col++;
}
col = 1;
row++;
}
WbObj.SaveAs(location);
}
catch (COMException x)
{
ErrorHandler.Handle(x);
}
catch (Exception ex)
{
ErrorHandler.Handle(ex);
}
finally
{
WbObj.Close();
}
}
Ответ 11
Excel Interop:
Этот метод предотвращает переключение Дат с dd-mm-yyyy на мм-дд-гггг
public bool DataTableToExcelFile(DataTable dt, string targetFile)
{
const bool dontSave = false;
bool success = true;
//Exit if there is no rows to export
if (dt.Rows.Count == 0) return false;
object misValue = System.Reflection.Missing.Value;
List<int> dateColIndex = new List<int>();
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(misValue);
Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets("sheet1");
//Iterate through the DataTable and populate the Excel work sheet
try {
for (int i = -1; i <= dt.Rows.Count - 1; i++) {
for (int j = 0; j <= dt.Columns.Count - 1; j++) {
if (i < 0) {
//Take special care with Date columns
if (dt.Columns(j).DataType is typeof(DateTime)) {
excelWorkSheet.Cells(1, j + 1).EntireColumn.NumberFormat = "d-MMM-yyyy;@";
dateColIndex.Add(j);
}
//else if ... Feel free to add more Formats
else {
//Otherwise Format the column as text
excelWorkSheet.Cells(1, j + 1).EntireColumn.NumberFormat = "@";
}
excelWorkSheet.Cells(1, j + 1) = dt.Columns(j).Caption;
}
else if (dateColIndex.IndexOf(j) > -1) {
excelWorkSheet.Cells(i + 2, j + 1) = Convert.ToDateTime(dt.Rows(i).ItemArray(j)).ToString("d-MMM-yyyy");
}
else {
excelWorkSheet.Cells(i + 2, j + 1) = dt.Rows(i).ItemArray(j).ToString();
}
}
}
//Add Autofilters to the Excel work sheet
excelWorkSheet.Cells.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
//Autofit columns for neatness
excelWorkSheet.Columns.AutoFit();
if (File.Exists(exportFile)) File.Delete(exportFile);
excelWorkSheet.SaveAs(exportFile);
} catch {
success = false;
} finally {
//Do this irrespective of whether there was an exception or not.
excelWorkBook.Close(dontSave);
excelApp.Quit();
releaseObject(excelWorkSheet);
releaseObject(excelWorkBook);
releaseObject(excelApp);
}
return success;
}
Если вам не нравится, когда Даты перевернуты, используйте ссылку, которая показывает, как заполнить все ячейки в электронной таблице Excel в одной строке кода:
Excel Interop - эффективность и производительность
CSV:
public string DataTableToCSV(DataTable dt, bool includeHeader, string rowFilter, string sortFilter, bool useCommaDelimiter = false, bool truncateTimesFromDates = false)
{
dt.DefaultView.RowFilter = rowFilter;
dt.DefaultView.Sort = sortFilter;
DataView dv = dt.DefaultView;
string csv = DataTableToCSV(dv.ToTable, includeHeader, useCommaDelimiter, truncateTimesFromDates);
//reset the Filtering
dt.DefaultView.RowFilter = string.Empty;
return csv;
}
public string DataTableToCsv(DataTable dt, bool includeHeader, bool useCommaDelimiter = false, bool truncateTimesFromDates = false)
{
StringBuilder sb = new StringBuilder();
string delimter = Constants.vbTab;
if (useCommaDelimiter)
delimter = ",";
if (includeHeader) {
foreach (DataColumn dc in dt.Columns) {
sb.AppendFormat("{0}" + Constants.vbTab, dc.ColumnName);
}
//remove the last Tab
sb.Remove(sb.ToString.Length - 1, 1);
sb.Append(Environment.NewLine);
}
foreach (DataRow dr in dt.Rows) {
foreach (DataColumn dc in dt.Columns) {
if (Information.IsDate(dr(dc.ColumnName).ToString()) & dr(dc.ColumnName).ToString().Contains(".") == false & truncateTimesFromDates) {
sb.AppendFormat("{0}" + delimter, Convert.ToDateTime(dr(dc.ColumnName).ToString()).Date.ToShortDateString());
} else {
sb.AppendFormat("{0}" + delimter, CheckDBNull(dr(dc.ColumnName).ToString().Replace(",", "")));
}
}
//remove the last Tab
sb.Remove(sb.ToString.Length - 1, 1);
sb.Append(Environment.NewLine);
}
return sb.ToString;
}
public enum enumObjectType
{
StrType = 0,
IntType = 1,
DblType = 2
}
public object CheckDBNull(object obj, enumObjectType ObjectType = enumObjectType.StrType)
{
object objReturn = null;
objReturn = obj;
if (ObjectType == enumObjectType.StrType & Information.IsDBNull(obj)) {
objReturn = "";
} else if (ObjectType == enumObjectType.IntType & Information.IsDBNull(obj)) {
objReturn = 0;
} else if (ObjectType == enumObjectType.DblType & Information.IsDBNull(obj)) {
objReturn = 0.0;
}
return objReturn;
}
Ответ 12
Одним из способов сделать это будет также поставщик ACE OLEDB (см. также строки подключения для Excel). Конечно, вам нужно будет установить и зарегистрировать провайдера. Вы должны иметь это, если у вас установлен Excel, но это то, что вам нужно учитывать при развертывании приложения.
Это пример вызова вспомогательного метода из ExportHelper: ExportHelper.CreateXlsFromDataTable(myDataTable, @"C:\tmp\export.xls");
Помощник для экспорта в файл Excel с помощью ACE OLEDB:
public class ExportHelper
{
private const string ExcelOleDbConnectionStringTemplate = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\";";
/// <summary>
/// Creates the Excel file from items in DataTable and writes them to specified output file.
/// </summary>
public static void CreateXlsFromDataTable(DataTable dataTable, string fullFilePath)
{
string createTableWithHeaderScript = GenerateCreateTableCommand(dataTable);
using (var conn = new OleDbConnection(String.Format(ExcelOleDbConnectionStringTemplate, fullFilePath)))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
OleDbCommand cmd = new OleDbCommand(createTableWithHeaderScript, conn);
cmd.ExecuteNonQuery();
foreach (DataRow dataExportRow in dataTable.Rows)
{
AddNewRow(conn, dataExportRow);
}
}
}
private static void AddNewRow(OleDbConnection conn, DataRow dataRow)
{
string insertCmd = GenerateInsertRowCommand(dataRow);
using (OleDbCommand cmd = new OleDbCommand(insertCmd, conn))
{
AddParametersWithValue(cmd, dataRow);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Generates the insert row command.
/// </summary>
private static string GenerateInsertRowCommand(DataRow dataRow)
{
var stringBuilder = new StringBuilder();
var columns = dataRow.Table.Columns.Cast<DataColumn>().ToList();
var columnNamesCommaSeparated = string.Join(",", columns.Select(x => x.Caption));
var questionmarkCommaSeparated = string.Join(",", columns.Select(x => "?"));
stringBuilder.AppendFormat("INSERT INTO [{0}] (", dataRow.Table.TableName);
stringBuilder.Append(columnNamesCommaSeparated);
stringBuilder.Append(") VALUES(");
stringBuilder.Append(questionmarkCommaSeparated);
stringBuilder.Append(")");
return stringBuilder.ToString();
}
/// <summary>
/// Adds the parameters with value.
/// </summary>
private static void AddParametersWithValue(OleDbCommand cmd, DataRow dataRow)
{
var paramNumber = 1;
for (int i = 0; i <= dataRow.Table.Columns.Count - 1; i++)
{
if (!ReferenceEquals(dataRow.Table.Columns[i].DataType, typeof(int)) && !ReferenceEquals(dataRow.Table.Columns[i].DataType, typeof(decimal)))
{
cmd.Parameters.AddWithValue("@p" + paramNumber, dataRow[i].ToString().Replace("'", "''"));
}
else
{
object value = GetParameterValue(dataRow[i]);
OleDbParameter parameter = cmd.Parameters.AddWithValue("@p" + paramNumber, value);
if (value is decimal)
{
parameter.OleDbType = OleDbType.Currency;
}
}
paramNumber = paramNumber + 1;
}
}
/// <summary>
/// Gets the formatted value for the OleDbParameter.
/// </summary>
private static object GetParameterValue(object value)
{
if (value is string)
{
return value.ToString().Replace("'", "''");
}
return value;
}
private static string GenerateCreateTableCommand(DataTable tableDefination)
{
StringBuilder stringBuilder = new StringBuilder();
bool firstcol = true;
stringBuilder.AppendFormat("CREATE TABLE [{0}] (", tableDefination.TableName);
foreach (DataColumn tableColumn in tableDefination.Columns)
{
if (!firstcol)
{
stringBuilder.Append(", ");
}
firstcol = false;
string columnDataType = "CHAR(255)";
switch (tableColumn.DataType.Name)
{
case "String":
columnDataType = "CHAR(255)";
break;
case "Int32":
columnDataType = "INTEGER";
break;
case "Decimal":
// Use currency instead of decimal because of bug described at
// http://social.msdn.microsoft.com/Forums/vstudio/en-US/5d6248a5-ef00-4f46-be9d-853207656bcc/localization-trouble-with-oledbparameter-and-decimal?forum=csharpgeneral
columnDataType = "CURRENCY";
break;
}
stringBuilder.AppendFormat("{0} {1}", tableColumn.ColumnName, columnDataType);
}
stringBuilder.Append(")");
return stringBuilder.ToString();
}
}
Ответ 13
используйте следующий класс
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using excel = Microsoft.Office.Interop.Excel;
using EL = ExcelLibrary.SpreadSheet;
using System.Drawing;
using System.Collections;
using System.Runtime.InteropServices;
using System.Windows.Forms;
namespace _basic
{
public class ExcelProcesser
{
public void WriteToExcel(System.Data.DataTable dt)
{
excel.Application XlObj = new excel.Application();
XlObj.Visible = false;
excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add(""));
excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet;
object misValue = System.Reflection.Missing.Value;
try
{
int row = 1; int col = 1;
foreach (DataColumn column in dt.Columns)
{
//adding columns
WsObj.Cells[row, col] = column.ColumnName;
col++;
}
//reset column and row variables
col = 1;
row++;
for (int i = 0; i < dt.Rows.Count; i++)
{
//adding data
foreach (var cell in dt.Rows[i].ItemArray)
{
WsObj.Cells[row, col] = cell;
col++;
}
col = 1;
row++;
}
WbObj.SaveAs(fileFullName, excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
WbObj.Close(true, misValue, misValue);
}
}
}
}