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