Logo 
Search:

Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

Event Handler to Import / Sync list contents with XLS

  Asked By: Akeem    Date: Nov 23    Category: Sharepoint    Views: 1665

We have spreadsheet-based reports (plain, tabular formats) that are
sent out from our data warehouse on a daily basis. I have them
being caught in a public folder and routed to a document library.
Rather than having users reference the Excel reports directly, our
executives would prefer to see multiple, professional-looking data
views with a "dashboard" type of look.

We have the views all built and have been hand-loading the data sets
each morning using copy and paste (ugh). We're also working with IT
on a longer-term solution to make the data storage / retrieval more
efficient and eliminate manual work.

In the mean time, one of our developers is working on an event
handler that will trigger when a new report arrives. The event
handler will read the contents of the file and synchronize the
changes with the corresponding list (as identified in a
configuration list).

He's running into problems with his approach when he encounters
cells in the spreadsheet with null values, though. We would like to
ask around to see if anyone has done something similar (and overcome
this issue), has ideas to either make this approach work or can
suggest a different approach.

Here's what the developer says, "
I am using the Excel object in my application, the scenario is I am
getting all the cell values in a 2 dimensional array object and
inserting those values cell by cell into a Sharepoint list.

The problem I face is, when there is an empty cell value i am not
able to check that cell with "NULL" or "EMPTY' in the c# code. The
program stops at that cell and I get dotnet Null value exception.

But I can see the value of that cell in my Dev. tool, as <Undefined
value> in the debugger windows."

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Rashawn Hopper     Answered On: Nov 23

Let me preface this reply with the fact that I haven't worked with
Excel's OM for quite sometime and I'm only speculating...

This sounds like the common C# casting issue  that occurs when working
with null. It can be handled in many ways but the old tried and true
"append an empty  string to the cell variable" trick will likely get the
guy unstuck. If that works, I can quickly find a more elegant solution.

 
Answer #2    Answered By: Tyron Calderon     Answered On: Nov 23

I solved my Event Sink problem. I have to logon to the SPS Server using the Administration Account and then I can add the EventSink .dll to the GAC and assign it to a library.

Now I have another problem  that I need to solve. the EventSink has been assigned to an InfoPath library. The EventSink loops through the fields in the InfoPath item and writes them to the Application Event log. When I look at the Event in the log, not all of the fields show up. Here is the loop:

For Each oField In oFields

sLog = sLog + "Field name = <" + oField.Title.ToString + "> " + vbCrLf + vbTab + "Value = <" + oItem(oField.Title.ToString) + ">" + vbCrLf

Next

There are a number of CheckBox fields that do not show while some do show. Any ideas?

 
Answer #3    Answered By: Irvin Foley     Answered On: Nov 23

Are you looping through the fields in the list  or the fields in the infopath form? The list will have limits … Maximum number of fields and such…

 
Answer #4    Answered By: Deonte Stein     Answered On: Nov 23

It appears to be the InfoPath form:


Event Type: Information
Event Source: Facilities event  Log
Event Category: None
Event ID: 6
Date: 12/1/2005
Time: 2:16:10 PM
User: N/A
Computer: EUSTA0272004
Description:
Event occurred in <Requests>
Item that caused the event was <Requests/Facilities Request Event Fields 2005-12-01T12_10_25.xml>

Item field details are as follows ->

Field name = <Created Date>
Value = <12/1/2005 12:10:25 PM>
Field name = <Created By>
Value = <3;#Bob Filipiak, RSI>
Field name = <Last Modified>
Value = <12/1/2005 1:33:13 PM>
Field name = <Modified By>
Value = <3;#Bob Filipiak, RSI>
Field name = <Approval Status>
Value = <0>
Field name = <Approver Comments>
Value = <>
Field name = <URL Path>
Value = </sites/facilities/Requests/Facilities Request Event Fields 2005-12-01T12_10_25.xml>
Field name = <URL Dir Name>
Value = <30;#sites/facilities/Requests>
Field name = <Modified>
Value = <12/1/2005 1:33:13 PM>
Field name = <Created>
Value = <12/1/2005 12:10:25 PM>
Field name = <File Size>
Value = <2222>
Field name = <File System Object Type>
Value = <0>
Field name = <ID of the User who has the item Checked Out>
Value = <30;#3>
Field name = <Name>
Value = <Facilities Request Event Fields 2005-12-01T12_10_25.xml>
Field name = <Virus Status>
Value = <30;#2222>
Field name = <Checked Out To>
Value = <30;#Bob Filipiak, RSI>
Field name = <Checked Out To>
Value = <30;#Bob Filipiak, RSI>
Field name = <Document Modified By>
Value = <NAE\Robert.Filipiak>
Field name = <Document Created By>
Value = <NAE\Robert.Filipiak>
Field name = <File Type>
Value = <xml>
Field name = <HTML File Type>
Value = <InfoPath.Document>
Field name = <Source Url>
Value = <>
Field name = <Shared File Index>
Value = <>
Field name = <Name>
Value = <Facilities Request Event Fields 2005-12-01T12_10_25.xml>
Field name = <Name>
Value = <Facilities Request Event Fields 2005-12-01T12_10_25.xml>
Field name = <Select>
Value = <30>
Field name = <Select>
Value = <30>
Field name = <Edit>
Value = <>
Field name = <Type>
Value = <xml>
Field name = <Server Relative URL>
Value = </sites/facilities/Requests/Facilities Request Event Fields 2005-12-01T12_10_25.xml>
Field name = <Encoded Absolute URL>
Value = <........./sites/facilities/Requests/Facilities%20Request%20Event%20Fields%202005-12-01T12_10_25.xml>
Field name = <Name>
Value = <Facilities Request Event Fields 2005-12-01T12_10_25.xml>
Field name = <File Size>
Value = <2222>
Field name = <InstanceID>
Value = <>
Field name = <Title>
Value = <Facilities Request Event Fields 2005-12-01T12_10_25.xml>
Field name = <Merge>
Value = <0>
Field name = <Relink>
Value = <0>
Field name = <Template Link>
Value = <........../.../template.xsn>
Field name = <ProgID>
Value = <InfoPath.Document>
Field name = <title>
Value = <Event Fields>
Field name = <Description>
Value = <Testing handler.>
Field name = <Begin_date>
Value = <12/1/2005>
Field name = <End_date>
Value = <12/2/2005>
Field name = <Request_POC>
Value = <Bob F>
Field name = <Request_POC_phone>
Value = <hghgh>
Field name = <Request_department>
Value = <Support Directorate>
Field name = <Reqeust_poc_email>
Value = <jhjhjh>
Field name = <Request_POC_location>
Value = <2787>
Field name = <Room_resource>
Value = <1735 - Conference Room>
Field name = <Activity>
Value = <Meeting>

There are eight boolean fields that should appear here....



Field name = <VTC_Tech_POC>
Value = <nolen>
Field name = <VTC_Tech_POC_Phone>
Value = <2207>
Field name = <VTC_site_id>
Value = <>
Field name = <VTC_Type>
Value = <>
Field name = <VTC_type>
Value = <Direct>
Field name = <VTC_site_poc>
Value = <Bob>
Field name = <VTC_POC_LOCATION>
Value = <2787>
Field name = <comments>
Value = <This is another comment.>

Below are three tmp fields that I capture while looping.

sBegin_Date = oItem(oField.Title.ToString)
sEnd_Date = oItem(oField.Title.ToString)


stitle = <Event Fields>
sBegin_Date = <12:00:00 AM>
sBegin_Date = <12:00:00 AM>


I tried adding a boolean field to another form and assigned the EventSink tothat library. The same thing happened.

 
Didn't find what you were looking for? Find more on Event Handler to Import / Sync list contents with XLS Or get search suggestion and latest updates.




Tagged: