DataTable을 Excel로 내보내는 방법
DataTable
C #에서를 Excel로 내보내려면 어떻게 해야합니까? Windows Forms를 사용하고 있습니다. 는 DataTable
와 관련된 DataGridView
제어. 레코드 DataTable
를 Excel 로 내 보내야합니다.
매우 읽기 쉬운 코드를 사용하여 DataTable을 Excel 워크 시트로 변환 할 수 있습니다.
XLWorkbook wb = new XLWorkbook();
DataTable dt = GetDataTableOrWhatever();
wb.Worksheets.Add(dt,"WorksheetName");
개발자는 반응이 빠르고 도움이됩니다. 이 프로젝트는 적극적으로 개발되었으며 문서는 훌륭합니다.
DataTable을 csv로 Excel 파일로 변환하려면 간단한 코드를 시도하십시오.
var lines = new List<string>();
string[] columnNames = dataTable.Columns
.Cast<DataColumn>()
.Select(column => column.ColumnName)
.ToArray();
var header = string.Join(",", columnNames.Select(name => $"\"{name}\""));
lines.Add(header);
var valueLines = dataTable.AsEnumerable()
.Select(row => string.Join(",", row.ItemArray.Select(val => $"\"{val}\"")));
lines.AddRange(valueLines);
File.WriteAllLines("excel.csv", lines);
이렇게하면 excel.csv
일반적으로 .exe가 있거나 파일을 시작하는 "현재 작업 디렉터리"에 새 파일이 작성 됩니다.
우아한 옵션은 .net 프레임 워크의 DataTable 클래스에 대한 확장 메서드 (아래 참조)를 작성하는 것입니다.
이 확장 메서드는 다음과 같이 호출 할 수 있습니다.
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);
}
}
}
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);
}
}
}
이 기능을 사용하여 내보낼 데이터 테이블 및 파일 경로를 전달하십시오.
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;
}
}
가장 쉽고 쉬운 방법
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);
}
}
Excel 파일 내보내기 용 라이브러리 인 EasyXLS 를 사용할 수 있습니다 .
이 코드를 확인하십시오.
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");
C #에서 Excel로 데이터 테이블을 내보내는 방법에 대한이 샘플도 참조하세요 .
그냥 사용합니다 CloseMXL.Excel 라이브러리. 쉽고 매우 빠릅니다.
수업
private DataTable getAllList()
{
string constr = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT EmpId, gender, EmpName, pOnHold FROM Employee WHERE EmpId= '"+ AnyVariable + "' ORDER BY EmpName"))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
DataTable dt = new DataTable();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);
dt.Columns[0].ColumnName = "Employee Id";
dt.Columns[1].ColumnName = "Gender";
dt.Columns[2].ColumnName = "Employee Name";
dt.Columns[3].ColumnName = "On Hold";
return dt;
}
}
}
}
그런 다음 데이터 세트를 가져 오는 또 다른 방법
public DataSet getDataSetExportToExcel()
{
DataSet ds = new DataSet();
DataTable dtEmp = new DataTable("CLOT List");
dtEmp = getAllList();
ds.Tables.Add(dtEmp);
ds.Tables[0].TableName = "Employee"; //If you which to use Mutliple Tabs
return ds;
}
이제 버튼 클릭 이벤트
protected void btn_Export_Click(object sender, EventArgs e)
{
DataSet ds = getDataSetExportToExcel();
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(ds);
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=EmployeeonHoldList.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
tuncalik 의 대답 과 관련하여 특히 코드를 약간 사용하고 싶다면 :) 그러나 내 날짜를 미국 형식으로 Excel에 넣습니다. 즉 영국에서 2014 년 3 월 2 일은 2014 년 2 월 3 일이지만 미국에서는 2014 년 3 월 2 일에 1 월 1 일, 다음 요일이 있습니다. 영국 형식으로해야합니다. 아이디어가 있으십니까?
내 DataTable에 영국 형식으로 저장되어 있고 Excel이 UK로 설정되어 있는지 확인했지만 Excel 문서를 만들 때 어떤 이유로 미국 회사라고 생각합니다 (Microsoft는 미국 회사이기 때문에 :)
나는 문화 코드를 실험 해보 겠지만 아직 어디에 넣을지 모르겠다. 시도했지만 효과가 없습니다.
추신
다음과 같이 '캐스트'를 추가하여 작동하도록 한 줄을 변경해야했습니다.
// 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();
건배.
Excel Interop :
이 방법은 날짜가 dd-mm-yyyy에서 mm-dd-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 스프레드 시트의 모든 셀을 한 줄의 코드로 채우는 방법을 보여주는 링크보기를 사용하십시오.
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;
}
오래된 스레드-그러나 여기에 내 코드를 던질 것이라고 생각했습니다. 지정된 경로 (위치)에서 새 엑셀 시트에 데이터 테이블을 쓰는 작은 함수를 작성했습니다. 또한 Microsoft Excel 14.0 라이브러리에 대한 참조를 추가해야합니다.
이 스레드에서 엑셀 쓰기에 대해 가져 왔습니다 -Excel 파일 (.xlsx)에 데이터를 쓰는 방법
나는 그것을 사용하여 데이터 테이블을 작성하는 방법을 추정했습니다.
* 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();
}
}
이를 수행하는 한 가지 방법은 ACE OLEDB 공급자 를 사용하는 것입니다 ( Excel 용 연결 문자열 참조 ). 물론 공급자를 설치하고 등록해야합니다. Excel이 설치되어 있으면 있어야하지만 앱을 배포 할 때 고려해야 할 사항입니다.
다음은에서 도우미 메서드를 호출하는 예입니다 ExportHelper
.ExportHelper.CreateXlsFromDataTable(myDataTable, @"C:\tmp\export.xls");
ACE OLEDB를 사용하여 Excel 파일로 내보내기위한 도우미 :
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();
}
}
다음 수업을 사용하십시오
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);
}
}
}
}
이 솔루션은 기본적으로
List<Object>
데이터를 Excel로 푸시하고 DataTable을 사용하여이를 달성하고 확장 방법을 구현했기 때문에 기본적으로 두 가지가 필요합니다. 1. 확장 방법.
public static class ReportHelper
{
public static string ToExcel<T>(this IList<T> data)
{
PropertyDescriptorCollection properties =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
{
//table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
if (prop.Attributes[typeof(FGMS.Entity.Extensions.ReportHeaderAttribute)] != null)
{
table.Columns.Add(GetColumnHeader(prop), Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
}
//So it seems like when there is only one row of data the headers do not appear
//so adding a dummy blank row which fixed the issues
//Add a blank Row - Issue # 1471
DataRow blankRow = table.NewRow();
table.Rows.Add(blankRow);
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
//row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
if (prop.Attributes[typeof(FGMS.Entity.Extensions.ReportHeaderAttribute)] != null)
{
row[GetColumnHeader(prop)] = prop.GetValue(item) ?? DBNull.Value;
}
table.Rows.Add(row);
}
table.TableName = "Results";
var filePath = System.IO.Path.GetTempPath() + "\\" + System.Guid.NewGuid().ToString() + ".xls";
table.WriteXml(filePath);
return filePath;
}
private static string GetColumnHeader(PropertyDescriptor prop)
{
return ((FGMS.Entity.Extensions.ReportHeaderAttribute)(prop.Attributes[typeof(FGMS.Entity.Extensions.ReportHeaderAttribute)])).ReportHeaderText;
}
}
- 속성을 사용하여 DTO 클래스 장식
[ReportHeaderAttribute("Column Name")]
public class UserDTO
{
public int Id { get; set; }
public int SourceId { get; set; }
public string SourceName { get; set; }
[ReportHeaderAttribute("User Type")]
public string UsereType { get; set; }
[ReportHeaderAttribute("Address")]
public string Address{ get; set; }
[ReportHeaderAttribute("Age")]
public int Age{ get; set; }
public bool IsActive { get; set; }
[ReportHeaderAttribute("Active")]
public string IsActiveString
{
get
{
return IsActive ? "Yes" : "No";
}
}}
Excel에서 열이되어야하는 모든 것은 [ReportHeaderAttribute("Column Name")]
그런 다음 간단히
Var userList = Service.GetUsers() //Returns List of UserDTO;
var excelFilePath = userList.ToExcel();
HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
var stream = new FileStream(excelFilePath, FileMode.Open);
result.Content = new StreamContent(stream);
result.Content.Headers.ContentType =
new MediaTypeHeaderValue("application/vnd.ms-excel");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "UserList.xls" };
return result;
Excel로 데이터를 내보내려면 ClosedXML.Report 라이브러리 ( https://github.com/ClosedXML/ClosedXML.Report )를 사용할 수 있습니다 . 저를 믿으십시오. 이것은 훌륭한 도서관이며 그녀가 사용하기 쉽습니다. 라이브러리에는 Excel Interop이 필요하지 않습니다. ClosedXML.Report는 서식을 사용하여 Excel에서 만들 수있는 템플릿을 기반으로 Excel 파일을 생성합니다. 예를 들면 :
var template = new XLTemplate(@".\Templates\report.xlsx");
using (var db = new DbDemos())
{
var cust = db.customers.LoadWith(c => c.Orders).First();
template.AddVariable(cust);
template.Generate();
}
template.SaveAs(outputFile);
Private tmr As System.Windows.Forms.Timer
Private Sub TestExcel() Handles Button1.Click
'// Initial data: SQL Server table with 6 columns and 293000 rows.
'// Data table holding all data
Dim dt As New DataTable("F161")
'// Create connection
Dim conn As New SqlConnection("Server=MYSERVER;Database=Test;Trusted_Connection=Yes;")
Dim fAdapter As New SqlDataAdapter With
{
.SelectCommand = New SqlCommand($"SELECT * FROM dbo.MyTable", conn)
}
'// Fill DataTable
fAdapter.Fill(dt)
'// Create Excel application
Dim xlApp As New Excel.Application With {.Visible = True}
'// Temporarily disable screen updating
xlApp.ScreenUpdating = False
'// Create brand new workbook
Dim xlBook As Excel.Workbook = xlApp.Workbooks.Add()
Dim xlSheet As Excel.Worksheet = DirectCast(xlBook.Sheets(1), Excel.Worksheet)
'// Get number of rows
Dim rows_count = dt.Rows.Count
'// Get number of columns
Dim cols_count = dt.Columns.Count
'// Here 's the core idea: after receiving data
'// you need to create an array and transfer it to sheet.
'// Why array?
'// Because it's the fastest way to transfer data to Excel's sheet.
'// So, we have two tasks:
'// 1) Create array
'// 2) Transfer array to sheet
'// =========================================================
'// TASK 1: Create array
'// =========================================================
'// In order to create array, we need to know that
'// Excel's Range object expects 2-D array whose lower bounds
'// of both dimensions start from 1.
'// This means you can't use C# array.
'// You need to manually create such array.
'// Since we already calculated number of rows and columns,
'// we can use these numbers in creating array.
Dim arr = Array.CreateInstance(GetType(Object), {rows_count, cols_count}, {1, 1})
'// Fill array
For r = 0 To rows_count - 1
For c = 0 To cols_count - 1
arr(r + 1, c + 1) = dt.Rows(r)(c)
Next
Next
'// =========================================================
'// TASK 2: Transfer array to sheet
'// =========================================================
'// Now we need to transfer array to sheet.
'// So, how transfer array to sheet fast?
'//
'// THE FASTEST WAY TO TRANSFER DATA TO SHEET IS TO ASSIGN ARRAY TO RANGE.
'// We could, of course, hard-code values, but Resize property
'// makes this work a breeze:
xlSheet.Range("A1").Resize.Resize(rows_count, cols_count).Value = arr
'// If we decide to dump data by iterating over array,
'// it will take LOTS of time.
'// For r = 1 To rows_count
'// For c = 1 To cols_count
'// xlSheet.Cells(r, c) = arr(r, c)
'// Next
'// Next
'// Here are time results:
'// 1) Assigning array to Range: 3 seconds
'// 2) Iterating over array: 45 minutes
'// Turn updating on
xlApp.ScreenUpdating = True
xlApp = Nothing
xlBook = Nothing
xlSheet = Nothing
'// Here we have another problem:
'// creating array took lots of memory (about 150 MB).
'// Using 'GC.Collect()', by unknown reason, doesn't help here.
'// However, if you run GC.Collect() AFTER this procedure is finished
'// (say, by pressing another button and calling another procedure),
'// then the memory is cleaned up.
'// I was wondering how to avoid creating some extra button to just release memory,
'// so I came up with the idea to use timer to call GC.
'// After 2 seconds GC collects all generations.
'// Do not forget to dispose timer since we need it only once.
tmr = New Timer()
AddHandler tmr.Tick,
Sub()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.WaitForFullGCComplete()
tmr.Dispose()
End Sub
tmr.Interval = TimeSpan.FromSeconds(2).TotalMilliseconds()
tmr.Start()
End Sub
참고 URL : https://stackoverflow.com/questions/8207869/how-to-export-datatable-to-excel
'Programing' 카테고리의 다른 글
투명한 배경이있는 Swift Modal View Controller (0) | 2020.08.24 |
---|---|
.gitignore는 특정 디렉토리가 아닌 디렉토리의 파일을 제외합니다. (0) | 2020.08.24 |
하위 배열 값으로 PHP 정렬 배열 (0) | 2020.08.24 |
페이지의 모든 AJAX 요청에 "후크"추가 (0) | 2020.08.24 |
프로그래밍 방식으로 식별 열이있는 SQL 테이블을 확인하는 방법 (0) | 2020.08.24 |