コード例 #1
0
        private void btnSel_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog openFileDialog = new OpenFileDialog();
                openFileDialog.Filter           = "Excel|*.xls|Excel|*.xlsx|All File|*.*";
                openFileDialog.RestoreDirectory = true;
                openFileDialog.FilterIndex      = 1;

                if (openFileDialog.ShowDialog() != DialogResult.OK)
                {
                    return;
                }

                string fName = openFileDialog.FileName;

                ClsExcel clsExcel = ClsExcel.Instance();

                string    sSQL = @"
select *
from [Sheet1$]
";
                DataTable dt   = clsExcel.ExcelToDT(fName, sSQL, true);

                DataColumn dc = new DataColumn();
                dc.ColumnName   = "bChoose";
                dc.DataType     = Type.GetType("System.Boolean");
                dc.DefaultValue = false;
                dt.Columns.Add(dc);

                dc              = new DataColumn();
                dc.ColumnName   = "bUsed";
                dc.DataType     = Type.GetType("System.Boolean");
                dc.DefaultValue = false;
                dt.Columns.Add(dc);

                gridControl1.DataSource = dt;
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }
        }
コード例 #2
0
        private void btnLoad_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog openFileDialog = new OpenFileDialog();
                openFileDialog.Filter           = "xlsx|*.xlsx|xls|*.xls|All File|*.*";
                openFileDialog.RestoreDirectory = true;
                openFileDialog.FilterIndex      = 1;

                if (openFileDialog.ShowDialog() != DialogResult.OK)
                {
                    return;
                }
                string sSQL  = "";
                string fName = openFileDialog.FileName;

                ClsExcel clsExcel = ClsExcel.Instance();

                SetTxtNull();

                sSQL = "select * from [PL$A4:B8]";
                DataTable dtHead = clsExcel.ExcelToDT(fName, sSQL, true);
                txtCurrency.Text   = dtHead.Rows[0][1].ToString().Trim();
                txtInvoiceNO.Text  = dtHead.Rows[1][1].ToString().Trim();
                dateEdit1.DateTime = BaseFunction.ReturnDate(dtHead.Rows[2][1]);
                txtCompany.Text    = dtHead.Rows[3][1].ToString().Trim();


                sSQL = "select * from [PL$A11:N65535]";
                DataTable dtParkingList = clsExcel.ExcelToDT(fName, sSQL, true);
                for (int i = 0; i < dtParkingList.Columns.Count; i++)
                {
                    dtParkingList.Columns[i].ColumnName = dtParkingList.Rows[0][i].ToString().Trim();
                }


                DataColumn dc = new DataColumn();
                dc.ColumnName = "U8RDCode";
                dtParkingList.Columns.Add(dc);

                dc            = new DataColumn();
                dc.ColumnName = "Rds01ID";
                dtParkingList.Columns.Add(dc);

                dtParkingList.Rows.RemoveAt(1);                 //去除列表中的 标题
                dtParkingList.Rows.RemoveAt(0);                 //去除列表中的 ATUP PARTS 这行数据

                for (int i = dtParkingList.Rows.Count - 1; i >= 0; i--)
                {
                    if (dtParkingList.Rows[i][0].ToString().Trim().ToUpper().StartsWith("CURRENCY"))
                    {
                        dtParkingList.Rows.RemoveAt(i);
                        break;
                    }
                    dtParkingList.Rows.RemoveAt(i);
                }
                for (int i = dtParkingList.Rows.Count - 1; i >= 0; i--)
                {
                    if (dtParkingList.Rows[i][0].ToString().Trim() == "")
                    {
                        dtParkingList.Rows.RemoveAt(i);
                    }
                }

                for (int i = 0; i < dtParkingList.Columns.Count; i++)
                {
                    string sColName = dtParkingList.Columns[i].ColumnName.ToString().Trim();
                    sColName = sColName.Replace(" ", "");
                    sColName = sColName.Replace("#", "");
                    sColName = sColName.Replace(".", "");
                    sColName = sColName.Replace("(", "");
                    sColName = sColName.Replace(")", "");
                    dtParkingList.Columns[i].ColumnName = sColName;
                }

                gridControlPL.DataSource = dtParkingList;
                gridViewPL.BestFitColumns();

                //sSQL = "select * from [IN$A5:B8]";
                //DataTable dt_Head = clsExcel.ExcelToDT(fName, sSQL, true);
                //txt_InvoiceNO.Text = dt_Head.Rows[0][1].ToString().Trim();
                //dateEdit_1.DateTime = BaseFunction.ReturnDate(dt_Head.Rows[1][1]);
                //txt_Company.Text = dt_Head.Rows[2][1].ToString().Trim();

                sSQL = "select * from [IN$A11:I65535]";
                DataTable dtInvoice = clsExcel.ExcelToDT(fName, sSQL, true);
                for (int i = 0; i < dtInvoice.Columns.Count; i++)
                {
                    dtInvoice.Columns[i].ColumnName = dtInvoice.Rows[0][i].ToString().Trim();
                }

                dtInvoice.Rows.RemoveAt(1);                 //去除列表中的 标题
                dtInvoice.Rows.RemoveAt(0);                 //去除列表中的 ATUP PARTS 这行数据

                for (int i = dtInvoice.Rows.Count - 1; i >= 0; i--)
                {
                    if (dtInvoice.Rows[i][0].ToString().Trim().ToUpper().StartsWith("CURRENCY"))
                    {
                        dtInvoice.Rows.RemoveAt(i);
                        break;
                    }
                    dtInvoice.Rows.RemoveAt(i);
                }
                for (int i = dtInvoice.Rows.Count - 1; i >= 0; i--)
                {
                    if (dtInvoice.Rows[i][0].ToString().Trim().ToUpper().StartsWith(""))
                    {
                        dtInvoice.Rows.RemoveAt(i);
                        break;
                    }
                    dtInvoice.Rows.RemoveAt(i);
                }
                for (int i = dtInvoice.Rows.Count - 1; i >= 0; i--)
                {
                    if (dtInvoice.Rows[i][0].ToString().Trim() == "" || dtInvoice.Rows[i][0].ToString().Trim().ToUpper().StartsWith("TOTAL"))
                    {
                        dtInvoice.Rows.RemoveAt(i);
                    }
                }

                for (int i = 0; i < dtInvoice.Columns.Count; i++)
                {
                    string sColName = dtInvoice.Columns[i].ColumnName.ToString().Trim();
                    sColName = sColName.Replace(" ", "");
                    sColName = sColName.Replace("#", "");
                    sColName = sColName.Replace(".", "");
                    sColName = sColName.Replace("(", "");
                    sColName = sColName.Replace(")", "");
                    sColName = sColName.Replace("\n", "");
                    sColName = sColName.Replace("\r", "");
                    sColName = sColName.Replace("UNITPRICE", "PRICEPERUNIT");
                    dtInvoice.Columns[i].ColumnName = sColName;
                }

                gridControlIN.DataSource = dtInvoice;
                gridViewIN.BestFitColumns();

                dateEdit1.DateTime = BaseFunction.ReturnDate(sLogDate);
            }
            catch (Exception ee)
            {
                SetTxtNull();

                MessageBox.Show(ee.Message);
            }
        }
コード例 #3
0
ファイル: _ActionFCost.cs プロジェクト: chrgu000/DEMO
        private void btnLoad_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog openFileDialog = new OpenFileDialog();
                openFileDialog.Filter           = "Excel|*.xls|Excel|*.xlsx|All File|*.*";
                openFileDialog.RestoreDirectory = true;
                openFileDialog.FilterIndex      = 1;

                if (openFileDialog.ShowDialog() != DialogResult.OK)
                {
                    return;
                }

                string fName = openFileDialog.FileName;

                ClsExcel clsExcel = ClsExcel.Instance();

                string    sSQL = @"
select *
from [Sheet1$]
";
                DataTable dt   = clsExcel.ExcelToDT(fName, sSQL, true);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string sCol = dt.Columns[i].ColumnName;
                    if (sCol.ToLower() == "CustomerCode".ToLower())
                    {
                        dt.Columns[i].ColumnName = "cCusCode";
                    }
                    if (sCol.ToLower() == "PartNo".ToLower())
                    {
                        dt.Columns[i].ColumnName = "cInvCode";
                    }
                    if (sCol.ToLower() == "StartDate".ToLower())
                    {
                        dt.Columns[i].ColumnName = "dtmStart";
                    }
                    if (sCol.ToLower() == "MAN-HOUR".ToLower())
                    {
                        dt.Columns[i].ColumnName = "MANHOUR";
                    }
                }
                DataColumn dc = new DataColumn();
                dc.ColumnName = "sStatus";
                dt.Columns.Add(dc);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["sStatus"] = "add";
                }

                gridControl1.DataSource = dt;

                SetBtnEnable(false);
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }
        }
コード例 #4
0
ファイル: GL_accvouch.cs プロジェクト: chrgu000/DEMO
        private void btnLoad_Click(object sender, EventArgs e)
        {
            string sSQL = "";

            try
            {
                if (dateEdit1.DateTime.Year != dateEdit2.DateTime.Year || dateEdit1.DateTime.Month != dateEdit2.DateTime.Month)
                {
                    dateEdit1.Focus();
                    throw new Exception("日期必须在同一个年月");
                }

                OpenFileDialog openFileDialog = new OpenFileDialog();
                openFileDialog.Filter           = "XML|*.xml|所有文件|*.*";
                openFileDialog.RestoreDirectory = true;
                openFileDialog.FilterIndex      = 1;

                if (openFileDialog.ShowDialog() != DialogResult.OK)
                {
                    return;
                }

                string fName = openFileDialog.FileName;

                ClsExcel clsExcel     = ClsExcel.Instance();
                string   tablename    = "";
                string   sqltablename = "";

                if (toolStripComboBox1.SelectedIndex == -1)
                {
                    MessageBox.Show("请选择导入单据类型");
                    return;
                }
                switch (toolStripComboBox1.SelectedIndex)
                {
                case 0:
                    tablename    = "View - General Ledger Entries $A2:R65536";
                    sqltablename = "_General Ledger Entries";
                    xtp0.Show();
                    break;

                case 1:
                    tablename    = "Posted Purchase Credit Memos$A2:J65536";
                    sqltablename = "_Purchase Credit Memos";
                    xtp1.Show();
                    break;

                case 2:
                    tablename    = "Posted Purchase Invoices$A2:M65536";
                    sqltablename = "_Purchase Invoices";
                    xtp2.Show();
                    break;

                case 3:
                    tablename    = "Posted Sales Credit Memos$A2:I65536";
                    sqltablename = "_Sales Credit Memos";
                    xtp3.Show();
                    break;

                case 4:
                    tablename    = "Posted Sales Invoices$A2:L65536";
                    sqltablename = "_Sales Invoices";
                    xtp4.Show();
                    break;

                case 5:
                    tablename    = "View - Bank Account Ledger Ent$A2:J65536";
                    sqltablename = "_Bank Account Ledger Entries";
                    xtp5.Show();
                    break;
                }
                DataTable     dt   = DbHelperXML.ReadFromXml(fName);
                SqlConnection conn = new SqlConnection(Conn);
                conn.Open();
                //conn.ConnectionTimeout = 0;

                SqlTransaction tran = conn.BeginTransaction();


                try
                {
                    //导入
                    int iCount = 0;

                    sSQL = "truncate table [" + sqltablename + "]";

                    DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);


                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        SqlClass sc = new SqlClass("[" + sqltablename + "]");
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            sc.ToString("[" + dt.Columns[j].ColumnName + "]", dt.Rows[i][dt.Columns[j].ColumnName].ToString().Replace("'", ""));
                        }
                        sSQL = sc.UpdateSql();
                        DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
                        iCount = iCount + 1;
                    }

                    //核对
                    switch (toolStripComboBox1.SelectedIndex)
                    {
                    case 0:
                        sSQL = "select a.*,null as 问题数据 from [" + sqltablename + "] a ";
                        dt   = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        gridControl0.DataSource            = dt;
                        gridView0.OptionsBehavior.Editable = false;
                        gridView0.Columns["Amount"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
                        gridView0.BestFitColumns();
                        break;

                    case 1:
                        sSQL = "select a.*,case when b.cVenCode is null then '用友未找到供应商' end as 问题数据 from [" + sqltablename + "] a left join Vendor b on a.[Buy-from Vendor No.]=b.cVenCode ";
                        dt   = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        gridControl1.DataSource            = dt;
                        gridView1.OptionsBehavior.Editable = false;
                        gridView1.Columns["Amount"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
                        gridView1.BestFitColumns();
                        break;

                    case 2:
                        sSQL = "select a.*,case when b.cVenCode is null then '用友未找到供应商' end as 问题数据 from [" + sqltablename + "] a left join Vendor b on a.[Buy-from Vendor No.]=b.cVenCode  ";
                        dt   = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        gridControl2.DataSource            = dt;
                        gridView2.OptionsBehavior.Editable = false;
                        gridView2.Columns["Amount"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
                        gridView2.BestFitColumns();
                        break;

                    case 3:
                        sSQL = "select a.*,case when b.cCusCode is null then '用友未找到客户' end as 问题数据 from [" + sqltablename + "] a left join Customer b on a.[Sell-to Customer No.]=b.cCusCode ";
                        dt   = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        gridControl3.DataSource            = dt;
                        gridView3.OptionsBehavior.Editable = false;
                        gridView3.Columns["Amount"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
                        gridView3.BestFitColumns();
                        break;

                    case 4:
                        sSQL = "select a.*,case when b.cCusCode is null then '用友未找到客户' end as 问题数据 from [" + sqltablename + "] a left join Customer b on a.[Sell-to Customer No.]=b.cCusCode ";
                        dt   = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        gridControl4.DataSource            = dt;
                        gridView4.OptionsBehavior.Editable = false;
                        gridView4.Columns["Amount"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
                        gridView4.BestFitColumns();

                        break;

                    case 5:
                        sSQL = "select a.*,null as 问题数据 from [" + sqltablename + "] a ";
                        dt   = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
                        gridControl5.DataSource            = dt;
                        gridView5.OptionsBehavior.Editable = false;
                        gridView5.Columns["Amount"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
                        gridView5.BestFitColumns();
                        break;
                    }
                    tran.Commit();
                    MessageBox.Show("导入成功,共" + iCount + "条");
                }
                catch (Exception ee)
                {
                    tran.Rollback();
                    throw new Exception("导入失败,原因:" + ee.Message);
                }
                //DataColumn dc = new DataColumn();
                //dc.ColumnName = "选择";
                //dc.DataType = System.Type.GetType("System.Boolean");
                //dt.Columns.Add(dc);

                //for (int i = 0; i < dt.Rows.Count; i++)
                //{
                //    dt.Rows[i]["选择"] = true;
                //}

                //gridControl1.DataSource = dt;

                //gridView1.BestFitColumns();
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }
        }