public void ShouldCalculateTwoCriteriaRanges()
{
_sheet.Cells["A5"].Formula = "SUMIFS(A1:A4;B1:B5;\">5\";C1:C5;\">4\")";
_sheet.Calculate();
Assert.AreEqual(9d, _sheet.Cells["A5"].Value);
}
public void ChooseSingleValue()
{
fillChooseOptions();
_worksheet.Cells["B1"].Formula = "CHOOSE(4, A1, A2, A3, A4, A5)";
_worksheet.Calculate();
Assert.AreEqual(5d, _worksheet.Cells["B1"].Value);
}
public void ExactShouldReturnTrueWhenEqualValues()
{
_worksheet.Cells["A2"].Value = 1d;
_worksheet.Cells["A4"].Formula = "EXACT(A1,A2)";
_worksheet.Calculate();
var result = _worksheet.Cells["A4"].Value;
Assert.IsTrue((bool)result);
}
public void Match_Without_Wildcard()
{
_worksheet.Cells["A1"].Value = "test";
_worksheet.Cells["A2"].Value = "value_to_match";
_worksheet.Cells["A3"].Value = "test";
//_worksheet.Cells["A4"].Value
_worksheet.Cells["A4"].Formula = "MATCH(\"value_to_match\", A1:A3, 0)";
_worksheet.Calculate();
Assert.AreEqual(2, _worksheet.Cells["A4"].Value);
}
public void VLookupShouldReturnCorrespondingValue()
{
var lookupAddress = "A1:B2";
_worksheet.Cells["A1"].Value = 1;
_worksheet.Cells["B1"].Value = 1;
_worksheet.Cells["A2"].Value = 2;
_worksheet.Cells["B2"].Value = 5;
_worksheet.Cells["A3"].Formula = "VLOOKUP(2, " + lookupAddress + ", 2)";
_worksheet.Calculate();
var result = _worksheet.Cells["A3"].Value;
Assert.AreEqual(5, result);
}
static void Main(string[] args)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = new ExcelFile();
// Set calculation options.
ef.CalculationOptions.MaximumIterations = 10;
ef.CalculationOptions.MaximumChange = 0.05;
ef.CalculationOptions.EnableIterativeCalculation = true;
// Add new worksheet
ExcelWorksheet ws = ef.Worksheets.Add("Iterative Calculation");
// Some column formatting.
ws.Columns[0].SetWidth(50, LengthUnit.Pixel);
ws.Columns[1].SetWidth(100, LengthUnit.Pixel);
// Simple example of circular reference limited by MaximumIterations in column A.
ws.Cells["A1"].Formula = "=A2";
ws.Cells["A2"].Formula = "=A1 + 1";
// Simple example of circular reference limited by MaximumChange in column B.
ws.Cells["B1"].Value = 100000.0;
ws.Cells["B2"].Formula = "=B3 * 0.03";
ws.Cells["B3"].Formula = "=B1 + B2";
// Calculate all cells.
ws.Calculate();
ef.Save("Iterative Calculation.xlsx");
}
/// <summary>
/// Fill Work Summary report
/// </summary>
/// <param name="worksheet"></param>
/// <param name="simulationDataModels"></param>
/// <param name="bridgeDataModels"></param>
/// <param name="simulationYears"></param>
/// <param name="dbContext"></param>
/// <param name="simulationId"></param>
/// <returns>ChartRowsModel object for usage in other tab reports.</returns>
public ChartRowsModel Fill(ExcelWorksheet worksheet, List <SimulationDataModel> simulationDataModels, List <BridgeDataModel> bridgeDataModels, List <int> simulationYears, BridgeCareContext dbContext, SimulationModel simulationModel, List <string> treatments)
{
var currentCell = new CurrentCell {
Row = 1, Column = 1
};
var yearlyBudgetAmounts = bridgeWorkSummaryData.GetYearlyBudgetAmounts(simulationModel.simulationId, simulationYears, dbContext);
var comittedProjectsData = workSummaryByBudgetData.GetAllCommittedProjects(simulationModel, dbContext);
costBudgetsWorkSummary.FillCostBudgetWorkSummarySections(worksheet, currentCell, simulationYears, simulationDataModels, yearlyBudgetAmounts, treatments, comittedProjectsData);
bridgesCulvertsWorkSummary.FillBridgesCulvertsWorkSummarySections(worksheet, currentCell, simulationYears, simulationDataModels, treatments);
var chartRowsModel = bridgeRateDeckAreaWorkSummary.FillBridgeRateDeckAreaWorkSummarySections(worksheet, currentCell, simulationYears, simulationDataModels);
nhsBridgeDeckAreaWorkSummary.FillNHSBridgeDeckAreaWorkSummarySections(worksheet, currentCell, simulationYears, simulationDataModels, bridgeDataModels, chartRowsModel);
chartRowsModel = deckAreaBridgeWorkSummary.FillPoorDeckArea(worksheet, currentCell, simulationYears, simulationDataModels, bridgeDataModels, chartRowsModel);
//chartRowsModel = postedClosedBridgeWorkSummary.FillPostedBridgeCount(worksheet, currentCell, simulationYears, simulationDataModels, bridgeDataModels, chartRowsModel);
//chartRowsModel = deckAreaBridgeWorkSummary.FillPostedDeckArea(worksheet, currentCell, simulationYears, simulationDataModels, bridgeDataModels, chartRowsModel);
//chartRowsModel = postedClosedBridgeWorkSummary.FillClosedBridgeCount(worksheet, currentCell, simulationYears, simulationDataModels, bridgeDataModels, chartRowsModel);
//chartRowsModel = deckAreaBridgeWorkSummary.FillClosedDeckArea(worksheet, currentCell, simulationYears, simulationDataModels, bridgeDataModels, chartRowsModel);
//chartRowsModel = postedClosedBridgeWorkSummary.FillBridgeCountTotal(worksheet, currentCell, simulationYears, simulationDataModels, bridgeDataModels, chartRowsModel);
chartRowsModel = postedClosedBridgeWorkSummary.FillMoneyNeededByBPN(worksheet, currentCell, simulationYears, simulationDataModels, bridgeDataModels, chartRowsModel);
worksheet.Calculate();
worksheet.Cells.AutoFitColumns();
return(chartRowsModel);
}
private void InputExcelByEPPlus(string newFilePath, List <TemplateConfiguration> configList)
{
try
{
//create a new Excel package from the file
using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo(newFilePath)))
{
//get only the config for this sheet to input
List <TemplateConfiguration> sheetConfig = configList.FindAll(c => ((InputOrOutput)Enum.Parse(typeof(InputOrOutput), c.InputOrOutput) == InputOrOutput.Input));
foreach (TemplateConfiguration config in sheetConfig)
{
if ((config.CellValue == null) || (config.CellLocation == null))
{
continue;
}
if (String.IsNullOrEmpty(config.SheetName))
{
Console.WriteLine("Exception occured at InputDataByInterop: " + config.SheetName);
return;
}
//create an instance of the the first sheet in the loaded file
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[config.SheetName];
int rowIndex = int.Parse(Regex.Match(config.CellLocation, @"[0-9]+").Value);
int columnIndex = ColumnIndex(config.CellLocation);
//insert the data
if (config.OutputDataType == "int")
{
worksheet.Cells[rowIndex, columnIndex].Value = int.Parse(config.CellValue);
}
else if (config.OutputDataType == "float")
{
worksheet.Cells[rowIndex, columnIndex].Value = float.Parse(config.CellValue);
}
else
{
worksheet.Cells[rowIndex, columnIndex].Value = config.CellValue;
}
worksheet.Calculate();
}
//excelPackage.Workbook.Calculate();
//save the changes
excelPackage.Save();
excelPackage.Dispose();
}
}
catch (Exception ex)
{
throw ex;
}
}
public FileResult GetExportFile()
{
var allSupply = _supplyService.GetAllSupplies(string.Empty);
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("Danh sách vật tư");
DataTable dt = new DataTable();
dt.Columns.Add("STT", typeof(int));
dt.Columns.Add("Mã vật tư", typeof(string));
dt.Columns.Add("Tên vật tư", typeof(string));
dt.Columns.Add("Số lượng ", typeof(int));
dt.Columns.Add("Số lượng thực tế ", typeof(int));
dt.Columns.Add("Chênh lệch ", typeof(int));
dt.Columns.Add("Ghi chú", typeof(string));
ws.Columns[0].SetWidth(30, LengthUnit.Pixel);
ws.Columns[1].SetWidth(100, LengthUnit.Pixel);
ws.Columns[2].SetWidth(250, LengthUnit.Pixel);
ws.Columns[3].SetWidth(100, LengthUnit.Pixel);
ws.Columns[4].SetWidth(150, LengthUnit.Pixel);
ws.Columns[5].SetWidth(100, LengthUnit.Pixel);
ws.Columns[6].SetWidth(200, LengthUnit.Pixel);
for (int i = 0; i < allSupply.Count; i++)
{
var item = allSupply[i];
dt.Rows.Add(new object[] { i + 1, item.SuppliesCode, item.SuppliesName, item.Quantity });
}
ws.Cells[0, 0].Value = "Danh sách vật tư trong kho";
// Insert DataTable into an Excel worksheet.
ws.InsertDataTable(dt,
new InsertDataTableOptions()
{
ColumnHeaders = true,
StartRow = 2
});
for (int i = 0; i < allSupply.Count; i++)
{
ws.Cells["F" + (i + 4) + ""].Formula = $"=(D{i+4}-E{i+4})";
ws.Cells["E" + (i + 4) + ""].Style.Locked = false;
ws.Cells["G" + (i + 4) + ""].Style.Locked = false;
ws.Cells["F" + (i + 4) + ""].Calculate();
}
ws.Protected = true;
var protectionSettings = ws.ProtectionSettings;
protectionSettings.AllowInsertingColumns = true;
protectionSettings.SetPassword("123456");
ws.Calculate();
ws.Parent.Calculate();
return(File(GetBytes(ef, SaveOptions.XlsxDefault), SaveOptions.XlsxDefault.ContentType));
}
private static void ExportStyleTables(DirectoryInfo outputFolder, ExcelWorksheet wsStyleTables)
{
//The last row of the cell contains uncalculated cell (they calculate when opened in Excel),
//but in EPPlus we need to calculate them first to get a result in cell A254 in the totals row.
wsStyleTables.Calculate();
var table1 = wsStyleTables.Tables[0];
var htmlExporter = table1.CreateHtmlExporter();
//This sample exports the table as well as some individually cell styles. The headers have font italic and the totals row has a custom formatted text.
//Also note that Column 2 has hyper links create for the mail addresses.
var html = htmlExporter.GetSinglePage();
File.WriteAllText(FileUtil.GetFileInfo(outputFolder, "Table-02-Styling_table1_with_hyperlinks.html", true).FullName,
html);
var table2 = wsStyleTables.Tables[1];
htmlExporter = table2.CreateHtmlExporter();
//Table 2 contains a custom table style.
html = htmlExporter.GetSinglePage();
File.WriteAllText(FileUtil.GetFileInfo(outputFolder, "Table-02-Styling_table2.html", true).FullName,
html);
}
public void ShouldHandleSingleNumericCriteria()
{
_worksheet.Cells["A1"].Value = 1;
_worksheet.Cells["A2"].Value = 1;
_worksheet.Cells["A3"].Value = 2;
_worksheet.Cells["A4"].Formula = "COUNTIFS(A1:A3, 1)";
_worksheet.Calculate();
Assert.AreEqual(2d, _worksheet.Cells["A4"].Value);
}
/// <summary>
/// Sample 1 - simply creates a new workbook from scratch.
/// The workbook contains one worksheet with a simple invertory list
/// </summary>
public void RunSample1()
{
FileInfo newFile = new FileInfo(Path.Combine("", "Sample1.xlsx"));
if (newFile.Exists)
{
newFile.Delete();
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
//Add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory");
//Add the headers
worksheet.Cells[1, 1].Value = "ID";
worksheet.Cells[1, 2].Value = "Product";
worksheet.Cells[1, 3].Value = "Quantity";
worksheet.Cells[1, 4].Value = "Price";
worksheet.Cells[1, 5].Value = "Value";
//Add some items...
worksheet.Cells["A2"].Value = 12001;
worksheet.Cells["B2"].Value = "Nails";
worksheet.Cells["C2"].Value = 37;
worksheet.Cells["D2"].Value = 3.99;
worksheet.Cells["A3"].Value = 12002;
worksheet.Cells["B3"].Value = "Hammer";
worksheet.Cells["C3"].Value = 5;
worksheet.Cells["D3"].Value = 12.10;
worksheet.Cells["A4"].Value = 12003;
worksheet.Cells["B4"].Value = "Saw";
worksheet.Cells["C4"].Value = 12;
worksheet.Cells["D4"].Value = 15.37;
//Add a formula for the value-column
worksheet.Cells["E2:E4"].Formula = "C2*D2";
// //Ok now format the values;
using (var range = worksheet.Cells[1, 1, 1, 5])
{
// Set Font to Bold
range.Style.Font.Bold = true;
// Set BackgroundColor to Gray
range.Style.Fill.PatternType = ExcelFillStyle.Gray0625;
}
// Set Top Border
worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
// Set Font to Bold
worksheet.Cells["A5:E5"].Style.Font.Bold = true;
worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 3, 4, 3).Address);
worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";
// //Create an autofilter for the range
worksheet.Cells["A1:E4"].AutoFilter = true;
worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@"; //Format as text
worksheet.Calculate();
package.Save();
}
}
public void Issue15063()
{
System.IO.FileInfo newFile = new System.IO.FileInfo(@"C:\Temp\bug\TableFormula.xlsx");
ExcelPackage excelP = new ExcelPackage(newFile);
ExcelWorksheet ws = excelP.Workbook.Worksheets[1];
ws.Calculate();
}
static void Main(string[] args)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("Formula Calculation");
// Some formatting.
ExcelRow row = ws.Rows[0];
row.Style.Font.Weight = ExcelFont.BoldWeight;
ExcelColumn col = ws.Columns[0];
col.SetWidth(250, LengthUnit.Pixel);
col.Style.HorizontalAlignment = HorizontalAlignmentStyle.Left;
col = ws.Columns[1];
col.SetWidth(250, LengthUnit.Pixel);
col.Style.HorizontalAlignment = HorizontalAlignmentStyle.Right;
// Use first row for column headers.
ws.Cells["A1"].Value = "Formula";
ws.Cells["B1"].Value = "Calculated value";
// Enter some Excel formulas as text in first column.
ws.Cells["A2"].Value = "=1 + 1";
ws.Cells["A3"].Value = "=3 * (2 - 8)";
ws.Cells["A4"].Value = "=3 + ABS(B3)";
ws.Cells["A5"].Value = "=B4 > 15";
ws.Cells["A6"].Value = "=IF(B5, \"Hello world\", \"World hello\")";
ws.Cells["A7"].Value = "=B6 & \" example\"";
ws.Cells["A8"].Value = "=CODE(RIGHT(B7))";
ws.Cells["A9"].Value = "=POWER(B8, 3) * 0.45%";
ws.Cells["A10"].Value = "=SIGN(B9)";
ws.Cells["A11"].Value = "=SUM(B2:B10)";
// Set text from first column as second row cell's formula.
int rowIndex = 1;
while (ws.Cells[rowIndex, 0].ValueType != CellValueType.Null)
{
ws.Cells[rowIndex, 1].Formula = ws.Cells[rowIndex++, 0].StringValue;
}
// GemBox.Spreadsheet supports single Excel cell calculation, ...
ws.Cells["B2"].Calculate();
// ... Excel worksheet calculation,
ws.Calculate();
// ... and whole Excel file calculation.
ws.Parent.Calculate();
ef.Save("Formula Calculation.xlsx");
}
public void SumX2My2_TwoRanges()
{
_sheet.Cells["A1"].Value = 5;
_sheet.Cells["A2"].Value = 6;
_sheet.Cells["A3"].Value = 7;
_sheet.Cells["B1"].Value = 3;
_sheet.Cells["B2"].Value = 4;
_sheet.Cells["B3"].Value = 2;
_sheet.Cells["C1"].Formula = "SUMX2MY2(A1:A3,B1:B3)";
_sheet.Calculate();
Assert.AreEqual(81d, _sheet.Cells["C1"].Value);
}
public void GenerateInvoices(int contractorId, string name, FileInfo templateFile, int cycle, List <string> fileList)
{
int blankRow = 14;
var workEntries = _WorkRepo.GetWorkExtended(contractorId, cycle, true).Where(x => "SOW".Contains(x.BillType))
.GroupBy(g => new { g.ClientId, g.ProjectId });
using (var templatePackage = new ExcelPackage(templateFile))
{
var templateSheet = templatePackage.Workbook.Worksheets["Invoice"];
var contractor = _AppUserRepo.GetContractor(contractorId);
var file = new FileInfo($"C:\\TEMP\\{name}_Invoices.xlsx");
System.IO.File.Delete(file.FullName);
using (var package = new ExcelPackage(file))
{
foreach (var inv in workEntries)
{
var first = inv.First();
var workBook = package.Workbook;
ExcelWorksheet sheet = workBook.Worksheets.Add($"{first.Client} {first.Project}", templateSheet);
sheet.Cells[2, 6].Value = $"{DateTime.Today: M/d/yyyy}";
sheet.Cells[9, 3].Value = first.Client;
sheet.Cells[10, 3].Value = first.Project;
sheet.Cells[2, 2].Value = contractor.InvoiceName;
sheet.Cells[3, 2].Value = contractor.InvoiceAddress;
sheet.Cells[11, 3].Value = contractor.Rate;
int currentRow = blankRow + 1;
foreach (var entry in inv)
{
sheet.InsertRow(currentRow, 1);
sheet.Cells[blankRow, 1, blankRow, 10].Copy(sheet.Cells[currentRow, 1]);
sheet.Cells[currentRow, 2].Value = $"{entry.WorkDate: M/d}";
sheet.Cells[currentRow, 3].Value = $"{entry.WorkType} : {entry.Descr}";
sheet.Cells[currentRow, 4].Value = entry.Hours;
sheet.Cells[currentRow, 6].Formula = $"=D{currentRow} * C11";
currentRow++;
}
sheet.Cells[currentRow, 4].Formula = $"=SUM(D{blankRow} : D{currentRow - 1})";
sheet.Cells[currentRow, 6].Formula = $"=SUM(F{blankRow} : F{currentRow - 1})";
sheet.Calculate();
}
if (package.Workbook.Worksheets.Any())
{
package.Save();
fileList.Add(package.File.FullName);
}
}
}
}
public void Index_Should_Handle_SingleRange()
{
_worksheet.Cells["A1"].Value = 1d;
_worksheet.Cells["A2"].Value = 3d;
_worksheet.Cells["A3"].Value = 5d;
_worksheet.Cells["A4"].Formula = "INDEX(A1:A3;3)";
_worksheet.Calculate();
Assert.AreEqual(5d, _worksheet.Cells["A4"].Value);
}
static void Main(string[] args)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = ExcelFile.Load("Template.xlsx");
int workingDays = 8;
DateTime startDate = DateTime.Now.AddDays(-workingDays);
DateTime endDate = DateTime.Now;
ExcelWorksheet ws = ef.Worksheets[0];
// Find cells with placeholder text and set their values.
int row, column;
if (ws.Cells.FindText("[Company Name]", true, true, out row, out column))
{
ws.Cells[row, column].Value = "ACME Corp";
}
if (ws.Cells.FindText("[Company Address]", true, true, out row, out column))
{
ws.Cells[row, column].Value = "240 Old Country Road, Springfield, IL";
}
if (ws.Cells.FindText("[Start Date]", true, true, out row, out column))
{
ws.Cells[row, column].Value = startDate;
}
if (ws.Cells.FindText("[End Date]", true, true, out row, out column))
{
ws.Cells[row, column].Value = endDate;
}
// Copy template row.
row = 17;
ws.Rows.InsertCopy(row + 1, workingDays - 1, ws.Rows[row]);
// Fill inserted rows with sample data.
var random = new Random();
for (int i = 0; i < workingDays; i++)
{
ExcelRow currentRow = ws.Rows[row + i];
currentRow.Cells[1].SetValue(startDate.AddDays(i));
currentRow.Cells[2].SetValue(random.Next(1, 12));
}
// Calculate formulas in worksheet.
ws.Calculate();
ef.Save("Template Use.xlsx");
}
private void CreateScoreCardWorksheet(ExcelWorksheet worksheet, IList <ScMeasure> scMeasures, List <MqsMeasure> scoreCardItems, DateTime startDate)
{
worksheet.Cells["A1:A2"].Merge = true;
var header = new Dictionary <string, string>
{
{ "A1", "MQS measure" },
{ "B1", "FYTD" },
{ "B2", "YTD" },
{ "C1", "Actuals" },
{ "C2", "Month" }
};
foreach (var cell in header)
{
HeaderStyle(worksheet.Cells[cell.Key], cell.Value);
}
worksheet.Column(1).Width = 50;
worksheet.Column(2).Width = 15;
worksheet.Column(3).Width = 15;
for (int i = 0; i < scMeasures.Count; i++)
{
worksheet.Cells[i + 3, 1].Value = scMeasures[i].Name;
worksheet.Cells[i + 3, 2].Value = scMeasures[i].YtdCalculatedType.ToString();
worksheet.Cells[i + 3, 3].Value = 0;
if (scMeasures[i].Formula != null && scMeasures[i].Formula.ToLower() == "manualedit")
{
if (scoreCardItems == null) // export for template
{
worksheet.Cells[i + 3, 3].Value = "ManualEdit";
}
else
{
var valueTemp = (scoreCardItems != null && scoreCardItems.Count() > 0) ? scoreCardItems.FirstOrDefault(x => x.ScMeasureId == scMeasures[i].Id && x.Date.Year == startDate.Year && x.Date.Month == startDate.Month) : null;
if (valueTemp != null && !String.IsNullOrEmpty(valueTemp.Value))
{
var v = 0.0d;
double.TryParse(valueTemp.Value, out v);
worksheet.Cells[i + 3, 3].Value = v;
}
else
{
worksheet.Cells[i + 3, 3].Value = null;
}
}
}
else
{
worksheet.Cells[i + 3, 3].CreateArrayFormula(scMeasures[i].Formula);
}
}
worksheet.Calculate();
}
private void WriteGroupWorksheet(
Dictionary <CurriculumGroup, List <TroopMember> > incomplete,
Dictionary <CurriculumGroup, List <TroopMember> > notStarted,
ExcelWorksheet excelWorksheet)
{
List <CurriculumGroup> groups = new List <CurriculumGroup>();
groups.AddRange(incomplete.Keys);
groups.AddRange(notStarted.Keys);
// Output Troop Summary
CellAddress cell = new CellAddress("A1");
foreach (CurriculumGroup key in groups
.Distinct()
.OrderByDescending(k => (incomplete.ContainsKey(k) ? incomplete[k].Count : 0) + (notStarted.ContainsKey(k) ? notStarted[k].Count : 0))
.ThenByDescending(k => (incomplete.ContainsKey(k) ? incomplete[k].Count : 0))
.ThenBy(k => k.GetDisplayName())
)
{
cell.Column = "A";
excelWorksheet.Cells[cell].Value = key.GetDisplayName();
cell.Row += 1;
cell.ColumnNumber += 1;
if (incomplete.ContainsKey(key))
{
foreach (TroopMember scout in incomplete[key].OrderBy(s => s.LastName).ThenBy(s => s.FirstName))
{
excelWorksheet.Cells[cell].Value = $"{(string.IsNullOrWhiteSpace(scout.NickName) ? scout.FirstName : scout.NickName)} {scout.LastName.First()}*";
cell.Row += 1;
}
}
if (notStarted.ContainsKey(key))
{
foreach (TroopMember scout in notStarted[key].OrderBy(s => s.LastName).ThenBy(s => s.FirstName))
{
excelWorksheet.Cells[cell].Value = $"{(string.IsNullOrWhiteSpace(scout.NickName) ? scout.FirstName : scout.NickName)} {scout.LastName.First()}";
cell.Row += 1;
}
}
cell.Row += 1;
}
excelWorksheet.Calculate();
try
{
excelWorksheet.Cells[excelWorksheet.Dimension.Address].AutoFitColumns(10);
}
catch (NullReferenceException)
{
Console.Error.WriteLine($"{excelWorksheet.Name} failed to fit columns");
}
}
public void Issue15172()
{
FileInfo fileInfo = new FileInfo(@"C:\temp\bug\book2.xlsx");
ExcelPackage excelPackage = new ExcelPackage(fileInfo);
{
ExcelWorksheet ws = excelPackage.Workbook.Worksheets[1];
Assert.AreEqual("IF($R10>=X$2,1,0)", ws.Cells["X10"].Formula);
ws.Calculate();
Assert.AreEqual(0D, ws.Cells["X10"].Value);
}
}
private void CalculateWorkbook()
{
if (Excel != null)
{
foreach (var _tempWorksheet in Excel.Workbook.Worksheets)
{
_tempWorksheet.Calculate();
}
}
else
{
WorkSheet.Calculate();
}
}
/// <summary>
/// Helper for displaying and inserting the formula on the sheet + assertion
/// </summary>
public static void Assert(this ExcelWorksheet sheet, string formula, Constraint constraint)
{
if (!_rowIndexes.TryGetValue(sheet, out int row))
{
_rowIndexes.Add(sheet, 2);
row = 2;
}
sheet.Cells["A" + row].Value = "=" + formula.Replace(", ", "; ");
sheet.Cells["B" + row].Formula = formula;
sheet.Calculate();
NUnit.Framework.Assert.That(sheet.Cells["B" + row].Value, constraint);
_rowIndexes[sheet]++;
}
public static void ViewExcel()
{
var path = string.Format(@"{0}\file.xlsx", TEMP_FOLDER);
FileInfo file = new FileInfo(path);
using (var package = new ExcelPackage(file))
{
ExcelWorkbook workBook = package.Workbook;
ExcelWorksheet currentWorksheet = workBook.Worksheets.FirstOrDefault();
int totalRows = currentWorksheet.Dimension.End.Row;
int totalCols = currentWorksheet.Dimension.End.Column;
// get date value from cell
var ahCell = currentWorksheet.Cells["AH5"];
// Date Format: value -> datetime
long dateNum = long.Parse(ahCell.Value.ToString());
DateTime result = DateTime.FromOADate(dateNum);
string strResult = result.ToString("yyyy/MM/dd");
var aiCell = currentWorksheet.Cells["AI5"];
var ajCell = currentWorksheet.Cells["AJ5"];
var akCell = currentWorksheet.Cells["AK5"];
var alCell = currentWorksheet.Cells["AL5"];
currentWorksheet.Cells["AM7"].Formula = "INT(AK7)";
currentWorksheet.Cells["AM8"].Formula = "INT(AK8)";
currentWorksheet.Cells["AM9"].Formula = "INT(AK9)";
currentWorksheet.Cells["AM10"].Formula = "INT(AK10)";
currentWorksheet.Cells["AM11"].Formula = "INT(AK11)";
currentWorksheet.Cells["AM12"].Formula = "INT(AK12)";
currentWorksheet.Cells["AM13"].Formula = "INT(AK13)";
// Calculate() invoke level > FILE / Sheet / Cell
//currentWorksheet.Cells["AM7"].Calculate(); // sample
currentWorksheet.Calculate();
var amVal = currentWorksheet.Cells["AM8"].Value;
var amCell = currentWorksheet.Cells["AM8"];
var aqCell = currentWorksheet.Cells["AQ3"];
package.Save();
}
}
/// <summary>
/// Fill Work Summary report
/// </summary>
/// <param name="worksheet"></param>
/// <param name="simulationDataModels"></param>
/// <param name="bridgeDataModels"></param>
/// <param name="simulationYears"></param>
/// <param name="dbContext"></param>
/// <param name="simulationId"></param>
/// <returns>ChartRowsModel object for usage in other tab reports.</returns>
public ChartRowsModel Fill(ExcelWorksheet worksheet, List <SimulationDataModel> simulationDataModels, List <BridgeDataModel> bridgeDataModels, List <int> simulationYears, BridgeCareContext dbContext, int simulationId)
{
var currentCell = new CurrentCell {
Row = 1, Column = 1
};
var yearlyBudgetModels = bridgeWorkSummaryData.GetYearlyBudgetModels(simulationId, dbContext);
costBudgetsWorkSummary.FillCostBudgetWorkSummarySections(worksheet, currentCell, simulationYears, simulationDataModels, yearlyBudgetModels);
bridgesCulvertsWorkSummary.FillBridgesCulvertsWorkSummarySections(worksheet, currentCell, simulationYears, simulationDataModels);
var chartRowsModel = bridgeRateDeckAreaWorkSummary.FillBridgeRateDeckAreaWorkSummarySections(worksheet, currentCell, simulationYears, simulationDataModels);
nhsBridgeDeckAreaWorkSummary.FillNHSBridgeDeckAreaWorkSummarySections(worksheet, currentCell, simulationYears, simulationDataModels, bridgeDataModels, chartRowsModel);
worksheet.Calculate();
worksheet.Cells.AutoFitColumns();
return(chartRowsModel);
}
private static void LoadWaterfallChartData(ExcelWorksheet ws)
{
ws.SetValue("A1", "Description");
ws.SetValue("A2", "Initial Saldo");
ws.SetValue("A3", "Food");
ws.SetValue("A4", "Beer");
ws.SetValue("A5", "Transfer");
ws.SetValue("A6", "Electrical Bill");
ws.SetValue("A7", "Cell Phone");
ws.SetValue("A8", "Car Repair");
ws.SetValue("B1", "Saldo/transaction");
ws.SetValue("B2", 1000);
ws.SetValue("B3", -237.5);
ws.SetValue("B4", -33.75);
ws.SetValue("B5", 200);
ws.SetValue("B6", -153.4);
ws.SetValue("B7", -49);
ws.SetValue("B8", -258.47);
ws.Cells["B9"].Formula = "SUM(B2:B8)";
ws.Calculate();
ws.Cells.AutoFitColumns();
}
public void ShouldHandleReferenceToOtherSheet()
{
_secondSheet.Cells["A1"].Formula = "SUM('sheet1'!A1:A2)";
_secondSheet.Calculate();
Assert.AreEqual(3d, _secondSheet.Cells["A1"].Value);
}
public void AbsShouldReturn3()
{
_worksheet.Cells["A4"].Formula = "ABS(A2)";
_worksheet.Calculate();
var result = _worksheet.Cells["A4"].Value;
Assert.AreEqual(3d, result);
}
public void RunSample1()
{
FileInfo newFile = new FileInfo(Path.Combine("bin", @"Sample1.xlsx"));
if (newFile.Exists)
{
newFile.Delete();
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory");
//Add the headers
worksheet.Cells[1, 1].Value = "ID";
worksheet.Cells[1, 2].Value = "Product";
worksheet.Cells[1, 3].Value = "Quantity";
worksheet.Cells[1, 4].Value = "Price";
worksheet.Cells[1, 5].Value = "Value";
//Add some items...
worksheet.Cells["A2"].Value = 12001;
worksheet.Cells["B2"].Value = "Nails";
worksheet.Cells["C2"].Value = 37;
worksheet.Cells["D2"].Value = 3.99;
worksheet.Cells["A3"].Value = 12002;
worksheet.Cells["B3"].Value = "Hammer";
worksheet.Cells["C3"].Value = 5;
worksheet.Cells["D3"].Value = 12.10;
worksheet.Cells["A4"].Value = 12003;
worksheet.Cells["B4"].Value = "Saw";
worksheet.Cells["C4"].Value = 12;
worksheet.Cells["D4"].Value = 15.37;
//Add a formula for the value-column
worksheet.Cells["E2:E4"].Formula = "C2*D2";
//Ok now format the values;
using (var range = worksheet.Cells[1, 1, 1, 5])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
}
worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A5:E5"].Style.Font.Bold = true;
worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 3, 4, 3).Address);
worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";
//Create an autofilter for the range
worksheet.Cells["A1:E4"].AutoFilter = true;
worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@"; //Format as text
//There is actually no need to calculate, Excel will do it for you, but in some cases it might be useful.
//For example if you link to this workbook from another workbook or you will open the workbook in a program that hasn't a calculation engine or
//you want to use the result of a formula in your program.
worksheet.Calculate();
worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells
// lets set the header text
worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";
// add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.OddFooter.RightAlignedText =
string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
// add the sheet name to the footer
worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
// add the file path to the footer
worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];
// Change the sheet view to show it in page layout mode
worksheet.View.PageLayoutView = true;
// set some document properties
package.Workbook.Properties.Title = "Invertory";
package.Workbook.Properties.Author = "Jan Källman";
package.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel 2007 workbook using EPPlus";
// set some extended property values
package.Workbook.Properties.Company = "AdventureWorks Inc.";
// set some custom property values
package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jan Källman");
package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
// save our new workbook and we are done!
package.Save();
}
}
public void WorksheetShouldCalculateAll()
{
_workSheet.Calculate(_package);
Assert.AreEqual(60d, _workSheet.Cells["A12"].Value);
}