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); } } } }); }
/// <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); } }
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()); } } }
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)); }
/// <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); }
/// <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)); }