C# Excel导入导出
/// <summary> /// 企业信息Excel批量导入 需要先引入NPOI /// </summary> /// <returns></returns> public ActionResult ImportEnterpriseInfomation() { var success = false; var message = string.Empty; var excelUrl = string.Empty; int totalCoun = 0; int successCount = 0; if (Request.Files == null || Request.Files.Count <= 0) { return Content("未获取到文件"); } var file1 = Request.Files[0]; int ImgSize = file1.ContentLength; if (ImgSize > (5 * 1024 * 1024)) { return Content("文件过大"); } string fileName = file1.FileName.Substring(0, file1.FileName.LastIndexOf('.')) + "-" + DateTime.Now.ToString("yyyyMMddhhmmss") + file1.FileName.Substring(file1.FileName.LastIndexOf('.')); //生成将要保存的随机文件名 // = GetFileName() + ".jpg"; //要上传的文件 var fs = file1.InputStream; BinaryReader r = new BinaryReader(fs); //使用UploadFile方法可以用下面的格式 byte[] postArray = r.ReadBytes((int)fs.Length); string fileSave = Server.MapPath("~/TemplateExcel/QuestionBank"); //获取文件的扩展名 file1.SaveAs(Path.Combine(fileSave, fileName)); TrainingFacade facade = new TrainingFacade(); var storePath = System.AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\QuestionBank\\" + fileName; //从EXCEL读取数据 var qbdto = EnterpriseInformationExcel(storePath, storePath, out message); var excelModels = new List<EnterpriseExcelModel>(); if (qbdto != null) { totalCoun = qbdto.Count; qbdto.ForEach(item => { var result = facade.InsertEnterpriseInformation(item); if (result.IsSuccess) { successCount++; } else { excelModels.Add(item); } }); success = successCount > 0 ? true : false; if (excelModels.Count > 0) { excelUrl = ExportEnterpriseInformation(excelModels); } } message += "<font style=\"color:green\">共" + totalCoun + "条，成功导入" + successCount + "条</font>"; return Json(new { success, msg = message, totalCoun, successCount, excelUrl }); } private List<EnterpriseExcelModel> EnterpriseInformationExcel(string filePath, string storePath, out string message) { var excelHelper = new ExcelHelper<EnterpriseExcelModel>(); 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("LicenseNum", "许可证编号*"); contentItem.Add("LicenceValidityPeriod", "许可证有效期"); contentItem.Add("Address", "注册地址*"); contentItem.Add("LegalPerson", "法定代表人"); contentItem.Add("LegalPhone", "法人联系电话"); contentItem.Add("Contacts", "联系人"); contentItem.Add("Phone", "联系电话"); var renewalItemViewModels = excelHelper.InformationExcel(filePath, storePath, titleList, contentItem, out message, 0); return renewalItemViewModels; } /// <summary> /// 企业信息错误信息导出 /// </summary> /// <returns></returns> private string ExportEnterpriseInformation(List<EnterpriseExcelModel> model) {
string filename = "";
string fullurl = "";
try
{
TrendArg arg = new TrendArg();
arg.appIds = AppId;
arg.Area = Area;
arg.timeOpt = StartTime;
arg.VersionTime = EndTime;
var EPFancade = new CockpitFacade();
var result = EPFancade.GetSunProduct_DifficultyStatistics(arg);
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
ICellStyle style = book.CreateCellStyle();
IFont dataFont = book.CreateFont();
dataFont.FontHeightInPoints = 11;//设置字体大小
style.SetFont(dataFont);
style.Alignment = HorizontalAlignment.Center;//居中
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.Height = 30 * 20;
row1.CreateCell(0).SetCellValue("序号");
row1.CreateCell(1).SetCellValue("困难和诉求");
row1.CreateCell(2).SetCellValue("数量（个）");
for (int i = 0; i <= 2; i++)
{
try
{
//style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
//sheet1.SetColumnWidth(i, 25 * 256);
ICell cell1 = sheet1.GetRow(0).GetCell(i);
cell1.CellStyle = style;
}
catch { }
}
sheet1.SetColumnWidth(0, 20 * 256);
sheet1.SetColumnWidth(1, 60 * 256);
sheet1.SetColumnWidth(2, 20 * 256);
if (result.Data != null && result.Data.Count > 0)
{
#region 声明Excel文件对象
//将数据逐步写入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].Num);
rowtemp.CreateCell(1).SetCellValue(result.Data[i].Difficulty ?? "");
rowtemp.CreateCell(2).SetCellValue(result.Data[i].DifficultyNum ?? "");
for (int j = 0; j <= 2; j++)
{
try
{
ICell cell1 = sheet1.GetRow(i + 1).GetCell(j);
cell1.CellStyle = style;
}
catch (Exception ex)
{
}
}
}
#endregion
}
string importPath = AppDomain.CurrentDomain.BaseDirectory + "ExcelExport\\";
filename = StartTime + "__" + EndTime + "_Sheet2_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
string produceCardPath = string.Format("{0}{1}", importPath, filename);
FileStream fileHSSF = new FileStream(produceCardPath, FileMode.Create);
book.Write(fileHSSF);
byte[] byteArry = new byte[fileHSSF.Length];
fileHSSF.Close();
System.IO.MemoryStream ms = new System.IO.MemoryStream(byteArry);
book.Write(ms);
//写入文件服务器
var pcmFilePath = Server.MapPath("~/ExcelExport/" + filename);
if (System.IO.File.Exists(pcmFilePath))
{
FileStream serverFs = new FileStream(pcmFilePath, FileMode.OpenOrCreate);
FileInfo fileInfo = new FileInfo(pcmFilePath);
BinaryReader serverBr = new BinaryReader(serverFs);
//使用UploadFile方法可以用下面的格式
byte[] postArray = serverBr.ReadBytes((int)serverFs.Length);
Jinher.JAP.BaseApp.FileServer.Deploy.CustomDTO.FileDTO fileDTO = new Jinher.JAP.BaseApp.FileServer.Deploy.CustomDTO.FileDTO();
fileDTO.UploadFileName = fileInfo.Name;
fileDTO.FileData = postArray;
fileDTO.FileSize = fileInfo.Length;
Jinher.JAP.BaseApp.FileServer.ISV.Facade.FileFacade fileFacade = new Jinher.JAP.BaseApp.FileServer.ISV.Facade.FileFacade();
string serverFilePath = fileFacade.UploadFile(fileDTO);
fullurl = System.Configuration.ConfigurationManager.AppSettings["FileServerUrl"] + serverFilePath;
}
}
catch (Exception ex)
{
SystemLog.LogWrite(
new LogEntity
{
CodeAuthor = "mazg",
ClassName = this.GetType().ToString(),
FunctionName = MethodBase.GetCurrentMethod().Name,
Error = ex
});
return Json(new { code = "0", Success = true, FileName = ex.Message }, JsonRequestBehavior.AllowGet);
}
return Json(new { uploaded = 1, fileName = "", url = fullurl, mes = "上传成功" }, JsonRequestBehavior.AllowGet);
using System; using System.Collections; using System.Collections.Generic; using System.IO; using System.Linq; using System.Reflection; using System.Text; using System.Text.RegularExpressions; using System.Xml; using System.Net; using System.Configuration; using Jinher.JAP.Common.Loging; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using NPOI.HSSF.UserModel; namespace Jinher.AMP.TPP.Common { public class ExcelHelper<T> { //private readonly ILog _logInfo = LogManager.GetLogger("INFO"); static int _startIndex = 1; readonly Hashtable _fromEnglishToChineseForBatchRenewalItem = new Hashtable() { { "QuestionType", "题型必填*" }, { "QuestionTitle", "题干内容必填*" }, { "QuestionExplain", "题目解析" }, { "IsRight", "正确答案必填*" }, { "QuestionAnswerA", "选项A必填*" }, { "QuestionAnswerB", "选项B必填*" }, { "QuestionAnswerC", "选项C" }, { "QuestionAnswerD", "选项D" } }; readonly List<string> CommonheaderRowNameList = new List<string>() { "题型必填*", "题干内容必填*", "题目解析", "正确答案必填*", "选项A必填*", "选项B必填*", "选项C", "选项D" }; readonly Hashtable _PersonItem = new Hashtable() { { "ID_Card", "ID_Card" }, { "Pro", "Pro" }, { "City", "City" }, { "Area", "Area" }, { "Town", "Town" }, { "Street", "Street" }, { "CompanyName", "CompanyName" }, { "Phone", "Phone" } }; readonly List<string> PersonNameList = new List<string>() { "ID_Card", "Pro", "City", "Area", "Town", "Street", "CompanyName", "Phone" }; private readonly string _fileServerURL = ConfigurationManager.AppSettings["FileServerURL"].ToString(); /// <summary> /// 导入Excel /// </summary> /// <param name="workbookFile">Excel所在路径</param> /// <param name="storePath">文件存放地址，包含文件名(最好加上时间戳避免文件名重复)</param> /// <param name="categories">客户类集合</param> /// <param name="agentInfos">传入代理人的集合</param> /// <param name="excelErrorDataList">输出的错误数据集合</param> /// <param name="message">返回的错误信息</param> /// <returns></returns> public List<T> FromExcel(string workbookFile, string storePath, string ExcelType, 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; var headerRowNameList = new List<string>(); if (ExcelType == "1") { headerRowNameList.AddRange(CommonheaderRowNameList); } else { headerRowNameList.AddRange(PersonNameList); } if (ExcelType == "1") { if (headerRow.GetCell(headerRowFirstCellNum) == null || headerRow.GetCell(headerRowFirstCellNum).ToString() != "题型必填*") { message = "<font style=\"color:red\">上传失败：</font>您上传的模板不正确"; return null; } } else { if (headerRow.GetCell(headerRowFirstCellNum) == null || headerRow.GetCell(headerRowFirstCellNum).ToString() != "ID_Card") { 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; } GetListData(sheet, headerRow, sheet.FirstRowNum + _startIndex + 1, sheet.LastRowNum, bag, ExcelType); if (bag.Count <= 0) { message = "<font style=\"color:red\">上传失败：</font>请在模板中查看上传规则，并检查数据正确性"; } return bag.ToList(); } else { message = "<font style=\"color:red\">读取出错</font>无任何数据"; return null; } } object valueType(Type t, ICell value) { object o = null; string strt = t.ToString(); if (t.Name == "Nullable`1") { strt = t.GetGenericArguments()[0].Name; } var valStr = string.Empty; if (value != null) valStr = GetNoTNullValue(GetCellValue(value)); if (string.IsNullOrWhiteSpace(valStr)) return o; switch (strt) { case "System.Decimal": o = decimal.Parse(valStr.Trim('\0')); break; case "System.Int32": o = int.Parse(valStr.Trim('\0')); break; case "System.Float": o = float.Parse(valStr.Trim('\0')); break; case "System.DateTime": if (HSSFDateUtil.IsCellDateFormatted(value)) { o = Convert.ToDateTime(valStr); } break; case "System.Int64": o = Int64.Parse(valStr.Trim('\0')); break; case "Int64": o = value == null ? null : (Int64.Parse(valStr.Trim('\0'))) as long?; break; case "DateTime": if (string.IsNullOrWhiteSpace(value.ToString())) o = null; else if (value.CellType == CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(value)) o = (DateTime?)value.DateCellValue; else o = Convert.ToDateTime(valStr); break; default: o = valStr.Trim('\0'); break; } return o; } #region 优化方法GetListData int type = 0;//这个全局变量在下面用到 public List<T> GetListData(ISheet sheet, IRow headerRow, int startIndex, int endIndex, List<T> lists, string ExcelType) { 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从新循环 { if (ExcelType == "1") { if (SpecificCheckCell(excelDictionary, i, sheet))//3 执行具体的校验 这一步下面还要细分8 { ConversionCell(excelDictionary, i, sheet, headerRow, lists); } } else { ConversionAttachCell(excelDictionary, i, sheet, headerRow, lists); } } } i = out_i; endIndex = out_endIndex; } return lists; } #region //转化 public bool ConversionCell(Dictionary<string, string> excelDictionary, int i, ISheet sheet, IRow headerRow, List<T> lists) { //int type = 0;//有车架号和发动机号 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 == (_fromEnglishToChineseForBatchRenewalItem[column.Name] == null ? column.Name : _fromEnglishToChineseForBatchRenewalItem[column.Name].ToString())); if (j >= 0 && row.GetCell(j) != null) { object value = valueType(column.PropertyType, row.GetCell(j)); if (value != null) { switch (column.Name) { case "QuestionType": value = value.ToString().ToUpper(); break; case "QuestionTitle": value = value.ToString().ToUpper(); break; case "QuestionExplain": value = value.ToString().ToUpper(); break; case "IsRight": value = value.ToString().ToUpper(); break; case "QuestionAnswerA": value = value.ToString().ToUpper(); break; case "QuestionAnswerB": value = value.ToString().ToUpper(); break; case "QuestionAnswerC": value = value.ToString().ToUpper(); break; case "QuestionAnswerD": value = value.ToString().ToUpper(); break; } } column.SetValue(t, value, null); } //j++; //if (j >= properties.Count()) //{ // break; //} } lists.Add(t); return true; } //转化 public bool ConversionAttachCell(Dictionary<string, string> excelDictionary, int i, ISheet sheet, IRow headerRow, List<T> lists) { //int type = 0;//有车架号和发动机号 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 == (_PersonItem[column.Name] == null ? column.Name : _PersonItem[column.Name].ToString())); if (j >= 0 && row.GetCell(j) != null) { object value = valueType(column.PropertyType, row.GetCell(j)); if (value != null) { switch (column.Name) { case "ID_Card": value = value.ToString().ToUpper(); break; case "Pro": value = value.ToString().ToUpper(); break; case "City": value = value.ToString().ToUpper(); break; case "Area": value = value.ToString().ToUpper(); break; case "Town": value = value.ToString().ToUpper(); break; case "Street": value = value.ToString().ToUpper(); break; case "CompanyName": value = value.ToString().ToUpper(); break; case "Phone": value = value.ToString().ToUpper(); break; } } 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 static string GetNoTNullValue(string values) { if (string.IsNullOrWhiteSpace(values)) return string.Empty; var stringItem = ""; foreach (var item in values.ToArray()) { if (char.IsNumber(item) || char.IsLetter(item) || char.IsPunctuation(item)) { stringItem = stringItem + item; } } return stringItem; } //取单元格赋值 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; } } /// <summary> /// 获取Excel对应的内容 为了支持有表达式的cell 取到正确的值 /// </summary> /// <param name="cell"></param> /// <returns></returns> public string GetCellValue(ICell cell) { try { var value = string.Empty; if (cell == null) return value; switch (cell.CellType) { case CellType.Numeric: if (DateUtil.IsCellDateFormatted(cell)) value = cell.DateCellValue.ToString("yyyy-MM-dd"); else value = cell.NumericCellValue.ToString(); break; case CellType.String: value = cell.StringCellValue; break; case CellType.Formula: value = cell.StringCellValue; break; case CellType.Boolean: value = cell.BooleanCellValue.ToString(); break; default: value = cell.ToString().Trim('\0'); break; } return value; } catch (Exception) { return cell.ToString().Trim('\0'); } } public bool SpecificCheckCell(Dictionary<string, string> excelDictionary, int i, ISheet sheet) { var sb = new StringBuilder(); //这里具体校验分了八个步骤 //1检查是否为空字典 if (!SpecificCheckCell_ToEmpty(excelDictionary)) { return false; } //验证是否包含特殊字符 var regex = @"[\'|\\‘’“”]+"; foreach (var item in excelDictionary) { if (Regex.IsMatch(item.Value, regex)) sb.Append(item.Key + "包含有特殊字符"); } //2验证 车牌 车架 发动机 车主证件号码 return Specific(excelDictionary, sb); } public bool Specific(Dictionary<string, string> excelDictionary, StringBuilder sb) { List<string> questionBank = new List<string>() { "单选题", "多选题", "判断题" }; string questionAnswer = "ABCD"; if (excelDictionary["题型必填*"] == null || string.IsNullOrEmpty(excelDictionary["题干内容必填*"]) || string.IsNullOrEmpty(excelDictionary["正确答案必填*"]) || string.IsNullOrEmpty(excelDictionary["选项A必填*"]) || string.IsNullOrEmpty(excelDictionary["选项B必填*"])) { sb.Append("必填项不能为空；"); } if (!questionBank.Exists(x => x.Contains(excelDictionary["题型必填*"].ToString()))) { sb.Append("题型只能选择单选题，多选题，判断题；"); } if (excelDictionary["题型必填*"] == "单选题" || excelDictionary["题型必填*"] == "多选题") { if (!questionAnswer.Contains(excelDictionary["正确答案必填*"].ToString())) { sb.Append("正确答案只能为A,B,C,D"); } } if (sb.Length >0) { return false; } else { return true; } } #region 校验 下面的八个方法 public bool SpecificCheckCell_ToEmpty(Dictionary<string, string> excelDictionary) { var str = new StringBuilder(); foreach (var item in excelDictionary) { str.Append(item.Value); } if (string.IsNullOrWhiteSpace(str.ToString())) return false; else return true; } /// <summary> /// 非法字符转换 /// </summary> /// <param name="str"></param> /// <returns></returns> public static string ReplaceStr(string str) { str = str.Replace("'", ""); str = str.Replace("/", ""); str = str.Replace(@"\", ""); return str; } /// <summary> /// 将字符串从0截取指定长度 /// </summary> /// <param name="oldString"></param> /// <param name="length"></param> /// <returns></returns> private string CutString(string oldString, int length) { return string.Join("", (oldString ?? "").ToList().Take(length)); } #endregion #endregion #endregion private bool CheckExcelRowData(ISheet sheet, int rowIndex, int cellIndex, int dataLength) { bool isPassChecked = true; if (sheet.GetRow(rowIndex).GetCell(cellIndex) != null && !string.IsNullOrEmpty(sheet.GetRow(rowIndex).GetCell(cellIndex).ToString()) && sheet.GetRow(rowIndex).GetCell(cellIndex).ToString().Trim().Length > dataLength) { isPassChecked = false; } return isPassChecked; } /// <summary> /// http下载文件 /// </summary> /// <param name="url">下载文件地址</param> /// <param name="storePath">文件存放地址，包含文件名(最好加上时间戳避免文件名重复)</param> /// <returns></returns> public bool HttpDownload(string url, string storePath) { return true; //try //{ // //_logInfo.Info("读取文件路径" + url); // string tempPath = System.IO.Path.GetDirectoryName(storePath); // System.IO.Directory.CreateDirectory(tempPath); //创建临时文件目录 // string tempFile = tempPath + @"\" + System.IO.Path.GetFileName(storePath) + ".temp"; //临时文件 // if (File.Exists(tempFile)) // { // File.Delete(tempFile); //存在则删除 // } // if (File.Exists(storePath)) // { // File.Delete(storePath); //存在则删除 // } // //_logInfo.Info("创建临时文件：" + tempFile); // FileStream fs = new FileStream(tempFile, FileMode.Append, FileAccess.Write, FileShare.ReadWrite); // // 设置参数 // HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest; // //发送请求并获取相应回应数据 // HttpWebResponse response = request.GetResponse() as HttpWebResponse; // //直到request.GetResponse()程序才开始向目标网页发送Post请求 // Stream responseStream = response.GetResponseStream(); // //创建本地文件写入流 // //Stream stream = new FileStream(tempFile, FileMode.Create); // byte[] bArr = new byte[1024]; // int size = responseStream.Read(bArr, 0, (int)bArr.Length); // while (size > 0) // { // //stream.Write(bArr, 0, size); // fs.Write(bArr, 0, size); // size = responseStream.Read(bArr, 0, (int)bArr.Length); // } // //stream.Close(); // fs.Close(); // responseStream.Close(); // //_logInfo.Info("移动临时文件到指定位置开始：" + storePath); // System.IO.File.Move(tempFile, storePath); // //_logInfo.Info("移动临时文件到指定位置完成：" + storePath); // return true; //} //catch (Exception ex) //{ // //_logInfo.Error("读取批量续保文件路径" + url + " 错误信息：" + ex.Message); // return false; //} } #region 个人信息导入 /// <summary> /// /// </summary> /// <param name="workbookFile">Excel所在路径</param> /// <param name="storePath">文件存放地址，包含文件名(最好加上时间戳避免文件名重复)</param> /// <param name="headerRowNameList">标题集合</param> /// <param name="contentItem"></param> /// <param name="message">返回的错误信息</param> /// <param name="startIndex">标头索引</param> /// <returns></returns> public List<T> InformationExcel(string workbookFile, string storePath, List<string> headerRowNameList,Hashtable contentItem, out string message,int startIndex=1) { IWorkbook iWorkBook = null; message = ""; _startIndex = startIndex; 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; } #endregion } }
ICellStyle style = book.CreateCellStyle(); IFont dataFont = book.CreateFont(); dataFont.FontHeightInPoints = 11;//设置字体大小 style.SetFont(dataFont); style.Alignment = HorizontalAlignment.Center;//居中 style.VerticalAlignment = VerticalAlignment.Center; style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
