Excel gives error

[jaimyjaimy]

I am creating 2 worksheets, the first sheet has some information in string format, the second sheet has a datetime in it.

Excel gives an error if create the sheet with string format first and the datetime second. If I turn around the order, so datetime sheet first and then string format second, it won't give an error.

Below is the C# code I used. (I used Clippit 1.13.5 also tried it on 1.4 and 1.5 and 1.7)

using Clippit.Excel;
using System.Data;

namespace Test_clippit_multiple_worksheets
{
    public partial class Form1 : Form
    {
        string directory;

        public Form1()
        {
            InitializeComponent();
            directory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
        }

        private void buttonWrong_Click(object sender, EventArgs e)
        {
            var filePath = Path.Combine(directory, $"ExportWrong.xltx");

            var worksheets = new List<WorksheetDfn>();
            Tuple<DataTable, DataTable> datatables = GetDataTables();

            worksheets.Add(CreateWorksheet(datatables.Item1));
            worksheets.Add(CreateWorksheet(datatables.Item2));

            CreateDocument(worksheets.ToArray(), filePath);
        }

        private void buttonCorrect_Click(object sender, EventArgs e)
        {
            var filePath = Path.Combine(directory, $"ExportCorrect.xltx");

            var worksheets = new List<WorksheetDfn>();
            Tuple<DataTable, DataTable> datatables = GetDataTables();

            //turned around the order, now it is okay... why???
            worksheets.Add(CreateWorksheet(datatables.Item2));
            worksheets.Add(CreateWorksheet(datatables.Item1));

            CreateDocument(worksheets.ToArray(), filePath);
        }

        private Tuple<DataTable, DataTable> GetDataTables()
        {
            DataTable dt1 = new DataTable("sheet1");
            dt1.Columns.Add("col1_string", typeof(string));
            dt1.Columns.Add("col2_string", typeof(string));

            dt1.Rows.Add("Hello", "world");

            DataTable dt2 = new DataTable("sheet2");
            dt2.Columns.Add("col1_date", typeof(DateTime));
            dt2.Columns.Add("col2_int", typeof(int));

            dt2.Rows.Add(DateTime.Now, -1);

            return new Tuple<DataTable, DataTable>(dt1, dt2);
        }

        private WorksheetDfn CreateWorksheet(DataTable dt)
        {
            var columnNames = from column in dt.Columns.Cast<DataColumn>() select column.ColumnName;
            var headerList = GetHeaderList(columnNames);
            var rows = dt.Rows.Cast<DataRow>().Select(j => GetRow(j.ItemArray)).ToArray();
            var worksheet = GetWorksheet(dt.TableName, headerList, rows);
            return worksheet;
        }

        public CellDfn[] GetHeaderList(IEnumerable<string> headers)
        {
            return headers.Select(x => new CellDfn
            {
                Value = x,
                Bold = true,
            }).ToArray();
        }

        public WorksheetDfn GetWorksheet(string name, CellDfn[] headerList, RowDfn[] rows)
        {
            return
                new WorksheetDfn
                {
                    Name = name,
                    ColumnHeadings = headerList,
                    Rows = rows
                };
        }

        public RowDfn GetRow(IEnumerable<object> values)
        {
            return new RowDfn
            {
                Cells = values.Select(x => new CellDfn
                {
                    CellDataType = GetType(x),
                    Value = x,
                    FormatCode = GetFormatCode(x)
                })
            };
        }

        private string GetFormatCode(object value)
        {
            if (value is DateTime)
                return "dd-MM-yyyy";

            return null;
        }

        private CellDataType GetType(object value)
        {
            if (value is bool)
                return CellDataType.Boolean;
            if (value is DateTime date)            
                return CellDataType.Date;
            if (value is string)
                return CellDataType.String;

            return CellDataType.Number;
        }

        public void CreateDocument(WorksheetDfn[] worksheets, string filePath)
        {
            var wb = new WorkbookDfn
            {
                Worksheets = worksheets
            };

            byte[] bytes;
            using (var stream = new MemoryStream())
            {
                wb.WriteTo(stream);
                bytes = stream.ToArray();
            }            

            File.WriteAllBytes(filePath, bytes);
        }
    }
}

[sergey-tihon]

Please try to change FormatCode to "mm-dd-yy"

also, recommend trying the Cell class, it usually significantly simplify the code https://github.com/sergey-tihon/Clippit/blob/master/OpenXmlPowerTools/Excel/Cell.cs

[jaimyjaimy]

Unfortunately the date format is not picked by me, but it is flexible based on what the customer wants. dd-MM-yyyy is what this customer wanted.

[sergey-tihon]

Then you have to dig into OpenXML spec format. (FormatCode is not .NET DateTime format)

https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.drawing.charts.formatcode?view=openxml-2.8.1

formatCode (Format Code) This element specifies a string representing the format code to apply. For more information see the SpreadsheetML numFmt element's (§18.8.30) formatCode attribute.

here is the spec https://learn.microsoft.com/en-us/openspecs/office_standards/ms-oi29500/17d11129-219b-4e2c-88db-45844d21e528