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 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;
}
}
// Data Reproducibility
// Copy "Relative Quant Data" tab, remove all non-QC
// Absolute Quant Calc with template
public static ExcelPackage AbsoluteQuantCalc(
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.SaveAs(new FileInfo(options["OutputFolder"] + "\\" + options["OutputFileName"]));
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";
//calcSheet.Hidden = eWorkSheetHidden.Hidden;
//excelPkg.SaveAs(new FileInfo(options["OutputFolder"] + "\\" + options["OutputFileName"]));
excelPkg.Save();
//progressPage.ProgressTextBox.AppendText("Finished writing into template\n");
return(excelPkg);
}
/// <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);
}