c# - OpenXml 正在覆盖现有工作表
问题描述
我正在尝试将工作表添加到现有工作簿。现有的 Excel 文件已经至少有一个工作表。我的问题是下面的代码只是覆盖了 excel 文件中的现有工作表。如何更改下面的代码以添加新工作表而不覆盖任何现有工作表
我是openxml的新手，所以我不确定我哪里出错了。
using (SpreadsheetDocument xl = SpreadsheetDocument.Open(filename, true))
{
List<OpenXmlAttribute> oxa;
OpenXmlWriter oxw;
WorkbookPart workbookPart = xl.WorkbookPart;
//xl.AddWorkbookPart();
WorksheetPart wsp = workbookPart.AddNewPart<WorksheetPart>();
oxw = OpenXmlWriter.Create(wsp);
oxw.WriteStartElement(new Worksheet());
oxw.WriteStartElement(new SheetData());
for (int rowNum = 0; rowNum <= arrExport.Count - 1; rowNum++)
{
int u = Convert.ToInt32(rowNum.ToString());
oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("r", null, "str"));
oxw.WriteStartElement(new Row(), oxa);
int NextPosition = 0;
for (int col = 0; col < arrExport[u].ColumnData.Count; col++)
{
if (!((col + 1) >= arrExport[u].ColumnData.Count - 1))
{
NextPosition = (arrExport[u].ColumnData[col + 1].ColumnNumber - arrExport[u].ColumnData[col].ColumnNumber);
}
else
{
NextPosition = arrExport[u].ColumnData[col].ColumnNumber - 1;
}
for (int x = 0; x < NextPosition; x++)
{
oxa = new List<OpenXmlAttribute>();
// this is the data type ("t"), with CellValues.String ("str")
oxa.Add(new OpenXmlAttribute("t", null, "str"));
oxw.WriteStartElement(new Cell(), oxa);
oxw.WriteElement(new CellValue(""));
// this is for Cell
oxw.WriteEndElement();
}
oxa = new List<OpenXmlAttribute>();
// this is the data type ("t"), with CellValues.String ("str")
oxa.Add(new OpenXmlAttribute("t", null, "str"));
oxw.WriteStartElement(new Cell(), oxa);
oxw.WriteElement(new CellValue(arrExport[u].ColumnData[col].ColumnData.ToString() == null ? "" : arrExport[u].ColumnData[col].ColumnData.ToString()));
// this is for Cell
oxw.WriteEndElement();
}
// this is for Row
oxw.WriteEndElement();
}
// this is for SheetData
oxw.WriteEndElement();
// this is for Worksheet
oxw.WriteEndElement();
oxw.Close();
oxw = OpenXmlWriter.Create(xl.WorkbookPart);
oxw.WriteStartElement(new Workbook());
oxw.WriteStartElement(new Sheets());
oxw.WriteElement(new Sheet()
{
Name = "Sheet" + sheetnumber.ToString(),
SheetId = Convert.ToUInt32(sheetnumber),
Id = xl.WorkbookPart.GetIdOfPart(wsp)
});
// this is for Sheets
oxw.WriteEndElement();
// this is for Workbook
oxw.WriteEndElement();
oxw.Close();
xl.Close();
}
我希望 excel 文件中的现有工作表保留下来，并添加一个新工作表。
编辑：更新代码-
if (File.Exists(filename))
{
using (SpreadsheetDocument xl = SpreadsheetDocument.Open(filename, true))
{
List<OpenXmlAttribute> oxa;
OpenXmlWriter oxw;
SharedStringTablePart shareStringPart;
if (xl.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
shareStringPart = xl.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = xl.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
WorkbookPart workbookPart = xl.WorkbookPart; //xl.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets objSheets = new Sheets();
workbookPart.Workbook.Append(new BookViews(new WorkbookView()));
workbookPart.Workbook.Append(objSheets);
//xl.AddWorkbookPart();
WorksheetPart wsp = workbookPart.AddNewPart<WorksheetPart>();
String strWorkSheetPartId = workbookPart.GetIdOfPart(wsp);
wsp.Worksheet = new Worksheet();
SheetData objSheetData = new SheetData();
Sheet objSheet = new Sheet() { Name = "Sheet" + sheetnumber.ToString(), SheetId = (UInt32Value)1U, Id = strWorkSheetPartId };
//wsp.Worksheet = objSheet;
objSheets.Append(objSheet);
//oxw = OpenXmlWriter.Create(wsp);
//oxw.WriteStartElement(new Worksheet());
//oxw.WriteStartElement(new SheetData());
//for (int rowNum = 0; rowNum <= arrExport.Count - 1; rowNum++)
//{
// int u = Convert.ToInt32(rowNum.ToString());
// oxa = new List<OpenXmlAttribute>();
// oxa.Add(new OpenXmlAttribute("r", null, "str"));
// //oxw.WriteStartElement(new Row(), oxa);
// int NextPosition = 0;
// for (int col = 0; col < arrExport[u].ColumnData.Count; col++)
// {
// if (!((col + 1) >= arrExport[u].ColumnData.Count - 1))
// {
// NextPosition = (arrExport[u].ColumnData[col + 1].ColumnNumber - arrExport[u].ColumnData[col].ColumnNumber);
// }
// else
// {
// NextPosition = arrExport[u].ColumnData[col].ColumnNumber - 1;
// }
// for (int x = 0; x < NextPosition; x++)
// {
// oxa = new List<OpenXmlAttribute>();
// // this is the data type ("t"), with CellValues.String ("str")
// oxa.Add(new OpenXmlAttribute("t", null, "str"));
// //oxw.WriteStartElement(new Cell(), oxa);
// //oxw.WriteElement(new CellValue(""));
// // this is for Cell
// // oxw.WriteEndElement();
// }
// oxa = new List<OpenXmlAttribute>();
// // this is the data type ("t"), with CellValues.String ("str")
// oxa.Add(new OpenXmlAttribute("t", null, "str"));
// //oxw.WriteStartElement(new Cell(), oxa);
// //oxw.WriteElement(new CellValue(arrExport[u].ColumnData[col].ColumnData.ToString() == null ? "" : arrExport[u].ColumnData[col].ColumnData.ToString()));
// // this is for Cell
// //oxw.WriteEndElement();
// }
// // this is for Row
// //oxw.WriteEndElement();
//}
// this is for SheetData
//oxw.WriteEndElement();
//// this is for Worksheet
//oxw.WriteEndElement();
//oxw.Close();
//oxw = OpenXmlWriter.Create(xl.WorkbookPart);
//oxw.WriteStartElement(new Workbook());
//oxw.WriteStartElement(new Sheets());
//oxw.WriteElement(new Sheet()
//{
// Name = "Sheet" + sheetnumber.ToString(),
// SheetId = Convert.ToUInt32(sheetnumber),
// Id = xl.WorkbookPart.GetIdOfPart(wsp)
//});
//// this is for Sheets
//oxw.WriteEndElement();
//// this is for Workbook
//oxw.WriteEndElement();
//oxw.Close();
xl.Close();
}
}
else
{
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
List<OpenXmlAttribute> oxa;
OpenXmlWriter oxw;
xl.AddWorkbookPart();
WorksheetPart wsp = xl.WorkbookPart.AddNewPart<WorksheetPart>();
oxw = OpenXmlWriter.Create(wsp);
oxw.WriteStartElement(new Worksheet());
oxw.WriteStartElement(new SheetData());
for (int rowNum = 0; rowNum <= arrExport.Count - 1; rowNum++)
{
int u = Convert.ToInt32(rowNum.ToString());
oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("r", null, "str"));
oxw.WriteStartElement(new Row(), oxa);
int NextPosition = 0;
for (int col = 0; col < arrExport[u].ColumnData.Count; col++)
{
if (!((col + 1) >= arrExport[u].ColumnData.Count - 1))
{
NextPosition = (arrExport[u].ColumnData[col + 1].ColumnNumber - arrExport[u].ColumnData[col].ColumnNumber);
}
else
{
NextPosition = arrExport[u].ColumnData[col].ColumnNumber - 1;
}
for (int x = 0; x < NextPosition; x++)
{
oxa = new List<OpenXmlAttribute>();
// this is the data type ("t"), with CellValues.String ("str")
oxa.Add(new OpenXmlAttribute("t", null, "str"));
oxw.WriteStartElement(new Cell(), oxa);
oxw.WriteElement(new CellValue(""));
// this is for Cell
oxw.WriteEndElement();
}
oxa = new List<OpenXmlAttribute>();
// this is the data type ("t"), with CellValues.String ("str")
oxa.Add(new OpenXmlAttribute("t", null, "str"));
oxw.WriteStartElement(new Cell(), oxa);
oxw.WriteElement(new CellValue(arrExport[u].ColumnData[col].ColumnData.ToString() == null ? "" : arrExport[u].ColumnData[col].ColumnData.ToString()));
// this is for Cell
oxw.WriteEndElement();
}
// this is for Row
oxw.WriteEndElement();
}
// this is for SheetData
oxw.WriteEndElement();
// this is for Worksheet
oxw.WriteEndElement();
oxw.Close();
oxw = OpenXmlWriter.Create(xl.WorkbookPart);
oxw.WriteStartElement(new Workbook());
oxw.WriteStartElement(new Sheets());
// you can use object initialisers like this only when the properties
// are actual properties. SDK classes sometimes have property-like properties
// but are actually classes. For example, the Cell class has the CellValue
// "property" but is actually a child class internally.
// If the properties correspond to actual XML attributes, then you're fine.
oxw.WriteElement(new Sheet()
{
Name = "Sheet" + sheetnumber.ToString(),
SheetId = Convert.ToUInt32(sheetnumber),
Id = xl.WorkbookPart.GetIdOfPart(wsp)
});
// this is for Sheets
oxw.WriteEndElement();
// this is for Workbook
oxw.WriteEndElement();
oxw.Close();
xl.Close();
}
}
解决方案
您想创建一个实例Sheets，将其附加到您的WorkbookPart，然后将零件添加到您的，SpreadsheetDocument然后您可以创建新工作表为SheetData
例如
WorkbookPart objWorkbookPart = objDocument.AddWorkbookPart();
objWorkbookPart.Workbook = new Workbook();
Sheets objSheets = new Sheets();
objWorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
objWorkbookPart.Workbook.Append(objSheets);
创建一个WorksheetPart
WorksheetPart objWorksheetPart = objWorkbookPart.AddNewPart<WorksheetPart>();
string strWorkSheetPartrId = objWorkbookPart.GetIdOfPart(objWorksheetPart);
objWorksheetPart.Worksheet = new Worksheet();
然后你将你的附加Sheet到它
SheetData objSheetData = new SheetData();
Sheet objSheet = new Sheet() { Name = "Yoursheetname", SheetId = (UInt32Value)1U, Id = strWorkSheetPartrId };
objSheets.Append(objSheet);
推荐阅读
- c# - ListBlobs() 仅查看 Azure blob 容器中的第一个文件夹
- javascript - 向多个收件人发送电子邮件
- python - 合并两个数组并应用一个函数
- carrot2 - 使用 Carrot2 时出现 Java 堆空间错误
- php - 尝试从 PHP git pull 时出现 known_hosts 文件权限问题
- html - 具有各种搜索参数的 HTML 网页查询
- css - 添加带有路径的SVG到css类不起作用
- javascript - 我如何将这些数据放在 html 中？
- typescript - React Native / typescript中的自定义警报
- plot - 如何在 Julia Plots Backend PGFPlotsX 中获得完整的黑色刻度线？