/// <summary>
///
/// </summary>
/// <param name="savePath"></param>
/// <param name="mbPath"></param>
/// <param name="listJob"></param>
/// <param name="className"></param>
/// <param name="startRow">从第几行开始写入</param>
/// <param name="startCell">从第几列开始写入</param>
/// <param name="note"></param>
/// <returns></returns>
public static bool ToMbExcel(string savePath, string mbPath, IList listJob, string className, int startRow, int startCell, string note = "", string HeadName = "", int rowHeight = 20)
{
try
{
DataTable table = ExcelExtHelper.ToDataTable(listJob, className, note);
Dictionary <string, string> excelDataMap = ReadXml(className, false, note);
IWorkbook workbook;
try
{
using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(file);//创建对应文件EXCEL2003
}
}
catch (Exception ex)
{
using (FileStream file = new FileStream(mbPath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(file);//创建对应文件EXCEL2007
}
}
ISheet sheet = workbook.GetSheetAt(0);
if (HeadName != "")
{
IRow rowhead = sheet.GetRow(0);
ICell cellhead = rowhead.GetCell(0);
cellhead.SetCellValue(HeadName);//写入表头
}
for (int i = 0; i < table.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + startRow - 1);
row.HeightInPoints = rowHeight; //行高
int cellIndex = startCell; //开始列索引
foreach (var item in excelDataMap)
{
string columsName = item.Value;
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型
{
string value = table.Rows[i][columsName].ToString();
if (value != "")
{
cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));//循环往第二行的单元格中添加数据
}
//cell.CellStyle = dateStyle;
}
else
{
cell.SetCellValue(table.Rows[i][columsName].ToString());//循环往第二行的单元格中添加数据
//cell.CellStyle = stylerow;
}
cellIndex++;
}
}
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(savePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
}
catch (Exception)
{
return(false);
}
return(true);
}
/// <summary>
/// DataTable转List
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static AjaxMsgModel ConvertToList(DataTable dts, int rowIdex, AjaxMsgModel amm)
{
//错误集合
List <TableToListErrorModel> errorList = new List <TableToListErrorModel>();
// 定义集合
List <T> ts = new List <T>();
// 获得此模型的类型
Type type = typeof(T);
DataTable dt = ExcelExtHelper.ReplaceColumnsName(dts, type.Name);
if (dt == null)
{
amm.Statu = AjaxStatu.err;
amm.Msg = "导入的文件模板不正确";
errorList.Add(
new TableToListErrorModel()
{
rowIndex = rowIdex,
columnIndex = 0,
errorInfo = "导入的文件模板不正确",
errorCause = "导入的文件模板不正确"
});
amm.Data = errorList;// ObjToJson.GetToJson(errorList);//转为JSON
return(amm);
}
//定义一个临时变量
string tempName = string.Empty;
//遍历DataTable中所有的数据行
foreach (DataRow dr in dt.Rows)
{
rowIdex++;//计算行
T t = new T();
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
//遍历该对象的所有属性
foreach (PropertyInfo pi in propertys)
{
Type pt = pi.PropertyType;
tempName = pi.Name;//将属性名称赋值给临时变量
//检查DataTable是否包含此列（列名==对象的属性名）
if (dt.Columns.Contains(tempName))
{
// 判断此属性是否有Setter
if (!pi.CanWrite)
{
continue; //该属性不可写，直接跳出
}
//取值
object value = dr[tempName];
//判断
if (pt.Equals(value.GetType()))
{
//如果非空，则赋给对象的属性
if (value != DBNull.Value)
{
try
{
pi.SetValue(t, value, null);
}
catch (Exception)
{
amm.Statu = AjaxStatu.err;
errorList.Add(
new TableToListErrorModel()
{
rowIndex = rowIdex,
columnIndex = dt.Columns.IndexOf(tempName) + 1,
errorInfo = "内容过多",
errorCause = "内容过多"
});
}
}
}
else
{
string fullName = pt.FullName;
if (fullName.Contains("System.String"))//文本型
{
//如果非空，则赋给对象的属性
if (value != DBNull.Value)
{
try
{
pi.SetValue(t, value.ToString(), null);
}
catch (Exception)
{
amm.Statu = AjaxStatu.err;
errorList.Add(
new TableToListErrorModel()
{
rowIndex = rowIdex,
columnIndex = dt.Columns.IndexOf(tempName) + 1,
errorInfo = "内容过多",
errorCause = "内容过多"
});
}
}
}
if (fullName.Contains("System.Int16"))//数字型
{
//如果非空，则赋给对象的属性
if (value != DBNull.Value)
{
try
{
if (!fullName.Contains("System.Nullable") || value != "")//如果是必填项或不为空时
{
value = Convert.ToInt16(value);
pi.SetValue(t, value, null);
}
}
catch (Exception)
{
amm.Statu = AjaxStatu.err;
//数字转换失败
errorList.Add(
new TableToListErrorModel()
{
rowIndex = rowIdex,
columnIndex = dt.Columns.IndexOf(tempName) + 1,
errorInfo = "数字转换失败",
errorCause = "需要数字类型"
});
}
}
}
if (fullName.Contains("System.Int32"))//数字型
{
//如果非空，则赋给对象的属性
if (value != DBNull.Value)
{
try
{
if (!fullName.Contains("System.Nullable") || value != "")//如果是必填项或不为空时
{
value = Convert.ToInt32(value);
pi.SetValue(t, value, null);
}
}
catch (Exception)
{
amm.Statu = AjaxStatu.err;
//数字转换失败
errorList.Add(
new TableToListErrorModel()
{
rowIndex = rowIdex,
columnIndex = dt.Columns.IndexOf(tempName) + 1,
errorInfo = "数字转换失败",
errorCause = "需要数字类型"
});
}
}
}
if (fullName.Contains("System.Decimal"))//数字型
{
//如果非空，则赋给对象的属性
if (value != DBNull.Value)
{
try
{
if (!fullName.Contains("System.Nullable") || value != "")//如果是必填项或不为空时
{
value = Convert.ToDecimal(value);
pi.SetValue(t, value, null);
}
}
catch (Exception)
{
amm.Statu = AjaxStatu.err;
//数字转换失败
errorList.Add(
new TableToListErrorModel()
{
rowIndex = rowIdex,
columnIndex = dt.Columns.IndexOf(tempName) + 1,
errorInfo = "数字转换失败",
errorCause = "需要数字类型"
});
}
}
}
if (fullName.Contains("System.DateTime"))//日期型
{
//如果非空，则赋给对象的属性
if (value != DBNull.Value)
{
try
{
if (!fullName.Contains("System.Nullable") || value != "")//如果是必填项或不为空时
{
value = Convert.ToDateTime(value.ToString());
pi.SetValue(t, value, null);
}
}
catch (Exception)
{
amm.Statu = AjaxStatu.err;
//日期转换失败
//excel日期列必须设置为日期格式才能正确转换
errorList.Add(
new TableToListErrorModel()
{
rowIndex = rowIdex,
columnIndex = dt.Columns.IndexOf(tempName) + 1,
errorInfo = "日期转换失败",
errorCause = "需要日期类型，请设置该单元格格式为日期型或算定义为yyyy-MM-dd HH:mm:ss"
});
}
}
}
}
}
}
//对象添加到泛型集合中
ts.Add(t);
}
if (errorList.Count > 0)//如果有错误
{
amm.Statu = AjaxStatu.err;
amm.Data = errorList;// ObjToJson.GetToJson(errorList);//转为JSON
}
else
{
amm.Statu = AjaxStatu.ok;
amm.Data = ts;
}
return(amm);
}