3

I have a query in SQL Server that returns me 5 columns with some data.

I want to create a BizTalk application that saves me the result of my query in an Excel file. I will create a SP for polling, using wcf adapter to do the communication between BT and SQL.

I don't know how to generate a file.xls. Is it possible?

Thanks

Jook
  • 4,564
  • 3
  • 26
  • 53
OmnipresentPerception
  • 93
  • 1
  • 9
  • 1
    .xls is a binary file - it will be difficult to generate this directly without using a 3rd party library, e.g. [epplus](http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp). Why not use the flat file adapter and create a .csv? – StuartLC Oct 24 '12 at 15:39

2 Answers2

1

Any .xls file can be saved as a XML Workbook ( File-Save As from Excel), which is an XML document. You can see that this an XML doc you open it in notepad.

Now, the trick is to get hands on the schema used for generating this, which is perhaps proprietary for microsoft and may not be given out. So, the workaround is, if your excel file is failrly simple, you can save it as the Workbook and see the XML and generate an XSD based on it.

Now, map your incoming XML to this Workbook XSD and the resultant XML will open directly in Excel, when you double-click on it.

Hope this helps.

user1826905
  • 558
  • 3
  • 9
0

in that case you need to developed a custom pipeline

biztalk-custom-excel-pipeline-component

MIkCode
  • 2,655
  • 5
  • 28
  • 46
  • unfortunately the output MUST be an excel file and unfortunately the link for custom pipeline is for a Decode xls file. I had to to realize en encoding custom component...Any suggest? – OmnipresentPerception Oct 24 '12 at 15:16