コード例 #1
0
        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;
            }
        }
コード例 #2
0
        /// <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);
        }