I am trying to export the summed up values into an excel file. I could easily do this with a CSV file but doing so with excel is difficult for me because I could only inject one value into a cell at a time. And I have to export it to an xlsx extension.
What I am trying to do here is set the first parameter as the row number, second parameter as the column number, and the third parameter as the value to be injected there.
int iColumn = 1;
excel.WriteToSheet(int value of row, iColumn, key);
excel.WriteToSheet(int value of row, iColumn + 1, iSumOpen);
excel.WriteToSheet(int value of row, iColumn + 2, iSumBuy);
excel.WriteToSheet(int value of row, iColumn + 3, iSumSell);
excel.WriteToSheet(int value of row, iColumn + 4, iSumSettleMM);
Here is the entire method. I have tried to change the row value to key.count, using a for loop and making it key.element(i), and have had no luck finding the logical way to do this, partly because I am inexperienced with dictionaries and the methods of it.
public void printToExcel(string vOutputPath)
{
string sExcelPath = @"C:\Users\jhochbau\Desktop\Test.xlsx";
ExcelHelper excel = new ExcelHelper(sExcelPath);
excel.OpenWorkbook();
foreach (KeyValuePair<string, List<DataRecord>> kvp in vSummaryResults)
{
string key = kvp.Key; //assigns key
List<DataRecord> list = kvp.Value; //assigns value
int iSumOpen = 0;
int iSumBuy = 0;
int iSumSell = 0;
double iSumSettleMM = 0;
foreach (DataRecord rec in list)
{
if (vSummaryResults.ContainsKey(key))
{
iSumOpen += rec.open;
iSumBuy += rec.buy;
iSumSell += rec.sell;
iSumSettleMM += rec.settleMM;
}
else
{
vSummaryResults.Add(key, list);
}
}
//WriteToSheet params: int row, int column, and value injected
//need to create some counters here to move the rows and columns?
int iColumn = 1;
excel.WriteToSheet(int value of row, iColumn, key);
excel.WriteToSheet(int value of row, iColumn + 1, iSumOpen);
excel.WriteToSheet(int value of row, iColumn + 2, iSumBuy);
excel.WriteToSheet(int value of row, iColumn + 3, iSumSell);
excel.WriteToSheet(int value of row, iColumn + 4, iSumSettleMM);
}
}
Also the output should look like this:
Account iSumOpen iSumBuy iSumSell iSumSettleMM
Account2...
Account3...
Just make a counter starting at the first row, if row 0 is your header row then start with 1, and use a foreach loop on the dictionary.
public void printToExcel(string vOutputPath)
{
string sExcelPath = @"C:\Users\jhochbau\Desktop\Test.xlsx";
ExcelHelper excel = new ExcelHelper(sExcelPath);
excel.OpenWorkbook();
int curRow = 1; //initialize to your starting row for data printing
foreach (KeyValuePair<string, List<DataRecord>> kvp in vSummaryResults)
{
string key = kvp.Key; //assigns key
List<DataRecord> list = kvp.Value; //assigns value
int iSumOpen = list.Sum(x => x.open);
int iSumBuy = list.Sum(x => x.buy);
int iSumSell = list.Sum(x => x.sell);
double iSumSettleMM = list.Sum(x => x.settleMM);
//No need to check vSummaryResults.ContainsKey(key) here, because you already took the key from the dictionary
//in 'string key = kvp.Key;' so you know it exists unless you've removed it
//also don't need to bother with a loop here, use LINQ instead
//foreach (DataRecord rec in list)
//WriteToSheet params: int row, int column, and value injected
//need to create some counters here to move the rows and columns?
int iColumn = 1;
excel.WriteToSheet(curRow, iColumn, key);
excel.WriteToSheet(curRow, iColumn + 1, iSumOpen);
excel.WriteToSheet(curRow, iColumn + 2, iSumBuy);
excel.WriteToSheet(curRow, iColumn + 3, iSumSell);
excel.WriteToSheet(curRow, iColumn + 4, iSumSettleMM);
curRow += 1; //increment to the next data row in the sheet
}
}
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments