如何使用OpenXML在Excel工作表中添加行 [英] How to add rows in excel sheet using OpenXML
本文介绍了如何使用OpenXML在Excel工作表中添加行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在Excel工作表之间添加/插入新/现有行时遇到问题.
任何人都可以帮助我实现这一目标吗?
例如,我需要复制第10行并将其粘贴到第20行之后.
请帮忙.
谢谢和问候,
YKK Reddy
Hi,
I am facing a problem in adding/inserting new/existing rows in between a excel sheet.
Can anyone help me in achieving this ?
For example, i need to copy Row# 10 and paste it after Row# 20.
Kindly help.
Thanks and Regards,
YKK Reddy
推荐答案
private static Row CreateRow(Row refRow, SheetData sheetData, Row refRow1)
{
uint newRowIndex = 0;
var newRow = new Row() { RowIndex = refRow.RowIndex.Value };
// 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 > refRow.RowIndex.Value);
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()));
}
// Update the row index.
row.RowIndex = new UInt32Value(newRowIndex);
}
//sheetData.InsertAt(newRow, 40);
sheetData.InsertBefore(newRow, refRow);
return newRow;
}
Herez示例代码:
Herez sample code:
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
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 = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
}
// If there is not a cell with the specified column name, insert one.
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
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
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;
}
}
这篇关于如何使用OpenXML在Excel工作表中添加行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文