Baanboard.com

Go Back   Baanboard.com > Blogs > vamsi_gujjula's blog

User login

Frontpage Sponsor

Main

Google search


Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
17%
200 - 500 GB
26%
500 - 800 GB
3%
800 - 1200 GB
9%
1200 - 1500 GB
11%
1500 - 2000 GB
14%
> 2000 GB
20%
Total votes: 35

Baanboard at LinkedIn


Reference Content

 
Excel Report with color and other formats
By vamsi_gujjula at 15 Sep 2014 - 15:57

Excel one of the most widely used S/W, it’s the best tool for analysis. Most of the end users like the reports in excel. Many time they prefer having highlights for important data ( Like Colours , Bold and  Italic ETC) ,What would you do when you are supposed to format the report in excel. Probably depending on the importance of the requirement & Man/hours required, you would decide from the following 

 

  1.       Creating a CSV file with no formatting, saying it’s not possible to generate formatted report (possible not aware the formatted report can be generated with VBS-Macro or Spreadsheet ML) .Now even creating CSV file has its own challenges because different regions have different REGIONAL SETTING with in the client system for Example : In USA  the CSV file is Comma Separated Value but where as in Germany CSV is semicolon Separated Value, it’s not just the separator but also the Numeric representation changes. Since this not being the main topic leaving it here ;)
  2.       The other option would be creating VBS – Macro, Storing in on server or any common place and transferring it to client and executing the macro. Now this too has its own draw backs like turning on and off of security setting on client side, Sometimes the VBS might be the right approach.      

 

VBS is not the only solution, now I would like say that there exists another solution that is creating a OpenXML , One need  to create flat file with OpenXML format which is nothing but Improvised XML format for Microsoft applications. One can have colour/ Bold/ italic ETC format for a cell or even column.

Refer :  http://technet.microsoft.com/en-us/magazine/2006.01.blogtales.aspx  for the basic  guide for  OpenXML

Refer :   http://msdn.microsoft.com/en-us/library/bb226687(v=office.11).aspx#office05012006excel2003spreadsheetml_introduction

 The problem with OpenXML file is it should have style defined in the header,  it mean whenever you need any new formatting it should be included in the header  but yes one can include  the general ones like the bold , italic and 2-3 colours  .

 

BTW  This is not my IDEA (Open XML)  I came across a code , which was written by Eli Nager  , So the credit goes to him .

 

ERPLN Example( code Snippet ):

 

function create.xml.header()

{

                string create.date(25),sdat(20)

                                sdat = dte$()

                create.date = "20" & sdat(5;2) & "-" & sdat(1;2) & "-" & sdat(3;2)& "T" & sdat(7;2)& ":" & sdat(9;2)& ":" & sdat(11;2) & "Z"

               

                ret = seq.puts("<?xml version=""1.0""?>", fp)

                ret = seq.puts("<?mso-application progid=""Excel.Sheet""?>", fp)

                ret = seq.puts("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""", fp)

                ret = seq.puts(" xmlns:o=""urn:schemas-microsoft-com:office:office""", fp)

                ret = seq.puts(" xmlns:x=""urn:schemas-microsoft-com:office:excel""", fp)

                ret = seq.puts(" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""", fp)

                ret = seq.puts(" xmlns:html=""http://www.w3.org/TR/REC-html40"">", fp)

                ret = seq.puts(" <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">", fp)

                ret = seq.puts("  <LastAuthor>" & strip$(logname$) & "</LastAuthor>", fp)

                ret = seq.puts("  <Created>" & create.date & "</Created>", fp)

                ret = seq.puts("  <LastSaved>" & create.date & "</LastSaved>", fp)

                ret = seq.puts(" </DocumentProperties>", fp)

                ret = seq.puts(" <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">", fp)

                ret = seq.puts("  <WindowHeight>12660</WindowHeight>", fp)

                ret = seq.puts("  <WindowWidth>19020</WindowWidth>", fp)

                ret = seq.puts("  <WindowTopX>120</WindowTopX>", fp)

                ret = seq.puts("  <WindowTopY>120</WindowTopY>", fp)

                ret = seq.puts("  <ProtectStructure>False</ProtectStructure>", fp)

                ret = seq.puts("  <ProtectWindows>False</ProtectWindows>", fp)

                ret = seq.puts(" </ExcelWorkbook>", fp)

                ret = seq.puts(" <Styles>", fp)

                ret = seq.puts("  <Style ss:ID=""Default"" ss:Name=""Normal"">", fp)

                ret = seq.puts("   <Alignment ss:Vertical=""Bottom""/>", fp)

                ret = seq.puts("   <Borders/>", fp)

                ret = seq.puts("   <Font/>", fp)

                ret = seq.puts("   <Interior/>", fp)

                ret = seq.puts("   <NumberFormat/>", fp)

                ret = seq.puts("   <Protection/>", fp)

                ret = seq.puts("  </Style>", fp)

                ret = seq.puts("  <Style ss:ID=""s21"">", fp)

                ret = seq.puts("   <Font x:Family=""Swiss"" ss:Bold=""1""/>", fp)

                ret = seq.puts("  </Style>", fp)

                ret = seq.puts("  <Style ss:ID=""s22"">", fp)

                ret = seq.puts("   <Font x:Family=""Swiss"" ss:Bold=""1""/>", fp)

                ret = seq.puts("  </Style>", fp)

                ret = seq.puts("  <Style ss:ID=""s23"">", fp)

                ret = seq.puts("   <Font x:Family=""Swiss"" ss:Bold=""0""/>", fp)

                ret = seq.puts("   <NumberFormat ss:Format=""Short Date""/>", fp)

                ret = seq.puts("  </Style>", fp)

                ret = seq.puts("  <Style ss:ID=""s24"">", fp)

                ret = seq.puts("   <Font x:Family=""Swiss"" ss:Bold=""0""/>", fp)

                ret = seq.puts("   <NumberFormat ss:Format=""Short Time""/>", fp)

                ret = seq.puts("  </Style>", fp)

                ret = seq.puts(" </Styles>", fp)

                ret = seq.puts(" <Worksheet ss:Name=""New Orders"">", fp)

                ret = seq.puts("  <Table>", fp)

                datum.style = " ss:StyleID=""s23"""

                time.style = " ss:StyleID=""s24"""

                bold.style = " ss:StyleID=""s22"""

 }

 

function create.xml.cells( domain tcmcs.str32 tmp.field,

                           long table.field )

{

                string domain_name(14),cell.style(24),data.type(20),dummy.s(1) , exp.str(30)

                long dummy.l,type, exp_id ,ret.val ,value_long

                tmp.field = trim$(tmp.field)

                value = ""

               

 

               

                if table.field = 0 then

                                 

                                data.type = "String"

                                cell.style = ""

                                value = tmp.field

                else

                                exp.str = "value:="&"str("&tmp.field&")"

                                ret = rdi.column(tmp.field, domain_name, dummy.l, dummy.l, dummy.l, type, dummy.l, dummy.s)

                                on case type

                                case DB.BYTE:   

                                                data.type = "Number"

                                                cell.style = ""

                                                break

                                case DB.DATE:

                                                data.type = "DateTime"

                                                cell.style = datum.style

                                                break

                                case DB.TIME:   |* UTC date

                                                data.type = "DateTime"

                                                cell.style = datum.style

                                                break

                                case DB.DOUBLE:

                                                data.type = "Number"

                                                cell.style = ""

                                                break

                                case DB.ENUM:

                                                data.type = "String"

                                                cell.style = ""

                                                break

                                case DB.FLOAT:

                                                data.type = "Number"

                                                cell.style = ""

                                                break

                                case DB.INTEGER:

                                                data.type = "Number"

                                                cell.style = ""

                                                break

                                case DB.LONG:

                                                data.type = "Number"

                                                cell.style = ""

                                                break

                                case DB.STRING:

                                                exp.str = "value:="&tmp.field

                                                data.type = "String"

                                                cell.style = ""

                                                break

                                case DB.MULTIBYTE:

                                                exp.str = "value:="&tmp.field

                                                data.type = "String"

                                                cell.style = ""

                                                break

                                default:

                                                data.type = "String"

                                                cell.style = ""

                                                exp.str = "value:="&tmp.field

                                endcase

                                exp_id = expr.compile(exp.str)

                                s.expr$(exp_id)

                                expr.free(exp_id)

                                value_long = lval(value)

                               

                                on case type

                                case DB.ENUM:

                                                enum.descr$(domain_name, ltoe(value_long))

                                                break

                                case DB.DATE:

                                                cell.style = datum.style

                                                value = sprintf$("%D(%04Y-%02m-%02d)", value_long)

                                                break

                                case DB.TIME:

                                               

value = sprintf$("%u(%04Y-%02m-%02d)T%U(%02h%x%02m%x%02s).000", value_long, value_long)

                                               

break

                                default:

                                                break

                                endcase

                               

                endif

                ret = seq.puts("    <Cell" & cell.style & "><Data ss:Type=" & """" & strip$(data.type) & """" & ">" & strip$(value) & "</Data></Cell>", fp)

}

 

function create.xml.footer()

{

                ret = seq.puts("  </Table>", fp)

                ret = seq.puts("  <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">", fp)

                ret = seq.puts("   <PageSetup>", fp)

                ret = seq.puts("    <Layout x:Orientation=""Landscape""/>", fp)

                ret = seq.puts("    <PageMargins x:Left=""0.15748031496062992"" x:Right=""0.11811023622047245"" />", fp)

                ret = seq.puts("   </PageSetup>", fp)

                ret = seq.puts("   <FitToPage/>", fp)

                ret = seq.puts("   <Print>", fp)

                ret = seq.puts("   <FitHeight>20</FitHeight>", fp)

                ret = seq.puts("    <ValidPrinterInfo/>", fp)

                ret = seq.puts("    <PaperSizeIndex>9</PaperSizeIndex>", fp)

                ret = seq.puts("   <Scale>74</Scale>", fp)

                ret = seq.puts("    <HorizontalResolution>600</HorizontalResolution>", fp)

                ret = seq.puts("    <VerticalResolution>600</VerticalResolution>", fp)

                ret = seq.puts("   </Print>", fp)

                ret = seq.puts("   <Selected/>", fp)

                ret = seq.puts("   <FreezePanes/>", fp)

                ret = seq.puts("   <FrozenNoSplit/>", fp)

                ret = seq.puts("   <SplitHorizontal>1</SplitHorizontal>", fp)

                ret = seq.puts("   <TopRowBottomPane>1</TopRowBottomPane>", fp)

                ret = seq.puts("   <ActivePane>2</ActivePane>", fp)

                ret = seq.puts("   <Panes>", fp)

                ret = seq.puts("    <Pane>", fp)

                ret = seq.puts("     <Number>3</Number>", fp)

                ret = seq.puts("     <ActiveRow>7</ActiveRow>", fp)

                ret = seq.puts("     <ActiveCol>5</ActiveCol>", fp)

                ret = seq.puts("    </Pane>", fp)

                ret = seq.puts("   </Panes>", fp)

                ret = seq.puts("   <ProtectObjects>False</ProtectObjects>", fp)

                ret = seq.puts("   <ProtectScenarios>False</ProtectScenarios>", fp)

                ret = seq.puts("  </WorksheetOptions>", fp)

                ret = seq.puts(" </Worksheet>", fp)

                ret = seq.puts("</Workbook>", fp)

                }

 

2
Average: 2 (1 vote)

by pillai.ganesh on September 16, 2014 - 7:07am
pillai.ganesh's picture
Thanks for the post Vamsi.

by Ajesh on August 29, 2016 - 11:38am
Ajesh's picture
so does this sequence of code directly writes the data into xlsx format?

by vamsi_gujjula on August 30, 2016 - 8:41pm
vamsi_gujjula's picture
its writes an XML file.

by BChary on August 31, 2016 - 9:23am
BChary's picture
After using session to generate xml, you can use command:
Code:
start.application.local("excel path\file.xml", false, exitcode)
to open xml as excel file.


All times are GMT +2. The time now is 17:19.


©2001-2018 - Baanboard.com - Baanforums.com