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
- 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 ;)
- 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
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)
}
