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;
}
}
}
/// <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);
}
/// <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
});
}
/// <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);
}
/// <summary>
/// 添加单元格
/// </summary>
/// <param name="cell">单元格</param>
public void Add(IExcelCell cell)
{
if (cell == null)
{
return;
}
cell.Row = this;
SetColumnIndex(cell);
Cells.Add(cell);
}
/// <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);
}
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);
}
/// <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);
}
}
/// <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);
}
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);
}
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);
}
/// <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);
}
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);
}
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);
}
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;
}
}
}
/// <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);
}
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));
}
}
}
/// <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);
}
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);
}
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);
}
/// <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");
}
}
}
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;
}
}
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);
}
}
/// <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]);
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++;
}
}
}
public ExcelCell(IExcelCell excelCell)
{
internalCell = excelCell;
}