Wednesday, March 7, 2012

How to articles for Open XML

Pivot Table

Using ExtremeML to create a Pivot Table

Using PowerTools to create a PivotTable - Part 1

Using PowerTools to create a PivotTable - Part 2

Using PowerTools to create a PivotTable - Part 3

 

Auto-Filter

Use Open XML SDK to add Auto-Filter

NOTE: This does NOT work well in the Excel file you are using for a template. Use code instead.

 

Validating Open XML file

Using the Open XML SDK to validate the file format

 

Working with Dates in SpreadsheetML

Dates in SpreadsheetML

 

Freezing the top row

Discussion on options for freezing the top row

NOTE: You can do this in the Excel file you are using for a template

Getting Started with Open XML SDK 2.0

Required Libraries

Open XML SDK 2.0 – This is the core SDK you need to do development for the Open XML format. You don’t have to get the larger OpenXMLSDKTool.msi (This includes the Open XML SDK 2.0 Productivity Tool for Microsoft Office which I talk about in the tools section), but I do recommend it. As a minimum, you have to get the OpenXMLSDKv2.msi.

Optional Libraries

PowerTools for Open XML – This is an open source project that makes doing a lot of things easier because it has a bunch of methods that make use of the SDK, but shield you from much of the complexities. At the same time the library does put you in another world where it is difficult to get down to the SDK as needed.
ExtremeML – Another open source project that makes working with Open XML format easily and much more of what you would hope. Includes support for Pivot tables. Stresses using Excel as a template.
ClosedXML – It appears to be pretty popular for easily working with spreadsheets. I have used it and I really like it so far. Most of the code I have seen shows using it to create Excel worksheets from scratch, but you can use the overloaded constructor to pass the name of the Excel file and open it. After that everything is the same. It is super easy to use for basic editing and I have to hope it would be for more complex stuff also. I did notice that it doesn't appear to support Pivot tables yet. However, you can still use the template approach where you create the Excel file using MS Excel, add your pivot table, related table, etc and then just use ClosedXML to update the table of data. If you do this you probably want to have the pivot table update on opening of the workbook. I love the template approach in general.

I haven't used this one, but it looks very good.

I haven't used this one either, but it looks very good also.

Tools

Open XML Package Editor Power Tool for Visual Studio 2010 – This is a must have for looking at the files in Visual Studio. It understands the relationships between files/parts which makes navigation and verifying relationships easier. It also allows you to use to XML formatter in Visual Studio to reformat the files so you can easily read them (there are not End of Line characters otherwise and everything is a blob of text which is difficult to read)
Open XML SDK 2.0 Productivity Tool for Microsoft Office – This is another must have. It allows you to verify a file and gives you pretty good error messages compared to Excel. It also does much of the same things that Open XML Package Editor for Visual Studio 2010 does, but it takes like 300MB of RAM so it is kind of heavy to keep running. You can also compare two files and it will show you the differences. One of the coolest parts of this tool is that you have it reflect the C# code you need to generate a file or part of the file. This can be very useful. I has docs on the SDK which is handy as well.
7-zip – I like to use it to open the zip file and also edit the files directly if I am trying a quick change and want to see if it will work. I also find it useful to troubleshoot when a relationship or something like that is broken.

Documentation

Welcome to the Open XML SDK for Microsoft Office – a good place to start
Class Library Reference – great reference. This is also available in the Open XML SDK 2.0 Productivity Tool for Microsoft Office
OpenXMLDeveloper.org – There are so many articles here, and definitely check out the wiki on the bottom left of the home page. They have videos, forums, several blogs, training series etc. If you want to find a how to article, this is the place to check.

Free Training

OpenXMLDeveloper.org Workshops (FREE)

Writing large amounts of data from a database to Excel in a server environment

The Problem and Criteria for a viable solution

It seems to be common place these days that people want their data in Excel so they can play around with it. The problem is that using Excel automation/COM/VBScript, etc is not a thread-safe option and also requires the installation of Excel. On the desktop these are not real big issues since most users have Excel on their desktop. However, this is a huge issue when allowing users to download large (dynamically generated) Excel files from the web. You really should not be installing Excel on the server to do this.
There are lots of solutions out there that are easy to use and many of them are free. The problem is that they load all the data into memory BEFORE it is written out to an Excel file. This is fine for smaller exports, but when you get into say 50 columns and 500 rows of text this can quickly become very memory intensive. On a server where there are lots of users this can become a problem very quickly.
The only solution that I am aware of must not load all the data in memory all at once. Instead, it must load only small chunks such as a cell or row at a time. This way the RAM footprint will stay constant and quite small. This means that the amount of RAM your process takes is NOT dependent on the amount of data you are trying to write to the Excel file. This is great news!
So to summarize, we want a solution that meets the following criteria:
  • Memory footprint is not tied to the amount of data being written to Excel
  • Excel does not need to be installed
  • Accessible via .NET. and prefer Managed Code.
  • Thread-safe (since we are running in a server environment)
  • Easy to use
  • Supports dates, text, and number formats correctly.
  • Allows for most all kinds of things in Excel like Pivot tables, Auto-Filters, Freezing rows, etc.
  • Fast

The Solution Concept

The solution is based on the Open XML SDK 2.0. However, I am using a less used part of the Open XML SDK 2.0. In particular, I use the OpenXmlReader and OpenXmlWriter. These work differently from how people usually use the SDK. These objects work like the Java SAX parser for XML; meaning they step through the XML tags one at a time and it is our responsibility to do whatever we need to do. This is a bit more work, but not pad because most of the same objects you would normally use are still used and just passed to the OpenXmlWriter to serialize them as XML.
My solution uses a template approach such that you create your basic Excel document in Excel and then read it in and just modify the parts that we want to modify. This is nice in the sense that we don’t have to code everything. This can be a HUGE time saver. On the downside, this can be a bit more difficult when the sheet we are modifying is the one that we are writing this large amount of data to. The good news is that is is not that difficult once you understand the solution. The basic idea is that use the OpenXmlReader to read the contents of the original Sheet. We never write to this Sheet. We actually create a new Sheet that we then use the OpenXmlWriter to write the data to. So, as we read a tag from the OpenXmlReader we write, ignore if we like, or create something new into the new Sheet using the OpenXmlWriter. When we are done, we delete the original sheet, and give the new sheet the id of the one we deleted so it can take its place. The basics for this design I have to give credit to this blog. I did expand on it quite a bit for my final solution though.

Excel File Format Basics

The Excel File format is actually a zip file of several files. Each sheet is actually a xml file in the zip file. The styles (formatting basically) is in a separate xml file and is shared among the sheets. Each file is called a part. Each file must be saved after you make changes to it. These files have relationships that you must define between the files.

A blending of models

The first thing we will do in our code is copy the Excel file we are using as a template. Once we do that we can use the Open XML SDK 2.0 to make any changes we want to it. This is easily done in the common way that everyone edits Excel file using the Open XML SDK. There is very little overhead since the template file should be relatively small because it doesn’t have much data in it yet.
Once we have made some changes to the copy of the template we then save our changes. Saving is Critical because when we use the OpenXmlReader, it will read the content from DISK, not memory.

Keeping Consistency

One of the difficulties when working with a template based solution is that not all tags you expect to be in the file will be there because many tags are optional. This can be a pain when working with a parser that only gives you the current parser and you don’t know if a tag will exist later or not. This is especially true when you need to add a tag and it has to be before or after a particular tag(s). I found that the parsing code quickly got cluttered and felt hacked if I tried to handle these optional tags while parsing. I found the better solution use the part of the SDK that loads the entire sheet into memory to check if the tags are there and if not add them. That way they will be guaranteed to be there when I am parsing the tags with the OpenXmlReader. You don’t have to do this for all optional tags, just the ones that you care about. For example, PageSetup and PageMargins or TableParts and TablePart.

The Code

Download
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
 
using System.Data.SqlClient;
using System.Data;
using System.IO;
public class LargeExcelFile
{
 // we can reuse these object to reduce overhead
 Row r = new Row();
 Cell cell = new Cell();
 
 public LargeExcelFile()
 {
 
 }
 
 private WorksheetPart GetWorksheetFromSheetName(WorkbookPart workbookPart, string sheetName)
 {
  Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
  if (sheet == null) throw new Exception(string.Format("Could not find sheet with name {0}", sheetName));
  else return workbookPart.GetPartById(sheet.Id) as WorksheetPart;
 }
 
 
 public void Write(SqlDataReader dbReader, string sheetName, string excelTableName, List<string> columnHeaders, List<double?> columnWidths, List<uint?> columnHeaderStyleIndexes, List<uint?> columnStyleIndexes, string templateFilename, string outputFilename, bool overwriteContent, bool addAutoFilters)
 {
  // create directories up to the file we are going to write
  Directory.CreateDirectory(new FileInfo(outputFilename).Directory.FullName);
  File.Copy(templateFilename, outputFilename, true);
 
  int existingRows = 0;
  int numDataRows = 0;
  int numCols = 0;
  TableDefinitionPart tableDefPart = null;
 
  using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(outputFilename, true))
  {
    
   WorkbookPart workbookPart = myDoc.WorkbookPart;
    
   WorksheetPart worksheetPart = GetWorksheetFromSheetName(workbookPart, sheetName);
 
   #region let's do some things to the worksheet to make parsing easier and consistent.
 
   #region make sure we have a cols section
   var colsSection = worksheetPart.RootElement.Descendants<Columns>().FirstOrDefault();
   if (colsSection == null)
   {
    var sheetData = worksheetPart.RootElement.Descendants<SheetData>().FirstOrDefault();
    colsSection = worksheetPart.RootElement.InsertBefore<Columns>(new Columns(), sheetData);
   }
 
   // we don't really care about the col tags in the cols section since we will add our own anyway... just remove them.
   colsSection.RemoveAllChildren<Column>();
   #endregion
 
   #region make sure we have a TableParts section
 
   var tableParts = worksheetPart.RootElement.Descendants<TableParts>().FirstOrDefault();
   if (tableParts == null)
   {
    var sheetData = worksheetPart.RootElement.Descendants<SheetData>().FirstOrDefault();
    tableParts = worksheetPart.RootElement.InsertAfter<TableParts>(new TableParts(), sheetData);
   }
 
   // we don't really care about the col tags in the cols section since we will add our own anyway... just remove them.
   tableParts.RemoveAllChildren<TablePart>();
 
   #endregion
 
   // be sure to save before we create the OpenXmlReader(), otherwise it won't see the changes
   worksheetPart.Worksheet.Save();    #endregion
 
   string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);
 
   WorksheetPart replacementPart = workbookPart.AddNewPart<WorksheetPart>();
   string replacementPartId = workbookPart.GetIdOfPart(replacementPart);
 
   // creates actual table part (file) (i.e. xl/tables/table1.xml)
   tableDefPart = replacementPart.AddNewPart<TableDefinitionPart>();
   string newTablePartID = replacementPart.GetIdOfPart(tableDefPart);
 
   OpenXmlReader excelReader = OpenXmlReader.Create(worksheetPart);
   OpenXmlWriter excelWriter = OpenXmlWriter.Create(replacementPart);
 
 
   cell.CellValue = new CellValue();
 
   bool isReadingSheetDataInOverwriteMode = false;
   while (excelReader.Read())
   {
    // if we get to the end of the SheetData (end tag) then let's append our new data before we write the end tag
    if (excelReader.ElementType == typeof(SheetData) && (overwriteContent || excelReader.IsEndElement))
    {
      
     // if we are supposed to overwrite the content on the data sheet
     // then ignore any data that is there already on the specified sheet
     // and instead create a new start sheet data tag.
     if (overwriteContent)
     {
      isReadingSheetDataInOverwriteMode = excelReader.IsStartElement;
 
      if (excelReader.IsEndElement)
      {
       // do nothing and then exit to top of while loop so we can process the next tag
       continue;
      }
      else // start element
      {
       excelWriter.WriteStartElement(new SheetData());
      }
     }
 
     // add start row tag to SheetData for header row
     excelWriter.WriteStartElement(r);
 
     // read row 1 from the database to get the column headers
     for (int colIdx = 0; colIdx < columnHeaders.Count(); colIdx++)
     {
      WriteColumnHeader(excelWriter, columnHeaders[colIdx], columnHeaderStyleIndexes[colIdx]);
     }
 
     // add end row tag to SheetData
     excelWriter.WriteEndElement();
 
     //excelWriter.WriteElement(new AutoFilter() { Reference = "A1:AA1" });
 
     DateTime start = DateTime.Now;
 
     // loop through our data and create corresponding rows in SheetData
     while (dbReader.Read())
     {
      // add start row tag to SheetData for the row of data we just read from the database
      excelWriter.WriteStartElement(r);
 
      // get the number of columns if we haven't done so already.
      if (numCols == 0)
      {
       numCols = dbReader.FieldCount;
      }
 
      // loop through the columns and write a cell for each one
      for (int colIdx = 0; colIdx < dbReader.FieldCount; colIdx++)
      {
       WriteCell(excelWriter, dbReader[colIdx], columnStyleIndexes[colIdx]);
      }
 
      // add end row tag to SheetData
      excelWriter.WriteEndElement();
 
      // update the number of rows
      numDataRows++;
     }
 
     // write end SheetData tag
     excelWriter.WriteEndElement();
 
     DateTime end = DateTime.Now;
 
     Console.WriteLine("File Writing Duration: " + end.Subtract(start).TotalSeconds.ToString());
 
    }     else if (excelReader.ElementType == typeof(Selection))
    {
     // only write the entire tag once when we see the start element.
     if (excelReader.IsStartElement)
     {
      // we want to change the select to be the top left cell.  // This is not a required step, but does prevent the user from seeing a selection
      // that just happen to be the last thing that the person that put together the Template had selected.
      // Assuming the sheet we are writing is the one that is the active sheet when the user opens the file in Excel
      excelWriter.WriteElement(new Selection() { ActiveCell = "A1"});
     }
    }
    else if (excelReader.ElementType == typeof(TablePart) || excelReader.ElementType == typeof(TableParts))
    {
     // do nothing for the TablePart or TableParts tags since we don't have it after we delete the sheet
     // if they existed on the original sheet, and we are adding the one we need explicitly
 
     if (excelReader.IsStartElement)
     {
      // write the TableParts (notice the 's') start tag
      excelWriter.WriteStartElement(excelReader);
 
      // we want to reference the new one we created not the one that will be deleted when the original sheet is deleted later
      excelWriter.WriteElement(new TablePart() { Id = newTablePartID });
     }
     else // end TableParts tag
     {
      // write the TableParts (notice the 's') end tag
      excelWriter.WriteEndElement();
     }
    }
    else if (excelReader.ElementType == typeof(PageSetup))
    {
     // do nothing for the PageSetup tag since we don't have it after we delete the sheet
    }
    else if (excelReader.ElementType == typeof(PageMargins))
    {
     // do nothing for the PageMargins tag since we don't have a PageSetup tag
    }
    else if (excelReader.ElementType == typeof(Columns))
    {     // we want to add our own colummns to the columns section.
     // only write the entire tag once when we see the start element.
     if (excelReader.IsStartElement)
     {
      excelWriter.WriteStartElement(excelReader);
 
      // write a column tag, one for each column we are showing
      for (int i = 0; i < columnWidths.Count; i++)
      {
       UInt32Value colNum = (UInt32)i + 1;
 
       DoubleValue colWidth;
 
       // if there is a column width specified then use it
       if (columnWidths[i].HasValue)
       {
        colWidth = new DoubleValue(columnWidths[i].Value);
         
       }
       else // no column width specified so use a default.
       {
        // NOTE: 8.43 is the default that Excel uses, but we can make it whatever we like as the default
        colWidth = new DoubleValue(8.43D);
       }
 
       excelWriter.WriteElement(new Column() { Min = colNum, Max = colNum, CustomWidth = true, Width = colWidth });   }
       
     }
     else // end Cols tags, so just write it
     {
      excelWriter.WriteEndElement();
     }
    }
    else
    {
     // if overwritingContent or reading  if (!isReadingSheetDataInOverwriteMode)
     {
      if (excelReader.IsStartElement)
      {
       excelWriter.WriteStartElement(excelReader);
      }
      else if (excelReader.IsEndElement)
      {
       excelWriter.WriteEndElement();
 
       if (excelReader.ElementType == typeof(Row))
       {
        existingRows++;
       }
      }
 
      // don't forget to copy the content between tags that is just text (i.e. not a tag). This is the case for leaf nodes.
      string text = excelReader.GetText();
      if (!string.IsNullOrEmpty(text))
      {
       excelWriter.WriteString(text);
      }
     }
    }
   }
   excelReader.Close();
   excelWriter.Close();
 
   Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)).First();
   sheet.Id.Value = replacementPartId;
   workbookPart.DeletePart(worksheetPart);
 
 
   // create a Table in Excel so that we can reference in a Pivot table if we want to.
   // it is also needed to do auto filtering.
   // Also useful for calculations, etc
 
   int numRows = numDataRows + 1;
 
   // we should start at row 1 if overwriting content,  // but it if we are not overwriting content then  // the start range is really the row after the rows that are already there.
   // NOTE: This assumes that  the rows that are alredy there are NOT part of the data we want in the Defined Range
   int startRow = 1;
   if (!overwriteContent)
   {
    startRow = existingRows + 1;
   }
 
   int startColumn = 1;
   string range = String.Format("{0}{1}:{2}{3}", GetColumnId(startColumn), startRow, GetColumnId(numCols), numRows);
   PopulateTableDefinitionPart(tableDefPart, excelTableName, range, columnHeaders, addAutoFilters);
    
 
  }
 }     // Translates the column number to the column reference string (e.g. 1 -> A, 2-> B)     // Copied from OpenXmlPowerTools.WorksheetAccessor.cs     

private static string GetColumnId(int columnNumber)     
{         
    string result = "";         
    do         
    {
             result = ((char)((columnNumber - 1) % 26 + (int)'A')).ToString() + result;
             columnNumber = (columnNumber - 1) / 26;         
    } while (columnNumber != 0);         
    return result;     
}
 
 
 // since we are reusing the cell object, we need to clear in in between uses.
 private void ResetCell()
 {
  cell.StyleIndex = null;
 }
 
 /// <summary>
 /// Fills in the content in the actual table part (i.e. xl/tables/table1.xml)
 /// NOTE: The columns added here MUST be in the Excel sheet in the cells that are referenced.
 /// </summary>
 /// <param name="part">A TableDefinitionPart that has been created, but still needs the Table added to it.</param>
 /// <param name="excelTableName">The name of the table. This is used in Excel and shown as the table name. (i.e. Table1)</param>
 /// <param name="reference">An Excel range like A1:J4027</param>
 /// <param name="columnNames">A list of column headers in row one of the rows indicated in the reference parameter.</param>
 /// <param name="addAutoFilters">true to add auto filters to columns, else false to leave alone.</param>
 private void PopulateTableDefinitionPart(TableDefinitionPart part, string excelTableName, string reference, List<string> columnNames, bool addAutoFilters)
 {
  Table table1 = new Table() { Id = (UInt32Value)1U, Name = excelTableName, DisplayName = excelTableName, Reference = reference, TotalsRowShown = false };
 
  AutoFilter autoFilter1 = null;
  if (addAutoFilters)
  {
   autoFilter1 = new AutoFilter() { Reference = reference };
  }
 
  TableColumns tableColumns = new TableColumns() { Count = (UInt32Value)(UInt32)columnNames.Count };
  for (int i = 0; i < columnNames.Count; i++)
  {
   string colName = columnNames[i];
   tableColumns.Append(new TableColumn() { Id = (UInt32Value)(UInt32)i+1, Name = colName });
  }
 
  TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { Name = "TableStyleMedium9", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
 
  if (addAutoFilters)
  {
   table1.Append(autoFilter1);
  }
 
  table1.Append(tableColumns);
  table1.Append(tableStyleInfo1);
 
  part.Table = table1;
 }
 
 
 protected void WriteColumnHeader(OpenXmlWriter excelWriter, string value, uint? styleIndex)
 {
  ResetCell();
 
  //// write a string
  cell.DataType = CellValues.String;
  cell.CellValue.Text = value;
 
  if (styleIndex.HasValue)
  {
   cell.StyleIndex = styleIndex.Value;
  }
 
 
  excelWriter.WriteElement(cell);
 }
 
 
 // ******* Understanding the StyleIndex ********
 // this is the order that it appears in the style file under CellStyleFormats (<cellXfs ...>.  // For instance if there is a style custom style defined as yyyy-mm you will find this in the <numFmts...> tag
 // Then search the file for 164. You will see that there is a reference to it in the cellXfs tag's children  // as numFmtId="164". Now that we have found the CellStyleFormat that uses our number format we just have to  // look at it zero based position in the CellSytleFormats (<cellXfs....> tag). In the case below it is  // at index 1 (remember start counting at zero like arrays in C#). In this example we would set cell.StyleIndex = 1
 // if we wanted it to be formatted as yyyy-mm.
 //...
 //<numFmts count="1">
 //    <numFmt numFmtId="164" formatCode="yyyy\-mm"/>
 //</numFmts>
 //...
 //<cellXfs count="3">
 //    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
 //    <xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
 //    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
 //</cellXfs>
 protected void WriteCell(OpenXmlWriter excelWriter, object val, uint? styleIndex)
 {
   
  Type valType = val.GetType();
  if (valType == typeof(string))
  {
   WriteTextCell(excelWriter, val, styleIndex);
  }
  else if (valType == typeof(DateTime))
  {
   WriteDateTimeCell(excelWriter, val, styleIndex);
  }
  else if (valType == typeof(Int16) || valType == typeof(Int32) || valType == typeof(Int64))
  {
   WriteIntegerCell(excelWriter, val, styleIndex);
 
  }
  else if (valType == typeof(float) || valType == typeof(double) || valType == typeof(decimal))
  {
   WriteDecimalCell(excelWriter, val, styleIndex);
  }
  else
  {
   WriteGeneralCell(excelWriter, val, styleIndex);
 
  }
 
 
 }
 
 
 protected void WriteDateTimeCell(OpenXmlWriter excelWriter, object value, uint? styleIndex)
 {
  ResetCell();
 
  // write a date
  cell.DataType = CellValues.Date;
  cell.CellValue.Text = DateTime.Now.ToOADate().ToString();
   
  if (styleIndex.HasValue)
  {
   cell.StyleIndex = styleIndex.Value;
  }
   
  excelWriter.WriteElement(cell);
 }
 
 protected void WriteGeneralCell(OpenXmlWriter excelWriter, object value, uint? styleIndex)
 {
  ResetCell();
 
  // write a string
  cell.DataType = CellValues.String;
  cell.CellValue.Text = Convert.ToString(value);
 
  if (styleIndex.HasValue)
  {
   cell.StyleIndex = styleIndex.Value;
  }
 
  excelWriter.WriteElement(cell);
 }
 
 protected void WriteTextCell(OpenXmlWriter excelWriter, object value, uint? styleIndex)
 {
  ResetCell();
 
  //// write a string
  cell.DataType = CellValues.String;
  cell.CellValue.Text = Convert.ToString(value);
   
  if (styleIndex.HasValue)
  {
   cell.StyleIndex = styleIndex.Value;
  }
 
  excelWriter.WriteElement(cell);
 }
 
 protected void WriteIntegerCell(OpenXmlWriter excelWriter, object value, uint? styleIndex)
 {
  ResetCell();
 
  //write a decimal
  cell.DataType = CellValues.Number;
  cell.CellValue.Text = Convert.ToString(value);     if (styleIndex.HasValue)
  {
   cell.StyleIndex = styleIndex.Value;
  }
 
  excelWriter.WriteElement(cell);
 }
 
 protected void WriteDecimalCell(OpenXmlWriter excelWriter, object value, uint? styleIndex)
 {
  ResetCell();  //write a decimal
  cell.DataType = CellValues.Number;
  cell.CellValue.Text = Convert.ToString(value);
   
  if (styleIndex.HasValue)
  {
   cell.StyleIndex = styleIndex.Value;
  }
 
  excelWriter.WriteElement(cell);
 }
 
 
 
}

Thursday, March 1, 2012

Division in SQL Server gives me an integer not a decimal

I was surprised to learn that SQL Server thinks that 3/2 is 1 instead of 1.5.

Try it for yourself: select 3/2

Result: 1

 

However, I also learned that it thinks 3.0 / 2 and 3/2.0 is 1.5 as we would hope.

Try it for yourself: select 3.0/2

Result: 1.5

 

Try it for yourself: select 3/2.0

Result: 1.5

 

It doesn’t help to cast the result to a float either. I assume this is because the division is done first and changed to an integer and then we are asking that integer to change to a float. In this case, the decimals are gone and cannot be brought back. The same goes for trying convert instead of cast because they are basically the same in this case.

Try it for yourself: select cast(3/2 as float)

Result: 1

 

Conclusion

What this tells me is that if we have integer / integer we will get an integer, not an float, decimal, etc. This also tells me that if one of the numbers in the division is a float, decimal, etc, then the result will be of that type.

Try it for yourself: select cast(3 as float)/2

Result: 1.5

 

Try it for yourself: select 3/cast(2  as float)

Result: 1.5

 

Try it for yourself: select cast(3 as float)/cast(2 as float)

Result: 1.5

 

NOTE: These examples use hard coded numbers, but the same results apply if you had columns in their places.