Reading from an Excel Sheet and inserting into a Custom List in Sharepoint

//Add reference to using Microsoft.Office.Interop.Excel;

Application ExcelObj = new Application();
Workbook workBook = null;
Worksheet ws = null;

workBook = ExcelObj.Workbooks.Open(_tempFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

ws = new WorksheetClass();
ws = (Worksheet)workBook.ActiveSheet;

Range range = ws.get_Range("A" + i.ToString(), "J" + i.ToString());

System.Array myvalues = (System.Array)range.Cells.Value2;

if (myvalues.GetValue(1, 1) != null)//Check that the row has data
{
String example1 = (string)myvalues.GetValue(1, 1);
String example2 = (string)myvalues.GetValue(1, 2);

..
..
..

//Insert into Sharepoint

SPWeb web = SPContext.Current.Web;
SPListItemCollection listItems = web.Lists[_sharepointListName].Items;

SPListItem newListItem = listItems.Add();
newListItem[ColumnName1] = example1;
newListItem[ColumnName2] = example2;
..
..
..
..
}

//After working with Excel it is important to close the procsses that Excel uses other wise in your task manager you will find loads of Excel processes without being closed, the following method does a complete close down of all excel processes

private void ReleaseResources(Application ExcelObjk)
{
try
{
if (ws != null)
Marshal.ReleaseComObject(ws);

if (workBook != null)
{
workBook.Close(false, false, Type.Missing);
Marshal.ReleaseComObject(workBook);
}

if (ExcelObjk != null)
{
ExcelObjk.Workbooks.Close();
ExcelObjk.Quit();
Marshal.ReleaseComObject(ExcelObjk);
}
CallGarbageCollectorToCleanupReleasedMemory();
}
catch (Exception x)
{
…
}
}

private void CallGarbageCollectorToCleanupReleasedMemory()
{
GC.Collect();

//The bottom 2 lines are unnecessary and very dirty but can be used in worst case scenarious when you absolutely have to, have included here just to give an idea that this is possible

Process[] processes;
processes = System.Diagnostics.Process.GetProcessesByName("Excel");
foreach (Process p in processes)
p.Kill();
}

Error 1920.Service Active MQ failed to start When installing Vamosa

When installing Vamosa If you recieve "Error 1920.Service Active MQ (ActiveMQ) failed to start. Verify that you have sufficient privileges to start system services", check the log file under c:\vamosa\activemq\bin\wrapper.log and you will notice that you will see an error message like

STATUS | wrapper | 2007/10/22 09:26:11 | Launching a JVM...
FATAL | wrapper | 2007/10/22 09:26:11 | Unable to execute Java command. The system cannot find the file specified. (0x2)
FATAL | wrapper | 2007/10/22 09:26:11 | "%JAVA_HOME%\bin\java" -
FATAL | wrapper | 2007/10/22 09:26:11 | Critical error: wait for JVM process failed..

This is because a new environment variable has to be created called "JAVA_HOME", this should point to the root folder of where your JDK is installed.

A Dummies guide to Creating a custom Web Part for SharePoint Server 2007

We have put together simple but important things about the development and deployment of web parts for Sharepoint server 2007.

1. Download the SharePoint Add on for Visual Stuido, this will create new templates so when you click on New Project in Visual Studio you will see new project types like "WebPart http://www.microsoft.com/downloads/details.aspx?FamilyID=19f21e5e-b715-4f0c-b959-8c6dcbdc1057&DisplayLang=en

2. Sharepoint web parts can be treated like Custom Server Controls in .Net. The difference is SharePoint references are added automatically when you create a new web part project and deploying to SharePoint is as easy as pressing F5.

3. Use the SharePoint object model classes like SPWeb, SPContext, SPListItemCollection and SPListItem to work with lists in Sharepoint. This way there is no need to add a web reference to the web service that is exposed by SharePoint.

4. Deploying - If Sharepoint is installed on the development machine then pressing F5 will automatically add the webpart in the GAC, mark the webpart as safe in the sharepoing web.config files and import the .dwp file into sharepoint. So to add the custom web part that we created, all we need to do is click Add web part and our custom web part should appear in the list of web parts along with other web parts.

5. When you run the project (F5) a setup.bat file is created in the Debug Folder or Release folder (depending on build type you do). Copy this folder accross to the server where you want to install this webpart and run the setup.bat file in the folder. Now this would do the steps mentioned in point 4 above so the webpart can be added to the sharepoint site by using the Add Web Part tab.

.Net Execution Timout - ThreadAbortException

I had to create a custom web part that read a list of items from an excel sheet and upload it into sharepoints custom list. In that excel sheet there was a url field which pointed to pdf documents on the internet, so while uploading contents into the custom list I also had to download the pdf documents and add them to the custom list as attachments.

During this I noticed that a “system.Threading.ThreadAbortException: Thread was being aborted.” exception was being thrown approximately after the web part was running for 125 seconds. I found out that the system was throwing this exception and it was because by default .Net threads have an execution time out limit of 110 seconds. To fix this we need to modify the web.config file that Sharepoint uses. The following line needs to be replaced

with

The executionTimeout value should be set depending on the number of items in the excel sheet. Please refer to the support document below for information on this http://support.microsoft.com/kb/928756.

The other thing to note is when the ThreadAbortException is thrown by the system you cannot catch it in a try catch. If you use a try catch it will go through the catch and at the end of the catch statement it will throw it again.

But if the ThreadAbortException is thrown by our code then we can abort it by using the class Thread.ResetAbort in the finally clause. More info on that can be found on
http://msdn2.microsoft.com/en-gb/library/system.threading.threadabortexception.aspx