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(); }