public List <SurveyBaseInfo> LoadWorkList(string filePath, string accountConfigPath)
{
List <SurveyBaseInfo> Worklist = new List <SurveyBaseInfo>();
DataSet ul = new DataSet();
//Load station dictionary
StationNameTranslation snt = new StationNameTranslation();
snt.LoadDict();
//Prepare for connection to Excel
DataSet workListDataset = new DataSet();
try
{
Console.WriteLine("正在读取Excel文件...");
String sConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=yes; IMEX=0'";;
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = objCmdSelect;
objAdapter.Fill(workListDataset);
for (int i = 0; i < workListDataset.Tables[0].Rows.Count; i++)
{
DataRow dr = workListDataset.Tables[0].Rows[i];
try
{
Console.WriteLine("正在识别第" + (i + 1) + "行");
if (dr["乘车日期"].ToString() != "")
{
TrainInfo ti = new TrainInfo(Convert.ToDateTime(dr["乘车日期"]).ToString("yyyy-MM-dd").Substring(0, 10), dr["所乘车次"].ToString(), snt.GetTelegramCode(dr["票面乘车站"].ToString()), snt.GetTelegramCode(dr["票面下车站"].ToString()));
String username = dr["用户名"].ToString();
Worklist.Add(new SurveyBaseInfo(username, ti));
Console.WriteLine("成功识别第" + (i + 1) + "行");
}
else
{
Console.WriteLine("该行内容为空，跳过");
}
}
catch (Exception e) {
Console.WriteLine("识别第" + (i + 1) + "行失败，错误原因" + e.ToString());
String username = "******";
TrainInfo ti = new TrainInfo("###", e.ToString(), "###", "###");
Worklist.Add(new SurveyBaseInfo(username, ti));
}
}
}
catch (Exception e) {
Console.WriteLine("读取Excel文件失败，错误原因" + e.ToString());
}
return(Worklist);
}
public String Output(List <SurveyBaseInfo> successList, List <SurveyBaseInfo> failedList, List <String> resultList)
{
//Output Result to Console
StationNameTranslation snt = new StationNameTranslation();
snt.LoadDict();
DateTime CurrentTime = DateTime.Now;
String Filename = CurrentTime.ToString("yyyy-MM-dd HHmmss") + ".txt";
FileStream fs = new FileStream(Filename, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.GetEncoding("gb2312"));
sw.WriteLine("报告打印时间：" + CurrentTime.ToString());
sw.WriteLine("总条目：" + resultList.Count + " 成功" + successList.Count + "条，失败" + failedList.Count + "条");
sw.WriteLine("");
sw.WriteLine("1.问卷填写结果(按Excel中顺序每条一行，成功条目显示问卷号，失败条目显示失败原因)");
sw.WriteLine("");
foreach (String i in resultList)
{
sw.WriteLine(i);
}
sw.WriteLine("");
sw.WriteLine("===================");
sw.WriteLine("2.填写成功的问卷列表(可直接复制到Excel)");
sw.WriteLine("");
sw.WriteLine("乘车人用户名\t乘车日期\t所乘车次\t票面乘车站\t票面下车站\t问卷编号");
foreach (SurveyBaseInfo sbi in successList)
{
sw.WriteLine(sbi.UserName + "\t" + sbi.TravelRecord.TravelDate + "\t" + sbi.TravelRecord.TravelTrainNumber + "\t" + snt.GetStationName(sbi.TravelRecord.OnBoardStation) + "\t" + snt.GetStationName(sbi.TravelRecord.OffBoardStation) + "\t" + sbi.SurveyNumber);
}
sw.WriteLine("");
sw.WriteLine("===================");
sw.WriteLine("3.填写失败的问卷列表(可直接复制到Excel)");
sw.WriteLine("");
sw.WriteLine("乘车人用户名\t乘车日期\t所乘车次\t票面乘车站\t票面下车站\t失败原因");
foreach (SurveyBaseInfo sbi in failedList)
{
sw.WriteLine(sbi.UserName + "\t" + sbi.TravelRecord.TravelDate + "\t" + sbi.TravelRecord.TravelTrainNumber + "\t" + snt.GetStationName(sbi.TravelRecord.OnBoardStation) + "\t" + snt.GetStationName(sbi.TravelRecord.OffBoardStation) + "\t" + sbi.SurveyNumber);
}
sw.Close();
fs.Close();
return(Filename);
}
