快捷導(dǎo)航

C#導(dǎo)入導(dǎo)出Excel數(shù)據(jù)的兩種方法

更新時(shí)間：2017年03月21日 17:26:13 作者：路博林

這篇文章主要為大家詳細(xì)介紹了C#導(dǎo)入導(dǎo)出Excel數(shù)據(jù)的兩種方法，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

本文為大家分享了C#導(dǎo)入導(dǎo)出Excel數(shù)據(jù)的具體代碼，供大家參考，具體內(nèi)容如下

注：對(duì)于實(shí)體類(lèi)對(duì)象最好新建一個(gè)并且繼承原有實(shí)體類(lèi)，這樣可以將類(lèi)型進(jìn)行修改；

方法一：此種方法是用EPPLUS中的FileInfo流進(jìn)行讀取的（是不是流我還真不太了解，若有懂得請(qǐng)留言，非常感謝了）

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("文件名不能為空");
 //因?yàn)檫@兒用得是EPPLUS對(duì)Excel進(jìn)行的操作，所以只能操作
 //2007以后的版本以后的（即擴(kuò)展名為.xlsx）
   if (!filePath.Trim().EndsWith("xlsx"))
    throw new Exception("請(qǐng)使用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> 打開(kāi)文件 </summary> 
  bool Open(); 
  //ExcelVersion Version { get; }
  /// <summary> 文件路徑 </summary> 
  string FilePath { get; set; }
  /// <summary> 文件是否已經(jīng)打開(kāi) </summary> 
  bool IfOpen { get; }
  /// <summary> 文件包含工作表的數(shù)量 </summary> 
  int SheetCount { get; }
  /// <summary> 當(dāng)前工作表序號(hào) </summary> 
  int CurrentSheetIndex { get; set; }
  /// <summary> 獲取當(dāng)前工作表中行數(shù) </summary> 
  int GetRowCount();
  /// <summary> 獲取當(dāng)前工作表中列數(shù) </summary> 
  int GetColumnCount();
  /// <summary> 獲取當(dāng)前工作表中某一行中單元格的數(shù)量 </summary> 
  /// <param name="Row">行序號(hào)</param> 
  int GetCellCountInRow(int Row);
  /// <summary> 獲取當(dāng)前工作表中某一單元格的值（按字符串返回） </summary> 
  /// <param name="Row">行序號(hào)</param> 
  /// <param name="Col">列序號(hào)</param> 
  string GetCellValue(int Row, int Col);
  /// <summary> 關(guān)閉文件 </summary> 
  void Close(); 
 }
}

方法實(shí)現(xiàn)：

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("工作表序號(hào)超出范圍"); 
     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; 
  } 
 //根據(jù)行號(hào)和列號(hào)獲取指定單元格的數(shù)據(jù)
  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(); 
  }   
 }   
}

方法調(diào)用實(shí)現(xiàn)功能：

//用于程序是在本地，所以此時(shí)的路徑是本地電腦的絕對(duì)路勁；
//當(dāng)程序發(fā)布后此路徑應(yīng)該是服務(wù)器上的絕對(duì)路徑，所以在此之前還要有
//一項(xiàng)功能是將本地文件上傳到服務(wù)器上的指定位置，此時(shí)在獲取路徑即可
 public string GetExcelToCtripPolicy(string filePath)
  {
   ExcelLib lib = new ExcelLib();
   if (filePath == null)
    return new ReturnResult<bool>(false, "未找到相應(yīng)文件");
   string str= tmp.GetCellValue(i, j); 
   return str;
  }

方法二：將Excel表格轉(zhuǎn)化成DataTable表，然后在對(duì)DataTable進(jìn)行業(yè)務(wù)操作

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的文件轉(zhuǎn)換成DataTable （Excel的第一個(gè)sheet）
  /// </summary>
  /// <param name="fullFielPath">文件的絕對(duì)路徑</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];//選定 指定頁(yè)

    return WorksheetToTable(worksheet);
   }
   catch (Exception)
   {
    throw;
   }
  }

  /// <summary>
  /// 將worksheet轉(zhuǎn)成datatable
  /// </summary>
  /// <param name="worksheet">待處理的worksheet</param>
  /// <returns>返回處理后的datatable</returns>
  public static DataTable WorksheetToTable(ExcelWorksheet worksheet)
  {
   //獲取worksheet的行數(shù)
   int rows = worksheet.Dimension.End.Row;
   //獲取worksheet的列數(shù)
   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++)
    {
     //默認(rèn)將第一行設(shè)置為datatable的標(biāo)題
     if (i == 1)
      dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
     //剩下的寫(xiě)入datatable
     else
      dr[j - 1] = GetString(worksheet.Cells[i, j].Value);
    }
   }
   return dt;
  }
 }
}

之前我有一個(gè)程序用的是方法一進(jìn)行Excel導(dǎo)入的，速度不是很快，后來(lái)我又用了第二種方法但是速度更慢了，到底這兩種方法哪種快，請(qǐng)指導(dǎo)，還是我用第二種方法的時(shí)候業(yè)務(wù)判斷有問(wèn)題，不得而知，就請(qǐng)明白人指導(dǎo)我到底這兩種方法哪種比較好些。

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章: