Insert multiple Worksheet or ranges, if possible in Word Document

[Ramesh]

Hi,

I'm trying to see whether it is possible to use Telerik Document Processing to do the following

I've say dynamic number of SpreadSheet created with ClosedXML.Reports (data coming from SQL server)

I've one Word Template document where I need a way to insert the above worksheet in certain pages. If there is a better way to show(insert) the spreadsheet, I'm open to any suggestion.

I also need some fields (merge fields possibly) in the Template file and once this is processed, I want to save the file as a docx file and save as pdf file also.

 

I thought Telerik Document Processing will help but, I really confused with the namespaces, trial products etc Rad vs wordprocessing vs spreadsheetprocessing etc.

 

I would appreciate if you could help me with what I need product wise, namespace wise and some help on the process to do this

 

Thanks

Ramesh

[Dimitar]

Hello Ramesh,

This is possible by using RadSpreadProcessing and RadWordProcessing libraries. Here is an overview of the required steps:  

1. Import the xlsx document. 
2. Get the used cell range and iterate the cells.
3. Import the template document and insert a table to it.
4. Export the result document.

Here is how you can implement the above approach (the attached image shows the required assemblies):

string fileName = @"D:\SampleFile.xlsx";

Workbook workbook;
var xlsxFormatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();

using (Stream input = new FileStream(fileName, FileMode.Open))
{
    workbook = xlsxFormatProvider.Import(input);
}

var worksheet = workbook.Sheets[0] as Worksheet;
CellRange usedCellRange = worksheet.UsedCellRange;

RadFlowDocument document;
var docxFormatProvider = new Telerik.Windows.Documents.Flow.FormatProviders.Docx.DocxFormatProvider();

using (Stream input = new FileStream(@"D:\Template.docx", FileMode.Open))
{
    document = docxFormatProvider.Import(input);
}
var table = document.Sections[0].Blocks.AddTable();


for (int rowIndex = usedCellRange.FromIndex.RowIndex; rowIndex <= usedCellRange.ToIndex.RowIndex; rowIndex++)
{
    TableRow row = table.Rows.AddTableRow();

    for (int columnIndex = usedCellRange.FromIndex.ColumnIndex; columnIndex <= usedCellRange.ToIndex.ColumnIndex; columnIndex++)
    {
        CellSelection cell = worksheet.Cells[rowIndex, columnIndex];
        string value = cell.GetValue().Value.RawValue;

        TableCell newCell = row.Cells.AddTableCell();
        newCell.Blocks.AddParagraph().Inlines.AddRun(value);
    }
}

using (FileStream fs = new FileStream(@"D:\resultDoc.docx", FileMode.OpenOrCreate))
{
    docxFormatProvider.Export(document, fs);
}

You need to include the following namespaces:

using Telerik.Windows.Documents.Flow.Model;
using Telerik.Windows.Documents.Spreadsheet.Model;

I hope this will be useful. Should you have further questions, I would be glad to help.

Regards,
Dimitar
Progress Telerik

[Ramesh]

Thanks for the detailed Post.

I tried to implement what you have mentioned but, for some reason, I'm not able to get a reference to Telerik.Windows.Documents.Spreadsheet dll at all. I tried nuget packages.

Please see the attached file. The main thing I need to do is copy of say 10 worksheet ranges into 10 different places in word template.(kind of replace rather than read). I also need to insert some dynamic no. of images into the word documents and name them say appendix a, b, c etc. 

The Worksheets will contain heatmap, chart etc. Hope to copy with out any format loses.

Is there way to html to be displayed as formatted text - Spreadsheet

Can you insert into a mergefield some formatted text - Word

Can you remove a word page by selecting a image in that page

Thanks

Ramesh

[Martin]

Hi Ramesh,

I am splitting my answer into several points:

1. Referencing Spreadsheet binaries: From the provided screenshot I see you are installing .NET Standard NuGet packages. The SpreadProcessing is the only Telerik Document Processing library that is still available for the full .NET Framework only (until the next official release scheduled for this month). If you are building your application as a cross-platform app and you need SpreadProcessing library for .NET Standard I would like to suggest you to wait for the official release. If this is not your case, you can download our .NET Framework binaries (Here you can check where you can download the binaries from). Please note that for .NET Framework & .NET Core compatibility pack projects, the references contain "Windows" in their assembly names (e.g. Telerik.Windows.Documents.Core.dll).
2. Importing XLSX document and get the used cell range: You can import an existing XLSX document using SpreadProcessing`s XlsxFormatProvider. Check the following example:
   

   string fileName = @"D:\SampleFile.xlsx";
   
   Workbook workbook;
   var xlsxFormatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();
   
   using (Stream input = new FileStream(fileName, FileMode.Open))
   {
       workbook = xlsxFormatProvider.Import(input);
   }
   
   var worksheet = workbook.Sheets[0] as Worksheet;
   CellRange usedCellRange = worksheet.UsedCellRange;

3. Importing DOCX document (template): You can import the DOCX document using WordsProcessing`s DocxFormatProvider. Check the following example:

   RadFlowDocument radFlowDocument;
   var docxFormatProvider = new Telerik.Windows.Documents.Flow.FormatProviders.Docx.DocxFormatProvider();
   
   using (Stream input = new FileStream(@"D:\Template.docx", FileMode.Open))
   {
       radFlowDocument = docxFormatProvider.Import(input);
   }

4. Importing XLSX range (usedCellRange) in the template via iterating usedCellRange`s cells:
   

   var table = radFlowDocument.Sections[0].Blocks.AddTable();
   
   
   for (int rowIndex = usedCellRange.FromIndex.RowIndex; rowIndex <= usedCellRange.ToIndex.RowIndex; rowIndex++)
   {
       TableRow row = table.Rows.AddTableRow();
   
       for (int columnIndex = usedCellRange.FromIndex.ColumnIndex; columnIndex <= usedCellRange.ToIndex.ColumnIndex; columnIndex++)
       {
           CellSelection cell = worksheet.Cells[rowIndex, columnIndex];
           string value = cell.GetValue().Value.RawValue;
   
           TableCell newCell = row.Cells.AddTableCell();
           newCell.Blocks.AddParagraph().Inlines.AddRun(value);
       }
   }

5. Inserting an image into the template: There are two types of supported image types: ImageInline and FloatingImage. For more information and examples please check the corresponding help topics: Inserting a FloatingImage help topic and Inserting an ImageInline help topic
6. HTML displayed as formated text (Workbook): Currently, the import of an HTML file into a Workbook is not supported, but importing an HTML file into a RadFlowDocument is supported using HtmlFormatProvider. For more information and examples, please, check the WordsProcessing`s HtmlFormatProvider help topic.
7. Inserting Merge Fields: For more information and examples, please, check the Inserting Merge Fields help topic. We have as well a good SDK example in our GitHub repository: MailMerge.
8. Removing a page from a RadFlowDocument by selecting an image on that page: The RadWordsProcessing library doesn’t actually measure the document elements inside. In other words, it doesn’t have a notion about how the space-specific elements will occupy and the page it will be rendered on, so the RadFlowDocument holds a collection of Section elements instead of Pages. Thus, I would suggest a way how to delete a Section containing the specific image inside it. Check the following code snippet:
   

   IEnumerable<ImageInline> image = this.document.EnumerateChildrenOfType<ImageInline>();
   Section firstSection = image.First().Paragraph.Parent as Section;
   this.document.Sections.Remove(firstSection);

I hope you find this information helpful.

Regards,
Martin
Progress Telerik