添加到已创建的excel文件中
本文关键字：excel 文件 创建 添加 | 更新日期: 2023-09-27 18:17:22
我有这个excel文件，目前从我的c#应用程序写入内容到它的单元格内容:
private void button8_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = comboBox2.Text;
xlWorkSheet.Cells[1, 2] = textBox5.Text;
xlWorkSheet.Cells[1, 3] = textBox2.Text;
xlWorkSheet.Cells[1, 4] = comboBox3.Text;
xlWorkSheet.Cells[1, 5] = textBox3.Text;
xlWorkSheet.Cells[1, 6] = comboBox1.Text;
xlWorkBook.SaveAs(@"cross_check.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
MessageBox.Show("Excel file created succcessfully");
}
}
如何添加到已经创建的同一个文件?为了进一步展开，目前我必须指定要添加值的单元格。我怎么喜欢某种方式的增量，不管用户点击多少次添加到文件按钮，它应该只增加之前的模式。如。我有:
xlWorkSheet.Cells[1, 1] = comboBox2.Text;
xlWorkSheet.Cells[1, 2] = textBox5.Text;
xlWorkSheet.Cells[1, 3] = textBox2.Text;
xlWorkSheet.Cells[1, 4] = comboBox3.Text;
xlWorkSheet.Cells[1, 5] = textBox3.Text;
xlWorkSheet.Cells[1, 6] = comboBox1.Text;
单击按钮后，我该如何使它现在遵循这个模式:
xlWorkSheet.Cells[2, 1] = comboBox2.Text;
xlWorkSheet.Cells[2, 2] = textBox5.Text;
xlWorkSheet.Cells[2, 3] = textBox2.Text;
xlWorkSheet.Cells[2, 4] = comboBox3.Text;
xlWorkSheet.Cells[2, 5] = textBox3.Text;
xlWorkSheet.Cells[2, 6] = comboBox1.Text;
我想你是通过Microsoft.Office.Interop.Excel参考。然后您必须将代码修改为遵循
private void button8_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application xlApp; //Declare the
//Excel object
try
{
xlApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
}
catch (Exception ee)
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
}
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook=xlApp.Workbooks.Add(misValue);
try
{
xlWorkBook = xlApp.Workbooks.Open(@"cross_check.xls");//,
}
catch (Exception ex)
{
;//
}
Microsoft.Office.Interop.Excel.Range range;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet =
(Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int rownum = 1;
int MAX_ROWS=30000; //You may define your own limit
bool written = true;
range = xlWorkSheet.UsedRange;
while ((written) && (rownum<MAX_ROWS))
{
var test = (range.Cells[rownum, 1] as
Microsoft.Office.Interop.Excel.Range).Value2;
if (test != null)
{
rownum++;
}
else
{
written = false;
}
}
if (written == false)
{
xlWorkSheet.Cells[rownum, 1] = comboBox2.Text;
xlWorkSheet.Cells[rownum, 2] = textBox5.Text;
xlWorkSheet.Cells[rownum, 3] = textBox2.Text;
xlWorkSheet.Cells[rownum, 4] = comboBox3.Text;
xlWorkSheet.Cells[rownum, 5] = textBox3.Text;
xlWorkSheet.Cells[rownum, 6] = comboBox1.Text;
}
xlApp.DisplayAlerts = false; //Disables the prompts
xlWorkBook.SaveAs(@"cross_check.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);
xlApp.DisplayAlerts = true; //
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
MessageBox.Show("Excel file created/updated succcessfully");
}
在代码的第一步检查Excel对象，如果它已经存在运行，执行。如果是，我们不创建一个新的Excel对象，而是使用系统中正在运行的Excel对象。然后正确地创建或更新工作簿。保存时，必须使用
保存 Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared
，以便能够重新打开它并更新它。
为了加快数据输入，您可以使用引号来修改代码。
您应该添加一个额外的按钮，例如button9，并在click事件中使用引号代码，并对输入数据的button8进行必要的修改。此外，你必须声明变量xlApp,xlWorkBook,xlWorkSheet等是全局的和PUBLIC的，如下面的代码
............
public bool startd = false;
public int rownum;
public int MAX_ROWS = 30000;//You may define your own limit here
public bool isFirstTime = true;
public string oldBtnFileText;
public string oldBtnDataText;
public Microsoft.Office.Interop.Excel.Application xlApp;
public Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
public Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
public Microsoft.Office.Interop.Excel.Range range;
public object misValue;
private void button8_Click(object sender, EventArgs e)
{
if (startd == false)
{
return;
}
if (isFirstTime == true)
{
bool written = true;
int rnum = 1;
if (xlWorkSheet!=null)
{
range=xlWorkSheet.UsedRange;
while ((written) && (rnum < MAX_ROWS))
{
var test = (range.Cells[rnum, 1] as Microsoft.Office.Interop.Excel.Range).Value2;
if (test != null)
{
rnum++;
}
else
{
written = false;
}
}
if (written == false)
{
rownum = rnum;
isFirstTime = false;
}
else
{
MessageBox.Show("The current WorkSheet is Full");
return;
}
}
}
if (xlWorkSheet!=null)
{
xlWorkSheet.Cells[rownum, 1] = comboBox2.Text;
xlWorkSheet.Cells[rownum, 2] = textBox5.Text;
xlWorkSheet.Cells[rownum, 3] = textBox2.Text;
xlWorkSheet.Cells[rownum, 4] = comboBox3.Text;
xlWorkSheet.Cells[rownum, 5] = textBox3.Text;
xlWorkSheet.Cells[rownum, 6] = comboBox1.Text;
rownum++;
}
}
private void button9_Click(object sender, EventArgs e)
{
if (startd == false)
{
try
{
xlApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
}
catch (Exception ee)
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
}
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
try
{
xlWorkBook = xlApp.Workbooks.Open(@"cross_check.xls");//,
}
catch (Exception ex)
{
;//
}
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
oldBtnFileText = button9.Text.ToString();
button9.Text = "File Ready to accept data";
oldBtnDataText = button1.Text.ToString();
button8.Text = "Enter Data";
startd = true;
}
else
{
xlApp.DisplayAlerts = false;
xlWorkBook.SaveAs(@"cross_check.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);
xlApp.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
MessageBox.Show("Excel file created/updated succcessfully");
startd = false;
button9.Text = oldBtnFileText; //Restore the initial captions
button8.Text = oldBtnDataText;//...
}
}
//