//轉成和泰格式
private void btnExportToExcel_Click(object sender, EventArgs e)
{
try
{
//如果要匯出的不只一天
if (excelright)
{
lockScreen();
Lib.ExcelExport ex = new Lib.ExcelExport();
ex.SetColumeName(new string[] { "員工卡號", "刷卡日期", "刷卡時間", "進出別" });
ex.SetNumberFormat("C1", "C" + dtforExl.Rows.Count + 1, "0000");
ex.SetNumberFormat("D1", "D" + dtforExl.Rows.Count + 1, "00");
//ex.myDGV = dataGridView1;
ex.ExportExcel(this.dtforExl);
unlockScreen();
}
else
{
MessageBox.Show("請先點匯出資料");
unlockScreen();
}
}
catch (Exception ex)
{
unlockScreen();
MessageBox.Show(ex.ToString());
}
}
//多日轉成和泰格式
private void btnToMultiExcel_Click(object sender, EventArgs e)
{
try
{
if (dtpStart.Value != dtpEnd.Value)
{
lockScreen();
List <DataTable> dtList = new List <DataTable>();
DateTime dtstart = dtpStart.Value.Date;
DateTime dtend = dtpEnd.Value.Date;
while (dtstart <= dtend)
{
string strstart = dtstart.ToString("yyyy-MM-dd") + " 00:00:00";
string strend = dtstart.ToString("yyyy-MM-dd") + " 23:59:00";
DataTable dt01 = checkExportData01(strstart, strend);
DataTable dt02 = checkExportData02(strstart, strend);
var data = (from q in dt01.AsEnumerable()
select new ExportData
{
員工卡號 = "#" + q.Field <string>("EMPLOYEE_ID").PadLeft(10, '0'),
刷卡日期 = q.Field <DateTime>("DATE_TIME_MIN").ToString("yyyy-MM-dd").Replace("-", ""),
刷卡時間 = q.Field <DateTime>("DATE_TIME_MIN").ToString("HH:mm").Replace(":", "").PadLeft(4, '0'),
進出別 = q.Field <string>("DATE_TIME_TYPE"),
}).Union(
from p in dt02.AsEnumerable()
select new ExportData
{
員工卡號 = "#" + p.Field <string>("EMPLOYEE_ID").PadLeft(10, '0'),
刷卡日期 = p.Field <DateTime>("DATE_TIME_MAX").ToString("yyyy-MM-dd").Replace("-", ""),
刷卡時間 = p.Field <DateTime>("DATE_TIME_MAX").AddMinutes(1).ToString("HH:mm").Replace(":", "").PadLeft(4, '0'),
進出別 = p.Field <string>("DATE_TIME_TYPE"),
});
dtList.Add(ConvertToDataTable <ExportData>(data.ToList <ExportData>()));
dtstart = dtstart.AddDays(1);
}
Lib.ExcelExport ex = new Lib.ExcelExport();
ex.SetColumeName(new string[] { "員工卡號", "刷卡日期", "刷卡時間", "進出別" });
ex.MiltiExportExcel(dtList, "出勤_管理者");
unlockScreen();
}
else
{
return;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
unlockScreen();
}
}
private void btnMakeExcel_Click(object sender, EventArgs e)
{
try
{
Lib.ExcelExport ex = new Lib.ExcelExport();
ex.SetColumeName(new string[] { "員工卡號", "刷卡日期", "刷卡時間", "進出別" });
ex.SetNumberFormat("C1", "C" + dtforExl.Rows.Count + 1, "0000");
ex.SetNumberFormat("D1", "D" + dtforExl.Rows.Count + 1, "00");
//ex.myDGV = dataGridView1;
ex.ExportExcel(this.dtforExl);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
//個人出缺勤匯出Excel
private void btnPersonalToExcel_Click(object sender, EventArgs e)
{
try
{
////宣告EXCEL工具
//MemoryStream ms = new MemoryStream(Resources.Aspose.Aspose_Excel_License);
//Aspose.Cells.License license = new Aspose.Cells.License();
//license.SetLicense(ms);
//Workbook newWbookNur = new Workbook();
////載入樣板資料
//newWbookNur.Open(Request.MapPath("~/CDS/HR/HRAttendance/HTreport/HTReport.xls"));
//string sheetNameNur = string.Format("{0}{1}", "出勤", "_管理者");
//newWbookNur.Worksheets.Add();
//newWbookNur.Worksheets[0].Name = sheetNameNur;//設定頁籤名稱
//Cells cellsA = newWbookNur.Worksheets[0].Cells;//頁籤1的CELL
//cellsA.Clear();
//cellsA.ImportDataTable(dtcopy, true, 0, 0, dtcopy.Rows.Count, dtcopy.Columns.Count);
////產生檔案
//MemoryStream stream = new MemoryStream();
//newWbookNur.Save(stream, FileFormatType.Default);
#region "aspose.dll"
//Workbook newbook = new Workbook();
//newbook.Open(Application.StartupPath +"\\HTReport.xls");
//newbook.Worksheets.Add();
//newbook.Worksheets[0].Name = "個人出勤";
//Cells cellA = newbook.Worksheets[0].Cells;
//cellA.Clear();
//cellA.ImportDataTable(dtforExl, true, 0, 0, dtforExl.Rows.Count, dtforExl.Columns.Count);
//SaveFileDialog saveFileDialog1 = new SaveFileDialog();
//saveFileDialog1.Filter = "(*.xls)";
//if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
//{
// MemoryStream stream = new MemoryStream();
// //newbook.Save(stream, FileFormatType.Default);
// newbook.Save(saveFileDialog1.FileName, FileFormatType.Default);
// //System.Diagnostics.Process.Start(saveFileDialog1.FileName);
//}
#endregion
lockScreen();
Lib.ExcelExport ex = new Lib.ExcelExport();
ex.SetColumeName(new string[] { "刷卡日期", "原編", "部門單位", "上班時間", "下班時間" });
//ex.worksheet.Name = "123456";
ex.SetNumberFormat("D1", "D" + dtforExl.Rows.Count + 1, "0000");
ex.SetNumberFormat("E1", "E" + dtforExl.Rows.Count + 1, "0000");
//ex.myDGV = dataGridView1;
ex.ExportExcel(this.dtforExl);
unlockScreen();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
