Convert datatable to excel 2007(.xlsx)

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

Can anyone hel开发者_运维技巧p me to achieve this?

---

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.

---

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

---

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?

You just need to add my free C# class to your project, and one line of code.

Full details (with free downloadable source code, and an example project) here:

Mikes Knowledge Base - Export to Excel

My library uses the free Microsoft OpenXML libraries (also provided in my downloads) to write the file, so you don't have to use the heavyweight VSTO libraries, or have Excel installed on your server.

Also, it creates a real .xlsx file, rather than some other methods which write a stream of data to a comma-separated text file, but name it as a .xls file.

By the way, I had loads of difficulties writing to Excel files using OLEDB, not least because I was running Windows 7 64-bit, with Office 2007 (which is 32-bit) and the Microsoft ACE provider has to be the 64-bit edition... but you can't install this, if you have the 32-bit version of Office installed.

So, you have to uninstall Office, install the ACE driver, and then re-install Office.
But even then, I gave up using OLEDB.. it just wasn't stable enough.

---

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

---

Saw that someone else posted a "save to csv" option. While that didn't seem to be the answer the OP was looking for, here is my version that includes the table's headers

    public static String ToCsv(DataTable dt, bool addHeaders)
    {
        var sb = new StringBuilder();
        //Add Header Header
        if (addHeaders)
        {
            for (var x = 0; x < dt.Columns.Count; x++)
            {
                if (x != 0) sb.Append(",");
                sb.Append(dt.Columns[x].ColumnName);
            }
            sb.AppendLine();
        }
        //Add Rows
        foreach (DataRow row in dt.Rows)
        {
            for (var x = 0; x < dt.Columns.Count; x++)
            {
                if (x != 0) sb.Append(",");
                sb.Append(row[dt.Columns[x]]);
            }
            sb.AppendLine();
        }
        return sb.ToString();
    }