asp.net to export Excel Simple method Examples _ Practical Tips

Excel's operation, the most commonly used is the export and import, nonsense not much to say on the code.

This example uses Npoi to realize, do not like to spray ha ....

Copy Code code as follows:

<summary>
Export Excel
</summary>
<param name= "Stime" ></param>
<param name= "ETime" ></param>
<returns></returns>
Public ActionResult Export (formcollection frm)
{
DataTable DTS = new DataTable ();
DTS = _shopmemeber.exportmemberdata (frm);
Iworkbook workbook = new Xssfworkbook ();
Isheet sheet = workbook. Createsheet ();
IRow HeaderRow = sheet. CreateRow (0);
foreach (DataColumn column in DTS). Columns)
Headerrow.createcell (column. Ordinal). Setcellvalue (column. Caption);
int rowIndex = 1;
foreach (DataRow row in DTS. Rows)
{
IRow DataRow = sheet. CreateRow (RowIndex);
foreach (DataColumn column in DTS). Columns)
{
Datarow.createcell (column. Ordinal). Setcellvalue (Row[column]. ToString ());
}
rowindex++;
}
string filepath = Server.MapPath ("/") + @ "user list. xlsx";
FileStream file = new FileStream (filepath, filemode.create);
Workbook. Write (file);
Excelhelper.download (@ "/user list. xlsx");
#region not enabled

#endregion
Return successmsg ("Adminmembermemberindex");
}
This is the way to download to the desktop, do not implement the optional path
public static void DownLoad (String FileName)
{
FileInfo FileInfo = new FileInfo (HttpContext.Current.Server.MapPath (FileName));
Downloading files as streams of characters
FileStream fs = new FileStream (HttpContext.Current.Server.MapPath (FileName), FileMode.Open);
byte[] bytes = new byte[(int) fs. Length];
Fs. Read (bytes, 0, bytes. Length);
Fs. Close ();
HttpContext.Current.Response.ContentType = "Application/octet-stream";
Notifies the browser to download the file instead of opening it
HttpContext.Current.Response.AddHeader ("Content-disposition", "attachment; Filename= "+ httputility.urlencode (Fileinfo.name, System.Text.Encoding.UTF8));
HttpContext.Current.Response.BinaryWrite (bytes);
HttpContext.Current.Response.Flush ();
HttpContext.Current.Response.End ();
}

Above is the export, below I introduce the next import.

Copy Code code as follows:

<summary>
Import data
</summary>
<param name= "File" ></param>
<returns>true indicates successful import </returns>
public bool Impoart (httppostedfilebase file)
{
Try
{
Save Excel
String path = HttpContext.Current.Server.MapPath ("/");
File. SaveAs (path + file). FileName);

Read

FileStream SW = File.Open (path + File. FileName, FileMode.Open, FileAccess.Read);
Iworkbook workbook = new Xssfworkbook (SW);
Isheet Sheet1 = workbook. Getsheet ("Sheet1");

Maximum number of rows
int rowscount = Sheet1. Physicalnumberofrows;

Determines whether the first row conforms to the specification, which is the column name in Excel
IRow FirstRow = Sheet1. GetRow (0);
if (
! (Firstrow.getcell (0). ToString () = = "Name" && Firstrow.getcell (1). ToString () = = "Short name" &&
Firstrow.getcell (2). ToString () = = "Classification" && Firstrow.getcell (3). ToString () = "Reference price" &&
Firstrow.getcell (4). ToString () = = "Product Introduction")
{
return false;
}

Skipping items of incorrect type
for (int i = 1; i < Rowscount; i++)
{
IRow row = Sheet1. GetRow (i);
Shop_product Product = new Shop_product ();

String category = row. Getcell (2)!= null? Row. Getcell (2). ToString (): null;
if (!string. IsNullOrEmpty (category))
{
var cate =
_unitofwork.shop_productcategoryrepository (). GetAll (). FirstOrDefault (t => t.name = = category);
if (Cate!= null)
{
Product. Productcategoryname = Cate. Name;
Product. shop_productcategory_id = cate.id;
}
Else
{
Continue
}
}
Else
{
Continue
}

Product. PName = row. Getcell (0)!= null? Row. Getcell (0). ToString (): null;
Product. Pcname = row. Getcell (1)!= null? Row. Getcell (1). ToString (): null;
if (row. Getcell (3)!= null)
{
Product. Price = Double.Parse (row. Getcell (3). ToString ());
}
Product. Description = row. Getcell (4)!= null? Row. Getcell (4). ToString (): null;

_unitofwork.shop_productrepository (). Insert (product);
}

_unitofwork.save ();
}
Catch
{
return false;
}

return true;
}