public ActionResult ComplaintSave(Order_Complaint m) { var mes = "保存失败!"; try { bool r = false; m.Status = 1; m.Time = DateTime.Now; Order_ComplaintDAL data1 = new Order_ComplaintDAL(); if (string.IsNullOrWhiteSpace(m.ID)) { r = data1.InsertOrder_Complaint(m); } else { r = data1.UpdateOrder_Complaint(m); } if (r) { mes = "OK"; } } catch (Exception ex) { } return(Content(mes)); }
/// <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); }