/// <summary>
/// getValue2
/// </summary>
/// <param name="row"></param>
/// <param name="startCol"></param>
/// <param name="endCol"></param>
/// <param name="excel"></param>
/// <returns></returns>
private string getValue2(int row, int startCol, int endCol, NCExcel excel)
{
string ret = "";
for (int idx = startCol; idx <= endCol; idx++)
{
ret += excel.getValue(idx, row);
}
return(ret);
}
/// <summary>
/// readTitle3
/// </summary>
/// <param name="excel"></param>
private void readTitle3(int startRow, string lessonId, NCExcel excel, string prefix)
{
string title = excel.getValue(1, startRow);
addContent(lessonId, "3", title, prefix
+ excel.getSheetName().Replace("第", "").Replace("课", "").Replace("課", "") + "/2.0.mp3",
"", "", "", "", "",
"", "", "", "",
"", "", "", "",
"", "", "", "",
"", "", "", "");
}
/// <summary>
/// 学习经历导入
/// </summary>
/// <param name="excel"></param>
/// <param name="resumeid"></param>
private void importStudyInfo(NCExcel excel, int resumeid, int startRow)
{
for (int idx = 0; idx < 15; idx++)
{
string school = excel.getValue(5, startRow + idx * 2); //对应 技术者履历书中的 大学名
//getvalue 表示获取指定对象的属性值
string special = excel.getValue(16, startRow + idx * 2); //对应 技术者履历书中的 专门
string enddate = excel.getValue(23, startRow + idx * 2); //对应 技术者履历书中的 卒業年月
string graduation = excel.getValue(30, startRow + idx * 2); //对应 技术者履历书中的 学歴
string fieldlist = "School,Special,EndDate,Grandation"; //分别对应数据库中学历表里的字段
string valuelist = "','" + school + "','" + special + "','" + enddate + "','"
+ graduation + "','";
int id = 0;
if (db.SetWork(0, 0, fieldlist,
"", valuelist, out id))
{
}
}
}
/// <summary>
/// readTail
/// </summary>
/// <param name="startRow"></param>
/// <param name="lessionId"></param>
/// <param name="excel"></param>
private void readTail(int startRow, string lessonId, NCExcel excel, string prefix)
{
for (int idx = startRow; ; idx++)
{
if (idx > 500)
{
break;
}
string title = excel.getValue(1, idx);
if (title == " ２、字幕閲読")
{
string content = excel.getValue(4, idx + 1);
addContent(lessonId, "5", content, prefix + "avi/"
+ excel.getSheetName().Replace("第", "").Replace("课", "").Replace("課", "") + ".avi",
"", "", "", "", "",
"", "", "", "",
"", "", "", "",
"", "", "", "",
"", "", "", "");
}
}
}
/// <summary>
/// 工作经历导入
/// </summary>
/// <param name="excel"></param>
/// <param name="resumeid"></param>
private void importWorkInfo(NCExcel excel, int resumeid, int startRow)
{
for (int idx = 0; idx < 15; idx++)
{
string range = excel.getValue(5, startRow + idx * 3); //对应 技术者履历书中的 期间
string company = excel.getValue(13, startRow + idx * 3); //对应 技术者履历书中的 会社名
string role = excel.getValue(26, startRow + idx * 3); //对应 技术者履历书中的 部门担当
string tool = excel.getValue(15, startRow + idx * 3) + "/" + excel.getValue(15, startRow + 1 + idx * 3) + "/" + excel.getValue(15, startRow + 2 + idx * 3);
if (!string.IsNullOrEmpty(range))
{
String fieldlist = "Range,Company,Role,Tool";// 对应数据库职历表中字段
String valuelist = "" + resumeid + ",'" + range + "','" + company + "','" + role + "','" + tool + "','";
int id = 0;
if (db.SetWork(0, 0, fieldlist,
"", valuelist, out id))
{
}
}
}
}
/// <summary>
/// 基本信息导入
/// </summary>
/// <param name="excel"></param>
private int importBaseInfo(NCExcel excel)
{
//数字是对应excel表里的坐标
string name = excel.getValue(5, 6); //对应 技术者履历书中的 氏名
string sex = excel.getValue(10, 6); //对应 技术者履历书中的 性别
string namekana = excel.getValue(5, 5); //对应 技术者履历书中的 フリガナ
string birthday = excel.getValue(12, 6); //对应 技术者履历书中的 生年月
string country = excel.getValue(20, 6); //对应 技术者履历书中的 国籍
string enterday = excel.getValue(23, 6); //对应 技术者履历书中的 来日年月
string nearstation = excel.getValue(27, 6); //对应 技术者履历书中的 自宅・最寄り駅
string fieldlist = "Name,Sex,NameKana,Birthday,Country,EnterDay,NearStation"; //分别对应数据库中雇员表里的字段
string valuelist = "','" + name + "','" + sex + "','" + namekana + "','"
+ birthday + "','" + country + "','" + enterday + "','" + nearstation + "','";
int id = 0;
if (db.SetEmp(0, 0, fieldlist, "", valuelist, out id))
{
return(id);
}
return(-1);
}
/// <summary>
/// 高级内容
/// </summary>
/// <param name="lessonId"></param>
/// <param name="excel"></param>
private Boolean readContentH(int startrow, string lessonId, NCExcel excel, int idx, ref int rowNum, string prefix)
{
string audiofile = prefix +
excel.getSheetName().Replace("第", "").Replace("课", "").Replace("課", "") + "/2." + idx.ToString() + ".mp3";
string block1 = excel.getValue(4, startrow);
if (block1 == "" || block1.Length > 10)
{
return(false);
}
string block2 = excel.getValue(7, startrow);
string[] block2a = new string[4];
string block3 = excel.getValue(11, startrow);
string[] block3a = new string[4];
string block4 = excel.getValue(17, startrow);
string[] block4a = new string[4];
string block5 = (excel.getValue(26, startrow)
+ excel.getValue(27, startrow)
+ excel.getValue(28, startrow)).Replace("→", "");
string[] block5a = new string[4];
string block6 = excel.getValue(39, startrow);
string[] block6a = new string[4];
int j = 0;
for (int i = 1; ; i++)
{
string block2_ = excel.getValue(7, startrow + i);
string block3_ = excel.getValue(11, startrow + i);
string block4_ = excel.getValue(17, startrow + i);
string block5_ = (excel.getValue(26, startrow + i)
+ excel.getValue(27, startrow + i)
+ excel.getValue(28, startrow + i)).Replace("→", "");
string block6_ = excel.getValue(39, startrow + i);
//下一行拼音为空或者到了第四块
if (block2_ == "" || j == 3)
{
//到了第四块
if (j == 3)
{
if (excel.getValue(6, startrow + i) != "")
{
block2a[j] = block2;
block3a[j] = block3 + block3a[j];
block4a[j] = block4 + block4a[j];
block5a[j] = block5 + block5a[j];
block6a[j] = block6 + block6a[j];
rowNum = i;
break;
}
else if (getValue2(startrow + i, 1, 39, excel).Trim() == "")
{
block2a[j] = block2;
block3a[j] = block3 + block3a[j];
block4a[j] = block4 + block4a[j];
block5a[j] = block5 + block5a[j];
block6a[j] = block6 + block6a[j];
rowNum = i;
break;
}
else
{
block3a[j] += ";" + block3_;
block4a[j] += ";" + block4_;
block5a[j] += ";" + block5_;
block6a[j] += ";" + block6_;
}
}
else
{
block3a[j] += ";" + block3_;
block4a[j] += ";" + block4_;
block5a[j] += ";" + block5_;
block6a[j] += ";" + block6_;
}
}
else
{
block2a[j] = block2;
block3a[j] = block3 + block3a[j];
block4a[j] = block4 + block4a[j];
block5a[j] = block5 + block5a[j];
block6a[j] = block6 + block6a[j];
j++;
block2 = block2_;
block3 = block3_;
block4 = block4_;
block5 = block5_;
block6 = block6_;
}
}
int spaceNum = 1;
string block7 = excel.getValue(6, startrow + rowNum);
if (block7 != "")
{
block7 += ";" + getValue2(startrow + rowNum, 11, 39, excel);
while (excel.getValue(6, startrow + rowNum + spaceNum) == "" &&
excel.getValue(4, startrow + rowNum + spaceNum) == "")
{
block7 += ";" + getValue2(startrow + rowNum + spaceNum, 11, 39, excel);
spaceNum++;
if (spaceNum > 10)
{
break;
}
}
}
else
{
while (excel.getValue(6, startrow + rowNum + spaceNum) == "" &&
excel.getValue(4, startrow + rowNum + spaceNum) == "")
{
spaceNum++;
if (spaceNum > 10)
{
break;
}
}
}
rowNum += spaceNum;
if (block1 != "")
{
addContent(lessonId, "4", block7, audiofile,
block1, block2a[0], block2a[1], block2a[2], block2a[3]
, block3a[0], block3a[1], block3a[2], block3a[3]
, block4a[0], block4a[1], block4a[2], block4a[3]
, block5a[0], block5a[1], block5a[2], block5a[3]
, block6a[0], block6a[1], block6a[2], block6a[3]
);
}
if (spaceNum > 10 || block1 == "")
{
return(false);
}
return(true);
}
/// <summary>
/// 初级内容
/// </summary>
/// <param name="lessonId"></param>
/// <param name="excel"></param>
private Boolean readContentL(int startrow, string lessonId, NCExcel excel, int idx, ref int rowNum, string prefix)
{
string audiofile = prefix +
excel.getSheetName().Replace("第", "").Replace("课", "").Replace("課", "") + "/2." + idx.ToString() + ".mp3";
string block1 = excel.getValue(3, startrow);
if (block1 == "" || block1.Length > 10)
{
return(false);
}
string block2 = excel.getValue(6, startrow);
if (block2 == "")
{
return(false);
}
string[] block2a = new string[4];
string block3 = excel.getValue(10, startrow);
string[] block3a = new string[4];
string block4 = excel.getValue(18, startrow);
if (excel.getValue(17, startrow) != "/")
{
block4 = excel.getValue(17, startrow) + block4;
}
if (excel.getValue(16, startrow) != "/")
{
block4 = excel.getValue(16, startrow) + block4;
}
string[] block4a = new string[4];
string block5 = excel.getValue(27, startrow);
if (excel.getValue(26, startrow) != "/")
{
block5 = excel.getValue(26, startrow) + block5;
}
string[] block5a = new string[4];
string block6 = excel.getValue(33, startrow)
+ excel.getValue(34, startrow)
+ excel.getValue(35, startrow)
+ excel.getValue(36, startrow)
+ excel.getValue(37, startrow);
string[] block6a = new string[4];
int j = 0;
for (int i = 1; ; i++)
{
//下一行读入
string block2_ = excel.getValue(6, startrow + i);
string block3_ = excel.getValue(10, startrow + i);
string block4_ = excel.getValue(18, startrow + i);
if (excel.getValue(17, startrow + i) != "/")
{
block4_ = excel.getValue(17, startrow + i) + block4_;
}
if (excel.getValue(16, startrow + i) != "/")
{
block4_ = excel.getValue(16, startrow + i) + block4_;
}
string block5_ = excel.getValue(27, startrow + i);
if (excel.getValue(26, startrow + i) != "/")
{
block5_ = excel.getValue(26, startrow + i) + block5_;
}
string block6_ = excel.getValue(33, startrow + i)
+ excel.getValue(34, startrow + i)
+ excel.getValue(35, startrow + i)
+ excel.getValue(36, startrow + i)
+ excel.getValue(37, startrow + i);
//下一行拼音为空或者到了第四块
if (block2_ == "" || j == 3)
{
//到了第四块
if (j == 3)
{
//下一行为簡体字練習
if (excel.getValue(5, startrow + i) != "")
{
block2a[j] = block2;//拼音设定
block3a[j] = block3 + block3a[j];
block4a[j] = block4 + block4a[j];
block5a[j] = block5 + block5a[j];
block6a[j] = block6 + block6a[j];
rowNum = i;
break;
}
//下一行汉字为空
else if (excel.getValue(10, startrow + i) == "")
{
block2a[j] = block2;//拼音设定
block3a[j] = block3 + block3a[j];
block4a[j] = block4 + block4a[j];
block5a[j] = block5 + block5a[j];
block6a[j] = block6 + block6a[j];
rowNum = i + 1;
break;
}
else
{
block3a[j] += ";" + block3_;
block4a[j] += ";" + block4_;
block5a[j] += ";" + block5_;
block6a[j] += ";" + block6_;
}
}
else//未到第四块则连接
{
block3a[j] += ";" + block3_;
block4a[j] += ";" + block4_;
block5a[j] += ";" + block5_;
block6a[j] += ";" + block6_;
}
}
//拼音发生变化
else
{
block2a[j] = block2;//拼音设定
block3a[j] = block3 + block3a[j];
block4a[j] = block4 + block4a[j];
block5a[j] = block5 + block5a[j];
block6a[j] = block6 + block6a[j];
j++;//块增加
//前一块值设定
block2 = block2_;
block3 = block3_;
block4 = block4_;
block5 = block5_;
block6 = block6_;
}
}
int spaceNum = 1;
string block7 = excel.getValue(5, startrow + rowNum);
if (block7 != "")
{
block7 += ";" + excel.getValue(10, startrow + rowNum) + ";"
+ excel.getValue(19, startrow + rowNum)
+ ";" + excel.getValue(28, startrow + rowNum);
while (excel.getValue(5, startrow + rowNum + spaceNum) == "" &&
excel.getValue(3, startrow + rowNum + spaceNum) == "")
{
block7 += ";" + excel.getValue(10, startrow + rowNum + spaceNum) + ";"
+ excel.getValue(19, startrow + rowNum + spaceNum)
+ ";" + excel.getValue(28, startrow + rowNum + spaceNum);
spaceNum++;
if (spaceNum > 10)
{
break;
}
}
}
else
{
while (excel.getValue(5, startrow + rowNum + spaceNum) == "" &&
excel.getValue(3, startrow + rowNum + spaceNum) == "")
{
spaceNum++;
if (spaceNum > 10)
{
break;
}
}
}
rowNum += spaceNum;
if (block1 != "")
{
addContent(lessonId, "4", block7, audiofile,
block1, block2a[0], block2a[1], block2a[2], block2a[3]
, block3a[0], block3a[1], block3a[2], block3a[3]
, block4a[0], block4a[1], block4a[2], block4a[3]
, block5a[0], block5a[1], block5a[2], block5a[3]
, block6a[0], block6a[1], block6a[2], block6a[3]
);
}
if (spaceNum > 10 || block1 == "")
{
return(false);
}
return(true);
}
/// <summary>
/// 文件导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnLoad_Click(object sender, EventArgs e)
{
if (!File.Exists(txtExcelFile.Text))
{
return;
}
if (cmbClass.Text != null)
{
string classId = ((DataTable)(cmbClass.DataSource)).Rows[cmbClass.SelectedIndex]["ClassId"].ToString();
deleteData(classId);
string prefix = "";
if (classId == "1")
{
prefix = AUDIOPATH_PREFIX_L;
}
else if (classId == "2")
{
prefix = AUDIOPATH_PREFIX_M;
}
else
{
prefix = AUDIOPATH_PREFIX_H;
}
NCExcel excel = new NCExcel();
excel.OpenExcelFile(txtExcelFile.Text);
progressBar1.Value = 0;
for (int i = 1; i <= 24; i++)
{
progressBar1.Value++;
Application.DoEvents();
excel.SelectSheet(i);
string lessonId = addLesson(classId, excel.getSheetName());
int startRow = 0;
for (startRow = 1; ; startRow++)
{
if (classId != "2")
{
if (excel.getValue(1, startRow).IndexOf("第") == 0)
{
readTitle(startRow, lessonId, excel, prefix);
}
if (excel.getValue(1, startRow).IndexOf("十六字訣") == 0 ||
excel.getValue(1, startRow).IndexOf("汉字口诀") == 0)
{
readTitle2(startRow, lessonId, excel, prefix);
}
if (excel.getValue(1, startRow).IndexOf("漢字子音") > 0 ||
excel.getValue(1, startRow).IndexOf("漢字母音") > 0)
{
readTitle3(startRow, lessonId, excel, prefix);
break;
}
}
else
{
if (excel.getValue(3, startRow).IndexOf("第") == 0)
{
readTitle(startRow, lessonId, excel, prefix);
}
if (excel.getValue(3, startRow).IndexOf("十六字訣") == 0 ||
excel.getValue(3, startRow).IndexOf("汉字口诀") == 0)
{
readTitle2(startRow, lessonId, excel, prefix);
}
if (excel.getValue(3, startRow).IndexOf("漢字子音") > 0 ||
excel.getValue(3, startRow).IndexOf("母音表") > 0)
{
readTitle3(startRow, lessonId, excel, prefix);
break;
}
}
}
startRow += 4;
int rowNum = 0;
int idx = 1;
while (readContent(classId, startRow, lessonId, excel, idx, ref rowNum, prefix))
{
startRow += rowNum;
idx++;
rowNum = 0;
}
readTail(50, lessonId, excel, prefix);
}
cmbClass_SelectedIndexChanged(null, null);
excel.Close();
MessageBox.Show("数据导入完成");
}
}
/// <summary>
/// 导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImport_Click(object sender, EventArgs e)
{
NCExcel excel = new NCExcel();
if (File.Exists(txtFile.Text))
{
excel.OpenExcelFile(txtFile.Text);
int recordNum = 0;
for (int i = 1; i < 10000; i++)
{
string id = excel.getValue(1, i + 1);
if (string.IsNullOrEmpty(id))
{
recordNum = i;
break;
}
}
pgbImport.Minimum = 1;
pgbImport.Maximum = recordNum;
for (int i = 1; i < recordNum; i++)
{
pgbImport.Value = i;
Application.DoEvents();
string id = excel.getValue(1, i + 1);
if (string.IsNullOrEmpty(id))
{
break;
}
string content = excel.getValue(2, i + 1);
string answerA = excel.getValue(3, i + 1);
string answerB = excel.getValue(4, i + 1);
string answerC = excel.getValue(5, i + 1);
string answerD = excel.getValue(6, i + 1);
string answer = excel.getValue(7, i + 1);
string score = excel.getValue(8, i + 1);
if (string.IsNullOrEmpty(score))
{
score = "5";
}
if (isQuestionExist(id))
{
string sql = "update tbl_Question_Single set "
+ "Content='" + content + "'"
+ ",SelectionA='" + answerA + "'"
+ ",SelectionB='" + answerB + "'"
+ ",SelectionC='" + answerC + "'"
+ ",SelectionD='" + answerD + "'"
+ ",Score=" + score + ""
+ ",Answer='" + answer + "'"
+ " where ID=" + id.ToString();
if (!db.ExeSQL(sql))
{
MessageBox.Show("数据更新失败！");
break;
}
}
else
{
string sql = "insert into tbl_Question_Single (Content, SelectionA,SelectionB,SelectionC,SelectionD,Score,Answer) values("
+ "'" + content + "'"
+ ",'" + answerA + "'"
+ ",'" + answerB + "'"
+ ",'" + answerC + "'"
+ ",'" + answerD + "'"
+ "," + score + ""
+ ",'" + answer + "')";
if (!db.ExeSQL(sql))
{
MessageBox.Show("数据追加失败！");
break;
}
}
}
excel.Close();
MessageBox.Show("数据导入完成！");
}
}