/// <summary> /// 方法名称: Dispose /// 内容描述: 如果对Excel的操作没有引发异常的话,用这个方法可以正常结束Excel进程 /// 否则要用KillExcelProcess()方法来结束Excel进程 /// 实现流程: /// 作 者: 林付国 /// 日 期: 2006-5-11 9:24:46 /// </summary> public void Dispose() { //注意:这里用到的所有Excel对象都要执行这个操作,否则结束不了Excel进程 if (wb != null) { wb.Close(null, null, null); app.Workbooks.Close(); app.Quit(); } if (rng != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(rng); rng = null; } if (tb != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(tb); tb = null; } if (ws != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(ws); ws = null; } if (wb != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(wb); wb = null; } if (app != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; } GC.Collect(); }
/// <summary> /// 向文本框写入数据,对指定WorkSheet操作 /// </summary> /// <param name="ht">Hashtable的键值对保存文本框的ID和数据</param> public void SetTextBoxes(int sheetIndex, Hashtable ht) { if (ht.Count == 0) return; if (sheetIndex > this.WorkSheetCount) { this.KillExcelProcess(); throw new Exception("索引超出范围,WorkSheet索引不能大于WorkSheet数量!"); } workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex); foreach (DictionaryEntry dic in ht) { try { textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key); textBox.Text = dic.Value.ToString(); } catch { this.KillExcelProcess(); throw new Exception("不存在ID为\"" + dic.Key.ToString() + "\"的文本框!"); } } }
private void Dispose() { workBook.Close(null, null, null); app.Workbooks.Close(); app.Quit(); if (range != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range); range = null; } if (range1 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range1); range1 = null; } if (range2 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range2); range2 = null; } if (textBox != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(textBox); textBox = null; } if (workSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); workSheet = null; } if (workBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); workBook = null; } if (app != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; } GC.Collect(); this.KillExcelProcess(); }
/// <summary> /// 向文本框写入数据,对每个WorkSheet操作 /// </summary> /// <param name="ht">Hashtable的键值对保存文本框的ID和数据</param> public void SetTextBoxes(Hashtable ht) { if (ht.Count == 0) return; for (int i = 1; i <= this.WorkSheetCount; i++) { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); foreach (DictionaryEntry dic in ht) { try { textBox = (Excel.TextBox)workSheet.TextBoxes(dic.Key); textBox.Text = dic.Value.ToString(); } catch { this.KillExcelProcess(); throw new Exception("不存在ID为\"" + dic.Key.ToString() + "\"的文本框!"); } } } }
/// <summary> /// 向指定文本框写入数据,对指定WorkSheet操作 /// </summary> /// <param name="sheetIndex">工作表索引</param> /// <param name="textboxName">文本框名称</param> /// <param name="text">要写入的文本</param> public void SetTextBox(int sheetIndex, string textboxName, string text) { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex); try { textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName); textBox.Text = text; } catch { this.KillExcelProcess(); throw new Exception("不存在ID为\"" + textboxName + "\"的文本框!"); } }
/// <summary> /// 向指定文本框写入数据,对每个WorkSheet操作 /// </summary> /// <param name="textboxName">文本框名称</param> /// <param name="text">要写入的文本</param> public void SetTextBox(string textboxName, string text) { for (int i = 1; i <= this.WorkSheetCount; i++) { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); try { textBox = (Excel.TextBox)workSheet.TextBoxes(textboxName); textBox.Text = text; } catch { this.KillExcelProcess(); throw new Exception("不存在ID为\"" + textboxName + "\"的文本框!"); } } }
///------------------------------------------------------------------ /// <summary> /// 出勤簿印刷・シート追加一括印刷 </summary> ///------------------------------------------------------------------ private void sReport() { const int S_GYO = 7; //エクセルファイル日付明細開始行 //開始日付 int StartDay = 1; //終了日付 int EndDay = DateTime.DaysInMonth(int.Parse(txtYear.Text) + Utility.GetRekiHosei(), int.Parse(txtMonth.Text)); string sDate; DateTime eDate; //////const int S_ROWSMAX = 7; //エクセルファイル列最大値 try { //マウスポインタを待機にする this.Cursor = Cursors.WaitCursor; string sAppPath = System.AppDomain.CurrentDomain.BaseDirectory; Excel.Application oXls = new Excel.Application(); Excel.Workbook oXlsBook = (Excel.Workbook)(oXls.Workbooks.Open(Properties.Settings.Default.OCR出勤簿シートパス, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)); Excel.Worksheet oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[1]; Excel.Range[] rng = new Microsoft.Office.Interop.Excel.Range[2]; try { // 休日配列インスタンス化 Config.Holiday[] Holiday = new Config.Holiday[1]; Holiday[0] = new Config.Holiday(); Holiday[0].hDate = DateTime.Parse("1900/01/01"); // ローカルデータベース接続 SysControl.SetDBConnect Con = new SysControl.SetDBConnect(); OleDbCommand sCom = new OleDbCommand(); sCom.Connection = Con.cnOpen(); OleDbDataReader dR; // 休日データを配列に読み込む int iH = 0; string sqlSTRING = string.Empty; sqlSTRING += "select * from 休日 order by 年月日"; sCom.CommandText = sqlSTRING; dR = sCom.ExecuteReader(); while (dR.Read()) { if (iH > 0) { Array.Resize(ref Holiday, iH + 1); // 配列要素数追加 Holiday[iH] = new Config.Holiday(); // 休日配列インスタンス化 } Holiday[iH].hDate = DateTime.Parse(dR["年月日"].ToString()); if (dR["月給者"].ToString() == "1") { Holiday[iH].Gekkyuu = true; } else { Holiday[iH].Gekkyuu = false; } if (dR["時給者"].ToString() == "1") { Holiday[iH].Jikyuu = true; } else { Holiday[iH].Jikyuu = false; } iH++; } dR.Close(); sCom.Connection.Close(); // ページカウント int pCnt = 0; //// 有給記号非表示設定 : 2017/11/27 //Excel.TextBox t34 = oxlsSheet.TextBoxes("Text Box 32"); //t34.Visible = false; //oxlsSheet.Cells[5, 5] = string.Empty; // パートのとき有給記号非表示設定 : 2017/11/30 if (_YakushokuType == global.STATUS_PART) { Excel.TextBox t34 = oxlsSheet.TextBoxes("Text Box 32"); t34.Visible = false; oxlsSheet.Cells[5, 5] = string.Empty; } //Excel.TextBox t14 = oxlsSheet.TextBoxes("テキスト 14"); //t14.Visible = false; //Excel.TextBox t34 = oxlsSheet.TextBoxes("yukyukigo"); //Excel.TextBox t14 = oxlsSheet.TextBoxes("kyukakigo"); //if (_tBoxStatus) //{ // t34.Visible = false; // t14.Visible = false; // oxlsSheet.Cells[5, 5] = string.Empty; //} //else //{ // t34.Visible = true; // t14.Visible = true; // oxlsSheet.Cells[5, 5] = "有給"; //} // グリッドを順番に読む for (int i = 0; i < dg1.RowCount; i++) { // チェックがあるものを対象とする if (dg1[0, i].Value.ToString() == "True") { // 印刷2件目以降はシートを追加する pCnt++; if (pCnt > 1) { oxlsSheet.Copy(Type.Missing, oXlsBook.Sheets[pCnt - 1]); oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[pCnt]; } int sRow = i; // 帳票区分(1:メイン勤務出勤簿、2:サブ勤務出勤簿) // 配列から取得 2017/05/17 //if (radioButton1.Checked) //{ // oxlsSheet.Cells[2, 1] = PR_STATUS_MAIN.ToString(); //} //else //{ // oxlsSheet.Cells[2, 1] = PR_STATUS_SUB.ToString(); //} oxlsSheet.Cells[2, 1] = dg1[5, sRow].Value.ToString(); // 年 oxlsSheet.Cells[3, 4] = string.Format("{0, 2}", int.Parse(txtYear.Text)).Substring(0, 1); oxlsSheet.Cells[3, 5] = string.Format("{0, 2}", int.Parse(txtYear.Text)).Substring(1, 1); // 月 oxlsSheet.Cells[3, 8] = string.Format("{0, 2}", int.Parse(txtMonth.Text)).Substring(0, 1); oxlsSheet.Cells[3, 9] = string.Format("{0, 2}", int.Parse(txtMonth.Text)).Substring(1, 1); // 所属名 oxlsSheet.Cells[3, 15] = dg1[2, sRow].Value.ToString(); // 所属コード string szCode = dg1[1, sRow].Value.ToString().PadLeft(global.ShozokuMaxLength, ' '); for (int ci = 0; ci < szCode.Length; ci++) { oxlsSheet.Cells[3, 25 + ci] = szCode.Substring(ci, 1); } //oxlsSheet.Cells[3, 27] = dg1[1, sRow].Value.ToString().Substring(0, 1); //oxlsSheet.Cells[3, 28] = dg1[1, sRow].Value.ToString().Substring(1, 1); //oxlsSheet.Cells[3, 29] = dg1[1, sRow].Value.ToString().Substring(2, 1); // 社員番号 //for (int ci = 0; ci < global.ShainLength; ci++) //{ // oxlsSheet.Cells[2, 25 + ci] = dg1[3, sRow].Value.ToString().Substring(ci, 1); //} oxlsSheet.Cells[2, 25] = dg1[3, sRow].Value.ToString().Substring(0, 1); oxlsSheet.Cells[2, 26] = dg1[3, sRow].Value.ToString().Substring(1, 1); oxlsSheet.Cells[2, 27] = dg1[3, sRow].Value.ToString().Substring(2, 1); oxlsSheet.Cells[2, 28] = dg1[3, sRow].Value.ToString().Substring(3, 1); oxlsSheet.Cells[2, 29] = dg1[3, sRow].Value.ToString().Substring(4, 1); // 氏名 oxlsSheet.Cells[2, 15] = dg1[4, sRow].Value.ToString(); // 日付 int addRow = 0; for (int iX = StartDay; iX <= EndDay; iX++) { // 暦補正値は設定ファイルから取得する sDate = (int.Parse(txtYear.Text) + Utility.GetRekiHosei()).ToString() + "/" + txtMonth.Text + "/" + iX.ToString(); eDate = DateTime.Parse(sDate); oxlsSheet.Cells[S_GYO + addRow, 2] = ("日月火水木金土").Substring(int.Parse(eDate.DayOfWeek.ToString("d")), 1); rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO + addRow, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[S_GYO + addRow, 2]; // 日曜日なら曜日の背景色を変える if (rng[1].Text.ToString() == "日") { oxlsSheet.get_Range(rng[0], rng[1]).Interior.Color = Color.LightGray; } // 祝祭日なら曜日の背景色を変える for (int j = 0; j < Holiday.Length; j++) { // 休日登録されている if (Holiday[j].hDate == eDate) { // 月給者または時給者が各々休日対象となっている if (dg1[5, sRow].Value.ToString() == "社員" && Holiday[j].Gekkyuu == true || dg1[5, sRow].Value.ToString() == "パート" && Holiday[j].Jikyuu == true) { oxlsSheet.get_Range(rng[0], rng[1]).Interior.Color = Color.LightGray; break; } else { oxlsSheet.get_Range(rng[0], rng[1]).Interior.Color = Color.White; } } } // 行数加算 addRow++; } } } // マウスポインタを元に戻す this.Cursor = Cursors.Default; // 確認のためExcelのウィンドウを表示する //oXls.Visible = true; // 印刷 oXlsBook.PrintOut(); // ウィンドウを非表示にする oXls.Visible = false; // 保存処理 oXls.DisplayAlerts = false; // Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); // Excelを終了 oXls.Quit(); } catch (Exception e) { MessageBox.Show(e.Message, "印刷処理", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); // ウィンドウを非表示にする oXls.Visible = false; // 保存処理 oXls.DisplayAlerts = false; // Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); // Excelを終了 oXls.Quit(); } finally { // COM オブジェクトの参照カウントを解放する System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(oXlsBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(oXls); //マウスポインタを元に戻す this.Cursor = Cursors.Default; } } catch (Exception e) { MessageBox.Show(e.Message, "印刷処理", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } // マウスポインタを元に戻す this.Cursor = Cursors.Default; }
/// <summary> /// 方法名称: Dispose /// 内容描述: 如果对Excel的操作没有引发异常的话,用这个方法可以正常结束Excel进程 /// 否则要用KillExcelProcess()方法来结束Excel进程 /// 实现流程: /// 作 者: 林付国 /// 日 期: 2006-5-11 9:24:46 /// </summary> public void Dispose() { //注意:这里用到的所有Excel对象都要执行这个操作,否则结束不了Excel进程 if(wb != null) { wb.Close(null,null,null); app.Workbooks.Close(); app.Quit(); } if(rng != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(rng); rng = null; } if(tb != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(tb); tb = null; } if(ws != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(ws); ws = null; } if(wb != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(wb); wb = null; } if(app != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; } GC.Collect(); }