插入新行和OpenXML的SDK 2.0移动exsisting的人 [英] Inserting new rows and moving exsisting ones with OpenXML SDK 2.0
本文介绍了插入新行和OpenXML的SDK 2.0移动exsisting的人的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我使用的OpenXML SDK 2.0将数据添加到S preadsheet模板，但我遇到了一个问题（因为我一直在使用的OpenXML了一天，这不是那么很难相信）。我无法弄清楚如何添加新行，同时推动所有低于新的向下行。
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.
下面是我使用s preadsheet模板。为了更precise，我要实现的是那些石板有色行移至底部，而将数据添加到白色的行。
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.
目前我使用的code是等同于一个在MSDN - 的通过使用Open XML格式SDK 2.0创建文档（共3部分2）
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)
我认为，美中不足的是在code，这部分的地方：
I believe that the catch is somewhere in this part of the code:
sheetData.AppendChild(polaznikRow);
我也试图与InsertAfter，InsertAt等实验。但没有任何成功。
I've also tried experimenting with InsertAfter, InsertAt ,etc. but without any success.
任何帮助将是AP preciated。
Any help would be appreciated.
推荐答案
在教程code。如果是添加新行到空白工作表将工作。既然你有你使用的是确实有行的模板，你需要做更多的工作，以便在工作表的中间添加一行。您pretty很多需要使用相同的code到行中添加，但你有你插入行后手动更新每一行的行索引。您还可以更新合并单元格引用和超链接引用为好。可能有更多的，你必须更新，但我从来没有更新比这三样东西更多。插入行的主要方法是如下：
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', ' ' };
这篇关于插入新行和OpenXML的SDK 2.0移动exsisting的人的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
