解决思路是将指定行数以下的内容全部下移一行,然后插入新内容。
示例代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using System.IO;
using System.Text.RegularExpressions;
namespace OpenXmlTest
{
public partial class OpenXml : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
try
{
string sfile = Server.MapPath("~/Office/template.xlsx");
string file = Server.MapPath("~/Office") + "/" + DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xlsx";
File.Copy(sfile, file);
using (SpreadsheetDocument xl = SpreadsheetDocument.Open(file, true))
{
WorkbookPart wp = xl.WorkbookPart;
Workbook wb = wp.Workbook;
Sheet s = wb.Descendants<Sheet>().Where(w => w.Name == "Sheet1").FirstOrDefault();
WorksheetPart sp = wp.GetPartById(s.Id) as WorksheetPart;
if (sp != null)
{
SheetData sda = sp.Worksheet.Descendants<SheetData>().FirstOrDefault();
//SharedStringTable stringTable = xl.WorkbookPart.SharedStringTablePart.SharedStringTable;
//Row row = sp.Worksheet.Descendants<Row>().ToList()[1];
int insertIndex = 3;//在第三行插入
//把后面的行内容全部下移
foreach (Row row in sp.Worksheet.Descendants<Row>())
{
int index = int.Parse(row.RowIndex.ToString());
if (index >= insertIndex)
{
//从这一行开始移动
row.RowIndex = new UInt32Value((uint)index + 1);
foreach (Cell cell in row.Descendants<Cell>())
{
Regex r = new Regex(@"[0-9]+");
string num = r.Match(cell.CellReference.ToString()).Value;
r = new Regex(@"[a-zA-Z]+");
string chr = r.Match(cell.CellReference.ToString()).Value;
string newRef = string.Format("{0}{1}", chr, (int.Parse(num) + 1));
cell.CellReference = newRef;
}
}
}
//移动完后再插入新的
Row nrow = new Row() { RowIndex = (UInt32Value)3U };
Cell ncell = new Cell();
ncell.CellReference = string.Format("A{0}", insertIndex);
ncell.DataType = CellValues.String;
ncell.CellValue = new CellValue("新插入的");
nrow.Append(ncell);
sda.InsertAt<Row>(nrow, insertIndex - 1);
//cell.DataType = CellValues.String;
//cell.CellValue = new CellValue("1");
//Row nrow = new Row();
//Cell ncell = new Cell();
//ncell.StyleIndex = row.Descendants<Cell>().ToList()[0].StyleIndex;
//ncell.CellValue = new CellValue(DateTime.Now.ToOADate().ToString());
//nrow.Append(ncell);
//sda.Append(nrow);
wb.Save();
}
wb.Clone();
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}