Save DataTable data in Excel file asp.Net

Question

I have a Gridview which i fill with a Datatable. On the webpage the user has to be able to download an excel file with the contents of the Gridview. So mi thought the easiest way to save the files is to save the datatable as an excel file. I have troied this method, which i found here on stackoverflow.

using Excel = Microsoft.Office.Interop.Excel;
 public static bool ExportDataTableToExcel(DataTable dt, string filepath)
    {

    Excel.Application oXL;
    Excel.Workbook oWB;
    Excel.Worksheet oSheet;
    Excel.Range oRange;

    try
    {
        // Start Excel and get Application object. 
        oXL = new Excel.Application();

        // Set some properties 
        oXL.Visible = true;
        oXL.DisplayAlerts = false;

        // Get a new workbook. 
        oWB = oXL.Workbooks.Add(Missing.Value);

        // Get the Active sheet 
        oSheet = (Excel.Worksheet)oWB.ActiveSheet;
        oSheet.Name = "Data";

        int rowCount = 1;
        foreach (DataRow dr in dt.Rows)
        {
            rowCount += 1;
            for (int i = 1; i < dt.Columns.Count + 1; i++)
            {
                // Add the header the first time through 
                if (rowCount == 2)
                {
                    oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                }
                oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
            }
        }

        // Resize the columns 
        oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                      oSheet.Cells[rowCount, dt.Columns.Count]);
        oRange.EntireColumn.AutoFit();

        // Save the sheet and close 
        oSheet = null;
        oRange = null;
        oWB.SaveAs(filepath, Excel.XlFileFormat.xlWorkbookNormal,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            Excel.XlSaveAsAccessMode.xlExclusive,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
        oWB.Close(Missing.Value, Missing.Value, Missing.Value);
        oWB = null;
        oXL.Quit();
    }
    catch
    {
        throw;
    }
    finally
    {
        // Clean up 
        // NOTE: When in release mode, this does the trick 
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
    }

    return true;
}

Bu i get a build error stating that "The name Missing does not exist in the current context" and i cant seem to figure out why? can anyone help ?

the code is pasted from this stackoverflow page here

it's a build error. so its not jumping out into an exception. But The variable Missing is underlined and is not defined. the Answer from Cybermaxs is correct. — Lahib
– Lahib, Commented Jan 3, 2013 at 10:11

Cybermaxs · Accepted Answer · 2013-01-03 10:08:57Z

2

According to msdn here, Missing.Value is defined in System.Reflection. You should add a reference to this namespace.

using System.Reflection;

answered Jan 3, 2013 at 10:08

Cybermaxs

24.6k8 gold badges86 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lahib Over a year ago

This did the trick. But now i keep getting this error "'object' does not contain a definition for 'get_Range'" I have tried to set the range and i have also tried to do this stackoverflow.com/questions/6546785/… But i still cant save the file. do u have any ideas ?

Lahib Over a year ago

Yes i got to that conclusion. but i cant seem to figure out how to do it

Lahib Over a year ago

Replacing the () from the get_Range and using the Range with [] brackets will work.

Collectives™ on Stack Overflow

Save DataTable data in Excel file asp.Net

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related