Copy/Paste after creating an Excel file using OpenXml

Question

I'm using OpenXml to generate excel files and after swimming in tons of different sample codes and SDK Productivity Tool, finally managed to get what I wanted. There's just one thing I can't get around. When I open my files using Excel and try to copy/paste cells to another Excel file, I get "That command cannot be used on multiple selections." It's definitely not a non-adjacent selection problem since I can't do it even with one single cell. Moreover, if I save my file with Excel and re-open it, the problem goes away so I'm thinking it might be related to the way I have created the file. Any suggestions would be appreciated. Here's the code I'm using to generate my file:

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

I have run into this problem and found it was a result of not appending a BookViews object to the Workbook. The BookViews must be appended before the Sheets object:

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