Hi, Amjad!I know how formatting, links etc works.The thing is that, due to the way we dynamically generate our Excel-documents, we can't have code like "SetStyle("yyyy-mm-dd") and the like.To me, the behaviour seems a bit buggy.To see what I mean, have a look at the attached xlsm-file and apply the code below.
Note there are two sheets in the document, Aspose and Macro. The Aspose-sheet will be filled in with data by the code below. The Macro-sheet will be filled in with data by a macro in the document.
You should see a different behaviour in the two sheets - in the Aspose-sheet, the date value doesn't seem to become a "real" date value until the user has edited the cell.
Again, note that we can't format the cell that contains the link to the date cell. This is for reasons out of scope in this message.
class Date
{
public static void DoIt()
{
WorkbookDesigner designer = new WorkbookDesigner();
Workbook workbook = new Workbook(Constants.sourcePath + "Date.xlsm");
designer.Workbook = workbook;
Range range = workbook.Worksheets.GetRangeByName("DATA");
Worksheet worksheet = range.Worksheet;
DataTable dt = CreateValidTable1();
range.Worksheet.Cells.ImportDataTable(dt, true, range.FirstRow, range.FirstColumn, dt.Rows.Count, dt.Columns.Count, false);
string output = Constants.destPath + "Date_result.xlsm";
workbook.Save(output);
Process.Start(output);
}
private static DataTable CreateValidTable1()
{
DataTable dt = new DataTable("MyDataSource");
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Value", typeof(string));
dt.Columns.Add("DataType", typeof(string));
dt.Rows.Add(1001, "2011-01-01", "Date");
dt.Rows.Add(1002, "123", "Int");
dt.Rows.Add(1003, "123.456", "Decimal");
// dt.Rows.Add("val2", "val2", 4);
//dt.Rows.Add("val3", "val3", 4);
return dt;
}
}
class Date
{
public static void DoIt()
{
WorkbookDesigner designer = new WorkbookDesigner();
Workbook workbook = new Workbook(Constants.sourcePath + "Date.xlsm");
designer.Workbook = workbook;
Range range = workbook.Worksheets.GetRangeByName("DATA");
Worksheet worksheet = range.Worksheet;
DataTable dt = CreateValidTable1();
range.Worksheet.Cells.ImportDataTable(dt, true, range.FirstRow, range.FirstColumn, dt.Rows.Count, dt.Columns.Count, false);
string output = Constants.destPath + "Date_result.xlsm";
workbook.Save(output);
Process.Start(output);
}
private static DataTable CreateValidTable1()
{
DataTable dt = new DataTable("MyDataSource");
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Value", typeof(string));
dt.Columns.Add("DataType", typeof(string));
dt.Rows.Add(1001, "2011-01-01", "Date");
dt.Rows.Add(1002, "123", "Int");
dt.Rows.Add(1003, "123.456", "Decimal");
// dt.Rows.Add("val2", "val2", 4);
//dt.Rows.Add("val3", "val3", 4);
return dt;
}
}