cell value over 10 billion can't be exported with a number format using UltraGridExcelExporter

Byonghoon posted over 14 years ago

Hi. I'm still poor at English. sorry first.

My 1st question in this forum has not resolved yet.

(I think it's platform bug, but SDK I'm using was so old and I decided to make a new grid and it works well.)

Now I'm interested in exporting to excel.

Suddenly, I found that cell value over 10 billion can't be exported with a number format using UltraGridExcelExporter.

- I put some hard coded values (111111111-a, 1111111111-b, 11111111111-c)

- a, b exported properly with a number format

- c exported improperly.

(in debugger, value type was Int64 but exported not a number format but a string format)

(string format assigned with column unit so in the cell of the column of the row, still remained custom number format)

Is there any problem with interface between ultragrid and excel? (data width, ...)

Thanks in advance

Parents

469350

Offline Mike Saltzman posted over 14 years ago

Hi,

It's not clear to me what format you are referring to.

Are you setting the Format on the Excel cell?

Are you exporting a WinGrid, or writing to an Excel Workbook object directly?
Cancel
Reply

Cancel
80

Byonghoon posted over 14 years ago in reply to Mike Saltzman

The format I'm referring to is a number format separated by comma (ex. #,###)

Is there another way to meet a standard excel number format separated by comma?

(The ways using "#" are always identified with custom format in excel)

Here are some parts of my source.

In this source, I set the format by column. But when I set the format by cell, results are always same.

..................

using (UltraGridExcelExporter uee = new UltraGridExcelExporter())

{

uee.InitializeColumn += new InitializeColumnEventHandler(uee_InitializeColumn);

uee.BeginExport +=new BeginExportEventHandler(uee_BeginExport);

uee.EndExport += new EndExportEventHandler(uee_EndExport);

uee.Export(ugdListToBank, sfd.FileName);

}

..............................................

private void uee_InitializeColumn(object sender, InitializeColumnEventArgs e)

{

e.ExcelFormatStr = e.FrameworkFormatStr;

}

private void uee_BeginExport(object sender, BeginExportEventArgs e)

{

e.CurrentWorksheet.Columns[3].CellFormat.Alignment = Infragistics.Excel.HorizontalCellAlignment.Right;

e.CurrentWorksheet.Columns[3].CellFormat.FormatString = "#,###";

e.CurrentWorksheet.Rows[e.CurrentRowIndex++].Cells[0].Value = "wire transfer";

e.CurrentWorksheet.Rows[e.CurrentRowIndex++].Cells[0].Value = "sender : KARI";

e.CurrentRowIndex++;

}

private void uee_EndExport(object sender, EndExportEventArgs e)

{

const int width_unit = 256;

int i;

long total;

e.CurrentWorksheet.Columns[0].Width = 20 * width_unit;

e.CurrentWorksheet.Columns[1].Width = 25 * width_unit;

e.CurrentWorksheet.Columns[2].Width = 20 * width_unit;

e.CurrentWorksheet.Columns[3].Width = 20 * width_unit;

e.CurrentWorksheet.Columns[4].Width = 15 * width_unit;

for (i = 4, total = 0; i < e.CurrentRowIndex; i++)

total += Convert.ToInt64(e.CurrentWorksheet.Rows[i].Cells[3].Value);

e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[0].Value = "total";

e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[0].CellFormat.Alignment = Infragistics.Excel.HorizontalCellAlignment.Center;

e.CurrentWorksheet.Rows[e.CurrentRowIndex++].Cells[3].Value = total;

e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[0].Value = "test1";

e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[0].CellFormat.Alignment = Infragistics.Excel.HorizontalCellAlignment.Center;

e.CurrentWorksheet.Rows[e.CurrentRowIndex++].Cells[3].Value = 1111111111;// It works.

e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[0].Value = "test2";

e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[0].CellFormat.Alignment = Infragistics.Excel.HorizontalCellAlignment.Center;

e.CurrentWorksheet.Rows[e.CurrentRowIndex++].Cells[3].Value = 11111111111;// It works.

e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[0].Value = "test3";

e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[0].CellFormat.Alignment = Infragistics.Excel.HorizontalCellAlignment.Center;

e.CurrentWorksheet.Rows[e.CurrentRowIndex++].Cells[3].Value = 111111111111;// It doesn't work.

}
Cancel
Reply

Cancel

Reply

469350

Offline Mike Saltzman posted over 14 years ago in reply to Byonghoon

Hi,

I'm still quite confused about what you are trying to do and what the problem is.

You have code in the InitializeColumn event to set e.ExcelFormatStr = e.FrameworkFormatStr. This is usualyl okay, but DotNet format strings and Excel format strings are not always the same, which is why we don't do this for you automatically. I think "#,###" is a valid Excel format string, but you might want to check Excel's documentation or maybe even just try this in Excel to make sure it works.

Since you are already setting this in InitializeColumn, there's no reason to set the format on an individual cell inside the BeginExport event:

e.CurrentWorksheet.Columns[3].CellFormat.FormatString = "#,###";

Why are you doing that? Is this format supposed to be different than the others? How do you know the cell you want will be at columns[3] in the worksheet? This seems like a dangerous assumption. It also seems unnecessary and since BeginExport fires before InitializeColumn, it's probably just getting overwritten, anyway.

What exactly isn't working? What are you expecting the values to look like and what do they actually look like? Have you tried applying the same format to the same values in Excel to see if they work there?
Cancel
Reply

Cancel

Children

No Data