protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e) { int rowIndex = Int32.Parse(e.CommandArgument.ToString());//获取点击了第几行 int posID = Int32.Parse(grVPositionManage.DataKeys[rowIndex].Values[0].ToString()); if (e.CommandName == "DimSetting") { Response.Redirect("~/System/DimensionSet.aspx?mode=0&posid=" + posID.ToString()); } if (e.CommandName.Equals("Export") || e.CommandName.Equals("ExportNewData"))//答题信息导出 or 数据导出 { PositionBLL temp = new PositionBLL(); DimensionBLL dBll = new DimensionBLL(); DataSet dimDS = dBll.GetDimensionAll(); DataSet dsAll = new DataSet(); for (int i = 0; i < dimDS.Tables[0].Rows.Count; i++) { int dimID = Int32.Parse(dimDS.Tables[0].Rows[i][0].ToString()); DataSet dsa = temp.GetExportDSIncAllDim(posID,dimID); dsa.Tables[0].TableName = dBll.GetDimension(dimID.ToString()).Dimnm; dsAll.Tables.Add(dsa.Tables[0].Copy()); } //2012.3.14,岗位导出时删除无关维度表 begin. for (int i = dsAll.Tables.Count - 1; i >= 0; i--) { if (dsAll.Tables[i].Rows.Count < 1) { dsAll.Tables.Remove(dsAll.Tables[i]); } } dsAll.AcceptChanges(); //2012.3.14,岗位导出时删除无关维度表 end. //新的导出格式---数据导出 DataSet dsPivot = new DataSet(); if (e.CommandName.Equals("ExportNewData")) { //开始进行pivot string activityName = string.Empty; string testerName = string.Empty; TesterInfoBLL tbll = new TesterInfoBLL(); ActivityBLL abll = new ActivityBLL(); GuidBLL gbll = new GuidBLL(); foreach (DataTable t in dsAll.Tables) { Pivot p = new Pivot(t); DataTable dt = p.PivotData("序列号", "原始得分", AggregateFunction.Sum, "题目编号"); p = null; dt.TableName = t.TableName; dt.Columns.Add("项目名称").SetOrdinal(0); dt.Columns.Add("姓名").SetOrdinal(1); foreach (DataRow row in dt.Rows) { string guid = row["序列号"].ToString(); row["项目名称"] = abll.GetActivityNM(gbll.GetActivityId(guid)); row["姓名"] = tbll.GetUserNameByGUID(guid); } dsPivot.Tables.Add(dt); } //pivot结束 } string filePath = Server.MapPath("../userfiles") + @"\position_" + DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + ".xls"; string fileName = Path.GetFileName(filePath); if (e.CommandName.Equals("Export"))//原始格式--答题信息导出 { //2013.10.22 add by conghui for export issue begin. foreach (DataTable dt in dsAll.Tables) { for (int i = 0; i < dt.Columns.Count; i++) { for (int j = 0; j < dt.Rows.Count; j++) { if (dt.Rows[j][i] == DBNull.Value || dt.Rows[j][i] == null)//如果单元格为空 { if (i == 2)//第二列是日期列 { dt.Rows[j][i] = "1900-1-1"; } else { dt.Rows[j][i] = ""; } } else//单元格非空 { if (i == 2)//将日期强制转化为字符串 { dt.Rows[j][i] = dt.Rows[j][i].ToString(); } } } } } //2013.10.22 add by conghui for export issue end. ExcelLibrary.DataSetHelper.CreateWorkbook(filePath, dsAll); } else if (e.CommandName.Equals("ExportNewData"))//新的格式--答题数据导出 { ExcelLibrary.DataSetHelper.CreateWorkbook(filePath, dsPivot); } Stream stream = null; stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read); long bytesToRead = stream.Length; Response.Clear(); Response.ContentType = "application/ms-excel"; Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); while (bytesToRead > 0) { if (Response.IsClientConnected) { byte[] buffer = new Byte[10000]; int length = stream.Read(buffer, 0, 10000); Response.OutputStream.Write(buffer, 0, length); Response.Flush(); bytesToRead = bytesToRead - length; } else { bytesToRead = -1; } } } }
private bool ImportComment(string xlsFileNm) { try { if (!File.Exists(xlsFileNm)) return false; int posID = Int32.Parse(ddlPosition.SelectedItem.Value); Workbook book = Workbook.Load(xlsFileNm); PositionBLL posBLL = new PositionBLL(); DimensionBLL dimBLL = new DimensionBLL(); CommentBLL comBLL = new CommentBLL(); Comment com = new Comment(); DataSet dimDS = dimBLL.GetDimensionAll(); DataSet posDS = posBLL.GetPositionAll(string.Empty); comBLL.DelAllComment(posID);//删除与此岗位相关的所有评语。 foreach (Worksheet sheet in book.Worksheets) { string sheetNm = sheet.Name.Trim(); int dimID = -1;//在这里加断点可以监视单个维度的导入情况 foreach (DataRow row in dimDS.Tables[0].Rows) { if (row["dimension_name"].ToString().Equals(sheetNm)) { dimID = Int32.Parse(row["dimension_id"].ToString().Trim()); break; } } for (int rowIndex = sheet.Cells.FirstRowIndex + 1;//忽略第一行(标题行) rowIndex <= sheet.Cells.LastRowIndex; rowIndex++) { Row row = sheet.Cells.GetRow(rowIndex); com.Posion_id = posID; com.Dimension_id = dimID; string score_region = row.GetCell(0).StringValue.Trim(); if (score_region.Equals("偏高")) { com.Region_id = 1; } else if (score_region.Equals("适配")) { com.Region_id = 2; } else if (score_region.Equals("稍低")) { com.Region_id = 3; } else if (score_region.Equals("偏低")) { com.Region_id = 4; } for (int i = 1; i < sheet.Cells.LastColIndex + 1; i++) { com.Report_part_id = i; com.Comment_nm = row.GetCell(i).StringValue.Trim(); comBLL.Add(com); } } } return true; } catch (Exception ex) { MessageBox.Show(ex.Message); return false; } }
private void bind() { DimensionBLL dimensionBll = new DimensionBLL(); DataSet dsPosition = dimensionBll.GetDimensionAll(); ddlDeminsion.DataSource = dsPosition.Tables[0].DefaultView; ddlDeminsion.DataTextField = "dimension_name"; ddlDeminsion.DataValueField = "dimension_id"; ddlDeminsion.DataBind(); if (Request.QueryString["dimID"] != null) { if (!string.IsNullOrEmpty(Request.QueryString["dimID"])) { ddlDeminsion.SelectedValue = Request.QueryString["dimID"]; } } }
private void bind() { DimensionBLL dimensionBll = new DimensionBLL(); DataSet dsPosition = dimensionBll.GetDimensionAll(); ddlDimension.DataSource = dsPosition.Tables[0].DefaultView; ddlDimension.DataValueField = "dimension_id"; ddlDimension.DataTextField = "dimension_name"; ddlDimension.DataBind(); if (Request.QueryString["dimID"] != null) { ddlDimension.SelectedValue = Request.QueryString["dimID"]; } QuestionBLL quesitionBll = new QuestionBLL(); DataSet ds = quesitionBll.GetQuestionByDimension(ddlDimension.SelectedValue); grVQuestionManage.DataSource = ds.Tables[0].DefaultView; grVQuestionManage.DataSourceID = null; grVQuestionManage.DataBind(); quesitionBll = null; dimensionBll = null; }
private bool ImportQuestion(string xlsFileNm) { try { xlsFileNm = @"E:\Project\svn_all\exam-report\需求文档\功能调试版题库(维度仍旧在增加).xls"; if (!File.Exists(xlsFileNm)) return false; Workbook book = Workbook.Load(xlsFileNm); //Worksheet sheet = book.Worksheets[0]; DimensionBLL dimBLL = new DimensionBLL(); QuestionBLL qesBLL = new QuestionBLL(); Question question = new Question(); DataSet dimDS = dimBLL.GetDimensionAll(); foreach (Worksheet sheet in book.Worksheets) { if (sheet.Name.Trim().Equals("说明")) { continue; } string sheetNm = sheet.Name.Trim(); int dimID = -1;//在这里加断点可以监视单个维度的导入情况 foreach (DataRow row in dimDS.Tables[0].Rows) { if (row["dimension_name"].ToString().Equals(sheetNm)) { dimID = Int32.Parse(row["dimension_id"].ToString().Trim()); break; } } for (int rowIndex = sheet.Cells.FirstRowIndex + 2;//忽略第一行(标题行) rowIndex <= sheet.Cells.LastRowIndex; rowIndex++) { Row row = sheet.Cells.GetRow(rowIndex); question.Question_no = Int32.Parse(row.GetCell(0).StringValue.Trim()); question.Question_title = row.GetCell(5).StringValue.Trim(); question.Question_answer_A = row.GetCell(6).StringValue.Trim(); question.Question_answer_B = row.GetCell(7).StringValue.Trim(); question.Question_answer_C = row.GetCell(8).StringValue.Trim(); question.Question_answer_D = row.GetCell(9).StringValue.Trim(); question.Question_answer_E = row.GetCell(10).StringValue.Trim(); question.Question_answer_A_score = row.GetCell(11).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(11).StringValue.Trim()); question.Question_answer_B_score = row.GetCell(12).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(12).StringValue.Trim()); question.Question_answer_C_score = row.GetCell(13).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(13).StringValue.Trim()); question.Question_answer_D_score = row.GetCell(14).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(14).StringValue.Trim()); question.Question_answer_E_score = row.GetCell(15).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(15).StringValue.Trim()); question.Question_typeid = 1;//1 单选 question.Question_status = 1;//1 有效 if (dimID > 0) { question.Question_dimensionid = dimID; } else { MessageBox.Show("Error"); return false; } qesBLL.Add(question); } } MessageBox.Show("题目导入成功"); return true; } catch (Exception ex) { MessageBox.Show(ex.Message); return false; } }
private bool ImportQuestion(string xlsFileNm,ArrayList arDimID) { try { if (!File.Exists(xlsFileNm)) return false; Workbook book = Workbook.Load(xlsFileNm); DimensionBLL dimBLL = new DimensionBLL(); QuestionBLL qesBLL = new QuestionBLL(); Question question = new Question(); DataSet dimDS = dimBLL.GetDimensionAll(); //qesBLL.DeleteAllQuestion(); for (int k = 0; k < arDimID.Count; k++) { qesBLL.DeleteQuestionByDim(Int32.Parse(arDimID[k].ToString())); } string dName = string.Empty; for (int i = 0; i < arDimID.Count; i++) { dName = dimBLL.GetDimension(arDimID[i].ToString()).Dimnm; foreach (Worksheet sheet in book.Worksheets) { string sheetNm = sheet.Name.Trim(); if (sheetNm.Equals("说明")) { continue; } if (false == sheetNm.Equals(dName)) { continue; } int dimID = -1;//在这里加断点可以监视单个维度的导入情况 foreach (DataRow row in dimDS.Tables[0].Rows) { if (row["dimension_name"].ToString().Equals(sheetNm)) { dimID = Int32.Parse(row["dimension_id"].ToString().Trim()); break; } } if (dimID < 1) { string msg = "Excel工作表中存在的[" + sheet.Name + "]维度在系统维度表中不存在!" + Environment.NewLine + "请先确保Excel工作表中的维度名称在系统中存在,然后再执行导入操作"; //Response.Write("<script>...alert('" + msg + "')</script>"); MessageBox.ShowMessage(msg); continue; } for (int rowIndex = sheet.Cells.FirstRowIndex + 2;//忽略第一行(标题行) rowIndex <= sheet.Cells.LastRowIndex; rowIndex++) { Row row = sheet.Cells.GetRow(rowIndex); question.Question_no = Int32.Parse(row.GetCell(0).StringValue.Trim()); question.Question_title = row.GetCell(5).StringValue.Trim(); question.Question_answer_A = row.GetCell(6).StringValue.Trim(); question.Question_answer_B = row.GetCell(7).StringValue.Trim(); question.Question_answer_C = row.GetCell(8).StringValue.Trim(); question.Question_answer_D = row.GetCell(9).StringValue.Trim(); question.Question_answer_E = row.GetCell(10).StringValue.Trim(); question.Question_answer_A_score = row.GetCell(11).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(11).StringValue.Trim()); question.Question_answer_B_score = row.GetCell(12).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(12).StringValue.Trim()); question.Question_answer_C_score = row.GetCell(13).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(13).StringValue.Trim()); question.Question_answer_D_score = row.GetCell(14).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(14).StringValue.Trim()); question.Question_answer_E_score = row.GetCell(15).StringValue.Trim().Equals(string.Empty) ? 0 : Int32.Parse(row.GetCell(15).StringValue.Trim()); question.Question_typeid = 1;//1 单选 question.Question_status = 1;//1 有效 if (dimID > 0) { question.Question_dimensionid = dimID; } else { break; } qesBLL.Add(question); } } } return true; } catch (Exception ex) { string errMsg = ex.Message; return false; } }
protected void Page_Load(object sender, EventArgs e) { ((SiteSystemMaster)Master).InitMenu(); ((SiteSystemMaster)Master).li3.Attributes.Add("class", "hover ccc"); ((SiteSystemMaster)Master).b_hmenu1.Style.Add("display", "none"); ((SiteSystemMaster)Master).b_hmenu2.Style.Add("display", "none"); ((SiteSystemMaster)Master).b_hmenu3.Style.Clear(); ((SiteSystemMaster)Master).s_menu3_1.Style.Add("COLOR", "#147ab8"); ((SiteSystemMaster)Master).s_menu3_1.Style.Add("TEXT-DECORATION", "none"); /*判断是否是管理员身份*/ if (Session["username"] == null) { Page.ClientScript.RegisterStartupScript(this.GetType(), "PopupScript", "<script>alert('请先登录系统!');top.location.href='../System/Login.aspx';</script>"); return; } else { if (!IsPostBack) { DimensionBLL temp = new DimensionBLL(); DataSet ds = temp.GetDimensionAll(); grVDimensionManage.DataSource = ds.Tables[0].DefaultView; grVDimensionManage.DataSourceID = null; grVDimensionManage.DataBind(); temp = null; } this.btnImport.Attributes.Add("onclick", "return CheckConfirm();"); } }
protected void GridView1_Index(object sender,GridViewPageEventArgs e) { grVDimensionManage.PageIndex = e.NewPageIndex; DimensionBLL temp = new DimensionBLL(); DataSet ds = temp.GetDimensionAll(); grVDimensionManage.DataSource = ds.Tables[0].DefaultView; grVDimensionManage.DataSourceID = null; grVDimensionManage.DataBind(); temp = null; }