facebooktwittermenuarrow-up

GemBox Support Forum

Creating save options with Data-Table-Cell-To-Excel-Cell-Converting

It seems like DataTableCellToExcelCellConverting is only looking at the first column of the datatable before it’s inserted into the spreadsheet. Are there any code examples of how to use this?

Hi Lynn,

I’m not sure what issue you’re experiencing, nevertheless, here is an example that shows how you can use DataTableCellToExcelCellConverting to add TimeSpan values to an Excel file.

var dataTable = new DataTable();
dataTable.Columns.Add("Text Column", typeof(string));
dataTable.Columns.Add("Number Column", typeof(int));
dataTable.Columns.Add("Time Column", typeof(TimeSpan));

for (int i = 1; i < 10; i++)
    dataTable.Rows.Add($"Sample {i}", i, TimeSpan.FromHours(i));

var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Sheet 1");

var insertOptions = new InsertDataTableOptions("A1");
insertOptions.ColumnHeaders = true;

insertOptions.DataTableCellToExcelCellConverting += (sender, e) =>
{
    Type type = e.DataTableValue.GetType();
    if (!ExcelFile.SupportsType(type))
    {
        switch(e.DataTableValue)
        {
            case TimeSpan time:
                e.ExcelCellValue = new DateTime(1899, 12, 31) + time;
                e.ExcelCell.Style.NumberFormat = "h:mm:ss;@";
                break;
            default:
                // Ignore other unsupported values.
                e.ExcelCellValue = null; 
                break;
        }
    }
};

worksheet.InsertDataTable(dataTable, insertOptions);

workbook.Save("Sample.xlsx");

I hope this helps, let me know if you need anything else.

Regards,
Mario

This is very helpful. Thank you.
I guess I should have been more accurate in my description. I really don’t like bugging you all.

I have a dynamic datatable that I will need to read from SQL and insert into a spreadsheet. I have all the other methods working, except exporting it to Excel. The issue is I never know what type of column data type it might be. String, decimal, percentage. Sometimes columns will show up with “$” signs in the data.

    InstOptions.DataTableCellToExcelCellConverting += (sender, e) =>
    {
        string textNumber = e.DataTableValue as string;
        double number;
        if (textNumber != null)
        {
            if (textNumber.Contains("$"))
            {
                textNumber = textNumber.Replace("$", "").Replace(",", "");
                double.TryParse(textNumber, out number);
                e.ExcelCellValue = number;
                e.ExcelCell.Style.NumberFormat = NumberFormatBuilder.Currency("$", 0, true, true, false, false);
            }
            else
            {
                if (double.TryParse(textNumber, out number))
                {
                    int num = number.ToString().Length - (((int)number).ToString().Length + 1);
                    if (num >= 4)
                    {
                        e.ExcelCellValue = number;
                        e.ExcelCell.Style.NumberFormat = NumberFormatBuilder.Percentage(2);
                    }
                    else
                    {
                        e.ExcelCellValue = number;

                    }
                }
            }
        }

    };

Apologize, but I don’t understand what your problem is.

Can you please explain exactly what issue you have?

Or better yet, can you please create a small Visual Studio project that demonstrates your problem?
Please include the desired Excel file that you would like to generate so that I can use that as a guideline.
Note, instead of using dynamic datatable, you can create a dummy DataTable object which will represent your real data.

Here is some quick code

// This is another type of data that I might recieve ....
var dataTable = new DataTable();
dataTable.Columns.Add("Text Column", typeof(string));
dataTable.Columns.Add("Number Column", typeof(int));
dataTable.Columns.Add("Money Column", typeof(string));
dataTable.Columns.Add("Decimal Column", typeof(decimal));
dataTable.Columns.Add("Date Column", typeof(DateTime));

dataTable.Rows.Add("Sheets", 97, "$4,234.05", 1.0, DateTime.Now);

string filePath = "ExcelTest.xlsx";

var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Store 51");

InsertDataTableOptions InstOptions = new InsertDataTableOptions() { ColumnHeaders = true, StartRow = 2, StartColumn = 2 };

InstOptions.DataTableCellToExcelCellConverting += (sender, e) =>
{
    string textNumber = e.DataTableValue as string;
    double number;
    if (textNumber != null)
    {
        if (textNumber.Contains("$"))
        {
            textNumber = textNumber.Replace("$", "").Replace(",", "");
            double.TryParse(textNumber, out number);
            e.ExcelCellValue = number;
            e.ExcelCell.Style.NumberFormat = NumberFormatBuilder.Currency("$", 0, true, true, false, false);
        }
        else
        {
            if (double.TryParse(textNumber, out number))
            {
                int num = number.ToString().Length - (((int)number).ToString().Length + 1);
                if (num >= 4)
                {
                    e.ExcelCellValue = number;
                    e.ExcelCell.Style.NumberFormat = NumberFormatBuilder.Percentage(2);
                }
                else
                {
                    e.ExcelCellValue = number;

                }
            }
        }
    }
};

worksheet.InsertDataTable(dataTable, InstOptions);

Ok, and what is the problem that you have with this type of data?

It seems like DataTableCellToExcelCellConverting is only looking at the first column of the datatable before it’s inserted into the spreadsheet.

I don’t understand what you mean by that, can you try this:

InstOptions.DataTableCellToExcelCellConverting += (sender, e) =>
{
    Console.WriteLine($"({e.DataTableRowIndex}, {e.DataTableColumnIndex})");

    // ...
};

What do you get, do you get all cells from DataTable?

I was getting all the rows but just the first column. This code, and the other, helped me pin-point the issue. The SQL stored procedures were not returning all the data columns. Thank you for all of the help on this.