循环浏览特定文件夹中的所有文件，并在特定单元格中将文件名插入Excel

Question

我有一个格式化的Excel表格，我想用作发票。我希望能够循环浏览特定文件夹中的所有文件，并在特定单元格中添加文件名，并在下面插入新行。

我目前有以下内容，但我找不到任何好的例子可以让我在一个非常特定的范围内添加新内容，然后在保持Excel文件格式的同时继续添加该范围以下的新内容。

string path = @"C:MyWorkingFolder";
string rootfolder = Path.GetDirectoryName(path);
string folder = Path.GetFileName(Path.GetDirectoryName(path));
var xlApp = new Microsoft.Office.Interop.Excel.Application();
var xlWorkBook = xlApp.Workbooks.Open(fileName, ReadOnly: true);
Worksheet xlWorkSheet = xlWorkBook.Sheets["Invoice"];
DirectoryInfo d = new DirectoryInfo(rootfolder);
int count = d.GetFiles("*.doc*").Length;
Console.WriteLine("Found {0} files.", count);
Console.Write("Press any key to continue.");
Console.ReadKey();
foreach (var file in Directory.EnumerateFiles(rootfolder, "*.doc*"))
{
Console.WriteLine("Processing...");
string FileName = Path.GetFileName(file);
WriteFileNameToExcel(xlWorkSheet, FileName);
}
Console.WriteLine("Done.");
Console.Write("Press any key to End.");
Console.ReadKey();
// Disable file override confirmaton message  
xlApp.DisplayAlerts = false;

string newfile = string.Format(rootfolder + @"" + "H&K Invoice - {0}.xlsx", DateTime.Now.ToString("dd_MM_yyyy"));
xlWorkBook.SaveAs(newfile, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook);
xlWorkBook.Close();
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);

private static void WriteFileNameToExcel(Worksheet ws, string filename)
{
//How to target the specific cell, then insert new rows so as to maintain formatting
}

Answer 1

插入行，保持以上格式：

Rows("8:8").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

在不更改格式的情况下向新行添加内容：

Cells(8,1).value = "This is new"

添加变量以获得当前的最后一行，并在其下方添加新行。