Excel gives error #64
Description
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);
}
}
}