Example #1
0
 // ** 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());
                    }
                }
            }
        }
Example #3
0
        /// <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);
            }
        }