public ActionResult Login(User user)
{
var data = _Context.Users.Where(a => a.Email == user.Email && a.Password == user.Password).FirstOrDefault();
if (data != null)
{
TempData["Message"] = data.Name;
DateTime date = DateTime.Now;
var log = _Context.UserLogs.Where(a => a.Date == date.Date && a.UserId == data.Id).FirstOrDefault();
if (log == null)
{
UserLog userLog = new UserLog();
userLog.UserId = data.Id;
userLog.UserName = data.Name;
userLog.Date = date.Date;
userLog.Count = 1;
_Context.UserLogs.Add(userLog);
_Context.SaveChanges();
}
else
{
log.Count = (log.Count + 1);
_Context.Update(log);
_Context.SaveChanges();
}
return(RedirectToAction("Index", "Dashboard"));
}
ViewBag.Invalid = "Invalid Email & Password..!";
return(View());
}
/// <summary>
/// 保存当前上下文的修改
/// </summary>
/// <returns></returns>
public int Save()
{
try
{
return(context.SaveChanges());
}
//catch (DbEntityValidationException dbEx)
catch (Exception dbEx)
{
throw new Exception(dbEx.Message);
//foreach (var validationErrors in dbEx.EntityValidationErrors)
//{
// foreach (var validationError in validationErrors.ValidationErrors)
// {
// throw new Exception(string.Format("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage));
// }
//}
}
}
public ActionResult InputData(UserData ud)
{
if (ud.RangeFrom >= ud.RangeTo)
{
ModelState.AddModelError("", "RangeFrom > RangeTo");
}
if ((ud.RangeTo - ud.RangeFrom) > 10000)
{
ModelState.AddModelError("", "Too much points");
}
if (ModelState.IsValid)
{
var check = db.UserDatas.FirstOrDefault(d => d.A == ud.A && d.B == ud.B && d.C == ud.C && d.RangeFrom == ud.RangeFrom && d.RangeTo == ud.RangeTo && d.Step == ud.Step);
if (check != null)
{
var existsPoints = db.Points.Where(p => p.UserDataId == check.Id).ToList();
return(Json(existsPoints, JsonRequestBehavior.AllowGet));
}
List <Point> points = new List <Point>();
for (int i = ud.RangeFrom; i <= ud.RangeTo; i += ud.Step)
{
Point p = new Point();
p.X = i;
p.Y = ud.A * i * i + ud.B * i + ud.C;
points.Add(p);
db.Points.Add(p);
}
db.UserDatas.Add(ud);
db.SaveChanges();
return(Json(points, JsonRequestBehavior.AllowGet));
}
ViewBag.Message = "Запрос не прошел валидацию на сервере";
return(PartialView(ud));
}
protected void lnkDeleteFile_Command(object sender, CommandEventArgs e)
{
string filePath = e.CommandArgument.ToString().Split('|')[0];
string id = e.CommandArgument.ToString().Split('|')[1];
using (var context = new ChartContext())
{
int gatherId = int.Parse(id);
var entity = context.Gathers.Find(gatherId);
if (entity != null)
{
var entities = context.GatherQuantities.Where(a => a.GatherID == gatherId);
context.GatherQuantities.RemoveRange(entities);
context.Gathers.Remove(entity);
try
{
context.SaveChanges();
string excelPath = Server.MapPath(filePath);
if (File.Exists(excelPath))
{
File.Delete(excelPath);
}
string pngFileName = filePath.Replace("xlsx", "png");
string pngPath = Server.MapPath(pngFileName);
if (File.Exists(pngPath))
{
File.Delete(pngPath);
}
MessageBox.ShowAndRedirect(this, "文件删除成功", "AddChartData.aspx?tab=gather");
}
catch (Exception ex)
{
//throw new Exception(ex.Message);
MessageBox.ShowAndRedirect(this, "文件删除失败:" + ex.Message, "AddChartData.aspx?tab=gather");
}
}
}
}
protected void lnkDeleteFile_Command(object sender, CommandEventArgs e)
{
string filePath = e.CommandArgument.ToString().Split('|')[0];
string id = e.CommandArgument.ToString().Split('|')[1];
using (var context = new ChartContext())
{
int gatherId = int.Parse(id);
var gather = context.Gathers.Find(gatherId);
var legend = context.Legends.FirstOrDefault(a => a.GatherID == gatherId);
var legendDatas = context.LegendDatas.Where(a => a.LegendID == legend.ID);
context.LegendDatas.RemoveRange(legendDatas);
context.Legends.Remove(legend);
context.Gathers.Remove(gather);
try
{
context.SaveChanges();
string excelPath = Server.MapPath(filePath);
if (File.Exists(excelPath))
{
File.Delete(excelPath);
}
string pngFileName = filePath.Replace("xlsx", "png");
string pngPath = Server.MapPath(pngFileName);
if (File.Exists(pngPath))
{
File.Delete(pngPath);
}
MessageBox.ShowAndRedirect(this, "图表删除成功", Request.RawUrl);
}
catch (Exception)
{
throw;
}
}
}
protected void btnGatherSubmit_Click(object sender, EventArgs e)
{
string customerId = this.ddlCustomer.SelectedValue;
string chartTitle = this.txtChartTitle.Text.Trim().Replace("#", "");
Dictionary <string, string> tables = (Dictionary <string, string>)ViewState["tables"];
using (var context = new ChartContext())
{
try
{
var missing = Type.Missing;
string fileName = Server.MapPath("~/template_files/template_workbook.xlsx");
Excel.Application application = new Excel.Application();
application.Visible = false;
application.DisplayAlerts = false;
Excel.Workbook workbook = application.Workbooks.Open(fileName);
string sheetname = "sheet1";
Excel._Worksheet worksheet = workbook.Sheets[sheetname];
worksheet.Activate();
Excel.Range Range_B1 = worksheet.get_Range("B1");
Range_B1.Value2 = "敏感信息量";
Excel.Range Range_C1 = worksheet.get_Range("C1");
Range_C1.Value2 = "信息总量";
int i = 1;
foreach (KeyValuePair <string, string> item in tables)
{
i++;
Excel.Range Range_Ai = worksheet.get_Range("A" + i);
Range_Ai.Value2 = item.Key;
Excel.Range Range_Bi = worksheet.get_Range("B" + i);
Range_Bi.Value2 = item.Value.Split(',')[0];
Excel.Range Range_Ci = worksheet.get_Range("C" + i);
Range_Ci.Value2 = item.Value.Split(',')[1];
}
Excel.Range dataSourceRange = worksheet.get_Range("A1", "C" + i);
Excel.Shape shape1 = worksheet.Shapes.AddChart(Excel.XlChartType.xl3DColumnClustered, missing, missing, missing, missing);
//设置数据源
shape1.Chart.SetSourceData(dataSourceRange, missing);
//设置标题
shape1.Chart.HasTitle = true;
shape1.Chart.ChartTitle.Text = chartTitle;
shape1.Chart.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 16;
//设置Legend
shape1.Chart.SeriesCollection(1).Name = (string)worksheet.get_Range("B1").Value2;
shape1.Chart.SeriesCollection(2).Name = (string)worksheet.get_Range("C1").Value2;
shape1.Chart.ApplyDataLabels();
shape1.Left = (float)worksheet.get_Range("G1").Left;
shape1.Top = (float)worksheet.get_Range("G6").Top;
shape1.Fill.Visible = MsoTriState.msoTrue;
shape1.Fill.ForeColor.RGB = Color.FromArgb(225, 236, 238).ToArgb();
shape1.Fill.Transparency = 0;
shape1.Fill.Solid();
shape1.Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
DirectoryInfo directoryInfo = new DirectoryInfo(Server.MapPath("~/Download/" + this.ddlCustomer.SelectedItem.Text));
if (!directoryInfo.Exists)
{
directoryInfo.Create();
}
String ymd = DateTime.Now.ToString("yyyyMMddHHmmss", DateTimeFormatInfo.InvariantInfo);
string saveExcelFileName = String.Format("{0}_{1}.xlsx", chartTitle, ymd);
string saveExcelFilePath = Path.Combine(directoryInfo.FullName, saveExcelFileName);
string savePngFileName = String.Format("{0}_{1}.png", chartTitle, ymd);
string savePngFilePath = Path.Combine(directoryInfo.FullName, savePngFileName);
string excelFileName = "/Download/" + this.ddlCustomer.SelectedItem.Text + "/" + saveExcelFileName;
string pngFileName = "/Download/" + this.ddlCustomer.SelectedItem.Text + "/" + savePngFileName;
worksheet.SaveAs(saveExcelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
shape1.Chart.Export(savePngFilePath, missing, missing);
application.Quit();
worksheet = null;
workbook = null;
application = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
var entity = new Gather
{
CustomerID = int.Parse(customerId),
TimeRange = "",
ChartTitle = chartTitle,
ExcelFilePath = excelFileName,
PngFilePath = pngFileName,
AddDate = DateTime.Now,
Flag = "gather"
};
foreach (KeyValuePair <string, string> item in tables)
{
var gatherQuantity = new GatherQuantity
{
ChannelName = item.Key,
SensitiveNumber = int.Parse(item.Value.Split(',')[0]),
TotalNumber = int.Parse(item.Value.Split(',')[1]),
};
entity.GatherQuantities.Add(gatherQuantity);
}
context.Gathers.Add(entity);
context.SaveChanges();
InitRepeater2();
using (FileStream fileStream = new FileStream(saveExcelFilePath, FileMode.Open))
{
byte[] bytes = fileStream.ToByteArray();
Response.Clear();
Response.AddHeader("Content-Length", fileStream.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", "inline;FileName=" + saveExcelFileName);
fileStream.Close();
fileStream.Dispose();
Response.BinaryWrite(bytes);
Response.End();
}
}
catch (Exception ex)
{
MessageBox.ShowAndRedirect(this, "生成图表是出错：" + ex.Message, Request.RawUrl);
}
}
}
public bool Save()
{
return(_context.SaveChanges() >= 0);
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
DataTable table = (DataTable)ViewState["CloneTable"];
if (table == null)
{
MessageBox.Show(this, "数据表不存在！");
return;
}
if (table.Rows.Count < 3)
{
MessageBox.Show(this, "不能低于3条数据！");
return;
}
string customerId = this.ddlCustomer.SelectedValue;
string chartTitle = this.txtChartTitle.Text.Trim().Replace("#", "");
try
{
var missing = Type.Missing;
string fileName = Server.MapPath("~/template_files/template_workbook.xlsx");
Excel.Application application = new Excel.Application();
application.Visible = false;
application.DisplayAlerts = false;
Excel.Workbook workbook = application.Workbooks.Open(fileName);
string SheetName = "sheet1";
Excel._Worksheet worksheet = workbook.Sheets[SheetName];
worksheet.Activate();
//写入Excel列头
int i = 0;
int columnsCount = table.Columns.Count;
for (int j = 0; j < columnsCount; j++)
{
if (j > 1)
{
i++;
var letter = CommonUtility.GetLetters()[i];
Excel.Range range = worksheet.get_Range(letter + "1");
range.Value2 = table.Columns[j].ColumnName;
}
}
//写入Excel数据
int a = 1;
foreach (DataRow row in table.Rows)
{
a++;
for (int b = 1; b < columnsCount; b++)
{
Excel.Range range = worksheet.get_Range(CommonUtility.GetLetters()[b - 1] + a);
range.Value2 = row[b].ToString();
}
}
Excel.Shape shape1 = worksheet.Shapes.AddChart(Excel.XlChartType.xlLine, missing, missing, missing, missing);
Excel.Range sourceRange = worksheet.get_Range("A1", CommonUtility.GetLetters()[columnsCount - 2] + a);
//设置数据源
shape1.Chart.SetSourceData(sourceRange, missing);
//设置标题
shape1.Chart.HasTitle = true;
shape1.Chart.ChartTitle.Text = chartTitle;
shape1.Chart.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 16;
Excel.Series se = shape1.Chart.SeriesCollection(1);
se.Format.Line.Visible = MsoTriState.msoTrue;
se.Format.Line.Transparency = 0.0f;
se.Format.Line.Weight = 2.25f;
se = shape1.Chart.SeriesCollection(2);
se.Format.Line.ForeColor.RGB = Color.FromArgb(0, 0, 192).ToArgb();
se.Format.Line.Visible = MsoTriState.msoTrue;
se.Format.Line.Transparency = 0.0f;
se.Format.Line.Weight = 2.25f;
shape1.Left = (float)worksheet.get_Range("G1").Left;
shape1.Top = (float)worksheet.get_Range("G8").Top;
shape1.Fill.Visible = MsoTriState.msoTrue;
shape1.Fill.ForeColor.RGB = Color.FromArgb(225, 236, 238).ToArgb();
shape1.Fill.Transparency = 0;
shape1.Fill.Solid();
shape1.Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
shape1.Chart.PlotArea.Format.Fill.Visible = MsoTriState.msoTrue;
shape1.Chart.PlotArea.Format.Fill.ForeColor.RGB = Color.FromArgb(218, 234, 253).ToArgb();
shape1.Chart.PlotArea.Format.Fill.Transparency = 0;
shape1.Chart.PlotArea.Format.Fill.Solid();
DirectoryInfo directoryInfo = new DirectoryInfo(Server.MapPath("~/Download/" + this.ddlCustomer.SelectedItem.Text));
if (!directoryInfo.Exists)
{
directoryInfo.Create();
}
String ymd = DateTime.Now.ToString("yyyyMMddHHmmss", DateTimeFormatInfo.InvariantInfo);
string saveExcelFileName = String.Format("{0}_{1}.xlsx", chartTitle, ymd);
string saveExcelFilePath = Path.Combine(directoryInfo.FullName, saveExcelFileName);
string savePngFileName = String.Format("{0}_{1}.png", chartTitle, ymd);
string savePngFilePath = Path.Combine(directoryInfo.FullName, savePngFileName);
string excelFileName = "/Download/" + this.ddlCustomer.SelectedItem.Text + "/" + saveExcelFileName;
string pngFileName = "/Download/" + this.ddlCustomer.SelectedItem.Text + "/" + savePngFileName;
worksheet.SaveAs(saveExcelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
shape1.Chart.Export(savePngFilePath, missing, missing);
application.Quit();
worksheet = null;
workbook = null;
application = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
using (var context = new ChartContext())
{
var entity = new Gather
{
CustomerID = int.Parse(customerId),
TimeRange = "",
ChartTitle = chartTitle,
ExcelFilePath = excelFileName,
PngFilePath = pngFileName,
AddDate = DateTime.Now,
Flag = "trendCompare"
};
StringBuilder sBuilder = new StringBuilder();
foreach (ListItem item in this.lstCategories.Items)
{
sBuilder.AppendFormat("{0},", item.Value);
}
string legendString = sBuilder.ToString().TrimEnd(',');
Legend legend = new Legend()
{
LegendName = legendString
};
entity.Legends.Add(legend);
StringBuilder legendDataBuilder = new StringBuilder();
foreach (DataRow row in table.Rows)
{
for (int c = 0; c < table.Columns.Count; c++)
{
if (c > 0)
{
if (c == 1)
{
legendDataBuilder.AppendFormat("{0}+", row[c].ToString());
}
else
{
legendDataBuilder.AppendFormat("{0},", row[c].ToString());
}
}
}
legendDataBuilder.Append("|");
}
string strValue = legendDataBuilder.ToString().TrimEnd('|');
foreach (string str in strValue.Split('|'))
{
LegendData legendData = new LegendData()
{
DateString = str.TrimEnd(',').Split('+')[0],
LegendValue = str.TrimEnd(',').Split('+')[1]
};
legend.LegendDatas.Add(legendData);
}
context.Gathers.Add(entity);
context.SaveChanges();
}
using (FileStream fileStream = new FileStream(saveExcelFilePath, FileMode.Open))
{
byte[] bytes = fileStream.ToByteArray();
Response.Clear();
Response.AddHeader("Content-Length", fileStream.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", "inline;FileName=" + saveExcelFileName);
fileStream.Close();
fileStream.Dispose();
Response.BinaryWrite(bytes);
Response.End();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string customerId = this.ddlCustomer.SelectedValue;
string filePath = Server.MapPath("~/template_files/template_workbook.xlsx");
string chartTitle = this.txtChartTitle.Text.Replace("#", "");
using (var context = new ChartContext())
{
DataTable table = (DataTable)ViewState["table"];
DataTable table1 = (DataTable)ViewState["table1"];
if (this.ddlChartType.SelectedValue == "1")
{
var rows = table.Select("Quantity <> '0'");
if (rows.Count() == 0)
{
MessageBox.Show(this, "图表数据不能为空");
return;
}
}
if (this.ddlChartType.SelectedValue == "2")
{
var rows = table1.Rows;
if (rows.Count == 0)
{
MessageBox.Show(this, "图表数据不能为空");
return;
}
}
try
{
var missing = Type.Missing;
string fileName = Server.MapPath("~/template_files/template_workbook.xlsx");
Excel.Application application = new Excel.Application();
application.Visible = false;
application.DisplayAlerts = false;
Excel.Workbook workbook = application.Workbooks.Open(fileName);
string sheetname = "sheet1";
Excel._Worksheet worksheet = workbook.Sheets[sheetname];
worksheet.Activate();
List <GatherTrend> gatherTrends = new List <GatherTrend>();
List <int> listElements = new List <int>();
int i = 0;
string chartCategory = this.ddlChartType.SelectedValue;
switch (chartCategory)
{
case "1":
table = (DataTable)ViewState["table"];
string dateString = string.Empty;
foreach (DataRow row in table.Rows)
{
if (row["Quantity"].ToString() != "0")
{
if (this.rbYearMonth.Checked == true)
{
dateString = DateTime.Parse(row["Date"].ToString()).ToString("yyyy年M月");
}
else if (this.rbMonthDay.Checked == true)
{
dateString = DateTime.Parse(row["Date"].ToString()).ToString("M月d日");
}
i++;
Excel.Range rangeA = worksheet.get_Range("A" + i);
rangeA.Value = dateString;
Excel.Range rangeB = worksheet.get_Range("B" + i);
rangeB.Value = row["Quantity"].ToString();
listElements.Add(row["Quantity"].ToInt32());
GatherTrend trend = new GatherTrend
{
Name = dateString,
Number = row["Quantity"].ToInt32()
};
gatherTrends.Add(trend);
}
}
break;
case "2":
table1 = (DataTable)ViewState["table1"];
foreach (DataRow row in table1.Rows)
{
i++;
Excel.Range rangeA = worksheet.get_Range("A" + i);
rangeA.Value = row["Issue"];
Excel.Range rangeB = worksheet.get_Range("B" + i);
rangeB.Value = row["Quantity"].ToString();
listElements.Add(row["Quantity"].ToInt32());
}
break;
}
Excel.Range dataSourceRange = worksheet.get_Range("A1", "B" + i);
Excel.Shape shape1 = worksheet.Shapes.AddChart(Excel.XlChartType.xlLineMarkers, missing, missing, missing, missing);
//设置数据源
shape1.Chart.SetSourceData(dataSourceRange, missing);
//设置标题
shape1.Chart.HasTitle = true;
shape1.Chart.ChartTitle.Text = chartTitle.Replace("#", "");
shape1.Chart.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 16;
//设置Legend
if (this.rblDataLabels.SelectedValue == "1")
{
shape1.Chart.ApplyDataLabels();
}
else if (this.rblDataLabels.SelectedValue == "2")
{
int maxIndex = listElements.IndexOf(listElements.Max()) + 1;
int minIndex = listElements.IndexOf(listElements.Min()) + 1;
shape1.Chart.SeriesCollection(1).Points(maxIndex).ApplyDataLabels();
shape1.Chart.SeriesCollection(1).Points(minIndex).ApplyDataLabels();
}
shape1.Left = (float)worksheet.get_Range("G1").Left;
shape1.Top = (float)worksheet.get_Range("G6").Top;
shape1.Fill.Visible = MsoTriState.msoTrue;
shape1.Fill.ForeColor.RGB = Color.FromArgb(225, 236, 238).ToArgb();
shape1.Fill.Transparency = 0;
shape1.Fill.Solid();
shape1.Chart.PlotArea.Format.Fill.Visible = MsoTriState.msoTrue;
shape1.Chart.PlotArea.Format.Fill.ForeColor.RGB = Color.FromArgb(218, 234, 253).ToArgb();
shape1.Chart.PlotArea.Format.Fill.Transparency = 0;
shape1.Chart.PlotArea.Format.Fill.Solid();
//shape1.Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
shape1.Chart.HasLegend = false;
DirectoryInfo directoryInfo = new DirectoryInfo(Server.MapPath("~/Download/" + this.ddlCustomer.SelectedItem.Text));
if (!directoryInfo.Exists)
{
directoryInfo.Create();
}
String ymd = DateTime.Now.ToString("yyyyMMddHHmmss", DateTimeFormatInfo.InvariantInfo);
string saveExcelFileName = String.Format("{0}_{1}.xlsx", chartTitle, ymd);
string saveExcelFilePath = Path.Combine(directoryInfo.FullName, saveExcelFileName);
string savePngFileName = String.Format("{0}_{1}.png", chartTitle, ymd);
string savePngFilePath = Path.Combine(directoryInfo.FullName, savePngFileName);
string excelFileName = "/Download/" + this.ddlCustomer.SelectedItem.Text + "/" + saveExcelFileName;
string pngFileName = "/Download/" + this.ddlCustomer.SelectedItem.Text + "/" + savePngFileName;
worksheet.SaveAs(saveExcelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
shape1.Chart.Export(savePngFilePath, missing, missing);
application.Quit();
worksheet = null;
workbook = null;
application = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
var entity = new Gather
{
CustomerID = int.Parse(customerId),
TimeRange = "",
ChartTitle = chartTitle,
ExcelFilePath = excelFileName,
PngFilePath = pngFileName,
AddDate = DateTime.Now,
Flag = "trend"
};
gatherTrends.ForEach(item =>
{
entity.GatherTrends.Add(item);
});
context.Gathers.Add(entity);
context.SaveChanges();
using (FileStream fileStream = new FileStream(saveExcelFilePath, FileMode.Open))
{
byte[] bytes = fileStream.ToByteArray();
Response.Clear();
Response.AddHeader("Content-Length", fileStream.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", "inline;FileName=" + saveExcelFileName);
fileStream.Close();
fileStream.Dispose();
Response.BinaryWrite(bytes);
Response.End();
}
InitRepeater3();
}
catch (Exception ex)
{
MessageBox.ShowAndRedirect(this, "生成图表是出错：" + ex.Message, Request.RawUrl);
}
}
}
public void Save()
{
db.SaveChanges();
}
public void SaveChartItem()
{
_context.SaveChanges();
}
