예제 #1
0
        /// <summary>
        /// Les  cells selectionnées dans le sheet actif
        /// </summary>
        ///
        public Range GetSelectedRange()
        {
            try
            {
                DevExpress.Spreadsheet.Range selection = this.spreadsheetControl.Selection;
                if (selection == null)
                {
                    return(null);
                }
                DevExpress.Spreadsheet.Worksheet worksheet = this.spreadsheetControl.ActiveWorksheet;

                if (worksheet == null)
                {
                    return(null);
                }

                Sheet sheet = new Sheet(worksheet.Index + 1, worksheet.Name);
                Range range = new Range(sheet);

                foreach (DevExpress.Spreadsheet.Range area in selection.Areas)
                {
                    RangeItem item = new RangeItem(area.TopRowIndex + 1
                                                   , area.BottomRowIndex + 1, area.LeftColumnIndex + 1,
                                                   area.RightColumnIndex + 1);
                    range.Items.Add(item);
                }
                //rangePreviousValue = range;
                return(range);
            }
            catch (Exception)
            {
                return(null);
            }
        }
        private void LoadSheet()
        {
            dt.Rows.Clear();
            DevExpress.Spreadsheet.Workbook workbook = new DevExpress.Spreadsheet.Workbook();
            workbook.LoadDocument(openFileDialog1.FileName);
            DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[i];
            if (i <= workbook.Sheets.Count)
            {
                txtStoreCode.Text = worksheet.Name;

                DataTable TempTable = new DataTable();


                string xlConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtAddress.Text + ";Extended Properties=\"Excel 12.0;\";";
                using (var myCommand = new OleDbDataAdapter("SELECT [EAN Code],[PI Qty] as [PI Qty] FROM [" + txtStoreCode.Text + "$]", xlConn))
                {
                    myCommand.Fill(TempTable);
                    if (TempTable.Rows.Count > 0)
                    {
                        foreach (DataRow dr in TempTable.Rows)
                        {
                            DataSet ds = ProjectFunctions.GetDataSet("sp_LoadEANDataNew '" + dr["EAN Code"].ToString() + "'");
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                DataRow dataRow = dt.NewRow();

                                dataRow["ARTNO"]      = ds.Tables[0].Rows[0]["ARTNO"].ToString();
                                dataRow["ARTALIAS"]   = ds.Tables[0].Rows[0]["ARTALIAS"].ToString();
                                dataRow["VARIANTART"] = ds.Tables[0].Rows[0]["VARIANT ART"].ToString();
                                dataRow["ARTMRP"]     = ds.Tables[0].Rows[0]["ARTMRP"].ToString();
                                dataRow["SIZE"]       = ds.Tables[0].Rows[0]["SIZE"].ToString();
                                dataRow["COLOR"]      = ds.Tables[0].Rows[0]["COLOR"].ToString();
                                dataRow["COLSYSID"]   = ds.Tables[0].Rows[0]["COLSYSID"].ToString();
                                dataRow["SZSYSID"]    = ds.Tables[0].Rows[0]["SZSYSID"].ToString();
                                dataRow["ARTSYSID"]   = ds.Tables[0].Rows[0]["ARTSYSID"].ToString();
                                dataRow["GrpDesc"]    = ds.Tables[0].Rows[0]["GrpDesc"].ToString();
                                dataRow["GrpSubDesc"] = ds.Tables[0].Rows[0]["GrpSubDesc"].ToString();
                                if (dr["PI Qty"].ToString().Trim().Length == 0)
                                {
                                    dr["PI Qty"] = 0;
                                }
                                dataRow["PIQty"] = Convert.ToDecimal(dr["PI Qty"]);
                                dataRow["EANNo"] = Convert.ToDecimal(dr["EAN Code"]);

                                dataRow["GrpHSNCode"] = ds.Tables[0].Rows[0]["GrpHSNCode"].ToString();
                                dt.Rows.Add(dataRow);
                            }
                        }


                        InfoGrid.DataSource = dt;
                        InfoGridView.BestFitColumns();
                    }
                }
            }
            else
            {
                XtraMessageBox.Show("PIS Created Successfully");
            }
        }
예제 #3
0
 /// <summary>
 /// getActiveSheetIndex
 /// </summary>
 /// <returns>La sheet active</returns>
 public int getActiveSheetIndex()
 {
     DevExpress.Spreadsheet.Worksheet worksheet = this.spreadsheetControl.ActiveWorksheet;
     if (worksheet != null)
     {
         return(worksheet.Index + 1);
     }
     return(-1);
 }
예제 #4
0
        private void Populate_Muncde_BSA_ccyy_Y(int finYear)
        {
            try
            {
                xlSheet = xlControl.Document.Worksheets["Sheet1"];
                xlControl.BeginUpdate();
                xlSheet.Workbook.BeginUpdate();

                string celladdress = "A6";
                //var cell = xlWorkSheet.get_Range(celladdress, Type.Missing);
                xlSheet.Cells[celladdress].Value = finYear.ToString();

                //QueryDb
                string  index  = string.Empty;
                DataRow row    = null;
                int     nStart = 5;

                using (DAL.mSCOA_VaultDataSetTableAdapters.GetMuncde_BSA_ccyy_YTableAdapter TA = new DAL.mSCOA_VaultDataSetTableAdapters.GetMuncde_BSA_ccyy_YTableAdapter())
                {
                    DAL.mSCOA_VaultDataSet.GetMuncde_BSA_ccyy_YDataTable tbl = new DAL.mSCOA_VaultDataSet.GetMuncde_BSA_ccyy_YDataTable();
                    TA.Fill(tbl);

                    DataTable tblResult = tbl;
                    for (int i = 0; i < tblResult.Rows.Count; i++)
                    {
                        row = tblResult.Rows[i];

                        index = row["Code"].ToString();

                        for (int j = nStart; j < 47; j++)
                        {
                            // xlField = xlWorkSheet.Rows.Cells[j, 3];
                            if (xlSheet.Cells[j, 2].DisplayText == index)
                            {
                                //xlField = xlWorkSheet.Rows.Cells[j, 5];
                                xlSheet.Cells[j, 4].Formula = "= 0" + row["EstYearEnd"].ToString();
                                nStart = j;
                                break;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                xlSheet.Workbook.EndUpdate();
                xlControl.EndUpdate();
            }
        }
예제 #5
0
        protected Point GetSelectionColumnBounds(Range selectedRange = null)
        {
            DevExpress.Spreadsheet.Worksheet workSheet = null;
            if (this.spreadsheetControl.ActiveWorksheet != null)
            {
                workSheet = this.spreadsheetControl.ActiveWorksheet;
            }

            DevExpress.Spreadsheet.Range UsedRange = null, UsableRange = null;

            int debutCol, finCol;

            Ui.Office.Sheet sheet = new Ui.Office.Sheet(workSheet.Index + 1, workSheet.Name);

            if (selectedRange != null)
            {
                UsedRange = workSheet.GetUsedRange();

                int debutLigneUsedRange = UsedRange.TopRowIndex + 1;
                int finLigneUsedRange   = UsedRange.BottomRowIndex + 1;

                int debutColUsedRange = UsedRange.LeftColumnIndex + 1;
                int finColUsedRange   = UsedRange.RightColumnIndex + 1;

                int debutLigneUsableRange = selectedRange.CellCount > 0 ? selectedRange.Cells[0].Row : selectedRange.Items[0].Row1;
                int finLigneUsableRange   = selectedRange.CellCount > 0 ? selectedRange.Cells[selectedRange.Cells.Count - 1].Row : selectedRange.Items[0].Row2;

                int debutColUsableRange = selectedRange.CellCount > 0 ? selectedRange.Cells[0].Column : selectedRange.Items[0].Column1;
                int finColUsableRange   = selectedRange.CellCount > 0 ? selectedRange.Cells[selectedRange.Cells.Count - 1].Column : selectedRange.Items[0].Column2;

                debutCol = (debutColUsedRange < debutColUsableRange) ? debutColUsableRange : debutColUsedRange;
                finCol   = (finColUsedRange > finColUsableRange) ? finColUsableRange : finColUsedRange;
            }
            else
            {
                UsableRange = workSheet.GetUsedRange();
                UsedRange   = UsableRange;

                debutCol = UsedRange.LeftColumnIndex + 1;
                finCol   = UsedRange.RightColumnIndex + 1;
            }
            return(new Point(debutCol, finCol));
        }
        private void BtnSave_Click(object sender, EventArgs e)
        {
            SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false);
            SplashScreenManager.Default.SetWaitFormDescription("Loading Data ");

            if (ValidateData())
            {
                InfoGridView.CloseEditor();
                InfoGridView.UpdateCurrentRow();
                using (var sqlcon = new SqlConnection(ProjectFunctions.GetConnection()))
                {
                    sqlcon.Open();
                    var sqlcom      = sqlcon.CreateCommand();
                    var transaction = sqlcon.BeginTransaction("SaveTransaction");
                    sqlcom.Connection  = sqlcon;
                    sqlcom.Transaction = transaction;
                    sqlcom.CommandType = CommandType.Text;
                    try
                    {
                        if (S1 == "&Add")
                        {
                            txtPINo.Text = GetNewInvoiceDocumentNo().PadLeft(6, '0');

                            sqlcom.CommandText = "Insert into PIMstNew(PINo,PIDate,PIStoreCode)values(@PINo,@PIDate,@PIStoreCode)";
                            sqlcom.Parameters.AddWithValue("@PINo", txtPINo.Text.Trim());
                            sqlcom.Parameters.AddWithValue("@PIDate", Convert.ToDateTime(txtPIDate.Text));
                            sqlcom.Parameters.AddWithValue("@PIStoreCode", txtStoreCode.Text.Trim());

                            sqlcom.ExecuteNonQuery();
                            sqlcom.Parameters.Clear();
                        }

                        if (S1 == "Edit")
                        {
                            sqlcom.CommandType = CommandType.Text;
                            sqlcom.CommandText = "Update PIMstNew Set PIStoreCode=@PIStoreCode Where PINo = @PINo And PIDate=@PIDate ";
                            sqlcom.Parameters.AddWithValue("@PINo", txtPINo.Text.Trim());
                            sqlcom.Parameters.AddWithValue("@PIDate", Convert.ToDateTime(txtPIDate.Text));
                            sqlcom.Parameters.AddWithValue("@PIStoreCode", txtStoreCode.Text.Trim());
                            sqlcom.Parameters.Clear();


                            sqlcom.CommandType = CommandType.Text;
                            sqlcom.CommandText = " Delete from PIDataNew Where PINo = @PINo And PIDate=@PIDate ";
                            sqlcom.Parameters.AddWithValue("@PINo", txtPINo.Text.Trim());
                            sqlcom.Parameters.AddWithValue("@PIDate", Convert.ToDateTime(txtPIDate.Text));
                            sqlcom.ExecuteNonQuery();
                            sqlcom.Parameters.Clear();
                        }


                        SplashScreenManager.Default.SetWaitFormDescription("Saving Data ");
                        //foreach (DataRow dr in dt.Rows)
                        //{
                        //    sqlcom.CommandType = CommandType.Text;


                        //    sqlcom.CommandText = " Insert into PIDataNew"
                        //                               + " (PINo,PIDate,PIQty,EANNo)"
                        //                               + " values(@PINo,@PIDate,@PIQty,@EANNo)";

                        //    sqlcom.Parameters.AddWithValue("@PINo", txtPINo.Text.Trim());
                        //    sqlcom.Parameters.AddWithValue("@PIDate", Convert.ToDateTime(txtPIDate.Text));
                        //    sqlcom.Parameters.AddWithValue("@PIQty", Convert.ToDecimal(dr["PIQty"]));
                        //    sqlcom.Parameters.AddWithValue("@EANNo", dr["EANNo"].ToString());

                        //    sqlcom.ExecuteNonQuery();
                        //    sqlcom.Parameters.Clear();
                        //}



                        DataTable dtFinal = new DataTable();
                        dtFinal.Columns.Add("PINo", typeof(string));
                        dtFinal.Columns.Add("PIDate", typeof(string));
                        dtFinal.Columns.Add("PIQty", typeof(string));
                        dtFinal.Columns.Add("EANNo", typeof(string));

                        dtFinal.Rows.Clear();
                        foreach (DataRow dr in (InfoGrid.DataSource as DataTable).Rows)
                        {
                            DataRow drRow = dtFinal.NewRow();
                            drRow["PINo"]   = txtPINo.Text.Trim();
                            drRow["PIDate"] = Convert.ToDateTime(txtPIDate.Text).ToString("yyyy-MM-dd");
                            drRow["PIQty"]  = dr["PIQty"].ToString();
                            drRow["EANNo"]  = dr["EANNo"].ToString();
                            dtFinal.Rows.Add(drRow);
                        }
                        dtFinal.AcceptChanges();
                        sqlcom.CommandType    = CommandType.StoredProcedure;
                        sqlcom.CommandText    = "sp_InsertPINew";
                        sqlcom.CommandTimeout = 600000;
                        SqlParameter param = new SqlParameter
                        {
                            ParameterName = "@PerformaInv",
                            Value         = dtFinal
                        };
                        sqlcom.Parameters.Add(param);
                        sqlcom.ExecuteNonQuery();
                        sqlcom.Parameters.Clear();



                        transaction.Commit();
                        //ProjectFunctions.SpeakError("PI Data Saved Successfully");
                        sqlcon.Close();

                        if (FromExcel == "Y")
                        {
                            //  SplashScreenManager.Default.SetWaitFormDescription("Saving PI " + i.ToString());
                            i++;


                            LoadSheet();
                            DevExpress.Spreadsheet.Workbook workbook = new DevExpress.Spreadsheet.Workbook();
                            workbook.LoadDocument(openFileDialog1.FileName);
                            DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[i];
                            if (i <= workbook.Sheets.Count)
                            {
                                BtnSave_Click(null, e);
                            }
                            else
                            {
                                SplashScreenManager.CloseForm(false);
                                Close();
                            }
                        }
                        else
                        {
                            SplashScreenManager.CloseForm(false);
                        }
                        Close();
                        FromExcel = "N";
                    }
                    catch (Exception ex)
                    {
                        ProjectFunctions.SpeakError("Something Wrong. \n I am going to Roll Back." + ex.Message);
                        try
                        {
                            transaction.Rollback();
                        }
                        catch (Exception ex2)
                        {
                            ProjectFunctions.SpeakError("Something Wrong. \n Roll Back Failed." + ex2.Message);
                        }
                    }
                }
            }
        }
예제 #7
0
        private void Populate_Muncde_OSA_ccyy_Mnn(int finYear, string monthEnd)
        {
            try
            {
                //xlWorkSheet = (Excel.Worksheet) xlWorkBook.Sheets["Sheet1"];

                xlSheet = xlControl.Document.Worksheets["Sheet1"];
                xlControl.BeginUpdate();
                xlSheet.Workbook.BeginUpdate();


                string celladdress = "A10";
                //var cell = xlWorkSheet.get_Range(celladdress, Type.Missing);
                //xlSheet.Cells[celladdress].Value =  finYear.ToString();

                xlSheet.Cells[celladdress].Value = finYear.ToString();

                //b  monthend
                celladdress = "B10";
                //cell = xlWorkSheet.get_Range(celladdress, Type.Missing);
                //cell.Value = monthEnd;

                xlSheet.Cells[celladdress].Value = monthEnd;

                //c _regionalIdentifier
                celladdress = "C10";
                // cell = xlWorkSheet.get_Range(celladdress, Type.Missing);
                xlSheet.Cells[celladdress].Value = _regionalIdentifier;

                //QueryDb
                string  index  = string.Empty;
                DataRow row    = null;
                int     nStart = 9;// 10;

                using (DAL.mSCOA_VaultDataSetTableAdapters.GetMuncde_OSA_ccyy_MnnTableAdapter TA = new DAL.mSCOA_VaultDataSetTableAdapters.GetMuncde_OSA_ccyy_MnnTableAdapter())
                {
                    DAL.mSCOA_VaultDataSet.GetMuncde_OSA_ccyy_MnnDataTable tbl = new DAL.mSCOA_VaultDataSet.GetMuncde_OSA_ccyy_MnnDataTable();
                    TA.Fill(tbl);

                    DataTable tblResult = tbl; // DBCall.GetMuncde_OSA_ccyy_Mnn();
                    for (int i = 0; i < tblResult.Rows.Count; i++)
                    {
                        row = tblResult.Rows[i];

                        index = row["Code"].ToString();
                        if (!string.IsNullOrEmpty(index))
                        {
                            for (int j = nStart; j < 3896; j++)
                            {
                                //xlField = xlSheet.Rows[j, 11].Value ;
                                if (xlSheet.Cells[j, 10].DisplayText == index)
                                {
                                    //xlField = xlWorkSheet.Rows.Cells[j, 10];
                                    decimal val = 0;
                                    decimal.TryParse(row[monthEnd].ToString(), out val);

                                    xlSheet.Cells[j, 9].Formula = "= " + val.ToString();
                                    nStart = j;
                                    break;
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                xlSheet.Workbook.EndUpdate();
                xlControl.EndUpdate();
            }
        }
예제 #8
0
 public void Dispose()
 {
     xlSheet   = null;
     xlControl = null;
 }
 private void InitializeWorksheet()
 {
     DevExpress.Spreadsheet.Worksheet sheet = spreadsheetControl1.ActiveWorksheet;
     sheet.Cells[0, 0].Formula = "SIN(B1)";
     sheet.Cells[0, 1].Value   = 30;
 }