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

(0)

相关推荐

  • asp.net GridView导出到Excel代码

    复制代码 代码如下: StringBuilder sb = new StringBuilder(); StringWriter sw = new StringWriter(sb); HtmlTextWriter htw = new HtmlTextWriter(sw); Page page = new Page(); HtmlForm form = new HtmlForm(); GVLinkman.EnableViewState = false; page.EnableEventValidat

  • asp.net导出excel数据的常见方法汇总

    本文实例讲述了asp.net中一些常用的excel数据导出方法,同时也介绍了在数据导入或导出时可能碰到的一些问题总结,分享给大家供大家参考.希望文章对你会有所帮助.具体实现方法如下: 1.由dataset生成 复制代码 代码如下: public void CreateExcel(DataSet ds,string typeid,string FileName)    {    HttpResponse resp;    resp = Page.Response;    resp.ContentE

  • ASP.NET使用GridView导出Excel实现方法

    本文实例讲述了ASP.NET使用GridView导出Excel实现方法.分享给大家供大家参考.具体实现方法如下: 复制代码 代码如下: /// <summary>  /// 将DataTable数据导出到EXCEL,调用该方法后自动返回可下载的文件流  /// </summary>  /// <param name="dtData">要导出的数据源</param>  public static void DataTable1Excel(S

  • ASP.NET用DataSet导出到Excel的方法

    复制代码 代码如下: /读取临时文件 GYYW.DA.Common.Base_SqlDataBase daBZDM = new GYYW.DA.Common.Base_SqlDataBase(); DataSet dsBZDM = daBZDM.GetDataSetBySql("select QCDM,MC,GG from WG_BZDM where QCDM like '02%'"); //同时将虚拟目录下的Data作为临时文件目录. string urlPath = HttpCon

  • asp.net生成Excel并导出下载五种实现方法

    方法一 通过GridView(简评:方法比较简单,但是只适合生成格式简单的Excel,且无法保留VBA代码),页面无刷新 aspx.cs部分 复制代码 代码如下: using System; using System.Collections; using System.Configuration; using System.Data; using System.Web; using System.Web.Security; using System.Web.UI; using System.We

  • asp.net Grid 导出Excel实现程序代码

    1. Aspx后台代码输出Content Type信息 复制代码 代码如下: Response.ClearContent(); Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls"); Response.ContentType = "application/excel"; Response.Write(GetGridTableHtml(Gr

  • ASP.NET 导出到Excel时保留换行的代码

    <br style='mso-data-placement:same-cell;'/> 完整代码: 复制代码 代码如下: <%@ Page Language="C#" Trace="false" AutoEventWireup="true" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://

  • ASP.NET导出数据到Excel的实现方法

    网上好些代码的原理大致与此类似,同样都存在一个问题,就是: 类型"GridView"的控件"ctl00_center_GridView1"必须放在具有 runat=server 的窗体标记内. 说明: 执行当前 Web 请求期间,出现未处理的异常.请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息. 异常详细信息:System.Web.HttpException: 类型"GridView"的控件"ctl00_cent

  • Asp.net中DataTable导出到Excel的方法介绍

    复制代码 代码如下: #region  DataTable导出到Excel        /// <summary>        /// DataTable导出到Excel        /// </summary>        /// <param name="pData">DataTable</param>        /// <param name="pFileName">导出文件名</p

  • .Net中导出数据到Excel(asp.net和winform程序中)

    一.asp.net中导出Excel的方法: 在asp.net中导出Excel有两种方法,一种是将导出的文件存放在服务器某个文件夹下面,然后将文件地址输出在浏览器上:一种是将文件直接将文件输出流写给浏览器.在Response输出时,t分隔的数据,导出Excel时,等价于分列,n等价于换行. 1.将整个html全部输出Excel 此法将html中所有的内容,如按钮,表格,图片等全部输出到Excel中. 复制代码 代码如下: Response.Clear(); Response.Buffer= tru

  • asp.net实现Gradview绑定数据库数据并导出Excel的方法

    本文实例讲述了asp.net实现Gradview绑定数据库数据并导出Excel的方法.分享给大家供大家参考,具体如下: protected void showData_Click(object sender, EventArgs e) { SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=test;User ID=sa;password=sa"); SqlDataA

随机推荐