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