如何在新的excel工作簿中创建空白工作表

Question

catch (COMException ex)
{
/*************Create new excel program in no running excel program*************/
//xlApp = new Excel.Application();
//xlApp.DisplayAlerts = false;
//xlApp.Visible = true;
//xlApp.ScreenUpdating = true;
//xlApp.WindowState = Excel.XlWindowState.xlMaximized;
//OpenExcelCB.Checked = true;
/*************Create new excel program in no running excel program*************/
HandleRunningException("Error occurs ...", ex);
MessageBox.Show("Please open Excel!");
//this.close();
}

我想对上面的代码进行调整。但是，当我删除注释标签并尝试运行程序时。它只打开excel应用程序而不需要一个新的工作表。

谁能帮我看看我的程序有什么问题或者缺什么吗?

Answer 1

看看下面的代码是否满足了您的需求。

• 如果文件存在，删除它以确保代码工作
• 重命名第一页
• 添加新表
• 保存新文件
• 验证所有对象被释放
• 可选择打开新文件

excel操作类

using System;
using System.Diagnostics;
using System.Linq;
using System.IO;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
namespace ExcelInteropApp.Classes
{
public class ExcelOperations
{
public delegate void OnAction(string sender);
public static event OnAction ActionHandler;
/// <summary>
/// create an excel file, rename sheet1 (default sheet),
/// create another worksheet, rename it and re-order to end.
/// </summary>
/// <param name="fileName">path and file name for excel file</param>
/// <param name="firstWorkSheetName">name for default sheet</param>
/// <param name="secondWorkSheetName">name for newly added sheet</param>
public static (bool success, Exception exception) CreateExcelFile(string fileName, string firstWorkSheetName, string secondWorkSheetName, bool open)
{
try
{
if (File.Exists(fileName))
{
File.Delete(fileName);
}
Excel.Application xlApp;
Excel.Workbooks xlWorkBooks;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Sheets xlWorkSheets;
xlApp = new Excel.Application { DisplayAlerts = false };
xlWorkBooks = xlApp.Workbooks;
xlWorkBook = xlWorkBooks.Add();
xlWorkSheets = xlWorkBook.Sheets;

xlWorkSheet = (Excel.Worksheet)xlWorkSheets[1];
xlWorkSheet.Name = firstWorkSheetName;
ActionHandler?.Invoke("renamed first sheet");

Excel.Worksheet xlNewSheet = (Excel.Worksheet)xlWorkSheets
.Add(xlWorkSheets[1], 
Type.Missing, 
Type.Missing, 
Type.Missing);
xlNewSheet.Move(System.Reflection.Missing.Value, xlWorkSheets[xlWorkSheets.Count]);
xlNewSheet.Name = secondWorkSheetName;
ActionHandler?.Invoke("Done with add sheet");
Marshal.FinalReleaseComObject(xlNewSheet);
xlNewSheet = null;
xlWorkBook.SaveAs(fileName);
ActionHandler?.Invoke("Saved file");
xlWorkBook.Close();
xlApp.UserControl = true;
xlApp.Quit();
Marshal.FinalReleaseComObject(xlWorkSheets);
xlWorkSheets = null;
Marshal.FinalReleaseComObject(xlWorkSheet);
xlWorkSheet = null;
Marshal.FinalReleaseComObject(xlWorkBook);
xlWorkBook = null;
Marshal.FinalReleaseComObject(xlWorkBooks);
xlWorkBooks = null;
Marshal.FinalReleaseComObject(xlApp);
xlApp = null;
ActionHandler?.Invoke($"Clean-up: {(Process.GetProcesses().Any((p) => p.ProcessName.Contains("EXCEL")) ? "Released" : "Not released")}");
if (open)
{
Process.Start(fileName);
}

return (true, null);
}
catch (Exception exception)
{
return (false, exception);
}
}

}
}

在windows窗体中的用法

private void CreateExcelButton1_Click(object sender, EventArgs e)
{
ExcelOperations.ActionHandler += ExcelOperationsOnActionHandler;
string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx");
string firstSheet = "Karen";
string secondSheet = "Karen 1";

var (success, exception) = ExcelOperations.CreateExcelFile(fileName,firstSheet, secondSheet, true);
if (success == false)
{
Console.WriteLine(exception.Message);
}
ExcelOperations.ActionHandler -= ExcelOperationsOnActionHandler;
}
private void ExcelOperationsOnActionHandler(string sender)
{
Console.WriteLine(sender);
}