// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
app.UseSwagger();
app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "Orsted Employee API v1"));
}
app.UseHttpsRedirection();
app.UseRouting();
app.UseCors("CorsPolicy");
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
// https://github.com/ExcelDataReader/ExcelDataReader#important-note-on-net-core
ExcelDataReaderHelper.AddEncodingSupport();
}
public void GetRowsFromDataSheets_With_NonExistent_Excel_File_Throws_FileNotFoundException(string nonExistentExcelFileName)
{
Assert.That(
() => ExcelDataReaderHelper.GetRowsFromDataSheets(nonExistentExcelFileName),
Throws.InstanceOf <FileNotFoundException>()
);
}
public void GetRowsFromDataSheets_With_NonExcel_Filename_Throws_InvalidOperationException(string nonExcelFileName)
{
Assert.That(
() => ExcelDataReaderHelper.GetRowsFromDataSheets(nonExcelFileName),
Throws.InstanceOf <InvalidOperationException>()
);
}
/// <summary>
/// Reads the excel file.
/// </summary>
/// <param name="filename">Filename of the excel file.</param>
static void ReadExcelFileExample(string filename)
{
Console.WriteLine("\n\n{0}\nReading from excel file: {1}\n{0}", new string('-', Math.Max(Console.WindowWidth - 1, 79)), filename);
using (ExcelDataReaderHelper excelHelper = new ExcelDataReaderHelper(filename))
{
ReadExcelExample(excelHelper);
}
}
public void GetRowsFromDataSheets_Get_All_Rows_From_All_Data_Sheets(string excelFileName)
{
var rows = ExcelDataReaderHelper.GetRowsFromDataSheets(excelFileName);
Assert.AreEqual(3, rows.Count());
for (int i = 0; i < rows.Count(); i++)
{
Assert.AreEqual(String.Format("baz{0}", i + 1), rows.ElementAt(i)["foo"]);
Assert.AreEqual(String.Format("qux{0}", i + 1), rows.ElementAt(i)["bar"]);
}
}
public static IEnumerable <GoalExcel> ReadFromXLSX(string fullPath)
{
List <GoalExcel> result = new List <GoalExcel>();
using (var stream = File.OpenRead(fullPath))
{
using (ExcelDataReaderHelper excelHelper = new ExcelDataReaderHelper(stream))
{
result = excelHelper.GetRange <GoalExcel>(0, 1, 1).ToList();
}
}
return(result);
}
public void GetRowsFromDataSheets_With_Empty_Filename_Throws_ArgumentNullException()
{
string nullInput = null;
string emptyInput = String.Empty;
Assert.That(
() => ExcelDataReaderHelper.GetRowsFromDataSheets(nullInput),
Throws.InstanceOf <ArgumentNullException>()
);
Assert.That(
() => ExcelDataReaderHelper.GetRowsFromDataSheets(emptyInput),
Throws.InstanceOf <ArgumentNullException>()
);
}
private static IEnumerable <Entry> GetHskListForLevel(string fileName)
{
using (var list = typeof(PopupChineseHskWordListImporter).GetEmbeddedFile(fileName))
{
var helper = new ExcelDataReaderHelper(list);
foreach (var row in helper.GetRangeCells(0, 2, 2, 5))
{
var result = new Entry
{
Simplified = (string)row[0],
Traditional = (string)row[1],
Pinyin = SplitPinyin((string)row[2]),
Definition = (string)row[3]
};
result.AddPartOfSpeech((string)row[4]);
yield return(result);
}
}
}
/// <summary>
/// Read zipped excel files.
/// </summary>
/// <param name="filename">Filename of the zip file containing one or more excel files.</param>
public static void ReadZippedExcelFileExample(string filename)
{
Console.WriteLine("\n\n{0}\nReading from excel zip file: {1}\n{0}", new string('-', Math.Max(Console.WindowWidth - 1, 79)), filename);
using (var stream = new FileStream(filename, FileMode.Open))
{
using (var zipArchive = new ZipArchive(stream, ZipArchiveMode.Read, true))
{
foreach (ZipArchiveEntry entry in zipArchive.Entries)
{
using (var zipStream = entry.Open())
{
using (ExcelDataReaderHelper excelHelper = new ExcelDataReaderHelper(zipStream))
{
ReadExcelExample(excelHelper);
}
}
}
}
}
}
/// <summary>
/// Example method showing how to use the <see cref="Excel.ExcelReaderHelper"/> for reading:
/// - Untyped jagged array using <see cref="GetRangeCells"/>
/// - Typed jagged array using <see cref="GetRangeCells<T>"/>
/// - Typed objects with values mapped to properties using <see cref="GetRange<T>"/>
/// </summary>
/// <param name="excelHelper">The <see cref="Excel.Helper.ExcelDataReaderHelper"/>.</param>
static void ReadExcelExample(ExcelDataReaderHelper excelHelper)
{
// worksheet info
Console.WriteLine("\nNumber of Worksheets: {0} ({1})", excelHelper.WorksheetCount, string.Join(", ", excelHelper.WorksheetNames));
// values
Console.WriteLine("\nValues from sheet 'values':");
object[][] values = excelHelper.GetRangeCells("values", 1, 1);
Console.WriteLine(string.Join("\n", values.Select(rowValues => string.Join(", ", rowValues))));
// numbers
Console.WriteLine("\nInt values from sheet 'numbers':");
int[][] numbers = excelHelper.GetRangeCells <int>("numbers", 1, 1);
Console.WriteLine(string.Join("\n", numbers.Select(rowValues => string.Join(", ", rowValues))));
// orders
Console.WriteLine("\nOrders from sheet 'orders':");
Order[] orders = excelHelper.GetRange <Order>("orders", 1, 3);
Console.WriteLine(string.Join("\n", orders.Select(x => x.ToString())));
}
/// <summary>
/// Example method showing how to use the <see cref="Excel.ExcelReaderHelper"/> for reading:
/// - Untyped jagged array using <see cref="GetRangeCells"/>
/// - Typed jagged array using <see cref="GetRangeCells<T>"/>
/// - Typed objects with values mapped to properties using <see cref="GetRange<T>"/>
/// </summary>
/// <param name="excelHelper">The <see cref="Excel.Helper.ExcelDataReaderHelper"/>.</param>
static void ReadExcelExample(ExcelDataReaderHelper excelHelper)
{
// worksheet info
Console.WriteLine ("\nNumber of Worksheets: {0} ({1})", excelHelper.WorksheetCount, string.Join (", ", excelHelper.WorksheetNames));
// values
Console.WriteLine("\nValues from sheet 'values':");
object[][] values = excelHelper.GetRangeCells("values", 1, 1);
Console.WriteLine(string.Join("\n", values.Select(rowValues => string.Join(", ", rowValues))));
// numbers
Console.WriteLine("\nInt values from sheet 'numbers':");
int[][] numbers = excelHelper.GetRangeCells<int>("numbers", 1, 1);
Console.WriteLine(string.Join("\n", numbers.Select(rowValues => string.Join(", ", rowValues))));
// orders
Console.WriteLine("\nOrders from sheet 'orders':");
Order[] orders = excelHelper.GetRange<Order>("orders", 1, 3);
Console.WriteLine(string.Join("\n", orders.Select(x => x.ToString())));
}
/// <summary>
/// Read zipped excel files.
/// </summary>
/// <param name="filename">Filename of the zip file containing one or more excel files.</param>
public static void ReadZippedExcelFileExample(string filename)
{
Console.WriteLine("\n\n{0}\nReading from excel zip file: {1}\n{0}", new string('-', Math.Max(Console.WindowWidth-1, 79)), filename);
using (var stream = new FileStream(filename, FileMode.Open))
{
using (var zipArchive = new ZipArchive(stream, ZipArchiveMode.Read, true))
{
foreach (ZipArchiveEntry entry in zipArchive.Entries)
{
using (var zipStream = entry.Open())
{
using (ExcelDataReaderHelper excelHelper = new ExcelDataReaderHelper (zipStream))
{
ReadExcelExample (excelHelper);
}
}
}
}
}
}
public void GetRowsFromDataSheets_Get_All_Rows_From_All_Data_Sheets_Without_Column_Names(string excelFileName)
{
var rows = ExcelDataReaderHelper.GetRowsFromDataSheets(excelFileName, false);
int dataCounter = 1;
Assert.AreEqual(6, rows.Count());
for (int i = 0; i < rows.Count(); i++)
{
if (i % 2 == 0)
{
Assert.AreEqual(String.Format("foo", i + 1), rows.ElementAt(i)[0]);
Assert.AreEqual(String.Format("bar", i + 1), rows.ElementAt(i)[1]);
}
else
{
Assert.AreEqual(String.Format("baz{0}", dataCounter), rows.ElementAt(i)[0]);
Assert.AreEqual(String.Format("qux{0}", dataCounter), rows.ElementAt(i)[1]);
dataCounter++;
}
}
}
public ExcelReaderTest()
{
ExcelDataReaderHelper.AddEncodingSupport();
}
/// <summary>
/// Reads the excel file.
/// </summary>
/// <param name="filename">Filename of the excel file.</param>
static void ReadExcelFileExample(string filename)
{
Console.WriteLine("\n\n{0}\nReading from excel file: {1}\n{0}", new string('-', Math.Max(Console.WindowWidth-1, 79)), filename);
using (ExcelDataReaderHelper excelHelper = new ExcelDataReaderHelper(filename))
{
ReadExcelExample (excelHelper);
}
}
public ExcelServiceTests()
{
ExcelDataReaderHelper.AddEncodingSupport();
}
