locked
Workflow to create and populate excel sheet from sharepoint list RRS feed

  • Question

  • Hello,

    I have a complex infopath form for claiming payments. The claims are in a repeating table. 

    What is needed:

    Once the claim has been submitted, I need workflow to run weekly and monthly on every submitted submitted form, collect the payment information, compile to an excel sheet and email it to the pay master. This is to prevent pay master from getting email for every claim and having their mail box clogged.

    What I have done:

    1. I have created a string variable that extracts all the information that needs to be sent to the pay master from every form, and also if the person is to be paid monthly or weekly.
    2. I also created a list that will hold the information: Payment List
    3. Once the form has been approved for payment, I add this string variable to the list created above.

    What is left to do:

    1. I need to design a workflow that runs weekly, loops through the payment list and if the person is to be paid weekly, collects the payment information and add it as a line on an excel document, then remove that particular payment information from the payment list, and adds it to a new list called "Paid List".
    2. It should then send this excel sheet as an attachment to the payment master.
    3. 1 and 2 should also be done monthly for the monthly paid staff.

    Now, I need a way of achieving the above. I looked into creating a custom workflow, but it looks like I need to have the Sharepoint stored on my development machine and this is not easily achievable. Is there another way of achieving this? I am not afraid to code, but I need good guidance on how to start it.

    Thank you in advance!


    • Edited by STaut Tuesday, January 17, 2012 9:47 AM
    Tuesday, January 17, 2012 9:47 AM

Answers

  • I'm pretty sure you will need to do development for this. Specifically I think you will need to create a custom timer job which can be scheduled to run as needed (http://msdn.microsoft.com/en-us/library/cc427068(v=office.12).aspx). Your timer job can then use the sharepoint object model to read over your Payment list and generate your Excel file. You can easily generate a standard XML or CSV file which can then be opened in Excel, or use the Open XML SDK to create native excel files (http://openxmldeveloper.org/).

    Unfortunately yes, you will need SharePoint on your development environment. This isn't actually that hard to achieve. The best way is to buld a virtual machine (I usually use Virtual PC 2007 for this: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=4580). You can install Windows Server into this, then install SharePoint on top of that and Visual Studio too. You can then build and test your code in the Virtual Machine and deploy it to the live server when ready.

    • Marked as answer by Pengyu Zhao Wednesday, January 25, 2012 2:52 PM
    Tuesday, January 17, 2012 3:53 PM

All replies

  • I'm pretty sure you will need to do development for this. Specifically I think you will need to create a custom timer job which can be scheduled to run as needed (http://msdn.microsoft.com/en-us/library/cc427068(v=office.12).aspx). Your timer job can then use the sharepoint object model to read over your Payment list and generate your Excel file. You can easily generate a standard XML or CSV file which can then be opened in Excel, or use the Open XML SDK to create native excel files (http://openxmldeveloper.org/).

    Unfortunately yes, you will need SharePoint on your development environment. This isn't actually that hard to achieve. The best way is to buld a virtual machine (I usually use Virtual PC 2007 for this: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=4580). You can install Windows Server into this, then install SharePoint on top of that and Visual Studio too. You can then build and test your code in the Virtual Machine and deploy it to the live server when ready.

    • Marked as answer by Pengyu Zhao Wednesday, January 25, 2012 2:52 PM
    Tuesday, January 17, 2012 3:53 PM
  • Thank you steven.

    So you mean apart from having sharepoint designer stored on my machine, i also need the sharepoint server?

    My company will sort out the licensing and I will tell you how i get along.

     

    Regards

    Wednesday, January 18, 2012 8:27 AM