C#實現匯入匯出Excel資料的兩種方法詳解

這篇文章主要為大家詳細介紹了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匯入的，速度不是很快，後來我又用了第二種方法但是速度更慢了，到底這兩種方法哪種快，請指導，還是我用第二種方法的時候業務判斷有問題，不得而知，就請明白人指導我到底這兩種方法哪種比較好些。