Aspose.cell import datatable

[shwank]

hi

im import datatable like workSheet.Cells.ImportDataTable(dataTable, true, 0, 0, true, false);

and create a excel sheet

the table containing some decimal values so decimal values display like 1.1E+24 in execel sheet

but orginal value like 11111111111111111111

i need to all values in datatable display as string "11111111111111111111"

Actualy i want to excel functionalty -->Format Cells >> Number Tab >>Catagory>>Text

Thanks

[babar.raza]

Hi,

Thank you for contacting Aspose support.

We have evaluated your presented scenario while using the latest version of Aspose.Cells for .NET 8.6.2 and the following piece of code to get the desired results, that is; the string “11111111111111111111” is rendered as it is in the resultant spreadsheet (attached). If you are not already using the latest version of the API then please give it a try on your side. In case the problem persists, please provide us an executable standalone sample application for further review. Please note, you should create the DataTable dynamically in your code so that we do not require the database connectivity to evaluate the scenario on our side.

C#

DataTable dt = new DataTable(“table”);
dt.Columns.Add(“Type”);
dt.Columns.Add(“Number”);
dt.Rows.Add(new object[] { “MMM”, “11111111111111111111” });

Workbook book = new Workbook();
book.Worksheets[0].Cells.ImportDataTable(dt, true, 0, 0, true, false);
book.Save(“C:/temp/output.xlsx”);

[babar.raza]

shwank:

Actualy i want to excel functionalty -->Format Cells >> Number Tab >>Catagory>>Text

In reference to your above comments, you should set the Style.Number property to 49 or Style.Custom property to "@" in order to set the formatting of a Row, Column or Cell to Text. Please check the detailed articles on Setting Display Format for Numbers & Formatting Rows/Columns.

[shwank]

Hi,

i try your solution but same result.I think when i get this row in my code"ImportDataTable(dataTable, true, 0, 0, true, false); " Already formatting is done. there for “style” not working.

Note: this datatable came from db select script, i just pass as an argument

my code like this

Aspose.Cells.License license = new Aspose.Cells.License();

license.SetLicense(“Aspose.Total.lic”);

string filePath = string.Empty;

SaveFileDialog saveForm = new SaveFileDialog();

saveForm.Filter = “Excel files|*.xlsx”;

if (saveForm.ShowDialog(this.ParentForm) == System.Windows.Forms.DialogResult.OK)

filePath = saveForm.FileName;

if (!string.IsNullOrEmpty(filePath))

{

Workbook workBook = new Workbook();

Worksheet worksheet = workBook.Worksheets[0];

worksheet.Cells.ImportDataTable(dataTable, true, 0, 0, true, false);

//I added style things after your comment

Style style = worksheet.Cells[“B3”].GetStyle();

style.Number = 49;

worksheet.Cells[“B3”].SetStyle(style);

workBook.Save(filePath);

}

[babar.raza]

Hi,

Could you please change the code as follow and try again? In case you still do not get the desired results, we need a standalone application (along with dependencies) as stated above.

From:

Style style = worksheet.Cells[“B3”].GetStyle();
style.Number = 49;
worksheet.Cells[“B3”].SetStyle(style);

To:

Style style = workBook.CreateStyle();
style.Number = 49;
worksheet.Cells[“B3”].SetStyle(style, new StyleFlag() { NumberFormat = true });

[shwank]

I have no such a overload.

//Assembly Aspose.Cells.dll, v5.1.3.0

public void SetStyle(Style style);

public void SetStyle(Style style, bool updateBorders);

[babar.raza]

Hi,

I am afraid, you are using a quite old revision of the API that could may also have a bug related to this behavior. By the way, once you have imported the data, you should be able to apply the format on the cell, row and/or column so you may try setting the style for complete row or column after importing the data to see if it works as expected. Moreover, try setting the Style.Custom property instead of Style.Number as suggested earlier in this thread.

[shwank]

Hi,

im upgrade my .dll to "Assembly Aspose.Cells.dll, v8.2.0.0" but i get same result. My code like that and I attached an image how display in Excel vs datatable, so you have any suggestion ?

Workbook workBook = new Workbook(FileFormatType.Xlsx);

Worksheet worksheet = workBook.Worksheets[0];

worksheet.AutoFitColumns();

worksheet.Cells.ImportDataTable(dataTable, true, 0, 0, true, false);

Style style = workBook.CreateStyle();

//style.Number = 49;

style.Custom = "@";

StyleFlag f = new StyleFlag();

f.NumberFormat = true;

worksheet.Cells["B3"].SetStyle(style,f);

workBook.Save(filePath);

[shwank]

Hi again,

I tried excel cell color change for testing, but it’s not working to.

I think, I have a big mistake for usage Aspose because nothing work

Workbook workBook = new Workbook(FileFormatType.Xlsx);

Worksheet worksheet = workBook.Worksheets[0];

worksheet.AutoFitColumns();

worksheet.Cells.ImportDataTable(dataTable, true, 0, 0, true, false);

Style style = workBook.CreateStyle();

//style.Number = 49;

style.Custom = “@”;

style.Pattern = BackgroundType.Solid;

style.ForegroundColor = Color.Red; 

StyleFlag f = new StyleFlag();

f.NumberFormat = true;

worksheet.Cells[“B3”].SetStyle(style,f);

workBook.Save(filePath);

[amjad.sahi]

Hi,

shwank:

I tried excel cell color change for testing, but it's not working to.

I think, I have a big mistake for usage Aspose because nothing work

Workbook workBook = new Workbook(FileFormatType.Xlsx);

Worksheet worksheet = workBook.Worksheets[0];

worksheet.AutoFitColumns();

worksheet.Cells.ImportDataTable(dataTable, true, 0, 0, true, false);

Style style = workBook.CreateStyle();

//style.Number = 49;

style.Custom = "@";

style.Pattern = BackgroundType.Solid;

style.ForegroundColor = Color.Red;

StyleFlag f = new StyleFlag();

f.NumberFormat = true;

worksheet.Cells["B3"].SetStyle(style,f);

workBook.Save(filePath);

Your code is not right. If you are setting the Style with respect to StyleFlag, you also need to make the relevant option on. You need to add a line of code to your code segment:

e.g

Sample code:

Workbook workBook = new Workbook(FileFormatType.Xlsx);

Worksheet worksheet = workBook.Worksheets[0];

worksheet.AutoFitColumns();

worksheet.Cells.ImportDataTable(dataTable, true, 0, 0, true, false);

Style style = workBook.CreateStyle();

//style.Number = 49;

style.Custom = "@";

style.Pattern = BackgroundType.Solid;

style.ForegroundColor = Color.Red;

StyleFlag f = new StyleFlag();

f.NumberFormat = true;

f.CellShading = true;

worksheet.Cells["B3"].SetStyle(style,f);

workBook.Save(filePath);

it should work fine.

Also, for your original issue, it might be again due to your underling source data (in the DataTable etc. ) or something to do with your code. We request you to kindly create a sample console demo application (runnable), zip it and post us here to show the issue, we will check it and help you to fix your issue asap. Also provide your template file(s) if you have any.

Thank you.

[shwank]

Hi,

I Created a sample proj.

[amjad.sahi]

Hi,

Thanks for the sample project.

I have evaluated your scenario/ case using your sample project a bit. Well, the problem is MS Excel automatically converts the numeric data (numbers with more than 11 digits) to scientific notations for bigger values. Also, it stores DateTime data in numeric values automatically. So, for your specific needs, you have to tweak your sample code a bit. First you got to convert your specific columns’ data type to string (as you need the data to be pasted as they are seen), you may do it by cloning your DataTable, you will import all your records to your cloned DataTable. Now you will use Aspose.Cells APIs (Cells.ImportDataTable()) to import data from cloned DataTable. This would work fine. I have written the following sample code to accomplish your task, please refer to the updated code segment and you may add/update your code in your original project/ scenario accordingly for your needs:

e.g

Sample code:

DataTable dt = new DataTable(“table”);

dt.Columns.Add(new DataColumn(“Id”, typeof(int)));

dt.Columns.Add(new DataColumn(“Text”, typeof(decimal)));//this column must be display like a string

dt.Columns.Add(new DataColumn(“Value”, typeof(string)));

dt.Columns.Add(new DataColumn(“Date”, typeof(DateTime)));//this column must be display like a string

dt.Rows.Add(new object[] { 1234567, 1234567891234567, “zdffff”, “31.05.2015 00:00:00” });

dt.Rows.Add(new object[] { 1234567, 1234567891234567, “zdffff”, “31.05.2015 00:00:00” });

dt.Rows.Add(new object[] { 1234567, 1234567891234567, “zdffff”, “31.05.2015 00:00:00” });

dt.Rows.Add(new object[] { 1234567, 1234567891234567, “zdffff”, “31.05.2015 00:00:00” });

Workbook workBook = new Workbook(FileFormatType.Xlsx);

Worksheet worksheet = workBook.Worksheets[0];

//Convert your underlying columns’ data type to string

//get all records in a duplicated data table

DataTable dtCloned = dt.Clone();

dtCloned.Columns[1].DataType = typeof(string);

dtCloned.Columns[3].DataType = typeof(string);

foreach (DataRow row in dt.Rows)

{

dtCloned.ImportRow(row);

}

//Import data from your cloned DataTable to worksheet cells.

worksheet.Cells.ImportDataTable(dtCloned, true, 0, 0, true, false);

//Set the style/formatting for background color for your desired cells

Style style = workBook.CreateStyle();

style.Pattern = BackgroundType.Solid;

style.ForegroundColor = Color.Red;

worksheet.Cells[“B3”].SetStyle(style);

worksheet.Cells[“D3”].SetStyle(style);

//Auto-fit the columns

worksheet.AutoFitColumns();

workBook.Save(@“e:\test2\out1.xlsx”);

Hope, this helps you a bit.

Thank you.

[shwank]

hi,

I already know this solution but we decided this way for performance issue, so your solution not suitable for our purpose. because datatable contain very large amount rows.

Thanks

[amjad.sahi]

Hi,

Well, as I told you regarding MS Excel behavior, so I am afraid, there is no better way to cope with it and you have to use the solution mentioned above for your requirements.

Thank you.