C# Excel导入导出

private List<StaffExcelModel> InformationExcel(string filePath, string storePath, out string message)
        {
            var excelHelper = new ExcelHelper<StaffExcelModel>();
            var titleList = new List<string>();
            titleList.Add("姓名 必填*");
            titleList.Add("身份证号 必填*");
            titleList.Add("手机号 必填*");
            titleList.Add("省/直辖市 必填*");
            titleList.Add("市/市辖区 必填*");
            titleList.Add("县/区 必填*");
            titleList.Add("单位 必填*");
            titleList.Add("是否为管理员 必填*");

            var contentItem = new Hashtable();
            contentItem.Add("Name", "姓名 必填*");
            contentItem.Add("ID_Card", "身份证号 必填*");
            contentItem.Add("Phone", "手机号 必填*");
            contentItem.Add("Pro", "省/直辖市 必填*");
            contentItem.Add("City", "市/市辖区 必填*");
            contentItem.Add("Area", "县/区 必填*");
            contentItem.Add("CompanyName", "单位 必填*");
            contentItem.Add("IsAdminStr", "是否为管理员 必填*");

            var renewalItemViewModels = excelHelper.InformationExcel(filePath, storePath, titleList, contentItem, out message);
            return renewalItemViewModels;
        }

 #region 个人信息导入
        readonly int _startIndex = 1;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="workbookFile">Excel所在路径</param>
        /// <param name="storePath">文件存放地址，包含文件名(最好加上时间戳避免文件名重复)</param>
        /// <param name="headerRowNameList">标题集合</param>
        /// <param name="contentItem"></param>
        /// <param name="message">返回的错误信息</param>
        /// <returns></returns>
        public List<T> InformationExcel(string workbookFile, string storePath, List<string> headerRowNameList,Hashtable contentItem, out string message)
        {
            IWorkbook iWorkBook = null;
            message = "";
            if (HttpDownload(workbookFile, storePath))
            {
                using (var file = new FileStream(storePath, FileMode.Open, FileAccess.Read))
                {
                    if (workbookFile.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
                        iWorkBook = new XSSFWorkbook(file);
                    else if (workbookFile.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
                        iWorkBook = new HSSFWorkbook(file);
                }
                var sheet = iWorkBook.GetSheetAt(0);
                var headerRow = sheet.GetRow(_startIndex);
                //EXCEL表头第一个单元格位置
                var headerRowFirstCellNum = headerRow.FirstCellNum;

                if (headerRow.GetCell(headerRowFirstCellNum) == null || headerRow.GetCell(headerRowFirstCellNum).ToString() != headerRowNameList[0])
                {
                    message = "<font style=\"color:red\">上传失败：</font>您上传的模板不正确";
                    return null;
                }

                var bag = new List<T>();

                var totalCount = sheet.LastRowNum - sheet.FirstRowNum - _startIndex;
                if (totalCount <= 0)
                {
                    message = "<font style=\"color:red\">上传失败：</font>无任何数据";
                    return null;
                }
                GetListDataInformation(sheet, headerRow, sheet.FirstRowNum + _startIndex + 1, sheet.LastRowNum, bag, contentItem);
                if (bag.Count <= 0)
                {
                    message = "<font style=\"color:red\">上传失败：</font>请在模板中查看上传规则，并检查数据正确性";
                }
                return bag.ToList();
            }
            else
            {
                message = "<font style=\"color:red\">读取出错</font>无任何数据";
                return null;
            }
        }

        public List<T> GetListDataInformation(ISheet sheet, IRow headerRow, int startIndex, int endIndex, List<T> lists,Hashtable contentItem)
        {
            var htLicenseNos = new List<string>();
            for (var i = startIndex; i <= endIndex; i++)
            {
                //1 去掉空行  如果返回false从新循环
                int out_i;
                int out_endIndex;
                if (RemoveEmptyRow(sheet, endIndex, i, out out_i, out out_endIndex))
                {
                    Dictionary<string, string> excelDictionary = null;
                    if (CellValuation(sheet, i, out excelDictionary))//2 给每个单元格判断空 不为空 赋值.ToString().Trim()  如果返回false从新循环
                    {
                        ConversionCellInformation(excelDictionary, i, sheet, headerRow, lists, contentItem);
                    }
                }
                i = out_i;
                endIndex = out_endIndex;
            }
            return lists;
        }

        public bool ConversionCellInformation(Dictionary<string, string> excelDictionary, int i, ISheet sheet, IRow headerRow, List<T> lists,Hashtable contentItem)
        {
            IRow row = sheet.GetRow(i);


            var t = Activator.CreateInstance<T>();
            var properties = t.GetType().GetProperties();
            // int j = 0;
            foreach (var column in properties)
            {

                int j = headerRow.Cells.FindIndex(
                    c =>
                        c.StringCellValue ==
                        (contentItem[column.Name] == null
                            ? column.Name
                            : contentItem[column.Name].ToString()));
                if (j >= 0 && row.GetCell(j) != null)
                {
                    object value = valueType(column.PropertyType, row.GetCell(j));
                    column.SetValue(t, value, null);

                }
                //j++;
                //if (j >= properties.Count())
                //{
                //    break;
                //}
            }
            lists.Add(t);
            return true;
        }

//去掉空行
        public bool RemoveEmptyRow(ISheet sheet, int endIndex, int i, out int out_i, out int out_endIndex)
        {
            if (sheet.GetRow(endIndex) == null)
            {
                out_i = --i;
                out_endIndex = --endIndex;
                return false;
            }
            for (int j = 0; j < sheet.GetRow(_startIndex).LastCellNum; j++)
            {
                bool c0_c14 = sheet.GetRow(endIndex).GetCell(j) == null || string.IsNullOrWhiteSpace(sheet.GetRow(endIndex).GetCell(j).ToString());
                if (!c0_c14)
                {
                    out_i = i;
                    out_endIndex = endIndex;
                    return true;
                }
            }
            out_i = --i;
            out_endIndex = --endIndex;
            return false;
        }

//取单元格赋值
        public bool CellValuation(ISheet sheet, int i, out Dictionary<string, string> excelDictionary)
        {
            IRow row = sheet.GetRow(i) ?? sheet.CreateRow(i);
            IRow headerRow = sheet.GetRow(_startIndex);
            try
            {
                excelDictionary = new Dictionary<string, string>();
                for (int j = headerRow.FirstCellNum; j < headerRow.LastCellNum; j++)
                {
                    var headerName = headerRow.GetCell(j).ToString().Trim();
                    var cell = row.GetCell(j);
                    var cellValue = GetCellValue(cell);
                    if (!string.IsNullOrWhiteSpace(headerName))
                    {
                        var value = GetNoTNullValue(cellValue);
                        excelDictionary.Add(headerName, value);
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                excelDictionary = null;
                return false;
            }
        }
        #endregion    

public ActionResult GetQuestionNaireExcelList(string CompanyName, Guid QuestionNaireId, int page, int rows)
        {
            //bool success = true;
            //string Error = string.Empty;
            var ExamId = "";
            QuestionManageFacade facade = new QuestionManageFacade();
            TrendArg tag = new TrendArg();
            if (page > 0)
            {
                tag.PageIndex = page;
            }

            if (rows > 0)
            {
                tag.PageSize = rows;
            }
            if (!string.IsNullOrEmpty(CompanyName))
            {
                tag.Name = CompanyName;

            }
            if (QuestionNaireId != null)
            {
                tag.QuestionNaireId = QuestionNaireId;

            }
            var result = facade.GetStaffByQuestionNaire(tag).Data;

            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

            if (result.Data != null)
            {
                #region 声明Excel文件对象

                //添加一个sheet
                NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
                //给sheet1添加第一行的头部标题
                NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
                row1.CreateCell(0).SetCellValue("考试时间");
                row1.CreateCell(1).SetCellValue("考试名称");
                row1.CreateCell(2).SetCellValue("企业名称");
                row1.CreateCell(3).SetCellValue("姓名");
                row1.CreateCell(4).SetCellValue("身份证号");
                row1.CreateCell(5).SetCellValue("电话");
                row1.CreateCell(6).SetCellValue("得分");
                row1.CreateCell(7).SetCellValue("是否合格");
                //row1.CreateCell(8).SetCellValue("监考人");

                ICellStyle style = book.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                sheet1.SetColumnWidth(0, 40 * 256);
                sheet1.SetColumnWidth(1, 30 * 256);
                sheet1.SetColumnWidth(2, 40 * 256);
                sheet1.SetColumnWidth(4, 36 * 256);
                sheet1.SetColumnWidth(6, 22 * 256);

                //将数据逐步写入sheet1各个行
                for (int i = 0; i < result.Data.Count; i++)
                {
                    NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                    rowtemp.CreateCell(0).SetCellValue(result.Data[i].StartTime.ToString());
                    rowtemp.CreateCell(1).SetCellValue(result.Data[i].QuestionNaireName);
                    rowtemp.CreateCell(2).SetCellValue(result.Data[i].CompanyName);
                    rowtemp.CreateCell(3).SetCellValue(result.Data[i].Name);
                    rowtemp.CreateCell(4).SetCellValue(result.Data[i].ID_Card);
                    rowtemp.CreateCell(5).SetCellValue(result.Data[i].Phone);
                    rowtemp.CreateCell(6).SetCellValue(Convert.ToDouble(result.Data[i].ResultsScore));
                    rowtemp.CreateCell(7).SetCellValue(result.Data[i].IsPass);
                    // rowtemp.CreateCell(8).SetCellValue(result.Data[i].InvigilateName);
                }
                #endregion
            }
            Guid newGuid = Guid.NewGuid();

            string excelPath = Path.Combine(this.Server.MapPath("/"), "考试记录", newGuid + ".xls");
            //string excelUrl = string.Format("{0}/{1}/{2}", Jinher.AMP.Store.BE.Common.CustomConfig.StoreUrl, "temp", newGuid + ".xls");
            Jinher.JAP.BaseApp.FileServer.Deploy.CustomDTO.FileDTO fileDTO = new Jinher.JAP.BaseApp.FileServer.Deploy.CustomDTO.FileDTO();
            fileDTO.UploadFileName = newGuid.ToString() + ".xls";

            FileStream fileHSSF = new FileStream(excelPath, FileMode.Create);
            book.Write(fileHSSF);
            byte[] byteArry = new byte[fileHSSF.Length];
            fileDTO.FileSize = fileHSSF.Length;
            fileHSSF.Close();
            System.IO.MemoryStream ms = new System.IO.MemoryStream(byteArry);
            fileDTO.FileData = byteArry;
            book.Write(ms);
            Jinher.JAP.BaseApp.FileServer.ISV.Facade.FileFacade fileFacade = new Jinher.JAP.BaseApp.FileServer.ISV.Facade.FileFacade();
            string excelUrl = System.Configuration.ConfigurationManager.AppSettings["FileServerUrl"] + fileFacade.UploadFile(fileDTO);

            return Json(new { code = "0", Success = true, Data = excelUrl }, JsonRequestBehavior.AllowGet);
        }

 

转载于:https://www.cnblogs.com/Mzg121584668/p/11194823.html