Пример #1
0
        public void SetArrayFormula(int sheet, int rowFirst, int rowLast, short colFirst, short colLast, IExcelFormula arrayFormula)
        {
            if (arrayFormula == null)
            {
                throw new ArgumentNullException("arrayFormula");
            }
            if (!arrayFormula.IsArrayFormula)
            {
                throw new ArgumentException(ResourceHelper.GetResourceString("arrayFormulaError"));
            }
            IExcelWorksheet worksheet = this._workbook.Worksheets[sheet];
            ExcelCellRange  range     = new ExcelCellRange {
                Row        = rowFirst,
                RowSpan    = (rowLast - rowFirst) + 1,
                Column     = colFirst,
                ColumnSpan = (colLast - colFirst) + 1
            };

            for (int i = rowFirst; i <= rowLast; i++)
            {
                for (int j = colFirst; j <= colLast; j++)
                {
                    IExcelCell cell = worksheet.GetCell(i, j, true);
                    cell.CellFormula = arrayFormula;
                    cell.CellFormula.ArrayFormulaRange = range;
                    cell.IsArrayFormula = true;
                    cell.CellType       = CellType.Array;
                }
            }
        }
Пример #2
0
 /// <summary>
 /// 合并单元格
 /// </summary>
 /// <param name="cell">单元格</param>
 /// <returns></returns>
 public IExcel MergeCell(IExcelCell cell)
 {
     if (cell.NeedMerge)
     {
         MergeCell(cell.RowIndex, cell.EndRowIndex, cell.ColumnIndex, cell.EndColumnIndex);
     }
     return(this);
 }
Пример #3
0
        /// <summary>
        /// 为下方受RowSpan影响的单元行添加占位单元格
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <param name="rowIndex">行索引</param>
        private void AddPlaceholderCell(IExcelCell cell, int rowIndex)
        {
            var row = CreateRow(rowIndex);

            row.Add(new NullCell()
            {
                ColumnIndex = cell.ColumnIndex, ColumnSpan = cell.ColumnSpan
            });
        }
Пример #4
0
 /// <summary>
 /// 设置列索引
 /// </summary>
 /// <param name="cell">单元格</param>
 private void SetColumnIndex(IExcelCell cell)
 {
     if (cell.ColumnIndex > 0)
     {
         _indexManager.AddIndex(cell.ColumnIndex, cell.ColumnSpan);
         return;
     }
     cell.ColumnIndex = _indexManager.GetIndex(cell.ColumnSpan);
 }
Пример #5
0
 /// <summary>
 /// 添加单元格
 /// </summary>
 /// <param name="cell">单元格</param>
 public void Add(IExcelCell cell)
 {
     if (cell == null)
     {
         return;
     }
     cell.Row = this;
     SetColumnIndex(cell);
     Cells.Add(cell);
 }
Пример #6
0
 /// <summary>
 /// 创建单元格
 /// </summary>
 /// <param name="cell">单元格</param>
 /// <returns></returns>
 public IExcel CreateCell(IExcelCell cell)
 {
     if (cell.IsNull())
     {
         return(this);
     }
     _cell = GetOrCreateCell(_row, cell.ColumnIndex);
     SetCellValue(cell.Value);
     MergeCell(cell);
     return(this);
 }
Пример #7
0
        public bool SetCellValue(short sheet, int row, int column, object value)
        {
            IExcelCell cell = this._workbook.Worksheets[sheet].GetCell(row, column, true);

            if (cell != null)
            {
                cell.Value = value;
                return(true);
            }
            return(false);
        }
Пример #8
0
 /// <summary>
 /// 添加单元格
 /// </summary>
 /// <param name="row">单元行</param>
 /// <param name="cell">单元格</param>
 /// <param name="rowIndex">行索引</param>
 private void AddCell(IExcelRow row, IExcelCell cell, int rowIndex)
 {
     row.Add(cell);
     if (cell.RowSpan <= 1)
     {
         return;
     }
     for (int i = 1; i < cell.RowSpan; i++)
     {
         AddPlaceholderCell(cell, rowIndex + i);
     }
 }
Пример #9
0
        /// <summary>
        /// Gets the cell at the specified row and column.
        /// </summary>
        /// <param name="row">The zero-based row index of the cell</param>
        /// <param name="column">The zero-based column index of the cell.</param>
        /// <param name="create">If set to <see langword="true" />. It will create the cell if
        /// the cell is null at the specified row and column when the row and column is in the valid range</param>
        /// <returns>
        /// An <see cref="T:Dt.Xls.IExcelCell" /> instance represents the cell at he specified location
        /// </returns>
        public IExcelCell GetCell(int row, int column, bool create = true)
        {
            Vector <IExcelCell> vector = this._dataTable1[row];

            if (vector != null)
            {
                IExcelCell cell = vector[column];
                if (cell == null)
                {
                    if ((!create || (column < 0)) || (column >= this._columnCount))
                    {
                        return(null);
                    }
                    cell           = new ExcelCell(this, row, column);
                    vector[column] = cell;
                    if (this.ActualColumnCount <= column)
                    {
                        this.ActualColumnCount = column + 1;
                    }
                    if (this.ActualRowCount <= row)
                    {
                        this.ActualRowCount = row + 1;
                    }
                }
                return(cell);
            }
            if (((!create || (column < 0)) || ((column >= this._columnCount) || (row < 0))) || (row >= this._rowCount))
            {
                return(null);
            }
            Vector <IExcelCell> vector2 = new Vector <IExcelCell>();
            ExcelCell           cell2   = new ExcelCell(this, row, column);

            vector2[column]       = cell2;
            this._dataTable1[row] = vector2;
            if (this._rows1[row] == null)
            {
                ExcelRow row2 = new ExcelRow(this)
                {
                    Index = row
                };
                this._rows1[row] = row2;
            }
            if (this.ActualColumnCount <= column)
            {
                this.ActualColumnCount = column + 1;
            }
            if (this.ActualRowCount <= row)
            {
                this.ActualRowCount = row + 1;
            }
            return(cell2);
        }
Пример #10
0
        public bool SetCellStyle(short sheet, int row, int column, int ixf, CellType type)
        {
            IExcelCell cell = this._workbook.Worksheets[sheet].GetCell(row, column, true);

            if (cell != null)
            {
                cell.CellType = type;
                cell.SetFormatId(ixf);
                return(true);
            }
            return(false);
        }
Пример #11
0
 public object GetCellValue(short sheet, int row, int column)
 {
     if (this.ValidateWorkbook(sheet))
     {
         IExcelCell cell = this._workbook.Worksheets[sheet].GetCell(row, column, false);
         if (cell != null)
         {
             return(cell.Value);
         }
     }
     return(null);
 }
Пример #12
0
        /// <summary>
        /// Добавить круговую диаграмму.
        /// </summary>
        /// <param name="position">Положение левого верхнего угла.</param>
        /// <param name="name">Название.</param>
        /// <param name="size">Размер в пикселах.</param>
        /// <param name="valuesRange">Диапазон значений.</param>
        /// <param name="axesRange">Диапазон названий.</param>
        public void AddPieChart(IExcelCell position,
                                string name,
                                int size,
                                IExcelRange valuesRange,
                                IExcelRange axesRange)
        {
            var chart = (ExcelPieChart)_worksheet.Drawings.AddChart(name, eChartType.PieExploded3D);

            chart.SetPosition(position.Row, 0, position.Column, 0);
            chart.SetSize(size, size);
            chart.Series.Add(valuesRange.GetAddress(), axesRange.GetAddress());
            chart.Title.Text = name;
            ApplyPieChartStyles(chart);
        }
Пример #13
0
        private string SafeGetColumn(IExcelRow row, int columnIndex)
        {
            if (row == null)
            {
                return(string.Empty);
            }
            IExcelCell cell = row.GetCell(columnIndex);

            if (cell != null && cell.value != null)
            {
                return(cell.value);
            }
            return(string.Empty);
        }
Пример #14
0
        public bool SetCellFormula(short sheet, int row, int column, IExcelFormula cellFormula)
        {
            if (cellFormula == null)
            {
                throw new ArgumentNullException("cellFormula");
            }
            IExcelCell cell = this._workbook.Worksheets[sheet].GetCell(row, column, true);

            if (cell != null)
            {
                cell.CellFormula = cellFormula;
                return(true);
            }
            return(false);
        }
Пример #15
0
        public void CompareCells(int columnCount, IExcelSheet left, IExcelSheet right)
        {
            _isDifferent = false;
            _cellStatus  = new bool[columnCount];
            IExcelRow leftRow  = left.GetRow(_leftRowIndex);
            IExcelRow rightRow = right.GetRow(_rightRowIndex);

            if (leftRow == null || rightRow == null)
            {
                for (int i = 0; i < columnCount; i++)
                {
                    _cellStatus[i] = true;
                }
            }
            else
            {
                for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
                {
                    IExcelCell selfCell  = leftRow.GetCell(columnIndex);
                    IExcelCell otherCell = rightRow.GetCell(columnIndex);
                    if ((selfCell == null && otherCell != null) || (selfCell != null && otherCell == null))
                    {
                        _cellStatus[columnIndex] = true;
                    }
                    else
                    {
                        if (selfCell == null && otherCell == null)
                        {
                            _cellStatus[columnIndex] = false;
                        }
                        else
                        {
                            _cellStatus[columnIndex] = string.Compare(selfCell.GetContent(), otherCell.GetContent()) != 0;
                        }
                    }
                }
            }

            for (int i = 0; i < _cellStatus.Length; i++)
            {
                if (_cellStatus[i])
                {
                    _isDifferent = true;
                    break;
                }
            }
        }
Пример #16
0
        /// <summary>
        /// Sets the value of the cell.
        /// </summary>
        /// <param name="row">The zero-based row index used to locate the cell</param>
        /// <param name="column">The zero-base column index used to locate the cell</param>
        /// <returns>The value of the cell</returns>
        public object GetValue(int row, int column)
        {
            IExcelCell cell = this.GetCell(row, column, true);

            if (cell != null)
            {
                return(cell.Value);
            }
            if ((row < 0) || (row >= this.RowCount))
            {
                throw new ArgumentOutOfRangeException("row");
            }
            if ((column < 0) || (column >= this.ColumnCount))
            {
                throw new ArgumentOutOfRangeException("column");
            }
            return(null);
        }
Пример #17
0
        public VRow(VSheet sheet, int rowIndex, IExcelRow referenceRow)
        {
            _sheet    = sheet;
            _rowIndex = rowIndex;

            if (referenceRow != null)
            {
                if (referenceRow is VRow)
                {
                    VRow row = referenceRow as VRow;
                    _realRowIndex   = row.realRowIndex;
                    _targetRowIndex = row.targetRowIndex;
                }
                else
                {
                    ExcelRow row = referenceRow as ExcelRow;
                    _realRowIndex = rowIndex;
                }

                for (int columnIndex = 0; columnIndex < sheet.columnCount; columnIndex++)
                {
                    IExcelCell cell    = referenceRow.GetCell(columnIndex);
                    IExcelCell newCell = null;
                    if (cell == null)
                    {
                        newCell = new ExcelCell(_rowIndex, columnIndex);
                    }
                    else
                    {
                        newCell = new ExcelCell(_rowIndex, columnIndex, cell.cellType, cell.value);
                    }

                    _columns.Add(newCell);
                }
            }
            else
            {
                _realRowIndex = -1;
                for (int columnIndex = 0; columnIndex < sheet.columnCount; columnIndex++)
                {
                    _columns.Add(new ExcelCell(_rowIndex, columnIndex));
                }
            }
        }
Пример #18
0
        /// <summary>
        /// Gets the non empty cells.
        /// </summary>
        /// <param name="row">The zero-based row index</param>
        /// <returns>
        /// A collection of <see cref="T:Dt.Xls.IExcelCell" /> instances represents the non-empty cells in the specified row
        /// </returns>
        public List <IExcelCell> GetNonEmptyCells(int row)
        {
            List <IExcelCell>   list   = new List <IExcelCell>();
            Vector <IExcelCell> vector = this._dataTable1[row];

            if (vector != null)
            {
                int count = vector.Count;
                for (int i = 0; i < count; i++)
                {
                    IExcelCell item = vector.GetItem(i);
                    if (item != null)
                    {
                        list.Add(item);
                    }
                }
            }
            return(list);
        }
Пример #19
0
        public bool SetCell(short sheet, int row, int column, object value, CellType type, int ixf, IExcelFormula cellFormula)
        {
            IExcelCell cell = this._workbook.Worksheets[sheet].GetCell(row, column, true);

            if (cell == null)
            {
                return(false);
            }
            if (value != null)
            {
                cell.Value = value;
            }
            cell.SetFormatId(ixf);
            cell.CellType = type;
            if ((cellFormula != null) && ((cell.CellFormula == null) || !cell.CellFormula.IsArrayFormula))
            {
                cell.CellFormula = cellFormula;
            }
            return(true);
        }
Пример #20
0
        public bool SetCellHyperLink(short sheet, int row, int column, IExcelHyperLink hyperLink)
        {
            if (hyperLink == null)
            {
                throw new ArgumentNullException("hyperLink");
            }
            IExcelWorksheet worksheet = this._workbook.Worksheets[sheet];
            IExcelCell      cell      = worksheet[row, column];

            if (cell == null)
            {
                return(false);
            }
            if (string.IsNullOrWhiteSpace(hyperLink.Description))
            {
                hyperLink.Description = cell.Value.ToString();
            }
            cell.Hyperlink = hyperLink;
            return(true);
        }
Пример #21
0
        /// <summary>
        /// Sets the value of the cell.
        /// </summary>
        /// <param name="row">The zero-based row index used to locate the cell</param>
        /// <param name="column">The zero-base column index used to locate the cell</param>
        /// <param name="value">The value of the cell</param>
        public void SetValue(int row, int column, object value)
        {
            IExcelCell cell = this.GetCell(row, column, true);

            if (cell != null)
            {
                cell.Value = value;
            }
            else
            {
                if ((row < 0) || (row >= this.RowCount))
                {
                    throw new ArgumentOutOfRangeException("row");
                }
                if ((column < 0) || (column >= this.ColumnCount))
                {
                    throw new ArgumentOutOfRangeException("column");
                }
            }
        }
Пример #22
0
        private void Copy2Left()
        {
            if (rightGrid.SelectedRows.Count > 0)
            {
                int        firstRowIndex = rightGrid.FirstDisplayedScrollingRowIndex;
                DataTable  table         = leftGrid.DataSource as DataTable;
                List <int> selectionList = new List <int>();
                for (int selectionIndex = 0; selectionIndex < rightGrid.SelectedRows.Count; selectionIndex++)
                {
                    int rowIndex = rightGrid.SelectedRows[selectionIndex].Index;
                    currentSheet.Copy2Left(rowIndex);
                    VRow row = currentSheet.left.GetRow(rowIndex) as VRow;

                    DataRow dataRow = table.Rows[rowIndex];
                    dataRow.BeginEdit();
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        IExcelCell cell = row.GetCell(i);
                        dataRow[i] = cell != null ? cell.value : string.Empty;
                    }
                    dataRow.EndEdit();
                    selectionList.Add(rowIndex);
                }
                table.AcceptChanges();

                rightGrid.FirstDisplayedScrollingRowIndex = firstRowIndex;

                stopUpdate = true;
                leftGrid.ClearSelection();
                for (int i = 0; i < selectionList.Count; i++)
                {
                    rightGrid.Rows[selectionList[i]].Cells[0].Selected = true;
                    leftGrid.Rows[selectionList[i]].Cells[0].Selected  = true;
                }
                stopUpdate = false;
            }
        }
Пример #23
0
        public void CopyFrom(IExcelRow other)
        {
            VRow row = other as VRow;

            _targetRowIndex = row.realRowIndex;
            _columns.Clear();
            _changed = true;

            for (int columnIndex = 0; columnIndex < sheet.columnCount; columnIndex++)
            {
                IExcelCell cell    = row.GetCell(columnIndex);
                IExcelCell newCell = null;
                if (cell == null)
                {
                    newCell = new ExcelCell(_rowIndex, columnIndex);
                }
                else
                {
                    newCell = new ExcelCell(_rowIndex, columnIndex, cell.cellType, cell.value);
                }

                _columns.Add(newCell);
            }
        }
Пример #24
0
 /// <summary>
 /// Вернуть диапазон ячеек.
 /// </summary>
 /// <param name="from">От ячейки.</param>
 /// <param name="to">До ячейки.</param>
 /// <returns>IExcelRange.</returns>
 public IExcelRange GetRange(IExcelCell from, IExcelCell to)
 => new EpPlusRange(_worksheet.Cells[from.Row, from.Column, to.Row, to.Column]);
Пример #25
0
        public void Save(ISheet sheet)
        {
            int realRowIndex = 0;

            for (int rowIndex = 0; rowIndex < _rows.Count; rowIndex++)
            {
                VRow row = _rows[rowIndex] as VRow;
                if (row.realRowIndex != -1 || row.targetRowIndex != -1)
                {
                    IRow sheetRow = null;
                    if (row.realRowIndex != -1 && row.changed && row.targetRowIndex == -1)
                    {
                        sheetRow = sheet.GetRow(realRowIndex);
                        if (sheetRow != null)
                        {
                            int lastRowIndex = sheet.LastRowNum;
                            sheet.ShiftRows(realRowIndex + 1, sheet.LastRowNum, -1, true, false);
                        }
                        continue;
                    }
                    if (realRowIndex <= sheet.LastRowNum && row.realRowIndex != -1)
                    {
                        sheetRow = sheet.GetRow(realRowIndex);
                    }
                    else
                    {
                        if (realRowIndex <= sheet.LastRowNum)
                        {
                            sheet.ShiftRows(realRowIndex, sheet.LastRowNum, 1, true, false);
                        }
                        sheetRow = sheet.CreateRow(realRowIndex);
                        //sheetRow = sheet.GetRow(realRowIndex);
                    }

                    int lastCellIndex = sheetRow.LastCellNum;
                    for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
                    {
                        IExcelCell cell      = row.GetCell(columnIndex);
                        ICell      sheetCell = sheetRow.GetCell(columnIndex);
                        if (cell != null && cell.value != null)
                        {
                            if (sheetCell == null)
                            {
                                sheetCell = sheetRow.CreateCell(columnIndex);
                            }
                            if (sheetCell != null)
                            {
                                sheetCell.SetCellValue(cell.value);
                            }
                        }
                        else
                        {
                            if (sheetCell != null)
                            {
                                sheetRow.RemoveCell(sheetCell);
                            }
                        }
                    }
                    realRowIndex++;
                }
            }
        }
Пример #26
0
 public ExcelCell(IExcelCell excelCell)
 {
     internalCell = excelCell;
 }