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