這篇文章主要介紹了asp.net導(dǎo)出excel的簡單方法實例,需要的朋友可以參考下

excel的操作，最常用的就是導(dǎo)出和導(dǎo)入，廢話不多說上代碼。

本例使用NPOI實現(xiàn)的，不喜勿噴哈。。。。

代碼如下:

/// <summary>

/// 導(dǎo)出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 不啟用

#endregion

return SuccessMsg("AdminMemberMemberIndex");

}

//這個是下載到桌面的方法，沒實現(xiàn)自選路徑

public static void DownLoad(string FileName)

{

FileInfo fileInfo = new FileInfo(HttpContext.Current.Server.MapPath(FileName));

//以字符流的形式下載文件

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";

//通知瀏覽器下載文件而不是打開

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();

}

上面是導(dǎo)出，下面我介紹下導(dǎo)入。

代碼如下:

/// <summary>

/// 導(dǎo)入數(shù)據(jù)

/// </summary>

/// <param name="file"></param>

/// <returns>true表示導(dǎo)入成功</returns>

public bool Impoart(HttpPostedFileBase file)

{

try

{

//保存excel

string path = HttpContext.Current.Server.MapPath("/");

file.SaveAs(path + file.FileName);

//讀取

FileStream sw = File.Open(path + file.FileName, FileMode.Open, FileAccess.Read);

IWorkbook workbook = new XSSFWorkbook(sw);

ISheet sheet1 = workbook.GetSheet("Sheet1");

//最大行數(shù)

int rowsCount = sheet1.PhysicalNumberOfRows;

//判斷首行是否符合規(guī)范 也就是Excel中的列名

IRow firstRow = sheet1.GetRow(0);

if (

!(firstRow.GetCell(0).ToString() == "名稱" && firstRow.GetCell(1).ToString() == "簡稱" &&

firstRow.GetCell(2).ToString() == "分類" && firstRow.GetCell(3).ToString() == "參考價" &&

firstRow.GetCell(4).ToString() == "商品介紹"))

{

return false;

}

//跳過類型不正確的品項

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;

}

更多信息請查看IT技術(shù)專欄