Share via


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.