Two Methods of C Import and Export Excel Data

This article shares the specific code of C # importing and exporting Excel data for your reference, the specific contents are as follows

Note: For the Entity Class object, it is best to create a new one and inherit the original entity class, so that the type can be modified;

Method 1: This method is read by FileInfo stream in EPPLUS (I really don't know if it is a stream, please leave a message if you know it, thank you very much)

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(" File name cannot be empty ");
 // Because what is used here is EPPLUS Right Excel The operation performed, so you can only operate the 
 //2007 After a later version (that is, with the extension of .xlsx ) 
   if (!filePath.Trim().EndsWith("xlsx"))
    throw new Exception(" Please use the office Excel 2007 Version or 2010 Version ");

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

Method interface:

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>  Open a file  </summary> 
  bool Open(); 
  //ExcelVersion Version { get; }
  /// <summary>  File path  </summary> 
  string FilePath { get; set; }
  /// <summary>  Is the file open  </summary> 
  bool IfOpen { get; }
  /// <summary>  Number of worksheets contained in the file  </summary> 
  int SheetCount { get; }
  /// <summary>  Current Sheet Sequence Number  </summary> 
  int CurrentSheetIndex { get; set; }
  /// <summary>  Gets the number of rows in the current worksheet  </summary> 
  int GetRowCount();
  /// <summary>  Gets the number of columns in the current worksheet  </summary> 
  int GetColumnCount();
  /// <summary>  Gets a certain in the current worksheet 1 Number of cells in a row  </summary> 
  /// <param name="Row"> Line serial number </param> 
  int GetCellCountInRow(int Row);
  /// <summary>  Gets a certain in the current worksheet 1 The value of the cell (returned as a string)  </summary> 
  /// <param name="Row"> Line serial number </param> 
  /// <param name="Col"> Column serial number </param> 
  string GetCellValue(int Row, int Col);
  /// <summary>  Close a file  </summary> 
  void Close(); 
 }
}

Method implementation:

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(" Sheet Sequence Number Out of Range "); 
     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; 
  } 
 // Gets the data of the specified cell according to the row number and column number 
  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(); 
  }   
 }   
}

Method calls implement functionality:

// The program is used locally, so the path at this time is the absolute strength of the local computer; 
// This path should be the absolute path on the server after the program is released, so before that, you should have 
//1 Item function is to upload the local file to the specified location on the server. At this time, you can get the path 
 public string GetExcelToCtripPolicy(string filePath)
  {
   ExcelLib lib = new ExcelLib();
   if (filePath == null)
    return new ReturnResult<bool>(false, " The corresponding file was not found ");
   string str= tmp.GetCellValue(i, j); 
   return str;
  }

Method 2: Convert Excel table into DataTable table, and then perform business operation on 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>
  /// Will specify the Excel Convert the file of to DataTable  ( Excel The first part of 1 A sheet ) 
  /// </summary>
  /// <param name="fullFielPath"> Absolute path of file </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];// Select   Specify a page 

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

  /// <summary>
  ///  Will worksheet Turn into datatable
  /// </summary>
  /// <param name="worksheet"> To be processed worksheet</param>
  /// <returns> Returns the processed datatable</returns>
  public static DataTable WorksheetToTable(ExcelWorksheet worksheet)
  {
   // Get worksheet Number of rows of 
   int rows = worksheet.Dimension.End.Row;
   // Get worksheet Number of columns of 
   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++)
    {
     // By default, the 1 Row set to datatable Title of 
     if (i == 1)
      dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
     // Remaining writes datatable
     else
      dr[j - 1] = GetString(worksheet.Cells[i, j].Value);
    }
   }
   return dt;
  }
 }
}

Before, I had a program that used method 1 to import Excel, and the speed was not very fast. Later, I used the second method but the speed was slower. In the end, which of these two methods is fast, please guide me, or when I use the second method, there is a problem in business judgment. I don't know, please understand people to guide me in the end.