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");
}
}