// ** ctor
public ExcelRow(ExcelRow styleRow)
{
IsReadOnly = false;
if (styleRow != null && styleRow.Grid != null)
{
foreach (var c in styleRow.Grid.Columns)
{
var cs = styleRow.GetCellStyle(c);
if (cs != null)
{
this.SetCellStyle(c, cs.Clone());
}
}
}
}
const string DEFAULT_FORMAT = "#,##0.######"; // set default precision to 6 digits
// ** ctor
public ExcelRow(ExcelRow styleRow)
{
IsReadOnly = false;
if (styleRow != null && styleRow.Grid != null)
{
foreach (var c in styleRow.Grid.Columns)
{
var cs = styleRow.GetCellStyle(c);
if (cs != null)
{
this.SetCellStyle(c, cs.Clone());
}
}
}
}
/// <summary>
/// Loads the content of an XLSheet into a C1FlexGrid.
/// </summary>
public static void Load(XLSheet sheet, C1FlexGrid flex)
{
// clear style cache if this is a new book
if (sheet.Book != _lastBook)
{
_cellStyles.Clear();
_excelStyles.Clear();
_lastBook = sheet.Book;
}
// set default parameters
flex.FontFamily = new FontFamily(sheet.Book.DefaultFont.FontName);
flex.FontSize = PointsToPixels(sheet.Book.DefaultFont.FontSize);
flex.Rows.DefaultSize = TwipsToPixels(sheet.DefaultRowHeight);
flex.Columns.DefaultSize = TwipsToPixels(sheet.DefaultColumnWidth);
flex.IsReadOnly = sheet.Locked;
flex.GridLinesVisibility = sheet.ShowGridLines
? GridLinesVisibility.All
: GridLinesVisibility.None;
//flex.GridLinesBrush = sheet.GridColor;
flex.HeadersVisibility = sheet.ShowHeaders
? HeadersVisibility.All
: HeadersVisibility.None;
flex.GroupRowPosition = sheet.OutlinesBelow
? GroupRowPosition.BelowData
: GroupRowPosition.AboveData;
// add columns
flex.Columns.Clear();
foreach (XLColumn c in sheet.Columns)
{
// create column, give it a unique name so undo/ColumnLayout work
var col = new Column();
col.ColumnName = col.GetHashCode().ToString("x0");
// set size and visibility
if (c.Width > -1)
{
col.Width = new GridLength(TwipsToPixels(c.Width));
}
col.Visible = c.Visible;
// set style
if (c.Style != null)
{
col.CellStyle = GetCellStyle(c.Style);
}
// and add to the grid
flex.Columns.Add(col);
}
// add rows
flex.Rows.Clear();
foreach (XLRow r in sheet.Rows)
{
var row = new ExcelRow();
if (r.Height > -1)
{
row.Height = TwipsToPixels(r.Height);
}
if (r.Style != null)
{
row.CellStyle = GetCellStyle(r.Style);
}
row.Level = r.OutlineLevel;
row.Visible = r.Visible;
flex.Rows.Add(row);
}
// add cells
for (int r = 0; r < flex.Rows.Count; r++)
{
for (int c = 0; c < flex.Columns.Count; c++)
{
var cell = sheet[r, c];
if (cell != null)
{
if (!string.IsNullOrEmpty(cell.Formula))
{
// save formula
var formula = cell.Formula.Trim();
if (!formula.StartsWith("="))
{
formula = string.Format("={0}", formula);
}
flex[r, c] = formula;
}
else if (cell.Value != null)
{
// save value
flex[r, c] = cell.Value;
}
if (cell.Style != null)
{
// save style
var row = flex.Rows[r] as ExcelRow;
var col = flex.Columns[c];
row.SetCellStyle(col, GetCellStyle(cell.Style));
}
}
}
}
// at least 20 columns, 50 rows
while (flex.Columns.Count < 20)
{
flex.Columns.Add(new Column());
}
while (flex.Rows.Count < 50)
{
flex.Rows.Add(new ExcelRow());
}
// load merged cells
var xmm = flex.MergeManager as ExcelMergeManager;
if (xmm == null)
{
xmm = new ExcelMergeManager();
}
xmm.GetMergedRanges(sheet);
// freeze rows/columns
flex.Rows.Frozen = sheet.Rows.Frozen;
flex.Columns.Frozen = sheet.Columns.Frozen;
// update selection
if (sheet.SelectedCells != null && sheet.SelectedCells.Count > 0)
{
// review: using the last one seems to work, but why?
var sel = sheet.SelectedCells[sheet.SelectedCells.Count - 1];
flex.Select(sel.RowFrom, sel.ColumnFrom, sel.RowTo, sel.ColumnTo, false);
}
else
{
flex.Select(0, 0);
}
}
//---------------------------------------------------------------------------------
#region ** object model
/// <summary>
/// Loads the content of an XLSheet into a C1FlexGrid.
/// </summary>
public static void Load(XLSheet sheet, C1FlexGrid flex)
{
// clear style cache if this is a new book
if (sheet.Book != _lastBook)
{
_cellStyles.Clear();
_excelStyles.Clear();
_lastBook = sheet.Book;
}
// set default parameters
flex.FontFamily = new FontFamily(sheet.Book.DefaultFont.FontName);
flex.FontSize = PointsToPixels(sheet.Book.DefaultFont.FontSize);
flex.Rows.DefaultSize = TwipsToPixels(sheet.DefaultRowHeight);
flex.Columns.DefaultSize = TwipsToPixels(sheet.DefaultColumnWidth);
flex.IsReadOnly = sheet.Locked;
flex.GridLinesVisibility = sheet.ShowGridLines
? GridLinesVisibility.All
: GridLinesVisibility.None;
//flex.GridLinesBrush = sheet.GridColor;
flex.HeadersVisibility = sheet.ShowHeaders
? HeadersVisibility.All
: HeadersVisibility.None;
flex.GroupRowPosition = sheet.OutlinesBelow
? GroupRowPosition.BelowData
: GroupRowPosition.AboveData;
// add columns
flex.Columns.Clear();
foreach (XLColumn c in sheet.Columns)
{
// create column, give it a unique name so undo/ColumnLayout work
var col = new Column();
col.ColumnName = col.GetHashCode().ToString("x0");
// set size and visibility
if (c.Width > -1)
{
col.Width = new GridLength(TwipsToPixels(c.Width));
}
col.Visible = c.Visible;
// set style
if (c.Style != null)
{
col.CellStyle = GetCellStyle(c.Style);
}
// and add to the grid
flex.Columns.Add(col);
}
// add rows
flex.Rows.Clear();
foreach (XLRow r in sheet.Rows)
{
var row = new ExcelRow();
if (r.Height > -1)
{
row.Height = TwipsToPixels(r.Height);
}
if (r.Style != null)
{
row.CellStyle = GetCellStyle(r.Style);
}
row.Level = r.OutlineLevel;
row.Visible = r.Visible;
flex.Rows.Add(row);
}
// add cells
for (int r = 0; r < flex.Rows.Count; r++)
{
for (int c = 0; c < flex.Columns.Count; c++)
{
var cell = sheet[r, c];
if (cell != null)
{
if (!string.IsNullOrEmpty(cell.Formula))
{
// save formula
var formula = cell.Formula.Trim();
if (!formula.StartsWith("="))
{
formula = string.Format("={0}", formula);
}
flex[r, c] = formula;
}
else if (cell.Value != null)
{
// save value
flex[r, c] = cell.Value;
}
if (cell.Style != null)
{
// save style
var row = flex.Rows[r] as ExcelRow;
var col = flex.Columns[c];
row.SetCellStyle(col, GetCellStyle(cell.Style));
}
}
}
}
// at least 20 columns, 50 rows
while (flex.Columns.Count < 20)
{
flex.Columns.Add(new Column());
}
while (flex.Rows.Count < 50)
{
flex.Rows.Add(new ExcelRow());
}
// load merged cells
var xmm = flex.MergeManager as ExcelMergeManager;
if (xmm == null)
{
xmm = new ExcelMergeManager();
}
xmm.GetMergedRanges(sheet);
// freeze rows/columns
flex.Rows.Frozen = sheet.Rows.Frozen;
flex.Columns.Frozen = sheet.Columns.Frozen;
// update selection
if (sheet.SelectedCells != null && sheet.SelectedCells.Count > 0)
{
// review: using the last one seems to work, but why?
var sel = sheet.SelectedCells[sheet.SelectedCells.Count - 1];
flex.Select(sel.RowFrom, sel.ColumnFrom, sel.RowTo, sel.ColumnTo, false);
}
else
{
flex.Select(0, 0);
}
}