当Excel保存文档时，如何保留编辑后的OpenXml单元格属性？ [英] How do you preserve OpenXml cell attributes after an edit when Excel saves the document?
问题描述
- 下载一个Excel文件（从我的程序导出）
- 编辑文件中的数据
- 保存
- 上传Excel文件（将其重新导入我的程序）
基本上，我将给他们一个离线文件的体验，如果他们没有任何互联网访问，他们可以编辑因为我们是一个Web应用程序）
使用 OpenXml SDK 我使用 OpenXmlElement.SetAttribute 方法将属性添加到工作表的列，行和单元格。我添加的属性被使用，以便在重新导入时，我可以将编辑的数据与应该存储的位置相匹配。
我导出的属性是：
- 数据库标识
- 原始值（导出时允许在导入时轻松同步时的数据库值） li>
- 上次修改导出数据的日期
对于单元格，导出例程如下所示：
var cell = new Cell {
CellReference = string.Format（{0} {1}，Column .Reference，Row.Index），
DataType = this.CellDataType
};
foreach（var keyValuePair in this.AttributeDictionary）{
cell.SetAttribute（new OpenXmlAttribute {
LocalName = keyValuePair.Key，
Value = keyValuePair.Value.ToString （）
}）;
}
此导出工作正常。在 OpenXml生产力工具我可以看到属性被正确添加。当在Excel中编辑后保存文件时，不会保留属性。有没有办法告诉Excel保存属性，还是还有另外一个程序，最好在这里保存我需要的数据，便于重新导入数据？
侧面问题：
如果Excel不保留它们的属性是什么？
我不认为您可以强制Excel进行未知的属性，但您可以使用 ExtensionLists 和扩展程序。 Excel将会往返这些元素，并且是按照我们之后的方式设计的（就我所能做的）来存储特定于应用程序的数据。
似乎没有我可以找到太多的文档，但是 ECMA的第3部分-376 spec 提到扩展名。
以下代码将创建一个在单元格A1和 ExtensionList 其中一个 Extension 作为该单元格的小孩：
public static void CreateSpreadsheetWorkbook（string filepath）
{
if（File.Exists（filepath））
File.Delete（filepath）;
使用（SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create（filepath，SpreadsheetDocumentType.Workbook））
{
//向工作簿添加一个WorkbookPart。
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart（）;
workbookpart.Workbook = new Workbook（）;
//将WorksheetPart添加到WorkbookPart。
WorksheetPart worksheetPart = workbookpart.AddNewPart< WorksheetPart>（）;
SheetData sheetData = new SheetData（）;
worksheetPart.Worksheet = new Worksheet（sheetData）;
//将工作簿添加到工作簿。
表格Sheet = spreadsheetDocument.WorkbookPart.Workbook.AppendChild< Sheets>（new Sheets（））;
//附加一个新的工作表，并将其与工作簿相关联。
Sheet sheet = new Sheet（）
{
Id = spreadsheetDocument.WorkbookPart。
GetIdOfPart（worksheetPart），
SheetId = 1，
Name =Sheet1
};
sheets.Append（sheet）;
行row = new Row（）
{
RowIndex = 1U
};
单元格= new Cell（）
{
CellReference =A1，
CellValue = new CellValue（A Test），
DataType = CellValues.String
};
ExtensionList extensions = new ExtensionList（）;
扩展扩展名= new Extension（）
{
Uri =Testing1234
};
extensions.AppendChild（extension）;
extension.AddNamespaceDeclaration（ns，http：// tempuri / someUrl）;
cell.AppendChild（extensions）;
row.Append（cell）;
sheetData.Append（row）;
workbookpart.Workbook.Save（）;
//关闭文档。
spreadsheetDocument.Close（）;
}
}
以下将再次读取该值，即使该文件已经通过Excel完成。
public static void ReadSheet（string filename，string sheetName）
{
使用（SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open（filename，false））
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
//获取正确的表格
Sheet sheet = workbookPart.Workbook.Descendants< Sheet>（）。其中（s => s.Name == sheetName）.First（）;
if（sheet！= null）
{
WorksheetPart worksheetPart = workbookPart.GetPartById（sheet.Id）as WorksheetPart;
foreach（WorkheetPart.Worksheet.Descendants< Cell>（））中的单元格单元
{
ExtensionList extensions = cell.GetFirstChild< ExtensionList>（）;
if（extensions！= null）
{
扩展扩展名= extensions.GetFirstChild< Extension>（）;
if（extension！= null）
{
Console.WriteLine（Cell {0}具有值{1}，cell.CellReference，extension.Uri）;
}
}
}
}
}
}
其输出为
单元格A1具有值Testing1234
对于你的问题：
如果Excel有什么属性不保存他们？
我不太确定 - 我唯一使用 OpenXmlAttribute class当我使用SAX方法来编写文档时。在这种情况下，您需要与元素一起显式地编写属性。例如：
列表< OpenXmlAttribute> oxa = new List< OpenXmlAttribute>（）;
//单元格引用属性
oxa.Add（new OpenXmlAttribute（r，，A1））;
//单元格类型属性
oxa.Add（new OpenXmlAttribute（t，，str））;
//使用上述属性写入单元格的起始元素
oxw.WriteStartElement（new Cell（），oxa）;
//向单元格写一个值
oxw.WriteElement（new CellValue（Test））;
//写最终元素
oxw.WriteEndElement（）;
我的 answer here 有一个使用SAX方法的完整示例。
I want to export an excel file with data so that my users can:
- Download an Excel file (export from my program)
- Edit the data in the file
- Save it
- Upload the Excel file (reimport it into my program)
Basically I will give them the experience of having an offline file that they can edit if they do not have any internet access (as ours is a web application)
When creating Excel files using the OpenXml SDK I use the OpenXmlElement.SetAttribute method to add attributes to the columns, rows and cells of the worksheet. The attributes I add are used so that on reimport I can match the edited data with the location where it should be stored.
The attributes I export are:
- Database Id's
- Original value (database value at time of export to allow for easy synchronisation on import)
- The date the exported data was last modified
The export routine looks like this for a cell:
var cell = new Cell {
CellReference = string.Format("{0}{1}", Column.Reference, Row.Index),
DataType = this.CellDataType
};
foreach (var keyValuePair in this.AttributeDictionary) {
cell.SetAttribute(new OpenXmlAttribute {
LocalName = keyValuePair.Key,
Value = keyValuePair.Value.ToString()
});
}
This export works fine. When examining the exported file in the OpenXml Productivity Tool I can see the attributes are added correctly. When the file is saved after editing in Excel the attributes are not preserved. Is there a way to tell Excel to preserve the attributes or is there another procedure that would be best used here to preserve the data I need for easy re-importation of the data?
Side Question:
What are the attributes for if Excel does not preserve them?
I don't think you can force Excel to round trip unknown attributes but you can add extension elements using ExtensionLists and Extensions. Excel will roundtrip these elements and are designed (as far as I can make out) for storing application specific data just as you are after.
There doesn't seem to be too much documentation around that I can find but Part 3 of the ECMA-376 spec mentions extensions.
The following code will create a sheet with a value in cell A1 and an ExtensionList with one Extension in it as a child of that cell:
public static void CreateSpreadsheetWorkbook(string filepath)
{
if (File.Exists(filepath))
File.Delete(filepath);
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet1"
};
sheets.Append(sheet);
Row row = new Row()
{
RowIndex = 1U
};
Cell cell = new Cell()
{
CellReference = "A1",
CellValue = new CellValue("A Test"),
DataType = CellValues.String
};
ExtensionList extensions = new ExtensionList();
Extension extension = new Extension()
{
Uri = "Testing1234"
};
extensions.AppendChild(extension);
extension.AddNamespaceDeclaration("ns", "http://tempuri/someUrl");
cell.AppendChild(extensions);
row.Append(cell);
sheetData.Append(row);
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
}
The following will read the value back again, even if the file has been round tripped through Excel.
public static void ReadSheet(string filename, string sheetName)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
//get the correct sheet
Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).First();
if (sheet != null)
{
WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;
foreach (Cell cell in worksheetPart.Worksheet.Descendants<Cell>())
{
ExtensionList extensions = cell.GetFirstChild<ExtensionList>();
if (extensions != null)
{
Extension extension = extensions.GetFirstChild<Extension>();
if (extension != null)
{
Console.WriteLine("Cell {0} has value {1}", cell.CellReference, extension.Uri);
}
}
}
}
}
}
The output from which is
Cell A1 has value Testing1234
As for your side question:
What are the attributes for if Excel does not preserve them?
I'm not too sure - the only time I've used the OpenXmlAttribute class is when I've used a SAX approach to write a document. In that case you need to write the attributes explicitly along with the elements. For example:
List<OpenXmlAttribute> oxa = new List<OpenXmlAttribute>();
//cell reference attribute
oxa.Add(new OpenXmlAttribute("r", "", "A1"));
//cell type attribute
oxa.Add(new OpenXmlAttribute("t", "", "str"));
//write the start element of a cell with the above attributes
oxw.WriteStartElement(new Cell(), oxa);
//write a value to the cell
oxw.WriteElement(new CellValue("Test"));
//write the end element
oxw.WriteEndElement();
My answer here has a full example of using a SAX approach.
这篇关于当Excel保存文档时，如何保留编辑后的OpenXml单元格属性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
