public HacchuuSho()
 {
     InitializeComponent();
     cf   = new CommonFunction();
     be   = new BaseEntity();
     hsbl = new HacchuuShoBL();
 }
        private void Excel_Export()
        {
            #region Coolumn Style
            hsbl = new HacchuuShoBL();
            DataTable dt = new DataTable();
            dt = hsbl.Get_ExportData(Get_UIData());
            if (dt.Rows.Count == 0)
            {
                bbl.ShowMessage("S013"); //HET
                return;
            }
            Excel.Application xlApp;
            Excel.Workbook    xlWorkBook;
            Excel.Worksheet   xlWorkSheet;
            object            misValue = System.Reflection.Missing.Value;

            xlApp       = new Excel.Application();
            xlWorkBook  = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet = xlWorkBook.ActiveSheet;
            //xlWorkSheet.Name = "発注書(PURCHASE ORDER)";
            xlWorkSheet.Name    = txtKanriNO.Text;//ssa
            xlApp.DisplayAlerts = false;
            if (!Directory.Exists(tmpSave))
            {
                Directory.CreateDirectory(tmpSave);;
            }
            SaveFileDialog savedialog = new SaveFileDialog();
            savedialog.Filter           = "Excel Files|*.xlsx;";
            savedialog.Title            = "Save";
            savedialog.FileName         = "PURCHASE_ORDER" + "_" + txtKanriNO.Text + ".xlsx";
            savedialog.InitialDirectory = tmpSave;
            savedialog.RestoreDirectory = true;
            try
            {
                if (savedialog.ShowDialog() == DialogResult.OK)
                {
                    if (Path.GetExtension(savedialog.FileName).Contains(".xlsx"))
                    {
                        //            try
                        //{
                        //xlWorkSheet.PageSetup.PrintArea = "A1:V1000";
                        xlWorkSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape; //Page horizontal
                        xlWorkSheet.PageSetup.Zoom        = 75;                                                           //page setting when printing, a few percent of the scale
                        xlWorkSheet.PageSetup.Zoom        = false;                                                        //Page setting when printing, must be set to false, page height, page width is valid
                                                                                                                          //try
                                                                                                                          //{
                        xlWorkSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;           //paper size
                                                                                                                          //}
                                                                                                                          //catch { }
                        xlWorkSheet.PageSetup.FitToPagesWide = 1;                                                         //Set the page width of the page to be 1 page wide
                        xlWorkSheet.PageSetup.FitToPagesTall = false;                                                     //Set the page height of the page zoom automatically
                                                                                                                          //}
                                                                                                                          //catch (Exception ex)
                                                                                                                          //{
                                                                                                                          //    var msg = ex.Message;
                                                                                                                          //}

                        xlApp.ActiveWindow.View             = Excel.XlWindowView.xlNormalView;
                        xlApp.ActiveWindow.DisplayGridlines = false;

                        xlWorkSheet.Columns[2].ColumnWidth  = 10;
                        xlWorkSheet.Columns[3].ColumnWidth  = 20;
                        xlWorkSheet.Columns[4].ColumnWidth  = 15;
                        xlWorkSheet.Columns[5].ColumnWidth  = 20;
                        xlWorkSheet.Columns[6].ColumnWidth  = 20;
                        xlWorkSheet.Columns[7].ColumnWidth  = 15;
                        xlWorkSheet.Columns[8].ColumnWidth  = 15;
                        xlWorkSheet.Columns[9].ColumnWidth  = 5;
                        xlWorkSheet.Columns[10].ColumnWidth = 5;
                        xlWorkSheet.Columns[11].ColumnWidth = 5;
                        xlWorkSheet.Columns[12].ColumnWidth = 5;
                        xlWorkSheet.Columns[13].ColumnWidth = 5;
                        xlWorkSheet.Columns[14].ColumnWidth = 5;
                        xlWorkSheet.Columns[15].ColumnWidth = 5;
                        xlWorkSheet.Columns[16].ColumnWidth = 5;
                        xlWorkSheet.Columns[17].ColumnWidth = 5;
                        xlWorkSheet.Columns[18].ColumnWidth = 5;
                        xlWorkSheet.Columns[19].ColumnWidth = 5;
                        xlWorkSheet.Columns[20].ColumnWidth = 10;
                        xlWorkSheet.Columns[21].ColumnWidth = 15;

                        #endregion

                        var dtsupplier = dt.Select("SiiresakiCD is not null").CopyToDataTable().AsEnumerable().Select(r => r.Field <string>("SiiresakiCD")).Distinct().ToList();

                        int col = 1; int startrow = 11; int gvrow = 16;
                        for (int j = 0; j < dtsupplier.Count(); j++)
                        {
                            int pageCount = 1;
                            int gyoCount  = 0;

                            //if (j > 0) break;
                            SetHeader(xlWorkSheet, dt, xlApp, col);

                            string SiiresakiCD = dtsupplier[j].ToString();

                            var dtgv = dt.AsEnumerable().Where(s => s.Field <string>("SiiresakiCD") == SiiresakiCD).CopyToDataTable();
                            var dgv  = dtgv.AsEnumerable().GroupBy(x => x.Field <string>("ColorNo"), x => x.Field <string>("ModelNo")).Count(); // get Model and Color
                            //「FLOOR、MEMO」で Distinct したレコードを取得
                            var dtDis = dtgv.DefaultView.ToTable(true, "ColorNo", "ModelNo");
                            dgv = dtDis.Rows.Count; //仕入先CD毎の行数

                            var dvresult = dtgv.AsEnumerable().GroupBy(r => new { Col1 = r["ColorNo"], Col2 = r["ModelNo"] }).Select(g => {
                                var row        = dt.NewRow();
                                row["Pairs"]   = g.Sum(r => r.Field <int>("Pairs"));
                                row["Amount"]  = g.Sum(r => r.Field <decimal>("Amount"));
                                row["ColorNo"] = g.Key.Col1;
                                row["ModelNo"] = g.Key.Col2;
                                return(row);
                            }).CopyToDataTable();

                            #region Style top part
                            SetStyleTopPart(xlWorkSheet, startrow, dtgv, xlApp);
                            #endregion

                            //col = (gvrow + 1) + dgv;
                            col = gvrow + 1 + MaxRow;
                            #region Child Style
                            SetChildStyle(xlWorkSheet, gvrow, col, xlApp);
                            #endregion

                            #region Header Supplier
                            SetHeaderSupplier(xlWorkSheet, gvrow);
                            #endregion

                            #region Child Item
                            var    modelno = ""; var otherModel = 0; var colorno = ""; var otherCkolor = "";
                            double sum1 = 0; double sum2 = 0;
                            //ShiiresakiCD毎の件数
                            for (int h = 0; h < dtgv.Rows.Count; h++)
                            {
                                //改ページ条件
                                if ((modelno != dtgv.Rows[h]["ModelNo"].ToString() || colorno != dtgv.Rows[h]["ColorNo"].ToString())  && otherModel > 0 && otherModel % MaxRow == 0)
                                {
                                    pageCount++;

                                    //最終ページでない場合
                                    if (gyoCount != dgv)
                                    {
                                        xlWorkSheet.get_Range("B" + (gvrow), "U" + (gvrow)).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FFF2CC");
                                        xlWorkSheet.Cells[col, 20].Formula = "=Sum(" + xlWorkSheet.Cells[gvrow + 1, 20].Address + ":" + xlWorkSheet.Cells[col - 1, 20].Address + ")";
                                        xlWorkSheet.Cells[col, 21].Formula = "=Sum(" + xlWorkSheet.Cells[gvrow + 1, 21].Address + ":" + xlWorkSheet.Cells[col - 1, 21].Address + ")";

                                        sum1 += xlWorkSheet.Cells[col, 20].value;
                                        sum2 += xlWorkSheet.Cells[col, 21].value;

                                        xlWorkSheet.Cells[col, 20] = "****";
                                        xlWorkSheet.Cells[col, 21] = "**********";

                                        SetFooter(xlWorkBook, xlWorkSheet, col);
                                        col     += 17 - 1;
                                        startrow = col + 10;
                                        gvrow    = startrow + 5;

                                        //次のページの設定
                                        SetHeader(xlWorkSheet, dt, xlApp, col);

                                        #region Style top part
                                        SetStyleTopPart(xlWorkSheet, startrow, dtgv, xlApp);
                                        #endregion

                                        //col = (gvrow + 1) + dgv;
                                        col = gvrow + 1 + MaxRow;
                                        #region Child Style
                                        SetChildStyle(xlWorkSheet, gvrow, col, xlApp);
                                        #endregion

                                        #region Header Supplier
                                        SetHeaderSupplier(xlWorkSheet, gvrow);
                                        #endregion

                                        otherModel = 0;
                                    }
                                }
                                //改行条件
                                if (modelno != dtgv.Rows[h]["ModelNo"].ToString())
                                {
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 2] = dtgv.Rows[h]["ModelNo"].ToString();
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 3] = dtgv.Rows[h]["ModelName"].ToString();
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 4] = dtgv.Rows[h]["FOBPRICE"].ToString();
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 5] = dtgv.Rows[h]["JAPANColor"].ToString();
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 6] = dtgv.Rows[h]["KOREAColor"].ToString();
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 7] = dtgv.Rows[h]["Shippingplace"].ToString();
                                    //Yellow_Color_Change_Item ==> [ModelNO]
                                    var Cell_Color  = (Excel.Range)xlWorkSheet.Cells[gvrow + (otherModel + 1), 2];
                                    var Cell_Color1 = (Excel.Range)xlWorkSheet.Cells[gvrow + (otherModel + 1), 5];
                                    //if (dtgv.Rows[h]["HacchuuLotFLG"].ToString().Equals("1"))
                                    //{
                                    //    Cell_Color.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#F0F179");
                                    //    Cell_Color1.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#F0F179");
                                    //}
                                    //try
                                    //{
                                    SettingImg(dtgv.Rows[h]["IMAGE"] as byte[]);
                                    SetImage(gvrow + (otherModel + 1), 8, tmpPath, xlWorkSheet);
                                    //}
                                    //catch (Exception ex) { var msg = ex.Message; }

                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 20] = dtgv.Rows[h]["Pairs"].ToString();
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 21] = dtgv.Rows[h]["Amount"].ToString();
                                    modelno = dtgv.Rows[h]["ModelNo"].ToString();
                                    colorno = dtgv.Rows[h]["ColorNo"].ToString();

                                    int rowIndex = gvrow + otherModel;
                                    xlWorkSheet.get_Range("B" + rowIndex, "U" + rowIndex).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d;

                                    otherModel++;
                                    gyoCount++;
                                    //xlWorkSheet.get_Range("B" + (otherModel + 1), "U" + gvrow + (otherModel + 1)).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d;
                                }
                                else if (modelno == dtgv.Rows[h]["ModelNo"].ToString() && colorno != dtgv.Rows[h]["ColorNo"].ToString())
                                {
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 2] = dtgv.Rows[h]["ModelNo"].ToString();
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 3] = dtgv.Rows[h]["ModelName"].ToString();
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 4] = dtgv.Rows[h]["FOBPRICE"].ToString();
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 5] = dtgv.Rows[h]["JAPANColor"].ToString();
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 6] = dtgv.Rows[h]["KOREAColor"].ToString();
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 7] = dtgv.Rows[h]["Shippingplace"].ToString();

                                    //Yellow_Color_Change_Item ==> [ModelNO]
                                    var Cell_Color  = (Excel.Range)xlWorkSheet.Cells[gvrow + (otherModel + 1), 2];
                                    var Cell_Color1 = (Excel.Range)xlWorkSheet.Cells[gvrow + (otherModel + 1), 5];

                                    //if (dtgv.Rows[h]["HacchuuLotFLG"].ToString().Equals("1"))
                                    //{
                                    //    Cell_Color.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#F0F179");
                                    //    Cell_Color1.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#F0F179");
                                    //}

                                    //try
                                    //    {
                                    SettingImg(dtgv.Rows[h]["IMAGE"] as byte[]);
                                    SetImage(gvrow + (otherModel + 1), 8, tmpPath, xlWorkSheet);
                                    //}
                                    //catch (Exception ex) { var msg = ex.Message; }
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 20] = dtgv.Rows[h]["Pairs"].ToString();
                                    xlWorkSheet.Cells[gvrow + (otherModel + 1), 21] = dtgv.Rows[h]["Amount"].ToString();
                                    modelno = dtgv.Rows[h]["ModelNo"].ToString();
                                    colorno = dtgv.Rows[h]["ColorNo"].ToString();
                                    otherModel++;
                                    gyoCount++;
                                }

                                xlWorkSheet.Cells[gvrow + (otherModel), GetSizeTitile(dtgv.Rows[h]["SizeNo"].ToString())] = dtgv.Rows[h]["HacchuuSuu"].ToString();
                                //Yellow_Color_Change_Item ==> [ModelNO]
                                if (dtgv.Rows[h]["HacchuuLotFLG"].ToString().Equals("1"))
                                {
                                    var Cell_Color2 = xlWorkSheet.Cells[gvrow + (otherModel), GetSizeTitile(dtgv.Rows[h]["SizeNo"].ToString())];
                                    Cell_Color2.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#F0F179");
                                }
                            }

                            int rowNo = gvrow + otherModel;
                            xlWorkSheet.get_Range("B" + rowNo, "U" + rowNo).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d;

                            xlWorkSheet.get_Range("B" + (gvrow), "U" + (gvrow)).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FFF2CC");
                            xlWorkSheet.Cells[col, 20].Formula = "=Sum(" + xlWorkSheet.Cells[gvrow + 1, 20].Address + ":" + xlWorkSheet.Cells[col - 1, 20].Address + ")";
                            xlWorkSheet.Cells[col, 21].Formula = "=Sum(" + xlWorkSheet.Cells[gvrow + 1, 21].Address + ":" + xlWorkSheet.Cells[col - 1, 21].Address + ")";

                            //xlWorkSheet.Cells[col, 8] = "TOTAL";
                            if (sum1 != 0 || sum2 != 0)
                            {
                                sum1 += xlWorkSheet.Cells[col, 20].value;
                                sum2 += xlWorkSheet.Cells[col, 21].value;

                                xlWorkSheet.Cells[col, 20] = sum1;
                                xlWorkSheet.Cells[col, 21] = sum2;
                            }
                            #endregion

                            SetFooter(xlWorkBook, xlWorkSheet, col);
                            col     += 17 - 1;
                            startrow = col + 10;
                            gvrow    = startrow + 5;
                        }

                        xlWorkSheet.PageSetup.PrintArea = "A1:V" + (col - 1);

                        // Footers

                        xlApp.get_Range("C" + col + 4, "L" + col + 4).Merge(Type.Missing);
                        var dtnow = DateTime.Now.ToString("yyyyMMdd HHmmss");
                        xlWorkBook.SaveAs(savedialog.FileName, misValue, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                        xlWorkBook.Close(true, misValue, misValue);
                        xlApp.Quit();
                        bbl.ShowMessage("I203", string.Empty, string.Empty, string.Empty, string.Empty, string.Empty);
                        Process.Start(System.IO.Path.GetDirectoryName(savedialog.FileName));
                        Clear(); //HET
                    }
                }
            }
            catch (Exception ex1)
            {
                bbl.ShowMessage("E122");
            }
        }