Example #1
0
        /// <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);
        }
Example #8
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);
        }