Using Open Xml to create xlsx file for Excel 2013

Question

I need to create an Excel file xlsx. So, i decided to use Open xml (Open Xml 2.0 because i have to use .net 3.5).

After some research about open xml, i created this class :

public class ExcelFileManager : FileManager
{
    private SpreadsheetDocument _package;

    private Row _currentRow;
    private SheetData _sheetData;

    private int? _cellColumnN2;
    private int _cellColumnN1;
    private int _cellRow;

    public ExcelFileManager(string path) 
        : base(path)
    {
        _package = SpreadsheetDocument.Create(path, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);

        _cellColumnN1 = 65;
        _cellRow = 1;

        CreateSpreadSheet();
    }

    private void CreateSpreadSheet()
    {
        var workbookPart1 = _package.AddWorkbookPart();

        workbookPart1.Workbook = new Workbook();
        var worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>();
        worksheetPart1.Worksheet = new Worksheet();

        var sheets = _package.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
        var sheet = new Sheet()
        {
            Id = _package.WorkbookPart.
                GetIdOfPart(worksheetPart1),
            SheetId = 1,
            Name = "Users"
        };
        sheets.Append(sheet);

        _sheetData = new SheetData();
        _currentRow = new Row();

        _sheetData.Append(_currentRow);
        worksheetPart1.Worksheet.Append(_sheetData);
    }

    public override void AddHeader(IEnumerable<string> columnsName)
    {
        if (columnsName == null || !columnsName.Any())
            throw new ArgumentNullException("columnsName");

        try
        {
            AddRow(columnsName.ToArray());
        }
        catch (Exception e)
        {
            Tracing.Application.TraceError(-1, "An error occured when adding header to excel file", e);

            throw;
        }
    }

    public override void AddRow(params object[] values)
    {
        if (values == null || !values.Any())
            throw new ArgumentNullException("values");

        try
        {
            var listCells = new List<OpenXmlElement>();

            foreach (var item in values)
            {
                listCells.Add(CreateCellRow(item));
            }

            _currentRow.Append(listCells);

            AddNewRow();
        }
        catch (Exception e)
        {
            Tracing.Application.TraceError(-1, "An error occured when adding row to excel file", e);

            throw;
        }
    }

    public override void AddCell(object value)
    {
        try
        {
            var cell = CreateCellRow(value);

            _currentRow.Append(cell);
        }
        catch (Exception e)
        {
            Tracing.Application.TraceError(-1, "An error occured when adding cell to excel file", e);

            throw;
        }
    }

    public override void AddNewRow()
    {
        _currentRow = new Row();

        _sheetData.Append(_currentRow);

        IncreaseRow();
    }

    #region Utils
    private Cell CreateCellRow(object value)
    {
        Tracing.Application.TraceInformation("Add value \"{0}\" in cell {1}", value, GetCellLocation());

        Cell cell = new Cell();

        if (value == null)
        {
            cell.CellValue = new CellValue(string.Empty);
            cell.DataType = CellValues.String;
        }
        else
        {                
            cell.CellValue = new CellValue(value.ToString());
            cell.DataType = GetCellType(value); 
        }

        cell.CellReference = GetCellLocation();

        IncreaseCellColumn();

        return cell;
    }

    private CellValues GetCellType(object value)
    {
        CellValues type = CellValues.String;

        if (value.GetType() == typeof(DateTime) || value.GetType() == typeof(DateTime?))
            type = CellValues.Date;

        if (value.GetType() == typeof(bool) || value.GetType() == typeof(bool?))
            type = CellValues.Boolean;

        if (IsNumericType(value.GetType()))
            type = CellValues.Number;

        return type;
    }

    public bool IsNumericType(Type type)
    {
        TypeCode typeCode = Type.GetTypeCode(type);

        //The TypeCode of numerical types are between SByte (5) and Decimal (15).
        return (int)typeCode >= 5 && (int)typeCode <= 15;
    }

    private void IncreaseCellColumn()
    {
        if (_cellColumnN1 < 90)
            _cellColumnN1++;
        else
        {
            _cellColumnN1 = 65;

            if (!_cellColumnN2.HasValue)
                _cellColumnN2 = 65;
            else
                _cellColumnN2++;
        }
    }

    private void IncreaseRow()
    {
        _cellRow++;
        _cellColumnN1 = 65;
        _cellColumnN2 = null;
    }

    private string GetCellLocation()
    {
        Char? c1 = null;

        if (_cellColumnN2.HasValue)
            c1 = (char)_cellColumnN2;

        Char c2 = (char)_cellColumnN1;

        return string.Format("{0}{1}{2}", c1, c2, _cellRow);
    }
    #endregion

    #region IDisposable
    public override void Dispose()
    {
        if (_package != null)
        {
            _package.WorkbookPart.Workbook.Save();
            _package.Close();
        }
    }
    #endregion
}

When i create an excel file using this class, an error message is displayed : "We found an error in the content..." Excel repairs the file and it become openable. Why do i have this error in Excel ?

I looked a bit in the xml file sheet.xml contained in the xlsx file, there are few differences :

Generated file :

<x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:sheetData>
    <x:row>
      <x:c r="A1" t="str">
        <x:v>a string</x:v>
      </x:c>
      <x:c r="B1" t="str">
        <x:v>another string</x:v>
      </x:c>
    </x:row>
    ...
  </x:sheetData>
</x:worksheet>

Repaired file :

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
  <dimension ref="A1:S12"/>
  <sheetViews>
    <sheetView tabSelected="1" workbookViewId="0">
      <selection activeCell="I11" sqref="I11"/>
    </sheetView>
  </sheetViews>
  <sheetFormatPr baseColWidth="10" defaultRowHeight="15" x14ac:dyDescent="0.25"/>
  <sheetData>
    <row r="1" spans="1:19" x14ac:dyDescent="0.25">
      <c r="A1" t="s">
        <v>0</v>
      </c>
      <c r="B1" t="s">
        <v>1</v>
      </c>
      <c r="C1" t="s">
        <v>2</v>
      </c>
   </row>
   ...
 </sheetData>
 <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

I tried to change namespaces, but it didn't work. I know that shared strings are used in the second xml, but i don't think it's the problem. ^^ I don't care about style like page margin, font,... (may be auto adjust width for columns would be useful ^^)

I just want a well formed xlsx file.

Do i have a mistake or an omission in my code ?

Sorry for my english by the way '^^

Thanks.

Answer 1

Dates are stored as numbers with type number, but with a style applied to them. Booleans are also stored as numbers, but with type boolean.

private Cell CreateCellRow(object value)
{
    Cell cell = new Cell();
    cell.CellReference = GetCellLocation();
    IncreaseCellColumn();

    if (value == null)
    {
        cell.DataType = CellValues.InlineString;
        cell.InlineString = new InlineString
        {
            Text = new Text("")
        };
        return cell;
    }

    switch (Type.GetTypeCode(value.GetType()))
    {
        case TypeCode.DateTime:
            cell.StyleIndex = 1;
            cell.CellValue = new CellValue(((DateTime)value).ToOADate().ToString(CultureInfo.InvariantCulture));
            break;
        case TypeCode.String:
            cell.DataType = CellValues.InlineString;
            cell.InlineString = new InlineString {Text = new Text((string) value)};
            break;
        case TypeCode.Boolean:
            cell.DataType = CellValues.Boolean;
            cell.CellValue = new CellValue((bool)value ? "1" : "0");
            break;
        case TypeCode.Byte:
        case TypeCode.Int16:
        case TypeCode.Int32:
        case TypeCode.Int64:
        case TypeCode.SByte:
        case TypeCode.UInt16:
        case TypeCode.UInt32:
        case TypeCode.UInt64:
        case TypeCode.Decimal:
        case TypeCode.Double:
        case TypeCode.Single:
            cell.CellValue = new CellValue(((IFormattable)value).ToString(null, CultureInfo.InvariantCulture));
            break;
        default:
            throw new Exception("Unrecognized type: " + value.GetType().FullName);
    }

    return cell;
}

To create the style needed (snipped from another answer):

var stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = CreateStylesheet();

private Stylesheet CreateStylesheet()
{
    var stylesheet = new Stylesheet();

    // Default Font
    var fonts = new Fonts() { Count = 1, KnownFonts = BooleanValue.FromBoolean(true) };
    var font = new Font
    {
        FontSize = new FontSize() { Val = 11 },
        FontName = new FontName() { Val = "Calibri" },
        FontFamilyNumbering = new FontFamilyNumbering() { Val = 2 },
        FontScheme = new FontScheme() { Val = new EnumValue<FontSchemeValues>(FontSchemeValues.Minor) }
    };
    fonts.Append(font);
    stylesheet.Append(fonts);

    // Default Fill
    var fills = new Fills() { Count = 1 };
    var fill = new Fill();
    fill.PatternFill = new PatternFill() { PatternType = new EnumValue<PatternValues>(PatternValues.None) };
    fills.Append(fill);
    stylesheet.Append(fills);

    // Default Border
    var borders = new Borders() { Count = 1 };
    var border = new Border
    {
        LeftBorder = new LeftBorder(),
        RightBorder = new RightBorder(),
        TopBorder = new TopBorder(),
        BottomBorder = new BottomBorder(),
        DiagonalBorder = new DiagonalBorder()
    };
    borders.Append(border);
    stylesheet.Append(borders);

    // Default cell format and a date cell format
    var cellFormats = new CellFormats() { Count = 2 };

    var cellFormatDefault = new CellFormat { NumberFormatId = 0, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0 };
    cellFormats.Append(cellFormatDefault);

    var cellFormatDate = new CellFormat { NumberFormatId = 22, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true) };
    cellFormats.Append(cellFormatDate);

    stylesheet.Append(cellFormats);

    return stylesheet;
}