private void WriteSheet(ISheet sheet, PlantSummaryDto sheetData)
{
int rowIndex = 0;
foreach (var item in sheetData.SectionList)
{
WriteSection(sheet, item, ref rowIndex);
}
}
public string GetExcel(DateTime beginTime, DateTime endTime, string orgCode)
{
ExcelStatistic exceler = new ExcelStatistic();
if (orgCode.Length == 8)
{
List <PlantSummaryDto> plantInfo = GetCompanySummary(beginTime, endTime, orgCode);
return(exceler.CreateCompanyStatisticExcel(plantInfo));
}
if (orgCode.Length == 12)
{
PlantSummaryDto plantInfo = GetPlantSummary(beginTime, endTime, orgCode);
return(exceler.CreateSingleSheetByPlant(plantInfo));
}
List <SectionSummaryDto> sectionInfo = GetSectionSummary(beginTime, endTime, orgCode);
return(exceler.CreateSingleSheetBySectionList(sectionInfo));
}
// 单个分厂
public string CreateSingleSheetByPlant(PlantSummaryDto excelData)
{
HSSFWorkbook workbook = new HSSFWorkbook();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "豫光";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "张承宇"; //填加xls文件作者信息
si.ApplicationName = "LIMS"; //填加xls文件创建程序信息
si.LastAuthor = "张承宇"; //填加xls文件最后保存者信息
si.Comments = "张承宇"; //填加xls文件作者信息
si.Title = "化验数据"; //填加xls文件标题信息
si.Subject = "化验数据"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ISheet sheet = workbook.CreateSheet(excelData.OrgName);
WriteSheet(sheet, excelData);
string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string filePath = DirPath + fileName;
try
{
using (FileStream fs = new FileStream(filePath, FileMode.CreateNew, FileAccess.Write))
{
workbook.Write(fs);
}
return(fileName);
}
catch
{
return(null);
}
}
// 获取单个分厂的数据
public PlantSummaryDto GetPlantSummary(DateTime beginTime, DateTime endTime, string orgCode)
{
using (UnitOfWorkManager.Current.DisableFilter(AbpDataFilters.SoftDelete))
{
// 获取查询的机构下所有的化验模板
var tplList = _tplRepostitory.GetAll().Where(x => x.OrgCode.StartsWith(orgCode)).ToList();
var tplIds = tplList.Select(x => x.Id).ToList();
// 获取模板下的所有样品
var tplSpecList = _tplSpecRepostitory.GetAll().Where(x => tplIds.Contains(x.TplId)).ToList();
// 获取所有的输入信息
var typeInList = _typeInRepostitory.GetAll().Where(x => tplIds.Contains(x.TplId) && x.SignTm >= beginTime && x.SignTm <= endTime).ToList();
var typeInIds = typeInList.Select(x => x.Id).ToList();
var typeInItemList = this._typeInItemRepostitory.GetAll().Where(x => typeInIds.Contains(x.TypeInId));
// 获取模板中所有的Org信息
var orgList = tplList.GroupBy(x => x.OrgCode).Select(x => x.Key).ToList();
var orgInfoList = _orgRepostitory.GetAll().Where(x => orgList.Contains(x.Code)).ToList();
List <SectionSummaryDto> sectionSummaryList = new List <SectionSummaryDto>();
// 组织信息
foreach (var item in orgList)
{
List <SpecSummaryDto> specList = new List <SpecSummaryDto>();
// 当前组织下的所有模板
var tempTplList = tplList.Where(x => x.OrgCode == item).Select(x => x.Id).ToList();
if (tempTplList.Count == 0)
{
continue;
}
// 组织下的所有化验元素
var tempTypeIn = typeInList.Where(x => tempTplList.Contains(x.TplId)).ToList();
if (tempTypeIn.Count == 0)
{
continue;
}
// 所有化验结果按照样品分类
var groupSpec = tempTypeIn.GroupBy(x => new { x.SpecId }).Select(x => x.Key.SpecId);
foreach (var specItem in groupSpec)
{
// 按照样品，获取所有的化验主表Id
var tTypeInIds = tempTypeIn.Where(x => x.SpecId == specItem).Select(x => x.Id).ToList();
// 获取样品下的所有元素
var tTypeInItemList = typeInItemList.Where(x => tTypeInIds.Contains(x.TypeInId) && !string.IsNullOrEmpty(x.EleValue)).ToList();
if (tTypeInItemList.Count() == 0)
{
continue;
}
string specName = tplSpecList.Where(x => x.Id == specItem).Select(x => x.SpecName).First();
// 金银个数
int auCount = tTypeInItemList.Where(x => x.EleName == "Au").Count();
int agCount = tTypeInItemList.Where(x => x.EleName == "Ag").Count();
int auAgCount = auCount > agCount ? auCount : agCount; // 金银取最大值
int allCount = tTypeInItemList.Count(); // 全部个数
// 普通元素个数
int commonCount = allCount - auCount - agCount;
// 样品个数
int specCount = tTypeInIds.Count();
SpecSummaryDto tSpecSummary = new SpecSummaryDto()
{
SpecName = specName,
SpecCount = specCount,
AuAg = auAgCount,
EleCount = commonCount
};
specList.Add(tSpecSummary);
}
string orgName = orgInfoList.Where(x => x.Code == item).Select(x => x.OrgName).First();
SectionSummaryDto ssd = new SectionSummaryDto()
{
OrgCode = item,
OrgName = orgName,
SpecList = specList
};
sectionSummaryList.Add(ssd);
}
string plantName = _orgRepostitory.GetAll().Where(x => x.Code == orgCode).Select(x => x.OrgName).First();
PlantSummaryDto p = new PlantSummaryDto()
{
OrgName = plantName,
OrgCode = orgCode,
SectionList = sectionSummaryList
};
return(p);
}
}
