public T GetProductByDataRow <T>(DataRow dr, Dictionary <string, ExcelModel> listColumn, T entity, Dictionary <string, ExcelFormatter> formatters, string clientID = "")
{
foreach (var property in entity.GetType().GetProperties())
{
var propertyname = property.Name.ToLower();
var defaulvalue = listColumn.FirstOrDefault(q => q.Value.ImportColumn.ToLower() == propertyname);
if (default(KeyValuePair <string, ExcelModel>).Equals(defaulvalue))
{
defaulvalue = listColumn.FirstOrDefault(q => q.Value.ColumnName.ToLower() == propertyname);
}
if (!default(KeyValuePair <string, ExcelModel>).Equals(defaulvalue))
{
var propname = defaulvalue.Value.Title;
var drvalue = dr[propname] != null && dr[propname] != DBNull.Value ? dr[propname].ToString() : "";
if (formatters != null && formatters.Count > 0 && formatters.ContainsKey(defaulvalue.Value.ColumnName))
{
ExcelFormatter excelFormatter = formatters[defaulvalue.Value.ColumnName];
///图片获取必须在Excel转Datatable之前获取到 此处不处理
if (!excelFormatter.ColumnTrans.Equals(EnumColumnTrans.ConvertImportImage))
{
drvalue = FormatterCoulumn(drvalue, excelFormatter.ColumnTrans, clientID);
}
}
switch (defaulvalue.Value.DataType)
{
case "int":
property.SetValue(entity, string.IsNullOrEmpty(drvalue)?-1: drvalue == "是" ? 1 : drvalue == "否" ? 0 :
PaserByValueOrName(drvalue, dr[propname]),
null);
break;
case "string":
object[] objArray = property.GetCustomAttributes(false);
if (objArray.Length > 0)
{
if ((objArray[0] as Property).Value.ToLower() == "lower")
{
property.SetValue(entity,
string.IsNullOrEmpty(drvalue) ? "" : drvalue.ToString().ToLower().Replace("\"", "“"),
null);
}
else if ((objArray[0] as Property).Value.ToLower() == "upper")
{
property.SetValue(entity,
string.IsNullOrEmpty(drvalue) ? "" : drvalue.ToString().ToUpper().Replace("\"", "“"),
null);
}
}
else
{
property.SetValue(entity,
string.IsNullOrEmpty(drvalue) ? "" : drvalue.ToString().Replace("\"", "“"),
null);
}
break;
case "decimal":
property.SetValue(entity,
!string.IsNullOrEmpty(drvalue) ? Convert.ToDecimal(drvalue): 0,
null);
break;
case "datetime":
property.SetValue(entity,
!string.IsNullOrEmpty(drvalue) ? Convert.ToDateTime(drvalue) : DateTime.MinValue,
null);
break;
case "bool":
property.SetValue(entity,
!string.IsNullOrEmpty(drvalue) ? Convert.ToBoolean(drvalue) : false,
null);
break;
default:
property.SetValue(entity, drvalue, null);
break;
}
}
}
return(entity);;
}
/// <summary>
/// 导出Excel DataTable
/// </summary>
/// <param name="records">records必须都为DataTable</param>
/// <param name="formatter">Dictionary key:DataTable中的列明此处必须小写 value:EnumColumnTrans</param>
/// <returns></returns>
public byte[] Write(DataTable records, Dictionary <string, ExcelFormatter> formatter = null, string imgBasepath = "")
{
Stopwatch sw = new Stopwatch();
sw.Start();
var workBook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
var sheet = workBook.CreateSheet();
var headerRow = sheet.CreateRow(0);
var cellIndex = 0;
Color lightGrey = Color.FromArgb(221, 221, 221);
ICellStyle cstyle = workBook.CreateCellStyle();
cstyle.Alignment = HorizontalAlignment.Center;
cstyle.IsLocked = true;
// cstyle.FillForegroundColor = new XSSFColor(lightGrey).Indexed;
cstyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
foreach (var map in Maps)
{
var hcell = headerRow.CreateCell(cellIndex, CellType.String);
hcell.CellStyle = cstyle;
hcell.SetCellValue(map.Name);
cellIndex++;
}
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
var rowIndex = 1;
IDrawing patriarch = sheet.CreateDrawingPatriarch();
bool isimg = false;
foreach (DataRow record in records.Rows)
{
var dr = sheet.CreateRow(rowIndex);
for (int i = 0; i < Maps.Count; i++)
{
string drValue = record[Maps[i].Info.ToString()].ToString();
ICell cell = dr.CreateCell(i);
if (formatter.Any() && formatter.ContainsKey(Maps[i].Info.ToLower()) && formatter[Maps[i].Info.ToLower()] != null)
{
ExcelFormatter excelFormatter = formatter[Maps[i].Info.ToLower()];
if (!string.IsNullOrEmpty(drValue))
{
if (excelFormatter != null && excelFormatter.ColumnTrans == EnumColumnTrans.ConvertDownList)
{
cell.SetCellValue(drValue);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(excelFormatter.DropSource.Split(','));
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, i, i);
XSSFDataValidation dataValidate = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, regions);
sheet.AddValidationData(dataValidate);
}
else if (excelFormatter != null && excelFormatter.ColumnTrans == EnumColumnTrans.ConvertImage)
{
if (File.Exists(@"" + imgBasepath + drValue))
{
if (!isimg)
{
sheet.SetColumnWidth(i, 256 * 20);
isimg = true;
}
dr.HeightInPoints = 90;
byte[] bytes = System.IO.File.ReadAllBytes(@"" + imgBasepath + drValue);
int pictureIdx = workBook.AddPicture(bytes, XSSFWorkbook.PICTURE_TYPE_PNG);
IClientAnchor anchor = new XSSFClientAnchor(100, 50, 0, 0, i, rowIndex, i + 1,
rowIndex + 1);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize(0.3);
}
else
{
cell.SetCellValue("");
}
}
else
{
cell.SetCellValue(FormatterCoulumn(drValue, excelFormatter.ColumnTrans));
}
}
else
{
cell.SetCellValue(drValue);
}
}
else
{
switch (records.Columns[Maps[i].Info].DataType.ToString())
{
case "System.String": //字符串类型
cell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
cell.SetCellValue(dateV);
//cell.CellStyle =
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
cell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
cell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
cell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
cell.SetCellValue("");
break;
default:
cell.SetCellValue("");
break;
}
}
}
rowIndex++;
}
workBook.Write(ms);
byte[] buffer = ms.ToArray();
ms.Close();
sw.Stop();
return(buffer);
}
