asp.net导出excel的简单方法实例

excel的操作，最常用的就是导出和导入，废话不多说上代码。

本例使用NPOI实现的，不喜勿喷哈。。。。

/// <summary>
 /// 导出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");
 }
//这个是下载到桌面的方法，没实现自选路径
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();
 }

上面是导出，下面我介绍下导入。

/// <summary>
 /// 导入数据
 /// </summary>
 /// <param name="file"></param>
 /// <returns>true表示导入成功</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");
 //最大行数
 int rowsCount = sheet1.PhysicalNumberOfRows;
 //判断首行是否符合规范 也就是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;
 }

到此这篇关于asp.net导出excel的简单方法实例就介绍到这了。调节好兴奋期，学习一浪高一浪。更多相关asp.net导出excel的简单方法实例内容请查看相关栏目，小编编辑不易，再次感谢大家的支持！