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