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

GemBox.Spreadsheet
1

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?

2

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

3

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;

                    }
                }
            }
        }

    };
4

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.

5

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);
6

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

7

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

8

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?

9

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.