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)); }