欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-26 21:02:28
excel的操作,最常用的就是导出和导入,废话不多说上代码。 本例使用npoi实现的,不喜勿喷哈。。。。 复制代码 代码如下:/// &n...

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