C#导入导出Excel数据的两种方法

本文为大家分享了c#导入导出excel数据的具体代码，供大家参考，具体内容如下

注：对于实体类对象最好新建一个并且继承原有实体类，这样可以将类型进行修改；

方法一：此种方法是用epplus中的fileinfo流进行读取的（是不是流我还真不太了解，若有懂得请留言，非常感谢了）

using system;
using system.collections.generic;
using system.linq;
using system.text;
using system.threading.tasks;
using abp.extensions;

namespace hyzt.ltxy.international.ctrip.exporting
{
 public class excellib
 {
  public ictrippolicyexcelimport getexcel(string filepath)
  {
   if (filepath.trim() .isnullorempty())
    throw new exception("文件名不能为空");
 //因为这儿用得是epplus对excel进行的操作，所以只能操作
 //2007以后的版本以后的（即扩展名为.xlsx）
   if (!filepath.trim().endswith("xlsx"))
    throw new exception("请使用office excel 2007版本或2010版本");

   else if (filepath.trim().endswith("xlsx"))
   {
    ictrippolicyexcelimport res = new ctrippolicyexcelimport(filepath.trim());
    return res;
   }
   else return null;
  }
 }
}

方法接口：

using system;
using system.collections.generic;
using system.linq;
using system.text;
using system.threading.tasks;

namespace hyzt.ltxy.international.ctrip.exporting
{
 public interface ictrippolicyexcelimport
 {
  /// <summary> 打开文件 </summary> 
  bool open(); 
  //excelversion version { get; }
  /// <summary> 文件路径 </summary> 
  string filepath { get; set; }
  /// <summary> 文件是否已经打开 </summary> 
  bool ifopen { get; }
  /// <summary> 文件包含工作表的数量 </summary> 
  int sheetcount { get; }
  /// <summary> 当前工作表序号 </summary> 
  int currentsheetindex { get; set; }
  /// <summary> 获取当前工作表中行数 </summary> 
  int getrowcount();
  /// <summary> 获取当前工作表中列数 </summary> 
  int getcolumncount();
  /// <summary> 获取当前工作表中某一行中单元格的数量 </summary> 
  /// <param name="row">行序号</param> 
  int getcellcountinrow(int row);
  /// <summary> 获取当前工作表中某一单元格的值（按字符串返回） </summary> 
  /// <param name="row">行序号</param> 
  /// <param name="col">列序号</param> 
  string getcellvalue(int row, int col);
  /// <summary> 关闭文件 </summary> 
  void close(); 
 }
}

方法实现：

using officeopenxml;
using system;
using system.collections.generic;
using system.io;
using system.linq;
using system.text;
using system.threading.tasks;

namespace hyzt.ltxy.international.ctrip.exporting
{
 public class ctrippolicyexcelimport:ictrippolicyexcelimport
 {

  public ctrippolicyexcelimport() 
  { } 
 
  public ctrippolicyexcelimport(string path) 
  { filepath = path; }


  private string filepath = "";
  private excelworkbook book = null;
  private int sheetcount = 0;
  private bool ifopen = false;
  private int currentsheetindex = 0;
  private excelworksheet currentsheet = null;
  private excelpackage ep = null; 
 
   public bool open() 
  { 
   try 
   { 
    ep = new excelpackage(new fileinfo(filepath)); 
     
    if (ep == null) return false; 
    book =ep.workbook; 
    sheetcount = book.worksheets.count; 
    currentsheetindex = 0; 
    currentsheet = book.worksheets[1]; 
    ifopen = true; 
   } 
   catch (exception ex) 
   { 
    throw new exception(ex.message); 
   } 
   return true; 
  } 
 
  public void close() 
  { 
   if (!ifopen || ep == null) return; 
   ep.dispose(); 
  } 
 
  //public excelversion version 
  //{ get { return excelversion.excel07; } } 
 
  public string filepath 
  { 
   get { return filepath; } 
   set { filepath = value; } 
  } 
 
  public bool ifopen 
  { get { return ifopen; } } 
 
  public int sheetcount 
  { get { return sheetcount; } } 
 
  public int currentsheetindex 
  { 
   get { return currentsheetindex; } 
   set 
   { 
    if (value != currentsheetindex) 
    { 
     if (value >= sheetcount) 
      throw new exception("工作表序号超出范围"); 
     currentsheetindex = value; 
     currentsheet =book.worksheets[currentsheetindex+1]; 
    } 
   } 
  } 
 
  public int getrowcount() 
  { 
   if (currentsheet == null) return 0; 
   return currentsheet.dimension.end.row; 
  } 
 
  public int getcolumncount() 
  { 
   if (currentsheet == null) return 0; 
   return currentsheet.dimension.end.column; 
  } 
 
  public int getcellcountinrow(int row) 
  { 
   if (currentsheet == null) return 0; 
   if (row >= currentsheet.dimension.end.row) return 0; 
   return currentsheet.dimension.end.column; 
  } 
 //根据行号和列号获取指定单元格的数据
  public string getcellvalue(int row, int col) 
  { 
   if (currentsheet == null) return ""; 
   if (row >= currentsheet.dimension.end.row || col >= currentsheet.dimension.end.column) return ""; 
   object tmpo =currentsheet.getvalue(row+1, col+1); 
   if (tmpo == null) return ""; 
   return tmpo.tostring(); 
  }   
 }   
}

方法调用实现功能：

//用于程序是在本地，所以此时的路径是本地电脑的绝对路劲；
//当程序发布后此路径应该是服务器上的绝对路径，所以在此之前还要有
//一项功能是将本地文件上传到服务器上的指定位置，此时在获取路径即可
 public string getexceltoctrippolicy(string filepath)
  {
   excellib lib = new excellib();
   if (filepath == null)
    return new returnresult<bool>(false, "未找到相应文件");
   string str= tmp.getcellvalue(i, j); 
   return str;
  }

 

方法二：将excel表格转化成datatable表，然后在对datatable进行业务操作

using abp.application.services;
using officeopenxml;
using system;
using system.collections.generic;
using system.data;
using system.io;
using system.linq;
using system.text;
using system.threading.tasks;

namespace hyzt.ltxy.international.ctrip.getexceltodatatable
{
 public class epplushelperappservice:applicationservice,iepplushelperappservice
 {
  private static string getstring(object obj)
  {
   try
   {
    return obj.tostring();
   }
   catch (exception ex)
   {
    return "";
   }
  }

  /// <summary>
  ///将指定的excel的文件转换成datatable （excel的第一个sheet）
  /// </summary>
  /// <param name="fullfielpath">文件的绝对路径</param>
  /// <returns></returns>
  public datatable worksheettotable(string filepath)
  {
   try
   {
    fileinfo existingfile = new fileinfo(filepath);

    excelpackage package = new excelpackage(existingfile);
    excelworksheet worksheet = package.workbook.worksheets[1];//选定 指定页

    return worksheettotable(worksheet);
   }
   catch (exception)
   {
    throw;
   }
  }

  /// <summary>
  /// 将worksheet转成datatable
  /// </summary>
  /// <param name="worksheet">待处理的worksheet</param>
  /// <returns>返回处理后的datatable</returns>
  public static datatable worksheettotable(excelworksheet worksheet)
  {
   //获取worksheet的行数
   int rows = worksheet.dimension.end.row;
   //获取worksheet的列数
   int cols = worksheet.dimension.end.column;

   datatable dt = new datatable(worksheet.name);
   datarow dr = null;
   for (int i = 1; i <= rows; i++)
   {
    if (i > 1)
     dr = dt.rows.add();

    for (int j = 1; j <= cols; j++)
    {
     //默认将第一行设置为datatable的标题
     if (i == 1)
      dt.columns.add(getstring(worksheet.cells[i, j].value));
     //剩下的写入datatable
     else
      dr[j - 1] = getstring(worksheet.cells[i, j].value);
    }
   }
   return dt;
  }
 }
}

之前我有一个程序用的是方法一进行excel导入的，速度不是很快，后来我又用了第二种方法但是速度更慢了，到底这两种方法哪种快，请指导，还是我用第二种方法的时候业务判断有问题，不得而知，就请明白人指导我到底这两种方法哪种比较好些。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。