haiping_bboy
Hooper_he1
2016-08-30 09:01

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

请教下各位大师,我写的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


  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

1条回答