Setting a cell's type to text throughout the sheet

Aspose.Cells Product Family
1

Hi everyone,

I am running Aspose.Cells for .NET v4.2 and I need to find out something.

I have to generate an Excel worksheet in a workbook from data retrieved from the database using a .NET datatable in C#. This file is then downloaded to the end-user via a web interface, which works fine.

The fields in the database can be numeric, but I need all the fields on the worksheet to be of type text (for example when you right click on a Cell in Excel and select the type to be “Text”), because some of these numeric values retrieved from the database need to be 5 digits long, and if not, must be prefixed with zeroes. I have managed to get this working using Aspose.Cells, however if the user goes and changes a value of the cell, then the cell suddenly behaves like a numeric cell. If I enter a value into a blank cell it behaves correctly, i.e. includes the prefixed zeroes, but if I change an existing cell, it behaves like a numeric. Which property can I use/workaround can I use to get all the cells to behave like text ALL the time?

Thanks a load!!

Regards,

2

Hi,

Thank you for considering Aspose.

Well, if you save a number value as a string, by default the cell format will become “General” and when you will edit it in excel it will change to number format, whereas, if the cell format is “Text” then on editing the format will not change. You may change the format of all the cells / columns in your worksheet to “Text” to overcome this issue. Please see the following sample code in this regard,

Sample Code:

//Instantiate a new Workbook.

Workbook excelbook = new Workbook();

//Instantiating a "Products" DataTable object

DataTable dataTable = new DataTable("Products");

//Adding columns to the DataTable object

dataTable.Columns.Add("Product ID", typeof(Int32));

dataTable.Columns.Add("Product Name", typeof(string));

dataTable.Columns.Add("Units In Stock", typeof(Int32));

//Creating an empty row in the DataTable object

DataRow dr = dataTable.NewRow();

//Adding data to the row

dr[0] = 001;

dr[1] = "Aniseed Syrup";

dr[2] = 15;

//Adding filled row to the DataTable object

dataTable.Rows.Add(dr);

//Creating another empty row in the DataTable object

dr = dataTable.NewRow();

//Adding data to the row

dr[0] = 002;

dr[1] = "Boston Crab Meat";

dr[2] = 123;

//Adding filled row to the DataTable object

dataTable.Rows.Add(dr);

excelbook.Worksheets[0].Cells.ImportDataTable(dataTable, true,0,0,false,false);

Style style;

StyleFlag flag;

for (int i = 0; i <= 255; i++)
{

style = excelbook.Worksheets[0].Cells.Columns[i].Style;

// Set Text Format

style.Number = 49;

flag = new StyleFlag();

flag.NumberFormat = true;

excelbook.Worksheets[0].Cells.Columns[i].ApplyStyle(style, flag);

}

excelbook.Save("C:\\data.xls");

Thank You & Best Regards,