private void OpenToData(string filePath)
{
int iMonth = DateTime.Now.Month;
string strDate = "";
if (comboBox1.SelectedIndex == 5)
{
strDate = "天津" + System.DateTime.Now.ToString("yyyy年") + "考勤";
iMonth = -1;
}
else
{
iMonth = iMonth - 2 + comboBox1.SelectedIndex;
strDate = "天津" + (iMonth < 0 ? (System.DateTime.Now.AddYears(-1).ToString("yyyy年") + (iMonth + 12).ToString()) : (System.DateTime.Now.ToString("yyyy年") + (iMonth).ToString())) + "月考勤";
}
try
{
DataToExcel("保存转换后的考勤表", strDate + "表", strDate, NameNumInfo.ReadNameToData(filePath, iMonth));
MessageBox.Show("考勤表转换完毕，接着统计信息", "统计");
DataToExcel("保存统计信息表", strDate + "信息", strDate + "统计", GatherDuty.GetDutyDataTable());
DialogResult dr = MessageBox.Show("统计完毕~要打开吗?", "统计完毕", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
System.Diagnostics.Process.Start(saveFileDialog1.FileName);
}
}
catch (Exception e)
{
MessageBox.Show("出现错误:" + e.Message + "在" + e.StackTrace + "，和你的亲爱的联系！", "出错了");
}
}
private void DataToExcel(string title, string sheetName, string filename, System.Data.DataTable dt)
{
saveFileDialog1.Filter = "EXCEL文件(*.xls)|*.xls";
saveFileDialog1.Title = title;
saveFileDialog1.FileName = filename;
saveFileDialog1.ShowDialog();
MessageBox.Show("亲~转换要一些时间哦", "温馨提示");
ApplicationClass ExcelApp = new ApplicationClass();
Workbook wb = ExcelApp.Workbooks.Add();
Worksheet st = ((Worksheet)wb.ActiveSheet);
st.Name = sheetName;
for (int i = 0; i < dt.Columns.Count; i++)
{
st.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
st.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
if (dt.Columns.Contains("EnNo"))
{
GatherDuty.GetDutyInfo(dt.Rows[i]);
st.Cells[i + 2, dt.Columns.Count + 1] = GatherDuty.GetRemark(dt.Rows[i]);
}
}
Microsoft.Office.Interop.Excel.Range oRange = st.get_Range(st.Cells[1, 1],
st.Cells[dt.Rows.Count + 1, dt.Columns.Count]);
oRange.EntireColumn.AutoFit();
wb.SaveAs(saveFileDialog1.FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value);
wb.Close(Missing.Value);
wb = null;
ExcelApp.Quit();
ExcelApp = null;
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}