public WorkbookDfn GenerateExcelSheet(DailyReportData dailyReportSheetData)
{
WorkbookDfn wb = new WorkbookDfn();
WorksheetDfn ws = new WorksheetDfn();
Rows = new List <RowDfn>();
SetWorksheetNames(ws, dailyReportSheetData.SheetName);
SetColumnHeaders(ws);
SetDailyReportInfo(ws, dailyReportSheetData.ReportInfo);
Rows.Add(new RowDfn {
Cells = new CellDfn[] { new CellDfn {
Value = ""
} }
});
SetActivityLogHeader(ws, dailyReportSheetData.ActivityLogHeaders);
SetActivityLogRows(ws, dailyReportSheetData.ActivityLog);
Rows.Add(new RowDfn {
Cells = new CellDfn[] { new CellDfn {
Value = ""
} }
});
SetReportBudgetHeader(ws, dailyReportSheetData.ReportBudgetHeaders);
SetReportBudgetRows(ws, dailyReportSheetData.ReportBudget);
ws.Rows = Rows;
List <WorksheetDfn> worksheetDfns = new List <WorksheetDfn> {
ws
};
wb.Worksheets = worksheetDfns;
return(wb);
}
public async Task <IActionResult> DownloadProductionDataAsync(DailyReportData dailyReportData)
{
if (dailyReportData == null)
{
throw new ArgumentNullException(nameof(dailyReportData));
}
else
{
string fileName = Path.GetTempFileName();
var data = dailyReportWriter.GenerateExcelSheet(dailyReportData);
SpreadsheetWriter.Write(fileName, data);
var memory = new MemoryStream();
using (var fileStream = new FileStream(fileName, FileMode.Open))
{
await fileStream.CopyToAsync(memory);
}
memory.Position = 0;
Response.Headers.Add("Content-Disposition",
"attachment; filename=DailyReport.xlsx");
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
}
/// <summary>
/// 每日结账单查询
/// </summary>
public ActionResult DailyReport(DailyReportSearchModel search)
{
DailyReportListViewModel model = new DailyReportListViewModel(); //页面模型
model.search = search; //页面的搜索模型
model.search.PageSize = 15; //每页显示
model.search.CurrentPage = Convert.ToInt32(Request["pageindex"]) <= 0 ? 1 : Convert.ToInt32(Request["pageindex"]); //当前页
model.DailyReportlist = DailyReportData.GetDailyReportList(search); //填充页面模型数据
return(View(model)); //返回页面模型
}
public IActionResult DownloadDailyReportDataAsync(DailyReportData dailyReportData)
{
if (dailyReportData == null)
{
throw new ArgumentNullException(nameof(dailyReportData));
}
else
{
var data = dailyReportWriterWithStyle.GenerateExcelSheet(dailyReportData);
Response.Headers.Add("Content-Disposition",
"attachment; filename=DailyReport.xlsx");
return(File(data, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
}
public byte[] GenerateExcelSheet(DailyReportData dailyReportSheetData)
{
var stream = new MemoryStream();
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorkbookStylesPart stylesPart = workbookpart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = Utility.GenerateStlyeSheet();
stylesPart.Stylesheet.Save();
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = dailyReportSheetData.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
SetColumnHeaders(sheetData);
SetDailyReportInfo(sheetData, dailyReportSheetData.ReportInfo);
SetEmptyRow(sheetData);
SetActivityLogHeader(sheetData, dailyReportSheetData.ActivityLogHeaders);
SetActivityLogRows(sheetData, dailyReportSheetData.ActivityLog);
SetEmptyRow(sheetData);
SetReportBudgetHeader(sheetData, dailyReportSheetData.ReportBudgetHeaders);
SetReportBudgetRows(sheetData, dailyReportSheetData.ReportBudget);
//SetColumnProperties(worksheetPart.Worksheet);
workbookpart.Workbook.Save();
document.Close();
stream.Position = 0;
return(stream.ToArray());
}
private void generateReport()
{
var date = DateTime.Now;
endDate = date.ToString("dd-MMM-yy hh:mm:ss tt");
//endDate = "28-May-18 23:59:59 PM";
var dat = DateTime.Today;
startDate = dat.ToString("dd-MMM-yy hh:mm:ss tt");
DailyReportData reportDetails = new DailyReportData();
DailyReport report = new DailyReport();
DataTable dtTable = reportDetails.CompanyDetails;
readCompanyDetails();
DataRow drr = dtTable.NewRow();
drr["CompanyName"] = companyName;
drr["CompanyDetails"] = companyDetails;
dtTable.Rows.Add(drr);
report.Database.Tables["CompanyDetails"].SetDataSource((DataTable)dtTable);
storeSales();
storeExpense();
DataTable dataTable = reportDetails._DailyReportData;
DataRow drow = dataTable.NewRow();
drow["StDate"] = endDate;
drow["TotalSales"] = totalSalesList.Sum();
drow["TotalCredit"] = totalCreditList.Sum();
dataTable.Rows.Add(drow);
for (int i = 0; i < expenseNameList.Count; i++)
{
DataRow drrow = dataTable.NewRow();
drrow["ExpenseName"] = expenseNameList[i];
drrow["ExpenseAmount"] = expenseAmountList[i];
dataTable.Rows.Add(drrow);
}
cashBalance = totalSalesList.Sum() - totalCreditList.Sum() - expenseAmountList.Sum();
DataRow dr = dataTable.NewRow();
dr["TotalExpense"] = expenseAmountList.Sum();
dr["CashBalance"] = cashBalance;
dataTable.Rows.Add(dr);
report.Database.Tables["DailyReportData"].SetDataSource((DataTable)dataTable);
dailyReportViewer.ViewerCore.ReportSource = report;
report.Refresh();
}
public static DailyReportData[] GET_DAILY_REPORT_RELATED_DATA(int?EXID, string BankID, string Branch, int?CityID, string DATE, int?STATUS)
{
try
{
List <DailyReportData> details = new List <DailyReportData>();
DataTable dt = CRBusinessLogicLayer.GET_DAILY_REPORT_RELATED_DATA(EXID, BankID, Branch, CityID, DATE, STATUS).Tables[0];
foreach (DataRow dtrow in dt.Rows)
{
DailyReportData DailyR = new DailyReportData();
DailyR.NAMEOFTHEEXECUTIVE = dtrow["NAME OF THE EXECUTIVE"].ToString();
DailyR.TARGET = dtrow["TARGET"].ToString();
DailyR.NOOFACSALLOTED = dtrow["NO. OF A/C'S ALLOTED"].ToString();
DailyR.NOOFNEWALLOTED = dtrow["NO. OF NEW ALLOTED"].ToString();
DailyR.NOOFACSATTEMPTEDTODAY = dtrow["NO. OF A/CS ATTEMPTED TODAY"].ToString();
DailyR.NOOFACSATTEMPTEDTILLDATE = dtrow["NO.OF A/CS ATTEMPTED TILL DATE"].ToString();
DailyR.TODAY = dtrow["TODAY"].ToString();
DailyR.TILLDATE = dtrow["TILL DATE"].ToString();
DailyR.BANK = dtrow["BANK"].ToString();
DailyR.BRANCH = dtrow["BRANCH"].ToString();
DailyR.ACCOUNTNO = dtrow["ACCOUNT NO."].ToString();
DailyR.CUSTOMERNAME = dtrow["CUSTOMER NAME"].ToString();
DailyR.AGEOFNPA = dtrow["AGE OF NPA(DAYS)"].ToString();
DailyR.BILLNO = dtrow["BILL NO"].ToString();
DailyR.BILLCLAIMED = dtrow["BILL CLAIMED"].ToString();
DailyR.BILLCOLLECTED = dtrow["BILL COLLECTED"].ToString();
DailyR.PERFORMANCEPERCENTAGE = dtrow["PERFORMANCE PERCENTAGE"].ToString();
DailyR.STATUS = dtrow["A/C STATUS"].ToString();
details.Add(DailyR);
}
return(details.ToArray());
}
catch (Exception)
{
// ignored
}
return(null);
}
static void Main(string[] args)
{
//创建要跨模型创建工作流对象共享的mlcontext
//为跨多个训练的可重复/确定性结果设置随机种子。
var mlContext = new MLContext(seed: 0);
// STEP 1: 定型模型的训练数据集，评估模型的测试数据集
IDataView dataTrainView = mlContext.Data.LoadFromTextFile <DailyReportData>(_trainDataPath, hasHeader: true);
IDataView dataTestView = mlContext.Data.LoadFromTextFile <DailyReportData>(_testDataPath, hasHeader: true);
// STEP 2: 数据特征化（按照管道所需的格式转换数据）
var dataProcessPipeline = mlContext.Transforms.Text.FeaturizeText(outputColumnName: "Features", inputColumnName: nameof(DailyReportData.Content));
// STEP 3: 根据学习算法添加学习管道
var trainer = mlContext.BinaryClassification.Trainers.SdcaLogisticRegression(labelColumnName: "Label", featureColumnName: "Features");
var trainingPipeline = dataProcessPipeline.Append(trainer);
// STEP 4: 根据定型模型的训练数据得到模型
ITransformer trainedModel = trainingPipeline.Fit(dataTrainView);
// STEP 5: 用评估模型的测试数据集评估模型的准确性
Console.WriteLine();
Console.WriteLine("=============== 评估模型的准确性，开始 ================");
var predictions = trainedModel.Transform(dataTestView);
var metrics = mlContext.BinaryClassification.Evaluate(data: predictions, labelColumnName: "Label", scoreColumnName: "Score");
Console.WriteLine($"模型质量量度评估结果精度: {metrics.Accuracy:P2}");
Console.WriteLine("=============== 评估模型的准确性，结束 ================");
Console.WriteLine();
// STEP 6: 评测完成之后保存定型的模型
mlContext.Model.Save(trainedModel, dataTrainView.Schema, _modelPath);
Console.WriteLine("模型->保存路径 {0}", _modelPath);
// 创建与加载的训练模型相关的预测引擎
var predEngine = mlContext.Model.CreatePredictionEngine <DailyReportData, DailyReportPrediction>(trainedModel);
// TRY IT: 测试数据预测
DailyReportData sampleStatement1 = new DailyReportData {
Content = "开发了添加多个item到wishlist的功能"
};
DailyReportData sampleStatement2 = new DailyReportData {
Content = "今天没有工作内容，打酱油"
};
// Score
var resultprediction1 = predEngine.Predict(sampleStatement1);
var resultprediction2 = predEngine.Predict(sampleStatement2);
Console.WriteLine();
Console.WriteLine("=============== 测试数据预测，开始 ===============");
Console.WriteLine($"日报内容: {sampleStatement1.Content} | 预测结果: {(Convert.ToBoolean(resultprediction1.Prediction) ? "合格" : "不合格")} | 合格的可能性: {resultprediction1.Probability} ");
Console.WriteLine();
Console.WriteLine($"日报内容: {sampleStatement2.Content} | 预测结果: {(Convert.ToBoolean(resultprediction2.Prediction) ? "合格" : "不合格")} | 合格的可能性: {resultprediction2.Probability} ");
Console.WriteLine("=============== 测试数据预测，结束 ================");
Console.ReadLine();
}
/// <summary>
/// 导出到Excel的操作
/// </summary>
/// <param name="json"></param>
public void ExportToExcel(string Name, string BindPhone, string timeStart, string timeEnd)
{
DataTable datasource = DailyReportData.DPExportToExcel(Name, BindPhone, timeStart, timeEnd);
ERP.Models.MyNPOIModel.ExportByWeb(datasource, "每日结账单", "每日结账单" + ".xls");
}