private Excel.Range addFirstCol(ref Excel.Range pRng, Excel.Range vCurCol) {
int vCurColN = vCurCol.Column;
int vCurRowN = vCurCol.Row;
int vCurRowsN = vCurCol.Rows.Count;
vCurCol.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, true);
Excel.Range vNewCol = ExcelSrv.getRange(vCurCol.Worksheet, vCurCol.Worksheet.Cells[vCurRowN, vCurColN],
vCurCol.Worksheet.Cells[vCurRowN + vCurRowsN, vCurColN]);
vCurCol.Copy(vNewCol);
vCurCol.ColumnWidth = vNewCol.ColumnWidth;
return vCurCol;
}
} // end TeamWorkbook()
public void PasteScores( Excel.Range scores )
{
// note: for PasteSpecial to work, both the student and team workbooks must be opend using the same Excel App
scores.Copy(); // copy scoares to clipboard
string newCell = "A" + (++currentRow).ToString(); // Note: increment row
Excel.Range excelRange = excelWorksheet.get_Range( newCell, newCell ); // starts with A7, then moves down
try {
excelRange.PasteSpecial( Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
false,
true ); // transpose the column into a row
}
catch( Exception e )
{
Console.WriteLine( e.Message );
}
} // end PasteScores()
} // end PasteScores()
public void PasteComments( Excel.Range comments )
{
comments.Copy();
string newCell = "J" + ( currentRow ).ToString(); // very specific
Excel.Range excelRange = excelWorksheet.get_Range( newCell, newCell );
try
{
excelRange.PasteSpecial( Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
false,
false );
}
catch ( Exception e )
{
Console.WriteLine( e.Message );
}
} // end PasteComments()
public void Paste_Filtered_Data(Excel.Range Filtered_Data_Range)
{
Filtered_Data_Range.Copy();
Excel.Worksheet PivotTable_WorkSheet = this.Application.ActiveWorkbook.Worksheets.Add();
PivotTable_WorkSheet.Name = "Pivot Table";
Excel.Range Paste_Range = PivotTable_WorkSheet.get_Range("A1");
Paste_Range.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
}
private void AddMDA4(excel.Workbook xlsWorkbook, excel.Worksheet xlsWorksheet, excel.Worksheet xlsSummary, excel.Range rng, List<AdminLevel> reportingUnits, Dictionary<int, DataRow> aggIntvs)
{
List<string> typeNames = new List<string> { "IntvAlb2" };
List<string> typesToCalc = new List<string>();
for (int i = Util.MaxRounds; i >= 1; i--)
{
if (!HasRoundNumber(reportingUnits, aggIntvs, typeNames, typesToCalc, i))
continue;
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets["MDA4"];
if (i > 1)
{
xlsWorksheet.Copy(System.Reflection.Missing.Value, xlsSummary);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[xlsSummary.Index + 1];
xlsWorksheet.Name = string.Format("MDA4_R{0}", i);
}
int rowCount = 9;
foreach (var unit in reportingUnits)
{
if (aggIntvs.ContainsKey(unit.Id))
{
AddValueToRange(xlsWorksheet, rng, "F" + rowCount, GetIntFromRow("PcIntvNumPsacTargeted", ref typesToCalc, aggIntvs[unit.Id], i, i, null, typeNames));
AddValueToRange(xlsWorksheet, rng, "G" + rowCount, GetIntFromRow("PcIntvNumSacTargeted", ref typesToCalc, aggIntvs[unit.Id], i, i, null, typeNames));
AddValueToRange(xlsWorksheet, rng, "H" + rowCount, GetIntFromRow("PcIntvNumAdultsTargeted", ref typesToCalc, aggIntvs[unit.Id], i, i, null, typeNames));
AddValueToRange(xlsWorksheet, rng, "J" + rowCount, GetIntFromRow("PcIntvPsacTreated", ref typesToCalc, aggIntvs[unit.Id], i, i, null, typeNames));
AddValueToRange(xlsWorksheet, rng, "K" + rowCount, GetIntFromRow("PcIntvNumSacTreated", ref typesToCalc, aggIntvs[unit.Id], i, i, null, typeNames));
AddValueToRange(xlsWorksheet, rng, "L" + rowCount, GetIntFromRow("PcIntvNumAdultsTreated", ref typesToCalc, aggIntvs[unit.Id], i, i, null, typeNames));
DateTime? startMda = GetDateFromRow("PcIntvStartDateOfMda", ref typesToCalc, aggIntvs[unit.Id], false, i, i, null, typeNames);
if (startMda.HasValue)
AddValueToRange(xlsWorksheet, rng, "E" + rowCount, startMda.Value.ToString("MM/dd/yyyy"));
}
rowCount++;
}
}
}
private void refreshFormulaCols_sub(Excel.Range pSrcFormula, Excel.Range[] pRng){
for(int i=0; i<pRng.Length; i++){
Excel.Range vRng = pRng[i];
pSrcFormula.Copy(vRng);
}
}
