How to export DataTable to Excel

问题:

How can I export a DataTable to Excel in C#? I am using Windows Forms. The DataTable is associated with a DataGridView control. I have to export records of DataTable to Excel.

回答1:

I would recommend ClosedXML -

You can turn a DataTable into an Excel worksheet with some very readable code:

XLWorkbook wb = new XLWorkbook();
DataTable dt = GetDataTableOrWhatever();
wb.Worksheets.Add(dt,\"WorksheetName\");

The developer is responsive and helpful. The project is actively developed, and the documentation is superb.

回答2:

Try simple code, to convert DataTable to excel file as 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);

This will write a new file excel.csv into the \"current working directory\" which is generally either where the .exe is or where you launch it from.

Note that the output puts commas (\",\") between the data already contained in the dataTable. Since it does not escape commas in the data, commas in the data will be mis-interpreted by the program reading the file.

回答3:

An elegant option is writing an extension method (see below) for the DataTable class of .net framework.

This extention method can be called as follows:

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

Extension method for DataTable class:

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:

Solution based on tuncalik (thanks for idea) article, but in case of big tables is working much more faster (and is a little less clear).

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:

Try this function pass the datatable and file path where you want to export

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:

The best and easiest way

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:

You can use EasyXLS that is a library for exporting Excel files.

Check this code:

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\");

See also this sample about how to export datatable to excel in C#.

回答8:

NOTE: If you\'re trying to pass values from a data table to an object then to Excel, you should be doing datatype error handling as well. For example, Guids will kill your assignment with an HRESULT: 0x800A03EC exception. One work around without testing for datatypes is to use \"ToString()\" when populating your object. Excel will convert numbers back to number format on its own. FlashTrev as addressed the related issue of date/times.

回答9:

In regards tuncalik\'s answer, which is great, especially if you want to have a little play with the code :) but it is putting my dates into Excel in American format i.e. 2nd March 2014 in the UK is 02/03/2014 but in the USA its 03/02/2014 with month 1st, then day of week after. I need to have it in UK format, any ideas please?

I have checked it is stored in UK format in my DataTable and also my Excel is set to UK but for some reason when it makes the Excel document it thinks its USA (is this because Microsoft are a USA company :)

I\'ll try experimenting with culture codes but not sure where to put that yet. Tried but this had no effect.

p.s.

I did have to change one line to get it to work by adding a \'cast\' as below

// single worksheet
Excel._Worksheet workSheet = (Excel._Worksheet)excelApp.ActiveSheet;

Update: I have achieved UK formatting of the dates by converting to LongDateTime format, its only a work around though.

DateTime startDate = Convert.ToDateTime(myList[0].ToString());
string strStartDate = startDate.ToLongDateString();
DateTime endDate = Convert.ToDateTime(myList[myListTotalRows].ToString());
string strEndDate = endDate.ToLongDateString();    

cheers.

回答10:

Excel Interop:

This method prevents Dates getting flipped from dd-mm-yyyy to 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;
}

If you dont care about Dates being flipped, then use see link that shows how to populate all the cells in the Excel spreadsheet in one line of code:

Excel Interop - Efficiency and performance

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

回答11:

Old thread - but thought i would throw my code in here. I wrote a little function to write a data table to a new excel sheet at a specified path (location). Also you will need to add a reference to microsoft excel 14.0 library.

I pulled from this thread on writing anything to excel - How to write some data to excel file(.xlsx)

i used that to extrapolate how to write a datatable

*note in catch statements i have an errorhandler static class reference (you can ignore those)

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

回答12:

One way of doing it would be also with ACE OLEDB Provider (see also connection strings for Excel). Of course you\'d have to have the provider installed and registered. You should have it, if you have Excel installed, but this is something you have to consider when deploying the app.

This is the example of calling the helper method from ExportHelper: ExportHelper.CreateXlsFromDataTable(myDataTable, @\"C:\\tmp\\export.xls\");

The helper for exporting to Excel file using 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:

use the following class

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

}

回答14:

This solution is basically pushing List<Object> data to Excel, It uses DataTable to achieve this, I implemented an extension method, so basically there are two things needed. 1. An Extension Method.

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

1. Decorate your DTO classes with the Attribute [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\";
            }
        }}

Everything that needs to be a column in the Excel has to be decorated with [ReportHeaderAttribute(\"Column Name\")]

Then Simply

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;

回答15:

To export data to Excel, you can use the ClosedXML.Report library (https://github.com/ClosedXML/ClosedXML.Report). Believe me, this is a wonderful library and easy for her to use. The library does not need Excel Interop. ClosedXML.Report generates an Excel file based on a template that you can create in Excel using any formatting. For example:

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

回答16:

Just Make use of the CloseMXL.Excel Library. It\'s easy and pretty fast too.

Class

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

Then another method which get the Dataset

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

Now you Button Click Event

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

回答17:

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