Inserting new rows and moving exsisting ones with OpenXML SDK 2.0
I'm using OpenXML SDK 2.0 to add data to the spreadsheet template but I ran into a problem (since I've been using OpenXML for a day that's not so hard to believe). I can't figure out how to add new rows while pushing all the rows that are below the new ones down.
Here is the spreadsheet template I'm using. To be more precise, what I want to achieve is to move those slate colored rows to the bottom while adding data to the white colored rows.
The code I'm currently using is identical to the one on MSDN - Creating Documents by Using the Open XML Format SDK 2.0 (Part 2 of 3)
I believe that the catch is somewhere in this part of the code:
sheetData.AppendChild(polaznikRow);
I've also tried experimenting with InsertAfter, InsertAt ,etc. but without any success.
Any help would be appreciated.
Answer
That code in the tutorial will work if you are adding new rows to a blank worksheet. Since you have a template that you are using that does have rows you will need to do a lot more work in order to add a row in the middle of your worksheet. You pretty much need to use the same code to add in the row, but then you have to manually update the row index of every row after the row you insert. You also have to update the merged cell references and the hyperlinks references as well. There might be more that you have to update, but I never had to update more than these three things. The main method to insert a row is below:
/// <summary>
/// Inserts a new row at the desired index. If one already exists, then it is
/// returned. If an insertRow is provided, then it is inserted into the desired
/// rowIndex
/// </summary>
/// <param name="rowIndex">Row Index</param>
/// <param name="worksheetPart">Worksheet Part</param>
/// <param name="insertRow">Row to insert</param>
/// <param name="isLastRow">Optional parameter - True, you can guarantee that this row is the last row (not replacing an existing last row) in the sheet to insert; false it is not</param>
/// <returns>Inserted Row</returns>
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 = retRow.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>();
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;
}
Then here are the following helper methods to update the row indices, hyperlinks, and merged cell references:
/// <summary>
/// Updates all of the Row indexes and the child Cells' CellReferences whenever
/// a row is inserted or deleted.
/// </summary>
/// <param name="worksheetPart">Worksheet Part</param>
/// <param name="rowIndex">Row Index being inserted or deleted</param>
/// <param name="isDeletedRow">True if row was deleted, otherwise false</param>
private static void UpdateRowIndexes(WorksheetPart worksheetPart, uint rowIndex, bool isDeletedRow)
{
// Get all the rows in the worksheet with equal or higher row index values than the one being inserted/deleted for reindexing.
IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex);
foreach (Row row in rows)
{
uint newIndex = (isDeletedRow ? row.RowIndex - 1 : row.RowIndex + 1);
string curRowIndex = row.RowIndex.ToString();
string newRowIndex = newIndex.ToString();
foreach (Cell cell in row.Elements<Cell>())
{
// Update the references for the rows cells.
cell.CellReference = new StringValue(cell.CellReference.Value.Replace(curRowIndex, newRowIndex));
}
// Update the row index.
row.RowIndex = newIndex;
}
}
/// <summary>
/// Updates the MergedCelss reference whenever a new row is inserted or deleted. It will simply take the
/// row index and either increment or decrement the cell row index in the merged cell reference based on
/// if the row was inserted or deleted.
/// </summary>
/// <param name="worksheetPart">Worksheet Part</param>
/// <param name="rowIndex">Row Index being inserted or deleted</param>
/// <param name="isDeletedRow">True if row was deleted, otherwise false</param>
private static void UpdateMergedCellReferences(WorksheetPart worksheetPart, uint rowIndex, bool isDeletedRow)
{
if (worksheetPart.Worksheet.Elements<MergeCells>().Count() > 0)
{
MergeCells mergeCells = worksheetPart.Worksheet.Elements<MergeCells>().FirstOrDefault();
if (mergeCells != null)
{
// Grab all the merged cells that have a merge cell row index reference equal to or greater than the row index passed in
List<MergeCell> mergeCellsList = mergeCells.Elements<MergeCell>().Where(r => r.Reference.HasValue)
.Where(r => GetRowIndex(r.Reference.Value.Split(':').ElementAt(0)) >= rowIndex ||
GetRowIndex(r.Reference.Value.Split(':').ElementAt(1)) >= rowIndex).ToList();
// Need to remove all merged cells that have a matching rowIndex when the row is deleted
if (isDeletedRow)
{
List<MergeCell> mergeCellsToDelete = mergeCellsList.Where(r => GetRowIndex(r.Reference.Value.Split(':').ElementAt(0)) == rowIndex ||
GetRowIndex(r.Reference.Value.Split(':').ElementAt(1)) == rowIndex).ToList();
// Delete all the matching merged cells
foreach (MergeCell cellToDelete in mergeCellsToDelete)
{
cellToDelete.Remove();
}
// Update the list to contain all merged cells greater than the deleted row index
mergeCellsList = mergeCells.Elements<MergeCell>().Where(r => r.Reference.HasValue)
.Where(r => GetRowIndex(r.Reference.Value.Split(':').ElementAt(0)) > rowIndex ||
GetRowIndex(r.Reference.Value.Split(':').ElementAt(1)) > rowIndex).ToList();
}
// Either increment or decrement the row index on the merged cell reference
foreach (MergeCell mergeCell in mergeCellsList)
{
string[] cellReference = mergeCell.Reference.Value.Split(':');
if (GetRowIndex(cellReference.ElementAt(0)) >= rowIndex)
{
string columnName = GetColumnName(cellReference.ElementAt(0));
cellReference[0] = isDeletedRow ? columnName + (GetRowIndex(cellReference.ElementAt(0)) - 1).ToString() : IncrementCellReference(cellReference.ElementAt(0), CellReferencePartEnum.Row);
}
if (GetRowIndex(cellReference.ElementAt(1)) >= rowIndex)
{
string columnName = GetColumnName(cellReference.ElementAt(1));
cellReference[1] = isDeletedRow ? columnName + (GetRowIndex(cellReference.ElementAt(1)) - 1).ToString() : IncrementCellReference(cellReference.ElementAt(1), CellReferencePartEnum.Row);
}
mergeCell.Reference = new StringValue(cellReference[0] + ":" + cellReference[1]);
}
}
}
}
/// <summary>
/// Updates all hyperlinks in the worksheet when a row is inserted or deleted.
/// </summary>
/// <param name="worksheetPart">Worksheet Part</param>
/// <param name="rowIndex">Row Index being inserted or deleted</param>
/// <param name="isDeletedRow">True if row was deleted, otherwise false</param>
private static void UpdateHyperlinkReferences(WorksheetPart worksheetPart, uint rowIndex, bool isDeletedRow)
{
Hyperlinks hyperlinks = worksheetPart.Worksheet.Elements<Hyperlinks>().FirstOrDefault();
if (hyperlinks != null)
{
Match hyperlinkRowIndexMatch;
uint hyperlinkRowIndex;
foreach (Hyperlink hyperlink in hyperlinks.Elements<Hyperlink>())
{
hyperlinkRowIndexMatch = Regex.Match(hyperlink.Reference.Value, "[0-9]+");
if (hyperlinkRowIndexMatch.Success && uint.TryParse(hyperlinkRowIndexMatch.Value, out hyperlinkRowIndex) && hyperlinkRowIndex >= rowIndex)
{
// if being deleted, hyperlink needs to be removed or moved up
if (isDeletedRow)
{
// if hyperlink is on the row being removed, remove it
if (hyperlinkRowIndex == rowIndex)
{
hyperlink.Remove();
}
// else hyperlink needs to be moved up a row
else{
hyperlink.Reference.Value = hyperlink.Reference.Value.Replace(hyperlinkRowIndexMatch.Value, (hyperlinkRowIndex - 1).ToString());
}
}
// else row is being inserted, move hyperlink down
else
{
hyperlink.Reference.Value = hyperlink.Reference.Value.Replace(hyperlinkRowIndexMatch.Value, (hyperlinkRowIndex + 1).ToString());
}
}
}
// Remove the hyperlinks collection if none remain
if (hyperlinks.Elements<Hyperlink>().Count() == 0)
{
hyperlinks.Remove();
}
}
}
/// <summary>
/// Given a cell name, parses the specified cell to get the row index.
/// </summary>
/// <param name="cellReference">Address of the cell (ie. B2)</param>
/// <returns>Row Index (ie. 2)</returns>
public static uint GetRowIndex(string cellReference)
{
// Create a regular expression to match the row index portion the cell name.
Regex regex = new Regex(@"\d+");
Match match = regex.Match(cellReference);
return uint.Parse(match.Value);
}
/// <summary>
/// Increments the reference of a given cell. This reference comes from the CellReference property
/// on a Cell.
/// </summary>
/// <param name="reference">reference string</param>
/// <param name="cellRefPart">indicates what is to be incremented</param>
/// <returns></returns>
public static string IncrementCellReference(string reference, CellReferencePartEnum cellRefPart)
{
string newReference = reference;
if (cellRefPart != CellReferencePartEnum.None && !String.IsNullOrEmpty(reference))
{
string[] parts = Regex.Split(reference, "([A-Z]+)");
if (cellRefPart == CellReferencePartEnum.Column || cellRefPart == CellReferencePartEnum.Both)
{
List<char> col = parts[1].ToCharArray().ToList();
bool needsIncrement = true;
int index = col.Count - 1;
do
{
// increment the last letter
col[index] = Letters[Letters.IndexOf(col[index]) + 1];
// if it is the last letter, then we need to roll it over to 'A'
if (col[index] == Letters[Letters.Count - 1])
{
col[index] = Letters[0];
}
else
{
needsIncrement = false;
}
} while (needsIncrement && --index >= 0);
// If true, then we need to add another letter to the mix. Initial value was something like "ZZ"
if (needsIncrement)
{
col.Add(Letters[0]);
}
parts[1] = new String(col.ToArray());
}
if (cellRefPart == CellReferencePartEnum.Row || cellRefPart == CellReferencePartEnum.Both)
{
// Increment the row number. A reference is invalid without this componenet, so we assume it will always be present.
parts[2] = (int.Parse(parts[2]) + 1).ToString();
}
newReference = parts[1] + parts[2];
}
return newReference;
}
Also some additional pieces you will need:
/// <summary>
/// Given a cell name, parses the specified cell to get the column name.
/// </summary>
/// <param name="cellReference">Address of the cell (ie. B2)</param>
/// <returns>Column name (ie. A2)</returns>
private static string GetColumnName(string cellName)
{
// Create a regular expression to match the column name portion of the cell name.
Regex regex = new Regex("[A-Za-z]+");
Match match = regex.Match(cellName);
return match.Value;
}
public enum CellReferencePartEnum
{
None,
Column,
Row,
Both
}
private static List<char> Letters = new List<char>() { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', ' ' };