UI for WPF support direct to excel

[herb]

Have a project that uses Telerik UI for WPF libraries.

Researching an up coming customer request: they want to submit a request from the current project to SQL and write the output directly to Excel without UI component.

Question using the current Telerik.Windows.Controls.GridView.SpreadsheetStreamingExport.for.Wpf or Telerik.Windows.Documents.SpreadsheetStreaming. Can I  do SQL query take the result/data store object and write the output directly to Excel or must I use a UI like RadGridView then export to Excel?

I will need to set the Excel worksheet format before import data.

 

[Tanya]

Hi,

With SpreadStreamProcessing, you can export the workbook without any dependencies on the platform or UI. Due to the specifics of the library's API, a direct conversion from data table is not available. Thus, you will need to iterate through the result returned by the database and insert the values into the cells of the worksheet.

Using RadGridView you can pass the data from the database and export it as well. However, the control requires to be visualized, which will inevitably affect the performance of the scenario.

Since you mentioned you won't need UI, I would suggest you stick with SpreadStreamProcessing. You can find examples of how to use that library in our documentation and SDK demos.

Hope this information is helpful.

Regards,
Tanya
Progress Telerik

[herb]

Hello Tanya,

Thank you for your response. I'm reviewing Telerik documentations and the SDK Demos. Will update shortly.

[herb]

Tanya,

How do you go to a specific CELL? Looking at the SDK demo:

AppendWorksheetToExistingWorkbook\program.cs

I see 

using (ICellExporter cell = row.CreateCellExporter())
{
    cell.SetValue("value 1");
    cell.SetValue("value 2");
    cell.SetValue("value 3");
}

but the data in cell A1 is over written. In VS I don't seem to specify "cell" 2 to set the value to "value 2".

 

[Tanya]

Hi Herb,

Indeed, in this case, only the last value set to the current cell is preserved in the document. I admit that this is an issue in the example and will make sure it is fixed in a timely manner. I have updated your Telerik points in appreciation for bringing this to our attention. Here is how the updated code will look like:

using (IRowExporter row = worksheet.CreateRowExporter())
{
    using (ICellExporter cell = row.CreateCellExporter())
    {
        cell.SetValue("value 1");
    }
    using (ICellExporter cell = row.CreateCellExporter())
    {
        cell.SetValue("value 2");
    }
    using (ICellExporter cell = row.CreateCellExporter())
    {
        cell.SetValue("value 3");
    }
}

As to the functionality in question, each invocation of worksheet.CreateRowExporter() generates a new row in the worksheet and similarly, row.CreateCellExporter() creates a cell in the current row. If you would like to skip several rows or cells and start from cell C3, for example, you can achieve that using the SkipRows() and SkipCells() methods respectively. 

I would also like to suggest you check the GenerateDocument example as it demonstrates more functionalities for inserting content into a worksheet.

Hope this information is useful.

Regards,
Tanya
Progress Telerik

[herb]

Tanya, I spent the weekend and stumbled to the conclusion about repeating the "using(icelexporter....)" in my test I can't get the .SkipColumn(x) to work:

using (IWorksheetExporter worksheet = workbook.CreateWorksheetExporter(sheetName))
{
    string svalue = "";
    using (IRowExporter row = worksheet.CreateRowExporter())
    {
        for (int i = 0; i < 3; i++)
        {
            switch (i)
            {
                case 0:
                    svalue = "value 1";
                    break;
                case 1:
                    svalue = "value 2";
                    break;
                case 2:
                    svalue = "value 3";
                    break;
            }

            using (ICellExporter cell = row.CreateCellExporter())
            {
                cell.SetValue(svalue);
            }
        }
    }

    worksheet.SkipColumns(1);//moving to a column's cell?

    using (IRowExporter row = worksheet.CreateRowExporter())
    {
        for (int i = 0; i < 3; i++)
        {
            switch (i)
            {
                case 0:
                    svalue = "value 4";
                    break;
                case 1:
                    svalue = "value 5";
                    break;
                case 2:
                    svalue = "value 6";
                    break;
            }

            using (ICellExporter cell = row.CreateCellExporter())
            {
                cell.SetValue(svalue);
            }
        }
    }

}

Getting an exception see jpg.

Must I exit the "using (IWorksheetExporter worksheet = workbook.CreateWorksheetExporter(sheetName))" first then start over?

 

 

[herb]

Tanya, my quest is to:

SQL result will be directed to a DataTable, which has 13 columns and many thousands row.

I will need to:

-populate the header in row 1

-populate row 2 will start the DT data

-row 3 will have column 1, 2, 5, 7 empty but other DT data will populate the remaining columns.

The net being I need to iterate rows, columns and cells.

Hope this makes sense.

 

[Tanya]

Hi Herb,

I have prepared a sample demo to demonstrate how you could achieve a similar scenario. The example fills the data following the description you have provided. It uses dummy content, but you can easily replace it to use the content you have from the data table.

Hope this helps.

Regards,
Tanya
Progress Telerik

[herb]

Tanya,

Brilliant! I see what is going on. I'm use to other methods of accessing the rows and columns.

Thank you.