/// <summary>
/// Customization for copying the styles.
/// </summary>
/// <param name="rangelist">Range List</param>
private void CopyStylesAsBiffToClipBoard(GridRangeInfoList rangelist)
{
GridExcelConverterControl gecc = new GridExcelConverterControl();
ExcelEngine engine = CreateEngine();
IWorkbook book = engine.Excel.Workbooks.Create(1);
IWorksheet sheet = book.Worksheets[0];
foreach (GridRangeInfo range in rangelist)
{
range.ExpandRange(1, 1, this.gridControl1.RowCount, this.gridControl1.ColCount);
for (int iRow = range.Top; iRow <= range.Bottom; iRow++)
{
for (int iColumn = range.Left; iColumn <= range.Right; iColumn++)
{
GridStyleInfo gridCell = this.gridControl1[iRow, iColumn];
if (gridCell.IsEmpty)
{
continue;
}
gecc.GridCellToExcel(this.gridControl1.Model, iRow, iColumn, sheet.Range[iRow, iColumn]);
}
}
}
book.CopyToClipboard();
book.Close();
engine.Dispose();
}
/// <summary>
/// Used to load the all worksheets and assign those sheets in grid models.
/// </summary>
/// <param name="Workbook">Exel work book that needs to be imported.</param>
public void LoadWorkbook(TabBarSplitterControl formTabBarSplitterControl, IWorkbook Workbook)
{
tabBarSplitterControl = formTabBarSplitterControl;
formTabBarSplitterControl.TabBarPages.Clear();
workbook = Workbook;
GridExcelConverterControl extensions = new GridExcelConverterControl();
gridModelCollection = extensions.ExcelToVirtualGrid(Workbook);
for (int i = 0; i < Workbook.Worksheets.Count; i++)
{
GridControl grid = new GridControl();
//Used to calculate the formula values.
Workbook.Worksheets[i].EnableSheetCalculations();
#region Adding Tab pages
TabBarPage tab = new TabBarPage();
tab.Text = Workbook.Worksheets[i].Name;
tab.Controls.Add(grid);
if (Workbook.Worksheets[i].Visibility != WorksheetVisibility.Visible)
{
tab.Visible = false;
}
#endregion
#region GridModel settings
grid.Model = gridModelCollection[i];
#endregion
#region Grid customization
grid.ThemesEnabled = true;
GridMetroColors colors = new GridMetroColors();
colors.HeaderBottomBorderColor = Color.FromArgb(208, 208, 208);
colors.HeaderBottomBorderWeight = GridBottomBorderWeight.Thin;
grid.SetMetroStyle(colors);
grid.FloatCellsMode = GridFloatCellsMode.OnDemandCalculation;
grid.ExcelLikeAlignment = true;
grid.Model.Options.ExcelLikeCurrentCell = true;
grid.Model.Options.ExcelLikeSelectionFrame = true;
grid.Model.Options.EnterKeyBehavior = GridDirectionType.Down;
grid.ActivateCurrentCellBehavior = GridCellActivateAction.DblClickOnCell;
grid.Model.Options.WrapCell = false;
grid.Model.CommandStack.Enabled = true;
grid.Properties.ForceImmediateRepaint = true;
grid.Model.ColWidths[0] = 35;
grid.SmoothMouseWheelScrolling = true;
RegisterCellModel.GridCellType(grid, CustomCellTypes.LinkLabelCell);
#endregion
formTabBarSplitterControl.TabBarPages.Add(tab);
}
formTabBarSplitterControl.ActivePageIndex = 0;
foreach (GridModel gridModel in gridModelCollection)
{
gridModel.QueryCellInfo += new GridQueryCellInfoEventHandler(Model_QueryCellInfo);
gridModel.SaveCellInfo += new GridSaveCellInfoEventHandler(gridModel_SaveCellInfo);
}
}
/// <summary>
/// Customization for Pasting the data to grid
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void gridControl1_ClipboardCanPaste(object sender, GridCutPasteEventArgs e)
{
IDataObject dataObject = Clipboard.GetDataObject();
if (dataObject != null)
{
if (dataObject.GetDataPresent("Biff", true))
{
ExcelEngine xlEngine = new ExcelEngine();
IApplication app = xlEngine.Excel;
IWorkbooks workBook = app.Workbooks;
workBook.PasteWorkbook();
IWorkbook book = workBook[0];
IWorksheet worksheet = book.Worksheets[0];
IRange ur = worksheet.UsedRange;
IRange[] ranges = ur.Cells;//worksheet.UsedCells;
System.Text.StringBuilder str = new System.Text.StringBuilder();
int lastCol = ranges[0].Column, lastRow = ranges[0].Row;
int rc = ur.LastRow - ur.Row + 1;
int cc = ur.LastColumn - ur.Column + 1;
GridExcelConverterControl converter = new GridExcelConverterControl();
GridModel model = new GridModel();
model.ColCount = cc;
model.RowCount = rc;
ur.MoveTo(worksheet.Range[1, 1, rc, cc]);
ranges = worksheet.Range[1, 1, rc, cc].Cells;
foreach (IRange cell in ranges)
{
converter.ConvertExcelRangeToGrid(cell, model);
}
GridData data = new GridData();
data.InsertRows(1, rc);
data.InsertCols(1, cc);
for (int i = 1; i <= model.Data.RowCount; i++)
{
for (int j = 1; j <= model.ColCount; j++)
{
if (model.Data[i, j] != null)
{
data[i - 1, j - 1] = model.Data[i, j].Clone() as GridStyleInfoStore;
}
}
}
//Clipboard.Clear();
DataObject newDataObject = new DataObject(data);
Clipboard.SetDataObject(newDataObject, true);
xlEngine.ThrowNotSavedOnDestroy = false;
xlEngine.Dispose();
}
}
}
public ImportingHelper()
{
excelConverter = new GridExcelConverterControl();
}
