Office Open XML Demo

Office Open XML 例子下载

http://www.brsbox.com/filebox/down/fc/db22ed6c6966dc15c3df0f8859d9c960

 

Excel 文档结构

创建Excel文档

const string xmlnsNamespaceURI = @"http://www.w3.org/2000/xmlns/"; const string spreadsheetML = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main"; const string relationSchema = @"http://schemas.openxmlformats.org/officeDocument/2006/relationships"; const string workbookContentType = @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"; const string worksheetContentType = @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"; const string stylesheetContentType = @"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"; const string stringsContentType = @"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"; public static void CreateEmptyExcel(string fileName) { // create the workbook "start part" XmlDocument xmlStartPart = new XmlDocument(); // create the workbook tag XmlElement tagWorkbook = xmlStartPart.CreateElement("workbook", spreadsheetML); // Add the relationships namespace XmlAttribute nsAttribute = xmlStartPart.CreateAttribute("xmlns", "r", xmlnsNamespaceURI); nsAttribute.Value = relationSchema; tagWorkbook.Attributes.Append(nsAttribute); xmlStartPart.AppendChild(tagWorkbook); // create the sheets tag XmlElement tagSheets = xmlStartPart.CreateElement("sheets", spreadsheetML); tagWorkbook.AppendChild(tagSheets); // create the sheet tag (must have at least one worksheet), and set name/SheetId attributes XmlElement tagSheet = xmlStartPart.CreateElement("sheet", spreadsheetML); tagSheet.SetAttribute("name", "Sheet 1"); tagSheet.SetAttribute("sheetId", "1"); // set the r:id attribute tagSheet.SetAttribute("id", relationSchema, "rId1"); // insert the sheet tag with all attributes set as above tagSheets.AppendChild(tagSheet); // create the worksheet XmlDocument xmlWorksheet = new XmlDocument(); XmlElement tagWorksheet = xmlWorksheet.CreateElement("worksheet", spreadsheetML); XmlAttribute nsWS = xmlWorksheet.CreateAttribute("xmlns", "r", xmlnsNamespaceURI); nsWS.Value = relationSchema; tagWorksheet.Attributes.Append(nsWS); //tagWorksheet.SetAttribute("xmlns:r", relationSchema); xmlWorksheet.AppendChild(tagWorksheet); // create the sheetViews tag XmlElement tagSheetViews = xmlWorksheet.CreateElement("sheetViews", spreadsheetML); tagWorksheet.AppendChild(tagSheetViews); // create the sheet View tag XmlElement tagSheetView = xmlWorksheet.CreateElement("sheetView", spreadsheetML); tagSheetView.SetAttribute("workbookViewId", "0"); tagSheetViews.AppendChild(tagSheetView); // create the empty sheetData tag (must be present, but can be empty) XmlElement tagSheetData = xmlWorksheet.CreateElement("sheetData", spreadsheetML); tagWorksheet.AppendChild(tagSheetData); // 创建样式 // create the style sheet XmlDocument xmlStylesheet = new XmlDocument(); XmlElement tagStylesheet = xmlStylesheet.CreateElement("styleSheet", spreadsheetML); xmlStylesheet.AppendChild(tagStylesheet); // create the fonts tag XmlElement tagFonts = xmlStylesheet.CreateElement("fonts", spreadsheetML); tagFonts.SetAttribute("count", "1"); tagStylesheet.AppendChild(tagFonts); // create the font tag XmlElement tagFont = xmlStylesheet.CreateElement("font", spreadsheetML); tagFonts.AppendChild(tagFont); // create the sz tag XmlElement tagSz = xmlStylesheet.CreateElement("sz", spreadsheetML); tagSz.SetAttribute("val", "11"); tagFont.AppendChild(tagSz); // create the name tag XmlElement tagName = xmlStylesheet.CreateElement("name", spreadsheetML); tagName.SetAttribute("val", "Calibri"); tagFont.AppendChild(tagName); // create the fills tag XmlElement tagFills = xmlStylesheet.CreateElement("fills", spreadsheetML); tagFills.SetAttribute("count", "1"); tagStylesheet.AppendChild(tagFills); // create the fill tag XmlElement tagFill = xmlStylesheet.CreateElement("fill", spreadsheetML); tagFills.AppendChild(tagFill); // create the patternFill tag XmlElement tagPatternFill = xmlStylesheet.CreateElement("patternFill", spreadsheetML); tagPatternFill.SetAttribute("patternType", "none"); tagFill.AppendChild(tagPatternFill); // create the borders tag XmlElement tagBorders = xmlStylesheet.CreateElement("borders", spreadsheetML); tagFills.SetAttribute("count", "1"); tagStylesheet.AppendChild(tagBorders); // create the border tag XmlElement tagBorder = xmlStylesheet.CreateElement("border", spreadsheetML); tagBorders.AppendChild(tagBorder); // create the left tag XmlElement tagLeft = xmlStylesheet.CreateElement("left", spreadsheetML); tagBorder.AppendChild(tagLeft); // create the right tag XmlElement tagRight = xmlStylesheet.CreateElement("right", spreadsheetML); tagBorder.AppendChild(tagRight); // create the top tag XmlElement tagTop = xmlStylesheet.CreateElement("top", spreadsheetML); tagBorder.AppendChild(tagTop); // create the left tag XmlElement tagBottom = xmlStylesheet.CreateElement("bottom", spreadsheetML); tagBorder.AppendChild(tagBottom); // create the diagonal tag XmlElement tagDiagonal = xmlStylesheet.CreateElement("diagonal", spreadsheetML); tagBorder.AppendChild(tagDiagonal); // create the cellStyleXfs tag XmlElement tagCellStyleXfs = xmlStylesheet.CreateElement("cellStyleXfs", spreadsheetML); tagCellStyleXfs.SetAttribute("count", "1"); tagStylesheet.AppendChild(tagCellStyleXfs); // create the xf tag XmlElement tagXf = xmlStylesheet.CreateElement("xf", spreadsheetML); tagXf.SetAttribute("numFmtId", "0"); tagXf.SetAttribute("fontId", "0"); tagCellStyleXfs.AppendChild(tagXf); // create the cellXfs tag XmlElement tagCellXfs = xmlStylesheet.CreateElement("cellXfs", spreadsheetML); tagCellXfs.SetAttribute("count", "1"); tagStylesheet.AppendChild(tagCellXfs); // create the xf tag XmlElement tagXf2 = xmlStylesheet.CreateElement("xf", spreadsheetML); tagXf2.SetAttribute("numFmtId", "0"); tagXf2.SetAttribute("fontId", "0"); tagXf2.SetAttribute("xfId", "0"); tagCellXfs.AppendChild(tagXf2); // create the shared-strings table (with no entries in it) XmlDocument xmlStrings = new XmlDocument(); XmlElement tagSst = xmlStrings.CreateElement("sst", spreadsheetML); tagSst.SetAttribute("count", "0"); tagSst.SetAttribute("uniqueCount", "0"); xmlStrings.AppendChild(tagSst); // create the package (i.e., the document container) Package pkgOutputDoc = null; pkgOutputDoc = Package.Open(fileName, FileMode.Create, FileAccess.ReadWrite); // save a temporary part to create the default application/xml content type Uri uriDefaultContentType = new Uri("/default.xml", UriKind.Relative); PackagePart partTemp = pkgOutputDoc.CreatePart(uriDefaultContentType, "application/xml"); // save the main document part (workbook.xml) Uri uriStartPart = new Uri("/xl/workbook.xml", UriKind.Relative); PackagePart partWorkbookXML = pkgOutputDoc.CreatePart(uriStartPart, workbookContentType); StreamWriter streamStartPart = new StreamWriter(partWorkbookXML.GetStream(FileMode.Create, FileAccess.Write)); xmlStartPart.Save(streamStartPart); streamStartPart.Close(); pkgOutputDoc.Flush(); // save the worksheet (sheet1.xml) Uri uriWorksheet = new Uri("/xl/worksheets/sheet1.xml", UriKind.Relative); PackagePart partWorksheetXML = pkgOutputDoc.CreatePart(uriWorksheet, worksheetContentType); StreamWriter streamWorksheet = new StreamWriter(partWorksheetXML.GetStream(FileMode.Create, FileAccess.Write)); xmlWorksheet.Save(streamWorksheet); streamWorksheet.Close(); pkgOutputDoc.Flush(); // save the stylesheet (styles.xml) Uri uriStylesheet = new Uri("/xl/styles.xml", UriKind.Relative); PackagePart partStylesheetXML = pkgOutputDoc.CreatePart(uriStylesheet, stylesheetContentType); StreamWriter streamStylesheet = new StreamWriter(partStylesheetXML.GetStream(FileMode.Create, FileAccess.Write)); xmlStylesheet.Save(streamStylesheet); streamStylesheet.Close(); pkgOutputDoc.Flush(); // save the shared-strings table (sharedStrings.xml) Uri uriStrings = new Uri("/xl/sharedStrings.xml", UriKind.Relative); PackagePart partStrings = pkgOutputDoc.CreatePart(uriStrings, stringsContentType); StreamWriter streamStrings = new StreamWriter(partStrings.GetStream(FileMode.Create, FileAccess.Write)); xmlStrings.Save(streamStrings); streamStrings.Close(); pkgOutputDoc.Flush(); // create the relationship parts pkgOutputDoc.CreateRelationship(uriStartPart, TargetMode.Internal, relationSchema + "/officeDocument", "rId1"); partWorkbookXML.CreateRelationship(uriWorksheet, TargetMode.Internal, relationSchema + "/worksheet", "rId1"); partWorkbookXML.CreateRelationship(uriStylesheet, TargetMode.Internal, relationSchema + "/styles", "rId2"); partWorkbookXML.CreateRelationship(uriStrings, TargetMode.Internal, relationSchema + "/sharedStrings", "rId3"); // remove the temporary part that created the default xml content type pkgOutputDoc.DeletePart(uriDefaultContentType); // close the document pkgOutputDoc.Flush(); pkgOutputDoc.Close(); //FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write); //XmlWriter xw = XmlWriter.Create(fs, new XmlWriterSettings { Indent = true, NewLineChars = "/n" }); //xmlStylesheet.WriteTo(xw); //xw.Close(); //fs.Close(); } public static void CreateExcel(string fileName) { using (SpreadsheetDocument pkgDoc = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document. WorkbookPart workbookpart = pkgDoc.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook. Sheets sheets = workbookpart.Workbook.AppendChild<Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = workbookpart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" }; sheets.Append(sheet); workbookpart.Workbook.Save(); } }