C # Implementation of two ways to import and export Excel data

This article mainly for you to introduce in detail the C # import and export Excel data two methods, with a certain reference value, interested in small partners can refer to

This article for you to share the C # import and export Excel data specific code for your reference, the specific content as follows

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

method One: This method is used to read the FileInfo stream in the Epplus (is not the flow I really do not know, if you have to know, please leave a message, 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 this is what Epplus does with Excel, you can only manipulate later versions of//2007 (that is, the extension is. xlsx)   if (!filepath.trim (). EndsWith ("xlsx"))    throw new Exception ("Please use Office Excel 2007 or version 2010");   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 File </summary   > bool Open ();  Excelversion Version {get;}  <summary> file path </summary> string FilePath {get; set;}  <summary> whether the file is already open </summary> bool Ifopen {get;}  <summary> file contains the number of worksheets </summary> int sheetcount {get;}  <summary> Current worksheet ordinal </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> get the number of cells in a row in the current worksheet </summary>//<param name= "Row" > Row ordinal </param> int Getcellcount  InRow (int Row); <summary> get the value of a cell in the current worksheet (returned as a string) </summary>//<param name= "Row" > Line number </param>////Lt;param name= "col" > Column ordinal </param> string getcellvalue (int Row, int Col);  <summary> close 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 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 for 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 call Implementation functionality:

For the program is local, so the path at this time is the absolute strength of the local computer;//The path should be the absolute path on the server after the program is published, so there is a function to upload the local file to the specified location on the server at this time, and then get the path to public string Getexceltoctrippolicy (String filePath)  {   Excellib lib = new Excellib ();   if (FilePath = = null)    return new returnresult<bool> (false, "no corresponding file found");   String str= tmp. Getcellvalue (i, j);    return str;  }

method Two: convert an Excel table into a DataTable table and then perform business operations on the 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> convert the specified Excel file to a DataTable (first sheet of Excel)///</summary>//<param name= "Fullfie Lpath "> Absolute path to 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 the specified page return worksheettotable (worksheet);   } catch (Exception) {throw; }}///<summary>///worksheet into a DataTable///</summary>//<param name= "Worksheet" > Pending worksheet</param> <returns> returns the processed datatable</returns> public static DataTable worksheettotable (excelworksheet worksheet ) {//Gets worksheet the number of rows int rows = worksheet.   Dimension.End.Row; Gets the number of columns of 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 + +) {//default sets the first row to the title of the datatable if (i = = 1) dt. Columns.Add (GetString (worksheet. Cells[i, J].     Value)); The remainder is written to the datatable else dr[j-1] = GetString (worksheet. Cells[i, J].    Value);  }} return DT; } }}

Before I have a program with the method of an Excel import, the speed is not very fast, and then I used the second method but slower, in the end the two methods which kind of fast, please guide, or I use the second method when the business judgment has a problem, unknown, Please be sensible to guide me in the end of the two methods which is better.