导入导出EXL

/// <summary>
    /// 导出公用方法
    /// </summary>
    public class ImportBase
    {
        /// <summary>
        ///  NOPI控件实现导入数据
        /// </summary>
        /// <param name="FilesName">file上传文件ID</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportExcel(HttpPostedFileBase files1)
        {

            //上传和返回(保存到数据库中)的路径
            string uppath = string.Empty;
            string savepath = string.Empty;


            string nameImg = Guid.NewGuid().ToString();

            //记录excel中的所有图片的数据库路径
            List<string> list_Url = new List<string>();


            #region 上传临时文件部分
            //导入文档格式校验
            if (files1.ContentType != "application/vnd.ms-excel")
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("error");
                dt.Rows.Add("error");
                return dt;
            }

            string fileName = files1.FileName;

            //获得上传图片的类型(后缀名)
            string type = fileName.Substring(fileName.LastIndexOf(".") + 1).ToLower();

            HttpPostedFileBase files = files1;
            Stream filedata = files.InputStream;//上传文件的流

            #endregion
            IWorkbook workbook = WorkbookFactory.Create(filedata);
            //HSSFWorkbook hssfWorkBook = new HSSFWorkbook(file);
            IList pictures = workbook.GetAllPictures();
            ISheet sheet = workbook.GetSheetAt(0); //取第一个表
            DataTable table = new DataTable();

            IRow headerRow = sheet.GetRow(0);//第一行为标题行
            int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
            int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

            //handling header.
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                ICell hs = headerRow.GetCell(i);
                //ColumnDataType[i] = GetCellDataType(hs);
                //CellType dsfa = hs.CellType;
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                if (row != null)
                {
                    for (int j = 0; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = row.GetCell(j);
                        }
                        //else
                        //{
                        //    dataRow[j] = list_Url[i - 1];
                        //}
                    }
                }

                table.Rows.Add(dataRow);
            }

            return table;
        }
        /// <summary>
        /// 将Excel导入到DataSet
        /// 返回DataSet
        /// </summary>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string fileupexcel,HttpPostedFileBase file)
        {
            string strType = System.IO.Path.GetExtension(fileupexcel);
            DataSet ds = new DataSet();
            string filepath = HttpContext.Current.Server.MapPath(@"~/UpExcel/") + Guid.NewGuid().ToString().Trim()+strType;//
            string path= HttpContext.Current.Server.MapPath("~/UpExcel/");
     
            if (File.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            file.SaveAs(filepath);
            string strCon = string.Empty;
            if (strType == ".xlsx")
            {
                strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;IMEX=1'";
            }
            else if (strType == ".xls")
            {
                strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'";
            }
            OleDbConnection conn = new OleDbConnection(strCon);
            conn.Open();
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string tblname = "[" + schemaTable.Rows[0][2].ToString().Trim() + "]";
            try
            {
                string sql = "select * from " + tblname;
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                adapter.Fill(ds, tblname);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                
                conn.Close();
                File.Delete(filepath);
            }

            return ds;
        }

    }


  #region 导出
        /// <summary>
        /// 导出
        /// </summary>
        /// <returns></returns>
        public ActionResult ExportExcel()
        {
            GetPubParameter();
            IList<IT_TechnicalInformation> list = new List<IT_TechnicalInformation>();
            if (Request.QueryString["ids"] != null)
            {
                string ids = HttpUtility.UrlDecode(Request.QueryString["ids"]);
                string[] idCollections = ids.TrimEnd(',').Split(new char[] { ',' });
                if (idCollections != null)
                {
                    for (int i = 0; i < idCollections.Length; i++)
                    {
                        IT_TechnicalInformation info = new IT_TechnicalInformation();
                        if (!string.IsNullOrEmpty(idCollections[i]))
                        {
                            info = technicalbll.GetModel(idCollections[i]);
                            list.Add(info);
                        }
                    }
                    NPOIExcel(list);
                }
            }

            return null;
        }
        #endregion

        #region 使用NPOI控件实现导出
        private void NPOIExcel(IList<IT_TechnicalInformation> list)
        {
            string path = ConfigurationManager.AppSettings["fileProxy"];
            FileInfo TheFile = new FileInfo(Server.MapPath(path));
            if (!TheFile.Directory.Exists)
            {
                TheFile.Directory.Create();
            }
            MemoryStream ms = new MemoryStream();
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
            NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
            NPOI.SS.UserModel.ICell cel2 = headerRow.CreateCell(0);
            headerRow.CreateCell(0).SetCellValue("主题");
            headerRow.CreateCell(1).SetCellValue("产品");
            headerRow.CreateCell(2).SetCellValue("技术");
            headerRow.CreateCell(3).SetCellValue("创建人");
            headerRow.CreateCell(4).SetCellValue("创建时间");

            NPOI.HSSF.UserModel.HSSFCellStyle cs2 = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
            NPOI.HSSF.UserModel.HSSFFont font2 = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
            font2.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            cs2.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
            cs2.FillForegroundColor = 40;
            cs2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top;
            cs2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cs2.SetFont(font2);
            cs2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cs2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cs2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cs2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            for (int i = 0; i <= 4; i++)
            {
                sheet.SetColumnWidth(i, 100 * 40);
                headerRow.GetCell(i).CellStyle = cs2;
            }
            headerRow.HeightInPoints = 30;

            int rowIndex = 1;
            NPOI.HSSF.UserModel.HSSFPatriarch patriarch = (NPOI.HSSF.UserModel.HSSFPatriarch)sheet.CreateDrawingPatriarch();
            if (list != null && list.Count > 0)
            {
                NPOI.HSSF.UserModel.HSSFCellStyle cs3 = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
                NPOI.HSSF.UserModel.HSSFFont font3 = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
                cs3.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top;
                cs3.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                for (int i = 0; i < list.Count; i++)
                {
                    IT_TechnicalInformation info = technicalbll.GetModel(list[i].TechnicalInformation_ID);
                    NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(info.TechnicalName);
                    dataRow.CreateCell(1).SetCellValue(info.ProductName);
                    dataRow.CreateCell(2).SetCellValue(info.ResearcherName);
                    dataRow.CreateCell(3).SetCellValue(info.CreateBy);
                    dataRow.CreateCell(4).SetCellValue(info.CreateTime.Value.ToString("yyyy-MM-dd"));

                    for (int j = 0; j <= 4; j++)
                    {
                        dataRow.GetCell(j).CellStyle = cs3;
                    }
                    dataRow.HeightInPoints = 30;
                    rowIndex++;
                }

                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                sheet = null;
                headerRow = null;
                workbook = null;

                string excelname = System.DateTime.Now.ToString().Replace(":", "").Replace("-", "").Replace(" ", "");
                string filePath = Server.MapPath(path + "ReadExcel") + "" + Guid.NewGuid().ToString() + "导出.xls";
                FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
                fs.Close();
                data = null;
                ms = null;
                fs = null;
                #region 导出到客户端
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                Response.AppendHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("导出", System.Text.Encoding.UTF8) + ".xls");
                Response.ContentType = "Application/excel";
                Response.WriteFile(filePath);
                Response.End();
                FileInfo f = new FileInfo(filePath);
                f.Delete();
                #endregion
                Response.Write("<script>history.go(-1);</script>");
            }
            else
            {
                Response.Write("<script>alert('没有可导出的数据！');history.go(-1);</script>");
            }
        }
        #region 导出
        /// <summary>
        /// 导出
        /// </summary>
        /// <returns></returns>
        public ActionResult ExportExcels()
        {
            GetPubParameter();
            IList<IT_InformationPatent> list = new List<IT_InformationPatent>();
            if (Request.QueryString["ids"] != null)
            {
                string ids = HttpUtility.UrlDecode(Request.QueryString["ids"]);
                string[] idCollections = ids.TrimEnd(',').Split(new char[] { ',' });
                if (idCollections != null)
                {
                    for (int i = 0; i < idCollections.Length; i++)
                    {
                        IT_InformationPatent info = new IT_InformationPatent();
                        if (!string.IsNullOrEmpty(idCollections[i]))
                        {
                            info = infoPatbll.GetModel(idCollections[i]);
                            list.Add(info);
                        }
                    }
                    NPOIExcels(list);
                }
            }

            return null;
        }
        #endregion
        private void NPOIExcels(IList<IT_InformationPatent> list)
        {
            string path = ConfigurationManager.AppSettings["fileProxy"];
            FileInfo TheFile = new FileInfo(Server.MapPath(path));
            if (!TheFile.Directory.Exists)
            {
                TheFile.Directory.Create();
            }
            MemoryStream ms = new MemoryStream();
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
            NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
            NPOI.SS.UserModel.ICell cel2 = headerRow.CreateCell(0);
            headerRow.CreateCell(0).SetCellValue("名称");
            headerRow.CreateCell(1).SetCellValue("申请号");
            headerRow.CreateCell(2).SetCellValue("发明人");
            headerRow.CreateCell(3).SetCellValue("类型");
            headerRow.CreateCell(4).SetCellValue("产品");
            headerRow.CreateCell(5).SetCellValue("技术");
            headerRow.CreateCell(6).SetCellValue("申请日");
            headerRow.CreateCell(7).SetCellValue("摘要");
            headerRow.CreateCell(8).SetCellValue("状态");
            headerRow.CreateCell(9).SetCellValue("日");
            headerRow.CreateCell(10).SetCellValue("号");
            headerRow.CreateCell(11).SetCellValue("申请人");
            headerRow.CreateCell(12).SetCellValue("机构");
            headerRow.CreateCell(13).SetCellValue("等级");
            headerRow.CreateCell(14).SetCellValue("备注");


            NPOI.HSSF.UserModel.HSSFCellStyle cs2 = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
            NPOI.HSSF.UserModel.HSSFFont font2 = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
            font2.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            cs2.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
            cs2.FillForegroundColor = 40;
            cs2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top;
            cs2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cs2.SetFont(font2);
            cs2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cs2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cs2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cs2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            for (int i = 0; i <= 14; i++)
            {
                sheet.SetColumnWidth(i, 100 * 40);
                headerRow.GetCell(i).CellStyle = cs2;
            }
            headerRow.HeightInPoints = 30;

            int rowIndex = 1;
            NPOI.HSSF.UserModel.HSSFPatriarch patriarch = (NPOI.HSSF.UserModel.HSSFPatriarch)sheet.CreateDrawingPatriarch();
            if (list != null && list.Count > 0)
            {
                NPOI.HSSF.UserModel.HSSFCellStyle cs3 = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
                NPOI.HSSF.UserModel.HSSFFont font3 = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
                cs3.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top;
                cs3.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                for (int i = 0; i < list.Count; i++)
                {
                    NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(list[i].PatentName);
                    dataRow.CreateCell(1).SetCellValue(list[i].AppPatentNO);
                    dataRow.CreateCell(2).SetCellValue(list[i].Inventor);
                    dataRow.CreateCell(3).SetCellValue(list[i].PatentType);
                    dataRow.CreateCell(4).SetCellValue(list[i].ProductName);
                    dataRow.CreateCell(5).SetCellValue(list[i].ResearcherName);
                    dataRow.CreateCell(6).SetCellValue(list[i].ApplicationDate.Value.ToString("yyyy-MM-dd"));
                    dataRow.CreateCell(7).SetCellValue(list[i].Summary);
                    dataRow.CreateCell(8).SetCellValue(list[i].LawState);
                    dataRow.CreateCell(9).SetCellValue(list[i].DulletinData.Value.ToString("yyyy-MM-dd"));
                    dataRow.CreateCell(10).SetCellValue(list[i].DulletinNO);
                    dataRow.CreateCell(11).SetCellValue(list[i].AppPatentUser);
                    dataRow.CreateCell(12).SetCellValue(list[i].Agency);
                    dataRow.CreateCell(13).SetCellValue(list[i].Level);
                    dataRow.CreateCell(14).SetCellValue(list[i].Remark);
                    for (int j = 0; j <= 14; j++)
                    {
                        dataRow.GetCell(j).CellStyle = cs3;
                    }
                    dataRow.HeightInPoints = 30;
                    rowIndex++;
                }

                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                sheet = null;
                headerRow = null;
                workbook = null;

                string excelname = System.DateTime.Now.ToString().Replace(":", "").Replace("-", "").Replace(" ", "");
                string filePath = Server.MapPath(path + "ReadExcel") + "" + Guid.NewGuid().ToString() + "导出.xls";
                FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
                fs.Close();
                data = null;
                ms = null;
                fs = null;
                #region 导出到客户端
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                Response.AppendHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("导出", System.Text.Encoding.UTF8) + ".xls");
                Response.ContentType = "Application/excel";
                Response.WriteFile(filePath);
                Response.End();
                FileInfo f = new FileInfo(filePath);
                f.Delete();
                #endregion
                Response.Write("<script>history.go(-1);</script>");
            }
            else
            {
                Response.Write("<script>alert('没有可导出的数据！');history.go(-1);</script>");
            }
        }
        #endregion
        /// <summary>
        /// 导入方法
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public ActionResult Import()
        {
            HttpPostedFileBase files1 = Request.Files["localImport"];
            DataTable dt = ImportBase.ExcelToDataSet(files1.FileName, files1).Tables[0];
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0][0].ToString().Trim() == "error")
                {
                    Response.Write("<script> window.parent.uploadSuccess('导入文件错误，请重新上传导入文件！');</script>");
                }
                else
                {
                    try
                    {
                        if (dt.Rows.Count > 0)
                        {
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                IT_InformationPatent info = new IT_InformationPatent();
                                info.InformationPatent_ID = Guid.NewGuid().ToString();
                                info.TechnicalID = List;
                                info.AppPatentNO = dt.Rows[i]["申请号"].ToString().Trim();
                                if (string.IsNullOrEmpty(dt.Rows[i]["申请日"].ToString().Trim()))
                                {
                                    info.ApplicationDate = null;
                                }
                                else
                                {
                                    info.ApplicationDate = Convert.ToDateTime(dt.Rows[i]["申请日"].ToString().Trim());
                                }
                                info.PatentName = dt.Rows[i]["名称"].ToString().Trim();
                                info.Summary = dt.Rows[i]["摘要"].ToString().Trim();
                                info.PatentType = dt.Rows[i]["类型"].ToString().Trim();
                                info.LawState = dt.Rows[i]["状态"].ToString().Trim();
                               
                                if (string.IsNullOrEmpty(dt.Rows[i]["日"].ToString().Trim()))
                                {
                                    info.DulletinData = null;
                                }
                                else
                                {
                                    info.DulletinData = Convert.ToDateTime(dt.Rows[i]["授权公告日"].ToString().Trim());
                                }
                                info.DulletinNO = dt.Rows[i]["号"].ToString().Trim();
                                info.AppPatentUser = dt.Rows[i]["申请人"].ToString().Trim();
                                info.Inventor = dt.Rows[i]["发明人"].ToString().Trim();
                                info.Agency = dt.Rows[i]["机构"].ToString().Trim();
                                info.Researchers = dt.Rows[i]["技术"].ToString().Trim();
                                info.Products = dt.Rows[i]["产品"].ToString().Trim();
                                info.Level = dt.Rows[i]["等级"].ToString().Trim();
                                info.Remark = dt.Rows[i]["备注"].ToString().Trim();
                                info.CreateBy = dt.Rows[i]["创建人"].ToString().Trim();
                                if (string.IsNullOrEmpty(dt.Rows[i]["创建时间"].ToString().Trim()))
                                {
                                    info.CreateTime = null;
                                }
                                else
                                {
                                    info.CreateTime = Convert.ToDateTime(dt.Rows[i]["创建时间"].ToString().Trim());
                                }
                                info.UpdateBy = dt.Rows[i]["更新人"].ToString().Trim();
                                if (string.IsNullOrEmpty(dt.Rows[i]["更新时间"].ToString().Trim()))
                                {
                                    info.UpdateTime = null;
                                }
                                else
                                {
                                    info.UpdateTime = Convert.ToDateTime(dt.Rows[i]["更新时间"].ToString().Trim());
                                }
                                informationbll.Add(info);
                                Response.Write("<script> top.uploadSuccess('导入数据成功！');window.parent.location.reload();</script>");
                            }
                        }
                    }
                    catch (Exception)
                    {
                        Response.Write("<script> window.parent.uploadSuccess('导入文件错误，请重新上传导入文件！');</script>");
                    }
                }
            }
            else
            {

                Response.Write("<script> window.parent.uploadSuccess('导入文件没有数据，请重新上传导入文件！');</script>");
            }
            return null;
        }