I am creating an EXCEL worksheet with data in the form of 01-01-01. I need the cell to contain exactly that. I have formatted the cell using the text format. However when I place this data into the formatted cell it puts the value 36892 into the cell. If I look at the formatting on the cell it is text and If I now copy the value 01-01-01 into the cell it keeps it as 01-01-01.
I have thought of creating a custom format however the value can have dots instead of dashes between the numbers and there can be as many as ten sets of numbers.
Hi,
Well, Aspose.Cells for .NET works in the same way with MS Excel. Following is my testing code and it works fine, also, attached is the output excel file.
Sample code:
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Style style = workbook.Styles[workbook.Styles.Add()];
style.Custom = "@";
Cells cells = worksheet.Cells;
Cell cell = cells["A1"];
cell.SetStyle(style);
cell.PutValue("01-01-01");
workbook.Save("f:\\test\\textstyle.xls");
Which version of Aspose.Cells for .NET you are using, could you try the latest fix (4.5.1): http://www.aspose.com/community/files/51/file-format-components/aspose.cells/entry145662.aspx
Thank you.
I installed this fix and I am still having the same problem. I am actually applying the formatting to a range. I'm not sure if this is why it doesn't work. Here is the code where I go through my list of formats and apply it to the range that requires that formatting
static private void ApplyNumericFormats(Worksheet wks, ReportInformation ri)
{
try
{
for (int i = 0; i < ri.NumericFormatRanges.Count; i++)
{
foreach (ReportRange r in ri.NumericFormatRanges[i])
{
STYLE_NUMERIC_FORMAT.Custom = ri.NumericFormats[i];
Range rng = GetRange(wks, r);
rng.ApplyStyle(STYLE_NUMERIC_FORMAT, FLAG_NUMERIC_FORMAT);
}
}
}
catch (Exception e)
{
myExceptions.Add(e);
}
}
Hi,
I am still unable to reproduce the issue, Aspose.Cells for .NET behaves similar to MS Excel. Could you try to implement your scenario manually in MS Excel and check whether MS Excel is behaving similarly and produces the same result as Aspose.Cells for .NET does.
Anyways, following is my testing code which works fine for a range and attached is the output file.
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
Range range = cells.CreateRange("A1", "C3");
range[0,0].PutValue("01-01-01");
range[0, 2].PutValue("01-10-03");
range[2, 2].PutValue("11-03-04");
Style style = workbook.Styles[workbook.Styles.Add()];
style.Custom = "@";
StyleFlag flag = new StyleFlag();
flag.NumberFormat = true;
range.ApplyStyle(style, flag);
workbook.Save("f:\\test\\ntextstyle.xls");
Could you create a simple console app., zip it and post it here (all the files) to show the issue, we will check it soon.
Thank you.
I will try to do as you asked but one thing that I see you are doing is you are assigning a style to an entire worksheet. I am assigning that style to just a range.
Hi,
No, I am not assigning the style to the entire sheet. In my code, I just create a new (separate) style object (adding it to the styles collection of the workbook), specify its formatting attributes, then, create a cell range i.e.., "A1:C3" and apply that style to it. That's it.
Thank you.
I figured out the problem. I was using a datatable to import my values (ie 01-01-01)and when I was importing it into the excel spreadsheet I was converting strings to numbers. Sorry about the confusion but the hotfix fixed another problem we were having.
Thanks again for the help