public static void Example2()
{
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("sheet");
worksheet.Columns[0].SetWidth(200, LengthUnit.Pixel);
// Show the value as a number with two decimal places and thousands separator.
worksheet.Cells[0, 0].Style.NumberFormat
= NumberFormatBuilder.Number(2, useThousandsSeparator: true);
worksheet.Cells[0, 0].Value = 2500.333;
// Show the value in Euros and display negative values in parentheses.
worksheet.Cells[1, 0].Style.NumberFormat
= NumberFormatBuilder.Currency("€", 2, useParenthesesToDisplayNegativeValue: true);
worksheet.Cells[1, 0].Value = -50;
// Show the value in accounting format with three decimal places.
worksheet.Cells[2, 0].Style.NumberFormat
= NumberFormatBuilder.Accounting(3, currencySymbol: "$");
worksheet.Cells[2, 0].Value = -50;
// Show the value in ISO 8061 date format.
worksheet.Cells[3, 0].Style.NumberFormat = NumberFormatBuilder.DateTimeIso8061();
worksheet.Cells[3, 0].Value = DateTime.Now;
// Show the value as percentage.
worksheet.Cells[4, 0].Style.NumberFormat = NumberFormatBuilder.Percentage(2);
worksheet.Cells[4, 0].Value = 1 / 3d;
// Show the value as fraction with 100 as a denominator.
worksheet.Cells[5, 0].Style.NumberFormat = NumberFormatBuilder.FractionWithPreciseDenominator(100);
worksheet.Cells[5, 0].Value = 1 / 3d;
// Show the value in scientific notation using two decimal places.
worksheet.Cells[6, 0].Style.NumberFormat = NumberFormatBuilder.Scientific(2);
worksheet.Cells[6, 0].Value = Math.Pow(Math.PI, 10);
workbook.Save("Number Format Builder.docx");
}
public ActionResult OverAllReportFile(DateTime?month)
{
if (month == null)
{
month = DateTime.UtcNow;
}
var budgets = budgetDac.Get().OrderBy(x => x.Name);
var transactions = transactionDac.Get(month.Value).ToList();
var bringForwordThisMonths = bringForwardDac.Get(month.Value);
transactions.InsertRange(0, bringForwordThisMonths.Select(b => new Transaction
{
IssueDate = new DateTime(month.Value.Year, month.Value.Month, 1),
Title = "ยอดยกมา",
Amount = b?.Amount ?? 0,
BudgetId = b.BudgetId,
}));
var FileName = Path.Combine(Directory.GetCurrentDirectory(), "ReportSrc/overallreport.xlsx");
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load(FileName, LoadOptions.XlsxDefault);
var worksheet = workbook.Worksheets[0];
worksheet.Cells[0, Col.A].SetValue(month.Value.ToString("MMMM", CultureInfo.CreateSpecificCulture("th-TH")));
worksheet.Cells[0, Col.C].SetValue(CurrentSchoolData.sc_name);
worksheet.Cells[2, Col.C].SetValue(month.Value.ToString("d MMMM yyyy", CultureInfo.CreateSpecificCulture("th-TH")));
var bankSumTotal = (double)transactions.Sum(t => t.Amount);
worksheet.Cells[5, Col.D].SetValue(0);
worksheet.Cells[5, Col.E].SetValue(bankSumTotal);
worksheet.Cells[5, Col.F].SetValue(0);
worksheet.Cells[5, Col.G].SetValue(bankSumTotal);
var issuer = identityService.GetUser();
worksheet.Cells[7, Col.D].SetValue(issuer.Name);
worksheet.Cells[8, Col.D].SetValue($"ตำแหน่ง เจ้าหน้าที่การเงิน{CurrentSchoolData.sc_name}");
//TODO: school director
worksheet.Cells[14, Col.D].SetValue("นายสุขสันต์ สอนนวล");
worksheet.Cells[15, Col.D].SetValue($"ผู้อำนวยการ{CurrentSchoolData.sc_name}");
worksheet.Cells[17, Col.A].SetValue($"ข้าพเจ้า/ผู้รับมอบหมายได้รับเงินสดตามรายการข้างต้นแล้ว เมื่อวันที่ {month.Value.ToString("d MMMM yyyy", CultureInfo.CreateSpecificCulture("th-TH"))}");
var tableRowIndex = 5;
var currentRowIndex = tableRowIndex;
foreach (var item in budgets)
{
worksheet.Rows[currentRowIndex].Style.NumberFormat = NumberFormatBuilder.Accounting(2);
worksheet.Rows.InsertCopy(currentRowIndex, worksheet.Rows[currentRowIndex]);
var bankSum = (double)transactions.Where(t => t.BudgetId == item.Id)
.OrderBy(x => x.IssueDate)
.ThenBy(x => x.Id)
.ToList()
.Sum(t => t.Amount);
worksheet.Cells[currentRowIndex, Col.A].SetValue(item.Name);
worksheet.Cells[currentRowIndex, Col.D].SetValue(0);
worksheet.Cells[currentRowIndex, Col.E].SetValue(bankSum);
worksheet.Cells[currentRowIndex, Col.F].SetValue(0);
worksheet.Cells[currentRowIndex, Col.G].SetValue(bankSum);
worksheet.Cells[currentRowIndex, Col.A].Style.Borders[IndividualBorder.Top | IndividualBorder.Bottom].LineStyle = LineStyle.None;
worksheet.Cells[currentRowIndex, Col.A].Style.Borders[IndividualBorder.Left | IndividualBorder.Right].LineStyle = LineStyle.Thin;
worksheet.Cells[currentRowIndex, Col.D].Style = worksheet.Cells[currentRowIndex, Col.A].Style;
worksheet.Cells[currentRowIndex, Col.E].Style = worksheet.Cells[currentRowIndex, Col.A].Style;
worksheet.Cells[currentRowIndex, Col.F].Style = worksheet.Cells[currentRowIndex, Col.A].Style;
worksheet.Cells[currentRowIndex, Col.G].Style = worksheet.Cells[currentRowIndex, Col.A].Style;
currentRowIndex++;
}
worksheet.Cells.GetSubrange($"A{tableRowIndex}", $"G{tableRowIndex + budgets.Count()}").Style.Borders[IndividualBorder.Top | IndividualBorder.Bottom].LineStyle = LineStyle.Thin;
worksheet.Cells.GetSubrange($"D{tableRowIndex + budgets.Count() + 1}", $"G{tableRowIndex + budgets.Count() + 1}").Style.Borders[IndividualBorder.Top].LineStyle = LineStyle.Thick;
var contentStream = new MemoryStream();
worksheet.PrintOptions.PaperType = PaperType.A4;
workbook.Save(contentStream, SaveOptions.PdfDefault);
return(File(contentStream, "application/pdf", $"OverAllReport-{month:yyyyMMdd}.pdf"));
}
