怎么给一个Excel增加一个Sheet和删除一个Sheet？

[xyaz1999]

请问，怎么给一个Excel增加一个Sheet和删除一个Sheet？

[jietuan]

你添加对office libaray的引用，使用Excel.Application 和 WorkBook ，你在网上随便搜索一下用.net操作Excel.

[cppfaq]

添加：

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

[cppfaq]

删除：

            xlApp.DisplayAlerts = false; //注意一定要加上这句
            ((Excel.Worksheet)workBook.Worksheets[2]).Delete();
            xlApp.DisplayAlerts = true;//注意一定要加上这句

[david_anwei]

添加

private void button1_Click(object sender, System.EventArgs e)
{
object missing = System.Reflection.Missing.Value;
Excel.ApplicationClass myExcel = new Excel.ApplicationClass();

Excel._Workbook xBk ; //工作薄
Excel._Worksheet xSt; //工作Sheet

xBk = myExcel.Workbooks.Add(true);

for (int i = 0; i < 5; i ++)
{
xSt = (Excel._Worksheet)xBk.ActiveSheet;
xSt.Name ="chenms_" + i ;
myExcel.Cells[2,2]="chenms";
if(i < 4)
{
myExcel.Sheets.Add(missing,missing,1,Excel.XlSheetType.xlWorksheet);
}
}
myExcel.Visible = true;
xBk.SaveAs(@"d:\cms.xls",missing,missing,
missing,missing,missing,Excel.XlSaveAsAccessMode.xlShared,
missing,missing,missing,missing,missing);
myExcel.Quit();



}

[david_anwei]

using Excel = Microsoft.Office.Interop.Excel;

[liumj2001]

copy添加的代码
string strexname;//计算得出的excel模板文件名
object missing = System.Reflection.Missing.Value;
//excel操作
GlobalClass.myExcel = new Excel.Application();
GlobalClass.myExcel.Visible = false; //激活Excel
Workbook wBook = GlobalClass.myExcel.Workbooks.Add(true);
//Worksheet wSheet = (GlobalClass.myExcel.Worksheet)wBook.ActiveSheet;
//设置禁止弹出保存和覆盖的询问提示框
GlobalClass.myExcel.DisplayAlerts = false;
GlobalClass.myExcel.AlertBeforeOverwriting = false;

intdot = GlobalClass.strrepname.IndexOf(".");
strexname = GlobalClass.strrepname.Remove(intdot) + ".xls";
Excel.Application ThisApplication = new Excel.ApplicationClass();
Excel.Workbook ThisWorkBook;
//如果存在就COPY模板文件
if (File.Exists(strexname))
{
//打开excel文件



//打开模板
ThisWorkBook = ThisApplication.Workbooks.Open(strexname, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//添加模板工作区
GlobalClass.myexwkb = (Excel.Worksheet)ThisWorkBook.Sheets[1];


}
else
{
GlobalClass.myexwkb = (Excel.Worksheet)GlobalClass.myExcel.Worksheets[1];
}

删除很简单，就不写了。

[benbirdar]

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

[anwsp]

mark

[kbtjh]

顶一下..答案都出来了

[zzxap]

string currentSheet = "Sheet1";
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

Excel.Worksheet objSheet=(Excel.Worksheet) newWorkbook.Worksheets.Add(excelWorksheet,Type.Missing,Type.Missing,Type.Missing);

[swalp]

Excel.Sheets sheets = excel.Sheets;
Excel.Sheets sheets1 = excel.Sheets;