public static void Generate(string LabInfoFileName, string LabDataFileName, string GeneratedFileName)
{
var elementInformation = new ElementInformation(LabInfoFileName);
var chemistryInformation = new ChemistryInformation(LabDataFileName, elementInformation);
Generate(GeneratedFileName, chemistryInformation);
}
public ChemistryInformation(string filename, ElementInformation elementInformation)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string element;
int rCnt;
int cCnt;
int rw = 0;
int cl = 0;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets["Report Face"];
range = xlWorkSheet.UsedRange;
rw = 98;
cl = 14;
chemistryInformation = new List <ChemistryInfoModel>();
string family = string.Empty;
rCnt = 3;
int order = 0;
while (rCnt < rw)
{
element = GetValue(range.Cells[rCnt, 1] as Excel.Range).Trim();
if (string.IsNullOrEmpty(element))
{
while (GetValue(range.Cells[rCnt, 1] as Excel.Range) == string.Empty)
{
rCnt++;
}
family = GetValue(range.Cells[rCnt, 1] as Excel.Range).Trim();
order++;
rCnt = rCnt + 1;
element = GetValue(range.Cells[rCnt, 1] as Excel.Range).Trim();
if (string.IsNullOrEmpty(element))
{
element = family;
rCnt = rCnt - 1;
}
}
string Unit = string.Empty;
string Type = string.Empty;
double eql = double.NaN;
var str = GetValue(range.Cells[rCnt, 2] as Excel.Range);
if (str.Equals("surr.", StringComparison.InvariantCultureIgnoreCase))
{
Unit = "%";
Type = "SUR";
}
else if (str.Equals("%", StringComparison.InvariantCultureIgnoreCase))
{
Unit = "%";
Type = "REG";
}
else
{
double.TryParse(str, out eql);
Unit = "mg/kg";
Type = "REG";
}
for (cCnt = 3; cCnt <= cl; cCnt++)
{
var chemistryInfo = new ChemistryInfoModel();
chemistryInfo.Order = order;
chemistryInfo.Element = element;
chemistryInfo.Result_Unit = chemistryInfo.EQL_Units = Unit;
chemistryInfo.Result_Type = Type;
if (eql != double.NaN)
{
chemistryInfo.EQL = eql;
}
else
{
chemistryInfo.EQL = null;
}
chemistryInfo.SampleCode = GetValue(range.Cells[1, cCnt] as Excel.Range);
var result = GetValue(range.Cells[rCnt, cCnt] as Excel.Range);
ParseResult(result, chemistryInfo);
var key = family + element;
chemistryInfo.ChemCode = elementInformation[key].ChemCode;
chemistryInfo.MethodName = elementInformation[key].MethodName;
chemistryInfo.MethodType = elementInformation[key].MethodType;
chemistryInformation.Add(chemistryInfo);
}
rCnt++;
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}