老余博客上线了!!!

C#利用NOPI导入导出教程

后端 老余 87℃ 0评论

之前写了个导入导出的功能,程序使用的是Microsoft.Office.Interop.Excel类来操作Excel。在本机测试的时候都好好的,但是将生成文件放到其他电脑上却怎样也导出,报出下面错误“检索 COM 类工厂中 CLSID 为 {000209FF-0000-0000-...

之前写了个导入导出的功能,程序使用的是Microsoft.Office.Interop.Excel类来操作Excel。在本机测试的时候都好好的,但是将生成文件放到其他电脑上却怎样也导出,报出下面错误“检索 COM 类工厂中 CLSID 为 {000209FF-0000-0000-C000-000000000046} 的组件时失败,原因是出现以下错误: 80070005。”按网上很多方法试过都没成功(重装Office除外)。

1.利用NOPI导入Excel

#region  导入数据
        [HttpPost]
        public ActionResult StationImport(HttpPostedFileBase filebase)
        {
            HttpPostedFileBase file = Request.Files["files"];
            string FileName;
            string savePath;
            if (file == null || file.ContentLength <= 0)
            {
                ViewBag.error = "文件不能为空";
                return RedirectToAction("Index");
            }
            else
            {
                string filename = Path.GetFileName(file.FileName);
                int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
                string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
                string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
                int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
                string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
                FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
                if (!FileType.Contains(fileEx))
                {
                    ViewBag.error = "文件类型不对,只能导入xls和xlsx格式的文件";
                    return RedirectToAction("Index");
                }
                if (filesize >= Maxsize)
                {
                    ViewBag.error = "上传文件超过4M,不能上传";
                    return RedirectToAction("Index");
                }
                string path = AppDomain.CurrentDomain.BaseDirectory + "upload/excel/";
                savePath = Path.Combine(path, FileName);
                file.SaveAs(savePath);
                var filepath = path + FileName;
                DataTable dt = new DataTable();
                using (FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read))
                {
                    HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
                    ISheet sheet = hssfworkbook.GetSheetAt(0);
                    //表头
                    IRow header = sheet.GetRow(sheet.FirstRowNum);
                    List<int> columns = new List<int>();
                    for (int i = 0; i < header.LastCellNum; i++)
                    {
                        object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
                        if (obj == null || obj.ToString() == string.Empty)
                        {
                            dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                            //continue;
                        }
                        else
                            dt.Columns.Add(new DataColumn(obj.ToString()));
                        columns.Add(i);
                    }
                    //数据
                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                    {
                        DataRow dr = dt.NewRow();
                        bool hasValue = false;
                        foreach (int j in columns)
                        {
                            dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
                            if (dr[j] != null && dr[j].ToString() != string.Empty)
                            {
                                hasValue = true;
                            }
                        }
                        if (hasValue)
                        {
                            dt.Rows.Add(dr);
                            //插入到询盘资源库
                            using (var db = new LinqDataMainDataContext())
                            {
                                db.CMS_CustomerEnquiries.InsertOnSubmit(new CMS_CustomerEnquiries()
                                {
                                    Name = dr["姓名"].ToString(),
                                    Email = dr["邮箱"].ToString(),
                                    Phone = dr["联系方式"].ToString(),
                                    WhatsApp = dr["WhatsApp"].ToString(),
                                    Company = dr["公司"].ToString(),
                                    Address = dr["地址"].ToString(),
                                    Gender = dr["性别"].ToString(),
                                    Industry =Convert.ToInt32(dr["行业"].ToString()),
                                    Message = dr["留言"].ToString(),
                                    IsDel = 0,
                                    UserId = cmsadmin.UserId,
                                    CreateTime = DateTime.Now
                                });
                                db.SubmitChanges();                                
                            }
                        }
                    }
                }
            }                   
            return RedirectToAction("Index");
        }
        #endregion

2.利用NOPI导出Excel

public FileResult ExportStu2()
        {
            
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
            //获取list数据
            //IEnumerable<MyMVC_Model.CMS_EnquiriesTask> listRainInfo = CMS_EnquiriesTask_dal.SelectExport();
            List<MyMVC_Model.CMS_EnquiriesTask> listRainInfo =CMS_EnquiriesTask_dal.SelectExport().ToList();
            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("资料库ID");
            row1.CreateCell(1).SetCellValue("定时发送时间");
            row1.CreateCell(2).SetCellValue("接受询盘站点");
            row1.CreateCell(3).SetCellValue("状态");
            row1.CreateCell(4).SetCellValue("创建者");
            row1.CreateCell(5).SetCellValue("创建时间");
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < listRainInfo.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].CustomerEnquiriesId.ToString());
                rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].PlannedTime.ToString());
                rowtemp.CreateCell(2).SetCellValue(listRainInfo[i].ReceiveWebId.ToString());
                rowtemp.CreateCell(3).SetCellValue(listRainInfo[i].Status.ToString());
                rowtemp.CreateCell(4).SetCellValue(listRainInfo[i].UserId.ToString());
                rowtemp.CreateCell(5).SetCellValue(listRainInfo[i].CreateTime.ToString());
            }
            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", "定时任务导出.xls");
        }
    }

转载请注明:老余博客 » C#利用NOPI导入导出教程

读后有收获可以请作者喝咖啡:

喜欢 (1)or分享 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址