Two ways to import and export Excel data in C#

This article shares the specific code for C# import and export Excel data for your reference. The specific content is as follows

Note: It is best to create a new entity class object and inherit the original entity class, so that the type can be modified;

Method 1:This method is read using the FileInfo stream in EPPLUS (I really don’t know much about whether it is a stream. If you understand, please leave a message. Thank you very much)

using System;
using ;
using ;
using ;
using ;
using ;

namespace 
{
 public class ExcelLib
 {
  public ICtripPolicyExcelImport GetExcel(string filePath)
  {
   if (() .IsNullOrEmpty())
    throw new Exception("File name cannot be empty");
 //Because EPPLUS is used here to operate on Excel, so you can only operate //After versions after 2007 (that is, the extension is .xlsx)   if (!().EndsWith("xlsx"))
    throw new Exception("Please use the 2007 or 2010 version");

   else if (().EndsWith("xlsx"))
   {
    ICtripPolicyExcelImport res = new CtripPolicyExcelImport(());
    return res;
   }
   else return null;
  }
 }
}

Method interface:

using System;
using ;
using ;
using ;
using ;

namespace 
{
 public interface ICtripPolicyExcelImport
 {
  /// <summary> Open the file </summary>  bool Open(); 
  //ExcelVersion Version { get; }
  /// <summary> File path </summary>  string FilePath { get; set; }
  /// <summary> Is the file already opened </summary>  bool IfOpen { get; }
  /// <summary> Number of worksheets that the file contains </summary>  int SheetCount { get; }
  /// <summary> Current worksheet number </summary>  int CurrentSheetIndex { get; set; }
  /// <summary> Get the number of rows in the current worksheet </summary>  int GetRowCount();
  /// <summary> Get the number of columns in the current worksheet </summary>  int GetColumnCount();
  /// <summary> Get the number of cells in a row in the current worksheet </summary>  /// <param name="Row">Line Serial Number</param>  int GetCellCountInRow(int Row);
  /// <summary> Get the value of a cell in the current worksheet (return by string) </summary>  /// <param name="Row">Line Serial Number</param>  /// <param name="Col">Column number</param>  string GetCellValue(int Row, int Col);
  /// <summary> Close the file </summary>  void Close(); 
 }
}

Method implementation:

using OfficeOpenXml;
using System;
using ;
using ;
using ;
using ;
using ;

namespace 
{
 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 =; 
    sheetCount = ; 
    currentSheetIndex = 0; 
    currentSheet = [1]; 
    ifOpen = true; 
   } 
   catch (Exception ex) 
   { 
    throw new Exception(); 
   } 
   return true; 
  } 
 
  public void Close() 
  { 
   if (!ifOpen || ep == null) return; 
   (); 
  } 
 
  //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 &gt;= sheetCount) 
      throw new Exception("Worksheet number out of range"); 
     currentSheetIndex = value; 
     currentSheet =[currentSheetIndex+1]; 
    } 
   } 
  } 
 
  public int GetRowCount() 
  { 
   if (currentSheet == null) return 0; 
   return ; 
  } 
 
  public int GetColumnCount() 
  { 
   if (currentSheet == null) return 0; 
   return ; 
  } 
 
  public int GetCellCountInRow(int Row) 
  { 
   if (currentSheet == null) return 0; 
   if (Row &gt;= ) return 0; 
   return ; 
  } 
 //Get data of the specified cell based on the row number and column number  public string GetCellValue(int Row, int Col) 
  { 
   if (currentSheet == null) return ""; 
   if (Row &gt;=  || Col &gt;= ) return ""; 
   object tmpO =(Row+1, Col+1); 
   if (tmpO == null) return ""; 
   return (); 
  }   
 }   
}

Method call implementation functions:

//The program is used locally, so the path at this time is the absolute path of the local computer;//After the program is released, this path should be the absolute path on the server, so there must be a//A function is to upload local files to a specified location on the server, and you can get the path at this time public string GetExcelToCtripPolicy(string filePath)
  {
   ExcelLib lib = new ExcelLib();
   if (filePath == null)
    return new ReturnResult&lt;bool&gt;(false, "No corresponding file found");
   string str= (i, j); 
   return str;
  }

Method 2:Convert Excel tables into DataTable tables, and then perform business operations on DataTable

using ;
using OfficeOpenXml;
using System;
using ;
using ;
using ;
using ;
using ;
using ;

namespace 
{
 public class EPPlusHelperAppService:ApplicationService,IEPPlusHelperAppService
 {
  private static string GetString(object obj)
  {
   try
   {
    return ();
   }
   catch (Exception ex)
   {
    return "";
   }
  }

  /// &lt;summary&gt;
  ///Convert the specified Excel file to DataTable (the first sheet of Excel)  /// &lt;/summary&gt;
  /// <param name="fullFielPath">Absolute path to file</param>  /// &lt;returns&gt;&lt;/returns&gt;
  public DataTable WorksheetToTable(string filePath)
  {
   try
   {
    FileInfo existingFile = new FileInfo(filePath);

    ExcelPackage package = new ExcelPackage(existingFile);
    ExcelWorksheet worksheet = [1];//Select Specify page
    return WorksheetToTable(worksheet);
   }
   catch (Exception)
   {
    throw;
   }
  }

  /// &lt;summary&gt;
  /// Convert worksheet to datatable  /// &lt;/summary&gt;
  /// <param name="worksheet">Pending worksheet</param>  /// <returns>Returns the processed datatable</returns>  public static DataTable WorksheetToTable(ExcelWorksheet worksheet)
  {
   //Get the number of lines of the worksheet   int rows = ;
   //Get the number of columns of the worksheet   int cols = ;

   DataTable dt = new DataTable();
   DataRow dr = null;
   for (int i = 1; i &lt;= rows; i++)
   {
    if (i &gt; 1)
     dr = ();

    for (int j = 1; j &lt;= cols; j++)
    {
     //By default, set the first line to the title of datatable     if (i == 1)
      (GetString([i, j].Value));
     //The remaining writes to datatable     else
      dr[j - 1] = GetString([i, j].Value);
    }
   }
   return dt;
  }
 }
}

Previously, I had a program that used a method to import Excel, but the speed was not very fast. Later, I used the second method, but the speed was slower. Which of these two methods is faster? Please guide me, or if there is a problem with the business judgment when I use the second method, I don’t know, so please understand who is better.

The above is all the content of this article. I hope it will be helpful to everyone's study and I hope everyone will support me more.