コード例 #1
0
        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;
            }
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        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();
            }
        }