/// <summary>
/// 將出勤統計資料滙出至EXCEL(Two Sheet)，並存在系統個人暫存區
/// </summary>
private void WPRtoExcelTemp()
{
//DataGridView沒有資料就不執行
if (dgvWorkPeopleReferShow.Rows.Count <= 1 & dgvWPRshow.Rows.Count <= 1)
{
MessageBox.Show("沒有可滙出的資料！", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
else
{
//建一個excel物件
Excel._Application excel = new Excel.Application();
//建一個excel物件下的工作簿
Excel._Workbook workbook = excel.Workbooks.Add();
//建二個excel物件下的工作表
Excel._Worksheet worksheet1 = excel.Worksheets.Add();
Excel._Worksheet worksheet2 = excel.Worksheets.Add();
//宣告表示欄位的英文和數字陣列
string[] EnRange = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M" };
string[] NumRange = new string[17];
//將數字帶入NumRange字串陣列
int q = 0;
for (int i = 0; i < NumRange.Count(); i++)
{
q++;
NumRange[i] = Convert.ToString(q);
}
try
{
string Date = DateTime.Now.ToString("yyyy-MM-dd");
string Temp = Path.GetTempPath();//取得使用者系統暫存路徑
string SaveFilePath = Temp + Date + ".xls";//檔案儲存的路徑
//Set Excel Sheet Name
worksheet1.Name = "未出勤名單";
worksheet2.Name = "人數統計";
PGB pgb = new PGB();
pgb.progressBar1.Minimum = 0;
pgb.progressBar1.Maximum = ((dgvWorkPeopleReferShow.Rows.Count - 1) + (dgvWPRshow.Rows.Count - 1) +
(EnRange.Count() * 2));
pgb.progressBar1.Step = 1;
pgb.progressBar1.Value = 0;
pgb.progressBar1.ForeColor = Color.Pink;
pgb.progressBar1.Style = ProgressBarStyle.Continuous;
pgb.Show();
//填入dgvWPRshow欄位名稱至worksheet1
for (int i = 0; i < dgvWPRshow.Columns.Count; i++)
{
worksheet1.Cells[1, i + 1] = dgvWPRshow.Columns[i].HeaderText;
}
//填入dgvWPRshow資料至worksheet1
for (int i = 0; i < dgvWPRshow.Rows.Count - 1; i++)
{
pgb.progressBar1.Value++;
for (int j = 0; j < dgvWPRshow.Columns.Count; j++)
{
if (dgvWPRshow[j, i].ValueType == typeof(string))
{
worksheet1.Cells[i + 2, j + 1] = "'" + dgvWPRshow[j, i].Value.ToString();
}
else
{
worksheet1.Cells[i + 2, j + 1] = dgvWPRshow[j, i].Value.ToString();
}
}
}
//填入dgvWorkPeopleReferShow欄位名稱至worksheet2
for (int i = 0; i < dgvWorkPeopleReferShow.Columns.Count; i++)
{
worksheet2.Cells[1, i + 1] = dgvWorkPeopleReferShow.Columns[i].HeaderText;
}
//填入dgvWorkPeopleReferShow資料至worksheet2
for (int i = 0; i < dgvWorkPeopleReferShow.Rows.Count - 1; i++)
{
pgb.progressBar1.Value++;
for (int j = 0; j < dgvWorkPeopleReferShow.Columns.Count; j++)
{
if (dgvWorkPeopleReferShow[j, i].ValueType == typeof(string))
{
worksheet2.Cells[i + 2, j + 1] = "'" + dgvWorkPeopleReferShow[j, i].Value.ToString();
}
else
{
worksheet2.Cells[i + 2, j + 1] = dgvWorkPeopleReferShow[j, i].Value.ToString();
}
}
}
//設定滙出後，欄位寛度自動配合資料調整
worksheet1.Cells.EntireRow.AutoFit();
worksheet2.Cells.EntireRow.AutoFit();
//自動調整列高
worksheet1.Cells.EntireColumn.AutoFit();
worksheet2.Cells.EntireColumn.AutoFit();
//用字串陣列下去設定worksheet2欄位格式
for (int i = 0; i < EnRange.Count(); i++)
{
pgb.progressBar1.Value++;
for (int x = 0; x < NumRange.Count(); x++)
{
//設定欄位垂直置中
worksheet2.get_Range(EnRange[i] + NumRange[x]).VerticalAlignment = Excel.XlHAlign.
xlHAlignCenter;
//設定欄位水平置中
worksheet2.get_Range(EnRange[i] + NumRange[x]).HorizontalAlignment = Excel.XlHAlign.
xlHAlignCenter;
//設定欄位框線
worksheet2.get_Range(EnRange[i] + NumRange[x]).Borders.LineStyle = 1;
//設定欄位框線顏色
worksheet2.get_Range(EnRange[i] + NumRange[x]).Borders.Color = Color.White;
//設定字型顏色為White
worksheet2.get_Range(EnRange[i] + NumRange[x]).Font.Color = Color.White;
//設定欄位背景顏色為DodgerBlue
worksheet2.get_Range(EnRange[i] + NumRange[x]).Interior.Color = Color.DodgerBlue;
}
}
//用字串陣列下去設定worksheet1欄位格式
for (int i = 0; i < EnRange.Count()-3; i++)
{
pgb.progressBar1.Value++;
for (int x = 0; x < NumRange.Count(); x++)
{
if (EnRange[i] == "A")
{
worksheet1.get_Range(EnRange[i] + NumRange[x]).ColumnWidth = 10;
worksheet1.get_Range(EnRange[i] + NumRange[x]).HorizontalAlignment = Excel.XlHAlign.
xlHAlignCenter;
}
else if(EnRange[i]=="J")
{
worksheet1.get_Range(EnRange[i] + NumRange[x]).ColumnWidth = 36;
worksheet1.get_Range(EnRange[i] + NumRange[x]).WrapText = true;//設定欄位自動換行
}
else
{
worksheet1.get_Range(EnRange[i] + NumRange[x]).ColumnWidth = 20;
worksheet1.get_Range(EnRange[i] + NumRange[x]).WrapText = true;//設定欄位自動換行
}
worksheet1.get_Range(EnRange[i] + NumRange[x]).VerticalAlignment = Excel.XlHAlign.
xlHAlignCenter;
worksheet1.get_Range(EnRange[i] + NumRange[x]).Borders.LineStyle = 1;
worksheet1.get_Range(EnRange[i] + NumRange[x]).Borders.Color = Color.White;
worksheet1.get_Range(EnRange[i] + NumRange[x]).Font.Color = Color.White;
worksheet1.get_Range(EnRange[i] + NumRange[x]).Interior.Color = Color.DodgerBlue;
}
}
//在第一列插入新列
((Excel.Range)worksheet1.Cells[1, 1]).EntireRow.Insert(null, null);
((Excel.Range)worksheet2.Cells[1, 1]).EntireRow.Insert(null, null);
//設定worksheet1(A1)欄位格式
worksheet1.Select();//先選取worksheet1，才能設定凍結窗格
worksheet1.Application.ActiveWindow.SplitRow = 2;//設定凍結列數
worksheet1.Application.ActiveWindow.SplitColumn = 1;//設定凍結欄數
worksheet1.Cells[1, 1].Application.ActiveWindow.FreezePanes = true;//設定從那個欄位開始凍結
worksheet1.get_Range("A1", "J1").Merge(worksheet1.get_Range("A1", "J1").MergeCells);//合併欄位
worksheet1.get_Range("A1").Value = "長鴻電子-每日未出勤名單";
worksheet1.get_Range("A1").Font.Size = 22;
worksheet1.get_Range("A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
worksheet1.get_Range("A1").Borders.LineStyle = 1;
worksheet1.get_Range("A1").Font.Color = Color.White;
worksheet1.get_Range("A1").Interior.Color = Color.DodgerBlue;
//設定worksheet2(A1)欄位格式
worksheet2.Select();
worksheet2.get_Range("A1", "M1").Merge(worksheet2.get_Range("A1", "M1").MergeCells);
worksheet2.get_Range("A1").Value = "長鴻電子-每日出勤人數統計表";
worksheet2.get_Range("A1").Font.Size = 22;
worksheet2.get_Range("A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
worksheet2.get_Range("A1").Borders.LineStyle = 1;
worksheet2.get_Range("A1").Font.Color = Color.White;
worksheet2.get_Range("A1").Interior.Color = Color.DodgerBlue;
//設定worksheet2最下方二列的格式並填值進去
int RowNum = dgvWorkPeopleReferShow.Rows.Count + 2;
worksheet2.get_Range("D" + RowNum, "H" + RowNum).Merge(worksheet2.get_Range("D" + RowNum, "H" +
RowNum).MergeCells);
worksheet2.get_Range("I" + RowNum, "M" + RowNum).Merge(worksheet2.get_Range("I" + RowNum, "M" +
RowNum).MergeCells);
worksheet2.get_Range("D" + RowNum, "H" + RowNum).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
worksheet2.get_Range("D" + RowNum, "H" + RowNum).Borders.LineStyle = 1;
worksheet2.get_Range("D" + RowNum, "H" + RowNum).Font.Size = 16;
worksheet2.get_Range("D" + RowNum, "H" + RowNum).Borders.Color = Color.White;
worksheet2.get_Range("D" + RowNum, "H" + RowNum).Font.Color = Color.White;
worksheet2.get_Range("D" + RowNum, "H" + RowNum).Interior.Color = Color.DodgerBlue;
worksheet2.get_Range("I" + RowNum, "M" + RowNum).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
worksheet2.get_Range("I" + RowNum, "M" + RowNum).Borders.LineStyle = 1;
worksheet2.get_Range("I" + RowNum, "M" + RowNum).Font.Size = 16;
worksheet2.get_Range("I" + RowNum, "M" + RowNum).Borders.Color = Color.White;
worksheet2.get_Range("I" + RowNum, "M" + RowNum).Font.Color = Color.White;
worksheet2.get_Range("I" + RowNum, "M" + RowNum).Interior.Color = Color.DodgerBlue;
worksheet2.get_Range("D" + RowNum, "H" + RowNum).Value = lblAllUser.Text + lblAllUserShow.Text;
worksheet2.get_Range("I" + RowNum, "M" + RowNum).Value = lblRealUser.Text + lblRealUserShow.Text;
RowNum++;//+1=換下一列
worksheet2.get_Range("D" + RowNum, "M" + RowNum).Merge(worksheet2.get_Range("D" + RowNum, "M" +
RowNum).MergeCells);
worksheet2.get_Range("D" + RowNum, "M" + RowNum).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
worksheet2.get_Range("D" + RowNum, "M" + RowNum).Borders.LineStyle = 1;
worksheet2.get_Range("D" + RowNum, "M" + RowNum).Font.Size = 16;
worksheet2.get_Range("D" + RowNum, "M" + RowNum).Borders.Color = Color.White;
worksheet2.get_Range("D" + RowNum, "M" + RowNum).Font.Color = Color.White;
worksheet2.get_Range("D" + RowNum, "M" + RowNum).Interior.Color = Color.DodgerBlue;
worksheet2.get_Range("D" + RowNum, "M" + RowNum).Value = lblTodayAttendance.Text +
lblTodayAttendanceShow.Text;
//設置禁止彈出覆蓋或儲存的彈跳視窗
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
//將檔案儲存到SaveFile指定的位置，儲存前先判斷系統上的Office版本號
if (excel.Application.Version == "11.0")//Office 2003
{
excel.ActiveWorkbook.SaveAs(SaveFilePath);
}
else
{
//Office 2003 Up，FileFormat: Excel.XlFileFormat.xlExcel8=>指定Excel 2003 xls格式
excel.ActiveWorkbook.SaveAs(SaveFilePath, FileFormat: Excel.XlFileFormat.xlExcel8);
}
pgb.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//關閉工作簿和結束Excel程式
workbook.Close();
excel.Quit();
//釋放資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
workbook = null;
worksheet1 = null;
worksheet2 = null;
GC.Collect();
}
}
}
private void btnSendToExcel_Click(object sender, EventArgs e)
{
Excel.Application excel = null;
try
{
//DataGridView沒有資料就不執行
if (this.dgvReferOrderAll.Rows.Count <= 1)
{
MessageBox.Show("沒有可滙出的資料！", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
string Date = DateTime.Now.ToString("yyyy-MM-dd");
//設定滙出後的存檔路徑(儲存在桌面)
string SaveFilePath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) +
@"\BentoReport_" + Date + ".xls";
//new 出一個Excel
excel = new Microsoft.Office.Interop.Excel.Application();
//看的到Excel在工作
excel.Visible = false;
//新增加一工作簿
excel.Application.Workbooks.Add(true);
PGB pgb = new PGB();
pgb.progressBar1.Minimum = 0;
pgb.progressBar1.Maximum = dgvReferOrderAll.Rows.Count - 1;
pgb.progressBar1.Step = 1;
pgb.progressBar1.Value = 0;
pgb.progressBar1.ForeColor = Color.Pink;
pgb.progressBar1.Style = ProgressBarStyle.Continuous;
pgb.Show();
//寫入欄位名稱
for (int i = 0; i < dgvReferOrderAll.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dgvReferOrderAll.Columns[i].HeaderText;
}
//把DataGridView資料寫到Excel
for (int i = 0; i < dgvReferOrderAll.Rows.Count - 1; i++)
{
pgb.progressBar1.Value++;
for (int j = 0; j < dgvReferOrderAll.Columns.Count; j++)
{
if (dgvReferOrderAll[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgvReferOrderAll[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgvReferOrderAll[j, i].Value.ToString();
}
}
//設定欄位靠右
excel.get_Range("A" + (i + 2)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.
xlHAlignRight;
excel.get_Range("C" + (i + 2)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.
xlHAlignRight;
//設定欄位顏色
excel.get_Range("A" + (i + 2)).Interior.Color = Color.Pink;
excel.get_Range("B" + (i + 2)).Interior.Color = Color.Pink;
excel.get_Range("C" + (i + 2)).Interior.Color = Color.Pink;
excel.get_Range("D" + (i + 2)).Interior.Color = Color.Pink;
//設定欄位框線
excel.get_Range("A" + (i + 2)).Borders.LineStyle = 1;
excel.get_Range("B" + (i + 2)).Borders.LineStyle = 1;
excel.get_Range("C" + (i + 2)).Borders.LineStyle = 1;
excel.get_Range("D" + (i + 2)).Borders.LineStyle = 1;
}
/*
先將DataGridView的Rows總數給變數aa，以利後續透過aa+1的方式來新增要加入的資料
(將DataGridView資料轉至Excel後，在Rows下方插入TextBox.Text)
*/
int aa = dgvReferOrderAll.Rows.Count + 1;
excel.Cells[aa, 1] = "[葷]數量：";
excel.Cells[aa, 2] = txtTotalOrder0.Text;
excel.Cells[aa, 3] = "[素]數量：";
excel.Cells[aa, 4] = txtTotalOrder1.Text;
//設定欄位靠右
excel.get_Range("A" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
//設定欄位顏色
excel.get_Range("A" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("B" + aa).Interior.Color = Color.MediumPurple;
excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple;
//設定欄位字體顏色
excel.get_Range("A" + aa).Font.Color = Color.Snow;
excel.get_Range("B" + aa).Font.Color = Color.Snow;
excel.get_Range("C" + aa).Font.Color = Color.Snow;
excel.get_Range("D" + aa).Font.Color = Color.Snow;
//設定欄位字體為粗體
excel.get_Range("A" + aa).Font.Bold = true;
excel.get_Range("B" + aa).Font.Bold = true;
excel.get_Range("C" + aa).Font.Bold = true;
excel.get_Range("D" + aa).Font.Bold = true;
aa = aa + 1;
excel.Cells[aa, 1] = "[葷]金額：";
excel.Cells[aa, 2] = txtTotalPrice0.Text;
excel.Cells[aa, 3] = "[素]金額：";
excel.Cells[aa, 4] = txtTotalPrice1.Text;
excel.get_Range("A" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
excel.get_Range("A" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("B" + aa).Interior.Color = Color.MediumPurple;
excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple;
excel.get_Range("A" + aa).Font.Color = Color.Snow;
excel.get_Range("B" + aa).Font.Color = Color.Snow;
excel.get_Range("C" + aa).Font.Color = Color.Snow;
excel.get_Range("D" + aa).Font.Color = Color.Snow;
excel.get_Range("A" + aa).Font.Bold = true;
excel.get_Range("B" + aa).Font.Bold = true;
excel.get_Range("C" + aa).Font.Bold = true;
excel.get_Range("D" + aa).Font.Bold = true;
aa = aa + 1;
excel.Cells[aa, 3] = "總金額：";
excel.Cells[aa, 4] = txtTotalPriceAll.Text;
excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple;
excel.get_Range("C" + aa).Font.Color = Color.Snow;
excel.get_Range("D" + aa).Font.Color = Color.Snow;
excel.get_Range("C" + aa).Font.Bold = true;
excel.get_Range("D" + aa).Font.Bold = true;
//設定滙出後，欄位寛度自動配合資料調整
excel.Cells.EntireRow.AutoFit();
//自動調整列高
excel.Cells.EntireColumn.AutoFit();
//將所有欄位做垂直置中
excel.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
//將A1~D1的欄位做水平置中
excel.get_Range("A1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.get_Range("B1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.get_Range("C1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.get_Range("D1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//設定A1~D1欄位框線
excel.get_Range("A1").Borders.LineStyle = 1;
excel.get_Range("B1").Borders.LineStyle = 1;
excel.get_Range("C1").Borders.LineStyle = 1;
excel.get_Range("D1").Borders.LineStyle = 1;
excel.get_Range("A1").Font.Color = Color.White;
excel.get_Range("A1").Interior.Color = Color.DodgerBlue;
excel.get_Range("B1").Font.Color = Color.White;
excel.get_Range("B1").Interior.Color = Color.DodgerBlue;
excel.get_Range("C1").Font.Color = Color.White;
excel.get_Range("C1").Interior.Color = Color.DodgerBlue;
excel.get_Range("D1").Font.Color = Color.White;
excel.get_Range("D1").Interior.Color = Color.DodgerBlue;
/*
excel.get_Range("E1").Font.Color = Color.White;
excel.get_Range("E1").Interior.Color = Color.DodgerBlue;
excel.get_Range("F1").Font.Color = Color.White;
excel.get_Range("F1").Interior.Color = Color.DodgerBlue;
excel.get_Range("G1").Font.Color = Color.White;
excel.get_Range("G1").Interior.Color = Color.DodgerBlue;
excel.get_Range("H1").Font.Color = Color.White;
excel.get_Range("H1").Interior.Color = Color.DodgerBlue;
excel.get_Range("I1").Font.Color = Color.White;
excel.get_Range("I1").Interior.Color = Color.DodgerBlue;
excel.get_Range("J1").Font.Color = Color.White;
excel.get_Range("J1").Interior.Color = Color.DodgerBlue;
excel.get_Range("K1").Font.Color = Color.White;
excel.get_Range("K1").Interior.Color = Color.DodgerBlue;
excel.get_Range("L1").Font.Color = Color.White;
excel.get_Range("L1").Interior.Color = Color.DodgerBlue;
excel.get_Range("M1").Font.Color = Color.White;
excel.get_Range("M1").Interior.Color = Color.DodgerBlue;
excel.get_Range("N1").Font.Color = Color.White;
excel.get_Range("N1").Interior.Color = Color.DodgerBlue;
excel.get_Range("O1").Font.Color = Color.White;
excel.get_Range("O1").Interior.Color = Color.DodgerBlue;
*/
//設置禁止彈出覆蓋或儲存的彈跳視窗
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
//將檔案儲存到SaveFile指定的位置
excel.ActiveWorkbook.SaveCopyAs(SaveFilePath);
pgb.Close();
MessageBox.Show("已成功滙出Excel檔！" + Environment.NewLine + "檔案儲存在您電腦的桌面，檔名：BentoReport_" +
Date + ".xls", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//關閉工作簿和結束Excel程式
excel.Workbooks.Close();
excel.Quit();
//釋放資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect();
}
