private XlsReadRequest ReadRequestBlock(StreamReader sr)
{
while (!sr.EndOfStream)
{
var line = sr.ReadLine();
if (line.StartsWith(COMMENT))
{
continue;
}
if (line.StartsWith(RULES_SECTION_NAME))
{
return(null);
}
if (line.StartsWith(SHEET_NAME))
{
var settingLine = line.Substring(SHEET_NAME.Length);
var splitSettings = settingLine.Split(",");
if (splitSettings.Length != 3)
{
Console.WriteLine($"I don't know how to parse setting line {0}", settingLine);
return(null);
}
var request = new XlsReadRequest();
request.SheetName = splitSettings[0];
request.KeysColumn = Int32.Parse(splitSettings[1]);
request.ValuesColumn = Int32.Parse(splitSettings[2]);
Console.WriteLine(string.Format("From Excel sheet {0} read columns {1} as keys and {2} as values:",
request.SheetName, request.KeysColumn, request.ValuesColumn));
return(request);
}
}
return(null);
}
public Dictionary <string, string> Read(string filePath, XlsReadRequest request)
{
var fileExists = File.Exists(filePath);
if (!fileExists)
{
Console.WriteLine($"No file found at {0}", filePath);
return(null);
}
var data = new Dictionary <string, string>();
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
// Auto-detect format, supports:
// - Binary Excel files (2.0-2003 format; *.xls)
// - OpenXml Excel files (2007 format; *.xlsx)
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
Console.WriteLine(string.Format("Read from Excel sheet {0}:", request.SheetName));
do
{
if (!string.Equals(request.SheetName, reader.Name))
{
continue;
}
while (reader.Read())
{
ReadRow(reader, data, request);
}
} while (reader.NextResult());
Console.WriteLine();
}
}
return(data);
}
private void ReadRow(IExcelDataReader reader, Dictionary <string, string> data, XlsReadRequest request)
{
var key = ParseCell(reader, request.KeysColumn);
if (string.IsNullOrEmpty(key))
{
return;
}
var val = ParseCell(reader, request.ValuesColumn);
if (!string.IsNullOrEmpty(val))
{
data[key] = val;
Console.WriteLine(string.Format("{0} : {1}", key, val));
}
}