Export columns as numeric

Aspose.Cells Product Family
1

Is there any way to specify that the data in a specific column should be numeric in excel file when exporting?

Worksheet = Excel.Worksheets(0)

Worksheet.Cells.ImportDataTable(oDS.Tables(1), True, 0, 0)

Excel.Save(sFileName, FileFormatType.Excel2000)

Thanks

2

Hi,

Thanks for considering Aspose.

When you import a data table to a worksheet, the data will be in General format. Are you in need to format a column to number format? Well you can add a line to do the task.

Following line will format the 10th column to simple numbers "0" .

(you can try other number formats too, See Number format List in Style.Number Property in Aspose.Cells APIs)

worksheet.Cells.Columns(9).Style.Number = 1

If you have further queries, feel free to contact us.

Regards

Amjad Sahi

Aspose Nanjing Team

3

Is there an API document that I can use for a reference for all the number types. Will this handle dates as well?


Thanks

Anthony

4

Hi,

Yes it will handle dates too.

Please see all the built-in number formats:

or

Aspose.Cells API Reference pages.

Thank you.

5

Thanks. That’s great. I don’t see any four digit dates however. Do you not support?

6

Hi,

Yes we do support four digit dates too.

Please check the following sample code:

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells["A1"].PutValue(Convert.ToDateTime("11/12/2006"));

worksheet.Cells["A1"].Style.Custom = "m/d;@";

worksheet.Cells["A2"].PutValue(DateTime.Now);

worksheet.Cells["A2"].Style.Number = 16;

workbook.Save("d:\\fddate.xls");

Regards,

7

I am setting the column to a number by doing this

Worksheet.Cells.Columns(i).Style.Number = 1

but when I open in Excel it says "number is stored as text" for these columns and I can't do math functions on them until I convert to number. This is what I am trying to avoid. Is there anything I can do about this before I get to Excel?

8

Hi,

Please use StyleFlag struct to apply number style to a whole column. I write the following code for you to apply number format to column A in the sheet.

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

sheet.Cells["A1"].PutValue(123);

Style style;

StyleFlag flag = new StyleFlag();

style = sheet.Cells.Columns[0].Style;

style.Number = 1;

sheet.Cells.Columns[0].ApplyStyle(style, flag);

workbook.Save("d:\\testingnumbercol.xls");

Thank you.

9

Hi,

Sorry, I missed the line in the above code,

......

style = sheet.Cells.Columns[0].Style;

style.Number = 1;

flag.NumberFormat = true;

sheet.Cells.Columns[0].ApplyStyle(style, flag);

......

I think your code is also right. But it might be possible that you are inserting numbers as text like using Cell.PutValue("123") instead of Cell.PutValue(123), as you are saying that you are seeing a message popup 'numbers stored as text'. with those cells

Here is another example:

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

sheet.Cells["A1"].PutValue(123);

sheet.Cells.Columns[0].Style.Number = 1;

workbook.Save("d:\\testingnumbercol1.xls");

Thank you.

10

Is this supposed to get rid of the error message in Excel...

"The number in this cell is formatted as text or preceded by an apostrophe"

?

Because I tried formatting the cells using this method, but it doesn't seem to work. Here is what I am doing.

workbook.Worksheets[0].Cells.Columns[2].Style.Number = 1;

I also tried formatting an indivual cell, but that didn't work either...

workbook.Worksheets[0].Cells["C2"].Style.Number;

I see you use a the StyleFlag in other posting, but I don't see this option in my code? Also, I don't have access to the ApplyStyle function that you used in other posts.

workbook.Worksheets[0].Cells.Columns[2].ApplyStyle(style, flag)

NEVERMIND, I DIDN'T HAVE THE LATEST VERSION OF ASPOSE CELLS, SO ONCE I DOWNLOADED THE LATEST DLL, IT FIXED THE PROBLEM

11

Hi,

For...

Is this supposed to get rid of the error message in Excel...

"The number in this cell is formatted as text or preceded by an apostrophe"

?

Well, I think while importing data into an excel sheet, it might be possible that your so called numeric data stored in the datatable, is actually coming from string fields(columns).. Well, you may try the following overloaded method:

public int ImportDataTable(
DataTable dataTable,
bool isFieldNameShown,
int firstRow,
byte firstColumn,
bool insertRows,
bool convertStringToNumber
);

I write the following coding example (C#), kindly check it for your need as it works fine:

DataSet ds = new DataSet();

System.Data.DataTable dt = new DataTable("MyTable");

dt.Columns.Add("Field1", typeof(string));

for (int i = 1; i<=40;i++)

{

System.Data.DataRow dr = dt.NewRow();

dr["Field1"]=i*2;

dt.Rows.Add(dr);

}

ds.Tables.Add(dt);

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

sheet.Cells.ImportDataTable(ds.Tables[0], true, 0, 0,false,true);

sheet.Cells.Columns[0].Style.Number = 1;

workbook.Save("d:\\test\\numberedcols.xls", FileFormatType.Excel2000);

For ....

workbook.Worksheets[0].Cells.Columns[2].Style.Number = 1;

workbook.Worksheets[0].Cells.Columns[2].ApplyStyle(style, flag)

Well, both are equal and works ok.

If you still find some problems, could you create a simple example with an output template file and post us here. so that we may sort out your issue soon.

Thank you.

12

same problem i am getting but if you are using locally you can set the property in Tools tab;
tools → options–> checkerrors–>textstored as text (uncheck this)

but i want this in code(asp.net) because my downloaded template is used across the net

13

hi what is flag in the above code. i am getting same error as number stored as text

14

Hi,

I am not familiar how do you insert your data into the cells. If you are exporting data from some data table to filling some cells in a worksheet, you may try the overloaded version i.e..,

public int ImportDataTable(
DataTable dataTable,
bool isFieldNameShown,
int firstRow,
byte firstColumn,
bool insertRows,
bool convertStringToNumber
);

E.g.,

DataSet ds = new DataSet();
System.Data.DataTable dt = new DataTable("MyTable");
dt.Columns.Add("Field1", typeof(string));
for (int i = 1; i<=40;i++)
{
System.Data.DataRow dr = dt.NewRow();
dr["Field1"]=i*2;
dt.Rows.Add(dr);

}
ds.Tables.Add(dt);
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Cells.ImportDataTable(ds.Tables[0], true, 0, 0,false,true);
workbook.Save("d:\\test\\numberedcols.xls", FileFormatType.Excel2003);

If you still find the problem, kindly give us more details, sample code, output file etc. We will check it soon.

Thank you.

15

Hi,

Please check my above post for reference related importing datatable to cells.

For style flag, if you want to format a column data, you may try as follows. For your info, StyleFlag is a struct used to apply your desired formattings only.

//Create a style object and get the style of the first column.
Style style = sheet.Cells.Columns[0].Style;
//set a format.
style.Number = 1;
//Create a style flag object.
StyleFlag flag = new StyleFlag();
//Apply the number format
flag.NumberFormat = true;
//Apply the style to first column.
sheet.Cells.Columns[0].ApplyStyle(style, flag);

Thank you.