Asp. NET use open Source Component Npoi Fast Import Export EXECL data _ practical Tips

Believe that many children's shoes have been developed EXECL import and export functions, the recent products, whether it is the need for background data analysis, or front-end to meet the convenience of user management, there are execl import and export maintenance needs to produce.

Before doing this function, if the web, using HttpContext.Current.Response.ContentType = "Application/ms-excel", you can export HTML data table to EXECL, The problem with this approach is that the coding format is too bad compatibility, using office such as Mac OS to open the garbled text to you. Or you can invoke Office COM components, or macro scripts to manipulate execl, the main problem is that the client must have Office installed, and if you are installing WPS you are still playing.

The appeal method feels too out of the box after the recent use of Npoi this open source component in the product. First feel the charm of the Code:

Copy Code code as follows:

<summary>
Export EXECL
</summary>
<returns></returns>
Public Fileresult dataexporttoexecl ()
{
Create an object for an Excel file
Npoi. HSSF. Usermodel.hssfworkbook book = new Npoi. HSSF. Usermodel.hssfworkbook ();
Npoi. Ss. Usermodel.isheet Sheet1 = Book. Createsheet ("Sheet1"); Add a sheet

var _data = Cardhelper.getalldata (); Get the list data, or pagination to get the data for more efficient performance
Add the header of the first row to Sheet1
Npoi. Ss. Usermodel.irow Row1 = Sheet1. CreateRow (0);
Row1. Createcell (0). Setcellvalue ("rank");
Row1. Createcell (1). Setcellvalue ("Cardid");
Row1. Createcell (2). Setcellvalue ("name");
Row1. Createcell (3). Setcellvalue ("mobile");
Row1. Createcell (4). Setcellvalue ("position");
Row1. Createcell (5). Setcellvalue ("Company");
Row1. Createcell (6). Setcellvalue ("Creation Time");

To progressively write data to Sheet1 rows
for (int i = 0; i < _data. Count; i++)
{
Npoi. Ss. Usermodel.irow rowtemp = Sheet1. CreateRow (i + 1);
Rowtemp. Createcell (0). Setcellvalue (i+1);
Rowtemp. Createcell (1). Setcellvalue (_data[i].id);
Rowtemp. Createcell (2). Setcellvalue (_data[i]. Realname);
Rowtemp. Createcell (3). Setcellvalue (_data[i]. Cellphone);
Rowtemp. Createcell (4). Setcellvalue (String. IsNullOrEmpty (_data[i]. Wortitle)? "None": _data[i]. Wortitle);
Rowtemp. Createcell (5). Setcellvalue (String. IsNullOrEmpty (_data[i]. Company)? "None": _data[i]. Company);
Rowtemp. Createcell (6). Setcellvalue (_data[i]. Createdate.tostring ());
}
Write to Client
System.IO.MemoryStream ms = new System.IO.MemoryStream ();
Book. Write (MS);
Ms. Seek (0, seekorigin.begin);
Return File (MS, "Application/vnd.ms-excel", DateTime.Now.ToString ("yyyyMMdd") + ". xls");
}

In front of you just use hypertext link to this action on it, as for the text or the button, according to their preferences.

Copy Code code as follows:

<summary>
Import execl Information
</summary>
<returns></returns>
Private DataView Loadexeclfile ()
{
Virtual generation of a DataTable
DataTable dt = new DataTable ();
Dt. Columns.Add ("Name", typeof (String));
Dt. Columns.Add ("Phone", typeof (String));
Dt. Columns.Add ("CID", typeof (String));
Dt. Columns.Add ("Status", typeof (String));
DataRow newrow = null;

string filepath = @ "Data.xls"; Reads the local execl, the current path is the directory where the program is located
Hssfworkbook wb = new Hssfworkbook (new FileStream (filepath, FileMode.Open));
Hssfsheet sheet = wb. Getsheet ("Data") as Hssfsheet; Gets the sheet table data in the EXECL named data

if (sheet = null)
{
MessageBox.Show ("Check if file path and file name is wrong!") ");
}
Else
{
Import data
for (int i = 1; I <= sheet. Lastrownum; i++)//Get All line numbers
{
IRow row = sheet. GetRow (i); Reading current row data
if (row!= null)
{
NewRow = dt. NewRow ();
newrow["Name" = row. Getcell (0). ToString ();
newrow["Phone" = row. Getcell (1). ToString ();
newrow["CID"] = row. Getcell (2). ToString ();
newrow["Status" = row. Getcell (3). ToString ();

if (IsMobile row. Getcell (1). ToString ()))
{
Dt. Rows.Add (NewRow);
}

}
}

}

Return dt. DefaultView;
}

This execl import function is to import data into the GridView, and if you want to import into a database or other data storage media, the code is replaced by:

Copy Code code as follows:

<summary>
Import execl Information
</summary>
<returns></returns>
private void Loadexeclfile ()
{
string filepath = @ "Data.xls"; Reads the local execl, the current path is the directory where the program is located
Hssfworkbook wb = new Hssfworkbook (new FileStream (filepath, FileMode.Open));
Hssfsheet sheet = wb. Getsheet ("Data") as Hssfsheet; Gets the sheet table data in the EXECL named data

if (sheet!= null)
{
Import data
for (int i = 1; I <= sheet. Lastrownum; i++)//Get All line numbers
{
IRow row = sheet. GetRow (i); Reading current row data
if (row!= null)
{
Insertdataintodb (row. Getcell (0). ToString (), row. Getcell (1). ToString (), row. Getcell (2). ToString (), row. Getcell (3). ToString ());
}
}
}
}

After looking at the code, I believe you can feel the npoi of the execl operation of the powerful has been unable to describe the words. The import and export of appeals should be the most frequently used execl operations in common development, and Npoi has basically encapsulated all kinds of methods to the extreme, unless you have a special business scene, otherwise, as long as a little modification, you can meet their different business needs. Of course, there are some problems with the appeal plan, the solution is basically similar, time for space, or space change time. Like what:

-Export data volumes, access to data and processing of data, many encounter performance problems, serious words even memory overflow, program error. The solution is very simple, code modification, the use of pagination to obtain data, exported to multiple sheet or divided into multiple execl export, the code I am lazy to write.

Summarize the pros and cons of Npoi:

Advantages: The use of super simple, small white can also see understand. Standalone components, reference can be. No third party dependencies are required. Flexible code control, excellent compatibility. The performance of the use of very excellent, the source code is also very beautiful ...

Disadvantage: If there is, please tell me!