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>
/// 简历文件导出
/// </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>
/// 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="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>
/// 工作经历导出
/// </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!");
}
}
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="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("数据导入完成");
}
}