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