View Code
1 private void btnExport_Click(object sender, EventArgs e) 2 { 3 foreach (System.Windows.Forms.Control con in this.Controls) 4 { 5 if (!(con is Label)) 6 { 7 if (con.Text == "") 8 { 9 MsgBox("001", "Please input Lot No."); 10 return; 11 } 12 } 13 } 14 Cursor = Cursors.WaitCursor; 15 16 string strWhere_facd105=""; 17 string strWhere = ""; 18 if (dtpStart.Checked) 19 strWhere_facd105 = gFun.GenWhere(strWhere_facd105) + " w_day>='" + dtpStart.Text.Trim().Replace("'", "''") + "' "; 20 if (dtpEnd.Checked) 21 strWhere_facd105 = gFun.GenWhere(strWhere_facd105) + " w_day<='" + dtpEnd.Text.Trim().Replace("'", "''") + "' "; 22 if (cboGroup.Text!="") 23 strWhere = gFun.GenWhere(strWhere) + " e.plangroup like nvl('" + cboGroup.Text.Trim().Replace("'", "''") + "','%') "; 24 25 string str = "SELECT C.LINENO,C.LOTNO,A.ISSUE_QTY,d.delproddate,TO_CHAR(C.W_DAY, 'MM/DD') MD,SUM(C.QTY) QTY from (select LINENO,LOTNO,W_DAY,QTY,OPERNO from facd105 " + strWhere_facd105 + " ) c " + 26 "left join (SELECT LOTNO, SUM(ISSUE_QTY) ISSUE_QTY FROM FACD106 GROUP BY LOTNO) A on C.LOTNO = A.LOTNO " + 27 "left join (select min(delproddate) delproddate, lotno from facd313 group by lotno) d on c.lotno = d.lotno " + 28 "left join FACD101 B on C.LOTNO = B.LOTNO " + 29 "left join facd201 e on c.lineno = e.new_line " + 30 "left join facd102 f on C.OPERNO = F.LINENO AND b.lotno = f.lotno " + strWhere + 31 "GROUP BY C.LINENO,C.LOTNO,A.ISSUE_QTY,d.delproddate,TO_CHAR(C.W_DAY, 'MM/DD')"; 32 33 34 int TotalCnt = 0; 35 try 36 { 37 TimeSpan time = DateTime.Parse(dtpEnd.Text.ToString()) - DateTime.Parse(dtpStart.Text.ToString()); 38 TotalCnt = time.Days+1; 39 TotalCnt += 5; 40 } 41 catch { } 42 43 DataTable tableCount = p.daO.GetDataTable("select distinct LINENO from (" + str + ")"); 44 45 Excel.Application xapp = null; 46 Excel.Workbook xbook = null; 47 Excel.Worksheet xsheet = null; 48 //int TotalCnt = 26;//設置要列印的列數 49 try 50 { 51 xapp = new Excel.Application(); 52 xbook = xapp.Workbooks.Add(1); 53 xsheet = (Excel.Worksheet)xbook.Worksheets.get_Item(1); 54 //------------------Set Page-------------------- 55 //xsheet.Cells.RowHeight = 15; 56 //xsheet.Cells.ColumnWidth = 5; 57 xsheet.Cells.NumberFormatLocal = "@"; 58 xsheet.Cells.Select(); 59 xsheet.Cells.Columns.AutoFit();//自动调整列宽 60 61 xsheet.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; 62 xapp.get_Range(xsheet.Cells[4, 1], xsheet.Cells[4, TotalCnt]).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; 63 //xsheet.get_Range(xsheet.Cells[1, 1], xsheet.Cells[1, 1]).ColumnWidth = 10; 64 //xsheet.get_Range(xsheet.Cells[1, 1], xsheet.Cells[1, 1]).RowHeight = 21.75; 65 //xsheet.get_Range(xsheet.Cells[2, 1], xsheet.Cells[2, 1]).RowHeight = 22.5; 66 //xsheet.get_Range(xsheet.Cells[3, 1], xsheet.Cells[3, 1]).RowHeight = 8.25; 67 //xsheet.get_Range(xsheet.Cells[4, 1], xsheet.Cells[4, 1]).RowHeight = 11.25; 68 //xsheet.get_Range(xsheet.Cells[5, 1], xsheet.Cells[5, 1]).RowHeight = 4; 69 //xsheet.get_Range(xsheet.Cells[7, 1], xsheet.Cells[7, 1]).RowHeight = 4; 70 //-------------------End------------------------ 71 72 //---------Company Logo------------------------- 73 System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select logo from sys_logo where code='"+cboLogo.Text.Trim().ToString().Replace("'","''")+"'", p.daO.conn); 74 System.Data.OleDb.OleDbDataReader dr = cmd.ExecuteReader(); 75 if (dr.Read()) 76 { 77 Byte[] byF = new Byte[500000]; 78 dr.GetBytes(0, 0, byF, 0, 500000); 79 System.IO.MemoryStream ms = new System.IO.MemoryStream(byF); 80 PictureBox imgT = new PictureBox(); 81 imgT.SizeMode = PictureBoxSizeMode.AutoSize; 82 imgT.Image = Image.FromStream(ms); 83 int WW = 450; 84 int HH = 60; 85 Bitmap bmp = new Bitmap(WW, HH); 86 Graphics grp = Graphics.FromImage(bmp); 87 SolidBrush blueBrush = new SolidBrush(Color.White); 88 grp.FillRectangle(blueBrush, 0, 0, WW, HH); 89 Single intW; 90 Single intH; 91 Single i1 = imgT.Width; 92 Single i2 = imgT.Height; 93 if (imgT.Width > WW) { intW = WW; intH = imgT.Height * Convert.ToSingle(Math.Round((WW / i1), 3)); } 94 else { intW = imgT.Width; intH = imgT.Height; } 95 96 if (intH > HH) { intH = HH; intW = imgT.Width * Convert.ToSingle(Math.Round((HH / i2), 3)); } 97 grp.DrawImage(imgT.Image, Convert.ToSingle(Math.Round((WW - intW) / 2, 3)), Convert.ToSingle(Math.Round((HH - intH) / 2, 3)), intW, intH); 98 Clipboard.SetDataObject(bmp); 99 xsheet.get_Range(xsheet.Cells[1, 5], xsheet.Cells[1, 5]).Select(); 100 xsheet.Paste(xapp.Selection, null); 101 xsheet.Shapes.Item(1).IncrementLeft(-2); 102 byF = null; 103 } 104 System.Windows.Forms.Clipboard.SetDataObject(""); 105 //---------Company Logo-------------------------- 106 107 //------------------Header----------------------- 108 109 xsheet.Cells[3, 1] = "Daily Output Report"; 110 xsheet.get_Range(xsheet.Cells[3, 1], xsheet.Cells[4, TotalCnt]).Merge(null); 111 xsheet.get_Range(xsheet.Cells[3, 1], xsheet.Cells[4, TotalCnt]).Font.Size = 16; 112 xsheet.get_Range(xsheet.Cells[3, 1], xsheet.Cells[4, TotalCnt]).Font.Bold = true; 113 114 xsheet.get_Range(xsheet.Cells[5, 1], xsheet.Cells[5, 1]).Select(); 115 xapp.ActiveWindow.FreezePanes = true;//固定 116 117 int i_x = 5; 118 foreach (DataRow Row in tableCount.Rows) 119 { 120 //---------------------列頭start-------------------------------- 121 DataTable tbHeader = p.daO.GetDataTable("select distinct md from (" + str + ") where lineno='" + Row[0].ToString()+"'"); 122 string HeaderTemp = "Team,Lotno,Cut Qty,To Prod"; 123 124 foreach(DataRow R in tbHeader.Rows) 125 { 126 HeaderTemp += ","+R[0].ToString() ; 127 } 128 129 HeaderTemp += ",Sub Total"; 130 string[] Header = "A,B".Split(','); 131 Header = HeaderTemp.Split(','); 132 object[] objHeader = new object[Header.Length]; 133 134 for (int i = 0; i < Header.Length; i++) 135 { 136 objHeader[i] = Header[i]; 137 } 138 Excel.Range xRange = xsheet.get_Range(xsheet.Cells[i_x, 1], xsheet.Cells[i_x, Header.Length]); 139 xRange.Value2 = objHeader; 140 xRange.Borders.LineStyle = 1; 141 xRange.Interior.ColorIndex = 15; 142 143 //---------------------列頭end-------------------------------- 144 145 i_x += 1; 146 int row = i_x; 147 //-----------------填充數據start--------------------- 148 DataTable tb = p.daO.GetDataTable("select distinct lineno,LOTNO,ISSUE_QTY,delproddate from (" + str + " ) where lineno='" + Row[0].ToString() + "'"); 149 150 object[,] objData = new object[tb.Rows.Count,Header.Length]; 151 152 for(int x=0;x<tb.Rows.Count;x++) 153 { 154 for (int y = 0; y < 4; y++) 155 { 156 objData[x,y] = tb.Rows[x][y].ToString(); 157 } 158 DataTable tbQty = p.daO.GetDataTable("select MD,QTY from (" + str + ") where lineno='" + Row[0].ToString() + "' and LOTNO='" + tb.Rows[x]["lotno"].ToString() + "'"); 159 int sum = 0; 160 161 foreach (DataRow rq in tbQty.Rows) 162 { 163 for (int yy = 4; yy < Header.Length - 1; yy++) 164 { 165 if (objHeader[yy].ToString() == rq["MD"].ToString()) 166 { 167 objData[x, yy] = rq["qty"].ToString(); 168 sum += Int32.Parse(rq["qty"].ToString()); 169 } 170 } 171 } 172 objData[x, Header.Length-1] = sum.ToString(); 173 i_x += 1; 174 } 175 176 xRange = xsheet.get_Range(xsheet.Cells[row, 1], xsheet.Cells[i_x-1, Header.Length]); 177 xRange.Value2 = objData; 178 xRange.Borders.LineStyle = 1; 179 180 181 //-----------------填充數據end--------------------- 182 183 //----------------底欄合計start------------------- 184 xRange=xsheet.get_Range(xsheet.Cells[i_x, 1], xsheet.Cells[i_x, 4]); 185 xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; 186 xRange.Merge(null); 187 xRange.Interior.ColorIndex = 15; 188 xRange.Borders.LineStyle = 1; 189 xRange.Value2="Sub Total"; 190 DataTable tbSub = p.daO.GetDataTable("select MD,sum(QTY) as sub from (" + str + ") where lineno='" + Row[0].ToString() + "' group by MD"); 191 int sub = 0; 192 object[] objBomeder = new object[Header.Length - 4]; 193 int k=0; 194 foreach (DataRow r in tbSub.Rows) 195 { 196 for (int y = 4; y < Header.Length-1; y++) 197 { 198 if (objHeader[y].ToString() == r["MD"].ToString()) 199 { 200 objBomeder[k] = r["sub"].ToString(); 201 sub += Int32.Parse(r["sub"].ToString()); 202 k++; 203 } 204 } 205 } 206 objBomeder[k] = sub.ToString(); 207 xRange = xsheet.get_Range(xsheet.Cells[i_x, 5], xsheet.Cells[i_x, Header.Length]); 208 xRange.Borders.LineStyle = 1; 209 xRange.Value2 = objBomeder; 210 //----------------底欄合計end------------------- 211 212 i_x += 2; 213 } 214 xsheet.get_Range(xsheet.Cells[1, 2], xsheet.Cells[i_x, 2]).ColumnWidth = 10; 215 216 217 218 xsheet.PageSetup.LeftHeader = "Print User:" + p.g_UserName + "(" + p.g_UserID + ")"; 219 xsheet.PageSetup.RightHeader = " Page &P of &N" + Convert.ToChar(10) + "Report ID: BAPFAC001"; 220 xsheet.PageSetup.PrintTitleRows = "$1:$4"; 221 xsheet.PageSetup.LeftMargin = xapp.Application.InchesToPoints(0.25); 222 xsheet.PageSetup.RightMargin = xapp.Application.InchesToPoints(0.25); 223 xsheet.PageSetup.TopMargin = xapp.Application.InchesToPoints(0.3); 224 xsheet.PageSetup.BottomMargin = xapp.Application.InchesToPoints(0.3); 225 xsheet.PageSetup.HeaderMargin = xapp.Application.InchesToPoints(0.3); 226 xsheet.PageSetup.FooterMargin = xapp.Application.InchesToPoints(0.3); 227 xsheet.PageSetup.CenterHorizontally = true; 228 xsheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; 229 xsheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; 230 xsheet.PageSetup.FitToPagesWide = 1; 231 xsheet.PageSetup.FitToPagesTall = 10000; 232 xsheet.PageSetup.Zoom = false; 233 //------------------------------------------------- 234 xapp.ActiveWindow.Zoom = 75; 235 xapp.Visible = true; 236 xapp.Caption = "Data Export"; 237 xapp.ActiveWindow.Caption = "Feed On/Off"; 238 } 239 catch (Exception ex) 240 { 241 MessageBox.Show(ex.ToString()); 242 //dsP = null; 243 //TP = null; 244 //TC = null; 245 //TCC = null; 246 //TH = null; 247 xapp.DisplayAlerts = false; 248 xbook.Close(null, null, null); 249 xapp.Application.Quit(); 250 xapp.Quit(); 251 } 252 finally 253 { 254 //dsP = null; 255 //TP = null; 256 //TC = null; 257 //TCC = null; 258 //TH = null; 259 xapp = null; 260 xbook = null; 261 xsheet = null; 262 } 263 Cursor = Cursors.Default; 264 }
