none
Migrate from XLS to XLSX format in Biztalk Map RRS feed

  • Question

  • Hi All,

    Currently I am using one map where source is xml and output I need in XLSX format.

    In map I am using Custom xslt code to generate excel format. Output I am getting as .xls extension. But I need in xlsx extension.

    Currently in target schema I am using schemas.microsoft.com.office dll from which I am selecting excelss schema.

    below is the configuration of custom xslt:

    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
         xmlns:o="urn:schemas-microsoft-com:office:office"
         xmlns:x="urn:schemas-microsoft-com:office:excel"
         xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
         xmlns:html="http://www.w3.org/TR/REC-html40">
          <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
            <Author></Author>
            <LastAuthor></LastAuthor>
            <LastPrinted>2018-01-10T19:17:09Z</LastPrinted>
            <Created>2018-01-10T19:17:09Z</Created>
            <LastSaved>2018-01-10T19:17:09Z</LastSaved>
            <Version>1.00</Version>
          </DocumentProperties>
          <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
            <AllowPNG/>
          </OfficeDocumentSettings>
          <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            <WindowHeight>9990</WindowHeight>
            <WindowWidth>16275</WindowWidth>
            <WindowTopX>480</WindowTopX>
            <WindowTopY>90</WindowTopY>
            <ProtectStructure>False</ProtectStructure>
            <ProtectWindows>False</ProtectWindows>
          </ExcelWorkbook>-->

    I want output in xlsx format.

    Any Help

    Wednesday, March 21, 2018 7:26 AM

All replies

  • Try saving the same output with .xlsx extension and see.


    Pi_xel_xar

    Blog: My Blog

    BizTalkApplicationDeploymentTool: BizTalk Application Deployment Tool/

    Wednesday, March 21, 2018 8:01 AM
    Answerer
  • Yes I have already tried this....File is not being opened. Getting below error.

    Wednesday, March 21, 2018 9:20 AM
  • How r u creating the excel file ? Are you using any office components ?

    Pi_xel_xar

    Blog: My Blog

    BizTalkApplicationDeploymentTool: BizTalk Application Deployment Tool/

    Wednesday, March 21, 2018 10:57 AM
    Answerer
  • I am creating Excel file by using XSLT. I am using DLL schemas.microsoft.com.office dll
    • Edited by Anshu Kumar Wednesday, March 21, 2018 11:14 AM
    Wednesday, March 21, 2018 11:13 AM
  • Hi All,

    Currently I am using one map where source is xml and output I need in XLSX format.

    In map I am using Custom xslt code to generate excel format. Output I am getting as .xls extension. But I need in xlsx extension.

    Currently in target schema I am using schemas.microsoft.com.office dll from which I am selecting excelss schema.

    below is the configuration of custom xslt:

    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
         xmlns:o="urn:schemas-microsoft-com:office:office"
         xmlns:x="urn:schemas-microsoft-com:office:excel"
         xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
         xmlns:html="http://www.w3.org/TR/REC-html40">
          <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
            <Author></Author>
            <LastAuthor></LastAuthor>
            <LastPrinted>2018-01-10T19:17:09Z</LastPrinted>
            <Created>2018-01-10T19:17:09Z</Created>
            <LastSaved>2018-01-10T19:17:09Z</LastSaved>
            <Version>1.00</Version>
          </DocumentProperties>
          <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
            <AllowPNG/>
          </OfficeDocumentSettings>
          <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            <WindowHeight>9990</WindowHeight>
            <WindowWidth>16275</WindowWidth>
            <WindowTopX>480</WindowTopX>
            <WindowTopY>90</WindowTopY>
            <ProtectStructure>False</ProtectStructure>
            <ProtectWindows>False</ProtectWindows>
          </ExcelWorkbook>-->

    I want output in xlsx format.

    Any Help

    Hi Anshu,

    To create a proper .xlsx file the best way is using a third party component and not reinvent the wheel. One I have used is BizExcel. It is free and works great.

    Get it from CodePlex while it is still available:

    BizExcel - CodePlex Archive


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com


    Wednesday, March 21, 2018 12:37 PM
  • I dont want to use or include any custom pipeline component for it.

    Is it possible using Map and XSLT. Likewise I have implemented currently. 

    Wednesday, March 21, 2018 1:16 PM
  • Import your file in Excel, save as XML Spreadsheet 2003
    You now have the document you need it can make the xsl transformation easier to create

    An article using this approach
    https://www.techrepublic.com/article/convert-an-xml-file-into-an-excel-spreadsheet-with-this-technique/

    A sample transform, xml output from database table and transformed to excel xml

    <?xml version="1.0" encoding="utf-8"?>
    <xsl:stylesheet version="1.0"
        xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    	xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    	xmlns:user="urn:my-scripts"
    	xmlns:o="urn:schemas-microsoft-com:office:office"
    	xmlns:x="urn:schemas-microsoft-com:office:excel"
    	xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >
    
    	<xsl:template match="/">
    		<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    		  xmlns:o="urn:schemas-microsoft-com:office:office"
    		  xmlns:x="urn:schemas-microsoft-com:office:excel"
    		  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    		  xmlns:html="http://www.w3.org/TR/REC-html40">
    			<xsl:apply-templates/>
    		</Workbook>
    	</xsl:template>
    
    	<xsl:template match="/*">
    		<Worksheet>
    			<xsl:attribute name="ss:Name">
    				<xsl:value-of select="local-name(/*/*)"/>
    			</xsl:attribute>
    			<Table x:FullColumns="1" x:FullRows="1">
    				<Row>
    					<xsl:for-each select="*[position() = 1]/*">
    						<Cell>
    							<Data ss:Type="String">
    								<!-- <xsl:value-of select="local-name()"/> -->
                    <xsl:value-of select="@label"/>
    							</Data>
    						</Cell>
    					</xsl:for-each>
    				</Row>
    				<xsl:apply-templates/>
    			</Table>
    		</Worksheet>
    	</xsl:template>
    
    	<xsl:template match="/*/*">
    		<Row>
    			<xsl:apply-templates/>
    		</Row>
    	</xsl:template>
    
    	<xsl:template match="/*/*/*">
    		<Cell>
    			<Data ss:Type="String">
    				<xsl:value-of select="."/>
    			</Data>
    		</Cell>
    	</xsl:template>
    </xsl:stylesheet>

    hth /Peter

    Wednesday, March 21, 2018 2:41 PM
  • I dont want to use or include any custom pipeline component for it.

    This will prevent you from creating a .xslx file on any platform. Nothing to do with BizTalk.

    Is it possible using Map and XSLT. Likewise I have implemented currently. 


    Yes, but, .xslx is entirely different from previous .xls flavors in that it is not just a data format, it is also a container format, specifically zip encoded. Meaning, yes, you can use xslt to create the internal document(s), but you also need code to zip them into the .xlsx structure.
    Wednesday, March 21, 2018 2:56 PM
    Moderator
  • This is not working for XLSS format
    Thursday, March 22, 2018 8:33 AM
  • How can we zip them into .xlsx structure If have the xml spreadsheet of 2003
    Thursday, March 22, 2018 8:34 AM
  • Hi,

    You could use a custom pipeline component. :)

    Br,

    Leo


    Did my post help? Please use "Vote As Helpful", "Mark as answer" or "Propose as answer". Thank you!

    Thursday, March 22, 2018 10:31 AM
  • How can we zip them into .xlsx structure If have the xml spreadsheet of 2003

    So...you can't.  Excel Xml as supported in Excel 2003 is completely different from .xlsx supported in Office 2007 and later.

    What you likely want to do is completely doable with BizTalk Server but...you need to do a lot more legwork to figure out exactly what is required, what is involved, how the file formats work, how they will be used, etc.  I say this because there is a significant gap between the questions you've asked and what is technically possible.

    Thursday, March 22, 2018 1:33 PM
    Moderator
  • Yes, either use the old 2003 XML format direct or a Pipeline Component to generate XLSX as you need binary libraries from eg the Opem XML SDK

    /Peter

    Thursday, March 22, 2018 1:37 PM
  • I dont want to use or include any custom pipeline component for it.

    Is it possible using Map and XSLT. Likewise I have implemented currently. 

    No, not possible.  As a xlsx it is not just one XML file, it is a set of them in a folder structure that is then zipped up.   There is no way to do that in just XSLT.
    Thursday, March 22, 2018 11:48 PM
  • I dont want to use or include any custom pipeline component for it.

    Is it possible using Map and XSLT. Likewise I have implemented currently. 

    Anshu, you have a few specialists here telling you how to do it, and you tell us you do not WANT to do it that way. Now, the question that begs to be asked is WHY?

    Why do you not want to use a pipeline component?

    Pipeline components are easy to write and use. There a few guidelines you need to follow, and that is it. It works. We use them often.

    Developing Custom Pipeline Components

    The link above explains how to do it. If you have questions about that, I recommend you post them on another thread in this same Forum. We will be happy to help, but our job here, on this question, is done.


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    Friday, March 23, 2018 11:09 AM
  • Thanks All,

    I achieved this through adding code for converting xls to xlsx to in my existing pipeline component . Below piece of code worked for me:

     Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                        excelApp.Visible = false;

                        Microsoft.Office.Interop.Excel.Workbook eWorkbook = excelApp.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                        eWorkbook.SaveAs(Xlsxpath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                        eWorkbook.Close(false, Type.Missing, Type.Missing);

    Friday, March 30, 2018 5:32 AM
  • Sure, that will work, but keep in mind....

    The Office Automation classes are not designed for server use.  So, set the Send Port for Ordered Delivery to avoid any threading issues and make sure you follow any advice on closing and disposing of the OA classes.

    Friday, March 30, 2018 12:32 PM
    Moderator