Asp.net simple excel export method example

Excel operations are most commonly used for export and import.

This example uses NPOI ....

Copy codeThe Code is 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 ("/") + @ ".xlsx ";
FileStream file = new FileStream (filepath, FileMode. Create );
Workbook. Write (file );
ExcelHelper. DownLoad (@ "/ .xlsx ");
# Region disabled

# Endregion
Return SuccessMsg ("AdminMemberMemberIndex ");
}
// This is the method for downloading to the desktop, and does not implement the Optional path
Public static void DownLoad (string FileName)
{
FileInfo fileInfo = new FileInfo (HttpContext. Current. Server. MapPath (FileName ));
// Download an object in the form of a streaming
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 ";
// Notify 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 ();
}

The above is the export. Next I will introduce the import.

Copy codeThe Code is as follows:
/// <Summary>
/// Import data
/// </Summary>
/// <Param name = "file"> </param>
/// <Returns> true indicates that the import is successful </returns>
Public bool Impoart (HttpPostedFileBase file)
{
Try
{
// Save the excel file
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;

// Determine whether the first line conforms to the specifications, that is, the column name in Excel.
IRow firstRow = sheet1.GetRow (0 );
If (
! (FirstRow. GetCell (0). ToString () = "name" & firstRow. GetCell (1). ToString () = "abbreviation "&&
FirstRow. GetCell (2). ToString () = "category" & firstRow. GetCell (3). ToString () = "reference price "&&
FirstRow. GetCell (4). ToString () = "Product Introduction "))
{
Return false;
}

// Skip items with 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;
}