Open XML操作Excel——生成Excel、插入文本、删除单元格、插入公式、创建样式

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;

        }