public ActionResult DesignTable(int Id = 0)
{
var rowModel = TableRowService.GetByWhere("where TableId=" + Id);
var tableUsers = TableUserRoleService.GetByWhere("where TableId=" + Id);
var tableModel = TableService.GetById(Id);
TableEditModel model = new TableEditModel();
model.TableId = Id;
model.Title = tableModel != null ? tableModel.Title : "";
model.RowNum = tableModel != null ? tableModel.RowNum : 100;
var AllUsers = UserService.GetAll();
model.AllUserJson = JsonConvert.SerializeObject(AllUsers);//获取当前的所有用户
List <UserModel> privatemodels = new List <UserModel>();
foreach (var item in tableUsers)
{
var whereItems = AllUsers.Where(x => x.Id == item.UserId);
if (whereItems.Count() > 0)
{
var extItem = whereItems.First();
privatemodels.Add(extItem);
}
}
model.PrivateUserJson = JsonConvert.SerializeObject(privatemodels); //获取当前的所有用户
//获取表的列宽度
List <string> fontwidthmodels = new List <string>();
if (tableModel != null && !string.IsNullOrWhiteSpace(tableModel.ColimnWidths))
{
fontwidthmodels = tableModel.ColimnWidths.Split(',').ToList();
}
else
{
for (int i = 0; i < 26; i++)
{
fontwidthmodels.Add("120");
}
}
model.colwidthmodels = JsonConvert.SerializeObject(fontwidthmodels);
//获取列标题
//获取表的列宽度
List <string> columntitlemodels = new List <string>();
if (tableModel != null && !string.IsNullOrWhiteSpace(tableModel.ColumnTitles))
{
columntitlemodels = tableModel.ColumnTitles.Split(',').ToList();
}
else
{
for (int i = 0; i < 26; i++)
{
columntitlemodels.Add(ExcelConvert.ToName(i));
}
}
model.coltitlesmodels = JsonConvert.SerializeObject(columntitlemodels);
return(View(model));
}
/// <summary>
/// 测试
/// </summary>
private void OnTest(object sender, RoutedEventArgs e)
{
ExcelConvert excelConvert = new ExcelConvert();
excelConvert.Export(null, GlobalDataManager.Instance.Config.OutputPath + "/test.xlsx");
ProcessUtility.OpenFolder(GlobalDataManager.Instance.Config.OutputPath);
}
public JsonResult AddTable(ScadaTableModel model)
{
model.CreateTime = DateTime.Now;
model.CreateUserId = Operator.UserId;
model.UpdateTime = DateTime.Now;
model.UpdateUserId = Operator.UserId;
model.ColNum = 26;
List <int> colWidths = new List <int>();
List <string> colTitles = new List <string>();
for (int i = 0; i < model.ColNum; i++)
{
colTitles.Add(ExcelConvert.ToName(i));
colWidths.Add(120);
}
model.ColimnWidths = string.Join(",", colWidths.ToArray());
model.ColumnTitles = string.Join(",", colTitles.ToArray());
model.RowNum = 100;
model.FilterRule = "";
int id = 0;
bool isres = TableService.Insert(model, out id);
if (isres)
{
TableUserRoleService.DeleteByWhere(" where TableId=" + id);
if (model.RoleUserID != null)
{
string[] users = model.RoleUserID.Split(',');
for (int i = 0; i < users.Length; i++)
{
ScadaTableUserRoleModel role = new ScadaTableUserRoleModel();
role.CreateTime = DateTime.Now;
role.CreateTime = DateTime.Now;
role.CreateUserId = Operator.UserId;
role.UpdateTime = DateTime.Now;
role.UpdateUserId = Operator.UserId;
role.SortCode = i + 1;
role.TableId = id;
role.UserId = int.Parse(users[i]);
TableUserRoleService.Insert(role);
}
}
}
var result = isres ? SuccessTip("添加成功") : ErrorTip("添加失败");
return(Json(result));
}
public IList <Row> GetSheetByRow()
{
List <Row> rows = new List <Row>();
object[,] value = (object[, ])app.ActiveSheet.Range["A1", ExcelConvert.ToName(app.ActiveSheet.UsedRange.Columns.Count) + app.ActiveSheet.UsedRange.Rows.Count].Value;
for (int row = 1; row <= value.GetLongLength(0); row++)
{
Row rowInfo = new Row()
{
Index = row
};
for (int col = 1; col <= value.GetLongLength(1); col++)
{
Cell cell = new Cell()
{
Value = value[row, col] == null ? "" : value[row, col].ToString(), ColumnName = ExcelConvert.ToName(col - 1), RowIndex = row
};
rowInfo.Cells.Add(cell);
}
rows.Add(rowInfo);
}
return(rows);
}
public IList <Cell> GetRangeByName(string rangeName)
{
List <Cell> cells = new List <Cell>();
dynamic rng = app.ActiveSheet.Range(rangeName);
if (rng == null)
{
return(cells);
}
if (rng.Cells.Count == 1)
{
Cell cell = new Cell()
{
Value = rng.Value(XlRangeValueDataType.xlRangeValueDefault), ColumnName = ExcelConvert.ToName((int)rng.Cells.Column - 1), RowIndex = rng.Cells.Row
};
cells.Add(cell);
return(cells);
}
object[,] exceldata = (object[, ])rng.Value(XlRangeValueDataType.xlRangeValueDefault);
for (int i = 1; i <= exceldata.GetLongLength(0); i++)
{
for (int j = 1; j <= exceldata.GetLongLength(1); j++)
{
Cell cell = new Cell()
{
Value = exceldata[i, j] == null ? "" : exceldata[i, j].ToString(), ColumnName = ExcelConvert.ToName(j - 1), RowIndex = i
};
cells.Add(cell);
}
}
return(cells);
}
public void SetCellValue(string sheetName, int rowIndex, string columnName, string value)
{
dynamic xlsWorkSheet = wkb.Worksheets[sheetName];
xlsWorkSheet.Cells[rowIndex, ExcelConvert.ToIndex(columnName) + 1] = value;
}
public void SetCellValue(int rowIndex, string columnName, string value)
{
app.ActiveSheet.Cells[rowIndex, ExcelConvert.ToIndex(columnName) + 1] = value;
}
public Column GetColumn(string columnName)
{
Column column = new Column();
dynamic rng = app.ActiveSheet.Range(columnName + 1 + ":" + columnName + UsedRowCount);
if (rng == null)
{
return(column);
}
if (rng.Cells.Count == 1)
{
Cell cell = new Cell()
{
Value = rng.Value(XlRangeValueDataType.xlRangeValueDefault).ToString(), ColumnName = ExcelConvert.ToName((int)rng.Cells.Column - 1), RowIndex = rng.Cells.Row
};
column.Cells.Add(cell);
return(column);
}
object[,] exceldata = (object[, ])rng.Value(XlRangeValueDataType.xlRangeValueDefault);
for (int i = 1; i <= exceldata.GetLongLength(0); i++)
{
for (int j = 1; j <= exceldata.GetLongLength(1); j++)
{
Cell cell = new Cell()
{
Value = exceldata[i, j] == null ? "" : exceldata[i, j].ToString(), ColumnName = ExcelConvert.ToName(j - 1), RowIndex = i
};
column.Cells.Add(cell);
}
}
return(column);
}
/// <summary>
/// 导出投诉统计
/// </summary>
/// <param name="dept"></param>
/// <param name="deptname"></param>
/// <param name="st"></param>
/// <param name="et"></param>
/// <param name="result"></param>
/// <returns></returns>
public static bool ComplaintoTable(string dept, string deptname, string st, string et, ref string result)
{
bool r = false;
#region 读取数值
DataTable dt = new DataTable();
string where = "";
if (!string.IsNullOrWhiteSpace(dept))
{
where = where + " and Order_Info.DeptID=" + dept;
}
if (st == et)
{
if (!string.IsNullOrWhiteSpace(st))
{
where = where + " and Order_Complaint.ComplainDate='" + st + "'";
}
}
else
{
if (!string.IsNullOrWhiteSpace(st))
{
where = where + " and Order_Complaint.ComplainDate>='" + st + "'";
}
if (!string.IsNullOrWhiteSpace(et))
{
where = where + " and Order_Complaint.ComplainDate<='" + et + "'";
}
}
Order_ComplaintDAL ocdata = new Order_ComplaintDAL();
DataTable db = ocdata.TableOrder_Complaint1(where);
int c = 1;
if (db != null && db.Rows.Count > 0)
{
dt.Columns.Add("序号", typeof(string));
dt.Columns.Add("部门", typeof(string));
dt.Columns.Add("工号", typeof(string));
dt.Columns.Add("姓名", typeof(string));
dt.Columns.Add("退费总额", typeof(string));
dt.Columns.Add("投诉量", typeof(string));
dt.Columns.Add("取消量", typeof(string));
dt.Columns.Add("话务员责任量", typeof(string));
double d1 = 0, d2 = 0, d3 = 0, d4 = 0;
ArrayList tempList = new ArrayList();
foreach (DataRow m in db.Rows)
{
tempList = new ArrayList();
tempList.Add(c);
tempList.Add(m["DeptName"]);
tempList.Add(m["UserID"]);
tempList.Add(m["UserName"]);
tempList.Add(m["a1"]); d1 += Convert.ToDouble(m["a1"]);
tempList.Add(m["a2"]); d2 += Convert.ToDouble(m["a2"]);
tempList.Add(m["a3"]); d3 += Convert.ToDouble(m["a3"]);
tempList.Add(m["a4"]); d4 += Convert.ToDouble(m["a4"]);
dt.LoadDataRow(tempList.ToArray(), true);
c++;
}
tempList = new ArrayList();
tempList.Add("");
tempList.Add("合计");
tempList.Add("");
tempList.Add("");
tempList.Add(d1);
tempList.Add(d2);
tempList.Add(d3);
tempList.Add(d4);
dt.LoadDataRow(tempList.ToArray(), true);
}
#endregion
result = "";
try
{ //创建文件夹
String savePath = "../upload/file/";
String dirPath = System.Web.HttpContext.Current.Server.MapPath(savePath);
if (!Directory.Exists(dirPath))
{
Directory.CreateDirectory(dirPath);
}
DateTime now = DateTime.Now;
String ymd = now.ToString("yyyyMMdd", DateTimeFormatInfo.InvariantInfo);
dirPath += ymd + "/";
savePath += ymd + "/";
if (!Directory.Exists(dirPath))
{
Directory.CreateDirectory(dirPath);
}
String newFileName = deptname + "投诉统计表" + st + "至" + et;
String filepath = dirPath + newFileName + ".xlsx";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
try
{
if (dt == null)
{
result = "DataTableToExcel:datatable 为空";
return(false);
}
FileInfo info = new FileInfo(filepath);
info.Create().Dispose();
//为单元格添加样式
Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
Worksheet sheet = wb.Worksheets[0]; //工作表
Cells cells = sheet.Cells; //单元格
//设置居中
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Black;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Black;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Black;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Black;
style.IsTextWrapped = true;//单元格内容自动换行
style.Font.IsBold = true;
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = ColorTranslator.FromHtml("#B6DDE8");
style.Font.Color = Color.Black;
cells.Merge(0, 0, 1, 8); //合并单元格
cells[0, 0].PutValue(newFileName); //填写内容
cells[0, 0].SetStyle(style); //给单元格关联样式
cells.Merge(0, 8, 1, 1); //合并单元格
cells[0, 8].PutValue(""); //填写内容
cells.SetRowHeight(0, 25); //设置行高
int rowIndex = 1;
for (int i = 0; i < dt.Columns.Count; i++)
{
DataColumn col = dt.Columns[i];
string columnName = col.Caption ?? col.ColumnName;
cells[rowIndex, i].PutValue(columnName);
cells[rowIndex, i].SetStyle(style);
}
cells.SetRowHeight(rowIndex, 25);//设置行高
rowIndex++;
style.Font.IsBold = false;
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
style.Font.Color = Color.Black;
style.ForegroundColor = Color.White;
cells[rowIndex, i].PutValue(row[i].ToString());
if (rowIndex == dt.Rows.Count + 1)
{
style.Font.IsBold = true;
style.ForegroundColor = ColorTranslator.FromHtml("#B6DDE8");
}
cells[rowIndex, i].SetStyle(style);
}
cells.SetRowHeight(rowIndex, 25);//设置行高
rowIndex++;
}
for (int k = 0; k < dt.Columns.Count; k++)
{
sheet.AutoFitColumn(k, 0, dt.Rows.Count);
}
for (int col = 0; col < cells.Columns.Count; col++)
{
if (cells.GetColumnWidthPixel(col) > 300)
{
cells.SetColumnWidthPixel(col, 300);
}
}
sheet.FreezePanes("A3", 2, dt.Columns.Count);
sheet.AutoFilter.Range = "A2:" + ExcelConvert.ToName(dt.Columns.Count - 1) + "2";
wb.Save(filepath);
result = savePath + newFileName + ".xlsx";
return(true);
}
catch (Exception e)
{
result = result + " DataTableToExcel: " + e.Message;
return(false);
}
}
catch (System.Exception e)
{
result = "失败：" + e.Message;
}
return(r);
}
public override ActionResult Index(int?id)
{
string para = Request.QueryString["id"].Split('?')[0];
string idstr = Request.QueryString["id"].Split('?')[1].Split('=')[1];
base.Index(int.Parse(idstr));
var rowModel = TableRowService.GetByWhere("where TableId=" + int.Parse(para)).First();
var tableUsers = TableUserRoleService.GetByWhere("where TableId=" + int.Parse(para));
var tableModel = TableService.GetById(int.Parse(para));
TableRealModel model = new TableRealModel();
model.TableId = int.Parse(para);
model.Title = tableModel != null ? tableModel.Title : "";
model.RowNum = tableModel != null ? tableModel.RowNum : 100;
var AllUsers = UserService.GetAll();
model.AllUserJson = JsonConvert.SerializeObject(AllUsers);//获取当前的所有用户
List <UserModel> privatemodels = new List <UserModel>();
foreach (var item in tableUsers)
{
var whereItems = AllUsers.Where(x => x.Id == item.UserId);
if (whereItems.Count() > 0)
{
var extItem = whereItems.First();
privatemodels.Add(extItem);
}
}
model.PrivateUserJson = JsonConvert.SerializeObject(privatemodels); //获取当前的所有用户
//获取表的列宽度
List <string> fontwidthmodels = new List <string>();
if (tableModel != null && !string.IsNullOrWhiteSpace(tableModel.ColimnWidths))
{
fontwidthmodels = tableModel.ColimnWidths.Split(',').ToList();
}
else
{
for (int i = 0; i < 26; i++)
{
fontwidthmodels.Add("120");
}
}
model.colwidthmodels = JsonConvert.SerializeObject(fontwidthmodels);
//获取列标题
//获取表的列宽度
List <string> columntitlemodels = new List <string>();
if (tableModel != null && !string.IsNullOrWhiteSpace(tableModel.ColumnTitles))
{
columntitlemodels = tableModel.ColumnTitles.Split(',').ToList();
}
else
{
for (int i = 0; i < 26; i++)
{
columntitlemodels.Add(ExcelConvert.ToName(i));
}
}
model.coltitlesmodels = JsonConvert.SerializeObject(columntitlemodels);
model.ScadaTable = tableModel;
model.FieldBackColors = rowModel.FieldBackColors;
model.FieldColors = rowModel.FieldColors;
model.FieldFontSizes = rowModel.FieldFontSizes;
model.FieldIOPaths = rowModel.FieldIOPaths;
model.FieldWeights = rowModel.FieldWeights;
///获取设备列表
JavaScriptSerializer js = new JavaScriptSerializer(); //实例化一个能够序列化数据的类
List <ExcelModel> excelModels = js.Deserialize <List <ExcelModel> >(rowModel.FieldIOPaths);
List <DeviceGroupModel> deviceAllModels = DeviceGroupService.GetAll().ToList();
List <DeviceGroupModel> deviceModels = new List <DeviceGroupModel>();
for (int i = 0; i < excelModels.Count; i++)
{
PropertyInfo[] properties = excelModels[i].GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
if (properties.Length <= 0)
{
continue;
}
foreach (PropertyInfo item in properties)
{
string name = item.Name;
object value = item.GetValue(excelModels[i], null);
if (value != null && value.ToString() != "")
{
if (value.ToString().Split('/').Length >= 8)
{
try
{
DeviceGroupModel groupDevice = new DeviceGroupModel();
groupDevice.GroupId = int.Parse(value.ToString().Split('/')[1]);
groupDevice.IO_SERVER_ID = value.ToString().Split('/')[2];
groupDevice.IO_COMM_ID = value.ToString().Split('/')[3];
groupDevice.IO_DEVICE_ID = value.ToString().Split('/')[4];
groupDevice.UpdateCycle = int.Parse(value.ToString().Split('/')[6]);
groupDevice.CurrentIO = value.ToString().Split('/')[5];//当前的iO
DeviceGroupModel exitItem = deviceAllModels.Find(x => x.IO_SERVER_ID == groupDevice.IO_SERVER_ID && x.IO_COMM_ID == groupDevice.IO_COMM_ID && x.IO_DEVICE_ID == groupDevice.IO_DEVICE_ID && x.GroupId == groupDevice.GroupId);
if (exitItem != null)
{
if (!deviceModels.Exists(x => x.IO_SERVER_ID == groupDevice.IO_SERVER_ID && x.IO_COMM_ID == groupDevice.IO_COMM_ID && x.IO_DEVICE_ID == groupDevice.IO_DEVICE_ID && x.GroupId == groupDevice.GroupId))
{
exitItem.CurrentIO = groupDevice.CurrentIO;
deviceModels.Add(exitItem);
}
}
}
catch
{
continue;
}
}
}
}
}
model.Devices = deviceModels;
model.JsonDevices = js.Serialize(deviceModels);
return(View(model));
}
