Using LINQ in Excel VSTO with efficient Data Push to Excel Sheet
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.