Ejemplo n.º 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;
            }
        }
Ejemplo n.º 2
0
        // 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);
        }