/// <summary>
/// 保存实体数据.
/// </summary>
public void Save(AssetCheckPlanModel model)
{
if (model.PlanId == 0)
{
DBProvider.AssetCheckPlanDAO.Add(model);
}
else
{
DBProvider.AssetCheckPlanDAO.Update(model);
}
}
/// <summary>
/// 更新一条数据.
/// </summary>
public void Update(AssetCheckPlanModel model)
{
DBProvider.dbMapper.Update("AssetCheck_Plan.Update", model);
}
/// <summary>
/// 增加一条数据.
/// </summary>
public void Add(AssetCheckPlanModel model)
{
DBProvider.dbMapper.Insert("AssetCheck_Plan.Insert", model);
}
/// <summary>
/// 根据盘点计划ID导出盘盈仪器数据
/// </summary>
/// <param name="barCode"></param>
/// <returns></returns>
public string CreateExcelForOverAge(int planId)
{
//盘点计划属性
AssetCheckPlanModel assetCheckPlanModel = ServiceProvider.AssetCheckPlanService.GetById(planId);
IList <AssetCheckPlanDetailModel> list = ServiceProvider.AssetCheckPlanDetailService.GetByPlanIdAndStatus(planId, Constants.AssetsCheckStatus.盘盈.GetHashCode());
ISheet sheet = null;
IWorkbook hssfworkbook = null;
//读取Excel模板的路径
string templatepath = ToolsLib.Utility.CommonUtils.GetPhysicsPath("/App_Data/盘点表及盘盈表模板.xls");
string newFile = ToolsLib.Utility.CommonUtils.GetPhysicsPath("/tempFile/" + Guid.NewGuid().ToString() + ".xls");
if (IsExistSheet(ref sheet, ref hssfworkbook, templatepath, "盘盈表"))
{
//强制要求Excel在打开时重新计算的属性
sheet.ForceFormulaRecalculation = true;
//获取当前工作表的索引
int sheetIndex = hssfworkbook.GetSheetIndex(sheet);
int sheetNumbers = hssfworkbook.NumberOfSheets;
//删除多余的工作表
for (int i = sheetNumbers - 1; i >= 0; i--)
{
if (i != sheetIndex)
{
hssfworkbook.RemoveSheetAt(i);
}
}
//单元格样式
ICellStyle style = hssfworkbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
//居中
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
style.WrapText = true;
sheet.GetRow(0).GetCell(0).SetCellValue(assetCheckPlanModel.PlanName);
int originalRowData = 2;
int rowIndex = 0;
foreach (AssetCheckPlanDetailModel model in list)
{
if (sheet.GetRow(originalRowData + rowIndex) == null)
{
sheet.CreateRow(originalRowData + rowIndex);
}
//sheet.SetColumnWidth(originalRowData + rowIndex, 5*256);
sheet.GetRow(originalRowData + rowIndex).Height = 600;
sheet.GetRow(originalRowData + rowIndex).CreateCell(0).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(0).SetCellValue(rowIndex + 1);//行数
sheet.GetRow(originalRowData + rowIndex).CreateCell(1).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(1).SetCellValue(model.InstrumentName);//资产名称
sheet.GetRow(originalRowData + rowIndex).CreateCell(2).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(2).SetCellValue(model.Specification);//规格型号
sheet.GetRow(originalRowData + rowIndex).CreateCell(3).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(3).SetCellValue(model.Manufacturer);//生产厂家
sheet.GetRow(originalRowData + rowIndex).CreateCell(4).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(4).SetCellValue(model.SerialNo);//出厂编号
sheet.GetRow(originalRowData + rowIndex).CreateCell(5).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(5).SetCellValue(model.BelongDepart);//单位
sheet.GetRow(originalRowData + rowIndex).CreateCell(6).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(6).SetCellValue("");//数量
sheet.GetRow(originalRowData + rowIndex).CreateCell(7).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(7).SetCellValue("");//存放地点
sheet.GetRow(originalRowData + rowIndex).CreateCell(8).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(8).SetCellValue(model.Remark);//备注
sheet.GetRow(originalRowData + rowIndex).CreateCell(9).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(9).SetCellValue(model.CreateDate.ToString("yyyy-MM-dd"));//最近盘点时间
sheet.GetRow(originalRowData + rowIndex).CreateCell(10).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(10).SetCellValue(model.CreateUser);//最近盘点人
rowIndex++;
}
FileStream fs = new FileStream(newFile, FileMode.Create, FileAccess.Write);
//写入内存流
hssfworkbook.Write(fs);
fs.Close();
fs.Dispose();
newFile = newFile + "," + assetCheckPlanModel.PlanName;
}
else
{
newFile = "";
}
return(newFile);
}
/// <summary>
/// 根据盘点计划ID导出仪器数据
/// </summary>
/// <param name="planId"></param>
/// <param name="where"></param>
/// <returns></returns>
public string CreateExcel(int planId, string where)
{
//盘点计划属性
AssetCheckPlanModel assetCheckPlanModel = ServiceProvider.AssetCheckPlanService.GetById(planId);
IList <AssetCheckPlanDetailModel> listModel = DBProvider.AssetCheckPlanDetailDAO.GetByWhere(where);
//获取所有仪器的ID
IList <int> listId = new List <int>();
foreach (AssetCheckPlanDetailModel a in listModel)
{
listId.Add(a.InstrumentId);
}
IList <InstrumentModel> instrumentList = ServiceProvider.InstrumentService.GetByIds(listId);
ISheet sheet = null;
IWorkbook hssfworkbook = null;
//读取Excel模板的路径
string templatepath = ToolsLib.Utility.CommonUtils.GetPhysicsPath("/App_Data/盘点表及盘盈表模板.xls");
string newFile = ToolsLib.Utility.CommonUtils.GetPhysicsPath("/tempFile/" + Guid.NewGuid().ToString() + ".xls");
if (IsExistSheet(ref sheet, ref hssfworkbook, templatepath, "盘点表"))
{
//强制要求Excel在打开时重新计算的属性
sheet.ForceFormulaRecalculation = true;
//获取当前工作表的索引
int sheetIndex = hssfworkbook.GetSheetIndex(sheet);
int sheetNumbers = hssfworkbook.NumberOfSheets;
//删除多余的工作表
for (int i = sheetNumbers - 1; i >= 0; i--)
{
if (i != sheetIndex)
{
hssfworkbook.RemoveSheetAt(i);
}
}
//单元格样式
ICellStyle style = hssfworkbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
//居中
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
style.WrapText = true;
sheet.GetRow(0).GetCell(0).SetCellValue(assetCheckPlanModel.PlanName);
int originalRowData = 4;
int rowIndex = 0;
if (listModel.Count > 15)
{
sheet.ShiftRows(19, 23, listModel.Count - 15, true, true);
}
foreach (AssetCheckPlanDetailModel model in listModel)
{
InstrumentModel instrumentModel = instrumentList.SingleOrDefault(t => t.InstrumentId == Convert.ToInt32(model.InstrumentId));
if (sheet.GetRow(originalRowData + rowIndex) == null)
{
sheet.CreateRow(originalRowData + rowIndex);
}
sheet.GetRow(originalRowData + rowIndex).Height = 600;
sheet.GetRow(originalRowData + rowIndex).CreateCell(0).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(0).SetCellValue(rowIndex + 1);//行数
sheet.GetRow(originalRowData + rowIndex).CreateCell(1).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(1).SetCellValue(model.ManageNo);//管理编号
sheet.GetRow(originalRowData + rowIndex).CreateCell(2).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(2).SetCellValue(model.AssetsNo);//资产编号
sheet.GetRow(originalRowData + rowIndex).CreateCell(3).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(3).SetCellValue(model.InstrumentName);//资产名称
sheet.GetRow(originalRowData + rowIndex).CreateCell(4).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(4).SetCellValue(model.Specification);//规格型号
sheet.GetRow(originalRowData + rowIndex).CreateCell(5).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(5).SetCellValue(model.SerialNo);//出厂编号
sheet.GetRow(originalRowData + rowIndex).CreateCell(6).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(6).SetCellValue(instrumentModel == null ? "" : instrumentModel.LeaderName);//保管人
sheet.GetRow(originalRowData + rowIndex).CreateCell(7).CellStyle = style;
if (model.Statuse == Constants.AssetsCheckStatus.已盘点.GetHashCode())
{
sheet.GetRow(originalRowData + rowIndex).GetCell(7).SetCellValue("√");//实物是否存在
}
else
{
sheet.GetRow(originalRowData + rowIndex).GetCell(7).SetCellValue("×");
}
sheet.GetRow(originalRowData + rowIndex).CreateCell(8).CellStyle = style;
if (model.IsRightAddress == Constants.AssetsIsRightAddress.一致.GetHashCode())
{
sheet.GetRow(originalRowData + rowIndex).GetCell(8).SetCellValue("√");//地点是否一致
}
else
{
sheet.GetRow(originalRowData + rowIndex).GetCell(8).SetCellValue("×");//地点是否一致
}
sheet.GetRow(originalRowData + rowIndex).CreateCell(9).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(9).SetCellValue(model.Remark);//备注
sheet.GetRow(originalRowData + rowIndex).CreateCell(10).CellStyle = style;
if (model.CheckDate == DateTime.MinValue)
{
sheet.GetRow(originalRowData + rowIndex).GetCell(10).SetCellValue("");//最近盘点时间，没有等于空
}
else
{
sheet.GetRow(originalRowData + rowIndex).GetCell(10).SetCellValue(model.CheckDate.ToString("yyyy-MM-dd"));//最近盘点时间
}
sheet.GetRow(originalRowData + rowIndex).CreateCell(11).CellStyle = style;
sheet.GetRow(originalRowData + rowIndex).GetCell(11).SetCellValue(model.Checkor);//最近盘点人
rowIndex++;
}
FileStream fs = new FileStream(newFile, FileMode.Create, FileAccess.Write);
//写入内存流
hssfworkbook.Write(fs);
fs.Close();
fs.Dispose();
newFile = newFile + "," + assetCheckPlanModel.PlanName;
}
else
{
newFile = "";
}
return(newFile);
}
