public static ExcelPackage MergeWorkbooks(ExcelPackage srcExcel, ExcelPackage destExcel, Dictionary <string, string> options) { var(startRow, startCol) = ExcelUtils.GetRowCol(options["StartInCell"]); // Merge one sheet at a time foreach (var srcSheet in srcExcel.Workbook.Worksheets) { var sheetName = srcSheet.Name; var destSheet = destExcel.Workbook.Worksheets[sheetName]; // Read source sheet into map if (options["SamplesIn"] == "rows") { // Location of sample and compound names var compoundRow = ExcelUtils.GetRowNum(options["CompoundLoc"]); var sampleCol = ExcelUtils.GetColNum(options["SampleLoc"]); // Read source sheet data var srcMap = ExcelToMap.SamplesInRowsToMap(compoundRow, sampleCol, srcExcel, sheetName); // Row to start writing data in destination sheet var nextEmptyRow = ExcelUtils.RowsInColumn(destSheet, sampleCol) + 1; // Write into destination sheet // Write sample names destExcel = MapToExcel.WriteSamplesInRows(srcMap, destExcel, sheetName, sampleCol, nextEmptyRow); // Write data into template destExcel = MapToExcel.WriteIntoTemplate(srcMap, destExcel, options, sheetName, false, nextEmptyRow, startCol, sampleCol, compoundRow); } } return(destExcel); }
public static OrderedDictionary ReadInputs(string menuSelection, List <string> filePathList, Dictionary <string, string> options, ProgressPage progressPage) { var dataMap = new OrderedDictionary(); switch (menuSelection) { case "Sciex6500": dataMap = options["InputType"] == "text" ? ReadMultiQuantTextInput.ReadMultiQuantText(filePathList, options, progressPage) : ExcelToMap.ReadAllFiles(filePathList, options); break; case "Lipidyzer": // Read one book at a time, write into template break; default: break; } return(dataMap); }
public static OrderedDictionary ReadMultiQuantText( List <string> filePaths, Dictionary <string, string> options, ProgressPage progressPage) { // <compound, <sample name, data>> var dataMap = new OrderedDictionary(); /* Since text files can only be read row by row, first write the * data to an excel sheet and then read it into the data map. * This cross references the sample ID and compound name when storing the data. */ // Create "Import" tab in data template ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // EPPlus license var templateFile = new FileInfo(options["TemplatePath"]); var excelPkg = new ExcelPackage(templateFile); var worksheet = excelPkg.Workbook.Worksheets.Add("Import"); int row = 1, col = 1, namesRow = 1; // One file at a time. Descending order makes POS before NEG // Keep ascending so samples are in order for multiple files? foreach (var filePath in filePaths) //.OrderByDescending(i => i)) { progressPage.ProgressTextBox.AppendText($"Reading file {Path.GetFileName(filePath)}\n"); // Read data from txt file to worksheet string[] lines = File.ReadAllLines(filePath); /* If samples in columns (MultiQuant export "Transposed" checked), * first line contains sample names with POS or NEG. */ /* If samples in rows (MultiQuant export "Transposed" not checked), * first line contains compound names. */ string[] names = lines[0].Split("\t"); foreach (var name in names) { // Write name to column // If samples in columns, name is sample name, remove POS or NEG worksheet.Cells[namesRow, col++].Value = options["SamplesIn"] == "columns" ? Regex.Replace(name, "^POS_|_POS$|^NEG_|_NEG$", "", RegexOptions.IgnoreCase) : name; } // Done with first line, increment row and reset column ++row; col = 1; // Write remaining lines for (var i = 1; i < lines.Length; ++i) { var line = lines[i]; string[] words = line.Split("\t"); //// If samples in rows, first word is sample name, remove POS or NEG if (options["SamplesIn"] == "rows") { words[0] = Regex.Replace(words[0], "^POS_|_POS$|^NEG_|_NEG$", "", RegexOptions.IgnoreCase); } // Write to cell, increment column after writing foreach (var word in words) { worksheet.Cells[row, col++].Value = word; } // Done with line, increment row and reset column ++row; col = 1; } var curMap = new OrderedDictionary(); // Read data into current map <compound, <sample name, data> curMap = options["SamplesIn"] == "rows" ? ExcelToMap.SamplesInRowsToMap(namesRow, 1, excelPkg, "Import") : ExcelToMap.SamplesInColumnsToMap(namesRow, 1, excelPkg, "Import"); // Remove "Sample Type" if that option was used. It is not a compound. try { curMap.Remove("Sample Type"); } catch { } // Merge current map with dataMap dataMap = Merge.MergeMaps(dataMap, curMap); /* At the end of each file, the next file's name row is the next row. * This is important to make sure the dataMap and curMap aren't * affected by mismatched sample order between the two files. */ namesRow = row; } excelPkg.SaveAs(new FileInfo(options["OutputFolder"] + "\\" + "import_" + options["OutputFileName"])); progressPage.ProgressTextBox.AppendText("All input files have been read.\n"); return(dataMap); }