Beispiel #1
0
        private static void SetCellValue_Column_Row <T>(DevExpress.Spreadsheet.Range rang, List <T> ListSheet) where T : new()
        {
            Type type = typeof(T);

            System.Reflection.PropertyInfo[] properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

            ListSheet.ForEach(s =>
            {
                var markTemp = properties.Single(x => x.Name == "Mark").GetValue(s).ToString();
                var mark     = markTemp == "A" ? 0 : markTemp == "B" ? 1 : markTemp == "C" ? 2 : 3;

                for (int j = 0; j < rang.RowCount; j++)
                {
                    var query = properties.Where(x => x.Name == $"A{(j + 1).ToString().PadLeft(2, '0')}");
                    if (query == null || rang[j, mark].Value.ToString() == "——")
                    {
                        continue;
                    }
                    foreach (var item in query)
                    {
                        if (item.PropertyType == typeof(string))
                        {
                            rang[j, mark].Value = (string)item.GetValue(s);
                        }
                    }
                }
            });
        }
Beispiel #2
0
        /// <summary>
        /// 多列多行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="rang"></param>
        /// <param name="sheet"></param>
        private static void SetModelValue_Column_Row <T>(DevExpress.Spreadsheet.Range rang, List <T> sheet) where T : class, new()
        {
            Type type = typeof(T);

            //sheet = new List<T>();
            System.Reflection.PropertyInfo[] properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            for (int i = 0; i < rang.ColumnCount; i++)
            {
                var    tempSheet = new T();
                string mark      = i == 0 ? "A" : i == 1 ? "B" : i == 2 ? "C" : "D";
                for (int j = 0; j < rang.RowCount; j++)
                {
                    var query = properties.Where(x => x.Name == $"A{(j + 1).ToString().PadLeft(2, '0')}");
                    if (query == null || rang[j, i].Value.ToString() == "——")
                    {
                        continue;
                    }
                    foreach (var item in query)
                    {
                        item.SetValue(tempSheet, rang[j, i].Value.ToString());
                    }
                }
                properties.Single(x => x.Name == "Mark").SetValue(tempSheet, mark);
                sheet.Add(tempSheet);
            }
        }
        /// <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);
            }
        }
Beispiel #4
0
        private static void SetModelValue <T>(DevExpress.Spreadsheet.Range rang, T sheet)
        {
            System.Reflection.PropertyInfo[] properties = sheet.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

            for (int i = 0; i < rang.ColumnCount; i++)
            {
                var query = properties.Where(x => x.Name == $"A{(i + 1).ToString().PadLeft(2, '0')}");
                if (query == null)
                {
                    continue;
                }
                foreach (var item in query)
                {
                    item.SetValue(sheet, rang[i].Value.ToString());
                }
            }
        }
Beispiel #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));
        }
Beispiel #6
0
 /// <summary>
 /// 单列多行赋值
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="rang"></param>
 /// <param name="sheet"></param>
 private static void SetModelValue_Column <T>(DevExpress.Spreadsheet.Range rang, T sheet)
 {
     for (int i = 0; i < rang.ColumnCount; i++)
     {
         string mark = i == 0 ? "A" : i == 1 ? "B" : i == 2 ? "C" : "D";
         System.Reflection.PropertyInfo[] properties = sheet.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
         for (int j = 0; j < rang.RowCount; j++)
         {
             var query = properties.Where(x => x.Name == $"A{(j + 1).ToString().PadLeft(2, '0')}");
             if (query == null || rang[j, i].Value.ToString() == "——")
             {
                 continue;
             }
             foreach (var item in query)
             {
                 item.SetValue(sheet, rang[j, i].Value.ToString());
             }
         }
         //properties.Single(x => x.Name == "Mark").SetValue(sheet, mark);
     }
 }
        public double getSelectedRangeSum()
        {
            DevExpress.Spreadsheet.Range range = this.spreadsheetControl.Selection;
            IEnumerable <DevExpress.Spreadsheet.Cell> cells = range.ExistingCells;

            double sum = 0;

            foreach (DevExpress.Spreadsheet.Cell cell in cells)
            {
                double value;
                try
                {
                    value = Convert.ToDouble(cell.Value.ToString());
                }
                catch
                {
                    value = 0;
                }
                sum = sum + value;
            }
            return(sum);
        }
Beispiel #8
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="selectedRange"></param>
        /// <param name="isSelected"></param>
        /// <returns>Rectangle(X,Y,Width,Height)
        ///  X =>startLine
        ///  Y => startCol
        ///  Width => endLine
        ///  Height => endCol
        /// </returns>
        public System.Drawing.Rectangle GetSelectionBounds(Range selectedRange = null, bool isSelected = false, bool FirstRowIsHeader = false)
        {
            DevExpress.Spreadsheet.Worksheet workSheet;
            if (this.spreadsheetControl.ActiveWorksheet != null)
            {
                workSheet = this.spreadsheetControl.ActiveWorksheet;
            }
            else
            {
                return(new System.Drawing.Rectangle(1, 1, 1, 1));
            }
            DevExpress.Spreadsheet.Range selection = this.spreadsheetControl.Selection;
            //if (selection == null) return  new System.Drawing.Rectangle(1, 1, 1, 1);


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

            int debutLigne, finLigne, debutCol, finCol;

            Ui.Office.Sheet sheet = new Ui.Office.Sheet(workSheet.Index + 1, workSheet.Name);
            if (isSelected)
            {
                int debutLigneUsedRange   = -1,
                    finLigneUsedRange     = -1,
                    debutColUsedRange     = -1,
                    finColUsedRange       = -1,
                    debutLigneUsableRange = -1,
                    finLigneUsableRange   = -1,
                    debutColUsableRange   = -1,
                    finColUsableRange     = -1;
                UsedRange = workSheet.GetUsedRange();

                if (selectedRange != null)
                {
                    debutLigneUsedRange = UsedRange.TopRowIndex + 1;
                    finLigneUsedRange   = UsedRange.BottomRowIndex + 1;
                    debutColUsedRange   = UsedRange.LeftColumnIndex + 1;
                    finColUsedRange     = UsedRange.RightColumnIndex + 1;;


                    debutLigneUsableRange = selectedRange.Cells[0].Row;
                    finLigneUsableRange   = selectedRange.Cells[selectedRange.Cells.Count - 1].Row;

                    debutColUsableRange = selectedRange.Cells[0].Column;
                    finColUsableRange   = selectedRange.Cells[selectedRange.Cells.Count - 1].Column;
                }
                else
                {
                    UsableRange         = selection;
                    debutLigneUsedRange = UsedRange.TopRowIndex + 1;
                    finLigneUsedRange   = UsedRange.BottomRowIndex + 1;

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

                    debutLigneUsableRange = UsableRange.TopRowIndex + 1;
                    finLigneUsableRange   = UsableRange.BottomRowIndex + 1;

                    debutColUsableRange = UsableRange.LeftColumnIndex + 1;
                    finColUsableRange   = UsableRange.RightColumnIndex + 1;
                }
                debutLigne = (debutLigneUsedRange < debutLigneUsableRange) ? debutLigneUsableRange : debutLigneUsedRange;
                finLigne   = (finLigneUsedRange > finLigneUsableRange) ? finLigneUsableRange : finLigneUsedRange;

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

                debutLigne = UsedRange.TopRowIndex + 1;
                finLigne   = UsedRange.BottomRowIndex + 1;

                debutCol = UsedRange.LeftColumnIndex + 1;
                finCol   = UsedRange.RightColumnIndex + 1;
            }

            debutLigne = FirstRowIsHeader ? debutLigne == 1 ? debutLigne + 1 : debutLigne : debutLigne;


            return(new System.Drawing.Rectangle(debutLigne, debutCol, finLigne, finCol));
        }