Interaction with Excel files

[Kasper.Vestrup]

Experior provides some methods to read data from an Excel file and to write data into an Excel file.

To do this you can use the Experior.Core.Data.Excel class with the following methods.

public static List<List<string>> Read(string filename, string sheetname);
public void Write(string filename, string sheetname, List<List<string>> records);

There is also the possibility to verify whether a worksheet exists in a an Excel file with a given name.

public static bool Exists(string filename, string sheetname);

By using the classes Excel, ExcelWorkbook, ExcelWorkSheet, from the namespace Experior.Core.Data.OfficeOpenXml you can obtain even more direct control.

For example.

string filePath = Experior.Core.Directories.Model + "\\Simulation_results_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour.ToString() + "_" + DateTime.Now.Minute.ToString() + "_.xlsx";
// create a new Excel workbook with the given filename and add a Worksheeet named "Simulation results"
Experior.Core.Data.OfficeOpenXml.Excel e = new Core.Data.OfficeOpenXml.Excel(new System.IO.FileInfo(filePath));
Experior.Core.Data.OfficeOpenXml.ExcelWorksheet worksheet = e.Workbook.Worksheets.Add("Simulation results");
// write the data into the cells of the worksheet
worksheet.Cells[1, 1].Value = "Total occupation time conveyor system";
worksheet.Cells[1, 2].Value = swTotalOnConv.elapsed.ToString();
worksheet.Cells[2, 1].Value = "Waiting time drivers";
for (int i = 0; i < driverWaitingTime.Count; i++)
{
    worksheet.Cells[3 + i, 1].Value = "Waiting time after pattern " + (i + 1).ToString();
    worksheet.Cells[3 + i, 1].Value = driverWaitingTime[i].elapsed.ToString("c");
}

// Add another worksheet named "Simulated Pattern"
Experior.Core.Data.OfficeOpenXml.ExcelWorksheet worksheetPattern = e.Workbook.Worksheets.Add("Simulated pattern");
r = 1;
c = 1;

foreach (List<string> rows in SimulatedPatternSheet)
{
    c = 1;
    foreach (string cell in rows)
    {
        worksheetPattern.Cells[r, c].Value = cell;
        c++;
    }
    r++;
}

//save the Excel workbook file
e.Save();