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