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 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 color:red\">上传失败：</font>您上传的模板不正确";
return null;
}
}
else
{
if (headerRow.GetCell(headerRowFirstCellNum) == null || headerRow.GetCell(headerRowFirstCellNum).ToString() != "ID_Card")
{
message = "<font color:red\">上传失败：</font>您上传的模板不正确";
return null;
}
}
var bag = new List<T>();
var totalCount = sheet.LastRowNum - sheet.FirstRowNum - _startIndex;
if (totalCount <= 0)
{
message = "<font color:red\">上传失败：</font>无任何数据";
return null;
}
GetListData(sheet, headerRow, sheet.FirstRowNum + _startIndex + 1, sheet.LastRowNum, bag, ExcelType);
if (bag.Count <= 0)
{
message = "<font color:red\">上传失败：</font>请在模板中查看上传规则，并检查数据正确性";
}
return bag.ToList();
}
else
{
message = "<font 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 color:red\">上传失败：</font>您上传的模板不正确";
return null;
}
var bag = new List<T>();
var totalCount = sheet.LastRowNum - sheet.FirstRowNum - _startIndex;
if (totalCount <= 0)
{
message = "<font color:red\">上传失败：</font>无任何数据";
return null;
}
GetListDataInformation(sheet, headerRow, sheet.FirstRowNum + _startIndex + 1, sheet.LastRowNum, bag, contentItem);
if (bag.Count <= 0)
{
message = "<font color:red\">上传失败：</font>请在模板中查看上传规则，并检查数据正确性";
}
return bag.ToList();
}
else
{
message = "<font 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;