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