OpenXml To Excel之创建文档2

上篇讲到了创建文档的其中一种方式(通过模板创建)，本篇将介绍完全由代码创建。

创建sheet

public static void CreateSpreadsheetWorkbook(string filepath){    // Create a spreadsheet document by supplying the filepath.    // By default, AutoSave = true, Editable = true, and Type = xlsx.    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>();    worksheetPart.Worksheet = new Worksheet(new 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 = "mySheet" };    sheets.Append(sheet);    workbookpart.Workbook.Save()    // Close the document.    spreadsheetDocument.Close();}

获取sheetData,填充数据这里不在具体讲，上篇中已经提及。

这里补充一下如何在单元格中插入公式

IEnumerable<Cell> cells = row.Elements<Cell>().Where(c => c.CellReference.Value == cellRef);                    if (cells.Count() == 0)                        return;                    else                    {                        Cell cell = cells.First();                        string startCell = GetCellReference(i);                        string endCell = GetCellReference(i) + (rowCount + 2);                        string formula = "=SUM(" + startCell + ":" + endCell + ")";                        cell.CellFormula = new CellFormula { CalculateCell = true, Text = formula };                    }

如何Merge单元格

public static void MergeSpeCells(string docName, string sheetName, string startCellName, string endCellName)        {            using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))            {                Worksheet worksheet = GetWorksheet(document, sheetName);                if (worksheet == null || string.IsNullOrEmpty(startCellName) || string.IsNullOrEmpty(endCellName))                {                    return;                }                //check cell exist                CreateSpreadsheetCellIfNotExist(worksheet, startCellName);                CreateSpreadsheetCellIfNotExist(worksheet, endCellName);                MergeCells mergeCells;                if (worksheet.Elements<MergeCells>().Count() > 0)                {                    mergeCells = worksheet.Elements<MergeCells>().First();                }                else                {                    mergeCells = new MergeCells();                    //insert mergecells into worksheet                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());                }                //insert mergecell                MergeCell mc = new MergeCell() { Reference = new StringValue(startCellName + ":" + endCellName) };                mergeCells.Append(mc);                worksheet.Save();            }        }