How to set Datatype in columnwise

Aspose.Cells Product Family
1

Hi Team,

Can we set datatype in columnwise For eg: column 3 should contain only dates or float and also from the row 3 to till row 25 it should be float value.

I need to set like this because i am not going to convert the string value to datetime or float in my code.After excel generation it showing warning symbol in excel sheet.

Please give idea to do this.

2

Hi,

Thanks for your question and considering Aspose.Cells for .NET.

You can set the data type of multiple cells or entire row or column using the ApplyStyle() method.

For example in the given sample code, it sets the number pattern and background fill color of multiple cells at once.

Please see the source.xls files, I have also attached the output file and screenshot for your reference.

Please download and use the latest version:
Aspose.Cells for .NET (Latest Version)

C#

string path = @“F:\Shak-Data-RW\Downloads\source.xlsx”;

Workbook workbook = new Workbook(path);

Worksheet worksheet = workbook.Worksheets[0];

string lastCell = worksheet.Cells.LastCell.Name;

Range rng = worksheet.Cells.CreateRange(“A1:” + lastCell);

Style st = workbook.CreateStyle();

//set the number pattern

st.Number = 2;

//set the background color to yellow

st.Pattern = BackgroundType.Solid;

st.ForegroundColor = Color.Yellow;

StyleFlag flag = new StyleFlag();

flag.All = true;

rng.ApplyStyle(st, flag);

workbook.Save(path + “.out.xlsx”);

Screenshot:

3
Veeraguru:

Hi Team,

Can we set datatype in columnwise For eg: column 3 should contain only dates or float and also from the row 3 to till row 25 it should be float value.

I need to set like this because i am not going to convert the string value to datetime or float in my code.After excel generation it showing warning symbol in excel sheet.

Please give idea to do this.

Hi,

For setting number patterns and date formats, please also see the following documentation article for your more help.

4

Hi,

Thanks for your reply.

How to remove the warning symbol shown in the attachement?

Please give an idea.

5

Hi,

Thanks for sharing the file.

Well, the numbers inserted into the yellow area are stored as text and not numbers. You may either need to convert them into numeric values to input into the cells or you may set the relevant error checking option off (of MS Excel) to disable the warning message attached to those cells.

See the document for reference (although the document is related to JAVA APIs but you may use the relevant .NET APIs accordingly):

Use Error Checking Options

Please see the following .NET example code.

[C#]

string filePath = @"F:\Shak-Data-RW\Downloads\source.xlsx";

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

//Add "23" it will show number stored as text but

//after applying error checking options, it won't show this message

worksheet.Cells["A1"].PutValue("23");

Aspose.Cells.ErrorCheckOptionCollection opts = worksheet.ErrorCheckOptions;

int i = opts.Add();

Aspose.Cells.ErrorCheckOption opt = opts[i];

opt.SetErrorCheck(Aspose.Cells.ErrorCheckType.TextNumber, false);

CellArea ca = new CellArea();

ca.StartRow = 0;

ca.StartColumn = 0;

ca.EndRow = 65535;

ca.EndColumn = 255;

opt.AddRange(ca);

//Save the excel file.

workbook.Save(filePath + ".out.xlsx");

Thank you.

6

Hi Team,

Even if i use style.Number=2 for decimal. Generated excel showing error in that column. I didnt off error check option. Is there any other way to do it?

7

Hi,

The error occurs because your cell contains a string value.

You can use the following code to convert your cells into double value. I have tested it and it works fine and your error is removed.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Test.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


for (int row = 3; row < 29; row++)

{

for (int col = 4; col < 7; col++)

{

Cell cell = worksheet.Cells[row, col];


double d = double.Parse(cell.StringValue);

cell.PutValue(d);

}

}


workbook.Save(filePath + “.out.xlsx”);


8

Hi,

The above mentioned code is fine. But in my case, dynamically data will come. So my plan was i am not ready to loop through each and every cell value to convert into double. For eg: column 3 and column 5 will contain float next time column 6 and 10 will contain float value. So I planned to set float as a data type for the whole column. If i do in this way i am getting error. Is there any other way to set data type in columnwise or the only way to loop through each and every cell then converting it to double?

9

Hi,


Since your so called double values are dynamic and you do not know where they will be placed in the cells of the worksheet, so one solution might be you may loop through each cell in the sheet which has data and use Cell.PutValue(string stringValue, bool isConverted) overload method, it will automatically convert to proper data type based on your data in the cells.

Sample code:

Workbook workbook = new Workbook(“e:\test2\Test.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;

int mRow = cells.MaxDataRow;
int mCol = cells.MaxDataColumn;

//Browse all the cells in the sheet that has value.
for (int i = 0; i <= mRow; i++)
{
for (int j = 0; j <= mCol; j++)
{
Aspose.Cells.Cell cell = cells[i, j];
if (cell.Value != null)
{
//We may use the overload: Cell.PutValue(string stringValue, bool isConverted) method, set “true” for the second parameter as it will automatically convert the data to property data type.
cell.PutValue(cell.StringValue, true);
}
}
}

//You may use your own code now if you want.

//Note:
//One issue is here, if your so called date time value is in string, it will be converted to numeric values as Dates are stored in numeric notations.
//The solution is you have to set the date style to those cells/column by using Aspose.Cells APIs/code.

workbook.Save(“e:\test2\outTest1.xlsx”);


Thank you.
10

Hi,

The above code is working fine except date field.I am going to pass string value and the columns are not dynamic. Is there any other way to set datatype in columnwise without converting the data to float and should not get error.

11
Veeraguru:

Hi,

The above code is working fine except date field.I am going to pass string value and the columns are not dynamic. Is there any other way to set datatype in columnwise without converting the data to float and should not get error.

Hi,

For date field, please use the following code to make your column into date. Please add this code just before saving the workbook. It will then convert all the date integers into proper dates.

C#

//Make your column C as DateTime.
Style dateStyle = workbook.CreateStyle();
dateStyle.Number = 14;

StyleFlag flag = new StyleFlag();
flag.All = true;

worksheet.Cells.Columns[2].ApplyStyle(dateStyle, flag);