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