Wanted to show a simple example to demonstrate, how we could use LINQ in VSTO applications and efficiently populate data into excel. There are two ways you could chose to populate the data into excel based on your requirement.
1. Use Open Xml SDK to open the contents of the file and populate the contents.
2. Use VSTO excel object model to push data into the sheet.
Brief steps to fetch and populate data :
1. Fetch Data from external source.
2. Convert return data to business entities using LINQ.
3. Apply business logic and transform business entities into object array.
4. Assign object array to excel sheet using excel object model.
Detailed steps :
1. Below method fetches data from a source in xml format and uses LINQ to convert it from xml to Business objects
public void FetchAcctMovements(SearchCriteria sc, OnAppendAction onAcctMovementAppendAction)
{
try
{
using (XmlReader reader = XmlReader.Create(m_context.Client.FetchPDCBalancesNAcctMovements(sc)))
{
if (HasLoginFault(reader))
{
reader.Close();
throw new LoginException("Login failure during AcctMovements fetch..");
}
var singleDayResults = reader.StreamElements("singleDayDataResult");
var singleDay =
from pd in singleDayResults
select new SingleDayMovements
{
BusinessDate = DateTime.Parse((string)pd.Element("businessDate")),
singleDayDataList = (
from singleDayData in pd.XPathSelectElements("singleDayDataList/singleDayData")
select new singleDayData
{
AcctMovements = (
from t in singleDayData.XPathSelectElements("AcctMovementList/AcctMovement")
select new AcctMovementDetail
{
AcctMovement = PdcHelper.FromXml<AcctMovement>(t.ToString())
}
).ToList()
,
Account = new account() { accountId = singleDayData.XPathElementValue("account/accountId"), bankId = singleDayData.XPathElementValue("account/bankId"), accountType = singleDayData.XPathElementValue("account/accountType") }
,
AccInfo = new AccountInfo() { AccountName = singleDayData.XPathElementValue("accountInfo/accountName"), BankName = singleDayData.XPathElementValue("accountInfo/bankName"), CurrencyCode = singleDayData.XPathElementValue("accountInfo/ns2:currencyCode"), StateCode = singleDayData.XPathElementValue("accountInfo/stateCode") }
}
).ToList()
};
foreach (var singleDayMovements in singleDay)
{
singleDayMovements pdd = (singleDayMovements)singleDayMovements;
pdd.singleDayDataList.ForEach((pd) => { onAcctMovementAppendAction(pdd.BusinessDate, pd); });
}
}
}
finally
{
m_context.Client.Close();
}
}
2. Below method AppendAcctMovements applies the business logic as per the requirement into a flat structure. This method transforms the business entities into an object array.
Below is the sample code :
public void AppendAcctMovements(DateTime businessDay, SingleDayData singleDayData)
{
int totalRowCount = singleDayData.AcctMovements.Count + 1;
int currentRowCount = 0;
//create the object to store the dataTable data
object[,] rowData;
rowData = new object[totalRowCount, MAX_COL_COUNT];
singleDayData.AcctMovements.ForEach(delegate(AcctMovementDetail td)
{
try
{
int rowNum = 0;
//"As-Of Date " extract only date businessDate
rowData.SetValue(businessDay.ToShortDateString(), currentRowCount, rowNum++);
//"As-Of-Time " extract only time businessDate
rowData.SetValue(td.AcctMovement.asOfTime.ToShortTimeString(), currentRowCount, rowNum++);
//"Bank ID " bankId
rowData.SetValue(singleDayData.Account.bankId, currentRowCount, rowNum++);
//"Bank Name " bankName
rowData.SetValue(singleDayData.AccInfo.BankName, currentRowCount, rowNum++);
//"State " stateCode
rowData.SetValue(singleDayData.AccInfo.StateCode, currentRowCount, rowNum++);
//"Acct No " accountId
rowData.SetValue(singleDayData.Account.accountId, currentRowCount, rowNum++);
//"Acct Type " accountType
rowData.SetValue(singleDayData.Account.accountType, currentRowCount, rowNum++);
//"Acct Name " accountName
rowData.SetValue(singleDayData.AccInfo.AccountName, currentRowCount, rowNum++);
//"Currency " currencyCode
rowData.SetValue(singleDayData.AccInfo.CurrencyCode, currentRowCount, rowNum++);
//"AIB Type Code " AcctMovementAIBCode
rowData.SetValue(td.AcctMovement.AcctMovementBAICode, currentRowCount, rowNum++);
//"Tran Desc " AcctMovement\AcctMovementDescription
rowData.SetValue(td.AcctMovement.AcctMovementDescription, currentRowCount, rowNum++);
if (td.AcctMovement.creditDebitType.Equals("DEBIT"))
{
rowData.SetValue(td.AcctMovement.AcctMovementAmount, currentRowCount, rowNum++);
rowData.SetValue(0, currentRowCount, rowNum++);
}
else if (td.AcctMovement.creditDebitType.Equals("CREDIT"))
{
//Skip the debit col
rowData.SetValue(0, currentRowCount, rowNum++);
rowData.SetValue(td.AcctMovement.AcctMovementAmount, currentRowCount, rowNum++);
}
else
{
rowData.SetValue(0, currentRowCount, rowNum++);
rowData.SetValue(0, currentRowCount, rowNum++);
}
//"0 Day Flt Amt " zeroDatFloatAmount
rowData.SetValue(td.AcctMovement.zeroDayFloatAmount, currentRowCount, rowNum++);
//"1 Day Flt Amt " oneDayFloatAmount
rowData.SetValue(td.AcctMovement.oneDayFloatAmount, currentRowCount, rowNum++);
//"2+ Day Flt Amt " twoPlusDayFloatAmount
rowData.SetValue(td.AcctMovement.twoPlusDayFloatAmount, currentRowCount, rowNum++);
//"Customer Ref No " customerReferenceNumber
rowData.SetValue(td.AcctMovement.customerReferenceNumber, currentRowCount, rowNum++);
//"Value Date " effectiveDate
rowData.SetValue(td.AcctMovement.effectiveDate, currentRowCount, rowNum++);
//"Location " subAccountLocationNumber
rowData.SetValue(0, currentRowCount, rowNum++);
//"Bank Reference " bankReferenceNumber
rowData.SetValue(td.AcctMovement.bankReferenceNumber, currentRowCount, rowNum++);
//"Tran Status " AcctMovementStatusCode
rowData.SetValue(td.AcctMovement.AcctMovementStatusCode, currentRowCount, rowNum++);
//"Movement Description Text " "This node occurs multiple times, first one will go to Text, second to Text2, third one goes to Text3 and so on
//" AcctMovement\AcctMovementDescription\AcctMovementDescription
//"Movement Description Text2 " same as above AcctMovement\AcctMovementDescription\AcctMovementDescription
//"Movement Description Text3 " same as above AcctMovement\AcctMovementDescription\AcctMovementDescription
//"Movement Description Text4 " same as above AcctMovement\AcctMovementDescription\AcctMovementDescription
//"Movement Description Text5 " same as above AcctMovement\AcctMovementDescription\AcctMovementDescription
//"Movement Description Text6 " same as above AcctMovement\AcctMovementDescription\AcctMovementDescription
//"Movement Description Text7 " same as above AcctMovement\AcctMovementDescription\AcctMovementDescription
foreach (string desc in td.AcctMovement.AcctMovementDescriptionList.AcctMovementDescriptionCollection)
{
rowData.SetValue(desc, currentRowCount, rowNum++);
}
for (; rowNum < MAX_COL_COUNT; rowNum++)
{
rowData.SetValue("", currentRowCount, rowNum);
}
}
catch (Exception) { }
finally
{
currentRowCount++;
}
});
// Assign to excel sheet.
AppendAcctMovementsToExcel(rowData);
}
3. AppendAcctMovementsToExcel is the method which takes the object array and assigns it to the excelsheet. I find this method to much efficient than using a for loop to assign the values to each cell. m_dataSheet is a excel sheet member variable.
protected override void AppendAcctMovementsToExcel(object[,] rowData)
{
Excel.Range dataCells = m_dataSheet.get_Range(m_dataSheet.Cells[m_currentTotalRowCount, 1],
m_dataSheet.Cells[m_currentTotalRowCount + rowData.GetLength(0), MAX_COL_COUNT]);
//assign data to worksheet
dataCells.Value2 = rowData;
m_currentTotalRowCount = rowData.GetLength(0) + m_currentTotalRowCount - 1;
}
Please contact me if you need more clarity on the above.
We can use Clickonce deployment almost on any Http Web Server. Clickonce deployment makes it very easy for the endusers to install the required application. I found ClickOnce deployment particularly useful when you want to distribute addins and document level customized projects for Excel / Word to the enduser in an internet/intranet scenario.
Below are the steps to configure Apache Server for ClickOnce Deployment of an Addin / Document customization projects :
1. Open the project you would like to publish.
2. Open the project properties. Update the properties as shown below.
a. Publishing Folder is the location which would be embedded into the VSTO manifest to locate server for updates.
b. Installation location is the location where the setup files are dropped.
3. Click on the Prerequisites button and select the applications which need to be preinstalled.
4. Add MIME Types to Apache server to enable ClickOnce Deployment. Apache server 2.2 version contains httpd.config at [Install Location] -> Apache Software Foundation->Apache2.2->conf
AddType application/x-ms-application application
AddType application/x-ms-manifest manifest
AddType application/octet-stream deploy
AddType application/vnd.ms-xpsdocument xps
AddType application/xaml+xml xaml
AddType application/x-ms-xbap xbap
AddType application/x-silverlight-app xap
AddType application/microsoftpatch msp
AddType application/microsoftupdate msu
5. Click on the updates button setup the frequency to check the updates.
6. Place the hyperlink pointing to the setup file for the addin in your website.
7. When the user clicks on the link. The end user will be prompted all prerequisite installations and will then install the addin.
8. End user can then open excel application.
9. First time use will prompt the user to trust the addin, if the addin does not have trusted certificate.
10. The Install prompt to the enduser may or may not come based on the security setting of the user machine. You could also configure the inclusion list security.
Note : Please check the below link for specific details.
How to: Configure Inclusion List Security (2007 System)
http://msdn.microsoft.com/en-us/library/bb772070.aspx
Deploy Document-Level Office 2007 Solutions with Windows Installer? (Mary Lee)
http://blogs.msdn.com/vsto/archive/2008/10/02/how-do-i-deploy-document-level-office-2007-solutions-with-windows-installer-mary-lee.aspx
How to: Add or Remove Inclusion List Entries (2007 System)
http://msdn.microsoft.com/en-us/library/bb398239.aspx
Server and Client Configuration Issues in ClickOnce Deployments
http://msdn.microsoft.com/en-us/library/ms228998(VS.80).aspx
Troubleshooting ClickOnce Deployments
http://msdn.microsoft.com/en-us/library/fb94w1t5(VS.80).aspx
How Do I Videos - Office
http://msdn.microsoft.com/en-us/office/bb496949.aspx
Deploying Departmental Solutions via Email (Christin Boyd)
http://blogs.msdn.com/vsto/archive/2008/02/20/deploying-departmental-solutions-via-email.aspx
Over the past couple of few week's I was working on a proof of concept to demostrate the power of Software + Services using Excel 2007, VSTO, Services and ClickOnce Deployment. I will try to demonstrate a sample step by step. It was really exciting to work on the POC. VSTO provided the ease to integrate the office applications with business data and gave enormous flexibility to develop complex business applications, deploy and update them.
The POC demonstrates the ease of deployment, Scalability and Richness.
Deployment Server :
Apache Server / any other Web Server. Using Click Once deployment.
Refer : http://blogs.msdn.com/josephkiran/archive/2009/01/06/clickonce-deployment-using-iis-apache-server-for-vsto.aspx
MIME Types to be added for Apache Server 2.2 httpd file.
AddType application/x-ms-application application
AddType application/x-ms-manifest manifest
AddType application/octet-stream deploy
AddType application/vnd.ms-xpsdocument xps
AddType application/xaml+xml xaml
AddType application/x-ms-xbap xbap
AddType application/x-silverlight-app xap
AddType application/microsoftpatch msp
AddType application/microsoftupdate msu
Services :
WebLogic Server with XML output.
User Interface :
Excel 2007. (With Complex charts and pivots)
High Level Usage of Sample App :
1. User navigates to the Web Site and clicks on the hyperlink to install the add-in.
2. The add-in install checks for the pre-requisites (Office 2007 PIA,VSTO Runtime,.NET 3.5) and installs all the prerequisites, then installs the addin.
3. User can then open the excel application directly where he will find a new tab. Addin acts as a Interface for the user to download the business specific metadata.
4. User clicks on download metadata. Addin then contacts the Services to download the metadata.
5. The Metadata contains information on the latest additions of the excel templates and its location of the server. Addin then lists data as needed.
6. User can then select any of the templates and open the same.
7. The addin then downloads excel template from the deployment location specified in the metadata and writes the file to the user's Documents folder. Addin then open the excel template which contains a remote location to download the document customization.
8. Excel automatically downloads the customizations and opens the document with all the customizations in place.
9. Excel template is now ready to contact any services to download business data and show complex pivots and charts as per the business requirements.
Code snippets, screen shots, detailed steps coming soon...
1. MVC for VSTO Projects.
2. Using LINQ with VSTO projects.
Usefull Links :
Video : Deploying Excel Document-level Customizations with Windows Installer
http://msdn.microsoft.com/en-us/vcsharp/cc998641.aspx
Video : Introduction to Action Panes
http://blogs.msdn.com/kathleen/archive/2007/01/04/vsto-for-mere-mortals-video-series.aspx
Debugging in Application-Level Projects
http://msdn.microsoft.com/en-us/library/ms269003.aspx
How Do I... in Excel
Goole Docs and MS Excel Integration
Using Http Web Clients
