// <summary> /// 导出按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void FrmAppItem_F9ClickHandler(object sender, EventArgs e) { string strName = ""; try { if (gvSelectedItems.RowCount == 0) { MessageBoxShow.ShowProMessage("Grid表格中没有数据，不能导出为Excel"); return; } DateTime MMSDate = RealSailing.UI.Service.ServiceManager.GetSystemDateTime(); using (SaveFileDialog saveFileDialog = new SaveFileDialog()) { saveFileDialog.Filter = "导出Excel(*.xls)|*.xls"; saveFileDialog.FilterIndex = 0; saveFileDialog.RestoreDirectory = true; saveFileDialog.CreatePrompt = true; saveFileDialog.Title = "导出文件保存路径"; //默认的文件名 saveFileDialog.FileName = strTitle + " - " + MMSDate.ToString("yyyyMMdd"); //saveFileDialog.ShowDialog(); if (saveFileDialog.ShowDialog() == DialogResult.OK) { strName = saveFileDialog.FileName; if (strName.Length != 0) { gridColumn2.Visible = false; gridItemID2.Visible = true; gridItemID2.VisibleIndex = 0; gvSelectedItems.ExportToXls(strName); gridColumn2.Visible = true; gridItemID2.Visible = false; MessageBoxShow.ShowProMessage("导出Excel成功", strTitle); //关闭操作 System.Reflection.Missing miss = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook objWorkBook = objExcel.Workbooks.Add(miss); Microsoft.Office.Interop.Excel.Worksheet objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkBook.ActiveSheet; objWorkBook.Close(null, null, null); objExcel.Workbooks.Close(); objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel); System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet); objSheet = null; objWorkBook = null; objExcel = null; } else { MessageBoxShow.ShowProMessage("保存的Excel名称不能为空"); } } } } catch (System.Exception msg) { MessageBoxShow.ShowProMessage(msg.ToString()); } finally { GC.Collect(); } } 导入Excel /// <summary> /// 导入按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void FrmAppItem_F8ClickHandler(object sender, EventArgs e) { try { OpenFileDialog ofd = new OpenFileDialog(); ofd.Title = "Excel文件"; ofd.FileName = ""; ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); ofd.Filter = "Excel文件(*.xls)|*.xls"; ofd.ValidateNames = true; //文件有效性验证ValidateNames，验证用户输入是否是一个有效的Windows文件名 ofd.CheckFileExists = true; //验证路径有效性 ofd.CheckPathExists = true; //验证文件有效性 string strName = string.Empty; if (ofd.ShowDialog() == DialogResult.OK) { strName = ofd.FileName; } if (strName == "") { return; } if (EcxelToGridView(strName, this.gvSelectedItems)) { MessageBoxShow.ShowProMessage("数据导入成功", strTitle); //isChanged = true; } else MessageBoxShow.ShowProMessage("数据导入失败,请检查导入的Excel格式与数据是否正确", strTitle); this.Cursor = Cursors.Default; } catch (System.Exception Msg) { MessageBoxShow.ShowProMessage("数据导入失败,请检查导入的Excel格式与数据是否正确", strTitle); //MessageBoxShow.ShowErrMessage(Msg.ToString()+"数据导入失败,请检查导入的Excel格式与数据是否正确"); } } /// <summary> /// Excel数据导入方法 /// </summary> /// <param name="filePath"></param> /// <param name="dgv"></param> /// <returns></returns> public bool EcxelToGridView(string filePath, DevExpress.XtraGrid.Views.Grid.GridView dgv) { bool isVailed = false; string itemid = string.Empty; string itemplu = string.Empty; string itemName = string.Empty; //根据路径打开一个Excel文件并将数据填充到ds中 try { string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=YES;IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; //获取Excel中的sheet的名称 string SheetName= GetExcelSheetNames(filePath)[0]; //strExcel = "select * from [sheet1$]"; strExcel = "select * from [" + SheetName + "$]"; myCommand = new OleDbDataAdapter(strExcel, strConn); // RealSailing.DataSet.SlipInfo.SLPD010_SLIPSUMHDS ds = new RealSailing.DataSet.SlipInfo.SLPD010_SLIPSUMHDS(); System.Data.DataSet ds = new System.Data.DataSet(); myCommand.Fill(ds, "table1"); conn.Close(); if (ds.Tables["table1"].Rows.Count == 0) { MessageBoxShow.ShowProMessage("要导入的Excel没有数据"); } ExcelDS.Clear(); for (int j = 0; j < ds.Tables["table1"].Rows.Count; j++) { if (ds.Tables["table1"].Rows[j]["商品id"].ToString().Trim() != string.Empty) itemid = ds.Tables["table1"].Rows[j]["商品id"].ToString().Trim(); if (ds.Tables["table1"].Rows[j]["商品货号"].ToString().Trim() != string.Empty) itemplu = ds.Tables["table1"].Rows[j]["商品货号"].ToString().Trim(); else itemplu = " "; if (ds.Tables["table1"].Rows[j]["商品名称"].ToString().Trim() != string.Empty) itemName = ds.Tables["table1"].Rows[j]["商品名称"].ToString().Trim(); //把数据填充到ds中 FillDataToDs(itemid, itemplu, itemName); isVailed = true; } if (lblSelectDs.Tables["MSTM150_ITEM"].Rows.Count == 0) { isVailed = false; } else if (ExcelDS.Tables["MSTM150_ITEM"].Rows.Count>0) {//重复数据 显示提示 MsShowData msShow = new MsShowData(); msShow.GetDs = ExcelDS; msShow.ShowDialog(); isVailed = true; } return isVailed; } catch (System.Data.OleDb.OleDbException ex) { if (ex.Message.IndexOf("不是一个有效名称。请确认它不包含无效的字符或标点，且名称不太长") != -1) { return false; } return false; } } /// <summary> /// 获取获得当前你选择的Excel Sheet的所有名字 /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static string[] GetExcelSheetNames(string filePath) { Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbooks wbs = excelApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, 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); int count = wb.Worksheets.Count; string[] names = new string[count]; for (int i = 1; i <= count; i++) { names[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name; } wb.Close(null, null, null); excelApp.Quit(); wbs.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs); excelApp = null; wbs = null; wb = null; return names; } private void FillDataToDs(string itemid, string itemplu, string itemName) { DataRow[] rows = lblSelectDs.Tables["MSTM150_ITEM"].Select(string.Format("MSTM150_ITEMID='{0}'", itemid)); if (rows.Length == 0) { if (itemid.Trim() != string.Empty && itemplu.Trim() != string.Empty && itemName != string.Empty) { DataRow dr; dr = lblSelectDs.Tables["MSTM150_ITEM"].NewRow(); dr[MSTM150_ITEM.MSTM150_ITEMID] = itemid; dr["MSTM151_PLUCD"] = itemplu; dr["MSTM150_ITEMCNM"] = itemName; dr["CheckRemove"] = "0"; lblSelectDs.Tables["MSTM150_ITEM"].Rows.Add(dr); } //else if (itemid.Trim() == string.Empty) //{ // string st = "导入的商品id不允许为空"; // sb.Append(st); // sb.Append(" "); //} //else if (itemplu.Trim() == string.Empty) //{ // string st = "导入的商品编码不允许为空"; // sb.Append(st); // sb.Append(" "); //} //else if (itemName.Trim() == string.Empty) //{ // string st = "导入的商品名称不允许为空"; // sb.Append(st); // sb.Append(" "); //} } else if(rows.Length>0) { ExcelDS.Tables["MSTM150_ITEM"].ImportRow(rows[0]); } } private void gvSelectedItems_RowCountChanged(object sender, EventArgs e) {//导出按钮 this.F9Property.Enable=(gvSelectedItems.RowCount == 0) ? false : true; } //导入导出Excel 注意的是及时的关闭Excel,不然占用进程很麻烦，我调试了大半天太弄好的