Excel Controls group first post

Hi All,

The Excel Controls project was originally intended to provide SmartForm controls that interact with Excel Files.

Note that this has been tested locally and works fine for me.  It has not been tested under load in a live environment.  Please test on your side before putting it into any production environment.

Important Note:

This project was a chance for me to try building a SmartForm control.

However on hindsight, changing this to a service broker makes it easier to maintain.

Please use the following brokers if you need to do export/import functionality.

Export to Excel (Adam castle took the code from this control and converted it to a service broker.  Thanks Adam!)

http://community.k2.com/t5/K2-blackpearl/ADO-query-to-Excel/ba-p/1115

Import from Excel

http://community.k2.com/t5/K2-blackpearl/Excel-Import-Service-Broker/ba-p/65814

Where is the documentation on how to use the "Excel Export" in Smartforms?

Hi,

I was able to get this control working using your instructions, but have some questions about the SmartObject query parameter to generate the Excel file.

In your example it uses SELECT * FROM Process_Instance which appears to use the "Workflow Reports -> Workflow General -> Process Instance" smartobject.  Is there a specific format to query other custom smartobjects?  Does it have to have a "List" method or will a "Get List" method also work?

For example, I have a custom SmartObject that read/write smartform data into the SmartBox service.  If I try SELECT * FROM CustomSmartojbect  it fails and says 'Query Not Supported' in the log.    (it only has a Get List method).

Thanks

I have a similar question as above.  I need to export data from a SharePoint Smart Object to Excel via Smart Forms.  Does the Smart Object Query parameter only accept ADO queries to query the K2 database or can it query a SmartObject that is already populated?  Can you provide an example of quering a different SmartObject, please?  Thanks!

Has anyone had trouble with is control in a load balanced environment.  When I loaded it into our QA environment, which is a single server, it works correctly.  I then loaded it into both servers in our production environment i was able to place it on a view and configure it for use.  

When I try and run the view and export to Excel I get an error "405 - Method not allowed"

Hi,

How to use excel controls in smart forms to export the data to excel sheet.

Can i get any forum link or document on this?

Hi guys, just managed to use this control to query any smartobject that I have created in my environment.

E.g - My smartobkect is called Accruals.RevenueAccrual , so in the query paramter i enter SELECT * FROM Accruals_RevenueAccrual

Hope this helps ! 

Hi All,

I have deployed Smartobject(ExcelFIle.sodx) and registered the Excel Export control using RegisterCustomControl.bat.

In the K2 designer while creating the listview from the smartobject(ExcelFIle.sodx) I am not able to find the Excel Export  control in the controls tab.

Need your help.

Thanks,

Sathya

Same here. The control is not there. 

Hi,

I have the same Problem. I can't find the Control in the Designer. The .dll is in the bin Folder and the SmartObject is Deployed.

Our K2 Version is 4.6.9.

Could you help me?

Thanks and Regards

Joerg

Hi,

I am trying to implement an export functionality using Excel Export control. I found following issues which needs to be resolved.

- Excel Export control not working when the Smart Object returns more than 26 columns. The control is not even able to save the excel file in the K2 DB.
- If the column names returned by Smart Object contain spaces, they are getting substituted by "_" (underscore) while being displayed in Excel column header.

Thanks in advance!
Siddhartha Dutta

HI,

I tested the control with the default query string in the PDF which returns data from a table innside of the K2 DB.

This worked fine.

But when I tried to export data from a table outside of the K2 DB then I got the following error in the event logs:

Event code: 3005
Event message: An unhandled exception has occurred.

Exception information:
Exception type: SmartObjectNotFoundException
Exception message: SmartObject tSOExpenseClaimApplication] could not be found.

I might be confused as to how the query string works.

So here is my example of the query string: 

select * from SOExpenseClaimApplication

Can any one explain this to me in more detail ?

Thanks in advance!

TheDude!

i just got it to work on our dev env : 

k2 blackpearl version : 4.6.11

Smartforms version : 4.6.11

 @TheDude : i think your smartobject doesnt exist. you need to use the name of the SmartObject rather than the sql table itself.

found this wonderful blog post that explains in detail the steps to make it work. 

To the posters above who cant find the control in the designer , please follow steps in the article below to register your assembly manually. the bat file isnt seem to be registering the controls anymore. 

http://blog.velocity-it.com/2016/02/04/how-to-register-and-use-the-excel-control-for-k2-smartforms/.

hope it helps someone.

Hi Johnny

I have a question, can i get the export file by one click on the export button.?

Hi Sidd,

I fixed limited column when export.

- go to ExcelUtils.cs file and edit  this code below

private static stringr] excelColumns ={"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
"AA","AB","AC","AD","AE","AF","AG","AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ",
"BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ"};

The trouble I'm having is that this control works successfully in our Test environment, but not in our Production environment. There are configuration variations between Test and Production, but we can't isolate what variation will allow this control to work successfully in Production. When we try to execute control in production, the Excel file is created successfully, but the resulting file contains only the column names, without any of the other row values beneath them. There is one exception to this scenario... When executing this control method against a SmartObject that was created by the System Administrator, the resulting Excel file is successfully created completely. However, SmartObjects created by other users, including System created SmartObjects, like the built-in ones for Reports, are all resulting in only column names in the Excel file.

Has anyone tried using this with SharePoint 2013? I want to export a couple of list items into excle but I am getting error message:

Error Details: Method: SharePointService.initializeContext.","anonymous","0.0.0.0","C:Program Files (x86)K2 blackpearlHost ServerBin","13286733","0e90d8e7d100462f8b4eaa8037e56aab",""
"13286734","2016-08-27 17:36:07","Error","SmartObjects","10702","Error","SourceCode.SmartObjects.ServiceBroker ExecuteSmartObject]","10702 An error occurred in the workflowsdev service Instance. The remote server returned an error: (401) Unauthorized.Failed to initialize the Context:
URL: http://site/subsite
Username:

Error Details: Method: SharePointService.initializeContext
x-ms-diagnostics : 3001000;reason='There has been an error authenticating the request.';category='invalid_client'
SPRequestGuid : 1aed9d9d-d942-00c7-685a-24de8a0f632c
","anonymous","0.0.0.0","C:Program Files (x86)K2 blackpearlHost ServerBin","13286734","e6fc0361aa554af2bfb5d92a1d1fe539",""

  @pdejongh17 With SP2013 you need to configure the broker to use OAuth.

Not sure if the broker supports it though. But that's pretty much the gist of the error exception you're seeing.

Hi,

anyone else having a problem with missing decimal point when exporting a column with numeric data type to Excel? Ie. value 10000,00 appears in Excel as 10000000000 ...

Initially when I was using it on Win 7 + Chrome - the problem was there (no decimal point in columns with numeric data type value in exported excel) but on Win 7 + IE 10/11 it was OK. Now with Win10 I have the same problem in CHROME and also in IE11 :(

Little workaround is to use the CONVERT and/or other functions in the query ... see examples below:

Simple conversions:

- CONVERT(decimal, SMOpropertyname) or CONVERT(numeric(12), SMOpropertyname) - then I (at least) get whole numbers users can work further with

Advanced conversions keeping also decimals in the exported value:

- REPLACE(CONVERT(nvarchar(50), SMOpropertyname), '.', ',') AS SMOpropertyname - returning values with 9 decimals in exported values

- REPLACE(STR(SMOpropertyname, 15, 2), '.', ',') AS SMOpropertyname - returning values with 2 decimals in exported values - THIS ONE SEEMS SO FAR AS BEST ONE

Hi

Is it possible to pass a parameter to the so?

Thx

Hello, I am unable to deploy this on my VM(K2 4.6.11). Can someone help?

General Error - Index was outside the bounds of the array when registering Excel Broker

Resolution:

Using the incorrect location causes this error, it needs to be Designer and not runtime.

Example

controlutil.exe register -assembly:”C:Program Files (x86)K2 blackpearlK2 SmartForms DesignerinExcelExport.dll”

Hello,

I tried usng this control to query a smartobject built from a sharepoint list and i end up getting the following error as a HTML inside the textbox where ID of the excel must appear.

<h2 class="h2 error">Server Error</h2> Exception of type &#39;SourceCode.SmartObjects.Client.SmartObjectException&#39; was thrown.

<h1 class="h1 error" style="display: none" id="ExHead">Exception Details:</h1>
<div id="ErrorMsg"> at SourceCode.SmartObjects.Client.SmartObjectClientServer.ExecuteSQLQueryReader(String sqlString, ExecuteSQLQueryReaderOptions options, DbParametert] dbParameters)
<br/> at SourceCode.Data.SmartObjectsClient.SODataReader..ctor(SOCommand command, CommandBehavior cmdBehaviour, Int32 startRecord, Int32 maxRecords)

Has anyone faced similar issue? Please help me out!

Thank you!

Hello

Has anyone tries using a stored procedure with this plug-in?

I have it successfully working for a view, passing an SQL statement like the following:
Select ..... from app_vwView where RegionID = 2

I have had to apply user security to the process so am now attempting to use a stored procedure to return the same columns:
exec app_spStoredProcedure @UserAd = 'AD
ame' ,@RegionID = 2
and
exec app_spStoredProcedure UserAd = 'AD
ame' ,RegionID = 2

Process is failing at the "Create Excel File" stage so temporary file is not created.

Has anyone who has used SPs got any tips on formatting the request? I can't really make out how the SOCommand/SODataAdapter C# classes work so have hit a bit of a brick wall.

Many thanks!

EDIT: User name is not easily filterable from a view hence the stored procedure to do the processing at the SQL level.

 This is one of the requirement in the my project, were all the comments need to be exported to excel. Ours is K2 4.7 with Oracle as backend. I used Oracle PROC to return the result in html table format within xml. Used JScript in the front end to export to excel and binded the smartobject value to Richtext box. No deployment of components in app server is required.

------------Backend Oracle Changes-----------

LQUERY here  is the query / data you want to export.

ctxh := DBMS_XMLGEN.newContext(L_QUERY);
                         
    -- XSLT Transformation to HTML :
    xslt_tranfsorm := NEW XMLTYPE('
        <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
          <xsl:template match="/ROWSET">
            <table ID=''tblData''>
              <tr>
                <th>YOURFIELD1</th>
                <th>YOURFIELD2</th>
                <th>YOURFIELD3</th>               
              </tr>
              <xsl:for-each select="ROW">
                <tr>
                  <td style="text-align:left;"><xsl:value-of select="YOURFIELD1"/></td>
                  <td style="text-align:left;"><xsl:value-of select="YOURFIELD2"/></td>
                  <td style="text-align:left;"><xsl:value-of select="YOURFIELD3"/></td>                 
                </tr>
              </xsl:for-each>
            </table>
          </xsl:template>
        </xsl:stylesheet>'); 
       
        queryresult := DBMS_XMLGEN.getXMLType(ctxh).transform(xslt_tranfsorm);
        L_DATA := queryresult.getClobVal();
       
        OPEN CUR FOR
            SELECT L_DATA FROM DUAL;

Create a Form with Rich Text Control bind the smart object. Create a data label and add the below code. Works very well for IE.

<a id="dlink" style="display:none;"></a><div id="name" style='display:none'>filename</div><button id="btn">Export to Excel</button><script type='text/javascript'>function isIE() {var isIE11 = navigator.userAgent.indexOf('.NET CLR') > -1;var isIE11orLess = isIE11 || navigator.appVersion.indexOf('MSIE') != -1;return isIE11orLess;} var tableToExcel = (function () {var uri = 'data&colon;application/vnd.ms-excel;base64,', template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></head><body><table>{table}</table></body></html>', base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }, format = function(s, c) { return s.replace(/{(w+)}/g, function(m, p) { return c

; }) };return function(table, name) {if (!table.nodeType) {table = document.getElementById(table);} var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}; var blob = new Blob([format(template, ctx)]); var blobURL = window.URL.createObjectURL(blob); if (isIE()) { csvData = table.innerHTML; if (window.navigator.msSaveBlob) { var blob = new Blob([format(template, ctx)], {type: "text/html"});navigator.msSaveBlob(blob, 'comments.xls');}}}})();function download(){var name = document.getElementById('name').innerHTML;tableToExcel('tblData', 'Sheet 1', name+'.xls');}var btn = document.getElementById('btn');btn.addEventListener('click',download);</script>