/// <summary>
/// 简历文件导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExport_Click(object sender, EventArgs e)
{
if (dgvData.SelectedRows.Count > 0)
{
int resumeid = int.Parse(dgvData.SelectedRows[0].Cells[1].Value.ToString());
string templatefile = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "技術者履歴書_模板.xlsx");
if (!File.Exists(templatefile))
{
MessageBox.Show("テンプレートファイルが存在しません。");
return;
}
SaveFileDialog dlg = new SaveFileDialog();
if (dlg.ShowDialog() == DialogResult.OK)
{
NCExcel excel = new NCExcel();
excel.OpenExcelFile(templatefile);
excel.SelectSheet(1);
ExportBaseInfo(excel, resumeid);
ExportStudyInfo(excel, resumeid);
ExportWorkInfo(excel, resumeid);
excel.SaveAs(dlg.FileName);
excel.Close();
}
}
}
/// <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);
}
private void btnExcel_Click(object sender, EventArgs e)
{
SaveFileDialog dlg = new SaveFileDialog();
if (dlg.ShowDialog() == DialogResult.OK)
{
string fileName = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "テーブル定義書.xls");
NCExcel execel = new NCExcel();
execel.OpenExcelFile(fileName);
execel.SelectSheet(1);
int idx = 7;
// add table list
foreach (Tbl tbl in tables)
{
execel.setValue(2, idx, (idx - 6).ToString());
execel.setValue(4, idx, tbl.name);
idx++;
}
// add sheet
execel.AddSheet(tables);
// add sheet data
for (int i = 0; i < tables.Count; i++)
{
execel.SelectSheet(i + 2);
execel.setValue(2, 2, "HighwayHR");
execel.setValue(4, 2, "HRWeb");
execel.setValue(9, 2, "Highwayns");
execel.setValue(11, 2, DateTime.Now.ToString("yyyy/MM/dd"));
execel.setValue(2, 4, tables[i].name);
idx = 7;
for (int j = 0; j < tables[i].fields.Count; j++)
{
execel.setValue(2, idx, (idx - 6).ToString());
execel.setValue(3, idx, tables[i].fields[j]);
execel.setValue(4, idx, tables[i].fields_type[j]);
execel.setValue(5, idx, tables[i].fields_size[j].Replace("(", "").Replace(")", ""));
if (tables[i].fields_null[j] == "NOT NULL")
{
execel.setValue(7, idx, "○");
}
if (tables[i].pk.IndexOf(tables[i].fields[j]) > -1)
{
execel.setValue(8, idx, "○");
}
execel.setValue(9, idx,
tables[i].fields_increase[j] + "/"
+ tables[i].fields_default[j] + "/"
+ tables[i].fields_sign[j]);
idx++;
}
}
execel.SaveAs(dlg.FileName);
MessageBox.Show("Save Over!");
}
}
/// <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>
/// file Export
/// </summary>
/// <param name="fileName"></param>
private void FileExport(string fileName)
{
int resumeid = int.Parse(dgvData.SelectedRows[0].Cells[1].Value.ToString());
string templatefile = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "技術者履歴書_模板.xlsx");
NCExcel excel = new NCExcel();
excel.OpenExcelFile(templatefile);
excel.SelectSheet(1);
ExportBaseInfo(excel, resumeid);
ExportWorkInfo(excel, resumeid);
excel.SaveAs(fileName);
}
/// <summary>
/// 学习经历导出
/// </summary>
/// <param name="excel"></param>
/// <param name="resumeid"></param>
private void ExportStudyInfo(NCExcel excel, int resumeid)
{
DataSet ds = new DataSet();
if (db.GetSchool(0, 0, "[School],[Special],[Graduation],[StartDate],[EndDate]", "EmpId=" + resumeid.ToString(), "StartDate Desc", ref ds) &&
ds.Tables[0].Rows.Count > 0)
{ //获取技术者履历表中的 最终学历
excel.setValue(5, 9, ds.Tables[0].Rows[0]["School"].ToString()); //对应 技术者履历书 中的大学名
excel.setValue(16, 9, ds.Tables[0].Rows[0]["Special"].ToString()); //对应 技术者履历书 中的専門
excel.setValue(23, 9, ds.Tables[0].Rows[0]["EndDate"].ToString()); //对应 技术者履历书 中的卒業年月
excel.setValue(30, 9, ds.Tables[0].Rows[0]["Graduation"].ToString()); //对应 技术者履历书 中的学历
}
}
/// <summary>
/// readContent
/// </summary>
/// <param name="classId"></param>
/// <param name="startrow"></param>
/// <param name="lessonId"></param>
/// <param name="excel"></param>
/// <param name="idx"></param>
/// <param name="rowNum"></param>
/// <returns></returns>
private Boolean readContent(string classId, int startrow, string lessonId, NCExcel excel, int idx, ref int rowNum, string prefix)
{
if (classId == "1")
{
return(readContentL(startrow, lessonId, excel, idx, ref rowNum, prefix));
}
else if (classId == "2")
{
return(readContentM(startrow, lessonId, excel, idx, ref rowNum, prefix));
}
else
{
return(readContentH(startrow, lessonId, excel, idx, ref rowNum, prefix));
}
}
private void btnConvertToPdf_Click(object sender, EventArgs e)
{
string dbt_wfile = "";
OpenFileDialog dlg = new OpenFileDialog();
if (dlg.ShowDialog() == DialogResult.OK)
{
dbt_wfile = dlg.FileName;
string pdf_file = Path.ChangeExtension(dbt_wfile, ".pdf");
string ext = Path.GetExtension(dbt_wfile).Replace(".", "");
switch (ext.ToLower())
{
case "docx":
case "doc":
if (!File.Exists(pdf_file))
{
NCWord.ConvertWordPDF(dbt_wfile, pdf_file);
}
break;
case "xlsx":
case "xls":
if (!File.Exists(pdf_file))
{
NCExcel excel = new NCExcel();
excel.OpenExcelFile(dbt_wfile);
excel.SaveAsPDF(pdf_file);
excel.Close();
}
break;
case "pptx":
case "ppt":
if (!File.Exists(pdf_file))
{
NCPPT ppt = new NCPPT();
if (ppt.PPTOpen(dbt_wfile))
{
ppt.PPTSaveAsPDF(pdf_file);
ppt.PPTClose();
}
}
break;
}
}
}
/// <summary>
/// 基本信息导出
/// </summary>
/// <param name="excel"></param>
private void ExportBaseInfo(NCExcel excel, int resumeid)
{
DataSet ds = new DataSet();
if (db.GetEmp(0, 0, "Name,NameKana,Sex,Birthday,NearStation,Country,EnterDay", "EmpId=" + resumeid.ToString(), "", ref ds) &&
ds.Tables[0].Rows.Count == 1)
{
excel.setValue(30, 4, DateTime.Now.ToString()); //对应 技术者履历书作成日
excel.setValue(5, 5, ds.Tables[0].Rows[0]["NameKana"].ToString()); //对应 技术者履历书 中的フリガナ
excel.setValue(5, 6, ds.Tables[0].Rows[0]["Name"].ToString()); //对应 技术者履历书 中的氏名
excel.setValue(10, 6, ds.Tables[0].Rows[0]["Sex"].ToString()); //对应 技术者履历书 中的性别
excel.setValue(12, 6, ds.Tables[0].Rows[0]["Birthday"].ToString()); //对应 技术者履历书 中的生年月
excel.setValue(20, 6, ds.Tables[0].Rows[0]["Country"].ToString()); //对应 技术者履历书 中的国籍
excel.setValue(23, 6, ds.Tables[0].Rows[0]["EnterDay"].ToString()); //对应 技术者履历书 中的入场日期
excel.setValue(27, 6, ds.Tables[0].Rows[0]["NearStation"].ToString()); //对应 技术者履历书 中的自宅・最寄り駅
}
}
/// <summary>
/// 工作经历导出
/// </summary>
/// <param name="excel"></param>
/// <param name="resumeid"></param>
private void ExportWorkInfo(NCExcel excel, int resumeid)
{
DataSet ds = new DataSet();
if (db.GetWork(0, 0, "[StartDate],[EndDate],[Country],[Content],[Tool],[Role],[Range]", "EmpId=" + resumeid.ToString(), "StartDate", ref ds) &&
ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
int startRow = 58;//从第58行开始
int idx = i;
if (i > 14)
{
excel.SelectSheet(2); //选择excel文件表中的第二张表
startRow = 9; //从第九行开始
idx = i - 15;
}
excel.setValue(3, startRow + 1 + idx * 3, ds.Tables[0].Rows[idx]["StartDate"].ToString()); //对应技术者履历书中的 期间（开始日期）
excel.setValue(3, startRow + 2 + idx * 3, ds.Tables[0].Rows[idx]["EndDate"].ToString());
excel.setValue(6, startRow + 1 + idx * 3, ds.Tables[0].Rows[idx]["Country"].ToString()); //国家
excel.setValue(7, startRow + idx * 3, ds.Tables[0].Rows[idx]["Content"].ToString()); //对应技术者履历书中的 システム名その他
excel.setValue(15, startRow + idx * 3, ds.Tables[0].Rows[idx]["Tool"].ToString().Split('/')[0]);
if (ds.Tables[0].Rows[idx]["Tool"].ToString().Split('/').Length > 2)
{
excel.setValue(15, startRow + 1 + idx * 3, ds.Tables[0].Rows[idx]["Tool"].ToString().Split('/')[1]);
excel.setValue(15, startRow + 2 + idx * 3, ds.Tables[0].Rows[idx]["Tool"].ToString().Split('/')[2]);
}
excel.setValue(22, startRow + 1 + idx * 3, ds.Tables[0].Rows[idx]["Role"].ToString());//对应技术者履历书中的 担当
string range = ds.Tables[0].Rows[idx]["Range"].ToString();
if (!string.IsNullOrEmpty(range))
{
string[] data = range.Split(':');
if (data.Length == 11)
{
for (int j = 0; j < 11; j++)
{
excel.setValue(23 + j, startRow + 1 + idx * 3, data[j]);
}
}
}
}
}
}
/// <summary>
/// Create Publish
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnPublish_Click(object sender, EventArgs e)
{
SaveFileDialog dlg = new SaveFileDialog();
if (dlg.ShowDialog() == DialogResult.OK)
{
string fileName = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "コンサルテントテンプレート.xlsx");
NCExcel execel = new NCExcel();
execel.OpenExcelFile(fileName);
execel.SelectSheet(1);
int idx = 2;
// add table list
foreach (DataGridViewRow row in dgvData.Rows)
{
string companyName = row.Cells[1].Value.ToString();
string departName = row.Cells[14].Value.ToString();
string manager = row.Cells[2].Value.ToString();
string postcode = row.Cells[3].Value.ToString();
string address = row.Cells[4].Value.ToString();
string tel = row.Cells[5].Value.ToString();
string fax = row.Cells[6].Value.ToString();
string mail = row.Cells[12].Value.ToString();
string web = row.Cells[13].Value.ToString();
string comment = row.Cells[11].Value.ToString();
execel.setValue(1, idx, companyName);
execel.setValue(2, idx, departName);
execel.setValue(3, idx, comment);
execel.setValue(4, idx, manager);
execel.setValue(5, idx, mail);
execel.setValue(6, idx, postcode);
execel.setValue(7, idx, address);
execel.setValue(8, idx, tel);
execel.setValue(12, idx, fax);
execel.setValue(13, idx, web);
idx++;
}
execel.SaveAs(dlg.FileName);
MessageBox.Show("Save ExcelOver!\r\n there are " + dgvData.Rows.Count + " record!");
}
}
/// <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>
/// 基本信息导入
/// </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="sender"></param>
/// <param name="e"></param>
private void btnLoad_Click(object sender, EventArgs e)
{
if (!File.Exists(txtExcelFile.Text))
{
MessageBox.Show("導入ファイルが存在しません。");
return;
}
NCExcel excel = new NCExcel();
excel.OpenExcelFile(txtExcelFile.Text);
excel.SelectSheet(1);
//MessageBox.Show("名前："+excel.getValue(5, 6));
int resumeid = importBaseInfo(excel);
if (resumeid > 0)
{
importWorkInfo(excel, resumeid, 58);
excel.SelectSheet(2);
importWorkInfo(excel, resumeid, 9);
}
excel.Close();
}
/// <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))
{
}
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
SaveFileDialog dlg = new SaveFileDialog();
if (dlg.ShowDialog() == DialogResult.OK)
{
string fileName = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "お客様テンプレート.xlsx");
NCExcel execel = new NCExcel();
execel.OpenExcelFile(fileName);
execel.SelectSheet(1);
int idx = 3;
// add table list
foreach (DataGridViewRow row in dgvData.Rows)
{
string comment = row.Cells[0].Value.ToString();
string companyName = row.Cells[1].Value.ToString();
string departName = row.Cells[2].Value.ToString();
string address = row.Cells[3].Value.ToString();
companyName = companyName + departName;
string[] strs = companyName.Split(' ');
if (strs.Length == 2)
{
companyName = strs[0];
departName = strs[1];
}
execel.setValue(2, idx, companyName);
execel.setValue(3, idx, departName);
execel.setValue(6, idx, address);
execel.setValue(7, idx, comment);
idx++;
}
execel.SaveAs(dlg.FileName);
MessageBox.Show("Save Over!");
}
}
/// <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 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("数据导入完成！");
}
}
/// <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("数据导入完成");
}
}
