using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using Infragistics.Win.UltraWinGrid; using System.IO; using System.Data.OleDb; //连接Access数据库 using ADOX; using System.Reflection; using System.Threading; using Infragistics.Win; namespace CreateAccess { public partial class frmExcelImpl : Form { ServiceReference1.WebServerClient webServer = new ServiceReference1.WebServerClient(); public frmExcelImpl() { InitializeComponent(); } private void ultraToolbarsManager1_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e) { switch (e.Tool.Key) { case "btnQuery": { btnQuery(); break; } case "btnImpl": { openFileDialog1.Filter = "excel文件|*.xls;*.xlsx";//只允许mdb文件 openFileDialog1.Title = "选择要导入的Excel文件"; //弹出框头部显示 openFileDialog1.AddExtension = true; //自动增加后缀 openFileDialog1.AutoUpgradeEnabled = true; //是否随系统自动升级弹出窗口样式 openFileDialog1.InitialDirectory = Application.StartupPath;//默认打开当前目录 if (openFileDialog1.ShowDialog() == DialogResult.OK) { btnImpl(openFileDialog1.FileName); }; break; } default: break; } } private void btnImpl(string fileName) { DataTable dt1 = null; DataTable dt2 = null; try { //Excel.Range oRow = (Excel.Range)_WorkSheet.Cells[row, 1]; //oRow.EntireRow.Delete(Missing.Value); //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串) //备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反 string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + fileName + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + "Extended Properties=Excel 8.0;"; using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); System.Data.DataTable sheetDt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); DataTable dt = sheetDt; string[] sheet = new string[sheetDt.Rows.Count]; for (int m = 0; m < sheetDt.Rows.Count; m++) { sheet[m] = sheetDt.Rows[m]["TABLE_NAME"].ToString(); } string strExcel = string.Format("select * from [{0}]", sheet[0]); OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn); dt1 = new System.Data.DataTable(); myCommand.Fill(dt1); strExcel = string.Format("select * from [{0}A2:M{1}]", sheet[0], dt1.Rows.Count + 1); myCommand = new OleDbDataAdapter(strExcel, strConn); dt2 = new System.Data.DataTable(); myCommand.Fill(dt2); conn.Close(); ServiceReference1.meterWorkStorageActual[] lm = new ServiceReference1.meterWorkStorageActual[dt2.Rows.Count]; string preNo = DateTime.Now.ToString("yyyyMMddHHmmssfff"); int i = 0; foreach (DataRow dr in dt2.Rows) { lm[i++] = new ServiceReference1.meterWorkStorageActual { predictionNo = preNo, batchNo = dr["批号"].ToString().Trim(), valueFlag = dr["无效"].ToString().Trim() == "FALSE" ? "1" : "0", checkFlag = "0", //createManNo = dr["计量员编号"].ToString().Trim(), createManName = dr["计量员"].ToString().Trim(), createTime = Convert.ToDateTime(dr["日期"].ToString().Trim()), createTimeSpecified = true, //productNo = "",s productName = dr["品名"].ToString().Trim(), //gradeNo = "", gradeName = dr["牌号"].ToString().Trim(), //standardNo = "", standardName = dr["标准"].ToString().Trim(), packageNo = dr["包号"].ToString().Trim(), blockNum = dr["片数"].ToString().Trim(), productDate = Convert.ToDateTime(dr["日期"].ToString().Trim()), productDateSpecified = true, //grossWeight = Convert.ToDecimal(dr["毛重"].ToString().Trim()), //grossWeightSpecified = true, //tareWeight = Convert.ToDecimal(dr["毛重"].ToString().Trim()) - Convert.ToDecimal(dr["主称净重"].ToString().Trim()), //tareWeightSpecified = true, netWeight = Convert.ToDecimal(dr["主秤净重"].ToString().Trim()), netWeightSpecified = true, baseSpotNo = "001034004", baseSpotName = "镍旧", memo = dr["二维码"].ToString().Trim(), actualWeight = Convert.ToDecimal(dr["主秤净重"].ToString().Trim()), actualWeightSpecified = true, referWeight = Convert.ToDecimal(dr["副秤净重"].ToString().Trim()), referWeightSpecified = true, deleteManName = dr["最终修改人"].ToString().Trim() }; } if (lm.Length > 0) { ServiceReference1.resTfulResult res = webServer.doAccessCuImpl(lm); if (res.succeed) { MessageBox.Show("导入完成"); btnQuery(); } else { MessageBox.Show("部分数据导入失败"); } } } } catch (Exception ex) { MessageBox.Show("导入失败：" + ex.Message); System.GC.Collect(); } } private void btnQuery() { try { if (DateTime.Compare(dtStartTime.Value, dtEndTime.Value) > 0) { MessageBox.Show("开始时间不能大于结束时间"); return; } if ((dtEndTime.Value - dtStartTime.Value).Days > 7) { MessageBox.Show("时间跨度不能大于7天"); return; } ServiceReference1.commonPage cp = new ServiceReference1.commonPage(); cp.startTime = dtStartTime.Value.ToString("yyyy-MM-dd HH:mm:ss"); cp.endTime = dtEndTime.Value.ToString("yyyy-MM-dd HH:mm:ss"); cp.param = new ServiceReference1.meterWorkStorageActual { valueFlag = "1", baseSpotNo = cmbPoint.Value?.ToString(), productNo = cmbMatterNo.Text.Trim() }; ServiceReference1.meterWorkStorageActual[] ls = webServer.doQueryTimeWf(cp); DataTable dt = this.dataTable1.Clone(); if (ls != null && ls.Length > 0) { dt = ListToDataTable(ls); } CopyDataToDatatable(ref dt, ref this.dataTable1, true); RefreshAndAutoSize(ultraGridSj); } catch (Exception ex) { MessageBox.Show("查询异常：" + ex.Message); } } #region 控件数据设置 public void RefreshAndAutoSize(UltraGrid ultraGrid) { try { ultraGrid.BeginUpdate(); foreach (UltraGridBand band in ultraGrid.DisplayLayout.Bands) { foreach (UltraGridColumn column in band.Columns) { column.PerformAutoResize(PerformAutoSizeType.AllRowsInBand, band.ColHeadersVisible ? true : false); } } } catch { } ultraGrid.EndUpdate(); } public void CopyDataToDatatable(ref DataTable src, ref DataTable dest, bool ClearExists) { if (src == null) { dest = null; return; } if (dest == null) { dest = new DataTable(); } if (ClearExists) { if (dest != null) dest.Rows.Clear(); } DataRow CurRow, NewRow; if (src.Rows.Count > 0) for (int i = 0; i < src.Rows.Count; i++) { CurRow = src.Rows[i]; NewRow = dest.NewRow(); for (int j = 0; j < src.Columns.Count; j++) { try { if (dest.Columns.Contains(src.Columns[j].ColumnName)) { NewRow[src.Columns[j].ColumnName] = CurRow[j]; } } catch { } } dest.Rows.Add(NewRow); } dest.AcceptChanges(); } public void SetUltraGridRowFilter(ref UltraGrid myGrid1, bool bAllowFilter) { try { if (bAllowFilter) { if (myGrid1.DisplayLayout.Override.FilterUIType != FilterUIType.FilterRow) myGrid1.DisplayLayout.Override.FilterUIType = FilterUIType.FilterRow; for (int i = 0; i < myGrid1.DisplayLayout.Bands.Count; i++) { for (int j = 0; j < myGrid1.DisplayLayout.Bands[i].Columns.Count; j++) { try { if (!myGrid1.DisplayLayout.Bands[i].Columns[j].Hidden && myGrid1.DisplayLayout.Bands[i].Columns[j].RowLayoutColumnInfo.LabelPosition != LabelPosition.LabelOnly) { //string sKey = myGrid1.DisplayLayout.Bands[i].Columns[j].Key; //UNCK UNCH myGrid1.DisplayLayout.Bands[i].Columns[j].AllowRowFiltering = Infragistics.Win.DefaultableBoolean.True; } } catch { } } } } else { if (myGrid1.DisplayLayout.Override.FilterUIType != FilterUIType.HeaderIcons) myGrid1.DisplayLayout.Override.FilterUIType = FilterUIType.HeaderIcons; for (int i = 0; i < myGrid1.DisplayLayout.Bands.Count; i++) { try { myGrid1.DisplayLayout.Bands[i].ColumnFilters.ClearAllFilters(); } catch { } for (int j = 0; j < myGrid1.DisplayLayout.Bands[i].Columns.Count; j++) { try { myGrid1.DisplayLayout.Bands[i].Columns[j].AllowRowFiltering = Infragistics.Win.DefaultableBoolean.False; } catch { } } } } } catch { } } public void SetUltraComboDataSource(UltraCombo cmb, DataTable table, string ColumnValue, string ColumnDisplay) { if (table == null || !table.Columns.Contains(ColumnValue) || !table.Columns.Contains(ColumnDisplay)) return; try { if (cmb != null) { cmb.DataSource = table; cmb.DisplayLayout.Override.TipStyleScroll = TipStyle.Hide; cmb.DisplayLayout.Override.BorderStyleCell = UIElementBorderStyle.None; cmb.DisplayLayout.Override.BorderStyleRow = UIElementBorderStyle.None; cmb.DisplayLayout.Override.SelectTypeRow = SelectType.Single; cmb.DisplayLayout.Override.HeaderStyle = HeaderStyle.Standard; cmb.DisplayLayout.Override.HeaderAppearance.TextHAlign = HAlign.Center; cmb.DisplayLayout.Override.RowAppearance.TextVAlign = VAlign.Middle; cmb.DisplayLayout.Override.ActiveRowAppearance.BackColor = Color.FromArgb(51, 153, 255); cmb.DisplayLayout.Override.ActiveRowAppearance.ForeColor = Color.FromArgb(255, 255, 255); cmb.DisplayLayout.Override.SelectedRowAppearance.BackColor = Color.FromArgb(51, 153, 255); cmb.DisplayLayout.Override.SelectedRowAppearance.ForeColor = Color.FromArgb(255, 255, 255); //cmb.DisplayLayout.Override.ActiveRowAppearance.FontData.Bold = DefaultableBoolean.True; foreach (UltraGridBand band in cmb.DisplayLayout.Bands) { band.ColHeadersVisible = false; foreach (UltraGridColumn column in band.Columns) { if (!column.Key.Equals(ColumnDisplay)) column.Hidden = true; column.Width = cmb.Width; //column.PerformAutoResize(PerformAutoSizeType.AllRowsInBand); } } cmb.ValueMember = ColumnValue; cmb.DisplayMember = ColumnDisplay; cmb.DropDownWidth = -1; } } catch { } } public DataTable ListToDataTable(T[] items) { DataTable dataTable = new DataTable(); PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance); foreach (PropertyInfo prop in Props) { dataTable.Columns.Add(prop.Name); } foreach (T obj in items) { var values = new object[Props.Length]; for (int i = 0; i < Props.Length; i++) { values[i] = Props[i].GetValue(obj, null); } dataTable.Rows.Add(values); } return dataTable; } #endregion private void frmExcelImpl_Load(object sender, EventArgs e) { dtStartTime.Value = Convert.ToDateTime((DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00")); dtEndTime.Value = Convert.ToDateTime((DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59")); SetUltraGridRowFilter(ref ultraGridSj, true); #region /* ServiceReference1.comBaseInfo cp = new ServiceReference1.comBaseInfo(); cp.validFlag = "1"; cp.pBaseCode = "001025"; ServiceReference1.comBaseInfo[] ls = webServer.doQueryComBaseInfoWf(cp); if (ls != null && ls.Length > 0) { SetUltraComboDataSource(cmbMatterNo, ListToDataTable(ls), "baseCode", "baseName"); } else { MessageBox.Show("产品信息载入失败"); return; } //*/ ServiceReference1.comBaseInfo co = new ServiceReference1.comBaseInfo(); co.validFlag = "1"; co.pBaseCode = "001034"; ServiceReference1.comBaseInfo[] lo = webServer.doQueryComBaseInfoWf(co); if (lo != null && lo.Length > 0) { SetUltraComboDataSource(cmbPoint, ListToDataTable(lo), "baseCode", "baseName"); } else { MessageBox.Show("计量点载入失败"); return; } #endregion } } }