/// <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>
/// 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="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("数据导入完成");
}
}
