用c#填充excel表格

本文关键字：表格 excel 填充 | 更新日期: 2023-09-27 18:05:53

我的程序结构出了问题。我的Excel表应该更新一个单元格的数据，每次当我按下一个按钮在我的windows窗体应用程序。

下面是我的代码: 
using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace Test6attendance
{
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;
        if (File.Exists("D:''login.xlscsharp-Excel.xls"))
        {
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open("D:''login.xlscsharp-Excel.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);
            for (int i = 1; i < 55555; i++)
            {

                if (xlWorkSheet.Cells[i, 1] == null)
                {
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                    xlWorkSheet.Cells[i, 1] = DateTime.Now.ToString("MM/dd/ yyyy, hh:mm:ss tt");
                    xlWorkBook.SaveAs("D:''login.xlscsharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
                    releaseObject(xlWorkSheet);
                    releaseObject(xlWorkBook);
                    releaseObject(xlApp);
                    MessageBox.Show("Excel file updated , you can find the file D:''csharp-Excel.xls");
                }
            }

        }
        else
        {
            //Create New Code

            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);


            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet.Cells[1, 1] = DateTime.Now.ToString("MM/dd/ yyyy, hh:mm:ss tt");

            xlWorkBook.SaveAs("D:''login.xlscsharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
            MessageBox.Show("Excel file created , you can find the file D:''csharp-Excel.xls");
        }
    }
         private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }
    private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
    {
    }
    }

}

我认为问题在于for循环，它似乎不起作用。需要一些建议或参考来修复它

用c#填充excel表格

问题在于

if (xlWorkSheet.Cells[i, 1] == null)

应该是

 if (xlWorkSheet.Cells[i, 1] == null)

由于您的第一个if条件检查文件是否存在if (File.Exists("D:''login.xlscsharp-Excel.xls"))，如果不存在则创建一个文件并将值赋给单元格[1,1]。现在，当你第二次点击单元格不是空的时候，你的值不会被更新。

试试这个代码

for (int i = 1; i < 55555; i++)
            {

                if (xlWorkSheet.Cells[i, 1] != null)
                {
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                    xlWorkSheet.Cells[i, 1] = DateTime.Now.ToString("MM/dd/ yyyy, hh:mm:ss tt");
                    xlWorkBook.SaveAs("D:''login.xlscsharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
                    releaseObject(xlWorkSheet);
                    releaseObject(xlWorkBook);
                    releaseObject(xlApp);
                    MessageBox.Show("Excel file updated , you can find the file D:''csharp-Excel.xls");
                    break;
                }
            }

你的循环很奇怪。您应该将循环中的所有代码放在循环的外部。唯一需要保留的是赋值的那一行。否则，每保存一行就保存应用程序。更不用说在分配第一个值后释放所有对象。我猜这只能写一个值，然后在for循环的第一行引发许多异常(您尝试从已经释放的工作簿对象中获取工作表)。

应该是这样的:

for (int i = 1; i < 55555; i++)
{
   if (xlWorkSheet.Cells[i, 1] != null)
   {
       xlWorkSheet.Cells[i, 1] = DateTime.Now.ToString("MM/dd/ yyyy, hh:mm:ss tt"); 
   }
}
xlWorkBook.Save("D:''login.xlscsharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file updated , you can find the file D:''csharp-Excel.xls");
相关文章：