public ActionResult ToInventoryReportExcel()
{
string searchKey = WebUtil.GetFormValue<string>("SearchKey", string.Empty);
string change = WebUtil.GetFormValue<string>("Change", string.Empty);
string beginTime = WebUtil.GetFormValue<string>("beginTime", string.Empty);
string endTime = WebUtil.GetFormValue<string>("endTime", string.Empty);
string storageNum = this.DefaultStore;
InventoryProvider provider = new InventoryProvider();
InventoryBookEntity entity = new InventoryBookEntity();
if (!searchKey.IsEmpty())
{
entity.Begin<InventoryBookEntity>()
.Where<InventoryBookEntity>("ProductName", ECondition.Like, "%" + searchKey + "%")
.Or<InventoryBookEntity>("ProductNum", ECondition.Like, "%" + searchKey + "%")
.Or<InventoryBookEntity>("BarCode", ECondition.Like, "%" + searchKey + "%")
.End<InventoryBookEntity>();
}
if (!storageNum.IsEmpty())
{
entity.Where("StoreNum", ECondition.Eth, storageNum);
}
if (!change.IsEmpty())
{
entity.Where("Type", ECondition.Eth, change);
}
PageInfo pageInfo = new PageInfo() { PageIndex = 1, PageSize = Int32.MaxValue };
List<InventoryBookEntity> listResult = provider.GetList(entity, ref pageInfo);
LocationProvider locaProvider = new LocationProvider();
foreach (InventoryBookEntity item in listResult)
{
LocationEntity locaEntity1 = locaProvider.GetSingleByNum(item.FromLocalNum);
if (locaEntity1.IsNotNull())
{
item.FromLocalName = locaEntity1.LocalName;
}
else
{
item.FromLocalName = string.Empty;
}
LocationEntity locaEntity2 = locaProvider.GetSingleByNum(item.ToLocalNum);
if (locaEntity2.IsNotNull())
{
item.ToLocalName = locaEntity2.LocalName;
}
else
{
item.ToLocalName = string.Empty;
}
}
if (!listResult.IsNullOrEmpty())
{
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Action<Cell> action = (Cell cellItem) =>
{
cellItem.Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
cellItem.Style.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
cellItem.Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
cellItem.Style.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
cellItem.Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
cellItem.Style.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
cellItem.Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
cellItem.Style.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
};
sheet.Name = "台帐报表";
sheet.Cells.Merge(0, 0, 1, 11);
//设置高度
Cells cells = sheet.Cells;
cells.SetRowHeight(0, 30);
for (int i = 0; i < 11; i++)
{
cells.SetColumnWidth(i, 15);
}
Cell cell = sheet.Cells[0, 0];
cell.PutValue("台帐报表");
cell.Style.HorizontalAlignment = TextAlignmentType.Center;
cell.Style.Font.Color = System.Drawing.Color.Blue;
cell.Style.Font.Size = 12;
cell.Style.Font.IsBold = true;
action(cell);
int rowIndex = 1;
sheet.Cells.SetRowHeight(1, 27);
sheet.Cells[rowIndex, 0].PutValue("序号");
sheet.Cells[rowIndex, 1].PutValue("产品编号");
sheet.Cells[rowIndex, 2].PutValue("产品条码");
sheet.Cells[rowIndex, 3].PutValue("产品名称");
sheet.Cells[rowIndex, 4].PutValue("数量");
sheet.Cells[rowIndex, 5].PutValue("台帐类型");
sheet.Cells[rowIndex, 6].PutValue("关联订单号");
sheet.Cells[rowIndex, 7].PutValue("原库位");
sheet.Cells[rowIndex, 8].PutValue("目标库位");
sheet.Cells[rowIndex, 9].PutValue("创建时间");
sheet.Cells[rowIndex, 10].PutValue("操作人");
for (int i = 0; i < 11; i++)
{
sheet.Cells[rowIndex, i].Style.Font.IsBold = true;
action(sheet.Cells[rowIndex, i]);
}
rowIndex++;
Dictionary<string, int> dic = new Dictionary<string, int>();
string tempProductNum = listResult[0].ProductNum;
int index = 0;
int tempIndex = 1;
foreach (InventoryBookEntity item in listResult)
{
if (item.ProductNum == tempProductNum)
{
index++;
}
if (item.ProductNum != tempProductNum)
{
dic[tempProductNum] = index;
index = 1;
}
if (listResult.Count == tempIndex)
{
Dictionary<string, int>.KeyCollection keys = dic.Keys;
if (!keys.Exists(a => a == item.ProductNum))
{
dic[item.ProductNum] = index;
}
}
tempProductNum = item.ProductNum;
tempIndex++;
}
index = 0;
int number = 1;
foreach (InventoryBookEntity t in listResult)
{
sheet.Cells.SetRowHeight(rowIndex, 25);
sheet.Cells[rowIndex, 0].PutValue(number);
if (index == 0)
{
index = dic[t.ProductNum];
sheet.Cells[rowIndex, 1].PutValue(t.ProductNum);
sheet.Cells[rowIndex, 2].PutValue(t.BarCode);
sheet.Cells[rowIndex, 3].PutValue(t.ProductName);
for (int i = 1; i < 4; i++)
{
sheet.Cells.Merge(rowIndex, i, index, 1);
sheet.Cells[rowIndex, i].Style.HorizontalAlignment = TextAlignmentType.Center;
}
}
sheet.Cells[rowIndex, 4].PutValue(t.Num);
sheet.Cells[rowIndex, 5].PutValue(EnumHelper.GetEnumDesc<EChange>(t.Type));
sheet.Cells[rowIndex, 6].PutValue(t.ContactOrder);
sheet.Cells[rowIndex, 7].PutValue(t.FromLocalName);
sheet.Cells[rowIndex, 8].PutValue(t.ToLocalName);
sheet.Cells[rowIndex, 9].PutValue(t.CreateTime.ToString("yyyy-MM-dd"));
sheet.Cells[rowIndex, 10].PutValue(t.UserName);
for (int i = 0; i < 11; i++)
{
//sheet.Cells[rowIndex, i].Style.Font.IsBold = true;
sheet.Cells[rowIndex, i].Style.Font.Size = 13;
action(sheet.Cells[rowIndex, i]);
}
number++;
rowIndex++;
index--;
}
string filePath = Server.MapPath("~/UploadFiles/");
if (!System.IO.Directory.Exists(filePath))
{
System.IO.Directory.CreateDirectory(filePath);
}
string filename = string.Format("台帐报表{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));
book.Save(filePath + filename);
this.ReturnJson.AddProperty("Path", ("/UploadFiles/" + filename).Escape());
this.ReturnJson.AddProperty("Path", ("/UploadFiles/" + filename).Escape());
}
else
{
this.ReturnJson.AddProperty("d", "无数据导出!");
}
return Content(this.ReturnJson.ToString());
}
public ActionResult InventoryList()
{
string searchKey = WebUtil.GetFormValue<string>("SearchKey", string.Empty);
string change = WebUtil.GetFormValue<string>("Change", string.Empty);
int pageIndex = WebUtil.GetFormValue<int>("pageIndex", 0);
int pageSize = WebUtil.GetFormValue<int>("pageSize", 0);
string beginTime = WebUtil.GetFormValue<string>("beginTime", string.Empty);
string endTime = WebUtil.GetFormValue<string>("endTime", string.Empty);
string storageNum = this.DefaultStore;
InventoryProvider provider = new InventoryProvider();
InventoryBookEntity entity = new InventoryBookEntity();
if (!searchKey.IsEmpty())
{
entity.Begin<InventoryBookEntity>()
.Where<InventoryBookEntity>("ProductName", ECondition.Like, "%" + searchKey + "%")
.Or<InventoryBookEntity>("ProductNum", ECondition.Like, "%" + searchKey + "%")
.Or<InventoryBookEntity>("BarCode", ECondition.Like, "%" + searchKey + "%")
.End<InventoryBookEntity>();
}
if (!storageNum.IsEmpty())
{
entity.Where("StoreNum", ECondition.Eth, storageNum);
}
if (!change.IsEmpty())
{
entity.Where("Type", ECondition.Eth, change);
}
PageInfo pageInfo = new PageInfo() { PageIndex = pageIndex, PageSize = pageSize };
List<InventoryBookEntity> listResult = provider.GetList(entity, ref pageInfo);
LocationProvider locaProvider = new LocationProvider();
foreach (InventoryBookEntity item in listResult)
{
LocationEntity locaEntity1 = locaProvider.GetSingleByNum(item.FromLocalNum);
if (locaEntity1.IsNotNull())
{
item.FromLocalName = locaEntity1.LocalName;
}
else
{
item.FromLocalName = string.Empty;
}
LocationEntity locaEntity2 = locaProvider.GetSingleByNum(item.ToLocalNum);
if (locaEntity2.IsNotNull())
{
item.ToLocalName = locaEntity2.LocalName;
}
else
{
item.ToLocalName = string.Empty;
}
}
string json = ConvertJson.ListToJson<InventoryBookEntity>(listResult, "List");
this.ReturnJson.AddProperty("Data", new JsonObject(json));
this.ReturnJson.AddProperty("RowCount", pageInfo.RowCount);
return Content(this.ReturnJson.ToString());
}