Spreadsheetgear set data flags for specific column?

Question

I am attempting to do this:

IWorksheet worksheet = Factory.GetWorkbook().Worksheets[0];

IRange range = worksheet.Cells["A1"];

range.CopyFromDataTable(dataTable, SetDataFlags.None);

worksheet.Cells.Columns.AutoFit();
return worksheet;

This works great normally, however I've run into an issue. I have one column that has a really long number, possibly with zeroes in the front and I need it to be entered and displayed as text. If I do a lookup of that particular cell like:

var cell = range["U34"].Value;

The data has already been turned into scientific notation so no amount of formatting afterwards fixes it. I tried SetDataFlags.AllText and that works great, except it breaks the rest of the worksheet because all of the numbers are stored as text, which is unacceptable.

I'm at a loss of how to fix this.

Solution: Since I'm just looking to change one column, if it's present and a lot of the columns are dynamic I went with the "preformatting" route. Find the column index from the datatable:

int ColumnIndex = -1;
        for (int x = 0; x < dataTable.Columns.Count; x++)
        {
            if (dataTable.Columns[x].ColumnName.Equals("Whatever"))
            {
                ColumnIndex = x;
            }
        }
worksheet.Cells[0, ColumnIndex, 0, ColumnIndex].EntireColumn.NumberFormat = "@";

Then perform the CopyFromDataTable, with Flags set to None and everything is perfect!

Answer 1

The IRange.CopyFromDataTable(...) method can be passed in a SetDataFlags.InsertCells enum option, which allows you to pre-format your destination range so that the inserted DataTable data picks up the formatting you specify. This formatting includes a cell's IRange.NumberFormat, which can be set to "@" and specifies that input to that cell should be treated as Text.

So, if you know what columns will have these unusually-large numbers that trigger scientific notation, another option would be to pre-format your worksheet's destination range with IRange.NumberFormat = "@" and will preserve your values for these columns as-is.

Please see the documentation for the IRange.CopyFromDataTable(...) method, as it provides important information on what range needs this "pre-formatting." Also, assuming you've installed SpreadsheetGear on your machine, check out the Reporting > DataSet to Workbook example in the SpreadsheetGear Explorer Solutions for C#/VB (found in the "SpreadsheetGear" folder under the Start Menu) for a live demo of this SetDataFlags.InsertCells option.