// 按签到序号清空指定用户的签到信息
public static int recoverSignupUserItem(int ID)
{
try
{
List <SignupUserItem> list;
Dictionary <String, String[]> conditions = new Dictionary <String, String[]>();
conditions.Add("ID", new String[] { new Operator(OperatorTypeEnum.Equal), ID.ToString() });
int ret = DbSelect.getSignupList(out list, conditions);
if (ret != OK)
{
throw new Exception(String.Format("不存在序号为\"{0}\"的记录。", ID));
}
using (SqlConnection conn = ConnectDb())
{
SqlCommand cmd = new SqlCommand(String.Format(sql_RecoverSignupUserItem_Update, ID), conn);
cmd.ExecuteNonQuery();
cmd = new SqlCommand(String.Format(sql_RecoverSignupUserItem_Delete, ID), conn);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(String.Format("清空指定用户的签到信息时出错：{0}", ex.Message));
}
return(OK);
}
// 导出签到名单到Excel文件
public static int exportSignupList(String excelFile, Dictionary <String, String[]> conditions)
{
try
{
List <QuestionItem> listQuestions;
List <SignupUserItem> list;
DbSelect.getQuestionList(out listQuestions, null);
// 读取签到名单
DbSelect.getSignupList(out list, conditions);
// 生成Excel文件
SpreadsheetClass factory = new SpreadsheetClass();
Worksheet sheet = factory.ActiveSheet;
List <string> columnHeaderNames = new List <string> {
"签到编号", "姓名", "身份证号码", "手机号码", "签到时间"
};
List <int> columnHeaderWidth = new List <int> {
10, 15, 12, 15
};
int columnHeaderQuestionWidth = 42;
listQuestions.ForEach((QuestionItem q) =>
{
columnHeaderNames.Add(q.Question);
columnHeaderWidth.Add(columnHeaderQuestionWidth);
});
sheet.Name = String.Format("签到名单({0}.{1})", DateTime.Today.Year, DateTime.Today.Month.ToString("00"));
// 字段名
for (int i = 0; i < columnHeaderNames.Count; i++)
{
Range col = sheet.get_Range((char)('A' + i) + "1");
sheet.Cells[1, i + 1] = columnHeaderNames[i];
col.set_ColumnWidth(columnHeaderWidth[i]);
col.set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
col.Font.set_Bold(true);
}
// 记录
for (int i = 0, ROW_OFFSET = 2, COL_OFFSET = 1; i < list.Count; i++)
{
sheet.Cells[ROW_OFFSET + i, COL_OFFSET] = list[i].AssignID;
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + 1] = list[i].Name;
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + 2] = '\'' + list[i].CardID;
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + 3] = '\'' + list[i].Mobile;
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + 4] = list[i].SignupTime.ToString();
if (list[i].Answers.Count == listQuestions.Count)
{
for (int j = 0; j < listQuestions.Count; j++)
{
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + j + 4] = list[i].Answers[j].Answer;
}
}
}
factory.Export(excelFile, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportXMLSpreadsheet);
return(OK);
}
catch (Exception ex)
{
throw new Exception(String.Format("导出签到名单时出错：{0}", ex.Message));
}
}