/// <summary>
/// Writes data into a new Excel tab with samples in columns (in row 1)
/// and compounds in rows (in column 1)
/// </summary>
/// <param name="dataMap"></param>
/// <param name="options"></param>
/// <param name="excelPkg"></param>
/// <param name="tabName"></param>
/// <param name="progressPage"></param>
/// <returns></returns>
public static ExcelPackage WriteSamplesInColumns(
OrderedDictionary dataMap, ExcelPackage excelPkg, string tabName,
Dictionary <string, string> options, int startRow = 2, int startCol = 2)
{
// Create new tab or write in existing tab
ExcelWorksheet worksheet;
try
{
worksheet = excelPkg.Workbook.Worksheets.Add(tabName);
}
catch
{
worksheet = excelPkg.Workbook.Worksheets[tabName];
}
// First cell
worksheet.Cells[1, 1].Value = "Compound";
// Write compound names in column, starting in startRow
var compoundCol = ExcelUtils.GetColNum(options["CompoundLoc"]);
var r = startRow;
foreach (string compoundName in dataMap.Keys)
{
worksheet.Cells[r++, compoundCol].Value = compoundName;
}
// Write sample names in row 1, starting in column 2
var sampleRow = ExcelUtils.GetRowNum(options["SampleLoc"]);
var c = startCol;
foreach (string sampleName in ((OrderedDictionary)dataMap[0]).Keys)
{
worksheet.Cells[sampleRow, c++].Value = sampleName;
}
// Write data into cells corresponding with sample and compound names
excelPkg = WriteIntoTemplate(dataMap, excelPkg, options, tabName, false, startRow, startCol);
// Save file
excelPkg.SaveAs(new FileInfo(options["OutputFolder"] + "\\" + options["OutputFileName"]));
return(excelPkg);
}
/// <summary>
/// Returns the location of sample names and starting data cell.
/// </summary>
/// <param name="rowsOrColumns"></param>
/// <param name="writeDataInTemplate"></param>
/// <param name="sampleLoc"></param>
/// <param name="startInCell"></param>
/// <returns>(row, col) numbers as a Tuple</returns>
public static (int, int) GetNameLocStartLoc(
string rowsOrColumns, string writeDataInTemplate, string sampleLoc, string startInCell)
{
// Default
var nameLoc = 1;
var startLoc = 2;
if (writeDataInTemplate == "True")
{
if (rowsOrColumns == "columns")
{
nameLoc = ExcelUtils.GetRowNum(sampleLoc); // nameRow
startLoc = ExcelUtils.GetRowCol(startInCell).col; // startCol
}
else if (rowsOrColumns == "rows")
{
nameLoc = ExcelUtils.GetColNum(sampleLoc); // nameCol
startLoc = ExcelUtils.GetRowCol(startInCell).row; // startRow
}
}
return(nameLoc, startLoc);
}
// Process Handler for Sciex Lipidyzer
public static void Lipidyzer(List <string> filePathList, Dictionary <string, string> options)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // EPPlus license
// Get template and save as output file
var destExcel = new ExcelPackage(new FileInfo(options["TemplatePath"]));
destExcel.SaveAs(new FileInfo(options["OutputFolder"] + "\\" + options["OutputFileName"]));
filePathList.Sort();
// Merge each file into template
foreach (var file in filePathList)
{
var srcExcel = new ExcelPackage(new FileInfo(file));
destExcel = Merge.MergeWorkbooks(srcExcel, destExcel, options);
}
// Format and options for every sheet
foreach (var sheet in destExcel.Workbook.Worksheets)
{
// Do nothing on the "Key" tab
if (sheet.Name.Contains("Key", StringComparison.OrdinalIgnoreCase))
{
continue;
}
// Remove unwanted samples
destExcel = RemoveReplace.RemoveSamples(destExcel, sheet.Name, options);
// Replace missing values
destExcel = RemoveReplace.ReplaceMissing(destExcel, sheet.Name, options["Replacement"]);
// Format data to 4 decimal places
destExcel = ExcelUtils.FormatCells(destExcel, sheet.Name, "0.0000");
}
destExcel.Save();
}
// 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>
/// Inserts 3 columns after each group of QC: "CV", "Avg CV", and "Median CV",
/// and calculates the CV.
/// </summary>
/// <param name="excelPkg"></param>
/// <param name="options"></param>
/// <param name="tabName"></param>
/// <returns></returns>
public static ExcelPackage InsertCVColumns(ExcelPackage excelPkg, Dictionary <string, string> options, string tabName)
{
var worksheet = excelPkg.Workbook.Worksheets[tabName];
var(nameRow, startCol) = GetNameLocStartLoc(
"columns", options["WriteDataInTemplate"], options["SampleLoc"], options["StartInCell"]);
var lastRow = 1;
var cols = ExcelUtils.ColumnsInRow(worksheet, nameRow);
// Insert CV columns after QC(I) and QC(S). CV, Avg CV, Median CV
for (var col = startCol; col <= cols + 6; ++col)
{
var cell = worksheet.Cells[nameRow, col]?.Value?.ToString();
if (cell?.Contains("QC") == true)
{
// Compare current QC string to next cell QC string
var nextCell = worksheet.Cells[nameRow, col + 1]?.Value?.ToString();
if (cell?.Contains("I") == true && nextCell?.Contains("I") == false ||
cell?.Contains("S") == true && nextCell?.Contains("S") == false ||
string.IsNullOrWhiteSpace(nextCell))
{
// Insert 3 columns
worksheet.InsertColumn(col + 1, 3);
worksheet.Cells[nameRow, col + 1].Value = "CV";
worksheet.Cells[nameRow, col + 2].Value = "Avg CV";
worksheet.Cells[nameRow, col + 3].Value = "Median CV";
/* Guide for row and column names
* startCol col col + 1 col + 2 col + 3
* nameRow QC(I)#1 QC(I)#2 QC(I)#3 CV Avg CV Median CV
* nameRow + 1 startCalc ... endCalc formula
* row data data data copy formula
*/
// Write first CV formula
var startCalc = worksheet.Cells[nameRow + 1, startCol].Address;
var endCalc = worksheet.Cells[nameRow + 1, col].Address;
worksheet.Cells[nameRow + 1, col + 1].Formula =
$"STDEV({startCalc}:{endCalc})/AVERAGE({startCalc}:{endCalc})";
// Copy CV formula down while there is data
var row = nameRow + 2;
while (!string.IsNullOrWhiteSpace(worksheet.Cells[row, col]?.Value?.ToString()))
{
worksheet.Cells[row - 1, col + 1].Copy(worksheet.Cells[row, col + 1]);
++row;
}
lastRow = row;
// Calculate CV column
worksheet.Cells[nameRow + 1, col + 1, row - nameRow, col + 1].Calculate();
// Calculate average and median CV
var startCV = worksheet.Cells[nameRow + 1, col + 1].Address;
var endCV = worksheet.Cells[row - nameRow, col + 1].Address;
worksheet.Cells[nameRow + 1, col + 2]
.CreateArrayFormula($"AVERAGE(IF(ISNUMBER({startCV}:{endCV}),{startCV}:{endCV}))");
worksheet.Cells[nameRow + 1, col + 3]
.CreateArrayFormula($"MEDIAN(IF(ISNUMBER({startCV}:{endCV}),{startCV}:{endCV}))");
// Format to % with 2 decimal places
worksheet.Cells[nameRow + 1, col + 1, row - nameRow, col + 3]
.Style.Numberformat.Format = "#0.00%";
// Center and bold text in cell
worksheet.Cells[nameRow, col + 1, row - nameRow, col + 3]
.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[nameRow, col + 1, row - nameRow, col + 3]
.Style.Font.Bold = true;
worksheet.Cells[nameRow, col + 1, row - nameRow, col + 3]
.Style.Font.Color.SetColor(Color.Red);
// Header - will be inserted into row 1 at the end to preserve current row count
if (cell.Contains("I"))
{
worksheet.Cells[lastRow, startCol].Value = "Instrument QC (Pooled Serum Samples) Reproducibility";
worksheet.Cells[nameRow, startCol, lastRow, col + 3]
.Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[nameRow, startCol, lastRow, col + 3]
.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
else if (cell.Contains("S"))
{
worksheet.Cells[lastRow, startCol].Value = "Sample QC (Pooled Study Samples) Reproducibility";
worksheet.Cells[nameRow, startCol, lastRow, col + 3]
.Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[nameRow, startCol, lastRow, col + 3]
.Style.Fill.BackgroundColor.SetColor(1, 146, 208, 80);
// Excel Standard Color "Light Green"
}
// Merge header cells
worksheet.Cells[lastRow, startCol, lastRow, col + 3].Merge = true;
// Reset for next group of QC
col += 3;
startCol = col + 1;
}
}
else
{
// Cell was not QC, increment startCol to track the start of CV calculation range
++startCol;
}
}
// Move header to row 1
var lastCol = ExcelUtils.ColumnsInRow(worksheet, nameRow);
worksheet.InsertRow(1, 1);
lastRow += 1;
var headerRow = worksheet.Cells[1, 1, 1, lastCol];
worksheet.Cells[lastRow, 1, lastRow, lastCol].Copy(headerRow);
headerRow.Style.Font.Bold = true;
headerRow.Style.Font.Size = 14;
worksheet.DeleteRow(lastRow);
excelPkg.Save();
//excelPkg.SaveAs(new FileInfo(options["OutputFolder"] + "\\" + options["OutputFileName"]));
return(excelPkg);
}
/// <summary>
///
/// </summary>
/// <param name="excelPkg"></param>
/// <param name="options"></param>
/// <param name="tabName"></param>
/// <returns></returns>
public static ExcelPackage InsertCVRows(ExcelPackage excelPkg, Dictionary <string, string> options, string tabName)
{
var worksheet = excelPkg.Workbook.Worksheets[tabName];
var(nameCol, startRow) = GetNameLocStartLoc(
"rows", options["WriteDataInTemplate"], options["SampleLoc"], options["StartInCell"]);
var rows = ExcelUtils.RowsInColumn(worksheet, nameCol);
// Insert CV columns after QC(I) and QC(S). CV, Avg CV, Median CV
for (var row = startRow; row <= rows + 6; ++row)
{
var cell = worksheet.Cells[row, nameCol]?.Value?.ToString();
if (cell?.Contains("QC") == true)
{
// Compare current QC string to next cell QC string
var nextCell = worksheet.Cells[row + 1, nameCol]?.Value?.ToString();
if (cell?.Contains("I") == true && nextCell?.Contains("I") == false ||
cell?.Contains("S") == true && nextCell?.Contains("S") == false ||
string.IsNullOrWhiteSpace(nextCell))
{
// Insert 3 rows
worksheet.InsertRow(row + 1, 3);
worksheet.Cells[row + 1, nameCol].Value = "CV";
worksheet.Cells[row + 2, nameCol].Value = "Avg CV";
worksheet.Cells[row + 3, nameCol].Value = "Median CV";
/* Guide for row and column names
* nameCol nameCol + 1 nameCol + 2 col
* startRow QC(I)#1 data startCalc data
* QC(I)#2 data | data
* row QC(I)#3 data endCalc data
* row + 1 CV formula -> copy formula
* row + 2 Avg CV
* row + 3 Median CV
*/
// Write first CV formula
var startCalc = worksheet.Cells[startRow, nameCol + 1].Address;
var endCalc = worksheet.Cells[row, nameCol + 1].Address;
worksheet.Cells[row + 1, nameCol + 1].Formula =
$"STDEV({startCalc}:{endCalc})/AVERAGE({startCalc}:{endCalc})";
// Copy CV formula across while there is data
var col = nameCol + 2;
while (!string.IsNullOrWhiteSpace(worksheet.Cells[row, col]?.Value?.ToString()))
{
worksheet.Cells[row + 1, col - 1].Copy(worksheet.Cells[row + 1, col]);
++col;
}
// Calculate CV row
worksheet.Cells[row + 1, nameCol + 1, row + 1, col - nameCol].Calculate();
// Calculate average and median CV
var startCV = worksheet.Cells[row + 1, nameCol + 1].Address;
var endCV = worksheet.Cells[row + 1, col - nameCol].Address;
worksheet.Cells[row + 2, nameCol + 1]
.CreateArrayFormula($"AVERAGE(IF(ISNUMBER({startCV}:{endCV}),{startCV}:{endCV}))");
worksheet.Cells[row + 3, nameCol + 1]
.CreateArrayFormula($"MEDIAN(IF(ISNUMBER({startCV}:{endCV}),{startCV}:{endCV}))");
// Format to % with 2 decimal places
worksheet.Cells[row + 1, nameCol + 1, row + 3, col - nameCol]
.Style.Numberformat.Format = "#0.00%";
// Center and bold text in cell
worksheet.Cells[row + 1, nameCol, row + 3, col - nameCol]
.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[row + 1, nameCol, row + 3, col - nameCol].Style.Font.Bold = true;
// Reset for next group of QC
row += 3;
startRow = row + 1;
}
}
else
{
// Cell was not QC, increment startRow to track the start of CV calculation range
++startRow;
}
}
excelPkg.Save();
//excelPkg.SaveAs(new FileInfo(options["OutputFolder"] + "\\" + options["OutputFileName"]));
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);
}
// Writes data into Excel tab containing compound and sample names.
// Compound names must be in the sheet. Sample names are optional
// and will be checked for and filled in if missing. Put sample names in sheet
// if you want a particular order.
//
// The following are optional parameters and can be omitted if using info from options dictionary:
// useOptions - default is true. If false, you must fill in the remaining parameters
// startRow and startCol - where to start writing data
// sampleLoc and compoundLoc - where the sample and compound names are located
public static ExcelPackage WriteIntoTemplate(
OrderedDictionary dataMap, ExcelPackage excelPkg, Dictionary <string, string> options, string templateTab,
bool useOptions = true, int startRow = 2, int startCol = 2, int sampleLoc = 1, int compoundLoc = 1)
{
var worksheet = excelPkg.Workbook.Worksheets[templateTab];
int numSamples, numCompounds, endRow, endCol;
// Check if map contains sample names, prevents index error later
List <string> samplesKeys;
try
{
samplesKeys = ((OrderedDictionary)dataMap[0]).Keys.Cast <string>().ToList();
}
catch
{
MessageBox.Show($"No data to write in {templateTab}. " +
$"Check if template and samples are in rows or columns.");
return(excelPkg);
}
// Get row and col to start writing
if (useOptions)
{
(startRow, startCol) = ExcelUtils.GetRowCol(options["StartInCell"]);
// Convert column name to number
sampleLoc = int.TryParse(options["SampleLoc"], out var sampleLocNum)
? sampleLocNum
: ExcelUtils.ColumnNameToNumber(options["SampleLoc"]);
compoundLoc = int.TryParse(options["CompoundLoc"], out var compoundLocNum)
? compoundLocNum
: ExcelUtils.ColumnNameToNumber(options["CompoundLoc"]);
}
if (options["SamplesOut"] == "rows")
{
// Fill in sample names if not in template
var name = worksheet.Cells[startRow, sampleLoc].Value?.ToString();
if (name is null || name.Length < 1)
{
var r = startRow;
foreach (string sampleName in samplesKeys)
{
worksheet.Cells[r++, sampleLoc].Value = sampleName;
}
}
// Get approximate bounds, samples in rows (in column sampleLoc)
numSamples = ExcelUtils.RowsInColumn(worksheet, sampleLoc);
numCompounds = ExcelUtils.ColumnsInRow(worksheet, compoundLoc);
endRow = numSamples + startRow;
endCol = numCompounds + startCol;
// Write each compound data (column)
for (int col = startCol; col <= endCol; ++col)
{
for (int row = startRow; row <= endRow; ++row)
{
// Write peak area corresponding to compound and sample name
// Compound in row compoundLoc
var compound = worksheet.Cells[compoundLoc, col]?.Value?.ToString();
if (compound != null && dataMap.Contains(compound))
{
// Read sample name from sample column, lookup data for sample in map
var sampleName = worksheet.Cells[row, sampleLoc]?.Value?.ToString();
if (sampleName is null)
{
continue;
}
var data = ((OrderedDictionary)dataMap[compound])[sampleName]?.ToString();
// Write data to cell, as a number if possible
if (double.TryParse(data, out double dataNum))
{
worksheet.Cells[row, col].Value = dataNum;
}
else
{
worksheet.Cells[row, col].Value = data;
}
}
}
}
}
else
{
// Fill in sample names if not in template
var name = worksheet.Cells[sampleLoc, startCol].Value?.ToString();
if (name is null || name.Length < 1)
{
var c = startCol;
foreach (string sampleName in samplesKeys)
{
worksheet.Cells[sampleLoc, c++].Value = sampleName;
}
}
// Get approximate bounds, samples in columns (in row sampleLoc)
numCompounds = ExcelUtils.RowsInColumn(worksheet, compoundLoc);
numSamples = ExcelUtils.ColumnsInRow(worksheet, sampleLoc);
endRow = numCompounds + startRow;
endCol = numSamples + startCol;
// Write each compound data (row)
for (int row = startRow; row <= endRow; ++row)
{
for (int col = startCol; col <= endCol; ++col)
{
// Write peak area corresponding to compound and sample name
// Compounds in column compoundLoc
var compound = worksheet.Cells[row, compoundLoc]?.Value?.ToString();
if (compound != null && dataMap.Contains(compound))
{
// Read sample name from row, lookup data for sample in map
var sampleName = worksheet.Cells[sampleLoc, col]?.Value?.ToString();
if (sampleName is null)
{
continue;
}
var data = ((OrderedDictionary)dataMap[compound])[sampleName]?.ToString();
// Write data to cell, as a number if possible
if (double.TryParse(data, out double dataNum))
{
worksheet.Cells[row, col].Value = dataNum;
}
else
{
worksheet.Cells[row, col].Value = data;
}
}
}
}
}
worksheet.Cells[startRow, startCol, endRow, endCol].Style.Numberformat.Format = "0";
worksheet.Cells[startRow, startCol, endRow, endCol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// TODO - check that save as new name works
//excelPkg.SaveAs(new FileInfo(options["OutputFolder"] + "\\" + options["OutputFileName"]));
excelPkg.Save();
return(excelPkg);
}