/// <summary> /// 打印预览工作薄 /// </summary> public void PrintPreview() { Microsoft.Office.Interop.Excel.Worksheet ws = MyExcel.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; if (ws != null) { ws.PrintPreview(true); } }
public void PrintPriviewExcelFile(string filePath) { ApplicationClass xlApp = new ApplicationClass(); xlApp.Visible = true; object oMissing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, 0, true, 5, oMissing, oMissing, true, 1, oMissing, false, false, oMissing, false, oMissing, oMissing); Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets[1]; xlWorksheet.PrintPreview(null); xlApp.Visible = false; xlWorksheet = null; }
public void createMonthlyFE(List <Entities.SAAOModel> Monthly, string month) { ExApp = new Excel.Application(); ExApp.Visible = false; ExBook = ExApp.Workbooks.Open("C:\\Monthly.xlsx"); ExSheet = (Excel.Worksheet)ExBook.Sheets[2]; ////////FINANCIAL EXPENSES MONTHLY // Month, Year ExSheet.Cells[3, 1] = month + DateTime.Now.Year.ToString(); /////// FINANCIAL EXPENSES //50301040 ExSheet.Cells[12, 6] = Monthly[0].AB; ExSheet.Cells[12, 9] = Monthly[0].Amount; // Month ExSheet.Cells[7, 9] = month; try { ExSheet.SaveAs("DBMS\\Monthly\\FE\\" + month + "_REPORT_FE.xlsx"); } catch { } finally { if (MessageBox.Show("Do you want to continue to printing?", "Print?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { ExSheet.PrintOutEx(); } ExApp.Visible = true; ExSheet.PrintPreview(); ExBook.Close(); } }
/// <summary> /// //////////////////////////////////////////////////////////////////////////////////////////////// /// </summary> private void DoExcelThings() { string old_ser, ser = ""; OracleCommand cmd1; OracleDataReader dr1; string date_bill, ch_date; excelFilePath = Path.GetFullPath("invoice_t.xlsx"); openExcel(); OracleCommand cmd = con.CreateCommand(); cmd.CommandText = $"select * from table_billing where num_of_bill = {ID}"; cmd.CommandType = CommandType.Text; OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); date_bill = dr[1].ToString(); ch_date = date_bill[6].ToString() + date_bill[7].ToString() + '.'; ch_date = ch_date + date_bill[4].ToString() + date_bill[5].ToString() + '.'; ch_date = ch_date + date_bill[0].ToString() + date_bill[1].ToString() + date_bill[2].ToString() + date_bill[3].ToString(); myExcelWorkSheet.Cells[1, 2].Value = "СЧЕТ НА ОПЛАТУ"; myExcelWorkSheet.Cells[2, "B"].Value = $"№ {ID} от {ch_date}"; date_bill = dr[4].ToString(); ch_date = date_bill[6].ToString() + date_bill[7].ToString() + '.'; ch_date = ch_date + date_bill[4].ToString() + date_bill[5].ToString() + '.'; ch_date = ch_date + date_bill[0].ToString() + date_bill[1].ToString() + date_bill[2].ToString() + date_bill[3].ToString(); myExcelWorkSheet.Cells[3, "B"].Value = $"к договору {dr[0].ToString()} от {ch_date}"; cmd1 = con.CreateCommand(); cmd1.CommandText = $"select CRP_NM, REG_ADDR_CONT from tbcb_crp_info where CRP_CD = '{dr[5]}'"; cmd1.CommandType = CommandType.Text; dr1 = cmd1.ExecuteReader(); dr1.Read(); myExcelWorkSheet.Cells[6, "AX"].Value = dr1[0].ToString(); myExcelWorkSheet.Cells[8, "AX"].Value = dr1[1].ToString(); dr1.Close(); myExcelWorkSheet.Cells[10, "AX"].Value = $"{dr[7]}"; if (dr[8].ToString() == "") { myExcelWorkSheet.Cells[12, "AX"].Value = "\t" + dr[9].ToString(); } else { myExcelWorkSheet.Cells[12, "AX"].Value = "\t" + dr[8].ToString(); } cmd1 = con.CreateCommand(); cmd1.CommandText = $"Select bk_acnt_no, mfo_cd from tbcb_crp_bk where crp_cd = '{dr[5]}'"; cmd1.CommandType = CommandType.Text; dr1 = cmd1.ExecuteReader(); dr1.Read(); myExcelWorkSheet.Cells[14, "AX"].Value = $"{dr1[0]}"; myExcelWorkSheet.Cells[16, "AX"].Value = $"{dr1[1]}"; string num_double = dr[11].ToString(); bool flag = false; var DS = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator[0]; string val = "", frac = "";; for (int i = 0; i < num_double.Count(); i++) { if (num_double[i] != DS && flag == false) { val += num_double[i]; } else if (num_double[i] != DS && flag == true) { frac += num_double[i]; } else if (num_double[i] == DS) { flag = true; } } if (flag == false) { frac = "00"; } myExcelWorkSheet.Cells[27, "J"].Value = $"{dr[16]}"; // комментарий myExcelWorkSheet.Cells[29, "J"].Value = $"{dr[17]}"; // основание myExcelWorkSheet.Cells[37, "AE"].Value = $"{dr[15]}"; dr1.Close(); ser = dr[3].ToString(); //old_ser = dr[2].ToString(); //flag = false; //for (int i = 0; i < old_ser.Count(); i++) //{ // if (old_ser[i] == '/') // { // flag = true; // } // else if (old_ser[i] != '/' && flag == true) // { // ser += old_ser[i]; // } //} cmd1 = con.CreateCommand(); cmd1.CommandText = $"select NDS from tbcb_cd where CD like '{ser}%' AND CD_NM = '{dr[10].ToString()}'"; cmd1.CommandType = CommandType.Text; dr1 = cmd1.ExecuteReader(); dr1.Read(); double percent = double.Parse(dr1[0].ToString()); dr1.Close(); myExcelWorkSheet.Cells[22, "AE"].Value = $"{dr[18]}"; ///валюта myExcelWorkSheet.Cells[22, "D"].Value = $"{dr[10]}"; //услуга myExcelWorkSheet.Cells[22, "AI"].Value = double.Parse(dr[11].ToString().Replace('.', DS)); myExcelWorkSheet.Cells[22, "BF"].Value = double.Parse(dr[11].ToString().Replace('.', DS)); double sum_without_NDS; sum_without_NDS = double.Parse(dr[11].ToString().Replace('.', DS)) / (1 + percent / 100); myExcelWorkSheet.Cells[22, "AO"].Value = sum_without_NDS; MessageBox.Show(RusNumber.Str(Int32.Parse(val))); if (percent == 0) { myExcelWorkSheet.Cells[22, "AZ"].Value = "БЕЗ НДС"; myExcelWorkSheet.Cells[22, "AW"].Value = "БЕЗ НДС"; myExcelWorkSheet.Cells[25, "B"].Value = "Всего к оплате: " + RusNumber.Str(Int32.Parse(val)) + dr[18].ToString() + " " + frac + " тийин"; } else { myExcelWorkSheet.Cells[22, "AZ"].Value = double.Parse(dr[11].ToString().Replace('.', DS)) - sum_without_NDS; myExcelWorkSheet.Cells[22, "AW"].Value = $"{percent}%"; // процент myExcelWorkSheet.Cells[25, "B"].Value = "Всего к оплате: " + RusNumber.Str(Int32.Parse(val)) + dr[18].ToString() + " " + frac + " тийин, в.т.ч. НДС: " + Math.Round(myExcelWorkSheet.Cells[22, "AZ"].Value, 2) + " " + dr[18].ToString(); } dr.Close(); myExcelApplication.Visible = true; // true will open Excel myExcelWorkSheet.PrintPreview(); myExcelApplication.Visible = false; // hides excel file when user closes preview }
public void CreateFile(System.Data.DataSet ds, string Dept_Code) { object missing = System.Reflection.Missing.Value; object fileName = "normal.dot"; object newTemplate = false; object docType = 0; object isVisible = true; ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); objBook = ExcelApp.Workbooks.Add(missing); objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objBook.Sheets["Sheet1"]; //objSheet.Name = "It's Me"; int i = 0; //objSheet.get_Range("A1", "A" + (dt.Rows.Count + 2).ToString()).WrapText = true; //range = objSheet.get_Range(sheet[i].ToString() + "1", sheet[i].ToString() + "1"); //range.Merge(2); int k = 0; foreach (DataColumn column in ds.Tables[0].Columns) { i++; if (i <= ds.Tables[0].Columns.Count - 1) { if (i % 2 == 1 && i > 2) { if (i != 3 && i <= ds.Tables[0].Columns.Count - 6) { range = objSheet.get_Range(sheet[i - 1].ToString() + "1", sheet[i].ToString() + "1"); range.Merge(2); if (ds.Tables[3].Rows.Count > k) { range.Value2 = "Received: " + ds.Tables[3].Rows[k].ItemArray[0].ToString(); k++; } else { range.Value2 = "Received: "; } } else if (i != 3 && i == ds.Tables[0].Columns.Count - 5) { range = objSheet.get_Range(sheet[i - 1].ToString() + "1", sheet[i].ToString() + "1"); range.Merge(2); range.Value2 = "Total Received"; } else if (i != 3 && i == ds.Tables[0].Columns.Count - 3) { range = objSheet.get_Range(sheet[i].ToString() + "1", sheet[i + 1].ToString() + "1"); range.Merge(2); range.Value2 = "Closing"; } else if (i == 3) { range = objSheet.get_Range(sheet[i - 1].ToString() + "1", sheet[i].ToString() + "1"); range.Merge(2); range.Value2 = "Opening"; } } if (i == 1) { range = objSheet.get_Range(sheet[i - 1].ToString() + "1", sheet[i - 1].ToString() + "2"); range.Merge(1); objSheet.Cells[2, i] = column.ColumnName.Replace("_", " ").ToUpper(); } else if (i == 2) { range = objSheet.get_Range(sheet[i - 1].ToString() + "1", sheet[i - 1].ToString() + "2"); range.Merge(1); objSheet.Cells[2, i] = "Unit"; } else if (i == ds.Tables[0].Columns.Count - 3) { range = objSheet.get_Range(sheet[i - 1].ToString() + "1", sheet[i - 1].ToString() + "2"); range.Merge(1); objSheet.Cells[2, i] = "Unit Price"; } else { if (i % 2 == 1 && i <= ds.Tables[0].Columns.Count - 4) { range = objSheet.get_Range(sheet[i - 1].ToString() + "1", Type.Missing); range.NumberFormat = "###00"; objSheet.Cells[2, i] = "Qty"; } else if (i % 2 == 0 && i <= ds.Tables[0].Columns.Count - 3) { range = objSheet.get_Range(sheet[i - 1].ToString() + "1", Type.Missing); range.NumberFormat = "$00.00"; objSheet.Cells[2, i] = "Amount"; } else if (i == ds.Tables[0].Columns.Count - 2) { objSheet.Cells[2, i] = "Qty"; range = objSheet.get_Range(sheet[i - 1].ToString() + "1", Type.Missing); range.NumberFormat = "###00"; } else if (i == ds.Tables[0].Columns.Count - 1) { objSheet.Cells[2, i] = "Amount"; range = objSheet.get_Range(sheet[i - 1].ToString() + "1", Type.Missing); range.NumberFormat = "$00.00"; } } if (i == 1) { SetColumnWidth(objSheet, i, 30); } else { SetColumnWidth(objSheet, i, 10); } if (i == 1) { SetTwoDecimalPlace(objSheet, i, 30); } else { SetTwoDecimalPlace(objSheet, i, 10); } } } i = i - 1; range = objSheet.get_Range(sheet[i].ToString() + "1", sheet[i + 1].ToString() + "1"); range.Merge(2); range.Value2 = "consumed"; SetColumnWidth(objSheet, i + 1, 6); SetColumnWidth(objSheet, i + 2, 6); SetTwoDecimalPlace(objSheet, i + 1, 10); SetTwoDecimalPlace(objSheet, i + 2, 10); objSheet.Cells[2, i + 2] = "Amount"; objSheet.Cells[2, i + 1] = "Qty"; i = 2; decimal Total_bal = 0; decimal Total_Recived = 0; decimal Total_CBal = 0; decimal Total_consumed = 0; System.Data.DataTable dt = new System.Data.DataTable(); dt = ds.Tables[0]; foreach (DataRow drSubcatalog in ds.Tables[2].Rows) { i++; dt.DefaultView.RowFilter = "subsystem_code='" + drSubcatalog.ItemArray[1].ToString() + "'"; //objSheet.get_Range("A1", "A" + (dt.Rows.Count + 2).ToString()).WrapText = true; range = objSheet.get_Range("A" + i.ToString(), "E" + i.ToString()); range.Merge(2); range.Value2 = "Sub CataLogue: " + drSubcatalog.ItemArray[2].ToString(); range.Font.Bold = true; //objSheet.Cells[i, 1] = drSubcatalog.ItemArray[2].ToString(); foreach (DataRowView dr in dt.DefaultView) { i++; for (int j = 0; j < ds.Tables[0].Columns.Count - 1; j++) { objSheet.Cells[i, j + 1] = dr[j].ToString(); } objSheet.Cells[i, dt.Columns.Count] = (Convert.ToDecimal(dr[ds.Tables[0].Columns.Count - 6].ToString()) + Convert.ToDecimal(dr[2].ToString()) - Convert.ToDecimal(dr[ds.Tables[0].Columns.Count - 3].ToString())).ToString(); objSheet.Cells[i, dt.Columns.Count + 1] = (Convert.ToDecimal(dr[ds.Tables[0].Columns.Count - 5].ToString()) + Convert.ToDecimal(dr[3].ToString()) - Convert.ToDecimal(dr[ds.Tables[0].Columns.Count - 2].ToString())).ToString(); Total_bal = Total_bal + Convert.ToDecimal(dr[3].ToString()); Total_Recived = Total_Recived + Convert.ToDecimal(dr[ds.Tables[0].Columns.Count - 5].ToString()); Total_CBal = Total_CBal + Convert.ToDecimal(dr[ds.Tables[0].Columns.Count - 2].ToString()); //Total_consumed = Total_consumed + Convert.ToDecimal(dr.ItemArray[3].ToString()); } } Total_consumed = Total_bal + Total_Recived - Total_CBal; i = i + 2; objSheet.Cells[i, 4] = Total_bal.ToString(); objSheet.Cells[i, dt.Columns.Count - 4] = Total_Recived.ToString(); objSheet.Cells[i, dt.Columns.Count - 1] = Total_CBal.ToString(); objSheet.Cells[i, dt.Columns.Count + 1] = Total_consumed.ToString(); objSheet.get_Range("A1", "A" + (ds.Tables[0].Rows.Count + 2).ToString()).WrapText = true; objSheet.get_Range("A1", sheet[ds.Tables[0].Columns.Count].ToString() + (ds.Tables[0].Rows.Count + 15).ToString()).Font.Name = "Arial"; objSheet.get_Range("A1", sheet[ds.Tables[0].Columns.Count].ToString() + (ds.Tables[0].Rows.Count + 15).ToString()).Font.Size = "8.25"; objSheet.get_Range("A1", sheet[ds.Tables[0].Columns.Count].ToString() + "2").Font.Bold = true; objSheet.get_Range("A1", sheet[ds.Tables[0].Columns.Count].ToString() + "2"); objSheet.get_Range("A1", sheet[ds.Tables[0].Columns.Count].ToString() + (ds.Tables[0].Rows.Count + 15).ToString()).Font.Name = "Arial"; objSheet.get_Range("A1", sheet[ds.Tables[0].Columns.Count].ToString() + (ds.Tables[0].Rows.Count + 15).ToString()).Font.Name = "Arial"; //objSheet.get_Range("A1", sheet[ds.Tables[0].Columns.Count + 1].ToString() + (dt.Rows.Count + 2).ToString()). objSheet.get_Range("A1", sheet[ds.Tables[0].Columns.Count].ToString() + "2").BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic); // objSheet.get_Range("A1", sheet[dt.Columns.Count - 1].ToString() + "2") objSheet.PageSetup.LeftHeader = HttpContext.Current.Session["Company_Name_GL"] + " \n\n" + ds.Tables[4].Rows[0][2].ToString().ToUpper(); objSheet.PageSetup.RightHeader = "C-2\n\n Date: " + DateTime.Now.ToString("dd/MM/yyyy"); string File_Name = ""; if (Dept_Code == "BS") { objSheet.PageSetup.CenterHeader = "\n BOND INVENTORY"; File_Name = "Bond_Inv_" + DateTime.Now.Month + "_" + DateTime.Now.ToString("yyMMddHHMMss") + ".xls"; } else { objSheet.PageSetup.CenterHeader = "\n PROVISION INVENTORY"; File_Name = "Pro_Inv_" + DateTime.Now.Month + "_" + DateTime.Now.ToString("yyMMddHHMMss") + ".xls"; } objSheet.PageSetup.LeftMargin = 0.5; //objSheet.PageSetup.BottomMargin = 2.5; objSheet.PageSetup.RightMargin = 0.5; objSheet.PageSetup.HeaderMargin = 2.0; objSheet.PageSetup.FooterMargin = 2.0; objSheet.PageSetup.CenterFooter = ""; objSheet.PageSetup.LeftFooter = "CH COOK \nOriginal: " + HttpContext.Current.Session["Company_Name_GL"] + " \nCopy: Ship's File"; objSheet.PageSetup.RightFooter = ds.Tables[4].Rows[0][2].ToString() + " "; objSheet.PageSetup.PrintGridlines = true; objSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape; //objSheet.PageSetup.PrintHeadings=true; objSheet.PageSetup.PrintTitleRows = "A1:H2"; ExcelApp.Visible = true; objSheet.PrintPreview(true); //if (!Directory.Exists(LibClass.ExcelCleanup.GetDrive() + @"PURC/Inventory")) //{ // Directory.CreateDirectory(LibClass.ExcelCleanup.GetDrive() + @"PURC/Inventory"); //} //string Path = ""; //Path = LibClass.ExcelCleanup.GetDrive() + @"PURC\Inventory\" + File_Name; //objBook.SaveAs(Path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); //System.Diagnostics.Process.Start(LibClass.ExcelCleanup.GetDrive() + @"PURC\Inventory"); objBook.Close(null, null, null); ExcelApp.Workbooks.Close(); ExcelApp.Quit(); Marshal.ReleaseComObject(ExcelApp); Marshal.ReleaseComObject(objSheet); Marshal.ReleaseComObject(objBook); //LibClass.ExcelCleanup.ExcelProcessKill(); }
private void KanryoReport(int tempPage, int tempCurrentPage, int tempFixRows) { const int S_GYO = 8; //エクセルファイル明細は8行目から印字 int dgvIndex; int i; int yMai, zMai; 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(sAppPath + Properties.Settings.Default.エクセル配布進捗状況, 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]; try { //日付・ページ数 oxlsSheet.Cells[1, 44] = "DATE : " + DateTime.Today.ToShortDateString() + " P." + tempCurrentPage.ToString() + "/" + tempPage.ToString(); //受注番号 oxlsSheet.Cells[3, 5] = long.Parse(dataGridView2[0, dataGridView2.SelectedRows[0].Index].Value.ToString(), System.Globalization.NumberStyles.Any); //得意先名 oxlsSheet.Cells[4, 5] = dataGridView2[1, dataGridView2.SelectedRows[0].Index].Value.ToString(); //チラシ名 oxlsSheet.Cells[5, 5] = dataGridView2[2, dataGridView2.SelectedRows[0].Index].Value.ToString(); //担当者名 oxlsSheet.Cells[3, 18] = dataGridView2[3, dataGridView2.SelectedRows[0].Index].Value.ToString(); //税込売上 oxlsSheet.Cells[4, 29] = int.Parse(dataGridView2[4, dataGridView2.SelectedRows[0].Index].Value.ToString(), System.Globalization.NumberStyles.Any); //予定枚数 oxlsSheet.Cells[4, 34] = int.Parse(dataGridView2[5, dataGridView2.SelectedRows[0].Index].Value.ToString(), System.Globalization.NumberStyles.Any); //配布枚数 oxlsSheet.Cells[4, 39] = int.Parse(dataGridView2[6, dataGridView2.SelectedRows[0].Index].Value.ToString(), System.Globalization.NumberStyles.Any); //残枚数 oxlsSheet.Cells[4, 44] = int.Parse(dataGridView2[7, dataGridView2.SelectedRows[0].Index].Value.ToString(), System.Globalization.NumberStyles.Any); //完了日 oxlsSheet.Cells[4, 49] = dataGridView2[8, dataGridView2.SelectedRows[0].Index].Value.ToString(); //配布エリア明細 i = 0; while (true) { dgvIndex = tempFixRows * (tempCurrentPage - 1) + i; //データグリッドビューの行インデックスを求める //指示番号 oxlsSheet.Cells[i + S_GYO, 1] = dataGridView1[1, dgvIndex].Value.ToString(); //配布日 oxlsSheet.Cells[i + S_GYO, 5] = dataGridView1[2, dgvIndex].Value.ToString(); //配布員ID oxlsSheet.Cells[i + S_GYO, 10] = dataGridView1[3, dgvIndex].Value.ToString(); //配布員名 oxlsSheet.Cells[i + S_GYO, 13] = dataGridView1[4, dgvIndex].Value.ToString(); //町名ID oxlsSheet.Cells[i + S_GYO, 19] = dataGridView1[5, dgvIndex].Value.ToString(); //町名 oxlsSheet.Cells[i + S_GYO, 22] = dataGridView1[6, dgvIndex].Value.ToString(); //予定枚数 oxlsSheet.Cells[i + S_GYO, 39] = dataGridView1[7, dgvIndex].Value.ToString(); //配布枚数 yMai = int.Parse(dataGridView1[7, dgvIndex].Value.ToString(), System.Globalization.NumberStyles.Any); zMai = int.Parse(dataGridView1[8, dgvIndex].Value.ToString(), System.Globalization.NumberStyles.Any); oxlsSheet.Cells[i + S_GYO, 44] = yMai - zMai; //残枚数 oxlsSheet.Cells[i + S_GYO, 49] = dataGridView1[8, dgvIndex].Value.ToString(); //グリッド最終行のとき終了 if (dgvIndex == (dataGridView1.Rows.Count - 1)) { break; } //印刷明細最大行のとき終了 if (i == (tempFixRows - 1)) { break; } i++; } //マウスポインタを元に戻す this.Cursor = Cursors.Default; // 確認のためExcelのウィンドウを表示する oXls.Visible = true; //印刷 oxlsSheet.PrintPreview(true); // ウィンドウを非表示にする oXls.Visible = false; //保存処理 oXls.DisplayAlerts = false; DialogResult ret; //ダイアログボックスの初期設定 saveFileDialog1.Title = MESSAGE_CAPTION + "保存"; saveFileDialog1.OverwritePrompt = true; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.FileName = MESSAGE_CAPTION + "_" + dataGridView2[2, dataGridView2.SelectedRows[0].Index].Value.ToString(); //複数ページのとき、ページ数も付与 if (tempPage > 1) { saveFileDialog1.FileName += "_" + tempCurrentPage.ToString(); } saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xls)|*.xls|全てのファイル(*.*)|*.*"; //ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示 string fileName; ret = saveFileDialog1.ShowDialog(); if (ret == System.Windows.Forms.DialogResult.OK) { fileName = saveFileDialog1.FileName; oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } //Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); //Excelを終了 oXls.Quit(); } catch (Exception e) { MessageBox.Show(e.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); //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, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } //マウスポインタを元に戻す this.Cursor = Cursors.Default; }
public void createBURExcel(Entities.BURModel SentBUR) { Entities.BURModel BUR = SentBUR; ExApp = new Excel.Application(); ExApp.Visible = false; ExBook = ExApp.Workbooks.Open("C:\\BUR.xls"); ExSheet = (Excel.Worksheet)ExBook.Sheets[2]; int lastRow = 20; decimal total = 0.00M; ExSheet.Cells[6, 7] = BUR.BURNumber; ExSheet.Cells[7, 2] = BUR.Payee; if (BUR.Office != "External") { ExSheet.Cells[8, 2] = BUR.Office; } ExSheet.Cells[12, 2] = BUR.Description + "\n" + "PR Number: " + BUR.PRNumber; foreach (var item in BUR.Particulars) { ExSheet.Cells[lastRow, 2] = item.Name; ExSheet.Cells[lastRow, 6] = item.Classification; //item.Code = item.Code.Insert(1, "-"); //item.Code = item.Code.Insert(4, "-"); //item.Code = item.Code.Insert(7, "-"); ExSheet.Cells[lastRow, 7] = item.Code; ExSheet.Cells[lastRow, 8] = item.Amount.ToString("C2"); total += item.Amount; lastRow++; } ExSheet.Cells[34, 8] = total; if (BUR.Office == "External") { ExSheet.Cells[41, 2] = BUR.OfficeheadName; ExSheet.Cells[42, 2] = BUR.OfficeheadPos; } else { ExSheet.Cells[41, 2] = BUR.OfficeheadName; ExSheet.Cells[42, 2] = BUR.OfficeheadPos; } ExSheet.Cells[44, 2] = DateTime.Now; ExSheet.Cells[41, 7] = BUR.BDHead; ExSheet.Cells[42, 7] = BUR.BDHead_Pos; ExSheet.Cells[44, 7] = DateTime.Now; ExBook.SaveAs("DBMS\\BUR\\BUR_" + BUR.BURNumber + ".xls"); if (MessageBox.Show("Do you want to continue to printing?", "Print?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { ExSheet.PrintOutEx(); } ExApp.Visible = true; ExSheet.PrintPreview(); ExBook.Close(); }
private void KanryoReport(int tempPage, int tempCurrentPage, int tempFixRows) { const int S_GYO = 13; //エクセルファイル明細は13行目から印字 int dgvIndex; int i; 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(sAppPath + Properties.Settings.Default.エクセル配布完了報告書, 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]; try { //得意先情報 long sID; string sqlSTR; sID = long.Parse(dataGridView2[0, dataGridView2.SelectedRows[0].Index].Value.ToString()); sqlSTR = ""; sqlSTR += "select 得意先.名称,得意先.担当者名,得意先.電話番号 "; sqlSTR += "from 受注 inner join 得意先 "; sqlSTR += "on 受注.得意先ID = 得意先.ID "; sqlSTR += "where 受注.ID = " + sID.ToString(); OleDbDataReader dR; Control.FreeSql fCon = new Control.FreeSql(); dR = fCon.free_dsReader(sqlSTR); while (dR.Read()) { oxlsSheet.Cells[1, 3] = dR["名称"].ToString() + " " + dR["担当者名"].ToString() + "様"; oxlsSheet.Cells[2, 3] = dR["電話番号"].ToString(); } dR.Close(); fCon.Close(); //納品数 oxlsSheet.Cells[8, 2] = int.Parse(dataGridView2[2, dataGridView2.SelectedRows[0].Index].Value.ToString(), System.Globalization.NumberStyles.Any); //前回まで oxlsSheet.Cells[9, 2] = int.Parse(dataGridView2[3, dataGridView2.SelectedRows[0].Index].Value.ToString(), System.Globalization.NumberStyles.Any); //残部数 oxlsSheet.Cells[10, 2] = int.Parse(dataGridView2[4, dataGridView2.SelectedRows[0].Index].Value.ToString(), System.Globalization.NumberStyles.Any); //チラシ名 oxlsSheet.Cells[10, 3] = dataGridView2[1, dataGridView2.SelectedRows[0].Index].Value.ToString(); //配布エリア明細 i = 0; while (true) { dgvIndex = tempFixRows * (tempCurrentPage - 1) + i; //データグリッドビューの行インデックスを求める //oxlsSheet.Cells[i + S_GYO, 2] = dateTimePicker1.Value.ToShortDateString(); //チラシ名 oxlsSheet.Cells[i + S_GYO, 3] = dataGridView1[0, dgvIndex].Value.ToString(); //配布区分 oxlsSheet.Cells[i + S_GYO, 4] = int.Parse(dataGridView1[1, dgvIndex].Value.ToString(), System.Globalization.NumberStyles.Any); //グリッド最終行のとき終了 if (dgvIndex == (dataGridView1.Rows.Count - 1)) { break; } //印刷明細最大行のとき終了 if (i == (tempFixRows - 1)) { break; } i++; } //配布枚数合計 oxlsSheet.Cells[45, 4] = int.Parse(dataGridView2[5, dataGridView2.SelectedRows[0].Index].Value.ToString(), System.Globalization.NumberStyles.Any); //残部数 oxlsSheet.Cells[46, 4] = int.Parse(dataGridView2[6, dataGridView2.SelectedRows[0].Index].Value.ToString(), System.Globalization.NumberStyles.Any); //マウスポインタを元に戻す this.Cursor = Cursors.Default; // 確認のためExcelのウィンドウを表示する oXls.Visible = true; //印刷 oxlsSheet.PrintPreview(true); // ウィンドウを非表示にする oXls.Visible = false; //保存処理 oXls.DisplayAlerts = false; DialogResult ret; //ダイアログボックスの初期設定 saveFileDialog1.Title = MESSAGE_CAPTION + "保存"; saveFileDialog1.OverwritePrompt = true; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.FileName = MESSAGE_CAPTION + "_" + dataGridView2[1, dataGridView2.SelectedRows[0].Index].Value.ToString() + "_" + DateTime.Today.ToLongDateString(); //複数ページのとき、ページ数も付与 if (tempPage > 1) { saveFileDialog1.FileName += "_" + tempCurrentPage.ToString(); } saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xls)|*.xls|全てのファイル(*.*)|*.*"; //ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示 string fileName; ret = saveFileDialog1.ShowDialog(); if (ret == System.Windows.Forms.DialogResult.OK) { fileName = saveFileDialog1.FileName; oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } //Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); //Excelを終了 oXls.Quit(); } catch (Exception e) { MessageBox.Show(e.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); //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, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } //マウスポインタを元に戻す this.Cursor = Cursors.Default; }
/// <summary> /// 打印预览. /// </summary> private void PrintPreview() { xlSheet.PrintPreview(); }
private void EigyoReport(DataGridView tempDGV) { const int S_GYO = 4; // エクセルファイル明細印刷開始行 const int S_ROWSMAX = 11; // エクセルファイル列最大値 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(sAppPath + Properties.Settings.Default.エクセルクライアント別請求一覧, 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 { // 合計情報を印字 StringBuilder sb = new StringBuilder(); sb.Append("入金計:").Append(txtTotal.Text).Append(" "); sb.Append("未収金:").Append(txtMinyu.Text).Append(" "); sb.Append("売掛金:").Append(txtUrikake.Text).Append(" "); sb.Append("入金残計:").Append(txtZan.Text); oxlsSheet.Cells[1, 6] = sb.ToString(); // グリッド情報を印字 for (int iX = 0; iX <= tempDGV.RowCount - 1; iX++) { oxlsSheet.Cells[S_GYO - 3, S_ROWSMAX] = int.Parse(this.txtZan.Text, System.Globalization.NumberStyles.Any); oxlsSheet.Cells[iX + S_GYO, 1] = tempDGV[0, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 2] = tempDGV[1, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 3] = tempDGV[3, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 4] = tempDGV[4, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 5] = tempDGV[5, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 6] = tempDGV[6, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 7] = tempDGV[7, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 8] = tempDGV[8, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 9] = tempDGV[9, iX].Value.ToString(); if (tempDGV[10, iX].Value.ToString() == "True") { oxlsSheet.Cells[iX + S_GYO, 10] = "*"; } else { oxlsSheet.Cells[iX + S_GYO, 10] = string.Empty; } oxlsSheet.Cells[iX + S_GYO, 11] = tempDGV[12, iX].Value.ToString(); } ////////セル上部へ実線ヨコ罫線を引く //////rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; //////rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; //////oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //セル下部へ実線ヨコ罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体に実線縦罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の左端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の右端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, S_ROWSMAX]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //マウスポインタを元に戻す this.Cursor = Cursors.Default; // 確認のためExcelのウィンドウを表示する oXls.Visible = true; //印刷 oxlsSheet.PrintPreview(true); // ウィンドウを非表示にする oXls.Visible = false; //保存処理 oXls.DisplayAlerts = false; DialogResult ret; //ダイアログボックスの初期設定 saveFileDialog1.Title = "クライアント別請求一覧"; saveFileDialog1.OverwritePrompt = true; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.FileName = "クライアント別請求一覧"; saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xls)|*.xls|全てのファイル(*.*)|*.*"; //ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示 string fileName; ret = saveFileDialog1.ShowDialog(); if (ret == System.Windows.Forms.DialogResult.OK) { fileName = saveFileDialog1.FileName; oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } } catch (Exception e) { MessageBox.Show(e.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } finally { //Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); //Excelを終了 oXls.Quit(); // 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, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } //マウスポインタを元に戻す this.Cursor = Cursors.Default; }
private void PrintReport() { const int S_GYO = 5; //エクセルファイル明細は5行目から印字 const int S_ROWSMAX = 24; //エクセルファイル列最大値 const int DKIKAN = 14; //期間列数 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(sAppPath + Properties.Settings.Default.エクセル配布スケジュール, 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]; Excel.Range srng; try { //見出し int sPos, sIdx, sCnt; string sHead; oxlsSheet.Cells[1, 1] = "配布スケジュール [" + dateTimePicker1.Value.ToShortDateString() + " 〜 " + dateTimePicker1.Value.AddDays(13).ToShortDateString() + "]"; //日・曜日・人数 for (int i = 10; i < S_ROWSMAX; i++) { sHead = dataGridView2.Columns[i].HeaderText.Replace(Environment.NewLine, "*"); sPos = -1; sIdx = 0; sCnt = -3; while (true) { sPos = sHead.IndexOf("*", sPos + 1); if (sPos == -1) { oxlsSheet.Cells[S_GYO + sCnt, i + 1] = sHead.Substring(sIdx, sHead.Length - sIdx); break; } oxlsSheet.Cells[S_GYO + sCnt, i + 1] = sHead.Substring(sIdx, sPos - sIdx); sIdx = sPos + 1; sCnt++; } } //配布エリア明細 for (int i = 0; i < dataGridView2.RowCount; i++) { oxlsSheet.Cells[i + S_GYO, 1] = dataGridView2[0, i].Value.ToString(); //受注番号 oxlsSheet.Cells[i + S_GYO, 2] = dataGridView2[1, i].Value.ToString(); //チラシ名 oxlsSheet.Cells[i + S_GYO, 3] = dataGridView2[2, i].Value.ToString(); //事業所名 oxlsSheet.Cells[i + S_GYO, 4] = dataGridView2[3, i].Value.ToString(); //担当者名 oxlsSheet.Cells[i + S_GYO, 5] = dataGridView2[4, i].Value.ToString(); //配布開始日 oxlsSheet.Cells[i + S_GYO, 6] = dataGridView2[5, i].Value.ToString(); //配布終了日 oxlsSheet.Cells[i + S_GYO, 7] = dataGridView2[6, i].Value.ToString(); //配布形態 oxlsSheet.Cells[i + S_GYO, 8] = int.Parse(dataGridView2[7, i].Value.ToString(), System.Globalization.NumberStyles.Any); //残枚数 oxlsSheet.Cells[i + S_GYO, 9] = int.Parse(dataGridView2[8, i].Value.ToString(), System.Globalization.NumberStyles.Any); //人数 oxlsSheet.Cells[i + S_GYO, 10] = int.Parse(dataGridView2[9, i].Value.ToString(), System.Globalization.NumberStyles.Any); //延日数 for (int n = 1; n <= DKIKAN; n++) { if (dataGridView2[n + 9, i].Value != null) { oxlsSheet.Cells[i + S_GYO, n + 10] = int.Parse(dataGridView2[n + 9, i].Value.ToString() + "", System.Globalization.NumberStyles.Any); //人数 } } ////セル上部へ実線ヨコ罫線を引く //rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; //rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; //oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //セル下部へ実線ヨコ罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; } //表全体に実線縦罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の左端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の右端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, S_ROWSMAX]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //土日の列は背景色を変える for (int i = S_ROWSMAX - DKIKAN + 1; i <= S_ROWSMAX; i++) { srng = (Excel.Range)oxlsSheet.Cells[3, i]; if (srng.Text.ToString() == "土" || srng.Text.ToString() == "日") { rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, i]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, i]; oxlsSheet.get_Range(rng[0], rng[1]).Interior.ColorIndex = 15; } } //マウスポインタを元に戻す this.Cursor = Cursors.Default; // 確認のためExcelのウィンドウを表示する oXls.Visible = true; //印刷 oxlsSheet.PrintPreview(true); // ウィンドウを非表示にする oXls.Visible = false; //保存処理 oXls.DisplayAlerts = false; DialogResult ret; //ダイアログボックスの初期設定 saveFileDialog1.Title = MESSAGE_CAPTION; saveFileDialog1.OverwritePrompt = true; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.FileName = MESSAGE_CAPTION; saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xls)|*.xls|全てのファイル(*.*)|*.*"; //ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示 string fileName; ret = saveFileDialog1.ShowDialog(); if (ret == System.Windows.Forms.DialogResult.OK) { fileName = saveFileDialog1.FileName; oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } //Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); //Excelを終了 oXls.Quit(); } catch (Exception e) { MessageBox.Show(e.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); //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, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } //マウスポインタを元に戻す this.Cursor = Cursors.Default; }
private void EigyoReport(DataGridView tempDGV) { const int S_GYO = 4; //エクセルファイル見出し行(明細は3行目から印字) const int S_ROWSMAX = 8; //エクセルファイル列最大値 string sMidashi; double gTL = 0; 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(sAppPath + Properties.Settings.Default.営業売上表, 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 { for (int iX = 0; iX <= tempDGV.RowCount - 1; iX++) { sMidashi = this.comboBox1.Text + " "; if (tDate.Checked == true) { sMidashi += this.tDate.Value.ToShortDateString() + " 〜 "; } if (tDate2.Checked == true) { if (tDate.Checked == false) { sMidashi += " 〜 "; } sMidashi += this.tDate2.Value.ToShortDateString(); } oxlsSheet.Cells[S_GYO - 2, 1] = sMidashi; oxlsSheet.Cells[iX + S_GYO, 1] = tempDGV[colNDt, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 2] = tempDGV[colClient, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 3] = tempDGV[colNyukin, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 4] = tempDGV[colEganka, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 5] = tempDGV[colArari1, iX].Value.ToString(); double g = Utility.strToDouble(tempDGV[colGaichuhi, iX].Value.ToString()) + Utility.strToDouble(tempDGV[colGaichuhi2, iX].Value.ToString()) + Utility.strToDouble(tempDGV[colGaichuhi3, iX].Value.ToString()); oxlsSheet.Cells[iX + S_GYO, 6] = g.ToString(); oxlsSheet.Cells[iX + S_GYO, 7] = tempDGV[colArari2, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 8] = tempDGV[colArariSai, iX].Value.ToString(); //セル下部へ実線ヨコ罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[iX + S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[iX + S_GYO, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; gTL += g; } ////セル上部へ実線ヨコ罫線を引く //rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; //rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; //oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ////セル下部へ実線ヨコ罫線を引く //rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; //rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; //oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体に実線縦罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の左端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の右端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, S_ROWSMAX]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //合計 oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count + 1, 3] = this.label5.Text; oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 4] = this.lblEgenka.Text; oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 5] = this.lblArari1.Text; oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 6] = gTL.ToString("#,##0"); oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 7] = this.lblArari2.Text; oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 8] = this.lblArarisai.Text; //マウスポインタを元に戻す this.Cursor = Cursors.Default; // 確認のためExcelのウィンドウを表示する oXls.Visible = true; //印刷 oxlsSheet.PrintPreview(true); // ウィンドウを非表示にする oXls.Visible = false; //保存処理 oXls.DisplayAlerts = false; DialogResult ret; string msgHD = ""; if (tDate.Checked == true) { msgHD += tDate.Value.ToLongDateString() + "から"; } if (tDate2.Checked == true) { msgHD += tDate2.Value.ToLongDateString() + "まで"; } //ダイアログボックスの初期設定 saveFileDialog1.Title = MESSAGE_CAPTION; saveFileDialog1.OverwritePrompt = true; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.FileName = MESSAGE_CAPTION + "_" + comboBox1.Text + "_" + msgHD; saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xls)|*.xls|全てのファイル(*.*)|*.*"; //ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示 string fileName; ret = saveFileDialog1.ShowDialog(); if (ret == System.Windows.Forms.DialogResult.OK) { fileName = saveFileDialog1.FileName; oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } //Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); //Excelを終了 oXls.Quit(); } catch (Exception e) { MessageBox.Show(e.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); //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; }
public void createMonthlyPS(List <Entities.SAAOModel> Monthly, string month) { ExApp = new Excel.Application(); ExApp.Visible = false; ExBook = ExApp.Workbooks.Open("C:\\Monthly.xlsx"); ExSheet = (Excel.Worksheet)ExBook.Sheets[4]; ////////PERSONAL SERVICES // Month, Year ExSheet.Cells[3, 1] = month + DateTime.Now.Year.ToString(); //PERSONAL SERVICES //50101010 ExSheet.Cells[12, 6] = Monthly[0].AB; ExSheet.Cells[12, 9] = Monthly[0].Amount; //50101020.1 ExSheet.Cells[14, 6] = Monthly[1].AB; ExSheet.Cells[14, 9] = Monthly[1].Amount; //50101020.2 ExSheet.Cells[15, 6] = Monthly[2].AB; ExSheet.Cells[15, 9] = Monthly[2].Amount; //50102010 ExSheet.Cells[16, 6] = Monthly[3].AB; ExSheet.Cells[16, 9] = Monthly[3].Amount; //50102020 ExSheet.Cells[17, 6] = Monthly[4].AB; ExSheet.Cells[17, 9] = Monthly[4].Amount; //50102030 ExSheet.Cells[18, 6] = Monthly[5].AB; ExSheet.Cells[18, 9] = Monthly[5].Amount; //50102040 ExSheet.Cells[19, 6] = Monthly[6].AB; ExSheet.Cells[19, 9] = Monthly[6].Amount; //50102050 ExSheet.Cells[21, 6] = Monthly[7].AB; ExSheet.Cells[21, 9] = Monthly[7].Amount; //50102060 ExSheet.Cells[22, 6] = Monthly[8].AB; ExSheet.Cells[22, 9] = Monthly[8].Amount; //50102100 ExSheet.Cells[20, 6] = Monthly[9].AB; ExSheet.Cells[20, 9] = Monthly[9].Amount; //50102110 ExSheet.Cells[23, 6] = Monthly[10].AB; ExSheet.Cells[23, 9] = Monthly[10].Amount; //50102120 ExSheet.Cells[24, 6] = Monthly[11].AB; ExSheet.Cells[24, 9] = Monthly[11].Amount; //50102130 ExSheet.Cells[25, 6] = Monthly[12].AB; ExSheet.Cells[25, 9] = Monthly[12].Amount; //50102140 ExSheet.Cells[27, 6] = Monthly[13].AB; ExSheet.Cells[27, 9] = Monthly[13].Amount; //50102150 ExSheet.Cells[26, 6] = Monthly[14].AB; ExSheet.Cells[26, 9] = Monthly[14].Amount; //50102990 ExSheet.Cells[28, 6] = Monthly[15].AB; ExSheet.Cells[28, 9] = Monthly[15].Amount; //50103010 ExSheet.Cells[29, 6] = Monthly[16].AB; ExSheet.Cells[29, 9] = Monthly[16].Amount; //50103020 ExSheet.Cells[30, 6] = Monthly[17].AB; ExSheet.Cells[30, 9] = Monthly[17].Amount; //50103030 ExSheet.Cells[31, 6] = Monthly[18].AB; ExSheet.Cells[31, 9] = Monthly[18].Amount; //50103040 ExSheet.Cells[32, 6] = Monthly[19].AB; ExSheet.Cells[32, 9] = Monthly[19].Amount; //50104030 ExSheet.Cells[33, 6] = Monthly[20].AB; ExSheet.Cells[33, 9] = Monthly[20].Amount; //50104990 ExSheet.Cells[34, 6] = Monthly[21].AB; ExSheet.Cells[34, 9] = Monthly[21].Amount; // Month ExSheet.Cells[7, 9] = month; try { ExSheet.SaveAs("DBMS\\Monthly\\PS\\" + month + "_REPORT_PS.xlsx"); } catch { } finally { if (MessageBox.Show("Do you want to continue to printing?", "Print?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { ExSheet.PrintOutEx(); } ExApp.Visible = true; ExSheet.PrintPreview(); ExBook.Close(); } }
public void createMonthlyMOOE(List <Entities.SAAOModel> Monthly, string month) { ExApp = new Excel.Application(); ExApp.Visible = false; ExBook = ExApp.Workbooks.Open("C:\\Monthly.xlsx"); ExSheet = (Excel.Worksheet)ExBook.Sheets[3]; /////////////// MOOE /////////////// // Month, Year ExSheet.Cells[3, 1] = month + DateTime.Now.Year.ToString(); ///////MOOE //50201010 ExSheet.Cells[12, 6] = Monthly[0].AB; ExSheet.Cells[12, 9] = Monthly[0].Amount; //50201020 ExSheet.Cells[13, 6] = Monthly[1].AB; ExSheet.Cells[13, 9] = Monthly[1].Amount; //50202010 ExSheet.Cells[14, 6] = Monthly[2].AB; ExSheet.Cells[14, 9] = Monthly[2].Amount; //50203010 ExSheet.Cells[15, 6] = Monthly[3].AB; ExSheet.Cells[15, 9] = Monthly[3].Amount; //50203020 ExSheet.Cells[16, 6] = Monthly[4].AB; ExSheet.Cells[16, 9] = Monthly[4].Amount; //50203070 ExSheet.Cells[17, 6] = Monthly[5].AB; ExSheet.Cells[17, 9] = Monthly[5].Amount; //50203080 ExSheet.Cells[18, 6] = Monthly[6].AB; ExSheet.Cells[18, 9] = Monthly[6].Amount; //50203090 ExSheet.Cells[19, 6] = Monthly[7].AB; ExSheet.Cells[19, 9] = Monthly[7].Amount; //50203990 ExSheet.Cells[20, 6] = Monthly[8].AB; ExSheet.Cells[20, 9] = Monthly[8].Amount; //50204010 ExSheet.Cells[21, 6] = Monthly[9].AB; ExSheet.Cells[21, 9] = Monthly[9].Amount; //50204020 ExSheet.Cells[22, 6] = Monthly[10].AB; ExSheet.Cells[22, 9] = Monthly[10].Amount; //50205010 ExSheet.Cells[23, 6] = Monthly[11].AB; ExSheet.Cells[23, 9] = Monthly[11].Amount; //50205020.1 ExSheet.Cells[24, 6] = Monthly[12].AB; ExSheet.Cells[24, 9] = Monthly[12].Amount; //50205020.2 ExSheet.Cells[25, 6] = Monthly[13].AB; ExSheet.Cells[25, 9] = Monthly[13].Amount; //50205030 ExSheet.Cells[26, 6] = Monthly[14].AB; ExSheet.Cells[26, 9] = Monthly[14].Amount; //50210030 ExSheet.Cells[27, 6] = Monthly[15].AB; ExSheet.Cells[27, 9] = Monthly[15].Amount; //50211030 ExSheet.Cells[28, 6] = Monthly[16].AB; ExSheet.Cells[28, 9] = Monthly[16].Amount; //50211990 ExSheet.Cells[29, 6] = Monthly[17].AB; ExSheet.Cells[29, 9] = Monthly[17].Amount; //50212020 ExSheet.Cells[30, 6] = Monthly[18].AB; ExSheet.Cells[30, 9] = Monthly[18].Amount; //50212030 ExSheet.Cells[31, 6] = Monthly[19].AB; ExSheet.Cells[31, 9] = Monthly[19].Amount; //50213040 ExSheet.Cells[32, 6] = Monthly[20].AB; ExSheet.Cells[32, 9] = Monthly[20].Amount; //50213050.1 ExSheet.Cells[33, 6] = Monthly[21].AB; ExSheet.Cells[33, 9] = Monthly[21].Amount; //50213050.2 ExSheet.Cells[34, 6] = Monthly[22].AB; ExSheet.Cells[34, 9] = Monthly[22].Amount; //50213050.3 ExSheet.Cells[35, 6] = Monthly[23].AB; ExSheet.Cells[35, 9] = Monthly[23].Amount; //50213050.4 ExSheet.Cells[36, 6] = Monthly[24].AB; ExSheet.Cells[36, 9] = Monthly[24].Amount; //50213050.5 ExSheet.Cells[37, 6] = Monthly[25].AB; ExSheet.Cells[37, 9] = Monthly[25].Amount; //50213050.6 ExSheet.Cells[38, 6] = Monthly[26].AB; ExSheet.Cells[38, 9] = Monthly[26].Amount; //50213050.7 ExSheet.Cells[39, 6] = Monthly[27].AB; ExSheet.Cells[39, 9] = Monthly[27].Amount; //50213060 ExSheet.Cells[40, 6] = Monthly[28].AB; ExSheet.Cells[40, 9] = Monthly[28].Amount; //50213070 ExSheet.Cells[41, 6] = Monthly[29].AB; ExSheet.Cells[41, 9] = Monthly[29].Amount; //50216020 ExSheet.Cells[42, 6] = Monthly[30].AB; ExSheet.Cells[42, 9] = Monthly[30].Amount; //50216030 ExSheet.Cells[43, 6] = Monthly[31].AB; ExSheet.Cells[43, 9] = Monthly[31].Amount; //50299010 ExSheet.Cells[44, 6] = Monthly[32].AB; ExSheet.Cells[44, 9] = Monthly[32].Amount; //50299020 ExSheet.Cells[45, 6] = Monthly[33].AB; ExSheet.Cells[45, 9] = Monthly[33].Amount; //50299030 ExSheet.Cells[46, 6] = Monthly[34].AB; ExSheet.Cells[46, 9] = Monthly[34].Amount; //50299050 ExSheet.Cells[47, 6] = Monthly[35].AB; ExSheet.Cells[47, 9] = Monthly[35].Amount; //50299060 ExSheet.Cells[48, 6] = Monthly[36].AB; ExSheet.Cells[48, 9] = Monthly[36].Amount; //50299070 ExSheet.Cells[49, 6] = Monthly[37].AB; ExSheet.Cells[49, 9] = Monthly[37].Amount; //50299990 ExSheet.Cells[50, 6] = Monthly[38].AB; ExSheet.Cells[50, 9] = Monthly[38].Amount; // Month ExSheet.Cells[7, 9] = month; try { ExSheet.SaveAs("DBMS\\Monthly\\MOOE\\" + month + "_REPORT_MOOE.xlsx"); } catch { } finally { if (MessageBox.Show("Do you want to continue to printing?", "Print?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { ExSheet.PrintOutEx(); } ExApp.Visible = true; ExSheet.PrintPreview(); ExBook.Close(); } }
public void createMonthlyCO(List <Entities.SAAOModel> Monthly, string month) { ExApp = new Excel.Application(); ExApp.Visible = false; ExBook = ExApp.Workbooks.Open("C:\\Monthly.xlsx"); ExSheet = (Excel.Worksheet)ExBook.Sheets[1]; /////////////// CAPITAL OUTLAY /////////////// // Month, Year ExSheet.Cells[3, 1] = month + DateTime.Now.Year.ToString(); //CAPITAL OUTLAY //10702990 ExSheet.Cells[12, 6] = Monthly[0].AB; ExSheet.Cells[12, 9] = Monthly[0].Amount; //10704020 ExSheet.Cells[13, 6] = Monthly[1].AB; ExSheet.Cells[13, 9] = Monthly[1].Amount; //10705020 ExSheet.Cells[14, 6] = Monthly[2].AB; ExSheet.Cells[14, 9] = Monthly[2].Amount; //10705030 ExSheet.Cells[15, 6] = Monthly[3].AB; ExSheet.Cells[15, 9] = Monthly[3].Amount; //10705070 ExSheet.Cells[16, 6] = Monthly[4].AB; ExSheet.Cells[16, 9] = Monthly[4].Amount; //10705090 ExSheet.Cells[17, 6] = Monthly[5].AB; ExSheet.Cells[17, 9] = Monthly[5].Amount; //10705110 ExSheet.Cells[18, 6] = Monthly[6].AB; ExSheet.Cells[18, 9] = Monthly[6].Amount; //10705130 ExSheet.Cells[19, 6] = Monthly[7].AB; ExSheet.Cells[19, 9] = Monthly[7].Amount; //10705140 ExSheet.Cells[20, 6] = Monthly[8].AB; ExSheet.Cells[20, 9] = Monthly[8].Amount; //10705990 ExSheet.Cells[21, 6] = Monthly[9].AB; ExSheet.Cells[21, 9] = Monthly[9].Amount; //10707010 ExSheet.Cells[22, 6] = Monthly[10].AB; ExSheet.Cells[22, 9] = Monthly[10].Amount; //10707020 ExSheet.Cells[23, 6] = Monthly[11].AB; ExSheet.Cells[23, 9] = Monthly[11].Amount; try { ExSheet.SaveAs("DBMS\\Monthly\\CO\\" + month + "_REPORT_CO.xlsx"); } catch { } finally { if (MessageBox.Show("Do you want to continue to printing?", "Print?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { ExSheet.PrintOutEx(); } ExApp.Visible = true; ExSheet.PrintPreview(); ExBook.Close(); } }
private void KanryoReport(DataGridView tempDGV) { const int S_GYO = 4; //エクセルファイル見出し行(明細は4行目から印字) const int S_ROWSMAX = 9; //エクセルファイル列最大値 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(sAppPath + Properties.Settings.Default.エクセル在庫管理表, 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 { for (int iX = 0; iX <= tempDGV.RowCount - 1; iX++) { //oxlsSheet.Cells[S_GYO - 2, 1] = txtYear.Text + "年" + txtMonth.Text + "月 稼働日数 " + "日以上" ; oxlsSheet.Cells[iX + S_GYO, 1] = tempDGV[0, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 2] = tempDGV[1, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 3] = tempDGV[2, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 4] = tempDGV[3, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 5] = tempDGV[4, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 6] = tempDGV[5, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 7] = tempDGV[6, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 8] = tempDGV[7, iX].Value.ToString(); oxlsSheet.Cells[iX + S_GYO, 9] = tempDGV[8, iX].Value.ToString(); //セル下部へ実線ヨコ罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; } //////セル上部へ実線ヨコ罫線を引く //rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; //rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; //oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ////セル下部へ実線ヨコ罫線を引く //rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; //rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; //oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体に実線縦罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の左端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の右端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, S_ROWSMAX]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //マウスポインタを元に戻す this.Cursor = Cursors.Default; // 確認のためExcelのウィンドウを表示する oXls.Visible = true; //印刷 oxlsSheet.PrintPreview(true); // ウィンドウを非表示にする oXls.Visible = false; //保存処理 oXls.DisplayAlerts = false; DialogResult ret; //ダイアログボックスの初期設定 saveFileDialog1.Title = MESSAGE_CAPTION; saveFileDialog1.OverwritePrompt = true; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.FileName = MESSAGE_CAPTION; saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xls)|*.xls|全てのファイル(*.*)|*.*"; //ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示 string fileName; ret = saveFileDialog1.ShowDialog(); if (ret == System.Windows.Forms.DialogResult.OK) { fileName = saveFileDialog1.FileName; oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } //Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); //Excelを終了 oXls.Quit(); } catch (Exception e) { MessageBox.Show(e.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); //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, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } //マウスポインタを元に戻す this.Cursor = Cursors.Default; }
private void EigyoReport(DataGridView tempDGV, int tempID) { const int S_GYO = 4; // エクセルファイル見出し行(明細は3行目から印字) const int S_ROWSMAX = 11; // エクセルファイル列最大値 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(sAppPath + Properties.Settings.Default.エクセル営業担当者別受注一覧シート名, 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 { for (int iX = 0; iX <= tempDGV.RowCount - 1; iX++) { oxlsSheet.Cells[S_GYO - 2, 1] = this.comboBox1.Text; // 担当 oxlsSheet.Cells[iX + S_GYO, 1] = tempDGV[0, iX].Value.ToString(); // 受注日 oxlsSheet.Cells[iX + S_GYO, 2] = tempDGV[1, iX].Value.ToString(); // 受注番号 //合計のとき if (tempID == 1) { oxlsSheet.Cells[iX + S_GYO, 3] = tempDGV[2, iX].Value.ToString() + ":" + tempDGV[3, iX].Value.ToString(); } else { oxlsSheet.Cells[iX + S_GYO, 3] = tempDGV[2, iX].Value.ToString(); } oxlsSheet.Cells[iX + S_GYO, 4] = tempDGV[4, iX].Value.ToString(); // 単価 oxlsSheet.Cells[iX + S_GYO, 5] = tempDGV[5, iX].Value.ToString(); // 枚数 oxlsSheet.Cells[iX + S_GYO, 6] = tempDGV[6, iX].Value.ToString(); // 売上 oxlsSheet.Cells[iX + S_GYO, 7] = tempDGV[7, iX].Value.ToString(); // 営業原価 2015/09/18 oxlsSheet.Cells[iX + S_GYO, 8] = tempDGV[8, iX].Value.ToString(); // 粗利1 2015/09/18 // 外注費1,2,3合計 2016/10/24 double g = Utility.strToDouble(tempDGV[9, iX].Value.ToString()) + Utility.strToDouble(tempDGV[10, iX].Value.ToString()) + Utility.strToDouble(tempDGV[11, iX].Value.ToString()); oxlsSheet.Cells[iX + S_GYO, 9] = g.ToString(); // 外注費 2016/10/24 oxlsSheet.Cells[iX + S_GYO, 10] = tempDGV[12, iX].Value.ToString(); // 粗利2 2015/09/18 oxlsSheet.Cells[iX + S_GYO, 11] = tempDGV[13, iX].Value.ToString(); // 粗利差異 2015/09/18 } //セル上部へ実線ヨコ罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //セル下部へ実線ヨコ罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体に実線縦罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の左端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の右端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, S_ROWSMAX]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //マウスポインタを元に戻す this.Cursor = Cursors.Default; // 確認のためExcelのウィンドウを表示する oXls.Visible = true; //印刷 oxlsSheet.PrintPreview(true); // ウィンドウを非表示にする oXls.Visible = false; //保存処理 oXls.DisplayAlerts = false; DialogResult ret; string msgHD = ""; if (tDate.Checked == true) { msgHD += tDate.Value.ToLongDateString() + "から"; } if (tDate2.Checked == true) { msgHD += tDate2.Value.ToLongDateString() + "まで"; } //ダイアログボックスの初期設定 saveFileDialog1.Title = MESSAGE_CAPTION; saveFileDialog1.OverwritePrompt = true; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.FileName = MESSAGE_CAPTION + "_" + comboBox1.Text + "_" + msgHD; saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xls)|*.xls|全てのファイル(*.*)|*.*"; //ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示 string fileName; ret = saveFileDialog1.ShowDialog(); if (ret == System.Windows.Forms.DialogResult.OK) { fileName = saveFileDialog1.FileName; oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } //Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); //Excelを終了 oXls.Quit(); } catch (Exception e) { MessageBox.Show(e.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); //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, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } //マウスポインタを元に戻す this.Cursor = Cursors.Default; }
public void PrintPreview() { xlSheet.PrintPreview(xlMissing); }
public void ExportData(List <WarehouseProductNumModel> dn, bool isPrint) { try { xls.Application xlApp = new xls.Application(); xls.Workbook xlWorkBook = xlApp.Workbooks.Add(true); xls.Worksheet xlWorkSheet = (xls.Worksheet)xlWorkBook.Worksheets.get_Item(1); //这里设置行高 // ((xls.Range)xlWorkSheet.Rows).RowHeight = 11; xlWorkSheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.19685); xlWorkSheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.19685); xlWorkSheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.19685); xlWorkSheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.19685); xlWorkSheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.19685); xlWorkSheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.19685); //xlWorkSheet.Columns.AutoFit(); xlWorkSheet.Cells.Font.Size = 10; ((xls.Range)xlWorkSheet.Cells[1, 2]).HorizontalAlignment = xls.XlVAlign.xlVAlignCenter; xlWorkSheet.Cells[1, 2] = "库存情况清单"; xlWorkSheet.Cells[2, 1] = "'序号"; xlWorkSheet.Cells[2, 2] = "'编号"; xlWorkSheet.Cells[2, 3] = "'名称"; xlWorkSheet.Cells[2, 4] = "'数量"; xlWorkSheet.Cells[2, 5] = "'包装数"; int rowid = 3; int sumAmount = 0; int sumPackNum = 0; int count = dn.Count; foreach (WarehouseProductNumModel m in dn) { xlWorkSheet.Cells[rowid, 1] = "'" + (rowid - 1); xlWorkSheet.Cells[rowid, 2] = "'" + m.ProductNumber; xlWorkSheet.Cells[rowid, 3] = "'" + m.ProductName; xlWorkSheet.Cells[rowid, 4] = "'" + m.Quantity; xlWorkSheet.Cells[rowid, 5] = "'" + m.PackageNum; sumAmount += m.Quantity; sumPackNum += m.PackageNum; rowid++; } xlWorkSheet.Cells[3 + count, 3] = "'总计"; xlWorkSheet.Cells[3 + count, 4] = "'" + sumAmount; xlWorkSheet.Cells[3 + count, 5] = "'" + sumPackNum; xlApp.Visible = true; if (isPrint) { xlWorkSheet.PrintPreview(); } releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } catch (Exception e) { Console.WriteLine(e.Message); } }
private void KanryoReport(DataGridView g) { const int S_GYO = 4; // エクセルファイル見出し行(明細は4行目から印字) const int S_ROWSMAX = 14; // エクセルファイル列最大値 int r = S_GYO; // 印刷する行 bool tl = true; 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(sAppPath + Properties.Settings.Default.エクセル振り表, 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 { // 対象配布日範囲 string hs = string.Empty; string he = string.Empty; if (iraiDtS.Checked) { hs = iraiDtS.Value.ToShortDateString(); } if (iraiDtE.Checked) { he = iraiDtE.Value.ToShortDateString(); } oxlsSheet.Cells[1, 3] = "配布日: " + hs + " 〜 " + he; // 明細 //for (int i = 0; i < 10; i++) //{ for (int iX = 0; iX <= g.RowCount - 1; iX++) { if (g[0, iX].Value != null) { // 1行空き oxlsSheet.Cells[r, 1] = " "; //セル下部へ実線ヨコ罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; r++; // 明細 oxlsSheet.Cells[r, 1] = g[0, iX].Value.ToString(); // 依頼日 oxlsSheet.Cells[r, 2] = g[1, iX].Value.ToString(); // 受注番号 oxlsSheet.Cells[r, 3] = g[2, iX].Value.ToString(); // クライアント名 oxlsSheet.Cells[r, 4] = g[3, iX].Value.ToString(); // チラシ名 oxlsSheet.Cells[r, 5] = g[4, iX].Value.ToString(); // サイズ oxlsSheet.Cells[r, 6] = g[5, iX].Value.ToString(); // 受注単価 oxlsSheet.Cells[r, 7] = g[6, iX].Value.ToString(); // 枚数 oxlsSheet.Cells[r, 8] = g[7, iX].Value.ToString(); // 予定表の配布期間 oxlsSheet.Cells[r, 9] = g[8, iX].Value.ToString(); // 渡した日 oxlsSheet.Cells[r, 10] = g[9, iX].Value.ToString(); // 渡した担当者 oxlsSheet.Cells[r, 11] = g[10, iX].Value.ToString(); // oxlsSheet.Cells[r, 12] = g[11, iX].Value.ToString(); // 振り先 oxlsSheet.Cells[r, 13] = g[12, iX].Value.ToString(); // 振り単価 oxlsSheet.Cells[r, 14] = g[13, iX].Value.ToString(); // 営業担当 } else { if (tl) { // 1行空き oxlsSheet.Cells[r, 1] = " "; //セル下部へ実線ヨコ罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; r++; } // 振り先別合計 oxlsSheet.Cells[r, 12] = g[11, iX].Value.ToString(); // 振り先 oxlsSheet.Cells[r, 13] = g[12, iX].Value.ToString(); // 振り合計 tl = false; } //セル下部へ実線ヨコ罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; r++; } //} //////セル上部へ実線ヨコ罫線を引く //rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; //rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; //oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ////セル下部へ実線ヨコ罫線を引く //rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; //rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; //oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // 表全体に実線縦罫線を引く rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // 表全体の左端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; // 表全体の右端縦罫線 rng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, S_ROWSMAX]; rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //// 外注先別:支払額 //var s = dts.受注1 // .Where(a => !a.Is外注依頼日支払Null()) // .GroupBy(a => new { a.外注先ID支払, a.外注先Row.名称 }) // .Select(gg => new // { // gg.Key.外注先ID支払, // gg.Key.名称, // 合計 = gg.Sum(a => (decimal)a.枚数 * a.外注原価支払) // }); //r++; //foreach (var t in s) //{ // oxlsSheet.Cells[r, 10] = t.名称; // 振り先 // oxlsSheet.Cells[r, 11] = t.合計; // 振り合計 // r++; //} // マウスポインタを元に戻す this.Cursor = Cursors.Default; // 確認のためExcelのウィンドウを表示する oXls.Visible = true; // 印刷 oxlsSheet.PrintPreview(true); // ウィンドウを非表示にする oXls.Visible = false; // 保存処理 oXls.DisplayAlerts = false; DialogResult ret; // ダイアログボックスの初期設定 saveFileDialog1.Title = MESSAGE_CAPTION; saveFileDialog1.OverwritePrompt = true; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.FileName = MESSAGE_CAPTION; saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xls)|*.xls|全てのファイル(*.*)|*.*"; // ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示 string fileName; ret = saveFileDialog1.ShowDialog(); if (ret == System.Windows.Forms.DialogResult.OK) { fileName = saveFileDialog1.FileName; oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } // Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); // Excelを終了 oXls.Quit(); } catch (Exception e) { MessageBox.Show(e.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); // 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, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } //マウスポインタを元に戻す this.Cursor = Cursors.Default; }
private void EigyoReport(DataGridView tempDGV, object [,] rtnArray) { const int S_GYO = 6; // エクセルファイル明細印刷開始行 //const int S_ROWSMAX = 8; // エクセルファイル列最大値 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(sAppPath + Properties.Settings.Default.エクセルクライアント別受注別集計, 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[] aRng = new Microsoft.Office.Interop.Excel.Range[2]; Excel.Range rng; try { // 見出し oxlsSheet.Cells[3, 1] = _msg; // 配列からシートセルに一括してデータをセットします rng = oxlsSheet.Range[oxlsSheet.Cells[6, 1], oxlsSheet.Cells[6 + rtnArray.GetLength(0) - 1, oxlsSheet.UsedRange.Columns.Count]]; rng.Value2 = rtnArray; ////////セル上部へ実線ヨコ罫線を引く //////rng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; //////rng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, S_ROWSMAX]; //////oxlsSheet.get_Range(rng[0], rng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //セル下部へ実線ヨコ罫線を引く aRng[0] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; aRng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, oxlsSheet.UsedRange.Columns.Count]; oxlsSheet.get_Range(aRng[0], aRng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体に実線縦罫線を引く aRng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; aRng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, oxlsSheet.UsedRange.Columns.Count]; oxlsSheet.get_Range(aRng[0], aRng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の左端縦罫線 aRng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, 1]; aRng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, 1]; oxlsSheet.get_Range(aRng[0], aRng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //表全体の右端縦罫線 aRng[0] = (Excel.Range)oxlsSheet.Cells[S_GYO, oxlsSheet.UsedRange.Columns.Count]; aRng[1] = (Excel.Range)oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, oxlsSheet.UsedRange.Columns.Count]; oxlsSheet.get_Range(aRng[0], aRng[1]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; //マウスポインタを元に戻す this.Cursor = Cursors.Default; // 確認のためExcelのウィンドウを表示する oXls.Visible = true; //印刷 oxlsSheet.PrintPreview(true); // ウィンドウを非表示にする oXls.Visible = false; //保存処理 oXls.DisplayAlerts = false; DialogResult ret; //ダイアログボックスの初期設定 saveFileDialog1.Title = "クライアント別受注別集計"; saveFileDialog1.OverwritePrompt = true; saveFileDialog1.RestoreDirectory = true; DateTime dt = DateTime.Now; saveFileDialog1.FileName = this.Text; saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xlsx)|*.xlsx|全てのファイル(*.*)|*.*"; //ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示 string fileName; ret = saveFileDialog1.ShowDialog(); if (ret == System.Windows.Forms.DialogResult.OK) { fileName = saveFileDialog1.FileName; oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } } catch (Exception e) { MessageBox.Show(e.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } finally { //Bookをクローズ oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing); //Excelを終了 oXls.Quit(); // COM オブジェクトの参照カウントを解放する System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(oXlsBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(oXls); oXls = null; oXlsBook = null; oxlsSheet = null; GC.Collect(); //マウスポインタを元に戻す this.Cursor = Cursors.Default; } } catch (Exception e) { MessageBox.Show(e.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } //マウスポインタを元に戻す this.Cursor = Cursors.Default; }
public void createSAAOExcel(List <Entities.SAAOModel> SAAO) { DateTimeFormatInfo dateFormat = new DateTimeFormatInfo(); ExApp = new Excel.Application(); ExApp.Visible = false; ExBook = ExApp.Workbooks.Open("C:\\SAAO.xls"); ExSheet = (Excel.Worksheet)ExBook.Sheets[1]; string month = dateFormat.GetMonthName(DateTime.Now.Month); /////////////// CAPITAL OUTLAY /////////////// // Month, Year ExSheet.Cells[7, 1] = "as of " + month + ", " + DateTime.Now.Year; //10702990 ExSheet.Cells[95, 6] = SAAO[0].AB; ExSheet.Cells[95, 9] = SAAO[0].Amount; //10704020 ExSheet.Cells[96, 6] = SAAO[1].AB; ExSheet.Cells[96, 9] = SAAO[1].Amount; //10705020 ExSheet.Cells[97, 6] = SAAO[2].AB; ExSheet.Cells[97, 9] = SAAO[2].Amount; //10705030 ExSheet.Cells[98, 6] = SAAO[3].AB; ExSheet.Cells[98, 9] = SAAO[3].Amount; //10705070 ExSheet.Cells[99, 6] = SAAO[4].AB; ExSheet.Cells[99, 9] = SAAO[4].Amount; //10705090 ExSheet.Cells[100, 6] = SAAO[5].AB; ExSheet.Cells[100, 9] = SAAO[5].Amount; //10705110 ExSheet.Cells[101, 6] = SAAO[6].AB; ExSheet.Cells[101, 9] = SAAO[6].Amount; //10705130 ExSheet.Cells[102, 6] = SAAO[7].AB; ExSheet.Cells[102, 9] = SAAO[7].Amount; //10705140 ExSheet.Cells[103, 6] = SAAO[8].AB; ExSheet.Cells[103, 9] = SAAO[8].Amount; //10705990 ExSheet.Cells[104, 6] = SAAO[9].AB; ExSheet.Cells[104, 9] = SAAO[9].Amount; //10707010 ExSheet.Cells[105, 6] = SAAO[10].AB; ExSheet.Cells[105, 9] = SAAO[10].Amount; //10707020 ExSheet.Cells[106, 6] = SAAO[11].AB; ExSheet.Cells[106, 9] = SAAO[11].Amount; //50101010 ExSheet.Cells[16, 6] = SAAO[12].AB; ExSheet.Cells[16, 9] = SAAO[12].Amount; //50101020.1 ExSheet.Cells[18, 6] = SAAO[13].AB; ExSheet.Cells[18, 9] = SAAO[13].Amount; //50101020.2 ExSheet.Cells[19, 6] = SAAO[14].AB; ExSheet.Cells[19, 9] = SAAO[14].Amount; //50102010 ExSheet.Cells[20, 6] = SAAO[15].AB; ExSheet.Cells[20, 9] = SAAO[15].Amount; //50102020 ExSheet.Cells[21, 6] = SAAO[16].AB; ExSheet.Cells[21, 9] = SAAO[16].Amount; //50102030 ExSheet.Cells[22, 6] = SAAO[17].AB; ExSheet.Cells[22, 9] = SAAO[17].Amount; //50102040 ExSheet.Cells[23, 6] = SAAO[18].AB; ExSheet.Cells[23, 9] = SAAO[18].Amount; //50102050 ExSheet.Cells[25, 6] = SAAO[19].AB; ExSheet.Cells[25, 9] = SAAO[19].Amount; //50102060 ExSheet.Cells[26, 6] = SAAO[20].AB; ExSheet.Cells[26, 9] = SAAO[20].Amount; //50102100 ExSheet.Cells[24, 6] = SAAO[21].AB; ExSheet.Cells[24, 9] = SAAO[21].Amount; //50102110 ExSheet.Cells[27, 6] = SAAO[22].AB; ExSheet.Cells[27, 9] = SAAO[22].Amount; //50102120 ExSheet.Cells[28, 6] = SAAO[23].AB; ExSheet.Cells[28, 9] = SAAO[23].Amount; //50102130 ExSheet.Cells[29, 6] = SAAO[24].AB; ExSheet.Cells[29, 9] = SAAO[24].Amount; //50102140 ExSheet.Cells[31, 6] = SAAO[25].AB; ExSheet.Cells[31, 9] = SAAO[25].Amount; //50102150 ExSheet.Cells[30, 6] = SAAO[26].AB; ExSheet.Cells[30, 9] = SAAO[26].Amount; //50102990 ExSheet.Cells[32, 6] = SAAO[27].AB; ExSheet.Cells[32, 9] = SAAO[27].Amount; //50103010 ExSheet.Cells[33, 6] = SAAO[28].AB; ExSheet.Cells[33, 9] = SAAO[28].Amount; //50103020 ExSheet.Cells[34, 6] = SAAO[29].AB; ExSheet.Cells[34, 9] = SAAO[29].Amount; //50103030 ExSheet.Cells[35, 6] = SAAO[30].AB; ExSheet.Cells[35, 9] = SAAO[30].Amount; //50103040 ExSheet.Cells[36, 6] = SAAO[31].AB; ExSheet.Cells[36, 9] = SAAO[31].Amount; //50104030 ExSheet.Cells[37, 6] = SAAO[32].AB; ExSheet.Cells[37, 9] = SAAO[32].Amount; //50104990 ExSheet.Cells[38, 6] = SAAO[33].AB; ExSheet.Cells[38, 9] = SAAO[33].Amount; //50201010 ExSheet.Cells[50, 6] = SAAO[34].AB; ExSheet.Cells[50, 9] = SAAO[34].Amount; //50201020 ExSheet.Cells[51, 6] = SAAO[35].AB; ExSheet.Cells[51, 9] = SAAO[35].Amount; //50202010 ExSheet.Cells[52, 6] = SAAO[36].AB; ExSheet.Cells[52, 9] = SAAO[36].Amount; //50203010 ExSheet.Cells[53, 6] = SAAO[37].AB; ExSheet.Cells[53, 9] = SAAO[37].Amount; //50203020 ExSheet.Cells[54, 6] = SAAO[38].AB; ExSheet.Cells[54, 9] = SAAO[38].Amount; //50203070 ExSheet.Cells[55, 6] = SAAO[39].AB; ExSheet.Cells[55, 9] = SAAO[39].Amount; //50203080 ExSheet.Cells[56, 6] = SAAO[40].AB; ExSheet.Cells[56, 9] = SAAO[40].Amount; //50203090 ExSheet.Cells[57, 6] = SAAO[41].AB; ExSheet.Cells[57, 9] = SAAO[41].Amount; //50203990 ExSheet.Cells[58, 6] = SAAO[42].AB; ExSheet.Cells[58, 9] = SAAO[42].Amount; //50204010 ExSheet.Cells[59, 6] = SAAO[43].AB; ExSheet.Cells[59, 9] = SAAO[43].Amount; //50204020 ExSheet.Cells[60, 6] = SAAO[44].AB; ExSheet.Cells[60, 9] = SAAO[44].Amount; //50205010 ExSheet.Cells[61, 6] = SAAO[45].AB; ExSheet.Cells[61, 9] = SAAO[45].Amount; //50205020.1 ExSheet.Cells[62, 6] = SAAO[46].AB; ExSheet.Cells[62, 9] = SAAO[46].Amount; //50205020.2 ExSheet.Cells[63, 6] = SAAO[47].AB; ExSheet.Cells[63, 9] = SAAO[47].Amount; //50205030 ExSheet.Cells[64, 6] = SAAO[48].AB; ExSheet.Cells[64, 9] = SAAO[48].Amount; //50210030 ExSheet.Cells[65, 6] = SAAO[49].AB; ExSheet.Cells[65, 9] = SAAO[49].Amount; //50211030 ExSheet.Cells[66, 6] = SAAO[50].AB; ExSheet.Cells[66, 9] = SAAO[50].Amount; //50211990 ExSheet.Cells[67, 6] = SAAO[51].AB; ExSheet.Cells[67, 9] = SAAO[51].Amount; //50212020 ExSheet.Cells[68, 6] = SAAO[52].AB; ExSheet.Cells[68, 9] = SAAO[52].Amount; //50212030 ExSheet.Cells[69, 6] = SAAO[53].AB; ExSheet.Cells[69, 9] = SAAO[53].Amount; //50213040 ExSheet.Cells[70, 6] = SAAO[54].AB; ExSheet.Cells[70, 9] = SAAO[54].Amount; //50213050.1 ExSheet.Cells[71, 6] = SAAO[55].AB; ExSheet.Cells[71, 9] = SAAO[55].Amount; //50213050.2 ExSheet.Cells[72, 6] = SAAO[56].AB; ExSheet.Cells[72, 9] = SAAO[56].Amount; //50213050.3 ExSheet.Cells[73, 6] = SAAO[57].AB; ExSheet.Cells[73, 9] = SAAO[57].Amount; //50213050.4 ExSheet.Cells[74, 6] = SAAO[58].AB; ExSheet.Cells[74, 9] = SAAO[58].Amount; //50213050.5 ExSheet.Cells[75, 6] = SAAO[59].AB; ExSheet.Cells[75, 9] = SAAO[59].Amount; //50213050.6 ExSheet.Cells[76, 6] = SAAO[60].AB; ExSheet.Cells[76, 9] = SAAO[60].Amount; //50213050.7 ExSheet.Cells[77, 6] = SAAO[61].AB; ExSheet.Cells[77, 9] = SAAO[61].Amount; //50213060 ExSheet.Cells[78, 6] = SAAO[62].AB; ExSheet.Cells[78, 9] = SAAO[62].Amount; //50213070 ExSheet.Cells[79, 6] = SAAO[63].AB; ExSheet.Cells[79, 9] = SAAO[63].Amount; //50216020 ExSheet.Cells[80, 6] = SAAO[64].AB; ExSheet.Cells[80, 9] = SAAO[64].Amount; //50216030 ExSheet.Cells[81, 6] = SAAO[65].AB; ExSheet.Cells[81, 9] = SAAO[65].Amount; //50299010 ExSheet.Cells[82, 6] = SAAO[66].AB; ExSheet.Cells[82, 9] = SAAO[66].Amount; //50299020 ExSheet.Cells[83, 6] = SAAO[67].AB; ExSheet.Cells[83, 9] = SAAO[67].Amount; //50299030 ExSheet.Cells[84, 6] = SAAO[68].AB; ExSheet.Cells[84, 9] = SAAO[68].Amount; //50299050 ExSheet.Cells[85, 6] = SAAO[69].AB; ExSheet.Cells[85, 9] = SAAO[69].Amount; //50299060 ExSheet.Cells[86, 6] = SAAO[70].AB; ExSheet.Cells[86, 9] = SAAO[70].Amount; //50299070 ExSheet.Cells[87, 6] = SAAO[71].AB; ExSheet.Cells[87, 9] = SAAO[71].Amount; //50299990 ExSheet.Cells[88, 6] = SAAO[72].AB; ExSheet.Cells[88, 9] = SAAO[72].Amount; //50301040 ExSheet.Cells[92, 6] = SAAO[73].AB; ExSheet.Cells[92, 9] = SAAO[73].Amount; try { ExSheet.SaveAs("DBMS\\SAAO\\SAAOAsOf_" + month + ".xlsx"); } catch { } finally { if (MessageBox.Show("Do you want to continue to printing?", "Print?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { ExSheet.PrintOutEx(); } ExApp.Visible = true; ExSheet.PrintPreview(); ExBook.Close(); } }