/// <summary>
/// Gets a cell selection from A1 reference
/// </summary>
/// <param name="cells">The cells collection</param>
/// <param name="cellRangeRef">An A1 reference designating a rectangular area, e.g. "A1:B5"</param>
/// <returns>A cell selection matching the referenced area</returns>
public static CellSelection GetCellSelection(this Cells cells, string cellRangeRef)
{
var cellNames = cellRangeRef.Split(':');
int row;
int column;
CellRange cellRange;
if (cellNames.Length == 1)
{
NameConverter.ConvertCellNameToIndex(cellRangeRef, out row, out column);
CellIndex index = new CellIndex(row, column);
cellRange = new CellRange(index, index);
}
else
{
int row1 = 0;
int column1 = 0;
bool isRowAbsolute;
bool isColumnAbsolute;
bool isRowAbsolute1;
bool isColumnAbsolute1;
NameConverter.ConvertCellNameToIndex(cellNames[0], out isRowAbsolute, out row, out isColumnAbsolute, out column);
NameConverter.ConvertCellNameToIndex(cellNames[1], out isRowAbsolute1, out row1, out isColumnAbsolute1, out column1);
cellRange = new CellRange(new CellIndex(row, column), new CellIndex(row1, column1));
}
return cells[cellRange];
}
public void DoCheckOperation(Parse expectedValue, CellRange cells)
{
CellOperation.Check(GetTargetObject(),
MethodRowSelector.SelectMethodCells(cells),
MethodRowSelector.SelectParameterCells(cells),
expectedValue);
}
public virtual FrameworkElement CreateCell(DataGrid grid, CellType cellType, CellRange rng)
{
// cell border
var bdr = CreateCellBorder(grid, cellType, rng);
// bottom right cells have no content
if (!rng.IsValid)
{
return bdr;
}
// bind/customize cell by type
switch (cellType)
{
case CellType.Cell:
CreateCellContent(grid, bdr, rng);
break;
case CellType.ColumnHeader:
CreateColumnHeaderContent(grid, bdr, rng);
break;
}
// done
return bdr;
}
public void DoCheckOperation(Parse expectedValue, CellRange cells)
{
CellOperation.Check(GetTargetObject(),
new CellRange(MethodCells(cells)),
new CellRange(ParameterCells(cells)),
expectedValue);
}
public MainPage()
{
InitializeComponent();
Workbook workbook = this.LoadWorkbook();
this.radSpreadsheet.Workbook = workbook;
this.DocumentTableCellRange = new CellRange(2, 0, 48, 5);
}
private void gridDesktop1_SelectedCellRangeChanged(object sender, Aspose.Cells.GridDesktop.CellRangeEventArgs e)
{
// Checking if the range of cells is not empty
if ((e.CellRange.EndColumn - e.CellRange.StartColumn > 0) ||
(e.CellRange.EndRow - e.CellRange.StartRow > 0))
{
// Assigning the updated CellRange to global variable
range = e.CellRange;
}
}
private void button1_Click(object sender, EventArgs e)
{
// ExStart:MergeCells
// Accessing the worksheet of the Grid that is currently active
Worksheet sheet = gridDesktop1.GetActiveWorksheet();
// Creating a CellRange object starting from "B4" to "C6"
CellRange range = new CellRange("B4", "C6");
// Merging a range of cells
sheet.Merge(range);
// ExEnd:MergeCells
}
private static string RangeToFormula(CellRange range, Worksheet activeSheet)
{
string sheetName = activeSheet.Name + "!";
if (range.RowCount == 1 && range.ColumnCount == 1)
{
CalcCellExpression exp = new CalcCellExpression(range.Row, range.Column, true, true);
return sheetName + ((ICalcEvaluator)activeSheet).Expression2Formula(exp, 0, 0);
}
else
{
CalcRangeExpression exp = CreateRangeExpressionByCount(
range.Row, range.Column, range.RowCount, range.ColumnCount,
true, true, true, true);
return sheetName + ((ICalcEvaluator)activeSheet).Expression2Formula(exp, 0, 0);
}
}
public virtual Border CreateCellBorder(DataGrid grid, CellType cellType, CellRange rng)
{
var bdr = new Border();
switch (cellType)
{
// scrollable cells
case CellType.Cell:
var row = grid.Rows[rng.Row];
// apply data context to border
bdr.DataContext = row.DataItem;
// apply cell background
var even = grid.Rows[rng.Row].VisibleIndex % 2 == 0;
bdr.Background = even || rng.RowSpan > 1 || grid.AlternatingRowBackground == null
? grid.RowBackground
: grid.AlternatingRowBackground;
// apply borders/padding
bdr.BorderThickness = GetBorderThickness(grid, rng);
bdr.BorderBrush = grid.GridLinesBrush;
bdr.Padding = GetCellPadding(grid, grid.Cells, rng);
break;
case CellType.ColumnHeader:
bdr.BorderThickness = GetColumnHeaderBorderThickness(grid);
bdr.BorderBrush = grid.HeaderGridLinesBrush;
bdr.Padding = GetCellPadding(grid, grid.ColumnHeaders, rng);
// apply background
bdr.Background = grid.ColumnHeaderBackground;
break;
}
// don't ever use null brushes for background
if (bdr.Background == null)
{
bdr.Background = _brTransparent;
}
bdr.Language = grid.Language;
// done
return bdr;
}
public override void CreateCellContent(C1FlexGrid grid, Border bdr, CellRange rng)
{
var tableLink = grid[rng.Row, rng.Column] as TableLink;
if (tableLink != null)
{
var btn = new HyperlinkButton();
btn.Tag = tableLink;
btn.Content = tableLink.Href;
btn.VerticalAlignment = VerticalAlignment.Center;
btn.HorizontalAlignment = HorizontalAlignment.Left;
btn.Click += btn_Click;
bdr.Child = btn;
}
else
{
base.CreateCellContent(grid, bdr, rng);
}
}
private void FormattingCellRange_Load(object sender, EventArgs e)
{
// ExStart:1
// Accessing the worksheet of the Grid that is currently active
Worksheet sheet = gridDesktop1.GetActiveWorksheet();
// Setting sample values
GridCell cell = sheet.Cells["b7"];
cell.SetCellValue("1");
cell = sheet.Cells["c7"];
cell.SetCellValue("2");
cell = sheet.Cells["d7"];
cell.SetCellValue("3");
cell = sheet.Cells["e7"];
cell.SetCellValue("4");
// Creating a CellRange object starting from "B7" to "E7"
CellRange range = new CellRange(6, 1, 6, 4);
// Accessing and setting Style attributes
Style style = new Style(this.gridDesktop1);
style.Color = Color.Yellow;
// Applying Style object on the range of cells
sheet.SetStyle(range, style);
// Creating a customized Font object
Font font = new Font("Courier New", 12f);
// Setting the font of range of cells to the customized Font object
sheet.SetFont(range, font);
// Setting the font color of range of cells to Red
sheet.SetFontColor(range, Color.Red);
// ExEnd:1
gridDesktop1.Refresh();
}
protected abstract IEnumerable<Parse> ParameterCells(CellRange theCells);
public override void DoRows(Parse rows)
{
memberNameCells = new CellRange(rows.Parts);
rowWidth = rows.Parts.Size;
valueCells = new ValueArray(RepeatString);
base.DoRows(rows.More);
}
protected override IEnumerable<Parse> ParameterCells(CellRange theCells)
{
return theCells.Cells.Skip(1).Alternate();
}
private string GetStringValue(CellRange excelRow, int position)
{
string value = null;
ExcelCell cell = excelRow[position];
if (cell.Value != null)
{
value = cell.Value.ToString();
value = value.Trim();
}
return value;
}
// заполнение помещений
private void FillMaterial(Table table)
{
int row = table.Rows.Count - 1;
// Группировка по секциям
var sections = _rooms.GroupBy(r => r.Ws.Section).OrderBy(s=>s.Key);
foreach (var section in sections)
{
table.InsertRows(row+1, 8, 1);
_mCells = CellRange.Create(table, row, 0, row, Enum.GetValues(typeof(ColEnum)).Length - 1);
table.MergeCells(_mCells);
table.Cells[row, 0].TextString = "Секция " + section.Key;
row++;
// Группировка по квартирам
var apartments = section.GroupBy(r => r.Owner).OrderBy(a=>a.Key);
foreach (var apartment in apartments)
{
int rowApartment = row;
table.Cells[rowApartment, (int)ColEnum.Apartament].TextString = apartment.Key;
// сортировка помещений в квартире по номерам
var rooms = apartment.OrderBy(n => n.Number);
foreach (var room in rooms)
{
FillRoom(table,row, room);
row = table.Rows.Count - 1;
}
if (row-1 > rowApartment)
{
_mCells = CellRange.Create(table, rowApartment, (int)ColEnum.Apartament, row-1, (int)ColEnum.Apartament);
table.MergeCells(_mCells);
}
}
}
}
Brush GetForegroundBrush(DataGrid grid, Row r, CellRange rng, Brush defBrush)
{
var fore = grid.Foreground;
// done
return fore != null ? fore : defBrush;
}
void CreateCellContent(DataGrid grid, DataGridPanel panel, Border bdr, CellRange rng)
{
// get row and column
var r = panel.Rows[rng.Row];
var c = panel.Columns[rng.Column];
//var gr = r as GroupRow;
// honor user templates (if the row has a backing data item)
if (r.DataItem != null && c.CellTemplate != null && panel.CellType == CellType.Cell)
{
bdr.Padding = GetTemplatePadding(bdr.Padding);
bdr.Child = GetTemplatedCell(grid, c.CellTemplate);
return;
}
// get binding, unbound value for the cell
var b = r.DataItem != null ? c.Binding : null;
var ubVal = r.DataItem != null ? null : panel[rng.Row, rng.Column];
// get foreground brush taking selected state into account
var fore = GetForegroundBrush(grid, r, rng, grid.Foreground);
// handle non-text types
var type = c.DataType;
TextBlock tb = null;
CheckBox chk = null;
if (// not a group, or hierarchical
panel.CellType == CellType.Cell &&
(type == typeof(bool) || type == typeof(bool?))) // and bool
{
// Boolean cells: use CheckBox
chk = new CheckBox();
chk.IsThreeState = type == typeof(bool?);
chk.HorizontalAlignment = HorizontalAlignment.Center;
chk.VerticalAlignment = VerticalAlignment.Center;
chk.MinWidth = 32;
chk.HorizontalAlignment = HorizontalAlignment.Stretch;
chk.VerticalAlignment = VerticalAlignment.Stretch;
chk.Margin = new Thickness(0, -10, 0, -10);
chk.IsHitTestVisible = false;
chk.IsTabStop = false;
if (fore != null)
{
chk.Foreground = fore;
}
bdr.Child = chk;
if (b != null)
{
chk.SetBinding(CheckBox.IsCheckedProperty, b);
}
else
{
var value = ubVal as bool?;
chk.IsChecked = value;
}
}
else
{
// use TextBlock for everything else (even empty cells)
tb = new TextBlock();
tb.VerticalAlignment = VerticalAlignment.Center;
if (fore != null)
{
tb.Foreground = fore;
}
bdr.Child = tb;
// bound values
if (b != null)
{
// apply binding
tb.SetBinding(TextBlock.TextProperty, b);
}
else if (ubVal != null) // unbound values
{
// get column format from column and/or binding
tb.Text = r.GetDataFormatted(c);
}
}
}
private void JoinTable()
{
if (BlockList.Count >= 2)
{
TplBlock block = BlockList[1]; //要合并的第一个Block
int startRowIndex = block.StartParseRowIndex;
int startColumn = block.StartParseColumnIndex + block.ColumnsCount;
int num3 = 0;
for (int i = 2; i < BlockList.Count; i++) //以第一个block为基准,合并其他指定joinat的block
{
TplBlock block2 = BlockList[i];
if ((block2.Joinat >= 0) && (block2.RowsCount > 0))
{
//获取joinat之后的区域
CellRange range = RangeHelper.GetRange(Sheet,
block2.StartParseColumnIndex + block2.Joinat + 1,
block2.StartParseRowIndex - num3,
block2.ColumnsCount,
block2.RowsCount);
CellRange destRange = RangeHelper.GetRange(Sheet, startColumn + 1, block.StartParseRowIndex,
block2.ColumnsCount, block2.RowsCount);
range.Copy(destRange);//block2区域复制到block1右边
Sheet.DeleteRow(range.Row, range.RowCount);
num3 += block2.RowsCount;
if ((block2.DynamicColumn != null) && (block2.DynamicColumn.StartCellIndex == block2.Joinat))
{
foreach (var line in block2.TplLineList)
{
if (line.ContainsHGroup) //block2中包含横向分组的行hg
{
bool flag = false;
foreach (var rowIndex in line.InsertedRowList)
{
int startRow = (rowIndex - block2.StartParseRowIndex) + startRowIndex;
//动态添加移动的起始行
CellRange range2 = RangeHelper.GetRange(Sheet, startColumn, startRow, 1, 1);
//相同行区域的block1最后单元格?
if (range2.MergeArea != null)
{
range2 = RangeHelper.GetRange(Sheet, range2.MergeArea.Column,
//?range2合并区域的起始单元格
range2.MergeArea.Row, 1, 1);
}
CellRange range3 = RangeHelper.GetRange(Sheet, startColumn + 1, startRow, 1, 1);
//block2的第一个单元格
if (range3.MergeArea != null)
{
range3 = RangeHelper.GetRange(Sheet, range3.MergeArea.Column, //第一个单元格的起始区域
range3.MergeArea.Row, 1, 1);
}
if (range2.Text.Equals(range3.Text))
{
RangeHelper.GetRange(Sheet, range2.Column, range2.Row,
(range3.Column + range3.ColumnCount) - range2.Column,
Math.Min(range3.RowCount, range2.RowCount)).Merge(true);
flag = true;
}
}
if (!flag)
{
break;
}
}
}
}
startColumn += block2.ColumnsCount - block2.Joinat; //合并后起始列更新
}
}
}
}
public virtual bool IsSortSymbolRow(DataGrid grid, CellRange rng)
{
return rng.BottomRow == grid.ColumnHeaders.Rows.Count - 1;
}
/// <summary>
/// Returns an ordered array of CellRange objects, from largest to smallest, that contain the selected cells
/// with minimal overlap between the ranges.
/// </summary>
/// <param name="rowCount">The number of rows in the sheet in which to look.</param>
/// <param name="columnCount">The number of columns in the sheet in which to look.</param>
/// <returns>Returns an ordered array of CellRange objects, from largest to smallest, that contain the selected cells with minimal overlap between the ranges.</returns>
public CellRange[] GetSelections(int rowCount, int columnCount)
{
List <CellRange> items = new List <CellRange>((IEnumerable <CellRange>) this.items);
List <CellRange> list2 = new List <CellRange>();
while (items.Count > 0)
{
CellRange range;
long num = 0L;
int num2 = -1;
for (int i = 0; i < items.Count; i++)
{
long num3;
range = items[i];
if (range.ColumnCount == -1)
{
if (range.Column == -1)
{
num3 = columnCount;
}
else
{
num3 = columnCount - range.Column;
}
}
else
{
num3 = range.ColumnCount;
}
if (range.RowCount == -1)
{
if (range.Row == -1)
{
num3 *= rowCount;
}
else
{
num3 *= rowCount - range.Row;
}
}
else
{
num3 *= range.RowCount;
}
if (num3 > num)
{
num2 = i;
num = num3;
}
}
if (num2 < 0)
{
num2 = items.Count - 1;
}
range = items[num2];
CellRange original = new CellRange(range.Row, range.Column, range.RowCount, range.ColumnCount);
list2.Add(original);
items.RemoveAt(num2);
int num5 = 0;
int num6 = items.Count;
while (num5 < num6)
{
range = items[num5];
if (original.Intersects(range.Row, range.Column, range.RowCount, range.ColumnCount))
{
CellRange split = new CellRange(range.Row, range.Column, range.RowCount, range.ColumnCount);
items.RemoveAt(num5);
this.Split(items, original, split);
num6--;
}
else
{
num5++;
}
}
}
CellRange[] rangeArray = new CellRange[list2.Count];
if (list2.Count > 0)
{
list2.CopyTo(rangeArray);
}
return(rangeArray);
}
/// <summary>
/// Extends the selection.
/// </summary>
/// <param name="row">The row index to which to extend the selection.</param>
/// <param name="column">The column index to which to extend the selection.</param>
/// <param name="rowCount">The number of rows to which to extend the selection.</param>
/// <param name="columnCount">The number of columns to which to extend the selection.</param>
public override void ExtendSelection(int row, int column, int rowCount, int columnCount)
{
int num;
int num2;
int num3;
int num4;
if ((row == -1) && (column == -1))
{
num = -1;
num3 = -1;
num2 = -1;
num4 = -1;
}
else if (row == -1)
{
num = -1;
num3 = -1;
num2 = Math.Min(this.anchorColumn, column);
num4 = (Math.Max(this.anchorColumn, column) - num2) + 1;
}
else if (column == -1)
{
num = Math.Min(this.anchorRow, row);
num3 = (Math.Max(this.anchorRow, row) - num) + 1;
num2 = -1;
num4 = -1;
}
else
{
num = row;
num3 = rowCount;
num2 = column;
num4 = columnCount;
}
if (this.selectionPolicy != Dt.Cells.Data.SelectionPolicy.Single)
{
if (this.selectionPolicy == Dt.Cells.Data.SelectionPolicy.Range)
{
while (this.items.Count > 1)
{
CellRange range = this.items[0];
base.FireChanged(range.Row, range.Column, range.RowCount, range.ColumnCount);
this.items.RemoveAt(0);
}
}
if (this.selectionUnit == Dt.Cells.Data.SelectionUnit.Row)
{
num2 = -1;
num4 = -1;
}
else if (this.selectionUnit == Dt.Cells.Data.SelectionUnit.Column)
{
num = -1;
num3 = -1;
}
if ((this.selectionUnit == Dt.Cells.Data.SelectionUnit.Row) && !this.IsSelected(this.anchorRow, this.anchorColumn))
{
this.RemoveSelection(num, num2, num3, num4);
}
else if (this.items.Count <= 0)
{
CellRange range6 = new CellRange(num, num2, num3, num4);
this.items.Add(range6);
base.FireChanged(range6.Row, range6.Column, range6.RowCount, range6.ColumnCount);
}
else
{
CellRange range2 = null;
int num5 = this.items.Count - 1;
while (num5 >= 0)
{
if (this.items[num5].Contains(this.anchorRow, this.anchorColumn))
{
range2 = this.items[num5];
break;
}
num5--;
}
CellRange original = new CellRange(num, num2, num3, num4);
if (!original.Equals(range2))
{
if (range2 != null)
{
this.items[num5] = original;
List <CellRange> items = new List <CellRange>();
this.Split(items, original, range2);
foreach (CellRange range4 in items)
{
base.FireChanged(range4.Row, range4.Column, range4.RowCount, range4.ColumnCount);
}
items.Clear();
this.Split(items, range2, original);
foreach (CellRange range5 in items)
{
base.FireChanged(range5.Row, range5.Column, range5.RowCount, range5.ColumnCount);
}
}
else
{
this.items.Add(original);
base.FireChanged(original.Row, original.Column, original.RowCount, original.ColumnCount);
}
}
}
}
}
private void ExamineTableStructure(Parse theHeaderCells)
{
expectedCount = 0;
IHaveNoteColumns = false;
try {
myParameterCount = CountParameterCells(theHeaderCells);
methodSuffixCells = new CellRange(theHeaderCells, myParameterCount);
headerCells = theHeaderCells.At(myParameterCount + 1);
foreach (Parse headerCell in new CellRange(theHeaderCells.At(myParameterCount + 1)).Cells) {
if (headerCell.Text.Length == 0) {
IHaveNoteColumns = true;
break;
}
expectedCount++;
}
}
catch (Exception e) {
TestStatus.MarkException(theHeaderCells, e);
}
}
public void SetInputFilterForWinCCTags(C1FlexGrid grid, int nRow,int nCommandType)
{
try
{
string nWinCCTagName = grid[nRow, 4].ToString();
int nWinCCTagType = -1;
if (nCommandType == (int)Definition.RoutingCommands.WRITEGLOBALTAG) { nWinCCTagType = myHC.GetWinCCTypeFromTagName((int)Definition.SQLTables.GLOBAL_TAGS, nWinCCTagName); }
if (nCommandType == (int)Definition.RoutingCommands.WRITEMACHINETAG) { nWinCCTagType = myHC.GetWinCCTypeFromTagName((int)Definition.SQLTables.MACHINE_TAGS, nWinCCTagName); }
CellRange rg = new CellRange();
rg = grid.GetCellRange(grid.Row, 5, grid.Row, 5);
CellStyle cs = null;
switch (nWinCCTagType)
{
case 1:
cs = grid.Styles.Add("GlobalTagBool");
cs.DataType = typeof(bool);
cs.TextAlign = TextAlignEnum.CenterCenter;
rg.Style = grid.Styles["GlobalTagBool"];
break;
case 2:
case 3:
case 4:
case 5:
case 6:
case 7:
switch (nWinCCTagType)
{
case 2: // DM_VARTYPE_SBYTE Vorzeichenbehafteter 8-Bit Wert
numericUpDown_Commands.Maximum = 128;
numericUpDown_Commands.Minimum = -127;
break;
case 3: // DM_VARTYPE_BYTE Vorzeichenloser 8-Bit Wert
numericUpDown_Commands.Maximum = 256;
numericUpDown_Commands.Minimum = 0;
break;
case 4: // DM_VARTYPE_SWORD Vorzeichenbehafteter 16-Bit Wert
numericUpDown_Commands.Maximum = 32768;
numericUpDown_Commands.Minimum = -32767;
break;
case 5: // DM_VARTYPE_WORD Vorzichenloser 16-Bit Wert
numericUpDown_Commands.Maximum = 65535;
numericUpDown_Commands.Minimum = 0;
break;
case 6: // DM_VARTYPE_SDWORD Vorzeichenbehafteter 32-Bit Wert
numericUpDown_Commands.Maximum = 2147483648;
numericUpDown_Commands.Minimum = -2147483648;
break;
case 7: // DM_VARTYPE_DWORD Vorzeichenloser 32-Bit Wert
numericUpDown_Commands.Maximum = 4294967296;
numericUpDown_Commands.Minimum = 0;
break;
}
cs = grid.Styles.Add("GlobalTagDezimal");
cs.DataType = typeof(int);
cs.Editor = numericUpDown_Commands;
rg.Style = grid.Styles["GlobalTagDezimal"];
break;
case 8: //DM_VARTYPE_FLOAT Gleitkommazahl 32-Bit IEEE
cs = grid.Styles.Add("GlobalTagFloat");
cs.DataType = typeof(float);
// cs.Format = "N0";
rg.Style = grid.Styles["GlobalTagFloat"];
break;
case 9: // DM_VARTYPE_DOUBLE Gleitkommazahl 64-Bit IEEE 754
cs = grid.Styles.Add("GlobalTagDouble");
cs.DataType = typeof(double);
rg.Style = grid.Styles["GlobalTagDouble"];
break;
case 10:
case 11:
cs = grid.Styles.Add("GlobalTagString");
cs.DataType = typeof(string);
rg.Style = grid.Styles["GlobalTagString"];
break;
}
}
catch {}
}
private void FillRoom(Table table, int row, Room room)
{
// Наименование помещения
table.Cells[row, (int)ColEnum.RoomName].TextString = room.Name;
table.Cells[row, (int)ColEnum.RoomNumber].TextString = room.Number.ToString();
// Примечание
table.Cells[row, (int)ColEnum.Description].TextString = room.Description;
// матариалы в помещении
foreach (var item in Enum.GetValues(typeof(EnumConstructionType)).Cast<EnumConstructionType>())
{
if (item == EnumConstructionType.Undefined) continue;
FillRowMaterial(table, row, room, item);
}
if ((table.Rows.Count - 2) > row)
{
_mCells = CellRange.Create(table, row, (int)ColEnum.RoomName, table.Rows.Count - 2, (int)ColEnum.RoomName);
table.MergeCells(_mCells);
_mCells = CellRange.Create(table, row, (int)ColEnum.RoomNumber, table.Rows.Count - 2, (int)ColEnum.RoomNumber);
table.MergeCells(_mCells);
}
}
// update chart range
void UpdateChartRange(int column)
{
var rng = new CellRange(0, column, Rows.Count - 1, column);
_columnMax = (double)GetAggregate(Aggregate.Maximum, rng);
_columnMin = (double)GetAggregate(Aggregate.Minimum, rng);
}
protected override IEnumerable<Parse> MethodCells(CellRange theCells)
{
return theCells.Cells.Take(1);
}
public override void CreateCellContent(C1FlexGrid grid, Border bdr, CellRange range)
{
// check that this is a ChartingGrid
var cg = grid as ChartingGrid;
if (cg != null)
{
// check that this is the chart column
var c = cg.Columns[range.Column];
if (c == cg.ChartColumn)
{
try
{
// get the value
var val = (double)Convert.ChangeType(cg[range.Row, range.Column], typeof(double), CultureInfo.InvariantCulture);
// get the range (min, zero, max)
var max = cg.ChartMaxValue;
var min = cg.ChartMinValue;
var zero = min;
if (cg.ZeroBased)
{
zero = 0;
if (min > 0) min = 0;
}
// build grid to show chart bar
var g = new Grid();
g.ColumnDefinitions.Add(new ColumnDefinition());
g.ColumnDefinitions.Add(new ColumnDefinition());
g.ColumnDefinitions[0].Width = new GridLength(zero - min, GridUnitType.Star);
g.ColumnDefinitions[1].Width = new GridLength(max - zero, GridUnitType.Star);
// add content to grid
var rc = new Rectangle();
rc.Margin = new Thickness(0, 2, 0, 2);
var st = new ScaleTransform();
st.ScaleY = .8;
rc.RenderTransform = st;
if (val > zero)
{
// positive bar
rc.Fill = cg.FillPositive;
rc.RenderTransformOrigin = new Point(0, 0.5);
st.ScaleX = (val - zero) / (max - zero);
rc.SetValue(Grid.ColumnProperty, 1);
}
else
{
// negative bar
rc.Fill = cg.FillNegative;
rc.RenderTransformOrigin = new Point(1, 0.5);
st.ScaleX = (zero - val) / (zero - min);
}
g.Children.Add(rc);
// add grid to cell and be done
bdr.Child = g;
return;
}
catch { }
}
}
// allow base class
base.CreateCellContent(grid, bdr, range);
}
private void GenerateExcelReport()
{
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet1"];
//We start at first row, because for ExcelLite control, the header row is not included
int BEGIN_COLUMN_INDEX = 1;
const int REPORT_HEADER_CELL_NUMBER = 6;
const int REPORT_SUB_HEADER_CELL_NUMBER = 4;
int iRow = 1;
// Global artistic setting
ws.Columns[0].Width = 20 * 20; // make the first column smaller
// Create Report Header
CellStyle styleReportHeader = new CellStyle();
styleReportHeader.Font.Color = System.Drawing.Color.Blue;
styleReportHeader.Font.Size = 22 * 20;
styleReportHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
ReportHeader.Value = "Session Length by Camp (Online Data Only)";
ws.Rows[iRow].Height = 25 * 20;
iRow += 1;
// Create Report SubHeader - usually it's camp year and report generation time
CellStyle styleReportSubHeader = new CellStyle();
styleReportSubHeader.Font.Size = 16 * 20;
styleReportSubHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Style = styleReportSubHeader;
SubHeader.Value = string.Format("Camp Year: {0}. Generated on {1} {2}", ddlCampYear.SelectedItem.Text, DateTime.Now.ToShortDateString(), DateTime.Now.ToLongTimeString());
iRow += 4;
CellStyle styleTableTitle = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Center
};
styleTableTitle.Font.Size = 16 * 20;
styleTableTitle.FillPattern.SetSolid(Color.LightBlue);
styleTableTitle.Font.Weight = ExcelFont.BoldWeight;
CellStyle styleTableDataRow = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Center
};
CellStyle styleTableDataRowCampName = new CellStyle {
HorizontalAlignment = HorizontalAlignmentStyle.Left
};
CellStyle styleTableHeaderColumns = new CellStyle();
styleTableHeaderColumns.Font.Weight = ExcelFont.BoldWeight;
styleTableHeaderColumns.HorizontalAlignment = HorizontalAlignmentStyle.Center;
styleTableHeaderColumns.FillPattern.SetSolid(Color.LightGray);
// Data Content of report
DataSet dsModified = GenerateDataSet();
// Get rid of first columns for other tables except first one
for (int i = 1; i < dsModified.Tables.Count; i++)
{
dsModified.Tables[i].Columns.Remove("Camp Name");
}
int table_column_count = dsModified.Tables[0].Columns.Count;
int times = 0, current_starting_column = 0;
foreach (DataTable dt in dsModified.Tables)
{
if (times == 1)
{
BEGIN_COLUMN_INDEX += 1;
}
// Get the header location
current_starting_column = BEGIN_COLUMN_INDEX + times * table_column_count;
int temp_column = 0;
if (times == 0)
{
temp_column = current_starting_column + 1;
}
else
{
temp_column = current_starting_column;
}
// Table Title
CellRange TableTitle = ws.Cells.GetSubrangeAbsolute(iRow - 1, temp_column, iRow - 1, current_starting_column + dt.Columns.Count - 1);
TableTitle.Merged = true;
TableTitle.Value = dt.TableName;
TableTitle.Style = styleTableTitle;
// this creats the real table
ws.InsertDataTable(dt, iRow, current_starting_column, true);
// loop through each column and set style accordingly
for (int i = current_starting_column; i <= (dt.Columns.Count + current_starting_column - 1); i++)
{
ws.Cells[iRow, i].Style = styleTableHeaderColumns;
ws.Cells[iRow + dt.Rows.Count, i].Style = styleTableHeaderColumns;
ws.Columns[i].Width = 11 * 256;
// first column of first table, e.g. camp naem
if (times == 0)
{
if (i == current_starting_column)
{
ws.Columns[i].Width = 55 * 256; // camp/program name
}
else if (i == current_starting_column + 1)
{
ws.Columns[i].Width = 15 * 256;
}
}
else if (i == current_starting_column)
{
ws.Columns[i].Width = 15 * 256;
}
}
// Set the data row style
for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
{
ws.Rows[j].Style = styleTableDataRow;
}
// left justify the camp names
if (times == 0)
{
for (int j = iRow + 1; j < iRow + dt.Rows.Count; j++)
{
ws.Cells[j, current_starting_column].Style = styleTableDataRowCampName;
}
}
times++;
}
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3}CamperSummaryReportByCamp.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.Charset = "";
if (newFile.Length == 0)
{
this.Response.Write("Error encountered - no spreadsheet to display");
}
else
{
this.Response.WriteFile(newFile);
}
Response.End();
}
/// <summary>
/// Initializes a new instance of a <see cref="T:C1.WPF.FlexGrid.CustomAggregateEventArgs" />.
/// </summary>
/// <param name="panel">
/// <see cref="T:C1.WPF.FlexGrid.GridPanel" /> that contains the range.
/// </param>
/// <param name="rng">
/// <see cref="T:C1.WPF.FlexGrid.CellRange" /> represented by this object.
/// </param>
public CustomAggregateEventArgs(GridPanel panel, CellRange rng)
: base(panel, rng)
{
}
protected override IEnumerable<Parse> MethodCells(CellRange theCells)
{
return theCells.Cells.Alternate();
}
protected override IEnumerable <Parse> ParameterCells(CellRange theCells)
{
return(theCells.Cells.Skip(1));
}
public void func_begin_update(CellRange viz)
{
}
protected override IEnumerable<Parse> ParameterCells(CellRange theCells)
{
return theCells.Cells.From(1);
}
protected abstract IEnumerable<Parse> MethodCells(CellRange theCells);
/// <summary>
/// 특정 Cell 의 Style 변경
/// </summary>
/// <param name="styleType"></param>
/// <param name="cellRange"></param>
public static void x_SetCellStyle(this C1FlexGrid flexGrid, StyleType styleType, CellRange cellRange)
{
CellStyle style = flexGrid.x_GetStyle(styleType);
cellRange.Style = style;
}
protected override IEnumerable <Parse> MethodCells(CellRange theCells)
{
return(theCells.Cells.Take(1));
}
private bool LoadDataFromExcel(string filePath)
{
try
{
aConfig.confgStationList = new List <sConfig_Station>();
string strDir = System.Environment.CurrentDirectory;
string strDirName = strDir + "\\" + filePath;
Workbook workbook = new Workbook();
workbook.LoadFromFile(@strDirName);
int nIndex = 1;
Worksheet sheetConfig = workbook.Worksheets["软件配置"];
// ats IP PORT
CellRange atsIP1 = sheetConfig[2, nIndex++];
aConfig.atsServerIP1 = atsIP1.DisplayedText;
CellRange atsPort1 = sheetConfig[2, nIndex++];
aConfig.atsServerPort1 = int.Parse(atsPort1.DisplayedText);
CellRange atsIP2 = sheetConfig[2, nIndex++];
aConfig.atsServerIP2 = atsIP2.DisplayedText;
CellRange atsPort2 = sheetConfig[2, nIndex++];
aConfig.atsServerPort2 = int.Parse(atsPort2.DisplayedText);
// pis IP PORT
CellRange pisIP1 = sheetConfig[2, nIndex++];
aConfig.pisServerIP1 = pisIP1.DisplayedText;
CellRange pisPort1 = sheetConfig[2, nIndex++];
aConfig.pisServerPort1 = int.Parse(pisPort1.DisplayedText);
CellRange pisIP2 = sheetConfig[2, nIndex++];
aConfig.pisServerIP2 = pisIP2.DisplayedText;
CellRange pisPort2 = sheetConfig[2, nIndex++];
aConfig.pisServerPort2 = int.Parse(pisPort2.DisplayedText);
// multicast IP PORT
CellRange multicastIP = sheetConfig[2, nIndex++];
aConfig.sendIP = multicastIP.DisplayedText;
CellRange multicastPort = sheetConfig[2, nIndex++];
aConfig.sendPort = int.Parse(multicastPort.DisplayedText);
// pcc 预留
CellRange pccIP1 = sheetConfig[2, nIndex++];
aConfig.pccServerIP1 = pccIP1.DisplayedText;
CellRange pccPort1 = sheetConfig[2, nIndex++];
aConfig.pccServerPort1 = int.Parse(pccPort1.DisplayedText);
CellRange pccIP2 = sheetConfig[2, nIndex++];
aConfig.pccServerIP2 = pccIP2.DisplayedText;
CellRange pccPort2 = sheetConfig[2, nIndex++];
aConfig.pccServerPort2 = int.Parse(pccPort2.DisplayedText);
// 中英文切换时间（单位秒）
CellRange switchTime = sheetConfig[2, nIndex++];
aConfig.switchBetweenChineseAndEnglis = int.Parse(switchTime.DisplayedText);
// 即将到站显示条件（单位秒）
CellRange arriveTime = sheetConfig[2, nIndex++];
aConfig.arrivingShow = int.Parse(arriveTime.DisplayedText);
// 即将到站英文内容
CellRange arriveComingEng = sheetConfig[2, nIndex++];
aConfig.arrivingComingEngValue = arriveComingEng.DisplayedText;
// 即将到站中文内容
CellRange arriveComingCh = sheetConfig[2, nIndex++];
aConfig.arrivingComingChValue = arriveComingCh.DisplayedText;
// 到站英文内容
CellRange arriveEng = sheetConfig[2, nIndex++];
aConfig.arriveEngValue = arriveEng.DisplayedText;
// 到站中文内容
CellRange arriveCh = sheetConfig[2, nIndex++];
aConfig.arriveChValue = arriveCh.DisplayedText;
// 列车跳站英文内容
CellRange skipStationEng = sheetConfig[2, nIndex++];
aConfig.skipStationEngValue = skipStationEng.DisplayedText;
// 列车跳站中文内容
CellRange skipStationCh = sheetConfig[2, nIndex++];
aConfig.skipStaionChValue = skipStationCh.DisplayedText;
// 列车到站清空标志
CellRange clearTime = sheetConfig[2, nIndex++];
aConfig.clearShow = int.Parse(clearTime.DisplayedText);
sheetConfig = workbook.Worksheets["站点编码表"];
for (int i = 1; i < sheetConfig.Rows.Length; i++)
{
sConfig_Station aStation = new sConfig_Station();
aStation.stationName = new List <string>();
aStation.destCode = new List <string>();
CellRange cellData = sheetConfig[i + 1, 2];
aStation.stationCode = cellData.DisplayedText;
cellData = sheetConfig[i + 1, 3];
aStation.stationName.Add(cellData.DisplayedText);
cellData = sheetConfig[i + 1, 4];
aStation.stationName.Add(cellData.DisplayedText);
cellData = sheetConfig[i + 1, 5];
aStation.stationName.Add(cellData.DisplayedText);
cellData = sheetConfig[i + 1, 6];
aStation.stationName.Add(cellData.DisplayedText);
cellData = sheetConfig[i + 1, 7];
aStation.stationName.Add(cellData.DisplayedText);
cellData = sheetConfig[i + 1, 8];
aStation.platformName = cellData.DisplayedText;
cellData = sheetConfig[i + 1, 9];
aStation.platformCode = int.Parse(cellData.DisplayedText);
cellData = sheetConfig[i + 1, 10];
aStation.destCode.Add(cellData.DisplayedText);
cellData = sheetConfig[i + 1, 11];
aStation.destCode.Add(cellData.DisplayedText);
cellData = sheetConfig[i + 1, 12];
aStation.destCode.Add(cellData.DisplayedText);
cellData = sheetConfig[i + 1, 13];
aStation.destCode.Add(cellData.DisplayedText);
cellData = sheetConfig[i + 1, 14];
aStation.destCode.Add(cellData.DisplayedText);
cellData = sheetConfig[i + 1, 15];
aStation.destCode.Add(cellData.DisplayedText);
aConfig.confgStationList.Add(aStation);
}
return(true);
}
catch (Exception err)
{
Console.WriteLine("数据绑定Excel失败!:" + err.ToString());
return(false);
}
}