public void TestMethod1()
{
var docPath = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
var filePath = Path.Combine(docPath, "Classroom Grid.xls");
// XSSFWorkbook constructor close filestream so need to open filestream again to read
HSSFWorkbook dest = new HSSFWorkbook();
HSSFWorkbook src;
using (var fileStream = File.OpenRead(filePath))
{
src = new HSSFWorkbook(fileStream);
}
ISheet srcSheet = src.GetSheetAt(0);
ISheet destSheet = dest.CreateSheet();
NpoiUtil.copySheets(destSheet, srcSheet, true);
var outPath = Path.Combine(docPath, "test.xls");
using (var fileStream = File.Open(outPath, FileMode.Create, FileAccess.Write))
{
dest.Write(fileStream);
}
src.Close();
dest.Close();
}
public byte[] ExportList(Bootstrap.BootstrapParams bootstrap)
{
bootstrap.sort = "创建时间";
bootstrap.order = "desc";
var query = _client.Queryable <Wms_material, Sys_dict, Sys_dict, Wms_storagerack, Wms_reservoirarea, Wms_warehouse, Sys_user, Sys_user>
((s, t, ut, r, k, w, c, u) => new object[] {
JoinType.Left, s.MaterialType == t.DictId,
JoinType.Left, s.Unit == ut.DictId,
JoinType.Left, s.StoragerackId == r.StorageRackId,
JoinType.Left, s.ReservoirAreaId == k.ReservoirAreaId,
JoinType.Left, s.WarehouseId == w.WarehouseId,
JoinType.Left, s.CreateBy == c.UserId,
JoinType.Left, s.ModifiedBy == u.UserId,
})
.Where((s, t, ut, r, k, w, c, u) => s.IsDel == 1 && t.IsDel == 1 && ut.IsDel == 1 && r.IsDel == 1 && k.IsDel == 1 && w.IsDel == 1)
.Select((s, t, ut, r, k, w, c, u) => new
{
物料编号 = s.MaterialNo,
物料名称 = s.MaterialName,
单位类别 = ut.DictName,
物料分类 = t.DictName,
安全库存 = s.Qty,
效期 = s.ExpiryDate,
货架编号 = r.StorageRackNo,
货架名称 = r.StorageRackName,
库区编号 = k.ReservoirAreaNo,
库区名称 = k.ReservoirAreaName,
仓库编号 = w.WarehouseNo,
仓库名称 = w.WarehouseName,
备注 = s.Remark,
创建人 = c.UserNickname,
创建时间 = s.CreateDate,
修改人 = u.UserNickname,
修改时间 = s.ModifiedDate
}).MergeTable();
if (!bootstrap.datemin.IsEmpty() && !bootstrap.datemax.IsEmpty())
{
query.Where(s => s.创建时间 > bootstrap.datemin.ToDateTimeB() && s.创建时间 <= bootstrap.datemax.ToDateTimeE());
}
if (bootstrap.order.Equals("desc", StringComparison.OrdinalIgnoreCase))
{
query.OrderBy($"MergeTable.{bootstrap.sort} desc");
}
else
{
query.OrderBy($"MergeTable.{bootstrap.sort} asc");
}
var list = query.ToList();
var buffer = NpoiUtil.Export(list, ExcelVersion.V2007);
return(buffer);
}
public IActionResult ImportExcel(IFormFile file)
{
if (file == null || file.Length <= 0)
{
return(BootJsonH((false, PubConst.File1)));
}
string fileExt = Path.GetExtension(file.FileName).ToLower();
if (!NpoiUtil.excel.Contains(fileExt))
{
return(BootJsonH((false, PubConst.File2)));
}
var filepath = Path.Combine(WebRoot, "upload", PubId.GetUuid()) + fileExt;
//1 直接通过流
return(DelegateUtil.TryExecute <IActionResult>(() =>
{
using (var st = new MemoryStream())
{
file.CopyTo(st);
var dt = NpoiUtil.Import(st, fileExt);
var json = _customerServices.Import(dt, UserDtoCache.UserId).JilToJson();
return Content(json);
}
}, BootJsonH((false, PubConst.File3))
));
//using (var st = new MemoryStream())
//{
// file.CopyTo(st);
// var dt = NpoiUtil.Import(st, fileExt);
// var json = _customerServices.Import(dt, UserDtoCache.UserId).JilToJson();
// return Content(json);
//}
//2 先上传到服务器，然后在读取
//using (var stream = new FileStream(filepath, FileMode.CreateNew))
//{
// file.CopyTo(stream);
//}
//var dt = NpoiUtil.Import(filepath);
//FileUtil.Delete(filepath);
//var json = _customerServices.Import(dt, UserDtoCache.UserId).JilToJson();
//return Content(json);
}
public async Task <RouteData> ImportList(IFormFile file)
{
try
{
MaterialImportData[] importList = null;
using (Stream stream = file.OpenReadStream())
{
importList = NpoiUtil.Import <MaterialImportData>(
stream, file.FileName.EndsWith("xlsx") ? ExcelVersion.V2007 : ExcelVersion.V2003);
stream.Close();
stream.Dispose();
}
List <Sys_dict> typeDicts = await _client.Queryable <Sys_dict>().Where(x => x.DictType == PubDictType.material.ToByte().ToString()).ToListAsync();
List <Sys_dict> unitDicts = await _client.Queryable <Sys_dict>().Where(x => x.DictType == PubDictType.unit.ToByte().ToString()).ToListAsync();
foreach (MaterialImportData importData in importList)
{
Wms_material material = null;
if (string.IsNullOrWhiteSpace(importData.MaterialOnlyId))
{
material = await _client.Queryable <Wms_material>().FirstAsync(x => x.MaterialNo == importData.MaterialNo);
}
else
{
material = await _client.Queryable <Wms_material>().FirstAsync(x => x.MaterialOnlyId == importData.MaterialOnlyId);
}
if (material == null)
{
Sys_dict typeDict = typeDicts.FirstOrDefault(x => x.DictName == importData.MaterialType);
if (typeDict == null)
{
return(RouteData <Wms_material> .From(PubMessages.E1001_SUPPLIESTYPE_NOTFOUND));
}
else if (typeDict.WarehouseId == null)
{
return(RouteData <Wms_material> .From(PubMessages.E1002_SUPPLIESTYPE_WAREHOUSEID_NOTSET));
}
Sys_dict unitDict = unitDicts.FirstOrDefault(x => x.DictName == importData.Unit);
if (unitDict == null)
{
return(RouteData <Wms_material> .From(PubMessages.E1003_UNIT_NOTFOUND));
}
long warehouseId = typeDict.WarehouseId.Value;
material = new Wms_material()
{
MaterialId = PubId.SnowflakeId,
MaterialOnlyId = importData.MaterialOnlyId ?? "",
MaterialNo = importData.MaterialNo ?? "",
MaterialName = importData.MaterialName,
MaterialType = typeDict.DictId,
WarehouseId = warehouseId,
Unit = unitDict.DictId,
};
this.Insert(material);
}
else
{
string unit = unitDicts.FirstOrDefault(x => x.DictId == material.Unit).DictName;
string type = typeDicts.FirstOrDefault(x => x.DictId == material.MaterialType).DictName;
if (material.MaterialNo != importData.MaterialNo)
{
return(RouteData.From(PubMessages.E4102_MATERIAL_IMPORT_EXIST_NOTMATCH,
$",物料编号不匹配,已保存的值为[{material.MaterialNo}],导入值为[{importData.MaterialNo}]"));
}
else if (material.MaterialOnlyId != importData.MaterialOnlyId)
{
return(RouteData.From(PubMessages.E4102_MATERIAL_IMPORT_EXIST_NOTMATCH,
$",物料唯一Id不匹配,已保存的值为[{material.MaterialOnlyId}],导入值为[{importData.MaterialOnlyId}]"));
}
else if (material.MaterialName != importData.MaterialName)
{
return(RouteData.From(PubMessages.E4102_MATERIAL_IMPORT_EXIST_NOTMATCH,
$",物料({importData.MaterialNo},{importData.MaterialOnlyId})名称不匹配,已保存的值为[{material.MaterialName}],导入值为[{importData.MaterialName}]"));
}
else if (type != importData.MaterialType)
{
return(RouteData.From(PubMessages.E4102_MATERIAL_IMPORT_EXIST_NOTMATCH,
$",物料({importData.MaterialNo},{importData.MaterialOnlyId})单位不匹配,已保存的值为[{type}],导入值为[{importData.MaterialType}]"));
}
}
}
return(RouteData.From(PubMessages.I4100_MATERIAL_IMPORT_SCCUESS));
}
catch
{
return(RouteData.From(PubMessages.E4100_MATERIAL_IMPORT_FAIL));
}
}
