Ejemplo n.º 1
0
        public static ExcelPackage MergeWorkbooks(ExcelPackage srcExcel, ExcelPackage destExcel, Dictionary <string, string> options)
        {
            var(startRow, startCol) = ExcelUtils.GetRowCol(options["StartInCell"]);

            // Merge one sheet at a time
            foreach (var srcSheet in srcExcel.Workbook.Worksheets)
            {
                var sheetName = srcSheet.Name;
                var destSheet = destExcel.Workbook.Worksheets[sheetName];

                // Read source sheet into map
                if (options["SamplesIn"] == "rows")
                {
                    // Location of sample and compound names
                    var compoundRow = ExcelUtils.GetRowNum(options["CompoundLoc"]);
                    var sampleCol   = ExcelUtils.GetColNum(options["SampleLoc"]);

                    // Read source sheet data
                    var srcMap = ExcelToMap.SamplesInRowsToMap(compoundRow, sampleCol, srcExcel, sheetName);

                    // Row to start writing data in destination sheet
                    var nextEmptyRow = ExcelUtils.RowsInColumn(destSheet, sampleCol) + 1;

                    // Write into destination sheet
                    // Write sample names
                    destExcel = MapToExcel.WriteSamplesInRows(srcMap, destExcel, sheetName, sampleCol, nextEmptyRow);

                    // Write data into template
                    destExcel = MapToExcel.WriteIntoTemplate(srcMap, destExcel, options, sheetName, false, nextEmptyRow,
                                                             startCol, sampleCol, compoundRow);
                }
            }
            return(destExcel);
        }
Ejemplo n.º 2
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);
        }
Ejemplo n.º 3
0
        /// <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);
        }