public static void WriteOutput(string menuSelection, OrderedDictionary dataMap,
Dictionary <string, string> options, ProgressPage progressPage)
{
// Get template and save as output file
var excelPkg = new ExcelPackage(new FileInfo(options["TemplatePath"]));
excelPkg.SaveAs(new FileInfo(options["OutputFolder"] + "\\" + options["OutputFileName"]));
var(startRow, startCol) = ExcelUtils.GetRowCol(options["StartInCell"]);
// Write Raw Data tab
if (options["SamplesOut"] == "columns")
{
excelPkg = MapToExcel.WriteSamplesInColumns(dataMap, excelPkg, "Raw Data", options);
}
else
{
// TODO - fix this. Method was separated/changed
//MapToExcel.WriteSamplesInRows(dataMap, excelPkg, "Raw Data", options);
//MapToExcel.WriteCompoundsInColumns
// Write to template if selected
progressPage.ProgressTextBox.AppendText("Writing data into template\n");
}
excelPkg = MapToExcel.WriteIntoTemplate(dataMap, excelPkg, options, options["TemplateTabName"]);
// QC - Copy data tab, remove non-QC, calculate CV
progressPage.ProgressTextBox.AppendText("Calculating QC CV\n");
excelPkg = QualityControl.WriteQCTab(excelPkg, options);
// Absolute Quant Calc
progressPage.ProgressTextBox.AppendText("Absolute Quantitation\n");
var compoundLoc = int.TryParse(options["CompoundLoc"], out var compoundLocNum)
? compoundLocNum
: ExcelUtils.ColumnNameToNumber(options["CompoundLoc"]);
excelPkg = MapToExcel.WriteIntoTemplate(dataMap, excelPkg, options, options["AbsoluteQuantTabName"], false, 2, 3, 1, compoundLoc);
excelPkg = AbsoluteQuant.Sciex6500Template(excelPkg, options, compoundLoc);
progressPage.ProgressTextBox.AppendText("Finished writing Absolute Quant Tab\n");
switch (menuSelection)
{
case "Sciex6500":
break;
case "Lipidyzer":
break;
default:
break;
}
}
/// <summary>
/// Absolute Quant Calc with Sciex 6500 template
/// </summary>
/// <param name="excelPkg"></param>
/// <param name="options"></param>
/// <param name="compoundLoc"></param>
/// <returns></returns>
public static ExcelPackage Sciex6500Template(
ExcelPackage excelPkg, Dictionary <string, string> options, int compoundLoc)
{
var calcSheet = excelPkg.Workbook.Worksheets[options["AbsoluteQuantTabName"]];
var writeSheet = excelPkg.Workbook.Worksheets["Absolute Quant Data"];
OrderedDictionary concMap = new OrderedDictionary();
// Get approximate bounds
var rows = ExcelUtils.RowsInColumn(calcSheet, compoundLoc);
var cols = ExcelUtils.ColumnsInRow(calcSheet, 1);
// Fill in formulas and calculate concentration
for (int row = 4; row <= rows; ++row)
{
// Calculate row with "Concentration (uM)"
var compound = calcSheet.Cells[row, compoundLoc]?.Value?.ToString();
if ("Concentration (uM)".Equals(compound))
{
// Change "Concentration (uM)" to compound name
compound = calcSheet.Cells[row - 2, compoundLoc]?.Value?.ToString();
calcSheet.Cells[row, compoundLoc].Value = compound;
if (!concMap.Contains(compound))
{
concMap.Add(compound, new OrderedDictionary());
}
// Copy formula into empty cells and calculate
for (int col = 2; col <= cols; ++col)
{
var cell = calcSheet.Cells[row, col]?.Value?.ToString();
if (cell is null || cell.Length < 1)
{
calcSheet.Cells[row, col - 1].Copy(calcSheet.Cells[row, col]);
}
calcSheet.Cells[row, col].Calculate();
try
{
var sampleName = calcSheet.Cells[1, col]?.Value?.ToString();
if (sampleName is null || sampleName == "Compound")
{
continue;
}
if (((OrderedDictionary)concMap[compound]).Contains(sampleName))
{
sampleName = Merge.RenameDuplicate(((OrderedDictionary)concMap[compound]).Keys,
sampleName);
}
var conc = calcSheet.Cells[row, col]?.Value?.ToString();
((OrderedDictionary)concMap[compound]).Add(sampleName, conc);
}
catch
{
}
}
}
}
// Save calculations
excelPkg.Save();
// Write concentrations to "Absolute Quant Data" tab
excelPkg = MapToExcel.WriteIntoTemplate(concMap, excelPkg, options, "Absolute Quant Data");
// Format to 3 decimal places
var writeCols = ExcelUtils.ColumnsInRow(writeSheet, 1);
writeSheet.Cells[1, 4, 31, writeCols].Style.Numberformat.Format = "0.000";
excelPkg.Save();
// Insert CV columns for QC
excelPkg = QualityControl.InsertCVColumns(excelPkg, options, "Absolute Quant Data");
excelPkg.Save();
// Replace missing values with N/A
excelPkg = MissingValue.ReplaceMissing(excelPkg, "Absolute Quant Data", "N/A", options["StartInCell"]);
excelPkg.Save();
// Write "Absolute Concentration (mM or micromoles/Liter)" above samples
return(excelPkg);
}
