Export to Excel in silverlight 5

Question

I use the following code for export to excel,it works fine,but how can I change it to xport directly to .xlsx file not xml file,also I do not want to use automation because it works very slow.

Thanks.

public static class DataGridxtensions
{
  public static void Export(this DataGrid dg)
  {
    ExportDataGrid(dg);
  }

  public static void ExportDataGrid(DataGrid dGrid)
  {    
    SaveFileDialog objSFD = new SaveFileDialog() { DefaultExt = "xml", Filter = "Excel XML (*.xml)|*.xml", FilterIndex = 1 };

    if (objSFD.ShowDialog() == true)
    {
      string strFormat = objSFD.SafeFileName.Substring(objSFD.SafeFileName.IndexOf('.') + 1).ToUpper();
      StringBuilder strBuilder = new StringBuilder();
      if (dGrid.ItemsSource == null) return;
      List<string> lstFields = new List<string>();

      if (dGrid.HeadersVisibility == DataGridHeadersVisibility.Column || dGrid.HeadersVisibility == DataGridHeadersVisibility.All)
      {
        foreach (DataGridColumn dgcol in dGrid.Columns)
          lstFields.Add(FormatField(dgcol.Header.ToString(), strFormat, false));
        BuildStringOfRow(strBuilder, lstFields, strFormat);
      }

      foreach (object data in dGrid.ItemsSource)
      {
        lstFields.Clear();
        foreach (DataGridColumn col in dGrid.Columns)
        {
          string strValue = "";
          Binding objBinding = null;

          if (col is DataGridBoundColumn)
            objBinding = (col as DataGridBoundColumn).Binding;

          if (col is DataGridTemplateColumn)
          {
            //This is a template column... let us see the underlying dependency object
            DependencyObject objDO = (col as DataGridTemplateColumn).CellTemplate.LoadContent();
            FrameworkElement oFE = (FrameworkElement)objDO;
            FieldInfo oFI = oFE.GetType().GetField("TextProperty");

            if (oFI != null)
            {
              if (oFI.GetValue(null) != null)
              {
                if (oFE.GetBindingExpression((DependencyProperty)oFI.GetValue(null)) != null)
                  objBinding = oFE.GetBindingExpression((DependencyProperty)oFI.GetValue(null)).ParentBinding;
              }
            }
          }

          if (objBinding != null)
          {
            if (objBinding.Path.Path != "")
            {
              PropertyInfo pi = data.GetType().GetProperty(objBinding.Path.Path);
              if (pi != null) strValue = pi.GetValue(data, null).ToString();
            }

            if (objBinding.Converter != null)
            {
              if (strValue != "")
                strValue = objBinding.Converter.Convert(strValue, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
              else
                strValue = objBinding.Converter.Convert(data, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
            }
          }

          lstFields.Add(FormatField(strValue, strFormat, true));
        }

        BuildStringOfRow(strBuilder, lstFields, strFormat);
      }

      StreamWriter sw = new StreamWriter(objSFD.OpenFile());
      if (strFormat == "XML")
      {
        //Let us write the headers for the Excel XML
        sw.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
        sw.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
        sw.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\">");
        sw.WriteLine("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
        sw.WriteLine("<Author>Arasu Elango</Author>");
        sw.WriteLine("<Created>" + DateTime.Now.ToLocalTime().ToLongDateString() + "</Created>");
        sw.WriteLine("<LastSaved>" + DateTime.Now.ToLocalTime().ToLongDateString() + "</LastSaved>");
        sw.WriteLine("<Company>Atom8 IT Solutions (P) Ltd.,</Company>");
        sw.WriteLine("<Version>12.00</Version>");
        sw.WriteLine("</DocumentProperties>");
        sw.WriteLine("<Worksheet ss:Name=\"Export\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");
        sw.WriteLine("<Table>");
      }

      sw.Write(strBuilder.ToString());

      if (strFormat == "XML")
      {
        sw.WriteLine("</Table>");
        sw.WriteLine("</Worksheet>");
        sw.WriteLine("</Workbook>");
      }

      sw.Close();
    }
  }

  private static void BuildStringOfRow(StringBuilder strBuilder, List<string> lstFields, string strFormat)
  {
    switch (strFormat)
    {
      case "XML":
        strBuilder.AppendLine("<Row>");
        strBuilder.AppendLine(String.Join("\r\n", lstFields.ToArray()));
        strBuilder.AppendLine("</Row>");
        break;
      case "CSV":
        strBuilder.AppendLine(String.Join(",", lstFields.ToArray()));
        break;
    }
  }

  private static string FormatField(string data, string format, bool isNumber)
  {
    switch (format)
    {
      case "XML":

        if (isNumber)
        {
          return String.Format("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>", data);
        }
        else
        {
          return String.Format("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", data);
        }
      case "CSV":
        return String.Format("\"{0}\"", data.Replace("\"", "\"\"\"").Replace("\n", "").Replace("\r", ""));
    }
    return data;
  }
}

Answer 1

I can think of two options.

First, there are several component vendors who have solved this problem. There's SyncFusion's XlsIo, and Infragistics, and Telerik (looks like they have .xls only).

If the financial cost is too high, then you might consider OpenXml. The learning curve would be a bit steeper, and you can't use it directly from Silverlight -- you'd have to make the server convert your data to an .xlsx file and then your client would save the results. Also, you'll need to be aware of this issue.

But, it is free. I have had some success reading Excel files with it. I think this might be the NuGet package you'd need, or you can download an installer.

Fortunately for me, my employer paid for the first option above when we needed to create an .xlsx file. :-)

Answer 2

Yup office Automation way to slow! I had to change to, I used code from

http://www.codeproject.com/Articles/45731/Export-Silverlight-DataGrid-to-Excel-XML-CSV?msg=4829021#xx4829021xx