/// <summary> /// Writes dataMap to excel spreadsheet. /// Compounds are in the first or second column, sample names are in the nameRow. /// Column 1 are compound names without MRM, column 2 has MRM /// </summary> /// <param name="progressWindow"></param> /// <param name="dataMap"></param> /// <param name="filePath"></param> public static void WriteMapToSheetCompoundsInRows(ProgressWindow progressWindow, Dictionary <string, Dictionary <string, string> > dataMap, string filePath, string tabName, int nameRow) { // Open workbook and specified tab var excelPkg = new ExcelPackage(new FileInfo(filePath)); var outputSheet = excelPkg.Workbook.Worksheets[tabName]; // Info to write var compoundList = new List <string>(dataMap.Keys); var sampleList = new List <string>(dataMap[compoundList[0]].Keys); int numSamples = sampleList.Count; int rows = outputSheet.Dimension.Rows; // Write sample names in nameRow, starting from col 5 for (int col = 5; col < numSamples + 5; ++col) { outputSheet.Cells[nameRow, col].Value = sampleList[col - 5]; } // Write each compound (row) for (int row = nameRow + 1; row <= rows; ++row) { for (int col = 5; col < numSamples + 5; col++) { // Write peak area corresponding to compound and sample name // Compounds in col 1 or col 2 depending on MRM var compound = outputSheet.Cells[row, 1].Value.ToString(); if (compound != null && dataMap.ContainsKey(compound)) { // dataMap[compound] get value of sample name in row 1 which is data dataMap[compound].TryGetValue(outputSheet.Cells[1, col].Value.ToString(), out var data); if (double.TryParse(data, out double dataNum)) { outputSheet.Cells[row, col].Value = dataNum; } else { outputSheet.Cells[row, col].Value = data; } } // Compound names with MRM are in col 2 var compoundMRM = outputSheet.Cells[row, 2].Value.ToString(); if (compoundMRM != null && dataMap.ContainsKey(compoundMRM)) { // dataMap[compoundMRM] get value of sample name in row 1 which is data dataMap[compoundMRM].TryGetValue(outputSheet.Cells[1, col].Value.ToString(), out var data); if (double.TryParse(data, out double dataNum)) { outputSheet.Cells[row, col].Value = dataNum; } else { outputSheet.Cells[row, col].Value = data; } } } } outputSheet.Cells[2, 5, rows, numSamples].Style.Numberformat.Format = "0"; outputSheet.Cells[2, 5, rows, numSamples].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; excelPkg.SaveAs(new FileInfo(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + $"\\DataProcessor\\sciex6500_output.xlsx")); //SaveFile(excelPkg, outputFileName); }
private void submitButton_Click(object sender, EventArgs e) { if (filePath == string.Empty) { return; } var progressWindow = new ProgressWindow(); progressWindow.Show(); // Create isotope map Dictionary <string, List <string> > isotopeMap = null; IsotopeCalc isotopeCalc = new IsotopeCalc(); //isotopeMap = isotopeCalc.IsotopeMap(filePath); // Read data to map Dictionary <string, Dictionary <string, string> > dataMap = null; if (skylineRadioButton.Checked) { dataMap = ProcessSkyline.ReadDataToMap(filePath); if (dataMap.Count == 0) { return; } // Create isotope map isotopeMap = isotopeCalc.IsotopeMap(filePath); } else if (sciexRadioButton.Checked) { dataMap = ProcessSciex.ReadDataToMap(filePath); if (dataMap.Count == 0) { return; } } else if (NormQcRadioButton.Checked) { dataMap = ProcessSciex.ReadDataToMap(filePath); //Normalize.NormalizeToQC(filePath, dataMap); } else if (multiquantTxtRadioButton.Checked) { dataMap = ReadInput.ReadMultiQuantTxt(filePaths); var templatePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + $"\\DataProcessor\\targeted_300_template-serum-mrm.xlsx"; WriteOutputFile.WriteMapToSheetCompoundsInRows(progressWindow, dataMap, templatePath, "Relative Quant Data", 1); //WriteOutputFile.WriteMapToSheetCompoundsInRows(progressWindow, dataMap, templatePath, "Data Reproducibility", 2); } if (dataMap == null) { progressWindow.progressTextBox.AppendLine("Error reading file."); return; } progressWindow.progressTextBox.AppendLine("Finished reading data.\r\nWriting data"); bool removeNA = removeMissingCheckBox.Checked; var missingValPercent = string.IsNullOrEmpty(missingValueBox.Text) ? missingValueBox.PlaceholderText : missingValueBox.Text; bool replaceNA = replaceMissingValueCheckBox.Checked; var missingValReplace = string.IsNullOrEmpty(replaceMissingValueTextBox.Text) ? replaceMissingValueTextBox.PlaceholderText : replaceMissingValueTextBox.Text; //WriteOutputFile.WriteSciex(replaceNA, missingValReplace, progressWindow, dataMap, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + $"\\DataProcessor\\targeted_300_template-tissue.xlsx"); WriteOutputFile.Run(removeNA, replaceNA, missingValPercent, missingValReplace, progressWindow, dataMap, isotopeCalc); }