Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Range'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{00020846-0000-0000-C000-000000000046}' failed due to the following error: Not enough storage is available to complete this operation. (Exception from HRESULT: 0x8007000E (E_OUTOFMEMORY)).
Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass()
Dim xBk As Microsoft.Office.Interop.Excel._Workbook
Dim xSt As Microsoft.Office.Interop.Excel._Worksheet
xBk = excel.Application.Workbooks.Add(True)
xSt = xBk.ActiveSheet
Dim ColumnIndex As Int64 = 0
Dim DT As DataTable
DT = CType(ViewState("gvListOutput"), DataTable)
' DT.Columns.RemoveAt(0)
For Each dc As DataColumn In DT.Columns
ColumnIndex = ColumnIndex + 1
excel.Cells(1, ColumnIndex) = dc.ColumnName
Next
Dim rowIndex As Int64 = 0
For Each dr As DataRow In DT.Rows
rowIndex = rowIndex + 1
ColumnIndex = 0
For Each dc As DataColumn In DT.Columns
ColumnIndex = ColumnIndex + 1
excel.Cells(rowIndex + 1, ColumnIndex) = dr(dc.ColumnName)
Next
Next
If System.IO.File.Exists(path) Then System.IO.File.Delete(path)
xBk.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)
xBk.Close(SaveChanges:=False)
excel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel)
GC.SuppressFinalize(excel)
GC.Collect()
xSt = Nothing
xBk = Nothing
excel = Nothing
//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");
//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();
//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();
adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();
//Add the table to the data set
ds.Tables.Add(dt);
//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);