DataTable을 Excel로 내보내는 방법 #에서를 Excel로 내보내려면 어떻게 해야합니까? Windows Forms를

DataTableC #에서를 Excel로 내보내려면 어떻게 해야합니까? Windows Forms를 사용하고 있습니다. 는 DataTable와 관련된 DataGridView제어. 레코드 DataTable를 Excel 로 내 보내야합니다.

답변

ClosedXML 을 권장 합니다. –

매우 읽기 쉬운 코드를 사용하여 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);

이렇게하면 일반적으로 .exe가 excel.csv있는 현재 작업 디렉토리에 새 파일이 작성 됩니다.

답변

우아한 옵션은 .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 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 스프레드 시트의 모든 셀을 한 줄의 코드로 채우는 방법을 보여주는 링크보기를 사용하십시오.

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;
}