public static async Task <string> Upload(Stream stream, ExcelMap map,
IEmployeeService employeeService = null,
IProjectService projectService = null)
{
IWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheetAt(map.SheetIndex);
if (sheet == null)
{
throw new Exception($"上传的文件没有索引为{map.SheetIndex}的工作表");
}
IDictionary <string, int> Indecies = new Dictionary <string, int>();
int startRowIndex;
if (map.WithHeader)
{
IRow header = sheet.GetRow(0);
if (header == null)
{
throw new Exception($"上传的文件第一行必须是符合格式的标题行");
}
foreach (var caption in header.Cells)
{
Indecies.Add(caption.StringCellValue, caption.ColumnIndex);
}
startRowIndex = 1;
}
else
{
IRow firstRow = sheet.GetRow(0);
if (firstRow == null)
{
throw new Exception("上传文件没有数据，必须从第一行开始有数据");
}
for (var i = 0; i <= firstRow.LastCellNum; i++)
{
Indecies.Add(i.ToString(), i);
}
startRowIndex = 0;
}
//由于现在仅支持项目和人员导入，因此直接使用一个布尔值来区别类型。
var projectType = typeof(Entity.Project);
var staffType = typeof(Entity.Employee);
bool isProject;
if (map.IsTypeOf(projectType))
{
isProject = true;
}
else if (map.IsTypeOf(staffType))
{
isProject = false;
}
else
{
throw new Exception("not supported upload entity type");
}
int total = 0, success = 0, failure = 0;
StringBuilder messageBuilder = new StringBuilder();
for (var rowIndex = startRowIndex; rowIndex <= sheet.LastRowNum; rowIndex++)
{
total++;
IRow data = sheet.GetRow(rowIndex);
object entity;
if (isProject)
{
entity = new Entity.Project();
}
//{
// ActiveStatus = true,
// CreatedOn = DateTime.Now,
// CreatedBy = Cnf.CodeBase.Serialize.ValueHelper.DBKEY_NULL
//};
else
{
entity = new Entity.Employee
{
ActiveStatus = true,
CreatedOn = DateTime.Now,
CreatedBy = Cnf.CodeBase.Serialize.ValueHelper.DBKEY_NULL,
InDate = Cnf.CodeBase.Serialize.ValueHelper.DbDate_Null,
InDutyID = Cnf.CodeBase.Serialize.ValueHelper.DBKEY_NULL,
InProjectID = Cnf.CodeBase.Serialize.ValueHelper.DBKEY_NULL
}
};
try
{
foreach (var propMap in map.Properties)
{
if (!Indecies.ContainsKey(propMap.ColumnIndex))
{
throw new Exception($"上传的文件无法定位到列索引[{propMap.ColumnIndex}]");
}
object cellValue = propMap.PropertyType switch
{
PropertyType.Boolean => data.GetCell(Indecies[propMap.ColumnIndex]).BooleanCellValue,
PropertyType.DateTime => data.GetCell(Indecies[propMap.ColumnIndex]).DateCellValue,
PropertyType.Int => Convert.ToInt32(data.GetCell(Indecies[propMap.ColumnIndex]).NumericCellValue),
PropertyType.Real => data.GetCell(Indecies[propMap.ColumnIndex]).NumericCellValue,
PropertyType.Text => data.GetCell(Indecies[propMap.ColumnIndex]).StringCellValue,
_ => throw new Exception("not supported property data type"),
};
PropertyInfo propertyInfo = isProject ?
projectType.GetProperty(propMap.PropertyName,
BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance) :
staffType.GetProperty(propMap.PropertyName,
BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
if (propertyInfo == null)
{
throw new Exception($"Excel映射配置的属性{propMap.PropertyName}不是实体类的成员");
}
propertyInfo.SetValue(entity, cellValue);
}
}
catch (Exception ex)
{
messageBuilder.AppendLine($"行{rowIndex}读文件错：{ex.Message}<br/>");
failure++;
continue; //直接开始处理下一行。
}
//保存到数据库
try
{
if (isProject)
{
await projectService.SaveProject((Entity.Project) entity);
}
else
{
await employeeService.SaveEmployee((Entity.Employee) entity);
}
success++;
}
catch (Exception ex)
{
messageBuilder.AppendLine($"行{rowIndex}写数据库错：{ex.Message}<br/>");
failure++;
}
}
messageBuilder.AppendLine($"共处理了{total}行，导入{success}行，错误{failure}行");
return(messageBuilder.ToString());
}
public static void WriteExcelTemplate(Stream stream, ExcelMap map)
{
IWorkbook workbook = new XSSFWorkbook();
IDataFormat dataformat = workbook.CreateDataFormat();
ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = dataformat.GetFormat("yyyy-MM-dd");
//【Tips】
// 1.yyyy 年份； yy 年份后两位
// 2.MM 月份零起始；M 月份非零起始; mmm[英文月份简写];mmmm[英文月份全称]
// 3.dd 日零起始；d 日非零起始
// 4.hh 小时零起始；h 小时非零起始[用于12小时制][12小时制必须在时间后面添加 AM/PM 或 上午/下午]
// 5.HH 小时零起始；H 小时非零起始[用于24小时制]
// 6.mm 分钟零起始；m 分钟非零起始
// 7.ss 秒数零起始；s 秒数非零起始
// 8.dddd 星期；ddd 星期缩写【英文】
// 9.aaaa 星期；aaa 星期缩写【中文】
ISheet sheet = null;
for (int i = 0; i <= map.SheetIndex; i++)
{
sheet = workbook.CreateSheet($"Sheet{i + 1}");
}
IRow header = null;
IRow data;
if (map.WithHeader)
{
header = sheet.CreateRow(0);
data = sheet.CreateRow(1);
}
else
{
data = sheet.CreateRow(0);
}
int j = 0;
foreach (var col in map.Properties)
{
if (header != null)
{
header.CreateCell(j).SetCellValue(col.ColumnIndex);
if (map.IsTypeOf(typeof(Entity.Project)))
{
SetProjectPropertyCell(data.CreateCell(j), col, dateStyle);
}
else if (map.IsTypeOf(typeof(Entity.Employee)))
{
SetStaffPropertyCell(data.CreateCell(j), col, dateStyle);
}
else
{
throw new Exception("not supported importing type.");
}
}
else
{
if (map.IsTypeOf(typeof(Entity.Project)))
{
SetProjectPropertyCell(data.CreateCell(
Convert.ToInt32(col.ColumnIndex)), col, dateStyle);
}
else if (map.IsTypeOf(typeof(Entity.Employee)))
{
SetStaffPropertyCell(data.CreateCell(
Convert.ToInt32(col.ColumnIndex)), col, dateStyle);
}
else
{
throw new Exception("not supported importing type.");
}
}
j++;
}
workbook.Write(stream);
}
