excel处理

最新推荐文章于 2024-04-14 19:29:11 发布

objsun

最新推荐文章于 2024-04-14 19:29:11 发布

阅读量637

点赞数

分类专栏： C# 文章标签： Excel

本文链接：https://blog.csdn.net/feigebuting/article/details/9835451

版权

C# 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、新建空白excel


using MyExcel=Microsoft.Office.Interop.Excel;
private void CreateExcel(string path)
{
     object missing=Type.Missing;
     MyExcel.Application excelApp=new MyExcel.Application();
     myExcel.WorkBook wBook=excelApp.Workbooks.Add(missing);
     wBook.SaveAs(path,missing,missing,missing,missing,missing,MyExcel.XlSaveAsAccessMode.xlShared,missing,missing, missing,missing,missing);
     wBook.Close(false,missing,missing);
     excelApp.Quit();
   
}

二、获取sheet的实际行数，以第一列为基准，为空则遍历结束


private int GetRealRowCount(MyExcel.Worksheet sheet)
{
   int rowIndex=1;
   MyExcel.Range range=null;
   do
   {
      range=(MyExcel.Range)sheet.Cells[RowIndex++,1]; 
   }
   while(range.Value2!=null&&range.Value2.ToString()!="");
   return rowIndex-2;
}

三、往已存在的excel中写数据


private void WriteToExcel(string path,DataTable dt)
{
    if(dt==null)
    return;
    object missing=Type.Missing;
    MyExcel.Application excelApp=new MyExcel.Application();
    MyExcel.Workbook wBook=MyExcel.Workbooks.open(path, missing, missing, missing, missing, missing,
                       missing,missing, missing, missing,missing, missing, missing, missing, missing);
    MyExcel.Worksheet sheet=(MyExcel.Worksheet)wBook.Worksheets.get_Item(1);
    int rowCount=GetRealRowCount(sheet);
    int excelRowIndex=rowCount==0?1:rowCount+1;//excel索引以1开始
    foreach(DataRow dr in dt.Rows)
    {
      ((MyExcel.Range)sheet.Cells)[ExcelRowIndex++,1]).Value2=dr["TEST"];
    }
    wBook.Save();
    wBook.Close(false, missing, missing);
    excelApp.Quit();
    GC.Collect();
}