/// <summary>
/// Create a new documnent.
/// </summary>
/// <param name="createLocation">The location and path to create the document.</param>
protected void CreateDocument(string createLocation)
{
_templateLocation = createLocation;
_createLocation = createLocation;
// Create the new doucument
_document = SpreadsheetDocument.Create(createLocation, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
_workBookPart = _document.AddWorkbookPart();
_workBookPart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
_worksheetParts = new List <WorksheetPart>();
_worksheetParts.Add(_workBookPart.AddNewPart <WorksheetPart>());
_worksheetParts[0].Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
_sheets = _workBookPart.Workbook.AppendChild <Sheets>(new Sheets());
NameTable nt = new NameTable();
_xmlNamespaceManager = new XmlNamespaceManager(nt);
_xmlNamespaceManager.AddNamespace("w", nameSpace);
NameTable ntr = new NameTable();
_xmlNamespaceManagerRelationships = new XmlNamespaceManager(ntr);
_xmlNamespaceManagerRelationships.AddNamespace("r", nameSpaceRelationships);
// Get the document part from the package.
_xDocument = new XmlDocument();
// Load the XML in the part into an XmlDocument instance.
_xDocument.Load(_workBookPart.GetStream());
}
private void ParseWorkbookPart(ExecuteArgs param, Dictionary <string, Dictionary <string, DefinedName> > cacheNames, WorkbookPart workbookPart, WorkbookPart newWorkbookPart, List <Excel.Sheet> sheetList)
{
using (var stream = workbookPart.GetStream())
{
ParseWorkbookPart(param, cacheNames, stream, newWorkbookPart, sheetList);
}
}
/// <summary>
/// Open a document with the template.
/// </summary>
/// <param name="templateLocation">The document template location.</param>
/// <param name="createLocation">The document creation location.</param>
protected void OpenDocument(string templateLocation, string createLocation)
{
_templateLocation = templateLocation;
_createLocation = createLocation;
// Open the document.
System.IO.File.Copy(templateLocation, createLocation, true);
_document = SpreadsheetDocument.Open(createLocation, true);
_workBookPart = _document.WorkbookPart;
_worksheetParts = new List <WorksheetPart>(_workBookPart.WorksheetParts);
_sheets = _workBookPart.Workbook.Sheets;
NameTable nt = new NameTable();
_xmlNamespaceManager = new XmlNamespaceManager(nt);
_xmlNamespaceManager.AddNamespace("x", nameSpace);
NameTable ntr = new NameTable();
_xmlNamespaceManagerRelationships = new XmlNamespaceManager(ntr);
_xmlNamespaceManagerRelationships.AddNamespace("r", nameSpaceRelationships);
// Get the document part from the package.
_xDocument = new XmlDocument();
// Load the XML in the part into an XmlDocument instance.
_xDocument.Load(_workBookPart.GetStream());
}
private static XmlDocument LoadWorkbookDOM(WorkbookPart workbook)
{
using (Stream workbookstr = workbook.GetStream(FileMode.Open, FileAccess.Read))
{
XmlDocument doc = new XmlDocument();
doc.Load(workbookstr);
return(doc);
}
}
private void ParseWorkbookPart(ExecuteArgs param, Dictionary <string, Dictionary <string, DefinedName> > cacheNames, WorkbookPart workbookPart, List <Excel.Sheet> sheetList)
{
using (var buffer = new MemoryStream())
{
using (var stream = workbookPart.GetStream())
stream.CopyTo(buffer);
buffer.Position = 0;
ParseWorkbookPart(param, cacheNames, buffer, workbookPart, sheetList);
}
}
/// <summary>
/// Gets the list of sheets from a File
/// </summary>
/// <param name="excelDoc"></param>
/// <returns></returns>
public static List <string> GetSheetNames(this SpreadsheetDocument excelDoc)
{
List <string> sheets = new List <string>();
WorkbookPart workbook = excelDoc.WorkbookPart;
Stream workbookstr = workbook.GetStream();
XmlDocument doc = new XmlDocument();
doc.Load(workbookstr);
XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);
nsManager.AddNamespace("default", doc.DocumentElement.NamespaceURI);
XmlNodeList nodelist = doc.SelectNodes("//default:sheets/default:sheet", nsManager);
foreach (XmlNode node in nodelist)
{
string sheetName = string.Empty;
sheetName = node.Attributes["name"].Value;
sheets.Add(sheetName);
}
return(sheets);
}
/// <summary>
/// 获取Excel中多表的表名
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private List <string> GetExcelSheetNames(string filePath)
{
string sheetName = string.Empty;
List <string> sheetNames = new List <string>();//所有Sheet表名
using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, false))
{
WorkbookPart workBook = spreadDocument.WorkbookPart;
Stream stream = workBook.GetStream(FileMode.Open);
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(stream);
XmlNamespaceManager xmlNSManager = new XmlNamespaceManager(xmlDocument.NameTable);
xmlNSManager.AddNamespace("default", xmlDocument.DocumentElement.NamespaceURI);
XmlNodeList nodeList = xmlDocument.SelectNodes("//default:sheets/default:sheet", xmlNSManager);
foreach (XmlNode node in nodeList)
{
sheetName = node.Attributes["name"].Value;
sheetNames.Add(sheetName);
}
}
return(sheetNames);
}
private void ParseWorkbookPart(ExecuteArgs param, Dictionary <string, Dictionary <string, DefinedName> > cacheNames, Stream workbookPart, WorkbookPart newWorkbookPart, List <Excel.Sheet> sheetList)
{
using (var reader = OpenXmlReader.Create(workbookPart))
using (var writer = XmlWriter.Create(newWorkbookPart.GetStream(),
new XmlWriterSettings {
Encoding = Encoding.UTF8, CloseOutput = true
}))
{
writer.WriteStartDocument(true);
while (reader.Read())
{
//if (reader.ElementType == typeof(AlternateContent))
//{
// var alternate = (AlternateContent)reader.LoadCurrentElement();
// continue;
//}
if (reader.ElementType == typeof(Excel.DefinedName))
{
var name = (Excel.DefinedName)reader.LoadCurrentElement();
if (!string.IsNullOrEmpty(name.InnerText))
{
var split = name.InnerText.Split('!');
if (split.Length == 2)
{
var sheet = split[0].Trim('\'');
var code = param.ParseCode(name.Name);
if (code != null)
{
if (!cacheNames.TryGetValue(sheet, out var names))
{
cacheNames[sheet] = names = new Dictionary <string, DefinedName>();
}
var defName = new DefinedName
{
Name = name.Name,
Sheet = sheet,
Reference = split[1],
Code = code
};
names.Add(defName.Range.Start.ToString(), defName);
}
}
}
WriteElement(writer, name);
}
else if (reader.ElementType == typeof(Excel.Sheet))
{
var sheet = (Excel.Sheet)reader.LoadCurrentElement();
sheetList.Add(sheet);
WriteElement(writer, sheet);
}
else if (reader.IsStartElement)
{
WriteStartElement(writer, reader);
}
else if (reader.IsEndElement)
{
writer.WriteEndElement();
}
}
writer.WriteEndDocument();
writer.Flush();
}
}
/// <summary>
/// Saves the current document.
/// </summary>
/// <param name="location">The location where the document is to be saved.</param>
protected void SaveDocument(string location)
{
// Save the document to it-self
_xDocument.Save(_workBookPart.GetStream(System.IO.FileMode.Create, FileAccess.ReadWrite));
}
