特别注意：
导入的Excel中标题不能有重复的，否则会导入出错
Excel模板1
Excel模板2
模板1在导入时，Excel的title对应后台字段的写法：
Dictionary<string, string> dict = new Dictionary<string, string>()
{
{ "清单层级码", "ListLevelCode" },
{ "清单名称", "ListName" },
{ "清单编码", "ListCode" },
{ "清单单位", "ListUnit" },
{ "单价(元)", "UnitPrice" },
{ "合同工作量工程量", "Quantities" },
{ "合价（元)", "TotalPrice" },
};
模板2在导入时，Excel的title对应后台字段的写法：
因为模板2是跨行标题，其实在后台读取时，第一行的title和第二行第一个单元格名称合并，其余不变，如主合同那一栏，后台读取的title其实是“主合同清单层级码”， “清单名称”，“清单编码”，所以对应的写法就为：
Dictionary<string, string> dict = new Dictionary<string, string>()
{
{ "主合同清单层级码", "ListLevelCode" },
{ "清单名称", "ListName" },
{ "清单编码", "ListCode" },
{ "清单单位", "ListUnit" },
{ "单价(元)", "UnitPrice" },
//........剩余的省略不写
};
公用方法：
public class ExcelHelper
{
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="excelPath"></param>
/// <param name="headerPropDict">表头字典</param>
/// <param name="sheetName"></param>
/// <param name="indexHeaderStart">表头开始行</param>
/// <param name="indexHeaderEnd">表头结束行</param>
/// <returns></returns>
public List<T> ExcelToCollection<T>(Stream excelFileStream, Dictionary<string, string> headerPropDict, string extension, string sheetName, int indexHeaderStart, int indexHeaderEnd ,ref Dictionary<T, string> errorDic)
{
var result = new List<T>();
List<string> errorInfoList = new List<string>();
if (indexHeaderStart < 0 || indexHeaderEnd < 0 || indexHeaderStart > indexHeaderEnd)
{
throw new ArgumentException();
}
try
{
IWorkbook workbook;
if (extension == ".xls")
{
workbook = new HSSFWorkbook(excelFileStream);
}
else if (extension == ".xlsx")
{
workbook = new XSSFWorkbook(excelFileStream);
}
else
{
throw new Exception("格式不支持");
}
ISheet sheet;
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
throw new Exception("sheet name Error");
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
var headerDict = GetHeaderIndexDict(sheet, indexHeaderStart, indexHeaderEnd);
if (headerDict.Count == 0)
{
throw new Exception("未读取到表头，请检查Excel模板！");
}
int errRow = 0;
for (int i = indexHeaderEnd; i <= sheet.LastRowNum; i++)
{
// 产生一个新的泛型对象
var model = Activator.CreateInstance<T>();
// 是否为空行
bool isEmptyRow = true;
IRow dataRow = sheet.GetRow(i);
int cellCount = headerDict.Count;
errRow = i + 1;
if (dataRow != null)
{
// 循环列数据
for (int j = dataRow.FirstCellNum; j < cellCount; j++)
{
var propertyName = headerPropDict[headerDict[j]];
string keyName = headerDict[j];
PropertyInfo prop = model.GetType().GetProperty(propertyName);
//string keyName = headerPropDict[j].Key;
ICell cell = dataRow.GetCell(j);
var value = GetCellValue<T>(sheet, cell, j, i, indexHeaderEnd, result, prop.Name);
if (!string.IsNullOrEmpty(value))
{
// 特殊格式：
string proFullName = prop.PropertyType.FullName;
if (proFullName.Contains("Decimal"))
{
decimal intV = 0;
if (value.IndexOf('*') > -1)
{
value = value.Replace("*", string.Empty);
}
if (!decimal.TryParse(value, out intV))
{
errorInfoList.Add(keyName + "格式有误");
}
prop.SetValue(model, intV, null);
}
else if (proFullName.Contains("Double"))
{
// 浮点型
double intV = 0;
if (!double.TryParse(value, out intV))
{
errorInfoList.Add(keyName + "格式有误");
}
prop.SetValue(model, intV, null);
}
else if (proFullName.Contains("DateTime"))
{
// 日期型
DateTime intV = DateTime.Now;
if (!string.IsNullOrEmpty(value) && value.IndexOf('月') > 0)
{
// xlsx版本的Excel,的日期格式为：01-5月-2004，需要转换
string[] strs = value.Split('-');
if (strs.Length == 3 && strs[0].Length == 2)
{
value = string.Format("{0}-{1}-{2}", strs[2], strs[1].Replace("月", string.Empty), strs[0]);
}
}
if (!DateTime.TryParse(value, out intV))
{
errorInfoList.Add(keyName + "格式有误");
}
prop.SetValue(model, intV, null);
}
else if (proFullName.Contains("Int32"))
{
// 日期型
int intV = 0;
if (!int.TryParse(value, out intV))
{
errorInfoList.Add(keyName + "格式有误");
}
prop.SetValue(model, intV, null);
}
else if (proFullName.Contains("Int16"))
{
// 日期型
short intV = 0;
if (!short.TryParse(value, out intV))
{
errorInfoList.Add(keyName + "格式有误");
}
prop.SetValue(model, intV, null);
}
else if (proFullName.Contains("Boolean"))
{
bool boV = false;
// 布尔型 是/否
if (value == "是")
{
boV = true;
}
else if (value == "否")
{
boV = false;
}
else
{
if (!bool.TryParse(value, out boV))
{
errorInfoList.Add(keyName + "格式有误");
}
}
prop.SetValue(model, boV, null);
}
else
{
try
{
prop.SetValue(model, value, null);
}
catch (Exception ex)
{
Logger.Write(string.Format("错误信息：{0}，堆栈：{1}", ex.Message, ex.StackTrace));
errorInfoList.Add(keyName + "格式有误");
}
}
// 赋值
isEmptyRow = false;
}
}
}
if (errorInfoList.Count > 0)
{
errorDic.Add(model, string.Join(",", errorInfoList));
}
// 添加非空行数据到DTO
if (!isEmptyRow&& (errorInfoList==null||errorInfoList.Count==0))
{
result.Add(model);
}
}
}
catch (Exception ex)
{
throw new Exception("解析失败", ex);
}
return result;
}
public DataTable ExcelToDataTable(string excelPath, string sheetName, int indexHeaderStart, int indexHeaderEnd)
{
DataTable dataTable = new DataTable();
if (string.IsNullOrEmpty(excelPath) || !File.Exists(excelPath))
{
throw new FileNotFoundException();
}
try
{
using (Stream fileStream = new FileStream(excelPath, FileMode.Open))
{
IWorkbook workbook= new HSSFWorkbook(fileStream);
ISheet sheet;
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
throw new Exception("sheet name Error");
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
//表头
if (indexHeaderStart > 0)
{
GetDataTableColumns(sheet, indexHeaderStart, indexHeaderEnd, ref dataTable);
}
else
{
var firstRow = sheet.GetRow(sheet.FirstRowNum);
int cellCount = firstRow.LastCellNum;
for (int i = firstRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn();
dataTable.Columns.Add(column);
}
}
//取值
for (int i = indexHeaderEnd; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = dataTable.NewRow();
int cellCount = row.LastCellNum;
bool isEmptyRow = true;
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
ICell cell = row.GetCell(j);
var value = GetCellValue(sheet, cell, j, i, indexHeaderEnd, dataTable);
if (!string.IsNullOrEmpty(value))
{
dataRow[j] = value;
isEmptyRow = false;
}
}
if (!isEmptyRow)
{
dataTable.Rows.Add(dataRow);
}
}
}
}
catch (Exception ex)
{
throw new Exception("解析失败", ex);
}
return dataTable;
}
/// <summary>
/// 读取DataTable的列名
/// </summary>
/// <param name="sheet"></param>
/// <param name="firstHeaderRowIndex"></param>
/// <param name="lastHeaderRowIndex"></param>
/// <param name="dataTable"></param>
private void GetDataTableColumns(ISheet sheet, int firstHeaderRowIndex, int lastHeaderRowIndex, ref DataTable dataTable)
{
var headerDict = GetHeaderIndexDict(sheet, firstHeaderRowIndex, lastHeaderRowIndex);
foreach (var key in headerDict.Keys.OrderBy(o => o))
{
var head = headerDict[key];
DataColumn column = new DataColumn(head);
dataTable.Columns.Add(column);
}
}
/// <summary>
/// 获取表头与所在列索引的映射
/// </summary>
/// <param name="sheet"></param>
/// <param name="firstHeaderRowIndex"></param>
/// <param name="lastHeaderRowIndex"></param>
/// <returns></returns>
private Dictionary<int, string> GetHeaderIndexDict(ISheet sheet, int firstHeaderRowIndex, int lastHeaderRowIndex)
{
var dict = new Dictionary<int, string>();
try
{
// 循环获得表头
for (int i = firstHeaderRowIndex - 1; i < lastHeaderRowIndex; i++)
{
IRow headerRow = sheet.GetRow(i);
int cellCount = headerRow.LastCellNum;
for (int j = headerRow.FirstCellNum; j < cellCount; j++)
{
var value = headerRow.GetCell(j).StringCellValue.Trim();
if (!string.IsNullOrEmpty(value))
{
if (dict.Keys.Contains(j))
{
dict[j] += value;
}
else
{
dict.Add(j, value);
}
}
}
}
// 遍历表头字典，消除空格
for (int i = 0; i < dict.Count; i++)
{
var value = dict[i];
ReplaceSpace(ref value);
dict[i] = value;
}
}
catch (Exception ex)
{
throw new Exception("获取表头映射异常", ex);
}
return dict;
}
/// <summary>
/// 去除空值与转义符号
/// </summary>
/// <param name="cellValue"></param>
private void ReplaceSpace(ref string cellValue)
{
cellValue = cellValue.Replace(" ", string.Empty);
cellValue = Regex.Replace(cellValue, @"\t|\n|\r", string.Empty);
}
/// <summary>
/// 判断单元格是否被合并
/// </summary>
/// <param name="cellIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="sheet"></param>
/// <param name="firstRegionRow"></param>
/// <returns></returns>
private static bool IsMergedRegionCell(int cellIndex, int rowIndex, ISheet sheet, ref int firstRegionRow)
{
bool isMerged = false;
var regionLists = GetMergedCellRegion(sheet);
foreach (var cellRangeAddress in regionLists)
{
for (int i = cellRangeAddress.FirstRow; i <= cellRangeAddress.LastRow; i++)
{
if (rowIndex == i)
{
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
if (cellIndex == j)
{
isMerged = true;
firstRegionRow = cellRangeAddress.FirstRow;
break;
}
else
{
continue;
}
}
}
else
{
continue;
}
}
}
return isMerged;
}
/// <summary>
/// 获取合并区域信息
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
private static List<CellRangeAddress> GetMergedCellRegion(ISheet sheet)
{
int mergedRegionCellCount = sheet.NumMergedRegions;
var returnList = new List<CellRangeAddress>();
for (int i = 0; i < mergedRegionCellCount; i++)
{
returnList.Add(sheet.GetMergedRegion(i));
}
return returnList;
}
/// <summary>
/// 读取单元格的内容
/// </summary>
/// <param name="sheet"></param>
/// <param name="cell"></param>
/// <param name="cellIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="indexHeaderEnd"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
private string GetCellValue<T>(ISheet sheet, ICell cell, int cellIndex, int rowIndex, int indexHeaderEnd, List<T> list, string propName)
{
if (sheet == null || cell == null)
{
return null;
}
string value;
switch (cell.CellType)
{
case CellType.Formula:
switch (cell.CachedFormulaResultType)
{
case CellType.Numeric:
value = cell.NumericCellValue + string.Empty;
break;
case CellType.Boolean:
value = cell.BooleanCellValue + string.Empty;
break;
default:
value = cell.StringCellValue;
break;
}
break;
default:
value = cell.ToString();
break;
}
if (!string.IsNullOrEmpty(value))
{
return value;
}
int firstRegionRow = 0;
if (IsMergedRegionCell(cellIndex, rowIndex, sheet, ref firstRegionRow)) //2、单元格为合并单元格且不在合并区域左上角
{
if (firstRegionRow >= indexHeaderEnd && rowIndex != firstRegionRow)//合并单元格 第一行无值为cell合并
{
int resultIndex = firstRegionRow - indexHeaderEnd;
var oldModel = list.Select((p, d) => new { p, d })
.Where(p => p.d == resultIndex)
.Select(p => p.p).First();
var regionValue = oldModel.GetType().GetProperty(propName).GetValue(oldModel, null);//获得合并单元格第一行数据
return regionValue + string.Empty;
}
}
return null;
}
/// <summary>
/// 读取单元格的内容
/// </summary>
/// <param name="sheet"></param>
/// <param name="cell"></param>
/// <param name="cellIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="indexHeaderEnd"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
private string GetCellValue(ISheet sheet, ICell cell, int cellIndex, int rowIndex, int indexHeaderEnd, DataTable dataTable)
{
if (sheet == null || cell == null)
{
return null;
}
string value;
switch (cell.CellType)
{
case CellType.Formula:
switch (cell.CachedFormulaResultType)
{
case CellType.Numeric:
value = cell.NumericCellValue + string.Empty;
break;
case CellType.Boolean:
value = cell.BooleanCellValue + string.Empty;
break;
default:
value = cell.StringCellValue;
break;
}
break;
default:
value = cell.ToString();
break;
}
if (!string.IsNullOrEmpty(value))
{
return value;
}
int firstRegionRow = 0;
if (IsMergedRegionCell(cellIndex, rowIndex, sheet, ref firstRegionRow)) //2、单元格为合并单元格且不在合并区域左上角
{
if (firstRegionRow >= indexHeaderEnd && rowIndex != firstRegionRow)//合并单元格 第一行无值为cell合并
{
int resultIndex = firstRegionRow - indexHeaderEnd;
var regionValue = dataTable.Rows[resultIndex].ItemArray[cellIndex] + string.Empty; //获得合并单元格第一行数据
return regionValue;
}
}
return null;
}
}
Controller调用：
[HttpPost]
public string EndOverallImportList(OverallExportOrImport overAllImport)
{
var isSuccess = true;//是否通过验证
CListEntity returnObj = new CListEntity();//返回前台的数据
ExcelHelper excelHelper = new ExcelHelper();
try
{
string strFileName = overAllImport.strFileName;//上传的Excel名称
string fileType = ".xls,.xlsx"; // 定义上传文件的类型字符串
string fileEx = System.IO.Path.GetExtension(strFileName); // 获取上传文件的扩展名
if (!fileType.Contains(fileEx))
{
returnObj.ErrorImportResult = "导入失败!\n原因：文件类型不对，只能导入xls和xlsx格式的文件!";
return JsonHelper.SerializeObject(returnObj);
}
var fileStream = new FileStream(strFileName, FileMode.Open, FileAccess.Read);
//Excel的title对应后台数据库字段
Dictionary<string, string> dict = new Dictionary<string, string>()
{
{ "主合同清单层级码", "ListLevelCode" },
{ "清单名称", "ListName" },
{ "清单编码", "ListCode" },
{ "清单单位", "ListUnit" },
{ "单价(元)", "UnitPrice" },
{ "合同工作量工程量", "Quantities" },
{ "合价（元)", "TotalPrice" },
{ "累计清单工程量", "LastQuantities" },
{ "合价(元）", "LastTotalPrice" },
{ "乙方上报工程量", "APCurrentQuantities" },
{ "合价(元)", "APCurrentTotalPrice" },
{ "基层初审工程量", "CurrentQuantities" },
{ "合价（元）", "CurrentTotalPrice" },
{ "超合同结算说明", "Remark" },
{ "最终审核工程量", "RVCurrentQuantities" },
{ "合价(元.)", "RVCurrentTotalPrice" },
{ "超合同结算说明.", "RVRemark" }
};
Dictionary<CListEntity, string> errorDic = new Dictionary<CListEntity, string>();
List<CListEntity> modelList = excelHelper.ExcelToCollection<CListEntity>(fileStream, dict, fileEx, "", 1, 2, ref errorDic);
//这个地方可以根据系统需求对导入数据进行各种验证，如：
if (modelList == null || modelList.Count == 0)
{
returnObj.ErrorImportResult = "导入失败!\n原因：无导入数据!";//向前台返回导入失败的原因
return JsonHelper.SerializeObject(returnObj);
}
// 格式转换出错
if (errorDic.Count > 0)
{
string error = "导入失败!\n原因：";
foreach (KeyValuePair<CListEntity, string> kv in errorDic)
{
error = "\n" + kv.Value;
}
returnObj.ErrorImportResult = error;
return JsonHelper.SerializeObject(returnObj);
}
//其余验证省略
if (isSuccess)
{
//验证通过以后，保存数据
}
}
catch (Exception ex)
{
Logger.Write(ex.Message);
returnObj.ErrorMessage = "数据导入出现异常:" + ex.Message;
return JsonHelper.SerializeObject(returnObj);
}
return JsonHelper.SerializeObject(returnObj);
}
以上，导入完成！
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
