Formatting Spreadsheet e-mail attachments

Former Member — Wed, 21 Feb 2007 12:54:48 GMT

Morning,

I need to be able to format a spreadsheet that is included as an attachment on an e-mail. For example, how do you make some of the cells bold, determine the column width and determine the colour of the cell.

I am currently creating the attachment using function module SO_DOCUMENT_SEND_API1. I would prefer to use the business communication server classes instead.

Thanks and regards

John.

Re: Formatting Spreadsheet e-mail attachments

Former Member — Thu, 22 Feb 2007 15:28:56 GMT

John,

Probably you are using OLE to make your Excel(?) file.

An alternative approach is to use XML. The Microsoft website gives info on the tags needed. You can also create an Excel file, save it as XML, and inspect it in Notepad. A bonus of not using OLE is that you can run the ABAP in the background, since you are just creating a text attachment (XML). The user probably wouldn't know that it was not the usual type of .xls file.

With the XML approach I've been able to format individual cells, set column widths, and do a number of levels of subtotalling via Excel formulas.

Using styles for cell formats, you can have e.g. negative amounts in red.

I've also been able to give the Excel file a second sheet in which the report selection criteria are given. That is a nice touch.

Over all, the XML approach gives pleasing results.

Some snags with the XML approach:

It is a lot of work doing your first one.

It is a lot of work setting everything up for this so that most of it is easily re-usable. I've set up a number of custom tables for styles etc, and a function module for appending the XML code for one sheet to the XML code, and an INCLUDE for the top of the XML.

It is very hard to make your ABAP calculate the column widths needed. I make do with a rough calculation: width = 6 times max characters / cell.

For subtotalling, Excel does not give the normal left-hand frame (with its vertical lines, plusses & minusses) that can be used for collapsing/expanding the subtotalled data.

John

Re: Formatting Spreadsheet e-mail attachments

Former Member — Mon, 26 Feb 2007 13:53:12 GMT

John,

Thank you very much for these pointers. It is very much appreciated. My apologies for the slow response but I took a long weekend.

I am going to use the XML route. To start of with I created a very simple spreadsheet consisting of 4 cells.This is displayed below.

Heading1 Heading2

1 2

The cell headings had a different colour and cell B1 was bold. I saved the spreadsheet as XML and looked at the data in notepad. I include this below:-

<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>

<o:DocumentProperties>

<o:Author>John Raymond Cotton</o:Author>

<o:LastAuthor>John Raymond Cotton</o:LastAuthor>

<o:Created>2007-02-26T09:01:34Z</o:Created>

<o:LastSaved>2007-02-26T09:09:05Z</o:LastSaved>

<o:Company>Capita Insurance Services</o:Company>

<o:Version>9.6926</o:Version>

</o:DocumentProperties>

<o:OfficeDocumentSettings>

<o:DownloadComponents/>

<o:LocationOfComponents HRef="file:msowc.cab"/>

</o:OfficeDocumentSettings>

</xml><![endif]-->

<style>

<!--table

{mso-displayed-decimal-separator:"\.";

mso-displayed-thousand-separator:"\,";}

@page

{mso-height-source:auto;}

col

{mso-width-source:auto;}

{mso-data-placement:same-cell;}

.style0

{mso-number-format:General;

text-align:general;

vertical-align:bottom;

white-space:nowrap;

mso-rotate:0;

mso-background-source:auto;

mso-pattern:auto;

color:windowtext;

font-size:10.0pt;

font-weight:400;

font-style:normal;

text-decoration:none;

font-family:Arial;

mso-generic-font-family:auto;

mso-font-charset:0;

border:none;

mso-protection:locked visible;

mso-style-name:Normal;

mso-style-id:0;}

{mso-style-parent:style0;

padding-top:1px;

padding-right:1px;

padding-left:1px;

mso-ignore:padding;

color:windowtext;

font-size:10.0pt;

font-weight:400;

font-style:normal;

text-decoration:none;

font-family:Arial;

mso-generic-font-family:auto;

mso-font-charset:0;

mso-number-format:General;

text-align:general;

vertical-align:bottom;

border:none;

mso-background-source:auto;

mso-pattern:auto;

mso-protection:locked visible;

white-space:nowrap;

mso-rotate:0;}

.xl24

{mso-style-parent:style0;

background:#FFFF99;

mso-pattern:auto none;}

.xl25

{mso-style-parent:style0;

font-weight:700;

font-family:Arial, sans-serif;

mso-font-charset:0;}

-->

</style>

<!--[if gte mso 9]><xml>

<x:ExcelWorkbook>

<x:ExcelWorksheets>

<x:ExcelWorksheet>

<x:Name>Sheet1</x:Name>

<x:WorksheetOptions>

<x:Print>

<x:ValidPrinterInfo/>

<x:PaperSizeIndex>9</x:PaperSizeIndex>

<x:HorizontalResolution>600</x:HorizontalResolution>

<x:VerticalResolution>600</x:VerticalResolution>

</x:Print>

<x:Selected/>

<x:ProtectContents>False</x:ProtectContents>

<x:ProtectObjects>False</x:ProtectObjects>

<x:ProtectScenarios>False</x:ProtectScenarios>

</x:WorksheetOptions>

</x:ExcelWorksheet>

<x:ExcelWorksheet>

<x:Name>Sheet2</x:Name>

<x:WorksheetOptions>

<x:ProtectContents>False</x:ProtectContents>

<x:ProtectObjects>False</x:ProtectObjects>

<x:ProtectScenarios>False</x:ProtectScenarios>

</x:WorksheetOptions>

</x:ExcelWorksheet>

<x:ExcelWorksheet>

<x:Name>Sheet3</x:Name>

<x:WorksheetOptions>

<x:ProtectContents>False</x:ProtectContents>

<x:ProtectObjects>False</x:ProtectObjects>

<x:ProtectScenarios>False</x:ProtectScenarios>

</x:WorksheetOptions>

</x:ExcelWorksheet>

</x:ExcelWorksheets>

<x:WindowHeight>7680</x:WindowHeight>

<x:WindowWidth>14700</x:WindowWidth>

<x:WindowTopX>600</x:WindowTopX>

<x:WindowTopY>510</x:WindowTopY>

<x:ProtectStructure>False</x:ProtectStructure>

<x:ProtectWindows>False</x:ProtectWindows>

</x:ExcelWorkbook>

</xml><![endif]-->

</head>

<table x:str border=0 cellpadding=0 cellspacing=0 width=128 style='border-collapse:

collapse;table-layout:fixed;width:96pt'>

<td height=17 class=xl24 width=64 style='height:12.75pt;width:48pt'>Heading1</td>

<td class=xl24 width=64 style='width:48pt'>Heading2</td>

</tr>

</tr>

<![if supportMisalignedColumns]>

</tr>

<![endif]>

</table>

</body>

</html>

Unfortunately I cannot see where the data for the 4 cells and their respective formats are held. You mention that it is difficult to set your first spreadsheet up. Would be possible for you to provide me with code that will demonstrate how to set up a basic spreadsheet. I would be very interested in seeing how you formatted individual cells and set column widths.

I am sure you are very busy so any further assistance would be greatly appreciated.

Thanks and regards

John.

topic Re: Formatting Spreadsheet e-mail attachments in Application Development and Automation Discussions

Formatting Spreadsheet e-mail attachments

Re: Formatting Spreadsheet e-mail attachments

Re: Formatting Spreadsheet e-mail attachments