将数据写入在C#中现有的Excel文件 [英] Writing Data to an Existing Excel File in C#
问题描述
我想将数据写入到现有的Excel文件。
该文件有Sheet1中,我想写的Sheet2中,然后保存。
问题是,我每次保存它会创建一个新的Excel文件并覆盖现有之一。任何有助于保持旧数据,保存。
我有以下功能
使用System.Windows.Forms的;
使用Excel =的Microsoft.Office.Interop.Excel;命名空间的WindowsApplication1
{
公共部分Form1类:表格
{
公共Form1中()
{
的InitializeComponent();
} 私人无效的button1_Click(对象发件人,EventArgs的发送)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
反对misValue = System.Reflection.Missing.Value; xlApp =新Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(csharp.net-informations.xls,0,真实,5,,,真实,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,\\ t的,假的,假0,真的,1,0);
xlWorkSheet =(Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); MessageBox.Show(xlWorkSheet.get_Range(A1,A1)Value2.ToString()); xlWorkBook.Close(真,misValue,misValue);
xlApp.Quit(); releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
} 私人无效releaseObject(obj对象)
{
尝试
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(OBJ);
OBJ = NULL;
}
赶上(异常前)
{
OBJ = NULL;
MessageBox.Show(无法释放对象+ ex.ToString());
}
最后
{
所以GC.Collect();
}
}
}
}
这是我的方式将数据添加到现有的excel文件:(它非常简单而有效的)
1 - 添加的Microsoft.Office.Interop.Excel组件为您的应用程序的引用
你可以找到它在.NET框架的扩展部分
2 - 然后添加:
使用Excel =的Microsoft.Office.Interop.Excel;
使用的System.Reflection;
3现在我有3种方法(openExcel,addDataToExcel,closeExcel)一个简单的类
公共类ExcelFile
{ 私人字符串excelFilePath =的String.Empty;
私人INT ROWNUMBER = 1; //定义第一个行号在Excel中输入数据 Excel.Application myExcelApplication;
Excel.Workbook myExcelWorkbook;
Excel.Worksheet myExcelWorkSheet; 公共字符串ExcelFilePath
{
{返回excelFilePath; }
集合{excelFilePath =价值; }
} 公众诠释ROWNUMBER
{
{返回ROWNUMBER; }
集合{ROWNUMBER =价值; }
} 公共无效openExcel()
{
myExcelApplication = NULL; myExcelApplication =新Excel.Application(); //创建的Excell应用
myExcelApplication.DisplayAlerts = FALSE; //关闭警报
myExcelWorkbook =(Excel.Workbook)(myExcelApplication.Workbooks._Open(excelFilePath,System.Reflection.Missing.Value,
System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,
System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,
System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,
System.Reflection.Missing.Value,System.Reflection.Missing.Value)); //打开现有的excel文件 INT numberOfWorkbooks = myExcelApplication.Workbooks.Count; //获取工作簿号(可选) myExcelWorkSheet =(Excel.Worksheet)myExcelWorkbook.Worksheets [1]; //定义在工作表中,你想添加数据
myExcelWorkSheet.Name =工作1; //为工作表定义一个名称(optinal) INT numberOfSheets = myExcelWorkbook.Worksheets.Count; //获取工作表数(可选)
} 公共无效addDataToExcel(串名字,姓氏字符串,字符串语言,字符串email,字符串公司)
{ myExcelWorkSheet.Cells [ROWNUMBER,H] =名字;
myExcelWorkSheet.Cells [ROWNUMBER,J] =姓氏;
myExcelWorkSheet.Cells [ROWNUMBER,Q] =语言;
myExcelWorkSheet.Cells [ROWNUMBER,BH] =电子邮件;
myExcelWorkSheet.Cells [ROWNUMBER,CH] =公司;
ROWNUMBER ++; //如果你把这个方法的循环中,你应该用一个或笏增加ROWNUMBER永远是你的逻辑 } 公共无效closeExcel()
{
尝试
{
myExcelWorkbook.SaveAs(excelFilePath,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,
System.Reflection.Missing.Value,System.Reflection.Missing.Value,Excel.XlSaveAsAccessMode.xlNoChange,
System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,
System.Reflection.Missing.Value,System.Reflection.Missing.Value);在Excel //保存数据
myExcelWorkbook.Close(真,excelFilePath,System.Reflection.Missing.Value); //关闭工作表
}
最后
{
如果(myExcelApplication!= NULL)
{
myExcelApplication.Quit(); //关闭Excel应用程序
}
} }
}
i want to write data to existing excel file. the file have sheet1, i want to write on sheet2, then save. the problem is that every time i save it will create a new excel file and overwrite the existing one. any help to keep the old data while saving.
i have the following function
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
MessageBox.Show(xlWorkSheet.get_Range("A1","A1").Value2.ToString());
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
This is my way to add data to existing excel file: (Its very simple and efficient)
1 - Add Microsoft.Office.Interop.Excel component as a reference to your application You can find it in .Net FrameWork in Extensions section
2- then add:
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
3- Now I have a simple class with 3 methods (openExcel, addDataToExcel, closeExcel)
public class ExcelFile
{
private string excelFilePath = string.Empty;
private int rowNumber = 1; // define first row number to enter data in excel
Excel.Application myExcelApplication;
Excel.Workbook myExcelWorkbook;
Excel.Worksheet myExcelWorkSheet;
public string ExcelFilePath
{
get { return excelFilePath; }
set { excelFilePath = value; }
}
public int Rownumber
{
get { return rowNumber; }
set { rowNumber = value; }
}
public void openExcel()
{
myExcelApplication = null;
myExcelApplication = new Excel.Application(); // create Excell App
myExcelApplication.DisplayAlerts = false; // turn off alerts
myExcelWorkbook = (Excel.Workbook)(myExcelApplication.Workbooks._Open(excelFilePath, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value)); // open the existing excel file
int numberOfWorkbooks = myExcelApplication.Workbooks.Count; // get number of workbooks (optional)
myExcelWorkSheet = (Excel.Worksheet)myExcelWorkbook.Worksheets[1]; // define in which worksheet, do you want to add data
myExcelWorkSheet.Name = "WorkSheet 1"; // define a name for the worksheet (optinal)
int numberOfSheets = myExcelWorkbook.Worksheets.Count; // get number of worksheets (optional)
}
public void addDataToExcel(string firstname, string lastname, string language, string email, string company)
{
myExcelWorkSheet.Cells[rowNumber, "H"] = firstname;
myExcelWorkSheet.Cells[rowNumber, "J"] = lastname;
myExcelWorkSheet.Cells[rowNumber, "Q"] = language;
myExcelWorkSheet.Cells[rowNumber, "BH"] = email;
myExcelWorkSheet.Cells[rowNumber, "CH"] = company;
rowNumber++; // if you put this method inside a loop, you should increase rownumber by one or wat ever is your logic
}
public void closeExcel()
{
try
{
myExcelWorkbook.SaveAs(excelFilePath, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value); // Save data in excel
myExcelWorkbook.Close(true, excelFilePath, System.Reflection.Missing.Value); // close the worksheet
}
finally
{
if (myExcelApplication != null)
{
myExcelApplication.Quit(); // close the excel application
}
}
}
}
这篇关于将数据写入在C#中现有的Excel文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!