André Krijnen

Tag: excel

Generating Excel (XmlSpreadSheet) in C# Part 2.

by on Nov.11, 2007, under Programming

In part 1 we started to explain a couple of things in our Excel xml spreadsheet. So let’s start how to explain it in code.

First of all we need a StringBuilder. With a StringBuilder we can append all the data we want. Let’s build a couple if simple functions. Everything is written in C#, because it’s a common written language in .NET. So if you would like to write in VB.NET you have to convert it to VB.Net.

First we start with the header of our Xml Spreadsheet:

  1. // We need a newline after each rule we’ve made. So this will make it in our StringBuilder.
  2. private string _Newline = “\n“;
  3.  
  4. // Returns the _NewLine
  5. protected string NewLine
  6. {
  7.        get
  8.        {
  9.              return _NewLine;
  10.        }
  11. }
  12. protected string StartHeader
  13. {
  14.        get
  15.        {
  16.                 StringBuilder sb = new StringBuilder();
  17.  
  18.                 sb.Append(“< ?xml version=\”1.0\”?>” + NewLine);
  19.                 sb.Append(“< ?mso-application progid=\”Excel.Sheet\”?>” + NewLine);
  20.                 sb.Append(“<workbook xmlns=\”urn:schemas-microsoft-com:office:spreadsheet\”“ + NewLine);
  21.                 sb.Append(“xmlns:o=\”urn:schemas-microsoft-com:office:office\”“ + NewLine);
  22.                 sb.Append(“xmlns:x=\”urn:schemas-microsoft-com:office:excel\”“ + NewLine);
  23.                 sb.Append(“xmlns:ss=\”urn:schemas-microsoft-com:office:spreadsheet\”“ + NewLine);
  24.                 sb.Append(“xmlns:html=\”http://www.w3.org/TR/REC-html40\”>” + NewLine);
  25.  
  26.                 return sb.ToString();
  27.       }
  28. }
  29.  
  30. </workbook>

So we have written our first part of our spreadsheet header. Let’s see what we’ve got now. Ah yes. Now our DocumentProperties we can write in our stringbuilder.

  1. protected string DocumentProperties
  2.         {
  3.             get
  4.             {
  5.                 StringBuilder sb = new StringBuilder();
  6.  
  7.                 sb.Append(“<documentproperties xmlns=\”urn:schemas-microsoft-com:office:office\”>” + NewLine);
  8.                 sb.Append(“<author>Author</author>” + NewLine);
  9.                 sb.Append(“<lastauthor>LastAuthor</lastauthor>” + NewLine);
  10.                 sb.Append(“<created>” + DateTime.Now + “</created>” + NewLine);
  11.                 sb.Append(“<version>12.00</version>” + NewLine);
  12.                 sb.Append(“</documentproperties>” + NewLine);
  13.  
  14.                 return sb.ToString();
  15.             }
  16.         }

So we have written now our DocumentProperties. Now we can write our ExcelWorkbook properties. There are a couple of things important. Don’t forget to put ProtectStructure and ProtectWindows in your ExcelWorkbook properties. These are important things we need to use.

  1.  protected string ExcelWorkbook
  2.         {
  3.             get
  4.             {
  5.                 StringBuilder sb = new StringBuilder();
  6.  
  7.                 sb.Append(“<excelworkbook xmlns=\”urn:schemas-microsoft-com:office:excel\”>” + NewLine);
  8.                 sb.Append(“<windowheight>8130</windowheight>” + NewLine);
  9.                 sb.Append(“<windowwidth>18015</windowwidth>” + NewLine);
  10.                 sb.Append(“<windowtopx>360</windowtopx>” + NewLine);
  11.                 sb.Append(“<windowtopy>75</windowtopy>” + NewLine);
  12.                 sb.Append(“<calculation>ManualCalculation</calculation>” + NewLine);
  13.                 sb.Append(“<donotcalculatebeforesave />” + NewLine);
  14.                 sb.Append(“<protectstructure>False</protectstructure>” + NewLine);
  15.                 sb.Append(“<protectwindows>False</protectwindows>” + NewLine);
  16.                 sb.Append(“</excelworkbook>” + NewLine);
  17.  
  18.                 return sb.ToString();
  19.             }
  20.         }

We have done our first part of our document. Now we can see allready that we have done alot of work to write our header for our document. Let’s start with our Styles. You can add styles in a couple of manners to your document. You can write an external document that we can add to our project. Or you can add this by code. Well like you write a stylesheet for your webpage, I wrote a styles.config to add to our project.

You can add this in by the following code part.

  1.  
  2. private string Styles(string filename)
  3.   {
  4.    System.IO.StreamReader sr;
  5.  
  6.    string sLine;
  7.    string sText = string.Empty;
  8.  
  9.    sr = System.IO.File.OpenText(filename);
  10.    sLine = sr.ReadLine();
  11.  
  12.    sText = sLine;
  13.  
  14.    while(sLine != null)
  15.    {
  16.     sLine = sr.ReadLine();
  17.     sText += sLine + “\n“;
  18.  
  19.    }
  20.  
  21.    sr.Close();
  22.  
  23.    return sText;
  24.   }

This was the part for reading our styles.config into a StreamReader and return it as string. Well we have done the most of things now. Let’s start with the main part. The main part is making the ExcelWorksheet and the table with data. In Html you can write a table like this:

  1. <table>
  2. <tr><td></td></tr><tr>
  3. </tr><tr><td></td></tr><tr>
  4. </tr></table>

Well the xml spreadsheet is almost the same. Let’s see how to make a simple template of it.

  1. <table>
  2. <row>
  3. <cell><data></data></cell>
  4. <cell><data></data></cell>
  5. </row>
  6. </table>

There are not many differences between html and xml spreadsheet. But it’s important to know that you don’t forget any closing tags. In particular big documents it’s a problem to find the problem. Well let’s start with some simple data to put it in our document.

  1.  protected string _data
  2.         {
  3.             get
  4.             {
  5.                 StringBuilder sb = new StringBuilder();
  6.  
  7.                 sb.Append(“<worksheet ss:Name=\”Sheet1\”>”);
  8.                 sb.Append(“<table ss:ExpandedColumnCount=\”12\” ss:ExpandedRowCount=\”1\” x:FullColumns=\”1\” x:FullRows=\”1\” ss:DefaultRowHeight=\”15\”>”);
  9.                 sb.Append(“<column ss:Index=\”2\” ss:StyleID=\”s62\” ss:AutoFitWidth=\”\”/>”);
  10.                 sb.Append(“<column ss:StyleID=\”s63\” ss:Width=\”53.25\”/>”);
  11.                 sb.Append(“<column ss:StyleID=\”s65\” ss:AutoFitWidth=\”\”/>”);
  12.                 sb.Append(“<column ss:StyleID=\”s66\” ss:AutoFitWidth=\”\” ss:Width=\”67.5\”/>”);
  13.                 sb.Append(“<column ss:StyleID=\”s67\” ss:Width=\”54.75\”/>”);
  14.                 sb.Append(“<column ss:StyleID=\”s68\” ss:AutoFitWidth=\”\”/>”);
  15.                 sb.Append(“<column ss:StyleID=\”s69\” ss:AutoFitWidth=\”\”/>”);
  16.                 sb.Append(“<column ss:StyleID=\”s70\” ss:AutoFitWidth=\”\”/>”);
  17.                 sb.Append(“<column ss:StyleID=\”s71\” ss:AutoFitWidth=\”\”/>”);
  18.                 sb.Append(“<column ss:StyleID=\”s72\” ss:AutoFitWidth=\”\”/>”);
  19.                 sb.Append(“<column ss:StyleID=\”s73\” ss:AutoFitWidth=\”\”/>”);
  20.                 sb.Append(“<row>”);
  21.                 sb.Append(“<cell><data ss:Type=\”String\”>General</data></cell>”);
  22.                 sb.Append(“<cell><data ss:Type=\”Number\”>0</data></cell>”);
  23.                 sb.Append(“<cell><data ss:Type=\”Number\”>12345</data></cell>”);
  24.                 sb.Append(“<cell><data ss:Type=\”String\”>€</data></cell>”);
  25.                 sb.Append(“<cell><data ss:Type=\”DateTime\”>2007-03-14T00:00:00.000</data></cell>”);
  26.                 sb.Append(“<cell><data ss:Type=\”DateTime\”>1899-12-31T13:30:00.000</data></cell>”);
  27.                 sb.Append(“<cell><data ss:Type=\”Number\”>1</data></cell>”);
  28.                 sb.Append(“<cell><data ss:Type=\”Number\”>0.33333333333333331</data></cell>”);
  29.                 sb.Append(“<cell><data ss:Type=\”Number\”>0.33333333333333331</data></cell>”);
  30.                 sb.Append(“<cell><data ss:Type=\”String\”>text</data></cell>”);
  31.                 sb.Append(“<cell><data ss:Type=\”String\”>Breda</data></cell>”);
  32.                 sb.Append(“<cell><data ss:Type=\”Number\”>12345</data></cell>”);
  33.                 sb.Append(“</row>”);
  34.                 sb.Append(“</table>”);
  35.  
  36.                 return sb.ToString();
  37.             }
  38.         }
  39. </worksheet>

On part 3 we’re going further. Cause the fact is that WordPress doesn’t allow me to write alot of text in one article…

1 Comment :, , , , , more...

Generating Excel (XmlSpreadSheet) in C# Part 1.

by on Nov.09, 2007, under Programming

If you want to export data with VB.NET of C# you can do it with the Office API’s delivered with your Office API’s, but I wasn’t to happy with the Excel Object. After doing some research on the internet I discovered alot of ways to write your own manner to Generate an Excel document with the XmlSpreadsheet. Well after doing some tryouts with Excel I discovered that Excel has alot of options how to save your data.

  • Excel Workbook (*.xlsx) Office 2007 Format
  • Excel Macro-Enabled Workbook (*.xlsm) Office 2007 Format
  • Excel Binary Workbook (*.xlsb) Office 2007 Format
  • Excel 97-2003 Workbook (*.xls)
  • XML Data (*.xml)
  • Single File Web Page (*.mht; *.mhtml)
  • Web Page (*.htm; *.html)
  • Excel Template (*.xltx) Office 2007 Format
  • Excel Macro-Enabled Template (*.xltm) Office 2007 Format
  • Excel 97-2003 Template (*.xlt)
  • Text (Tab Delimited) (*.txt)
  • Unicode Text (*.txt)
  • XML Spreadsheet (*.xml)
  • Microsoft Excel 5.0/95 Workbook (*.xls)
  • CSV (Comma Delimited) (*.csv)
  • Formatted Text (Space Delimited) (*.prn)
  • Text (Macintosh) (*.txt)
  • Text (MS-DOS) (*.txt)
  • CSV (Macintosh) (*.csv)
  • CSV (MS-DOS) (*.csv)
  • DIF (Data Interchange Format) (*.dif)
  • SYLK (Symbolic Link) (*.slk)
  • Excel Add-In (*.xlam) Office 2007 Format
  • Excel 97-2003 Add-In (*.xla)

You’ll see that the XML Spreadsheet normally would be saved in the Extension of *.xml, however you can save this also as *.xls. Why is that? You would ask? Well Excel has alot of manners how to open or save data. If you would have a CSV file delimited in Comma’s or Tab delimited files you can also open this with Excel. And you can see that the data is perfectly rendered in the Spreadsheet.

Let’s see where a XML SpreadSheet starts with:

  1. < ?xml version=“1.0”?>
  2. < ?mso-application progid=“Excel.Sheet”?>
  3. <workbook xmlns=“urn:schemas-microsoft-com:office:spreadsheet”
  4. xmlns:o=“urn:schemas-microsoft-com:office:office”
  5. xmlns:x=“urn:schemas-microsoft-com:office:excel”
  6. xmlns:ss=“urn:schemas-microsoft-com:office:spreadsheet”
  7. xmlns:html=“http://www.w3.org/TR/REC-html40”>
  8. <documentproperties xmlns=“urn:schemas-microsoft-com:office:office”>
  9. <author>Author</author>
  10. <lastauthor>LastAuthor</lastauthor>
  11. <created>11-09-2007</created>
  12. <version>12.00</version>
  13. </documentproperties>
  14. <excelworkbook xmlns=“urn:schemas-microsoft-com:office:excel”>
  15. <protectstructure>False</protectstructure>
  16. <protectwindows>False</protectwindows>
  17. </excelworkbook>
  18. </workbook>

After this section you will include you styles. When writing your styles you can build a Excel Workbook manually and make some different styles init. After that you can save this Workbook to a XML Spreadsheet. When you finally saved a Workbook it will generate for example the following styles:

  1. <styles>
  2. <style ss:ID=“Default” ss:Name=“Normal”>
  3. <alignment ss:Vertical=“Bottom”/>
  4. <borders />
  5. <font ss:FontName=“Calibri” x:Family=“Swiss” ss:Size=“11” ss:Color=“#000000”/>
  6. <interior />
  7. <numberformat />
  8. <protection />
  9. </style>
  10. <style ss:ID=“s62”>
  11. <numberformat ss:Format=“Fixed”/>
  12. </style>
  13. <style ss:ID=“s63”>
  14. <numberformat ss:Format=“&quot;€&quot;#,##0.00”/>
  15. </style>
  16. <style ss:ID=“s65”>
  17. <numberformat ss:Format=“0%”/>
  18. </style>
  19. <style ss:ID=“s66”>
  20. <numberformat ss:Format=“Short Date”/>
  21. </style>
  22. <style ss:ID=“s67”>
  23. <numberformat ss:Format=“[$-F400]h:mm:ss\ AM/PM”/>
  24. </style>
  25. <style ss:ID=“s68”>
  26. <numberformat ss:Format=“Percent”/>
  27. </style>
  28. <style ss:ID=“s69”>
  29. <numberformat ss:Format=“#\ ?/?”/>
  30. </style>
  31. <style ss:ID=“s70”>
  32. <numberformat ss:Format=“Scientific”/>
  33. </style>
  34. <style ss:ID=“s71”>
  35. <numberformat ss:Format=“@”/>
  36. </style>
  37. <style ss:ID=“s72”>
  38. <numberformat ss:Format=“00.00.00.000”/>
  39. </style>
  40. <style ss:ID=“s73”>
  41. <numberformat ss:Format=“Standard”/>
  42. </style>
  43. </styles>

What you can do is giving alot of extra options to your excel sheet. Every above style has a particular function. You can specify a column and give it a specific style. So that the column will be formatted by for example Scientific functions. After the styles there will be the data for your spreadsheet.

First we start with a new Worksheet. Every worksheet has a name . Let’s start with that:

  1. <worksheet ss:Name=“ExampleSheet”>
  3. </worksheet>

When we have done that we can add our table with data in our Worksheet. It will look like this.

  1. <worksheet ss:Name=“ExampleSheet”>
  2. <table ss:ExpandedColumnCount=“12” ss:ExpandedRowCount=“1” x:FullColumns=“1”
  3. x:FullRows=“1” ss:DefaultRowHeight=“15”>
  4. <column ss:Index=“2” ss:StyleID=“s62” ss:AutoFitWidth=“0”/>
  5. <column ss:StyleID=“s63” ss:Width=“53.25”/>
  6. <column ss:StyleID=“s65” ss:AutoFitWidth=“0”/>
  7. <column ss:StyleID=“s66” ss:AutoFitWidth=“0” ss:Width=“67.5”/>
  8. <column ss:StyleID=“s67” ss:Width=“54.75”/>
  9. <column ss:StyleID=“s68” ss:AutoFitWidth=“0”/>
  10. <column ss:StyleID=“s69” ss:AutoFitWidth=“0”/>
  11. <column ss:StyleID=“s70” ss:AutoFitWidth=“0”/>
  12. <column ss:StyleID=“s71” ss:AutoFitWidth=“0”/>
  13. <column ss:StyleID=“s72” ss:AutoFitWidth=“0”/>
  14. <column ss:StyleID=“s73” ss:AutoFitWidth=“0”/>
  15. <row>
  16. <cell><data ss:Type=“String”>General</data></cell>
  17. <cell><data ss:Type=“Number”></data></cell>
  18. <cell><data ss:Type=“Number”>12345</data></cell>
  19. <cell><data ss:Type=“String”>€</data></cell>
  20. <cell><data ss:Type=“DateTime”>2007-03-14T00:00:00.000</data></cell>
  21. <cell><data ss:Type=“DateTime”>1899-12-31T13:30:00.000</data></cell>
  22. <cell><data ss:Type=“Number”>1</data></cell>
  23. <cell><data ss:Type=“Number”>0.33333333333333331</data></cell>
  24. <cell><data ss:Type=“Number”>0.33333333333333331</data></cell>
  25. <cell><data ss:Type=“String”>text</data></cell>
  26. <cell><data ss:Type=“String”>Breda</data></cell>
  27. <cell><data ss:Type=“Number”>12345</data></cell>
  28. </row>
  29. </table>
  30. </worksheet>
Leave a Comment :, , , , , , , , more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Blogroll

A few highly recommended websites...

Archives

All entries, chronologically...