Open XML 资料

目前发现Open XML的SDK有两个版本，SDK2.0和SDK2.5。

Open XML 2.0的帮助文档： https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2010/ff478153(v=office.14)

Open XML 2.5的帮助文档： https://docs.microsoft.com/zh-cn/office/open-xml/spreadsheets

安装OpenXml 2.11.0

通过NuGet安装OpenXml 2.11.0

https://www.nuget.org/packages/DocumentFormat.OpenXml/2.11.

生成Excel、插入文本、插入公式（cell5）

 private static void CreateExcel(string filePath,string sheetName)
            using (SpreadsheetDocument spreadsheetDocument=SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)) 
                //添加workbook文件,并创建workbook节点
                WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();
                //在workbook里添加sheetdata节点
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData());
                //添加sheets文件夹
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                //添加sheet文件
                Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheetName };
                sheets.Append(sheet);
                //给SheetData添加数据
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                Cell cell1 = new Cell() { CellValue = new CellValue("1"), DataType = new EnumValue<CellValues>(CellValues.Number),CellReference="B2" };
                Cell cell2 = new Cell() { CellValue = new CellValue("2"), DataType = new EnumValue<CellValues>(CellValues.String), CellReference = "C2" };
                Cell cell3 = new Cell() { CellValue = new CellValue("1"), DataType = new EnumValue<CellValues>(CellValues.Boolean), CellReference = "D2" };
                                Cell cell5 = new Cell() { CellFormula = new CellFormula("SUM(B2:E2)"), DataType = new EnumValue<CellValues>(CellValues.InlineString), CellReference = "F2" };
                Row row = new Row(new List<Cell>() { cell1, cell2, cell3, cell4,cell5 }) {RowIndex=2 };
                sheetData.Append(row);
                workbookPart.Workbook.Save();
删除特定的行列的单元格
 
 private static void DeleteTextFromCell(string docName, string sheetName, string colName, uint rowIndex)
            // Open the document for editing.
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
                IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
                if (sheets.Count() == 0)
                    // The specified worksheet does not exist.
                    return;
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
                // Get the cell at the specified column and row.
                Cell cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);
                if (cell == null)
                    // The specified cell does not exist.
                    return;
                cell.Remove();
                worksheetPart.Worksheet.Save();
        private static Cell GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)
            IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex);
            if (rows.Count() == 0)
                // A cell does not exist at the specified row.
                return null;
            IEnumerable<Cell> cells = rows.First().Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0);
            if (cells.Count() == 0)
                // A cell does not exist at the specified column, in the specified row.
                return null;
            return cells.First();
                WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
                // add styles to sheet
                workbookStylesPart.Stylesheet = CreateStylesheet();
                workbookStylesPart.Stylesheet.Save();
 
 private static Stylesheet CreateStylesheet()
            Stylesheet stylesheet = new Stylesheet();
            // blank font list
            stylesheet.Fonts = new Fonts();
            //stylesheet.Fonts.Count = 1;
            Font font1 = new Font();
            FontSize fontSize1 = new FontSize() { Val = 12D };
            Color color = new Color() { Rgb= HexBinaryValue.FromString("c8efd1") };
            FontName fontName = new FontName() { Val = "宋体" };
            FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 1 };
            FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
            font1.Append(fontSize1);
            font1.Append(color);
            font1.Append(fontName);
            font1.Append(fontScheme1);
            font1.Append(fontFamilyNumbering1);
            stylesheet.Fonts.AppendChild(font1);
            // create fills
            stylesheet.Fills = new Fills();
            // create a solid red fill
            var solidRed = new PatternFill() { PatternType = PatternValues.Solid };
            solidRed.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("c8efd1") }; // red fill
            solidRed.BackgroundColor = new BackgroundColor { Indexed = 64 };
            Fill fill3 = new Fill();
            PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
            ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "FFFF0000" };
            BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };
            patternFill3.Append(foregroundColor1);
            patternFill3.Append(backgroundColor1);
            fill3.Append(patternFill3);
            stylesheet.Fills.AppendChild(fill3);
            stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); // required, reserved by Excel
            stylesheet.Fills.AppendChild(new Fill { PatternFill = solidRed }); // required, reserved by Excel
            stylesheet.Fills.Count = 3;
            // blank border list
            stylesheet.Borders = new Borders();
            stylesheet.Borders.Count = 1;
            //no border
            stylesheet.Borders.AppendChild(new Border());
            //cuntom border
            stylesheet.Borders.AppendChild(new Border()
                BottomBorder = new BottomBorder() { Style = new EnumValue<BorderStyleValues>(BorderStyleValues.Thin) },
                RightBorder = new RightBorder() { Style = new EnumValue<BorderStyleValues>(BorderStyleValues.Thin) },
                LeftBorder = new LeftBorder() { Style = new EnumValue<BorderStyleValues>(BorderStyleValues.Thin) },
                TopBorder = new TopBorder() { Style = new EnumValue<BorderStyleValues>(BorderStyleValues.Thin) }
            // blank cell format list
            stylesheet.CellStyleFormats = new CellStyleFormats();
            stylesheet.CellStyleFormats.Count = 1;
            stylesheet.CellStyleFormats.AppendChild(new CellFormat());
            // cell format list
            stylesheet.CellFormats = new CellFormats();
            // empty one for index 0, seems to be required
            stylesheet.CellFormats.AppendChild(new CellFormat());
            // cell format references style format 0, font 0, border 0, fill 2 and applies the fill
            stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 1, FillId = 0, ApplyFill = true }).AppendChild(new Alignment { Horizontal = new EnumValue<DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues>(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center) });
            stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 1, FillId = 2, ApplyFill = true, ApplyBorder = true }).AppendChild(new Alignment { Horizontal = new EnumValue<DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues>(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center) });
            stylesheet.CellFormats.Count = 3;
            return stylesheet;
				
一、Office Open XML 概述
   office open xml是一种新的文件格式，是微软office2007以后的新的文件储存格式,较之以前的二进制储存格式，它有很多优点，1融合zip压缩技术，使得占有更小的存储空间；2即时部分文件损坏也不妨碍其它文件的获取。
二、Office Open XML使用步骤
首先，下载并安装OpenXMLSDKv2.msi和OpenXMLSDKT
				
这个例子比较简单，没有考虑格式之类的问题。
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using S...
				
要使用OpenXml首先要下载最新的Open XML Format SDK 2.0。具体的导入openxml的dll，去网上搜，很多
1.我个人写的XmlHelp类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Data...