使用 C# 将数据附加到现有 Excel 文件 [英] Appending data to existing Excel file using C#
本文介绍了使用 C# 将数据附加到现有 Excel 文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我对 C# 还很陌生，我正在尝试将一些数据从 C# 中的 DataGridView 导出到 Excel 文件中.来自 datagridview 的输入由用户填写.
目前，我的程序可以使用给定日期作为文件名的 datagridview 中的值创建一个 excel 文件.
我的问题是，如果 excel 文件已经存在，我似乎找不到从 gridview 附加数据的方法，它会改写当前的 excel 文件.
非常感谢任何帮助/提示/建议.
谢谢:)
这是我的代码:
Microsoft.Office.Interop.Excel.Application xlApp;Microsoft.Office.Interop.Excel.Workbook xlWorkBook;Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;Microsoft.Office.Interop.Excel.Sheets xlBigSheet;Microsoft.Office.Interop.Excel.Sheets xlSheet;对象错误值；字符串新路径；私有无效按钮OK_Click(对象发送者，EventArgs e){createXLSfile();}私有无效 createXLSfile(){String cDate = datePicker.Value.ToShortDateString();String cMonth = datePicker.Value.ToString("MMMM");String cYear = datePicker.Value.ToString("yy");String cDay = datePicker.Value.ToString("dd");String fName = cDay + "-" + cMonth+ "-" + cYear + ".xls";String mainPath = @"C:\Users\User1\Desktop\" + cYear;String folderPath = System.IO.Path.Combine(mainPath, cMonth);String excelPath = System.IO.Path.Combine(folderPath, fName);System.IO.Directory.CreateDirectory(mainPath);System.IO.Directory.CreateDirectory(folderPath);String fNameOnly = Path.GetFileNameWithoutExtension(excelPath);String extension = Path.GetExtension(excelPath);String path = Path.GetDirectoryName(excelPath);newPath = excelPath;如果(文件.存在(新路径)){现有文件()；}别的{新文件();}MessageBox.Show("提交");}私有无效 newFile(){xlApp = new Microsoft.Office.Interop.Excel.Application();xlApp.Visible = true;misValue = System.Reflection.Missing.Value;xlWorkBook = xlApp.Workbooks.Add(misValue);xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);xlWorkSheet = xlWorkBook.Sheets["Sheet1"];xlWorkSheet = xlWorkBook.ActiveSheet;xlWorkSheet.Name = "Sheet1";xlWorkSheet.Cells[2, 1] = "Header1";xlWorkSheet.Cells[2, 2] = "Header2";xlWorkSheet.Cells[2, 3] = "总计";获取数据()；xlWorkBook.SaveAs(newFullPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,misValue、misValue、misValue、Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive、misValue、misValue、misValue、misValue、misValue)；xlWorkBook.Close(true, misValue, misValue);xlApp.退出();Marshal.ReleaseComObject(xlWorkSheet);Marshal.ReleaseComObject(xlWorkBook);Marshal.ReleaseComObject(xlApp);}私有无效现有文件(){xlApp = new Microsoft.Office.Interop.Excel.Application();xlApp.Visible = true;xlWorkBook = xlApp.Workbooks.Open(newPath, 0,假，5，"，"，假，Microsoft.Office.Interop.Excel.XlPlatform.xlWindows，"", true, false, 0, true, false, false);xlBigSheet = xlWorkBook.Worksheets;字符串 x = "Sheet1";xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBigSheet.get_Item(x);获取数据()；xlWorkBook.SaveAs(newPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,misValue、misValue、misValue、misValue、Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive、misValue, misValue, misValue,错误值，错误值)；xlWorkBook.Close(misValue, misValue, misValue);xlWorkBook = null;xlApp.退出();GC.WaitForPendingFinalizers();GC.Collect();GC.WaitForPendingFinalizers();GC.Collect();}私有无效 getData(){双 a,b,c,d,total = 0;int lastRow_ = 3;foreach(DataGridViewRow r 在 dataGridView1.Rows){如果(！r.IsNewRow){a = Convert.ToDouble(r.Cells[2].Value);b = Convert.ToDouble(r.Cells[3].Value);c = Convert.ToDouble(r.Cells[4].Value);d = Convert.ToDouble(r.Cells[5].Value);总计 = a + b + c + d；xlWorkSheet.Cells[lastRow_, 1] = "嗨";xlWorkSheet.Cells[lastRow_, 2] = "你好";xlWorkSheet.Cells[lastRow_, 3] = Convert.ToString(total);lastRow_ = xlWorkSheet.Cells.Find("*",xlWorkSheet.Cells[1, 1],错误值，Microsoft.Office.Interop.Excel.XlLookAt.xlPart，Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows，Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,错误值，错误值，misValue).Row + 1;}}总计 = 0;}更新 1:还是卡住了.一直试图按照这个链接:
解决方案
当您需要向现有工作表追加数据时，您需要找出最后使用的行的位置，并在该行之后开始添加数据.您当前获取最后"行的代码很尴尬，因为一旦开始添加行，您就会不断检查最后"行，这是不必要的.getData() 方法只是将数据添加到新的 excel 文件中，其中最后一行无关紧要.如果文件存在，那么您只需要获取最后使用的行并开始导入下一行的数据.我猜随着您的代码运行，发送 GetData(RowToStart) 方法的起始行索引并简单地增加 lastRow_ 变量可能会更好，如下所示:无需继续检查最后一行.
private void getData(int lastRow_) {双 a, b, c, d, 总计 = 0;//int lastRow_ = 4;foreach(dataGridView1.Rows 中的 DataGridViewRow r){//如果(！row.IsNewRow){如果(！r.IsNewRow){a = Convert.ToDouble(r.Cells[2].Value);b = Convert.ToDouble(r.Cells[3].Value);c = Convert.ToDouble(r.Cells[4].Value);d = Convert.ToDouble(r.Cells[5].Value);总计 = a + b + c + d；xlWorkSheet.Cells[lastRow_, 1] = "嗨";xlWorkSheet.Cells[lastRow_, 2] = "你好";xlWorkSheet.Cells[lastRow_, 3] = Convert.ToString(total);lastRow_++;//lastRow_ = xlWorkSheet.Cells.Find(//"*",//xlWorkSheet.Cells[1, 1],//错误值，//Microsoft.Office.Interop.Excel.XlLookAt.xlPart,//Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,//Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,//错误值，//错误值，//misValue).Row + 1;}}总计 = 0;}如果文件是新的，你可以像下面这样调用这个方法.
...xlWorkSheet.Cells[3, 1] = "Header1";xlWorkSheet.Cells[3, 2] = "Header2";xlWorkSheet.Cells[3, 3] = "总计";获取数据(4)；...如果文件已经存在并且您需要将数据附加到现有工作表中，您需要获取最后使用的行，然后从下一行开始.您可以像下面这样调用 getData(RowToStart).
...xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBigSheet.get_Item("Sheet1");Microsoft.Office.Interop.Excel.Range last = xlWorkSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);int lastUsedRow = last.Row;getData(lastUsedRow + 1);...我希望这是有道理的.
I'm fairly new with C# and I am trying to export some data from a DataGridView in C# into an Excel file. The inputs from the datagridview are filled in by the user.
Currently, my program can create an excel file along with the values from the datagridview with the given date as its file name.
My problem is I can't seem to find a way to append the data from the gridview IF the excel file already exists, it overwrites the current excel file instead.
Any help/tips/suggestion is highly appreciated.
Thanks :)
Here is my code:
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Microsoft.Office.Interop.Excel.Sheets xlBigSheet;
Microsoft.Office.Interop.Excel.Sheets xlSheet;
object misValue;
String newPath;
private void buttonOK_Click(object sender, EventArgs e)
{
createXLSfile();
}
private void createXLSfile(){
String cDate = datePicker.Value.ToShortDateString();
String cMonth = datePicker.Value.ToString("MMMM");
String cYear = datePicker.Value.ToString("yy");
String cDay = datePicker.Value.ToString("dd");
String fName = cDay + "-" + cMonth+ "-" + cYear + ".xls";
String mainPath = @"C:\Users\User1\Desktop\" + cYear;
String folderPath = System.IO.Path.Combine(mainPath, cMonth);
String excelPath = System.IO.Path.Combine(folderPath, fName);
System.IO.Directory.CreateDirectory(mainPath);
System.IO.Directory.CreateDirectory(folderPath);
String fNameOnly = Path.GetFileNameWithoutExtension(excelPath);
String extension = Path.GetExtension(excelPath);
String path = Path.GetDirectoryName(excelPath);
newPath = excelPath;
if(File.Exists(newPath))
{
existingFile();
}else
{
newFile();
}
MessageBox.Show("Submitted");
}
private void newFile()
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = true;
misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet = xlWorkBook.Sheets["Sheet1"];
xlWorkSheet = xlWorkBook.ActiveSheet;
xlWorkSheet.Name = "Sheet1";
xlWorkSheet.Cells[2, 1] = "Header1";
xlWorkSheet.Cells[2, 2] = "Header2";
xlWorkSheet.Cells[2, 3] = "Total";
getData();
xlWorkBook.SaveAs(newFullPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,
misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
private void existingFile()
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Open(newPath, 0,
false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"", true, false, 0, true, false, false);
xlBigSheet = xlWorkBook.Worksheets;
string x = "Sheet1";
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBigSheet.get_Item(x);
getData();
xlWorkBook.SaveAs(newPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue,
misValue, misValue);
xlWorkBook.Close(misValue, misValue, misValue);
xlWorkBook = null;
xlApp.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
private void getData()
{
double a,b,c,d,total = 0;
int lastRow_ = 3;
foreach(DataGridViewRow r in dataGridView1.Rows)
{
if(!r.IsNewRow)
{
a = Convert.ToDouble(r.Cells[2].Value);
b = Convert.ToDouble(r.Cells[3].Value);
c = Convert.ToDouble(r.Cells[4].Value);
d = Convert.ToDouble(r.Cells[5].Value);
total = a + b + c + d;
xlWorkSheet.Cells[lastRow_, 1] = "Hi";
xlWorkSheet.Cells[lastRow_, 2] = "Hello";
xlWorkSheet.Cells[lastRow_, 3] = Convert.ToString(total);
lastRow_ = xlWorkSheet.Cells.Find(
"*",
xlWorkSheet.Cells[1, 1],
misValue,
Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,
misValue,
misValue,
misValue).Row + 1;
}
}
total = 0;
}
Update 1: Still stuck. Been trying to follow this link: https://www.codeproject.com/articles/5123/opening-and-navigating-excel-with-c
OUTPUT
解决方案
When you need to append data to an existing worksheet, you need to find out where the last used row is and start adding data after this row. Your current code to get this "last" row is awkward as once you start adding rows you keep checking for this "last" row which is unnecessary. The getData() method is simply adding data to a new excel file where the last row won’t matter. If the file exists, then you simply need to get the last used row and start importing the data on the next row. I am guessing it may be better as your code goes, to send over a starting row index for the GetData(RowToStart) method and simply increment the lastRow_ variable, like below: There is no need to keep checking for this last row.
private void getData(int lastRow_) {
double a, b, c, d, total = 0;
//int lastRow_ = 4;
foreach (DataGridViewRow r in dataGridView1.Rows) {
//if (!row.IsNewRow) {
if (!r.IsNewRow) {
a = Convert.ToDouble(r.Cells[2].Value);
b = Convert.ToDouble(r.Cells[3].Value);
c = Convert.ToDouble(r.Cells[4].Value);
d = Convert.ToDouble(r.Cells[5].Value);
total = a + b + c + d;
xlWorkSheet.Cells[lastRow_, 1] = "Hi";
xlWorkSheet.Cells[lastRow_, 2] = "Hello";
xlWorkSheet.Cells[lastRow_, 3] = Convert.ToString(total);
lastRow_++;
//lastRow_ = xlWorkSheet.Cells.Find(
// "*",
// xlWorkSheet.Cells[1, 1],
// misValue,
// Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
// Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
// Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,
// misValue,
// misValue,
// misValue).Row + 1;
}
}
total = 0;
}
If the file is new, you would call this method like below.
.
.
.
xlWorkSheet.Cells[3, 1] = "Header1";
xlWorkSheet.Cells[3, 2] = "Header2";
xlWorkSheet.Cells[3, 3] = "Total";
getData(4);
.
.
.
If the file already exists and you need to append the data to existing worksheet you need to get the last used row then start on the next row. You can call getData(RowToStart) like below.
.
.
.
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBigSheet.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range last = xlWorkSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
int lastUsedRow = last.Row;
getData(lastUsedRow + 1);
.
.
.
I hope this makes sense.
这篇关于使用 C# 将数据附加到现有 Excel 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
