C#根據當前DataGridView查詢數據導出Excel
private void btnsuggestinfo_Click(object sender, EventArgs e) { DataTable dt = new DataTable(); dt.Columns.Add("單號", typeof(string)); dt.Columns.Add("項次", typeof(decimal)); dt.Columns.Add("項次預約數量", typeof(decimal)); dt.Columns.Add("廠別", typeof(string)); dt.Columns.Add("料號", typeof(string)); dt.Columns.Add("倉碼", typeof(string)); dt.Columns.Add("BATCH", typeof(string)); dt.Columns.Add("棧板", typeof(string)); dt.Columns.Add("入庫日期", typeof(string));// add by chao dt.Columns.Add("儲位", typeof(string)); dt.Columns.Add("數量", typeof(decimal)); foreach (DataGridViewRow dgvr in dgvdnitem.Rows) { var docitem = dgvr.DataBoundItem as Wms_doc_item; var stockpallets = WmsEntity.Select<Wms_stock_pallet_v>(x => x.PLANT == docitem.PLANT_FROM & x.SLOC == docitem.SLOC_FROM & x.MATERIAL == docitem.MATERIAL &x.BATCH==docitem.BATCH& x.STOCK_FLAG == (int)Step_Type.None & x.LOCK_FLAG == 0 & x.WH == Basic.WorkingWH).OrderBy(x => x.IN_DATE); decimal? remain_qty = (decimal?)(docitem.REQUEST_QTY - docitem.OPERATION_QTY - docitem.RESERVED_QTY); foreach (var pallet in stockpallets) { DataRow dr = dt.NewRow(); dr.SetField<string>("單號", docitem.DOC_NO); dr.SetField<decimal>("項次", docitem.DOC_ITEM.Value); dr.SetField<decimal>("項次預約數量", docitem.REQUEST_QTY.Value); dr.SetField<string>("廠別", pallet.PLANT); dr.SetField<string>("料號", pallet.MATERIAL); dr.SetField<string>("倉碼", pallet.SLOC); dr.SetField<string>("BATCH", docitem.BATCH); dr.SetField<string>("棧板", pallet.PALLET_ID); dr.SetField<string>("入庫日期",pallet.IN_DATE.ToString());// add by chao dr.SetField<string>("儲位", pallet.BIN); dr.SetField<decimal>("數量", pallet.PANEL_QTY.Value); dt.Rows.Add(dr); remain_qty -= pallet.PANEL_QTY; if (remain_qty <= 0) { break; } } } DataTableToExcel(dt); } public static void DataTableToExcel(DataTable table) { SaveFileDialog dlg = new SaveFileDialog(); dlg.Filter = "Execl files (*.xls)|*.xls"; dlg.FilterIndex = 0; dlg.RestoreDirectory = true; dlg.Title = "保存為Excel文件"; if (dlg.ShowDialog() == DialogResult.OK) { Stream myStream; myStream = dlg.OpenFile(); StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.Default); string columnTitle = ""; try { //寫入標題列 for (int i = 0; i < table.Columns.Count; i++) { if (columnTitle == "") { columnTitle = "\"" + table.Columns[i].ColumnName + "\""; } else { columnTitle += "\t" + "\"" + table.Columns[i].ColumnName + "\""; } } sw.WriteLine(columnTitle); //寫入內容列 for (int j = 0; j < table.Rows.Count; j++) { string columnValue = ""; for (int i = 0; i < table.Columns.Count; i++) { string cellValue = table.Rows[j][i] == DBNull.Value ? "" : table.Rows[j][i].ToString().Replace("\"", "'"); if (columnValue == "") { columnValue = "\"" + cellValue + "\""; } else { columnValue += "\t" + "\"" + cellValue + "\""; } } sw.WriteLine(columnValue); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { sw.Close(); myStream.Close(); } } }