Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Excel gives error #64

Open
jaimyjaimy opened this issue Apr 19, 2023 · 3 comments
Open

Excel gives error #64

jaimyjaimy opened this issue Apr 19, 2023 · 3 comments
Labels
Excel Excel related tasks

Comments

@jaimyjaimy
Copy link

jaimyjaimy commented Apr 19, 2023

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 sergey-tihon added the Excel Excel related tasks label Apr 21, 2023
@sergey-tihon
Copy link
Owner

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
Copy link
Author

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
Copy link
Owner

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Excel Excel related tasks
Projects
None yet
Development

No branches or pull requests

2 participants