openxml - 使用 OpenXml 创建 Excel 文件后复制/粘贴

Question

我正在使用 OpenXml 生成 Excel 文件，在研究了大量不同的示例代码和 SDK Productivity Tool 后，终于得到了我想要的东西。只有一件事我无法回避。当我使用 Excel 打开文件并尝试将单元格复制/粘贴到另一个 Excel 文件时，我收到“该命令无法用于多项选择”。这绝对不是一个非相邻选择问题，因为即使只有一个单元格我也无法做到这一点。此外，如果我用 Excel 保存文件并重新打开它，问题就会消失，所以我认为这可能与我创建文件的方式有关。任何建议，将不胜感激。这是我用来生成文件的代码:

public class ExcelGenerator
{
    #region Fields

    private List<Tuple<DbDataReader, string>> datasource;
    private string[] cellReferences = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
    private SpreadsheetDocument workbook;
    Hashtable stringItemIndexMap;
    int nextIndex;

    private uint numberStyleID;
    private uint decimalStyleID;
    private uint textStyleID;
    private uint headerTextStyleID;

    #endregion

    #region Constructors

    public ExcelGenerator(DbDataReader reader, string sheetName) : this(new List<Tuple<DbDataReader, string>>() { new Tuple<DbDataReader, string>(reader, sheetName) }) { }

    public ExcelGenerator(List<Tuple<DbDataReader, string>> datasource)
    {
        if (datasource == null)
        {
            throw new Exception("The value of 'datasource' cannot be null.");
        }

        this.datasource = datasource;
        this.stringItemIndexMap = new Hashtable();
        cellReferences = cellReferences.Concat(cellReferences.SelectMany(a => cellReferences.Select(b => a + b))).ToArray();
    }

    #endregion

    #region Properties

    public Dictionary<string, string> FieldTitleMappings { get; set; }

    public string[] FieldsToExport { get; set; }

    #endregion

    #region Methods

    public void Generate(string path)
    {
        if (string.IsNullOrEmpty(path))
        {
            throw new ArgumentNullException("path");
        }

        using (workbook = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
        {
            Generate(workbook);
        }
    }

    public void Generate(Stream workBookStream)
    {
        if (workBookStream == null)
        {
            throw new ArgumentNullException("workBookStream");
        }

        using (workbook = SpreadsheetDocument.Create(workBookStream, SpreadsheetDocumentType.Workbook))
        {
            Generate(workbook);
        }
    }

    public void Generate(SpreadsheetDocument workbook)
    {
        if (workbook == null)
        {
            throw new ArgumentNullException("workbook");
        }


        workbook.AddWorkbookPart();
        workbook.WorkbookPart.Workbook = new Workbook();
        workbook.WorkbookPart.AddNewPart<SharedStringTablePart>();

        var worksheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
        var sheets = workbook.WorkbookPart.Workbook.AppendChild(new Sheets());

        workbook.WorkbookPart.AddNewPart<WorkbookStylesPart>();
        workbook.WorkbookPart.WorkbookStylesPart.Stylesheet = CreateStylesheet();
        workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();

        uint sheetCounter = 0;

        foreach (var ds in datasource)
        {
            var reader = ds.Item1;

            Worksheet worksheet = new Worksheet();
            SheetData sheetData = new SheetData();

            SheetViews sheetViews = new SheetViews();

            SheetView sheetView = new SheetView() { RightToLeft = true, TabSelected = true, WorkbookViewId = (UInt32Value)0U };
            Selection selection = new Selection() { ActiveCell = "A1", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1" } };

            sheetView.Append(selection);

            sheetViews.Append(sheetView);
            worksheet.Append(sheetViews);

            #region Generate header

            uint counter = 0;

            var headerRow = new Row();
            headerRow.RowIndex = ++counter;

            var schema = reader.GetSchemaTable();
            var dataTypes = new List<Type>();

            var columnHeaders = new List<string>();

            foreach (DataRow r in schema.Rows)
            {
                var c = new Cell();
                var headerText = r["ColumnName"].ToString();

                if (FieldTitleMappings != null && FieldTitleMappings.ContainsKey(headerText))
                {
                    headerText = FieldTitleMappings[headerText];
                }

                c.DataType = CellValues.String;
                c.CellValue = new CellValue(headerText);
                c.StyleIndex = headerTextStyleID;

                headerRow.AppendChild(c);

                dataTypes.Add((Type)r["DataType"]);
                columnHeaders.Add(headerText);
            }

            CreateColumnsFromHeaderText(worksheet, columnHeaders);

            sheetData.AppendChild(headerRow);

            #endregion

            #region Populate contents

            var fieldsCount = reader.FieldCount;

            while (reader.Read())
            {
                object[] currentRowData = new object[fieldsCount];
                reader.GetValues(currentRowData);

                var row = new Row();
                row.RowIndex = ++counter;

                for (var i = 0; i < fieldsCount; i++)
                {
                    row.AppendChild(CreateCell(currentRowData[i], dataTypes[i], cellReferences[i] + row.RowIndex));
                }

                sheetData.AppendChild(row);
            }

            #endregion

            workbook.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();

            worksheet.AppendChild(sheetData);
            worksheetPart.Worksheet = worksheet;
            worksheetPart.Worksheet.Save();

            sheets.AppendChild(new Sheet()
            {
                Id = workbook.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = ++sheetCounter,
                Name = ds.Item2
            });
        }

        workbook.WorkbookPart.Workbook.Save();
    }

    private Stylesheet CreateStylesheet()
    {
        Stylesheet ss = new Stylesheet();

        Fonts fts = new Fonts();

        Font ft = new Font();
        FontName ftn = new FontName();
        ftn.Val = "Tahoma";
        FontSize ftsz = new FontSize();
        ftsz.Val = 10;
        ft.FontName = ftn;
        ft.FontSize = ftsz;
        fts.Append(ft);

        ft = new Font();
        ftn = new FontName();
        ftn.Val = "Tahoma";
        ftsz = new FontSize();
        ftsz.Val = 10;
        ft.FontName = ftn;
        ft.FontSize = ftsz;
        ft.Bold = new Bold { Val = true };
        fts.Append(ft);

        fts.Count = (uint)fts.ChildElements.Count;

        Fills fills = new Fills() { Count = (UInt32Value)3U };

        Fill fill1 = new Fill();
        PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };

        fill1.Append(patternFill1);

        Fill fill2 = new Fill();
        PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };

        fill2.Append(patternFill2);

        Fill fill3 = new Fill();

        PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
        ForegroundColor foregroundColor1 = new ForegroundColor() { Theme = (UInt32Value)0U, Tint = -0.14999847407452621D };
        BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };

        patternFill3.Append(foregroundColor1);
        patternFill3.Append(backgroundColor1);

        fill3.Append(patternFill3);

        fills.Append(fill1);
        fills.Append(fill2);
        fills.Append(fill3);

        Borders borders = new Borders() { Count = (UInt32Value)2U };

        Border border1 = new Border();
        LeftBorder leftBorder1 = new LeftBorder();
        RightBorder rightBorder1 = new RightBorder();
        TopBorder topBorder1 = new TopBorder();
        BottomBorder bottomBorder1 = new BottomBorder();
        DiagonalBorder diagonalBorder1 = new DiagonalBorder();

        border1.Append(leftBorder1);
        border1.Append(rightBorder1);
        border1.Append(topBorder1);
        border1.Append(bottomBorder1);
        border1.Append(diagonalBorder1);

        Border border2 = new Border();

        LeftBorder leftBorder2 = new LeftBorder() { Style = BorderStyleValues.Thin };
        Color color2 = new Color() { Indexed = (UInt32Value)64U };

        leftBorder2.Append(color2);

        RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin };
        Color color3 = new Color() { Indexed = (UInt32Value)64U };

        rightBorder2.Append(color3);

        TopBorder topBorder2 = new TopBorder() { Style = BorderStyleValues.Thin };
        Color color4 = new Color() { Indexed = (UInt32Value)64U };

        topBorder2.Append(color4);

        BottomBorder bottomBorder2 = new BottomBorder() { Style = BorderStyleValues.Thin };
        Color color5 = new Color() { Indexed = (UInt32Value)64U };

        bottomBorder2.Append(color5);
        DiagonalBorder diagonalBorder2 = new DiagonalBorder();

        border2.Append(leftBorder2);
        border2.Append(rightBorder2);
        border2.Append(topBorder2);
        border2.Append(bottomBorder2);
        border2.Append(diagonalBorder2);

        borders.Append(border1);
        borders.Append(border2);

        CellStyleFormats csfs = new CellStyleFormats();
        CellFormat cf = new CellFormat();
        cf.NumberFormatId = 0;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Right };
        csfs.Append(cf);
        csfs.Count = (uint)csfs.ChildElements.Count;

        uint iExcelIndex = 164;
        NumberingFormats nfs = new NumberingFormats();
        CellFormats cfs = new CellFormats();

        cf = new CellFormat();
        cf.NumberFormatId = 0;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cf.Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Right };
        cfs.Append(cf);

        cf = new CellFormat();
        cf.NumberFormatId = 0;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 1;
        cf.FormatId = 0;
        cf.Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Right };
        cfs.Append(cf);

        textStyleID = (uint)(cfs.ChildElements.Count - 1);

        NumberingFormat nf;
        nf = new NumberingFormat();
        nf.NumberFormatId = iExcelIndex++;
        nf.FormatCode = "dd/mm/yyyy hh:mm:ss";
        nfs.Append(nf);
        cf = new CellFormat();
        cf.NumberFormatId = nf.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 1;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = true;
        cf.Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Right };
        cfs.Append(cf);

        nf = new NumberingFormat();
        nf.NumberFormatId = iExcelIndex++;
        nf.FormatCode = "#,##0";
        nfs.Append(nf);
        cf = new CellFormat();
        cf.NumberFormatId = nf.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 1;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = true;
        cf.Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Right };
        cfs.Append(cf);

        decimalStyleID = (uint)(cfs.ChildElements.Count - 1);

        // #,##0.00 is also Excel style index 4
        nf = new NumberingFormat();
        nf.NumberFormatId = iExcelIndex++;
        nf.FormatCode = "#";
        nfs.Append(nf);
        cf = new CellFormat();
        cf.NumberFormatId = nf.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 1;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = true;
        cf.Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Right };
        cfs.Append(cf);

        numberStyleID = (uint)(cfs.ChildElements.Count - 1);

        cf = new CellFormat();
        cf.FontId = 1;
        cf.FillId = 2;
        cf.BorderId = 1;
        cf.FormatId = 0;
        cf.Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Right };
        cf.ApplyNumberFormat = true;

        cfs.Append(cf);

        headerTextStyleID = (uint)(cfs.ChildElements.Count - 1);

        nfs.Count = (uint)nfs.ChildElements.Count;
        cfs.Count = (uint)cfs.ChildElements.Count;

        ss.Append(nfs);
        ss.Append(fts);
        ss.Append(fills);
        ss.Append(borders);
        ss.Append(csfs);
        ss.Append(cfs);

        CellStyles css = new CellStyles();
        CellStyle cs = new CellStyle();
        cs.Name = "Normal";
        cs.FormatId = 0;
        cs.BuiltinId = 0;
        css.Append(cs);
        css.Count = (uint)css.ChildElements.Count;
        ss.Append(css);

        DifferentialFormats dfs = new DifferentialFormats();
        dfs.Count = 0;
        ss.Append(dfs);

        TableStyles tss = new TableStyles();
        tss.Count = 0;
        tss.DefaultTableStyle = "TableStyleMedium9";
        tss.DefaultPivotStyle = "PivotStyleLight16";
        ss.Append(tss);

        return ss;
    }

    private ForegroundColor TranslateForeground(System.Drawing.Color fillColor)
    {
        return new ForegroundColor()
        {
            Rgb = new HexBinaryValue()
            {
                Value =
                    System.Drawing.ColorTranslator.ToHtml(
                    System.Drawing.Color.FromArgb(
                        fillColor.A,
                        fillColor.R,
                        fillColor.G,
                        fillColor.B)).Replace("#", "")
            }
        };
    }

    private Cell CreateCell(object value, Type dataType, string cellReference)
    {
        var cell = new Cell();
        cell.CellReference = cellReference;

        value = value ?? "";

        if (dataType == typeof(decimal))
        {
            cell.DataType = CellValues.Number;
            cell.CellValue = new CellValue(value.ToString());
            cell.StyleIndex = decimalStyleID;
        }
        else if (dataType == typeof(int) || dataType == typeof(short) || dataType == typeof(long) ||
                 dataType == typeof(uint) || dataType == typeof(ushort) || dataType == typeof(ulong))
        {
            cell.DataType = CellValues.Number;
            cell.CellValue = new CellValue(value.ToString());
            cell.StyleIndex = numberStyleID;
        }
        else
        {
            if (value is DateTime)
            {
                value = DateTimeUtil.ToShamsiDate((DateTime)value);
            }

            cell.DataType = CellValues.SharedString;
            cell.CellValue = new CellValue(InsertSharedStringItem(value.ToString()));
            cell.StyleIndex = textStyleID;
        }

        return cell;
    }


    private string InsertSharedStringItem(string value)
    {
        if (workbook.WorkbookPart.SharedStringTablePart.SharedStringTable == null)
        {
            workbook.WorkbookPart.SharedStringTablePart.SharedStringTable = new SharedStringTable();
            nextIndex = 0;
        }

        var i = 0;

        var index = stringItemIndexMap[value] as string;

        if (index == null)
        {
            workbook.WorkbookPart.SharedStringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new Text(value)));

            index = nextIndex.ToString();
            stringItemIndexMap.Add(value, index);

            nextIndex++;
        }

        return index;
    }

    public void CreateColumnsFromHeaderText(Worksheet workSheet, IEnumerable<string> headerTexts)
    {
        Columns columns = new Columns();
        uint index = 1;
        foreach (var sILT in headerTexts)
        {
            double fSimpleWidth = 0.0f;
            double fWidthOfZero = 0.0f;
            double fDigitWidth = 0.0f;
            double fMaxDigitWidth = 0.0f;
            double fTruncWidth = 0.0f;

            System.Drawing.Font drawfont = new System.Drawing.Font("Tahoma", 10);

            System.Drawing.Graphics g = System.Drawing.Graphics.FromImage(new System.Drawing.Bitmap(200, 200));
            fWidthOfZero = (double)g.MeasureString("0", drawfont).Width;
            fSimpleWidth = (double)g.MeasureString(sILT, drawfont).Width;
            fSimpleWidth = fSimpleWidth / fWidthOfZero;

            for (int i = 0; i < 10; ++i)
            {
                fDigitWidth = (double)g.MeasureString(i.ToString(), drawfont).Width;
                if (fDigitWidth > fMaxDigitWidth)
                {
                    fMaxDigitWidth = fDigitWidth;
                }
            }
            g.Dispose();

            // Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256
            fTruncWidth = Math.Truncate((sILT.ToCharArray().Count() * fMaxDigitWidth + 40.0) / fMaxDigitWidth * 256.0) / 256.0;

            var column = new Column();
            column.Min = index;
            column.Max = index++;
            column.Width = fTruncWidth;
            column.CustomWidth = true;

            columns.Append(column);
        }

        workSheet.Append(columns);
    }

    #endregion
}

Answer 1

我遇到了这个问题，发现这是由于未将 BookViews 对象附加到工作簿而导致的。 BookViews 必须附加在 Sheets 对象之前:

workbook.Append(new BookViews(new WorkbookView()));