如何在C＃中编辑excel文件（.xlsx）的单元格内容 [英] How to edit the cell content of excel file (.xlsx) in C#

问题描述

我正在尝试编辑excel文件中的一些细节，但是，获取运行时绑定程序异常无法对null执行运行时绑定参考。



我正在尝试的一些操作是：



i。重命名工作表名称，

ii。用昨天的日期01/03/2018替换'昨天'这个词，

iii。如果标题不在预期的行中，则插入几行。





我添加了excel dll引用并使用Microsoft导入了命名空间.Office.Interop.Excel。



以下是我的代码：





有人可以建议我在这里做错了什么..？



我尝试过的事情：

 string folder = Path.GetDirectoryName（@\\OpsVsAdp \\Files \\Daily \\TempHours.xlsx）; 
 string fileName = Path.GetFileNameWithoutExtension（@\\OpsVsAdp \\Files \\Daily \\TempHours.xlsx）; 
 string extension = Path.GetExtension（@\\OpsVsAdp \\Files \\Daily \\TempHours.xlsx）; 
 
 FileInfo info = new FileInfo（fileName + extension）; 
 {
 //将文件重命名为FileName_YYYYMMDD日期格式。 
 string newName = folder +\\+ fileName +_+ ISO_Date（）+ extension; 
 
 if（！File.Exists（newName））
 {
 // info.MoveTo（newName）; 
} 
} 
 
 MyApp = new Excel.Application（）; 
 MyApp.Visible = false; 
 xlBook = MyApp.Workbooks.Open（@D：\\OpsVsAdp \\Files \\Daily \\TempHours.xlsx）; 
 xlSheet =（Excel.Worksheet）xlBook.Worksheets.get_Item（1）; //这里不需要显式强制转换
 //将工作表名称修改为'Sheet1'
 xlSheet.Name =Sheet1; 
 xlBook.Save（）; 
 
 int totalRows = xlSheet.Rows.Count; 
 int totalCols = xlSheet.Columns.Count; 
 
 //在第二行替换单词'昨天'，日期为
 if（xlSheet.Cells [2,1] .Value.Contain（昨天））
 {
 xlSheet.Cells [2,1] .Value.Replace（昨天，DateTime.Today.AddDays（-1））; 
} 
 //如果是日期范围，删除第一个日期
 else if（xlSheet.Cells [2,1] .Value.Contain（ - ））
 {
 int indexOf = xlSheet.Cells [2,1] .Value.IndexOf（ - ）; 
 xlSheet.Cells [2,1] .Value.Remove（indexOf，-14）; 
} 
 
 Excel.Range range =（Excel.Range）xlSheet.Columns [A，Type.Missing]; 
 Excel.Range currentFind = null; 
 Excel.Range firstFind = null; 
 
 //名称列标题应从第21行开始。 
 if（xlSheet.Cells [21,1] .Value！=Name）
 {
 currentFind = range.Find（Name，xlSheet.Cells [1,1]， 
 Excel.XlFindLookIn.xlValues，
 Excel.XlLookAt.xlPart，
 Excel.XlSearchOrder.xlByRows，
 Excel.XlSearchDirection.xlNext，
 false，
 false，
 false）; 
} 
 

解决方案

你犯了很多错误......



请阅读我的评论：



为什么要使用路径 class，如果可以直接启动变量？

错过了一封驱动器号！

 // string folder = Path.GetDirectoryName（@\\OpsVsAdp \\ Files\\Daily\\TempHours.xlsx）; 
 // string fileName = Path.GetFileNameWithoutExtension（@\\OpsVsAdp \\Files \\Daily \\TempHours.xlsx）; 
 //字符串扩展名= Path.GetExtension（@\\OpsVsAdp \\Files \\Daily \\TempHours.xlsx）; 

而不是上面，使用：

  string  fullFileName =  @  C：\ OpsVsAdp \Files \Daily\TempHours.xlsx; 

下面的部分代码不是必需的，因为您可以在Excel中打开文件并使用 SaveAs将其移动到不同的位置方法。

 FileInfo info =  new  FileInfo（fileName +延期）; 
 {
  //  将文件重命名为FileName_YYYYMMDD日期格式。 
  string  newName = folder +   \\  + fileName +   _ + ISO_Date（）+ extension; 
 
  if （！File.Exists（newName））
 {
  //   info.MoveTo（newName）;  
} 
} 

您应该使用 fullFileName 变量而不是包含文件路径的字符串。以这种方式这样做被称为良好的编程实践，让你以后避免几个错误。

 MyApp =  new  Excel.Application（）; 
 MyApp.Visible =  false ; 
  //  正确方法： 
 xlBook = MyApp.Workbooks.Open（fullFileName ）; 

为了能够修改工作表名称，首先应检查其名称。

 xlSheet =（Excel.Worksheet）xlBook.Worksheets.get_Item（1）; //这里不需要显式强制转换
 //检查并更改工作表名称为'Sheet1'
 if（xlSheet.Name！=Sheet1）
 xlSheet.Name =Sheet1; 
 //你现在不需要保存工作簿
 //如上所述，你可以稍后通过使用SaveAs方法来实现
 //xlBook.Save();

totalRows 和 totalCols 变量已经启动，但从未使用过！

如果您知道包含日期（或日期范围或任何其他数据）的单元格的确切地址，您可以直接更改其内容！

 // int totalRows = xlSheet.Rows.Count; 
 // int totalCols = xlSheet.Columns.Count; 
 
 //在第二行
 //用日期替换单词'昨天'
 //如果是日期范围，删除第一个日期
 string stmp =（ string）xlSheet.Cells [2,1] .Value; 
 if（stmp ==昨天）
 xlSheet.Cells [2,1] .Value = DateTime.Today.AddDays（-1）; 
 
 if（stmp.Contains（ - ））
 xlSheet.Cells [2,1] .Value = stmp.Remove（ind，ind）; 

对不起，但代码的其他部分对我来说并不清楚...



要保存在不同位置的工作簿，请调用：

  string  sNewFileName = Path.Combine（Path.GetDirectoryName（fullFileName） ），
  string  .Concat（Path.GetFileNameWithoutExtension（fullFileName），  _，DateTime.Today.ToString（  yyyyMMdd ）， 。xslx））; 
 xlBook.SaveAs（sNewFileName）; 
 MyApp.Quit（）; 
  //  阅读最后一张便条！ 

最后注意事项： 不要忘记发布资源！有关详细信息，请参阅：如何释放COM互操作对象，以便被叫程序可以退出 [ ^ ]

hi,

I'm trying to edit some of the details in an excel file, but, getting Runtime binder exception "Cannot perform runtime binding on a null reference".

Some of the manipulations I'm trying to do is:

i. renaming the sheet name,
ii. replacing the word 'yesterday' with yesterday's date 01/03/2018,
iii. insert few rows if the header is not in the expected row.


I've added excel dll reference and imported the namespace using Microsoft.Office.Interop.Excel.

Below is my code:


Could someone suggest what am I doing wrong here..?

What I have tried:

string folder = Path.GetDirectoryName(@"\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");
string fileName = Path.GetFileNameWithoutExtension(@"\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");
string extension = Path.GetExtension(@"\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");

FileInfo info = new FileInfo(fileName + extension);
{
    // Rename the file as FileName_YYYYMMDD date format.
    string newName = folder + "\\" + fileName + "_" + ISO_Date() + extension;

    if (!File.Exists(newName))
    {
        // info.MoveTo(newName);
    }
}

MyApp = new Excel.Application();
MyApp.Visible = false;
xlBook = MyApp.Workbooks.Open(@"D:\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");
xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1); // Explicit cast is not required here
// Modify the sheet name to 'Sheet1'
xlSheet.Name = "Sheet1";
xlBook.Save();

int totalRows = xlSheet.Rows.Count;
int totalCols = xlSheet.Columns.Count;

// replace word 'yesterday' in second row with date
if(xlSheet.Cells[2, 1].Value.Contain("yesterday"))
{
    xlSheet.Cells[2, 1].Value.Replace("yesterday", DateTime.Today.AddDays(-1));
}
// if it is date range, remove the first date
else if (xlSheet.Cells[2, 1].Value.Contain("-"))
{
    int indexOf = xlSheet.Cells[2, 1].Value.IndexOf("-");
    xlSheet.Cells[2, 1].Value.Remove(indexOf, -14);
}

Excel.Range range = (Excel.Range)xlSheet.Columns["A", Type.Missing];
Excel.Range currentFind = null;
Excel.Range firstFind = null;

// Name column header should start from 21st row.
if(xlSheet.Cells[21, 1].Value != "Name")
{
    currentFind = range.Find("Name", xlSheet.Cells[1, 1],
                              Excel.XlFindLookIn.xlValues,
                              Excel.XlLookAt.xlPart,
                              Excel.XlSearchOrder.xlByRows,
                              Excel.XlSearchDirection.xlNext,
                              false,
                              false,
                              false);
}

解决方案

A lot of errors you've been made...

Please, read my comments:

Why to use methods of Path class, if you can initiate variables directly?
A drive letter has been missed!

//string folder = Path.GetDirectoryName(@"\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");
//string fileName = Path.GetFileNameWithoutExtension(@"\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");
//string extension = Path.GetExtension(@"\\OpsVsAdp\\Files\\Daily\\TempHours.xlsx");

Rather than above, use this:

string fullFileName = @"C:\OpsVsAdp\Files\Daily\TempHours.xlsx";

Below part of code won't be necessary, because you can open file in Excel and "move" it into different location by using SaveAs method.

FileInfo info = new FileInfo(fileName + extension);
{
    // Rename the file as FileName_YYYYMMDD date format.
    string newName = folder + "\\" + fileName + "_" + ISO_Date() + extension;

    if (!File.Exists(newName))
    {
        // info.MoveTo(newName);
    }
}

You should use fullFileName variable instead of string containing a path to file. Doing it this way is called "good programming practice" and let you avoid of several mistakes later.

MyApp = new Excel.Application();
MyApp.Visible = false;
//proper way:
xlBook = MyApp.Workbooks.Open(fullFileName);

To be able to modify sheet name, you should check its name firstly.

xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1); // Explicit cast is not required here
// Check and change sheet name to 'Sheet1'
if(xlSheet.Name != "Sheet1")
    xlSheet.Name = "Sheet1";
//you don't need to save a workbook now
//as i mentioned above, you can do it later by using SaveAs method
//xlBook.Save();

totalRows and totalCols variables have been initiated, but never used!
If you know the exact address of cell containing date (or date range or any other data), you can change its content directly!

//int totalRows = xlSheet.Rows.Count;
//int totalCols = xlSheet.Columns.Count;

// in second row 
// replace word 'yesterday' with date 
// in case of date range, remove the first date
string stmp = (string)xlSheet.Cells[2, 1].Value;
if(stmp == "yesterday")
    xlSheet.Cells[2, 1].Value = DateTime.Today.AddDays(-1);

if (stmp.Contains("-"))
    xlSheet.Cells[2, 1].Value = stmp.Remove(ind, ind);

Sorry, but the other part of code is not clear to me...

To save workbook in different location, call this:

string sNewFileName = Path.Combine(Path.GetDirectoryName(fullFileName), 
	string.Concat(Path.GetFileNameWithoutExtension(fullFileName), "_", DateTime.Today.ToString("yyyyMMdd"), ".xslx"));
xlBook.SaveAs(sNewFileName);
MyApp.Quit();
//read a Final note!

Final note: do not forget release resources! For further details, please see: How to Release COM Interop Objects so the Called Program Can Exit[^]

这篇关于如何在C＃中编辑excel文件（.xlsx）的单元格内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！