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); }