/// <summary> /// 插入新行 /// </summary> /// <param name="y">模板行号</param> public void insertRow(int row) { Excel.Range range = m_objSheet.get_Range("A" + row.ToString(), "IV" + row.ToString()); range.Copy(miss); range.Insert(Excel.XlDirection.xlDown, miss); //range.get_Range(GetAix(1, row), GetAix(256, row)); //range.Select(); //m_objSheet.Paste(miss, miss); }
/// <summary> /// Replace single scripts depend value sheet /// </summary> public void DependentValReplace(string source, string file) { string _sPath_Source = source; string _sPath_Destine = file; object oMissing = System.Reflection.Missing.Value; System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); objExl_Source.EnableEvents = false; objExl_Source.DisplayAlerts = false; objExl_Source.Visible = false; double[] widthOfScripts = new double[15] { 84.75, 134.25, 54, 36.75, 127.5, 220.5, 80.25, 99, 103.5, 132.75, 54, 54, 54, 54, 54 }; // objExl_Source.ScreenUpdating = true; Excel.Workbook objWBook_Source = objExl_Source.Workbooks.Open(_sPath_Source, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); //ApplicationClass objExl_Destine = new ApplicationClass(); //objExl_Destine.EnableEvents = false; //objExl_Destine.DisplayAlerts = false; //objExl_Destine.Visible = false; // objExl_Destine.ScreenUpdating = true; Excel.Workbook objWBook_Destine = objExl_Source.Workbooks.Open(_sPath_Destine, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); //Workbook objWBook_Source = objExl_Source.Workbooks.Open(_sPath_Source, // 0,false,5,oMissing,oMissing,false,XlPlatform.xlWindows, // oMissing,true,false,0,true,false,false); //Workbook objWBook_Destine = objExl_Destine.Workbooks.Open(_sPath_Destine, // 0, false, 5, oMissing, oMissing, false, XlPlatform.xlWindows, // oMissing, true, false, 0, true, false, false); string strSheetName = "Scripts"; Excel.Worksheet objWSheet_Source = (Excel.Worksheet)objWBook_Source.Worksheets[strSheetName]; //objWSheet_Source.Copy((Excel.Worksheet)objWBook_Destine.Sheets[objWBook_Destine.Sheets.Count], oMissing); //strSheetName = "Action DataValDepend"; //objWSheet_Source = (Excel.Worksheet)objWBook_Source.Worksheets[strSheetName]; //objWSheet_Source.Copy((Excel.Worksheet)objWBook_Destine.Sheets[objWBook_Destine.Sheets.Count], oMissing); Excel.Range range = objWSheet_Source.get_Range(objWSheet_Source.Cells[1, 1], objWSheet_Source.Cells[900, 20]); // range.Value = "123"; Excel.Worksheet sheet1 = (Excel.Worksheet)objWBook_Destine.Sheets[1]; Excel.Range range1 = sheet1.get_Range(sheet1.Cells[1, 1], sheet1.Cells[900, 20]); range.Copy(range1); for (int i = 1; i <= 15; i++) { Excel.Range rmgSource = (Excel.Range)objWSheet_Source.Cells[1, i]; Excel.Range rmgDestine = (Excel.Range)sheet1.Cells[1, i]; rmgDestine.EntireColumn.ColumnWidth = rmgSource.EntireColumn.ColumnWidth; //rmgDestine.EntireColumn.ColumnWidth = widthOfScripts[i - 1]; //rmg.EntireColumn.AutoFit(); //rmg.Width = widthOfScripts[i-1]; //MessageBox.Show(rmg.Width.ToString()); } foreach (Excel.Worksheet xSheet in objWBook_Source.Worksheets) { if (xSheet.Name.Equals("Notes") || xSheet.Name.Equals("Note")) { strSheetName = "Notes"; objWSheet_Source = (Excel.Worksheet)objWBook_Source.Worksheets[strSheetName]; objWSheet_Source.Copy(oMissing, (Excel.Worksheet)objWBook_Destine.Sheets[objWBook_Destine.Sheets.Count]); } } //objWBook_Source.Save(); objWBook_Destine.Save(); objWBook_Destine = null; objExl_Source.Quit(); // objExl_Destine.Quit(); }
/// <summary> /// 将指定范围区域拷贝到目标区域 /// </summary> /// <param name="sheetName">WorkSheet名称</param> /// <param name="startCell">要拷贝区域的开始Cell位置(比如:A10)</param> /// <param name="endCell">要拷贝区域的结束Cell位置(比如:F20)</param> /// <param name="targetCell">目标区域的开始Cell位置(比如:H10)</param> public void RangeCopy(string sheetName, string startCell, string endCell, string targetCell) { try { Excel.Worksheet sheet = null; for (int i = 1; i <= this.WorkSheetCount; i++) { workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(i); if (workSheet.Name == sheetName) { sheet = workSheet; } } if (sheet != null) { for (int i = sheetCount; i >= 1; i--) { range1 = sheet.get_Range(startCell, endCell); range2 = sheet.get_Range(targetCell, this.missing); range1.Copy(range2); } } else { this.KillExcelProcess(); throw new Exception("名称为\"" + sheetName + "\"的工作表不存在"); } } catch (Exception e) { this.KillExcelProcess(); throw e; } }
/// <summary> /// 将指定范围区域拷贝到目标区域 /// </summary> /// <param name="sheetIndex">WorkSheet索引</param> /// <param name="startCell">要拷贝区域的开始Cell位置(比如:A10)</param> /// <param name="endCell">要拷贝区域的结束Cell位置(比如:F20)</param> /// <param name="targetCell">目标区域的开始Cell位置(比如:H10)</param> public void RangeCopy(int sheetIndex, string startCell, string endCell, string targetCell) { if (sheetIndex > this.WorkSheetCount) { this.KillExcelProcess(); throw new Exception("索引超出范围,WorkSheet索引不能大于WorkSheet数量!"); } try { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex); range1 = workSheet.get_Range(startCell, endCell); range2 = workSheet.get_Range(targetCell, this.missing); range1.Copy(range2); } catch (Exception e) { this.KillExcelProcess(); throw e; } }
/// <summary> /// 复制列(在指定WorkSheet指定列右边复制指定数量列) /// </summary> /// <param name="sheetIndex"></param> /// <param name="columnIndex"></param> /// <param name="count"></param> public void CopyColumns(int sheetIndex, int columnIndex, int count) { if (sheetIndex > this.WorkSheetCount) { this.KillExcelProcess(); throw new Exception("索引超出范围,WorkSheet索引不能大于WorkSheet数量!"); } try { workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex]; // range1 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex]; range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000"); for (int i = 1; i <= count; i++) { // range2 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex + i]; range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000"); range1.Copy(range2); } } catch (Exception e) { this.KillExcelProcess(); throw e; } }
/// <summary> /// 复制列(在指定列右边复制指定数量列) /// </summary> /// <param name="columnIndex"></param> /// <param name="count"></param> public void CopyColumns(int columnIndex, int count) { try { for (int n = 1; n <= this.WorkSheetCount; n++) { workSheet = (Excel.Worksheet)workBook.Worksheets[n]; // range1 = (Excel.Range)workSheet.Columns[columnIndex,this.missing]; range1 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex) + "1", this.IntToLetter(columnIndex) + "10000"); for (int i = 1; i <= count; i++) { // range2 = (Excel.Range)workSheet.Columns[this.missing,columnIndex + i]; range2 = (Excel.Range)workSheet.get_Range(this.IntToLetter(columnIndex + i) + "1", this.IntToLetter(columnIndex + i) + "10000"); range1.Copy(range2); } } } catch (Exception e) { this.KillExcelProcess(); throw e; } }
/// <summary> /// 复制行(在指定WorkSheet指定行下面复制指定数量行) /// </summary> /// <param name="sheetIndex"></param> /// <param name="rowIndex"></param> /// <param name="count"></param> public void CopyRows(int sheetIndex, int rowIndex, int count) { if (sheetIndex > this.WorkSheetCount) { this.KillExcelProcess(); throw new Exception("索引超出范围,WorkSheet索引不能大于WorkSheet数量!"); } try { workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex]; range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; for (int i = 1; i <= count; i++) { range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing]; range1.Copy(range2); } } catch (Exception e) { this.KillExcelProcess(); throw e; } }
/// <summary> /// 复制行(在指定行下面复制指定数量行) /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void CopyRows(int rowIndex, int count) { try { for (int n = 1; n <= this.WorkSheetCount; n++) { workSheet = (Excel.Worksheet)workBook.Worksheets[n]; range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; for (int i = 1; i <= count; i++) { range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing]; range1.Copy(range2); } } } catch (Exception e) { this.KillExcelProcess(); throw e; } }