jisco_winform_wzjl/CreateAccess/frmExcelImpl.cs

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<ServiceReference1.meterWorkStorageActual>(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>(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<ServiceReference1.comBaseInfo>(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<ServiceReference1.comBaseInfo>(lo), "baseCode", "baseName");
            }
            else
            {
                MessageBox.Show("计量点载入失败");
                return;
            }
            #endregion
        }
    }
}