C#操作向Excel添加数据时,将之前数据覆盖了,怎么处理呢

[Hooper_he1]

请教下各位大师,我写的C#操作向Excel添加数据时,将之前数据覆盖了,怎么处理呢,谢谢!

int OKYield;
                    int NGYield;
                    int.TryParse(TbxOKyield.Text, out OKYield);
                    int.TryParse(TbxNGyield.Text, out NGYield);
                    int AllYield = OKYield + NGYield;
                    #region 创建保存Excel方法1  测试成功
                    // 文件保存路径及名称
                    string fileName = @"C:\Hooper_He\Yield.xlsx";
                    // 创建Excel文档
                    Microsoft.Office.Interop.Excel.Application ExcelApp
                        = new Microsoft.Office.Interop.Excel.Application();
                    Microsoft.Office.Interop.Excel.Workbook ExcelDoc = ExcelApp.Workbooks.Add(Type.Missing);
                    Microsoft.Office.Interop.Excel.Worksheet xlSheet = ExcelDoc.Worksheets.Add(Type.Missing,Type.Missing, Type.Missing, Type.Missing);
                    ExcelApp.DisplayAlerts = false;
                    #region   遍历Excel  计算总行数
                    int rowsnum = 0;
                    try
                    {
                        string strPath = @"C:\Hooper_He\Yield.xlsx";
                        string fileType = System.IO.Path.GetExtension(strPath);
                        string strCon = "";
                        if (fileType == ".xls")
                            strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
                        else
                            strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
                        OleDbConnection Con = new OleDbConnection(strCon);//建立连接
                        string strSql = "select * from [Sheet4$]";//表名的写法也应注意不同，对应的excel表为sheet1，在这里要在其后加美元符号$，并用中括号
                        OleDbCommand Cmd = new OleDbCommand(strSql, Con);//建立要执行的命令
                        OleDbDataAdapter da = new OleDbDataAdapter(Cmd);//建立数据适配器
                        DataSet ds = new DataSet();//新建数据集
                        da.Fill(ds, "shyman");//把数据适配器中的数据读到数据集中的一个表中（此处表名为shyman，可以任取表名）
                        DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
                        rowsnum = ds.Tables[0].Rows.Count;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);//捕捉异常
                    }
                    #endregion
                    // 单元格下标是从[1，1]开始的
                    xlSheet.Cells[1, 1] = "Time";
                    xlSheet.Cells[1, 2] = "OK_Data";
                    xlSheet.Cells[1, 3] = "NG_Data";
                    xlSheet.Cells[1, 4] = "All_Data";
                    for (int i = rowsnum + 2; i < rowsnum + 3; i++)
                    {
                        xlSheet.Cells[i, 1] = DateTime.Now.Month.ToString() + "月" + DateTime.Now.Day.ToString();
                        xlSheet.Cells[i, 2] = OKYield.ToString();
                        xlSheet.Cells[i, 3] = NGYield.ToString();
                        xlSheet.Cells[i, 4] = AllYield.ToString();
                    }
                    // 文件保存
                    xlSheet.SaveAs(fileName);
                    ExcelDoc.Close(Type.Missing, fileName, Type.Missing);
                    ExcelApp.Quit();
                    #endregion

```
 
```

[Hooper_he1]

有大神吗? 我同时还需要判断原Excel最后一行第一列的值是否是当天的时间,是的话只是覆盖后面3列数值,请指导,谢谢!

[Hooper_he1]

有人吗？继续顶下！！！

[Hooper_he1]

搞定了,附上代码,望大师指导

//写数据到Excel
        private static Microsoft.Office.Interop.Excel.Workbook mWorkBook;
        private static Microsoft.Office.Interop.Excel.Sheets mWorkSheets;
        private static Microsoft.Office.Interop.Excel.Worksheet mWSheet1;
        private static Microsoft.Office.Interop.Excel.Application oXL;
        public static void ReadExistingExcel(int a1,int a2,int a3)
        {
            string path = @"C:\Hooper_He\生产产量纪录.xls";
            oXL = new Microsoft.Office.Interop.Excel.Application();
            oXL.Visible = true;
            oXL.DisplayAlerts = false;
            mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            //Get all the sheets in the workbook
            mWorkSheets = mWorkBook.Worksheets;
            //Get the allready exists sheet
            mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
            Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
            int colCount = range.Columns.Count;
            int rowCount = range.Rows.Count;

            for (int index = 1; index < 2; index++)
            {
                if (mWSheet1.Cells[rowCount, 1].Value2.ToString() != DateTime.Now.ToString("yyyyMMdd"))
                {
                    mWSheet1.Cells[rowCount + index, 1] = DateTime.Now.ToString("yyyyMMdd");
                    mWSheet1.Cells[rowCount + index, 2] = a1;
                    mWSheet1.Cells[rowCount + index, 3] = a2;
                    mWSheet1.Cells[rowCount + index, 4] = a3;
                }
                else
                {
                    mWSheet1.Cells[rowCount, 2] = a1;
                    mWSheet1.Cells[rowCount, 3] = a2;
                    mWSheet1.Cells[rowCount, 4] = a3;
                }
            }
            mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
            mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
            mWSheet1 = null;
            mWorkBook = null;
            oXL.Quit();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }

[xhk008]

进来学习学习

[Justin-Liu]

自己debug吧

[Hooper_he1]

引用 3 楼 FoxDave 的回复:

自己debug吧

在网上找了,没有成功