Wednesday, March 7, 2012

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

25 comments:

Anonymous said...

THis looks great! I am going to try this. But I am sure I'll get stuck at some point. Are you actively updating this blog?

Brent V said...

I am always adding new content to my blog. This particular page, I wrote fairly recently.

Asad Naeem said...

where is GetColumnId function's definition?

Asad Naeem said...

I have no words to thank you for this post. It made my life easy and comfortable. Again Thanks a lot....

Asad Naeem said...

only one issue i am facing, whenever I open the file, I always get this repair message. "Excel was able to open the file by repairing or emoving the unreachable content".

Repaired Records: Cell information from /x/worksheets/sheet2.xml part

Brent V said...

Hi Asad,

Thanks for the feedback. Sorry, GetColumnId was not formatted very well. I fixed it so it is not jammed on the end of a line. Also, check out the file you can download. It kept the formatting better.

As far as that generic error you are getting, it can be a big pain to figure out where the bug is. It just means that something is not formatted correctly. Most likely some tag is missing. It is telling you that sheet2.xml is the file that has the error. There are some tools for Visual Studio and also stand alone (I reference them here). In particular, in the Open XML SDK 2.0 Productivity Tool for Microsoft Office you can validate your Excel file. It sometimes gives you better error messages.

I would recommend starting with a blank template (file) again and adding back content until it breaks again.

Good luck.

Brent

The Answer said...

Hi Brent,

Thanks a million for posting this code. I saved me for one of my projects. I had to make some small modifications to the code such as comment out the following

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

This was giving me the "Excel was able to open the file by repairing or emoving the unreachable content" problem which a commenter above had.

Any plans on making a high level API?

btw your blog rocks. i wish you all the best for your future endeavors and please keep up your wonderful work, since you are impacting lives.

Brent V said...

Hi The Answer,

Thank you for the very nice kudos. That meant a lot to me.

As far as your change, I can see how that might be template specific and thus not work for you. Great find and thanks for the correction.

I had not thought much of making it into an API, but I may if I get a lot of feedback to do so.

Thank you again,

Brent

Andrew Miller said...

Great post - this was a lifesaver. Thanks for putting the time into posting it. There's definately a need for a library that encapsulates this functionality!

Anonymous said...

This post looks great.It is a real life saver.I'am facing one problem where in the code is writing data from the 2425 row(I'am using an existing formatted template).How do i insert the data at a specified row(Say 5th row).Many thanks!

Brent V said...

Hi Andrew,

Try the setting the overwriteContent parameter to false. I think that will do it.

Brent

Anonymous said...

Is there any way to format the cells using Currency format?Many thanks!

Brent V said...

Hi Anonymous,

Yes, you should be able to use the Currency Format. It works the same as any other format. You just have to reference it by index just as you would the other styles/formats.

I hope that helps.

Brent

Anonymous said...

How do we suppress the errors like "Number stored as Text" using OpenXML programatically

Unknown said...
This comment has been removed by the author.
Venkat said...

HI,

Your code is awesome and this is exactly i was looking for. But one thing i could not understand the parameters for the columnHeaderStyleIndexes. What i need to pass to the variable. I am trying to fill a background color and set the font to bold. Please help.

Thanks

Brent V said...

Hi Venkat,

The short answer is a list of unsigned integers where each item is the index of the style you want to apply to that cell. For more info on that search this page or the downloaded source code for "Understanding the StyleIndex". The easiest way is to define the style in original template then just reference it by index in the code.

Hopefully that helps.

Thanks,

Brent

Venkat said...

Thanks Brent. It worked :)

Now trying to see if i can have alternate row colors for the data. Will try and let you know if it works.
Thank you once again.

Venkat

Brian Wells said...

Good stuff!
Has anyone made a version generic enough so that 1)
you could pass in a DataSet with multiple tables and create a new sheet for each datatable?
Also if you could create the column headers on each sheet dynamically via the dataTables - that would be huge so you wouldn't need the 'templatefile.xlsx' to reference..

Thanks

Unknown said...
This comment has been removed by the author.
Unknown said...

Thank you very very much, good work!

I have solved "repair message" issue by correcting WriteDateTimeCell function.

To solve this issue:
Step 1: Create date column in your template
Step 2: find out style index of date in your code.
Step 3: Assign style index of date to your target date column
Step 4: Replace WriteDateTimeCell function with below

protected void WriteDateTimeCell(OpenXmlWriter excelWriter, object value, uint? styleIndex)
{
    ResetCell();
    // write a date cell.DataType = CellValues.Number;
    cell.CellValue = new CellValue(Convert.ToDateTime(value).ToOADate().ToString());

    if (styleIndex.HasValue)
    {
        cell.StyleIndex = styleIndex.Value;
    }
    excelWriter.WriteElement(cell);
}

krishnan said...
This comment has been removed by the author.
krishnan said...

using below code ,i have added autofilter in Excel,string reference= "B6:BA6";
AutoFilter autoFilter1 = new AutoFilter() { Reference = reference };

Worksheet sheet1 = new Worksheet();
sheet1.Append(sheetData);
sheet1.Append(autoFilter1);

Filter working fine,I tried to sort the data using that autofilter option in newly created Excel,but it crashes the entire excel,It displays error message like 'Excel stopped working'...

Can any one help me to resolve this issue?

Anonymous said...

I know this is an old blog, but I'm looking for a solution to Krishnan's problem.

Nitin said...

Hi,

Thanks for providing this code snippet. But when I try to run this I'm getting "The function evaluation was disabled because of an out of memory exception" error for 57 columns and 2 lac rows in my DataTable.

I'm just coding this in VS 2013 and running it in debug mode.

Please provide me any code or settings so that I can get rid of this error.

Thanks,
Nitin.