SharePoint - Microsoft.Office.Interop.Excel

Asked By Chetan Govindaswamy
19-Mar-10 05:18 AM

Hi,
      I am using Microsoft.Office.Interop.Excel to export the System.data Datatable values to the MS Excel.

using Excel = Microsoft.Office.Interop.Excel;
        Excel.Application App = null;
        Excel._Workbook WB = null;
        Excel._Worksheet WS = null;
        //Start Excel and get Application object.
        App = new Excel.Application();
        App.Visible = true;
        //Get a new workbook.
        WB = (Excel._Workbook)(App.Workbooks.Add(Missing.Value));
        WS = (Excel._Worksheet)WB.ActiveSheet;
        //Getting data from datatable using string[,] arrFromTable
        WS.get_Range("A2", endRange).Value2 = arrFromTable;
        string ExcelFileName = DateTime.Now.Ticks + "s" + App.UserName.GetHashCode().ToString() + ".xls";
        string loginUri = "http://sharepointmachinename/sites/site1/Temp/" + ExcelFileName;
        WB.SaveAs(loginUri, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, null, null, null, null, null);
        WB.Close(false, null, null);
        App.Quit();
      The SaveAs method doesnt throw any exception but the file will not be saved in the destination as the browser will not respond at this step and finally timeout.
       Could there be any other method to save the excel file in the Sharepoint document library? What could be going wrong in the above method?

Thanks and Regards.
G.Chetan
  samjayander thiagarajan replied to Chetan Govindaswamy
19-Mar-10 05:32 AM
Hi,

Instead of using null as argument for the SaveAs method use

object nullObject = System.Reflection.Missing.Value;
WB.SaveAs(loginUri, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, nullObject, nullObject, nullObject, nullObject, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, nullObject, nullObject, nullObject, nullObject, nullObject );


Regards,
Sam
  Chetan Govindaswamy replied to samjayander thiagarajan
19-Mar-10 05:46 AM
Hi Sam,
              I tried using System.Reflection.Missing.Value instead of null, but still it didnt work.
I tried a work around where I used the same code in a handler file 'Handler.ashx' in a New solution and I was able to save the excel file to the destination, but once I put the same code in another handler file in the old solution it doesnt work. The browser keeps loading and timeout. Sounds unusual but true.
  Cika Pero replied to Chetan Govindaswamy
22-Nov-11 04:09 AM
Hi,

Excel Interop is notoriously known for it's slow performance and unintuitive usage. You can easily export DataTable to Excel with this Excel C# / VB.NET library.

Here is a sample Excel C# code:

var ef = new ExcelFile();

 

ef.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, true);

 

ef.SaveXls(dataTable.TableName + ".xls");

Create New Account
help
Publishing Excel, Infopath Form SharePoint hi , I started working on sharepoint and its related products from last month.I learned some what, the interface which is given in Sharepoint. Microsoft Excel 2007 has the option to publish the Excel to the sharepoint server for collaberation and for some WEb Access. Microsoft Infopath has the option to publish
Setup MS Office SharePoint 2007 Test / Dev Environment Authentication Problems Hello all, i like to setup a fast clonable has dsiplay some warning, regarding the installation of MSSQL on a Domain Controller. My single SharePoint admin account is named SPAdmin (domain admin, mssql: security, dbcreator), the local domain ist named mistake, but currently I am reading the logfiles placed under "c: \ program files \ common files \ microsoft shared \ web server extensions \ 12 \ logs". I don't find any relavant information regarding authentication EXE, onetnative.dll) 03 / 04 / 2010 13:53:04.95 OWSTIMER.EXE (0x0980) 0x0988 Windows SharePoint Services Topology 0 Medium Diagnostics settings: 32768 03 / 04 / 2010 13:53:05.38 OWSTIMER.EXE (0x0980) 0x0988 Windows SharePoint Services Topology 9e7d Medium Initializing the configuration database connection. 03 / 04 / 2010 13:53:11 35 OWSTIMER.EXE (0x0980) 0x0988 Windows SharePoint Services Database 880i High System.Data.SqlClient.SqlException: Die von der Anmeldung angeforderte 'SharePoint_07_Config'-Datenbank ServerInfo serverInfo, Strin. . . 03 / 04 / 2010 13:53:11.35* OWSTIMER.EXE (0x0980) 0x0988 Windows SharePoint Services Database 880i High . . .g newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) bei System.Data SqlClient.SqlConnectionFactory.CreateConn. . . 03 / 04 / 2010 13:53:11.35* OWSTIMER.EXE (0x0980) 0x0988 Windows SharePoint Services Database 880i High . . .ection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) bei System
the right permissions on the content type. Then, I created a column "external data" in Sharepoint that I linked to my content type. When I want to change the data in EventID Level Message Correlation 11 / 13 / 2011 00:08:30.31 w3wp.exe (0x1D9C) 0x1038 SharePoint Foundation Monitoring nasq Medium Entering monitored scope (Request (GET:http: / / tricoflex:80 / CRM / _layouts / FldNewEx Cust%2E%20Relation%20ID&DescriptionParam = &VldFormulaParam = &VldMessageParam = )) 11 / 13 / 2011 00:08:30.31 w3wp.exe (0x1D9C) 0x1038 SharePoint Foundation Logging Correlation Data xmnv Medium Name = Request (GET:http: / / tricoflex:80 / CRM / _layouts / FldNewEx e307-4c5d-b470-c4351e5dff53 11 / 13 / 2011 00:08:30.32 w3wp.exe (0x1D9C) 0x1038 SharePoint Foundation Logging Correlation Data xmnv Medium Site = / 9694064f-e307-4c5d-b470-c4351e5dff53 11 / 13 / 2011 00:08:30.35 w3wp.exe (0x1D9C) 0x1038 SharePoint Foundation Monitoring b4ly Medium Leaving Monitored Scope (Request (GET:http: / / tricoflex:80 / CRM / _layouts / FldNewEx e307-4c5d-b470-c4351e5dff53 11 / 13 / 2011 00:08:30.45 wsstracing.exe (0x06A0) 0x17A0 SharePoint Foundation Unified Logging Service b9wt High Log retention limit reached. Log file 'C: \ Program Files \ Common Files \ Microsoft Shared \ Web Server Extensions \ 14 \ LOGS \ TRICOFLEX-20111030-0008.log' has been deleted. 11 / 13 2011 00:08:30.45 wsstracing.exe (0x06A0) 0x17A0 SharePoint Foundation Tracing Controller Service 8096 Information Usage log retention limit reached. Some old usage log
Unable to cast COM object of type 'Microsoft.SharePoint.Library.SP SharePoint I periodically get the following error message on a page that displays devexpress charts and such based off of a search result: Unable to cast COM object of type 'Microsoft.SharePoint.Library.SPRequestInternalClass' to interface type Microsoft.SharePoint.Library.ISPRequest'. This operation failed because the QueryInterface call on the COM component for the to the following error: Bad variable type. (Exception from HRESULT: 0x80020008 (DISP_E_BADVARTYPE)). Stack Trace: at Microsoft.SharePoint.Library.SPRequestInternalClass.GetWebAncestry(String bstrUrl) at Microsoft.SharePoint.Library.SPRequest.GetWebAncestry(String bstrUrl) at