//A method for copying a row and insert it:
//Copy an existing row and insert it
//We don't need to copy styles of a refRow because a CloneNode() or Clone() methods do it for us
public static Row CopyToLine(this Row refRow, uint rowIndex, SheetData sheetData)
{
uint newRowIndex;
var newRow = (Row)refRow.CloneNode(true);
// Loop through all the rows in the worksheet with higher row
// index values than the one you just added. For each one,
// increment the existing row index.
IEnumerable <Row> rows = sheetData.Descendants <Row>().Where(r => r.RowIndex.Value >= rowIndex);
foreach (Row row in rows)
{
newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);
foreach (Cell cell in row.Elements <Cell>())
{
// Update the references for reserved cells.
string cellReference = cell.CellReference.Value;
cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
// Update the row index.
row.RowIndex = new UInt32Value(newRowIndex);
}
sheetData.InsertBefore(newRow, refRow);
return(newRow);
}
private void CopyRange(ref CellRangeRef sourceRange, SheetData sheetData, ref CellRef target)
{
CellRef source = sourceRange.Start;
CellRef offset = target.CalculateOffset(source);
var cellsToCopy = FindCellsByRange(sourceRange);
foreach (var rowGroup in cellsToCopy)
{
Row keyRow = rowGroup.Key;
Row targetRow = new Row()
{
RowIndex = (UInt32)(keyRow.RowIndex + offset.Row)
};
MoveCurrentRow((int)targetRow.RowIndex.Value);
sheetData.InsertBefore(targetRow, currentRow);
foreach (Cell cellToCopy in rowGroup)
{
Cell targetCell = (Cell)cellToCopy.Clone();
targetCell.CellReference = CellRef.OffsetIt(targetCell.CellReference, offset);
targetRow.Append(targetCell);
}
}
}
private static bool CreateOrUpdateRowAt(SheetData sheetData, Row newRow, uint rowNo)
{
bool res = false;
//create row and cell. append or insert.
if (sheetData != null && newRow != null)
{
//是否存在row,1.存在,删除.2.现在是否存在大于row,存在,在前插入.不存在.直接附加.
var rows = sheetData.Elements <Row>().Where(x => x.RowIndex == rowNo);
if (rows.Count() > 0)
{
sheetData.RemoveChild(rows.First());
}
var biggerrows = sheetData.Elements <Row>().Where(x => x.RowIndex > rowNo);
if (biggerrows.Count() <= 0)
{
sheetData.Append(newRow);//todo:装载数据7/10的时间耗费在这里。需要优化！如果是大数据插入，应该创建大量row，再使用一次append或其他插入函数。
}
else
{
sheetData.InsertBefore(newRow, biggerrows.First());
}
res = true;
}
return(res);
}
//根据行索引值获取列Row
private Row getRow(int rowIndex)
{
int rowcount = sheetData.Elements <Row>().Count();
Row row = sheetData.Elements <Row>().FirstOrDefault(r => r != null && r.RowIndex != null && r.RowIndex == rowIndex);
if (row == null)
{
row = new Row()
{
RowIndex = (uint)rowIndex
};
Row lastRow = sheetData.Elements <Row>().LastOrDefault();
if ((lastRow != null && lastRow.RowIndex != null && lastRow.RowIndex < rowIndex) || lastRow == null)
{
sheetData.AppendChild(row);
}
else
{
Row refRow = sheetData.Elements <Row>().FirstOrDefault(r => r != null && r.RowIndex != null && r.RowIndex > rowIndex);
sheetData.InsertBefore(row, refRow);
}
}
return(row);
}
public void InsertRowAt(DataRow rowToInsert, DataColumnCollection columns, int refRow, WorksheetPart worksheetPart)
{
CellFormat format = new CellFormat();
format.BorderId = InsertBorder(GenerateBorder());
uint styleIndex = InsertCellFormat(_workbookPart, format);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
Row newRow = new Row {
RowIndex = Convert.ToUInt32(refRow)
};
foreach (DataColumn col in columns)
{
Cell cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(" \n" + rowToInsert[col].ToString() + " s")
};
cell.StyleIndex = styleIndex;
newRow.AppendChild(cell);
}
Row refrenceRow = null;//sheetData.Descendants<Row>().Where(x => x.RowIndex != null && x.RowIndex.Value == refRow).First();
IEnumerable <Row> rows = sheetData.Descendants <Row>().Where(x => x.RowIndex != null && x.RowIndex >= refRow);
foreach (Row row in rows)
{
if (row.Elements <Cell>().Any(x => x.CellReference != null))
{
if (refrenceRow == null)
{
refrenceRow = row;
}
uint newIndex = row.RowIndex.Value + 1;
foreach (Cell cell in row.Elements <Cell>())
{
string cellReference = cell.CellReference.Value;
cell.CellReference = cellReference.Replace(row.RowIndex.Value.ToString(), newIndex.ToString());
}
row.RowIndex = new UInt32Value(newIndex);
}
}
if (refrenceRow != null)
{
sheetData.InsertBefore(newRow, refrenceRow);
}
else
{
sheetData.Append(newRow);
}
worksheetPart.Worksheet.Save();
}
/// <summary>
/// Метод для получения или создания ячейки и вставки данных в эту ячейку
/// </summary>
/// <param name="columnName">Имя столбца в формате как в Excel [A-Z...]</param>
/// <param name="rowIndex">Индекс строки</param>
/// <param name="worksheetPart"></param>
/// <returns>Ячейка</returns>
private Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
string cellReference = columnName + rowIndex;
// Если рабочий лист не содержит строки с указанным индексом строки, то просто добавляем ее.
Row row;
if (sheetData.Elements <Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
// Обязательно необходимо соблюсти последовательность индексов
Row nextRow = sheetData.Elements <Row>().OrderBy(r => r.RowIndex).FirstOrDefault(r => r.RowIndex > rowIndex);
row = new Row()
{
RowIndex = rowIndex
};
sheetData.InsertBefore(row, nextRow);
}
// Если ячейки с указанным именем столбца нет, то добавляем.
if (row.Elements <Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return(row.Elements <Cell>().Where(c => c.CellReference.Value == cellReference).First());
}
else
{
// Ячейки должны располагаться в последовательном порядке согласно CellReference.
// Необходимо определить, куда вставить новую ячейку.
Cell refCell = null;
foreach (Cell cell in row.Elements <Cell>())
{
if (cell.CellReference.Value.Length == cellReference.Length)
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
}
Cell newCell = new Cell()
{
CellReference = cellReference
};
row.InsertBefore(newCell, refCell);
worksheet.Save();
return(newCell);
}
}
/// <summary>
/// Inserts new rows at index. It adds multiple rows and multiple columns for each row.
/// </summary>
/// <param name="rowIndex">Index of the row that will be the first of the inserted rows</param>
/// <param name="sheetData"></param>
/// <param name="howManyRows">How many rows to insert</param>
/// <param name="howManyColumns">How many columns to insert for each row</param>
private static void InsertNewRows(uint rowIndex, SheetData sheetData, int howManyRows, int howManyColumns)
{
Row RefRow = GetRow(sheetData.Parent as Worksheet, rowIndex);
var styleIndex = RefRow.Elements <Cell>().First().StyleIndex;
List <Row> RowsToAdd = new List <Row>();
for (int i = 0; i < howManyRows; ++i)
{
int newRowIndex = (int)rowIndex + i;
Row rowToAdd = new Row()
{
RowIndex = new UInt32Value((uint)newRowIndex)
};
for (uint cellColumnId = 0; cellColumnId <= howManyColumns; ++cellColumnId)
{
Cell cellToAdd = new Cell()
{
CellReference = new StringValue(GetExcelColumnName(cellColumnId) + newRowIndex),
CellFormula = new CellFormula(),
DataType = CellValues.String,
StyleIndex = styleIndex
};
rowToAdd.Append(cellToAdd);
}
RowsToAdd.Add(rowToAdd);
}
// Insert 1st row above given row index. Insert all others below that.
for (int i = 0; i < RowsToAdd.Count; ++i)
{
Row row = RowsToAdd[i];
if (i == 0)
{
sheetData.InsertBefore(row, RefRow);
}
else
{
sheetData.InsertAfter(row, RowsToAdd[i - 1]);
}
}
// Row that had previously row index of the 1st row inserted must now update its cell references and row index.
uint newIndexForFirstRow = RefRow.RowIndex.Value + (uint)howManyRows;
foreach (Cell cell in RefRow.Elements <Cell>())
{
cell.CellReference = new StringValue(cell.CellReference.Value.Replace(RefRow.RowIndex.Value.ToString(), newIndexForFirstRow.ToString()));
}
RefRow.RowIndex = new UInt32Value(newIndexForFirstRow);
}
/// <summary>
/// Iterates over all row and cell nodes to check and add (if missing) RowIndex and CellReference attributes.
/// Those values make it easier to set error style and add comments.
/// </summary>
/// <param name="document">File being modified.</param>
/// <param name="worksheet">Current sheet that is going to be changed.</param>
public static void StructurateExcelSheetDataXmlAttributes(SpreadsheetDocument document, Worksheet worksheet)
{
//Row and Cell nodes are contained within a sheetData node, there is one sheetData node per worksheet.
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
bool firstRow = true;
int currentRow = 0;
int currentRowIndex = 0;
foreach (Row rowItem in sheetData)
{
if (rowItem.RowIndex == null)
{
if (firstRow)
{
currentRow = 1;
currentRowIndex = 1;
}
else
{
currentRow++;
currentRowIndex++;
}
rowItem.RowIndex = (uint)currentRowIndex;
firstRow = false;
}
else
{
currentRow++;
firstRow = false;
currentRowIndex = (int)rowItem.RowIndex.Value;
while ((currentRow) < currentRowIndex)
{
sheetData.InsertBefore(new Row()
{
RowIndex = (uint)(currentRow)
}, rowItem);
currentRow++;
}
}
StructurateCellsInRow(rowItem);
}
if (document.FileOpenAccess != FileAccess.Read)
{
document.Save();
}
}
internal static void InsertRow(Worksheet worksheet, Row refRow, Row insertedRow)
{
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
uint rowIndex = refRow.RowIndex;
MoveRowsDown(worksheet, rowIndex);
Row newRow = sheetData.InsertBefore(insertedRow, refRow);
string newIndex = rowIndex.ToString();
ReplaceRowIndex(newRow, newRow.RowIndex, newIndex.ToString());
newRow.RowIndex = rowIndex;
}
public static Row GetRow(SheetData sheetData, uint line, bool createIfDoesntExists = false)
{
var row = sheetData?.Elements <Row>().FirstOrDefault(r => r.RowIndex == line);
// Se existir a linha retorna.
if (row != null)
{
row.Spans = null;
return(row);
}
else if (!createIfDoesntExists)
{
return(null);
}
// Senao cria uma nova linha.
row = new Row {
RowIndex = line
};
var rows = sheetData.Elements <Row>().ToList();
// Caso nao exista linhas pode simplesmente inserir
if (!rows.Any())
{
sheetData.AppendChild(row);
return(row);
}
// Insere a linha na order correta.
var before = rows.Where(w => w.RowIndex < line).OrderBy(o => o.RowIndex.Value).LastOrDefault();
if (before != null) // Existem linhas anteriores a que sera inserida.
{
sheetData.InsertAfter(row, before);
}
else // Nao existem nenhuma linha anterior a que sera inserida.
{
var after = rows.Where(w => w.RowIndex > line).OrderBy(o => o.RowIndex.Value).FirstOrDefault();
// Insere antes do primeiro.
sheetData.InsertBefore(row, after);
}
return(row);
}
/// <summary>
/// Получить строку.
/// Создается новый элемент строки, если строка еще не существует.
/// </summary>
/// <param name="sheetData">Данные листа в котором находится требуемая строка</param>
/// <param name="rowNum">Номер запрашиваемой строки</param>
/// <returns>Объект строки</returns>
public static Row GetRow(this SheetData sheetData, uint rowNum)
{
var row = sheetData
.Elements <Row>()
.Where(r => r.RowIndex.Value >= rowNum)
.OrderBy(r => r.RowIndex.Value).FirstOrDefault();
if (row != null && row.RowIndex == rowNum)
{
return(row);
}
var newRow = new Row();
newRow.RowIndex = rowNum;
sheetData.InsertBefore(newRow, row);
return(newRow);
}
private static Row GetRow(SheetData sheetData, int rowIndex)
{
Row newRow = null;
foreach (Row current in sheetData.Elements <Row>())
{
if (current.RowIndex >= rowIndex)
{
if (current.RowIndex == rowIndex)
{
return(current);
}
newRow = GetNewRow(rowIndex);
sheetData.InsertBefore <Row>(newRow, current);
return(newRow);
}
}
newRow = GetNewRow(rowIndex);
sheetData.Append(newRow);
return(newRow);
}
private void CopyRange(ref CellRangeTemplate sourceRange, SheetData sheetData, Worksheet worksheet, ref CellPosition target, double?rowHeight = null)
{
#region
Sheet sheetTemplate = sourceRange.CellRange.SheetTemplate;
var workbookPartTemplate = sourceRange.CellRange.WorksheetPart;
MergeCells mergeCellsTemplate = sourceRange.CellRange.MergeCells;
MergeCells mergeCells = worksheet.GetFirstChild <MergeCells>();
if (false && workbookPartTemplate.DrawingsPart != null && worksheet.WorksheetPart.DrawingsPart == null)
{
var drawingsPart = worksheet.WorksheetPart.AddPart <DrawingsPart>(workbookPartTemplate.DrawingsPart);
drawingsPart = worksheet.WorksheetPart.DrawingsPart;
if (!worksheet.WorksheetPart.Worksheet.ChildElements.OfType <Drawing>().Any())
{
worksheet.WorksheetPart.Worksheet.Append(new Drawing {
Id = worksheet.WorksheetPart.GetIdOfPart(drawingsPart)
});
}
}
Dictionary <string, MergeCell> mergeCellTemplateDic = sourceRange.CellRange.MergeCellsDic;
#endregion
CellPosition source = sourceRange.CellRange.Start;
CellPosition offset = target.CalculateOffset(source);
var cellsToCopy = document.FindCellsByRange(sourceRange.CellRange);
for (int i = 0; i < cellsToCopy.Count(); i++)
{
var rowGroup = cellsToCopy.ElementAt(i);
Row keyRow = rowGroup.Key;
Row targetRow = new Row()
{
RowIndex = (UInt32)(keyRow.RowIndex + offset.Row),
Height = (short)-1,
};
if (rowHeight != null)
{
targetRow.Height = rowHeight;
targetRow.CustomHeight = true;
}
MoveCurrentRow((int)targetRow.RowIndex.Value);
sheetData.InsertBefore(targetRow, currentRow);
foreach (Cell cellToCopy in rowGroup)
{
Cell targetCell = (Cell)cellToCopy.Clone();
targetCell.CellReference = CellPosition.OffsetIt(targetCell.CellReference, offset);
targetRow.Append(targetCell);
MergeCell _findMerge;
if (mergeCellTemplateDic != null && mergeCellTemplateDic.TryGetValue(cellToCopy.CellReference.Value.ToUpper(), out _findMerge))
{
var positionParent = _findMerge.Reference.Value.Split(':');
CellPosition offsetStart = new CellPosition(positionParent[0]);
CellPosition offsetEnd = new CellPosition(positionParent[1]);
var celRefNew = new CellPosition(targetCell.CellReference);
if (mergeCells == null)
{
var a = new MergeCells();
worksheet.InsertAfter(a, sheetData);
mergeCells = worksheet.GetFirstChild <MergeCells>();
}
var mergeCell = new MergeCell();
mergeCell.Reference = celRefNew.ToString() + ":" + new CellPosition(celRefNew.Row + (offsetEnd.Row - offsetStart.Row), celRefNew.Column + (offsetEnd.Column - offsetStart.Column)).ToString();
mergeCells.Append(mergeCell);
mergeCells.Count = (mergeCells.Count ?? 0) + 1;
}
}
}
}
public static Row CreateRow(this Worksheet worksheet, Row refRow)
{
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
uint newRowIndex = 0;
var newRow = new Row()
{
RowIndex = refRow.RowIndex.Value
};
var cells = refRow.Elements <Cell>();
newRow.Height = new DoubleValue(refRow.Height);
newRow.CustomHeight = new BooleanValue(refRow.CustomHeight);
foreach (Cell cell in cells)
{
Cell newCell = (Cell)cell.CloneNode(true);
newCell.StyleIndex = new UInt32Value(cell.StyleIndex);
newRow.Append(newCell);
}
IEnumerable <Row> rows = sheetData.Descendants <Row>().Where(r => r.RowIndex.Value >= refRow.RowIndex.Value);
foreach (Row row in rows)
{
newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);
foreach (var cell in row.Elements <Cell>())
{
// Update the references for reserved cells.
string cellReference = cell.CellReference.Value;
cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
}
row.RowIndex = new UInt32Value(newRowIndex);
}
sheetData.InsertBefore(newRow, refRow);
// process merge cell in cloned rows
var mcells = worksheet.GetFirstChild <MergeCells>();
if (mcells != null)
{
//处理所有动态行以下的merg
var clonedMergeCells = mcells.Elements <MergeCell>().
Where(m => Utility.GetRowIndex(m.Reference.Value.Split(':')[0]) >= newRow.RowIndex.Value).ToList <MergeCell>();
foreach (var cmCell in clonedMergeCells)
{
cmCell.Reference.Value = Utility.AddRow(cmCell.Reference.Value);
}
//增加新的merg
var newMergeCells = new List <MergeCell>();
var rowMergeCells = mcells.Elements <MergeCell>().
Where(m => Utility.GetRowIndex(m.Reference.Value.Split(':')[0]) == refRow.RowIndex).ToList <MergeCell>();
foreach (var mc in rowMergeCells)
{
newMergeCells.Add(new MergeCell()
{
Reference = mc.Reference.Value.Replace(refRow.RowIndex.Value.ToString(), (newRow.RowIndex.Value).ToString())
});
}
uint count = mcells.Count.Value;
mcells.Count = new UInt32Value(count + (uint)newMergeCells.Count);
mcells.Append(newMergeCells.ToArray());
}
return(newRow);
}
private static bool SetOrUpdateCellValue(SheetData sheetData, uint rowNumber, uint columnNumber, Type datatype, object value, DefaultCellStyle defaultCellStyle, uint customStyle = 0)
{
if (sheetData != null && value != null && datatype != null)
{
//创建cell.
//0.不存在row,建立row,插入cell.
//0.row存在1.删除cell.2.是否有比这个cell更大的cell,有插入大cell之前.否则直接附加在row后面.
string cellRefrence = GetCellReference(rowNumber, columnNumber);
IEnumerable <Row> equalOrbiggerRows = sheetData.Elements <Row>().Where(x => x.RowIndex >= rowNumber);
Row equalRow = null, biggerRow = null;
if (equalOrbiggerRows != null && equalOrbiggerRows.Count() > 0 && equalOrbiggerRows.First().RowIndex == rowNumber)
{
equalRow = equalOrbiggerRows.First();
}
else if (equalOrbiggerRows != null && equalOrbiggerRows.Count() > 0 && equalOrbiggerRows.First().RowIndex > rowNumber)
{
biggerRow = equalOrbiggerRows.First();
}
if (equalRow != null)//存在row. 1.是否存在cell,存在跟新,不存在建立新cell.2.是否有比这个cell更大的cell,有插入大cell之前.否则直接附加在Row后面.
{
IEnumerable <Cell> equalOrbiggerCells = equalRow.Elements <Cell>().Where(x => x.CellReference >= new StringValue(cellRefrence));
Cell equalCell = null;
Cell biggerCell = null;
if (equalOrbiggerCells != null && equalOrbiggerCells.Count() > 0 && equalOrbiggerCells.First().CellReference == cellRefrence)
{
equalCell = equalOrbiggerCells.First();
}
else if (equalOrbiggerCells != null && equalOrbiggerCells.Count() > 0 && equalOrbiggerCells.First().CellReference > new StringValue(cellRefrence))
{
biggerCell = equalOrbiggerCells.First();
}
Cell newCell = createCell(cellRefrence, datatype, value, defaultCellStyle, customStyle);
if (equalCell != null)
{
equalOrbiggerRows.First().ReplaceChild(newCell, equalCell);
}
else
{
if (biggerCell != null)
{
equalOrbiggerRows.First().InsertBefore(newCell, biggerCell);
}
else
{
equalOrbiggerRows.First().Append(newCell);
}
}
}
else//不存在.新建row and cell.
{
Row newrow = new Row();
newrow.RowIndex = rowNumber;
Cell theCell = IncOpenExcel.createCell(cellRefrence, datatype, value, defaultCellStyle, customStyle);
if (theCell != null)
{
newrow.Append(theCell);
}
if (biggerRow != null)
{
sheetData.InsertBefore(newrow, equalOrbiggerRows.First());//插入的行不是最大的,插到比它大的前面.
}
else
{
sheetData.Append(newrow);; //插入的行是最大的,直接附加到最后
}
}
}
return(true);
}
//Copy an existing row and insert it
//We don't need to copy styles of a refRow because a CloneNode() or Clone() methods do it for us
// private void CopyToLine(Row refRow, uint rowIndex, SheetData sheetData)
private void CopyToLine(Row refRow, SheetData sheetData)
{
uint newRowIndex;
var newRow = (Row)refRow.CloneNode(true);
sheetData.InsertBefore(newRow, refRow);
//newRowIndex = System.Convert.ToUInt32(refRow.RowIndex.Value + 1);
//refRow.RowIndex.Value = newRowIndex;
//IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= refRow.RowIndex.Value);
//int posicao = (int)refRow.RowIndex.Value;
//foreach (Row row in rows)
//{
// newRowIndex = System.Convert.ToUInt32(posicao + 1);// System.Convert.ToUInt32(row.RowIndex.Value + 1);
// foreach (Cell cell in row.Elements<Cell>())
// {
// // Update the references for reserved cells.
// string cellReference = getCOL(cell.CellReference.Value) + newRowIndex.ToString();
// cell.CellReference = new StringValue(cellReference);
// }
// // Update the row index.
// row.RowIndex = new UInt32Value(newRowIndex);
// posicao += 1;
//}
// Loop through all the rows in the worksheet with higher row
// index values than the one you just added. For each one,
// increment the existing row index.
// IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value > newRow.RowIndex.Value);
// IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex);
//int primeiro = 0;
//foreach (Row row in rows)
//{
// if (primeiro == 0)
// {
// newRowIndex = row.RowIndex.Value;
// primeiro = 1;
// }
// else
// newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);
// foreach (Cell cell in row.Elements<Cell>())
// {
// // Update the references for reserved cells.
// string cellReference = cell.CellReference.Value;
// cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
// }
// // Update the row index.
// row.RowIndex = new UInt32Value(newRowIndex);
//}
//foreach (Cell c in newRow.Descendants<Cell>())
//{
// string coluna = getCOL(c.CellReference.Value);
// Cell refCell = refRow.Elements<Cell>().Where(cc => string.Compare(cc.CellReference.Value, coluna + (rowIndex - 1).ToString(), true) == 0).First();
// c.StyleIndex = refCell.StyleIndex;
//}
}
public static Row InsertRow(uint rowIndex, WorksheetPart worksheetPart, Row insertRow, bool isNewLastRow = false)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
Row retRow = !isNewLastRow?sheetData.Elements <Row>().FirstOrDefault(r => r.RowIndex == rowIndex) : null;
// If the worksheet does not contain a row with the specified row index, insert one.
if (retRow != null)
{
// if retRow is not null and we are inserting a new row, then move all existing rows down.
if (insertRow != null)
{
UpdateRowIndexes(worksheetPart, rowIndex, false);
UpdateMergedCellReferences(worksheetPart, rowIndex, false);
UpdateHyperlinkReferences(worksheetPart, rowIndex, false);
// actually insert the new row into the sheet
retRow = sheetData.InsertBefore(insertRow, retRow); // at this point, retRow still points to the row that had the insert rowIndex
string curIndex = rowIndex.ToString();
string newIndex = rowIndex.ToString();
foreach (Cell cell in retRow.Elements <Cell>())
{
// Update the references for the rows cells.
cell.CellReference = new StringValue(cell.CellReference.Value.Replace(curIndex, newIndex));
}
// Update the row index.
retRow.RowIndex = rowIndex;
}
}
else
{
// Row doesn't exist yet, shifting not needed.
// Rows must be in sequential order according to RowIndex. Determine where to insert the new row.
Row refRow = !isNewLastRow?sheetData.Elements <Row>().FirstOrDefault(row => row.RowIndex > rowIndex) : null;
// use the insert row if it exists
retRow = insertRow ?? new Row()
{
RowIndex = rowIndex
};
IEnumerable <Cell> _cellsInRow = retRow.Elements <Cell>().ToList();
if (_cellsInRow.Any())
{
string curIndex = retRow.RowIndex.ToString();
string newIndex = rowIndex.ToString();
foreach (Cell cell in _cellsInRow)
{
// Update the references for the rows cells.
cell.CellReference = new StringValue(cell.CellReference.Value.Replace(curIndex, newIndex));
}
// Update the row index.
retRow.RowIndex = rowIndex;
}
sheetData.InsertBefore(retRow, refRow);
}
return(retRow);
}
private static Cell RetrieveCellReference(Worksheet worksheet, string addressName)
{
//Use regular expressions to get the row number and column name.
//If the paramenter wasn't well formed this code will fail
Regex regex = new Regex("^(?<col>\\D+)(?<row>\\d+)");
Match match = regex.Match(addressName);
uint rowNumber = uint.Parse(match.Result("${row}"));
string colName = match.Result("${col");
//Retrieve reference to the sheet's data.
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
//If the worksheet does not contain a row
//with the specified row index, insert one.
var rows = sheetData.Elements <Row>();
var theRow = rows.FirstOrDefault(r => r.RowIndex.Value == rowNumber);
if (theRow == null)
{
//Rows dosen't exist, so we create a new one.
theRow = new Row();
theRow.RowIndex = rowNumber;
//Must insert row in the appropiate location.
Row refRow = null;
foreach (Row row in rows)
{
if (row.RowIndex > rowNumber)
{
refRow = row;
break;
}
}
//If refRow is null, InsertBefore appends.
sheetData.InsertBefore(theRow, refRow);
}
//At this point, theRow refers to the row to contain the cell value.
//The cell may or may not exist.
//If the cell you need already exists, return it.
//If there is not a cell with the specified address name, insert one.
var cells = theRow.Elements <Cell>();
Cell theCell = cells.FirstOrDefault(c => c.CellReference.Value == addressName);
if (theCell == null)
{
//Cell dosen't exist, so create one.
theCell = new Cell();
theCell.CellReference = addressName;
//Must insert cell in the appropiate location.
Cell refCell = null;
foreach (Cell cell in cells)
{
if (string.Compare(cell.CellReference.Value, addressName, true) > 0)
{
refCell = cell;
break;
}
}
//If refCell is null, InsertBefore appends.
theRow.InsertBefore(theCell, refCell);
}
return(theCell);
}