public static dynamic GetFiscalYearBalanaceSheet()
{
string fiscalYear = string.Empty;
decimal totalAssetAmt = 0, totalLiabAmt = 0;
dynamic balObj = new ExpandoObject();
try
{
balObj.CondoName = "Sushan Condo";
balObj.CondoAdd = "Some Address";
balObj.CondoCity = "City Name";
balObj.FYPeriod = "FY2018-19";
var resultDs = BalanceSheetViewModel.GetData();
//distinct values of Catagory
var lstLiabCat = resultDs.Liabilities.Select(x => x.CatagoryName).Distinct();
totalLiabAmt = resultDs.Liabilities.Sum(x => x.Ammount);
List <dynamic> liabObj = new List <dynamic>();
foreach (var cat in lstLiabCat)
{
dynamic oLiab = new ExpandoObject();
oLiab.CatagoryItem = cat;
oLiab.TotalValue = CurrencyFormate(resultDs.Liabilities.Where(x => x.CatagoryName == cat).Sum(y => y.Ammount));
oLiab.Items = resultDs.Liabilities.Where(x => x.CatagoryName == cat).Select(y => new { ItemName = y.AssetItem, Value = CurrencyFormate(y.Ammount) });
liabObj.Add(oLiab);
}
balObj.LiabilityItem = new
{
Liabilities = liabObj,
TotalAmount = CurrencyFormate(totalLiabAmt)
};
var lstAssetCat = resultDs.Assets.Select(x => x.CatagoryName).Distinct();
totalAssetAmt = resultDs.Assets.Sum(x => x.Ammount);
List <dynamic> assetObj = new List <dynamic>();
foreach (var cat in lstAssetCat)
{
dynamic oAsst = new ExpandoObject();
oAsst.CatagoryItem = cat;
oAsst.TotalValue = CurrencyFormate(resultDs.Assets.Where(x => x.CatagoryName == cat).Sum(y => y.Ammount));
oAsst.Items = resultDs.Assets.Where(x => x.CatagoryName == cat).Select(y => new { ItemName = y.AssetItem, Value = CurrencyFormate(y.Ammount) });
assetObj.Add(oAsst);
}
balObj.AssetItem = new
{
Assets = assetObj,
TotalAmount = CurrencyFormate(totalAssetAmt)
};
return(balObj);
}
catch (Exception ex)
{
throw ex;
}
}
public static BalanceSheetViewModel GetData()
{
var rslt = new BalanceSheetViewModel();
rslt.Liabilities = new List <CatagoryItems>();
rslt.Assets = new List <CatagoryItems>();
for (int i = 1; i <= 5; i++)
{
rslt.Liabilities.Add(new CatagoryItems {
CatagoryName = "XXX", AssetItem = string.Format("Liable - {0}", i), Ammount = 123 * i
});
}
for (int i = 1; i <= 3; i++)
{
rslt.Liabilities.Add(new CatagoryItems {
CatagoryName = "YYY", AssetItem = string.Format("Liable - {0}", i), Ammount = 123 * i
});
}
for (int i = 1; i <= 5; i++)
{
rslt.Assets.Add(new CatagoryItems {
CatagoryName = "AAA", AssetItem = string.Format("Asset - {0}", i), Ammount = 255 * i
});
}
for (int i = 1; i <= 3; i++)
{
rslt.Assets.Add(new CatagoryItems {
CatagoryName = "BBB", AssetItem = string.Format("Asset - {0}", i), Ammount = 255 * i
});
}
//for (int i = 1; i <= 7; i++)
//{
// rslt.Assets.Add(new CatagoryItems { CatagoryName = "CCC", AssetItem = string.Format("Asset - {0}", i), Ammount = 147 * i });
//}
return(rslt);
}
public void CreateExcelDoc(string fileName)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
// Setting up columns
Columns columns = new Columns(
new Column // empty
{
Min = 1,
Max = 1,
Width = 8,
CustomWidth = true
},
new Column // Liab
{
Min = 2,
Max = 2,
Width = 30,
CustomWidth = true
},
new Column // amt
{
Min = 3,
Max = 3,
Width = 15,
CustomWidth = true
},
new Column //amt
{
Min = 4,
Max = 4,
Width = 15,
CustomWidth = true
},
new Column // asset
{
Min = 5,
Max = 5,
Width = 30,
CustomWidth = true
},
new Column // amt
{
Min = 6,
Max = 6,
Width = 15,
CustomWidth = true
},
new Column // amt
{
Min = 7,
Max = 7,
Width = 15,
CustomWidth = true
}
);
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Balance Sheet"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
var balList = BalanceSheetViewModel.GetFiscalYearBalanaceSheet();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
//create a MergeCells class to hold each MergeCell
MergeCells mergeCells = new MergeCells();
UInt32 rowIndex = 1;
var refCell = string.Empty;
string mergeRef = string.Empty;
// Constructing rows
Row row = new Row();
//empty row
row.RowIndex = rowIndex;
row.Append(
ConstructMergedCells(string.Empty, string.Format("{0}{1}", "A", rowIndex), 7, 0));
sheetData.AppendChild(row);
rowIndex++;
//condo name - merge and center, all borders
row = new Row();
row.RowIndex = rowIndex;
row.Append(ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)));
refCell = string.Format("{0}{1}", "B", rowIndex);
row.Append(
ConstructMergedCells(balList.CondoName, refCell, 6, 6));
sheetData.AppendChild(row);
rowIndex++;
mergeRef = IncrementXLColumn(refCell, 6);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
//condo Add - merge and center, all borders
row = new Row();
row.RowIndex = rowIndex;
row.Append(ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)));
refCell = string.Format("{0}{1}", "B", rowIndex);
row.Append(
ConstructMergedCells(balList.CondoAdd, refCell, 6, 6));
sheetData.AppendChild(row);
rowIndex++;
mergeRef = IncrementXLColumn(refCell, 6);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
//Fiscal year period - merge and center, all borders
row = new Row();
row.RowIndex = rowIndex;
row.Append(ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)));
refCell = string.Format("{0}{1}", "B", rowIndex);
row.Append(
ConstructMergedCells("Balance Sheet at \"" + balList.FYPeriod + "\"", refCell, 6, 6));
sheetData.AppendChild(row);
rowIndex++;
mergeRef = IncrementXLColumn(refCell, 6);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
//liable&asset col
row = new Row();
row.RowIndex = rowIndex;
row.Append(
ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)),
ConstructCell("Liabilities", CellValues.String, 6, string.Format("{0}{1}", "B", rowIndex)),
ConstructCell("Amount(RM)", CellValues.String, 6, string.Format("{0}{1}", "C", rowIndex)),
ConstructCell("Amount(RM)", CellValues.String, 6, string.Format("{0}{1}", "D", rowIndex)),
ConstructCell("Assets", CellValues.String, 6, string.Format("{0}{1}", "E", rowIndex)),
ConstructCell("Amount(RM)", CellValues.String, 6, string.Format("{0}{1}", "F", rowIndex)),
ConstructCell("Amount(RM)", CellValues.String, 6, string.Format("{0}{1}", "G", rowIndex)));
sheetData.AppendChild(row);
rowIndex++;
//empty row
row = new Row();
row.RowIndex = rowIndex;
row.Append(
ConstructMergedCells(string.Empty, string.Format("{0}{1}", "A", rowIndex), 7, 8));
sheetData.AppendChild(row);
rowIndex++;
//data liab in B,C,D and asset in E,F,G
int liabCount = 0, assetCount = 0;
UInt32 assetRowIndx = rowIndex;
RowDicSheetData = new Dictionary <UInt32, Row>();
//Liabilities
foreach (var liabItem in balList.LiabilityItem.Liabilities)
{
//catagory Name
row = new Row();
row.RowIndex = rowIndex;
row.Append(
ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)),
ConstructCell(liabItem.CatagoryItem, CellValues.String, 9, string.Format("{0}{1}", "B", rowIndex)),
ConstructCell(string.Empty, CellValues.String, 8, string.Format("{0}{1}", "C", rowIndex)),
ConstructCell(string.Empty, CellValues.String, 8, string.Format("{0}{1}", "D", rowIndex)));
RowDicSheetData.Add(rowIndex, row);
liabCount++;
rowIndex++;
foreach (var item in liabItem.Items)
{
//Sub Catagory Names with amount
row = new Row();
row.RowIndex = rowIndex;
row.Append(
ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)),
ConstructCell(item.ItemName, CellValues.String, 8, string.Format("{0}{1}", "B", rowIndex)),
ConstructCell(item.Value, CellValues.String, 7, string.Format("{0}{1}", "C", rowIndex)),
ConstructCell(string.Empty, CellValues.String, 8, string.Format("{0}{1}", "D", rowIndex)));
RowDicSheetData.Add(rowIndex, row);
liabCount++;
rowIndex++;
}
//total amount per catagory
row = new Row();
row.RowIndex = rowIndex;
row.Append(
ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)),
ConstructCell(string.Empty, CellValues.String, 8, string.Format("{0}{1}", "B", rowIndex)),
ConstructCell(string.Empty, CellValues.String, 8, string.Format("{0}{1}", "C", rowIndex)),
ConstructCell(liabItem.TotalValue, CellValues.String, 7, string.Format("{0}{1}", "D", rowIndex)));
RowDicSheetData.Add(rowIndex, row);
liabCount++;
rowIndex++;
}
//Assets asset in E,F,G
foreach (var assetItem in balList.AssetItem.Assets)
{
var rowAtIndx = TryGetAssetRow(assetRowIndx);
rowAtIndx.Append(
ConstructCell(assetItem.CatagoryItem, CellValues.String, 9, string.Format("{0}{1}", "E", assetRowIndx)),
ConstructCell(string.Empty, CellValues.String, 8, string.Format("{0}{1}", "F", assetRowIndx)),
ConstructCell(string.Empty, CellValues.String, 8, string.Format("{0}{1}", "G", assetRowIndx)));
assetCount++;
assetRowIndx++;
foreach (var item in assetItem.Items)
{
//Sub Catagory Names with amount
rowAtIndx = TryGetAssetRow(assetRowIndx);
rowAtIndx.Append(
ConstructCell(item.ItemName, CellValues.String, 8, string.Format("{0}{1}", "E", assetRowIndx)),
ConstructCell(item.Value, CellValues.String, 7, string.Format("{0}{1}", "F", assetRowIndx)),
ConstructCell(string.Empty, CellValues.String, 8, string.Format("{0}{1}", "G", assetRowIndx)));
assetCount++;
assetRowIndx++;
}
//total amount per catagory
rowAtIndx = TryGetAssetRow(assetRowIndx);
rowAtIndx.Append(
ConstructCell(string.Empty, CellValues.String, 8, string.Format("{0}{1}", "E", assetRowIndx)),
ConstructCell(string.Empty, CellValues.String, 8, string.Format("{0}{1}", "F", assetRowIndx)),
ConstructCell(assetItem.TotalValue, CellValues.String, 7, string.Format("{0}{1}", "G", assetRowIndx)));
assetCount++;
assetRowIndx++;
}
if (liabCount < assetCount)
{
rowIndex = assetRowIndx;
}
else
{
//need work out
for (UInt32 indx = assetRowIndx; indx <= rowIndex; indx++)
{
var rowEmtAsset = TryGetAssetRow(indx);
rowEmtAsset.Append(ConstructMergedCells(string.Empty, string.Format("{0}{1}", "E", indx), 3, 8));
}
}
//Add RowDictionary To sheet data
foreach (var rowDic in RowDicSheetData)
{
sheetData.AppendChild(rowDic.Value);
}
//empty row
row = new Row();
row.RowIndex = rowIndex;
row.Append(
ConstructMergedCells(string.Empty, string.Format("{0}{1}", "A", rowIndex), 7, 8));
sheetData.AppendChild(row);
rowIndex++;
//total summary row
row = new Row();
row.RowIndex = rowIndex;
row.Append(
ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)),
ConstructCell(string.Empty, CellValues.String, 12, string.Format("{0}{1}", "B", rowIndex)),
ConstructCell(string.Empty, CellValues.String, 6, string.Format("{0}{1}", "C", rowIndex)),
ConstructCell(balList.LiabilityItem.TotalAmount, CellValues.String, 10, string.Format("{0}{1}", "D", rowIndex)),
ConstructCell(string.Empty, CellValues.String, 12, string.Format("{0}{1}", "E", rowIndex)),
ConstructCell(string.Empty, CellValues.String, 6, string.Format("{0}{1}", "F", rowIndex)),
ConstructCell(balList.AssetItem.TotalAmount, CellValues.String, 10, string.Format("{0}{1}", "G", rowIndex)));
sheetData.AppendChild(row);
rowIndex++;
//disclimar row
row = new Row();
row.RowIndex = rowIndex;
row.Append(ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)));
refCell = string.Format("{0}{1}", "B", rowIndex);
row.Append(
ConstructMergedCells("As per our report of even date attached", refCell, 3, 13));
mergeRef = IncrementXLColumn(refCell, 3);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
refCell = IncrementXLColumn(mergeRef);
row.Append(
ConstructMergedCells(string.Empty, refCell, 3, 14));
mergeRef = IncrementXLColumn(refCell, 3);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
sheetData.AppendChild(row);
rowIndex++;
//disclimar row
row = new Row();
row.RowIndex = rowIndex;
row.Append(ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)));
refCell = string.Format("{0}{1}", "B", rowIndex);
row.Append(
ConstructMergedCells(string.Empty, refCell, 3, 13));
mergeRef = IncrementXLColumn(refCell, 3);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
refCell = IncrementXLColumn(mergeRef);
row.Append(
ConstructMergedCells("For and on behalf of the Board", refCell, 3, 14));
mergeRef = IncrementXLColumn(refCell, 3);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
sheetData.AppendChild(row);
rowIndex++;
//empty row
row = new Row();
row.RowIndex = rowIndex;
row.Append(ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)));
refCell = string.Format("{0}{1}", "B", rowIndex);
row.Append(
ConstructMergedCells(string.Empty, refCell, 3, 13));
mergeRef = IncrementXLColumn(refCell, 3);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
refCell = IncrementXLColumn(mergeRef);
row.Append(
ConstructMergedCells(string.Empty, refCell, 3, 14));
mergeRef = IncrementXLColumn(refCell, 3);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
sheetData.AppendChild(row);
rowIndex++;
//empty row
row = new Row();
row.RowIndex = rowIndex;
row.Append(ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)));
refCell = string.Format("{0}{1}", "B", rowIndex);
row.Append(
ConstructMergedCells(string.Empty, refCell, 3, 13));
mergeRef = IncrementXLColumn(refCell, 3);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
refCell = IncrementXLColumn(mergeRef);
row.Append(
ConstructMergedCells(string.Empty, refCell, 3, 14));
mergeRef = IncrementXLColumn(refCell, 3);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
sheetData.AppendChild(row);
rowIndex++;
//empty row
row = new Row();
row.RowIndex = rowIndex;
row.Append(ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)));
refCell = string.Format("{0}{1}", "B", rowIndex);
row.Append(
ConstructMergedCells(string.Format("Place : {0}", balList.CondoCity), refCell, 3, 13));
mergeRef = IncrementXLColumn(refCell, 3);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
refCell = IncrementXLColumn(mergeRef);
row.Append(
ConstructMergedCells(string.Empty, refCell, 3, 14));
mergeRef = IncrementXLColumn(refCell, 3);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
sheetData.AppendChild(row);
rowIndex++;
//withness row
row = new Row();
row.RowIndex = rowIndex;
row.Append(ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)));
refCell = string.Format("{0}{1}", "B", rowIndex);
row.Append(
ConstructMergedCells(string.Format("Date : {0}", DateTime.Today.ToString("dd/MM/yyyy")), refCell, 3, 13));
mergeRef = IncrementXLColumn(refCell, 3);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
refCell = IncrementXLColumn(mergeRef);
row.Append(ConstructCell("President", CellValues.String, 11, refCell));
refCell = IncrementXLColumn(refCell);
row.Append(
ConstructMergedCells("Vice President", refCell, 2, 14));
mergeRef = IncrementXLColumn(refCell, 2);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
sheetData.AppendChild(row);
rowIndex++;
//last row
row = new Row();
row.RowIndex = rowIndex;
row.Append(ConstructCell(string.Empty, CellValues.String, 0, string.Format("{0}{1}", "A", rowIndex)));
refCell = string.Format("{0}{1}", "B", rowIndex);
row.Append(
ConstructMergedCells(string.Empty, refCell, 6, 12));
mergeRef = IncrementXLColumn(refCell, 6);
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(string.Format("{0}:{1}", refCell, mergeRef))
});
sheetData.AppendChild(row);
rowIndex++;
worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements <SheetData>().First());
worksheetPart.Worksheet.Save();
}
}