Questions on Telerik Document Processing Forum

6 answers

905 views

Accessing computed values of formula cells

when I import an Excel-Sheet with formulas,

in very simple cases,it converts correctly into a FormulaValueCell, gives me the formula and the GetResultValueAsString() gives the computed Value.

But with more complicated formulas, this doesn't seem to work.

var value = sheet.Cells[6, 2].GetValue();
var a = value.Value.GetResultValueAsString(CellValueFormat.GeneralFormat);
var b = value.Value.GetValueAsString(CellValueFormat.GeneralFormat);
var c = value.Value.RawValue;
var d = value.Value.ValueType;

a"='[2]Übernahme 11-12'!C7"
b"='[2]Übernahme 11-12'!C7"
c"='[2]Übernahme 11-12'!C7"
d = Text

So first, it's generally wrong to consider this a text-cell

Second, in the xlsx File, there is a <v> Tag for each cell, containing the actual (numeric) value of a cell, even of formula cells.

Is there a way to access this computed value ? I can find the <v>100</v> Tag in the xlsx-File, but I don't find a way to access it.

SpreadProcessing

answered on 28 Mar 2018

1 answer

335 views

Dispose

How should I dispose the target object?

The file is kept locked...and cannot be accessed later by other code.

Dim fileName As String = Server.MapPath("~/userfiles/file/excel/" & Session.SessionID & ".xlsx")

            Dim formatProvider As IWorkbookFormatProvider = New XlsxFormatProvider()

            Using output As New FileStream(fileName, FileMode.Create)
                formatProvider.Export(workbook, output)
            End Using

PdfProcessing

answered on 26 Mar 2018

4 answers

177 views

Create Linear Gradient

Dear Sirs,

I'm seaching for a pdf solution and found your product. Now I'm evaluating.

In the documentation there is an example for creating a gradient:

http://docs.telerik.com/devtools/aspnet-ajax/controls/pdfprocessing/concepts/colors-and-color-spaces

1.FixedContentEditor containerEditor = new FixedContentEditor(container);

2.LinearGradient linearGradient = new LinearGradient(new Point(0, 0), new Point(30, 30));

3.linearGradient.GradientStops.Add(new GradientStop(new RgbColor(0, 207, 0), 0));

4.linearGradient.GradientStops.Add(new GradientStop(new RgbColor(0, 102, 204), 0));

5.

6.containerEditor.GraphicProperties.FillColor = linearGradient;

7.containerEditor.DrawRectangle(new Rect(10, 10, 48, 29));

If I copy these code I just get a monochrome rectangle filled with the color (0,102,204).

Any help would be fine.

many thanks in advance

Gustav Meier

PdfProcessing

answered on 05 Mar 2018

1 answer

576 views

Array formula and SetValue() GetValue() - Is it possible ?

The spreadsheet widget supports array formulas with a manual Ctrl-Shift-Enter. (see bottom of https://docs.telerik.com/kendo-ui/controls/data-management/spreadsheet/end-user/list-of-formulas)

I want to do the same programmatically using the Spreadsheet.Model classes.

This example shows how I can set and get results of a simple calculation, however the array formula I want to use is an Error.

Indeed, the Sample.xlsx it creates, when opened shows #N/A where the array formula is. However, in Excel, when I edit the cell and do Ctrl-Shift-Enter, it is interpreted as an array formula and shows the proper result. The same formula in the spreadsheet widget also works (kudos to the dev team -- array formulas can make you squirrely).

console output

Starting

B2 retrieved 1

B3 retrieved 10

b4FormulaCell: =B2+B3

b4Result.RawValue: 11

k9CellValue: Telerik.Windows.Documents.Spreadsheet.Model.RangePropertyValue`1[Telerik.Windows.Documents.Spreadsheet.Model.ICellValue]

k9FormulaCell: Telerik.Windows.Documents.Spreadsheet.Model.FormulaCellValue

k9FormulaCell.RawValue: =MATCH(MIN(ABS(K2:K4-K8)),ABS(K2:K4-K8),0)

k9FormulaCell.ResultValueType: Error

Finished

program

using System;

using System.IO;

using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;

using Telerik.Windows.Documents.Spreadsheet.Model;

namespace ConsoleApp1

{

class Program

{

static void Main(string[] args)

{

Console.WriteLine("Starting");

var workbook = new Workbook();

var sheet = workbook.Worksheets.Add();

// B2

var b2 = sheet.Cells[1, 1];

b2.SetValue(1);

var b2CellValue = b2.GetValue();

var b2NumCell = b2CellValue.Value as NumberCellValue;

var b2Num = b2NumCell.Value;

Console.WriteLine($"B2 retrieved {b2Num}");

// B3

var b3 = sheet.Cells[2, 1];

b3.SetValue(10);

var b3CellValue = b3.GetValue();

var b3NumCell = b3CellValue.Value as NumberCellValue;

var b3Num = b3NumCell.Value;

Console.WriteLine($"B3 retrieved {b3Num}");

// B4 = B2 + B3

var b4 = sheet.Cells[3, 1];

b4.SetValue("=B2+B3");

var b4CellValue = b4.GetValue();

var b4FormulaCell = b4CellValue.Value as FormulaCellValue;

var b4Result = b4FormulaCell.GetResultValueAsCellValue();

Console.WriteLine($"b4FormulaCell: {b4FormulaCell.RawValue}");

Console.WriteLine($"b4Result.RawValue: {b4Result.RawValue}");

sheet.Cells[1, 10].SetValue(6); // K2

sheet.Cells[2, 10].SetValue(7); // K3

sheet.Cells[3, 10].SetValue(8); // K4

sheet.Cells[4, 10].SetValue(100); // K5

sheet.Cells[5, 10].SetValue(1000); // K6

sheet.Cells[6, 10].SetValue(10000); // K7

sheet.Cells[7, 10].SetValue(7.1); // K8

// https://www.extendoffice.com/documents/excel/1050-excel-find-closest-value.html // goal use MATCH look up to find index of value in K2:K4 nearest to K8

var arrayDiff = "ABS(K2:K4-K8)"; // formula for computing nearness

var matchFormula = $"=MATCH(MIN({arrayDiff}),{arrayDiff},0)"; // formula for computing index of nearest (smallest ABS diff)

sheet.Cells[8, 10].SetValue(matchFormula);

// K9, nearest index for 7.1 should be computed as 2, which corresponds to value of 7 in K3, which is the second item in K2:K4

var k9 = sheet.Cells[8, 10];

var k9CellValue = k9.GetValue();

var k9FormulaCell = k9CellValue.Value as FormulaCellValue;

// var k9Result = k9FormulaCell.GetResultValueAsCellValue();

Console.WriteLine($"k9CellValue: {k9CellValue}");

Console.WriteLine($"k9FormulaCell: {k9FormulaCell}");

Console.WriteLine($"k9FormulaCell.RawValue: {k9FormulaCell.RawValue}");

Console.WriteLine($"k9FormulaCell.ResultValueType: {k9FormulaCell.ResultValueType}");

// Console.WriteLine($"k9Result.RawValue: {k9Result.RawValue}");

using (var output = new FileStream(@"C:\Temp\Sample.xlsx", FileMode.Create))

{

new XlsxFormatProvider().Export(workbook, output);

}

Console.WriteLine("Finished");

}

SpreadProcessing

answered on 05 Mar 2018

1 answer

885 views

replace Word merge field or text with WordsProcessing merge field

Is there a way to take an existing Word document that is full of Word merge fields and replace them with merge fields that Telerik Document Processing understands («Zipcode» to MERGEFIELD Zipcode)?

I understand how I can create merge fields in code, but there are many templates with all sorts of formats and I would prefer to not have to recreate each one in code. As a second option is there a way to do a replace text with a MERGEFIELD? That would at least allow me to minimally modify the Word document.

These Word templates will continue to be used outside of the web and ideally I would want a user to be able to select a template (.docx) to load with allowable merge fields, and then I take care of merging them via document processing and return the nicely merged document based on the data in our application.

I do not see how I can easily just translate between the two.

WordsProcessing

answered on 15 Feb 2018

1 answer

213 views

Print titles

Hi,

is it possible to specify a row in worksheet, which will be printed on each page as header? In Excel Page setup this is called "Rows to repeat at top".

Thanks

Alex

SpreadProcessing

answered on 14 Feb 2018

1 answer

609 views

Using in-built style when programmatically adding table to RadFlowDocument

I'm using a template to generate a document with a table in it. I'm adding the table, from scratch, to the document dynamically. This all exists in an ASP.Net MVC application. The table is being built ok. The only thing is I can't get it to take on the inbuilt Word table style I want. It doesn't matter what style i stry, it's not working.

See code example.

01.private static void BuildScheduleItemsTable(RadFlowDocument document,

02. RadFlowDocumentEditor editor,

03. ScheduleOfWork scheduleOfWork,

04. ScheduleStage scheduleStage = ScheduleStage.Preliminary,

05. ScheduleRecipient scheduleRecipient = ScheduleRecipient.Customer)

06. {

07. List<ScheduleOfWorkItem> scheduleOfWorkItems =

08. GetScheduleOfWorkItemsFor(scheduleOfWork.ScheduleOfWorkID, scheduleStage);

09.

10.

11. Table table = editor.InsertTable(scheduleOfWorkItems.Count + 1, 5);

12. table.StyleId = "MediumGrid3-Accent4";

13. table.PreferredWidth = new TableWidthUnit(TableWidthUnitType.Auto);

14. table.LayoutType = TableLayoutType.AutoFit;

15.

16. // create header row

17. TableRow headerRow = table.Rows[0];

18. headerRow.RepeatOnEveryPage = true;

19. //headerRow.CanSplit = false;

20. //headerRow.RepeatOnEveryPage = true; // header row repeats when new page

21. headerRow.AddHeaderCellAndText("Description", 0);

22. headerRow.AddHeaderCellAndText("Quantity", 1);

23. headerRow.AddHeaderCellAndText("Cost", 2);

24. headerRow.AddHeaderCellAndText("VAT Amount", 3);

25. headerRow.AddHeaderCellAndText("Total Cost Inc VAT", 4);

26.

27. int rowIter = 1;

28. foreach (ScheduleOfWorkItem scheduleOfWorkItem in scheduleOfWorkItems)

29. {

30. TableRow itemRow = table.Rows[rowIter];

31. //itemRow.CanSplit = false;

32. itemRow.UpdateDescriptionCell(scheduleOfWorkItem, scheduleRecipient);

33. itemRow.UpdateQuantityCell(scheduleOfWorkItem);

34. itemRow.UpdateCostCell(scheduleOfWorkItem);

35. itemRow.UpdateVatAmountCell(scheduleOfWorkItem);

36. itemRow.UpdateTotalCostIncVatCell(scheduleOfWorkItem);

37. rowIter++;

38. }

39. }

The Update...Cell and AddHeaderCellAndText methods are just methods that create paragraphs and runs inside the cells that were created when the table was created. No matter what I set in table.StyleId it doesn't seem to take effect

WordsProcessing

answered on 14 Feb 2018

0 answers

129 views

Inserting row in table by index?

Is there a way to insert row with specified index? e.g. I have table with 10 rows and I want to insert new row between row 5 and 6, is it possible?

WordsProcessing

asked on 05 Feb 2018

1 answer

159 views

How to reference table from existing docx tamplate?

Is there a way to reference table from existing docx template? My template is already formatted and I just need to populate items from that table.

fileFormatProvider = New DocxFormatProvider()
Dim fileName As String = "D:\myTemplate.docx"
fileExtension = ".docx"
Using input As New FileStream(fileName, FileMode.Open)
document = fileFormatProvider.Import(input)
End Using

Dim editor As New RadFlowDocumentEditor(document)

dim myTemplateTable as Table = editor.findTable?? (Cant find any sample or documentation to reference the table)

'Now I need to insert row after 4th row (Note that I have existing rows on 5th which is the subtotal, 6th credits, 7th expenses, 8th Grand Total)

'This new row/rows are dynamic and based on how many items

Dim myNewRow5 as TableRow = myTemplateTable.Rows(3).AddTableRow/InsertTableRow?? (cant find any sample either)

'Once I got the reference of the row I can now populate cells with my data...

Hoping for any information for this scenario.

Thanks in advance,

RJ

WordsProcessing

answered on 05 Feb 2018

2 answers

311 views

Font size problem with HTML export inline styles

Greetings.

Imported the following HTML into the DPL.

<style type="text/css">

p {

font-family: Calibri;

font-size: 14.6666666666667px;

margin-top: 0px;

margin-bottom: 0px;

line-height: 115%;

}

.TelerikNormal {

font-family: Calibri;

font-size: 14.6666666666667px;

margin-top: 0px;

margin-bottom: 0px;

}

.TelerikHeading1 {

font-family: Cambria;

font-size: 28px;

font-weight: bold;

color: #4F81BD;

margin-top: 18.6666666666667px;

margin-bottom: 18.6666666666667px;

}

.TelerikHeading2 {

font-family: Cambria;

font-size: 20px;

font-weight: bold;

color: #4F81BD;

margin-top: 18.6666666666667px;

margin-bottom: 5px;

}

</style>

<h1 class="TelerikHeading1">

<span>Study Results</span>

</h1>

<h2 class="TelerikHeading2">Issue 1</h2>

<p>This is the issue</p>

Then we export using the following settings in order to include the HTML in an email:

HtmlExportSettings exportSettings = new HtmlExportSettings()

{

DocumentExportLevel = DocumentExportLevel.Fragment,

ImagesExportMode = ImagesExportMode.External,

ImagesFolderPath = @"C:\Temp\TestImages",

ImagesSourceBasePath = "cid:",

StylesExportMode = StylesExportMode.Inline

};

htmlFormatProvider.ExportSettings = exportSettings;

string body = htmlFormatProvider.Export(outputDocument);

The resulting HTML replaces the <h1> and <h2> tags as expected, but it looks like only the color from the assigned classes is being used. The rest of the styles appear to come from the <p> style.

Is there a workaround for this?

WordsProcessing

answered on 05 Feb 2018