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