Convert datatable to excel 2007(.xlsx)

Question

Hi,

I have an DataTable I need to put into Excel 2007 format and save it as an excel file(.xlsx) 2007.

Can anyone help me to achieve this?

Answer 1

Found this in some old code I did like 5 years ago that should work...

public static void DataTableToExcel(DataTable tbl)
{
    HttpContext context = HttpContext.Current;
    context.Response.Clear();
    foreach (DataColumn c in tbl.Columns)
    {
        context.Response.Write(c.ColumnName + ";");
    }
    context.Response.Write(Environment.NewLine);
    foreach (DataRow r in tbl.Rows)
    {
        for (int i = 0; i < tbl.Columns.Count; i++)
        {
            context.Response.Write(r[i].ToString().Replace(";", string.Empty) + ";");
        }
        context.Response.Write(Environment.NewLine);
    }
    context.Response.ContentType = "text/csv";
    context.Response.AppendHeader("Content-Disposition",
        "attachment; filename=export.csv");
    context.Response.End();
}

This will output from ASP.NET a response with a CSV file that Excel 2007 can open. If you want you can change the extension to mimic excel and it should work just by replacing the following lines:

    context.Response.ContentType = "application/vnd.ms-excel";
    context.Response.AppendHeader("Content-Disposition",
        "attachment; filename=export.xlsx");

A CSV is the easiest way if you don't need to do anything complex. If you do require it to truly be a Excel 2007 file in the native format, you will need to use an Office library to build it or convert it from the CSV and then serve/save it.

This link might also be useful:

How to avoid the Excel prompt window when exporting data to Excel 2007

Answer 2

you'll have to do lots of interop, here is a tutorial, hope it helps. link text

Answer 3

You can use an OLEDB data provider and just treat Excel as another ADO.NET data source in order to loop through your DataTable rows and insert them into the Excel spreadsheet. Here's a Microsoft KB article that walks you through a lot of the details.

http://support.microsoft.com/kb/316934/en-us

The big thing to keep in mind is that you can create workbooks and sheets within the workbook, and you can reference existing sheets by appending a '$' at the end of the name. If you omit the '$' at the end of the sheet name, the OLEDB provider will assume that it's a new sheet and will try to create it.

The dollar sign following the worksheet name is an indication that the table exists. If you are creating a new table, as discussed in the Create New Workbooks and Tables section of this article, do not use the dollar sign.

You can create and spreadsheet in 2003 (.xls) or 2007 format (xlsx), and that's defined on your connection string -- you specify the file that you're going to write to, and just specify the extension. Make sure you use the right OLEDB provider version.

If you want to create a 2003 (.xls) version, you use this connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties="Excel 8.0;HDR=YES

If you want to create a 2007 (.xlsx) version, you use this connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xlsx;Extended Properties="Excel 12.0;HDR=YES

You may have to download the ACE provider from Microsoft in order to create XLSX files. You can find it here.

I usually use the XLS provider, so I haven't worked with the XLSX provider as much.

Hope this helps. Let me know if you have other questions.

Answer 4

I wrote the following code for the company some time back. It takes Enumerable of any class type and exports all its (get)properties to Excel and also open Excel. You should be able to do something similar for a DataTable. Remember you need to add reference to Microsoft.Office.Interop.Excel

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