110,502
社区成员
发帖
与我相关
我的任务
分享
Excel.Sheets sheets = excel.Sheets;
Excel.Sheets sheets1 = excel.Sheets;
private Excel.Application ExcelApp = null;
private Excel.Workbooks workbooks = null;
private _Workbook workbook = null;
private _Worksheet worksheet = null;
/// <summary>
/// 向当前打开的excel中添加一个新的worksheet
/// </summary>
/// <param name="strNewSheetName">要添加的sheet的名称</param>
/// <param name="strSheetAfter">新建的工作表将置于此工作表之后</param>
/// <param name="strType">指定工作表类型。可为以下 XlSheetType 常量之一:xlWorksheet、
///xlChart、xlExcel4MacroSheet 或 xlExcel4IntlMacroSheet。如果要基于现有模板插入工作表,
///则请指定该模板的路径。</param>
/// <returns>添加成功与否</returns>
public bool AddWorksheet(string strNewSheetName, string strSheetAfter, object objType)
{
try
{
if ((ExcelApp != null) && (ExcelApp.ActiveWorkbook != null))
{
int iCnt = 0;
_Worksheet wksheetA = null;
Sheets sheets = workbook.Worksheets;
iCnt = sheets.Count;
//获取前和后面的sheet
for (int i = 0; i < iCnt; i++)
{
_Worksheet wksheet = (_Worksheet)sheets.get_Item(i + 1);
if (wksheet.Name == strSheetAfter)
{
wksheetA = (_Worksheet)sheets.get_Item(i + 1);
}
}
//添加
if (wksheetA == null)
{
return false;
}
else
{
sheets.Add(System.Reflection.Missing.Value, wksheetA, 1, objType);
}
//命名
for (int i = 0; i < iCnt; i++)
{
_Worksheet wksheet = (_Worksheet)sheets.get_Item(i + 1);
if (wksheet.Name == strSheetAfter)
{
_Worksheet wkst = (_Worksheet)sheets.get_Item(i + 2);
wkst.Name = strNewSheetName;
}
}
return true;
}
return false;
}
catch (Exception ex)
{
Console.Write(ex.ToString());
//System.Windows.Forms.MessageBox.Show(ex.Message + "-" + ex.StackTrace);
return false;
}
}
/// <summary>
/// 从当前打开的excel中删除一个sheet
/// </summary>
/// <param name="strSheetName">要删除的sheet的名称</param>
/// <returns>删除成功与否</returns>
public bool DeleteWorksheet(string strSheetName)
{
try
{
if ((ExcelApp != null) && (ExcelApp.ActiveWorkbook != null))
{
int iFlag = 0;
int iCnt;
Sheets sheets = workbook.Worksheets;
iCnt = sheets.Count;
for (int i = 0; i < iCnt; i++)
{
_Worksheet wksheet = (_Worksheet)sheets.get_Item(i + 1);
if (wksheet.Name == strSheetName)
{
iFlag = 1;
wksheet.Delete();
break;
}
}
if (iFlag == 1)
{
return true;
}
else
{
return false;
}
}
return false;
}
catch
{
return false;
}
}
xlApp.DisplayAlerts = false; //注意一定要加上这句
((Excel.Worksheet)workBook.Worksheets[2]).Delete();
xlApp.DisplayAlerts = true;//注意一定要加上这句
private void AddWorksheetToExcelWorkbook(string fullFilename, string worksheetName)
{
try
{
xlApp = new Excel.Application();
if (xlApp == null) return;
xlApp.Visible = true;
xlWorkbook = xlApp.Workbooks.Open(fullFilename, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true,
false, 0,
true, false, false);
xlSheets = xlWorkbook.Sheets as Sheets;
xlNewSheet = (Worksheet) xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
xlNewSheet.Name = worksheetName;
xlWorkbook.Save();
xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp.Quit();
}
finally
{
Marshal.ReleaseComObject(xlNewSheet);
Marshal.ReleaseComObject(xlSheets);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
}