DataTable to excel conversion

Question

I have a datagrid which populates a datatable, I want to export the DataTable to excel on click of a button. I am using MVVM for this application. So is it ok to implement the export feature in my view?

Secondly, as i am using xaml and desktop application, how do i capture the grid and its details.

Can any one suggest me some pointers? I am new to this.

Thanks, Sagar

Look at [ClosedXML](http://closedxml.codeplex.com/) as a 3rd party library to help clean up some of the code neccessary — KyleMit, Jun 19 '13 at 20:14
@KyleMit ClosedXML is a ASP.net based, i need this feature in an application which is using xaml controls with mvvm architecture. — voonna, Jun 20 '13 at 15:17

score 1 · Accepted Answer · answered Jun 21 '13 at 19:12

This is what i have done and it helped me:

private readonly ICommand m_ExportButtonClick;
    private string ExcelFilePath = "";

    public ICommand ExportButtonClick { get { return m_ExportButtonClick; } }

    private void OnRunExport()
    {
        try
        {
            if (queryDatatable == null || queryDatatable.Columns.Count == 0)
                throw new Exception("ExportToExcel: Null or empty input table!\n");

            // load excel, and create a new workbook
            Excell.Application excelApp = new Excell.Application();
            excelApp.Workbooks.Add();

            // single worksheet
            Excell._Worksheet workSheet = excelApp.ActiveSheet;

            // column headings
            for (int i = 0; i < queryDatatable.Columns.Count; i++)
            {
                workSheet.Cells[1, (i + 1)] = queryDatatable.Columns[i].ColumnName;
            }

            // rows
            for (int i = 0; i < queryDatatable.Rows.Count; i++)
            {
                // to do: format datetime values before printing
                for (int j = 0; j < queryDatatable.Columns.Count; j++)
                {
                    workSheet.Cells[(i + 2), (j + 1)] = queryDatatable.Rows[i][j];
                }
            }

            // check fielpath
            if (ExcelFilePath != null && 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 filepath is given, the file opens up and the user can save it accordingly.
            {
                excelApp.Visible = true;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("There is no data to export. Please check your query/ Contact administrator." + ex.Message);
        }
    }      
    #endregion

score 0 · Answer 2 · answered Jun 19 '13 at 20:05

Here's an extension method to output to any DataTable to a csv (which excel can open)

Imports System.Text
Imports System.IO
Imports System.Runtime.CompilerServices

Module ExtensionMethods

    <Extension()> _
    Public Sub OutputAsCSV(ByVal dt As DataTable, ByVal filePath As String)
        Dim sb As New StringBuilder()

                'write column names to string builder
        Dim columnNames As String() = (From col As DataColumn In dt.Columns Select col.ColumnName).ToArray
        sb.AppendLine(String.Join(",", columnNames))

                'write cell value in each row to string builder
        For Each row As DataRow In dt.Rows
            Dim fields As String() = (From cell In row.ItemArray Select CStr(cell)).ToArray
            sb.AppendLine(String.Join(",", fields))
        Next

                'write string builder to file
        File.WriteAllText(filePath, sb.ToString())
    End Sub

End Module

score 0 · Answer 3 · edited May 23 '17 at 12:15

Try googling around a little. This is a very common problem and has been asked a lot already. Here's a good answer from this SO question

public static void ExportToExcel<T>(IEnumerable<T> exportData)
{
    Excel.ApplicationClass excel = new Excel.ApplicationClass();
    Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
    PropertyInfo[] pInfos = typeof(T).GetProperties();
    if (pInfos != null && pInfos.Count() > 0)
    {
        int iCol = 0;
        int iRow = 0;
        foreach (PropertyInfo eachPInfo in pInfos.Where(W => W.CanRead == true))
        {
            // Add column headings...
            iCol++;
            excel.Cells[1, iCol] = eachPInfo.Name;
        }
        foreach (T item in exportData)
        {
            iRow++;
            // add each row's cell data...
            iCol = 0;
            foreach (PropertyInfo eachPInfo in pInfos.Where(W => W.CanRead == true))
            {
                iCol++;
                excel.Cells[iRow + 1, iCol] = eachPInfo.GetValue(item, null);
            }

        }
        // Global missing reference for objects we are not defining...
        object missing = System.Reflection.Missing.Value;
        // If wanting to Save the workbook...   
        string filePath = System.IO.Path.GetTempPath() + DateTime.Now.Ticks.ToString() + ".xlsm";
        workbook.SaveAs(filePath, Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled, missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
        // If wanting to make Excel visible and activate the worksheet...
        excel.Visible = true;
        Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
        excel.Rows.EntireRow.AutoFit();
        excel.Columns.EntireColumn.AutoFit();
        ((Excel._Worksheet)worksheet).Activate();
    }
}

thanks, I am using .Net framework 4.0, and i think ApplicationClass is not supported by 4.0. When i try to refactor, it is not letting me do it. — voonna, Jun 19 '13 at 21:29

DataTable to excel conversion

3 Answers3