/// <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(); // 粗利１ 2015/09/18
// 外注費１，２，３合計 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(); // 粗利２ 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();
}
}
