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();
            }
        }