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