That must be a common scenario, but I have not found anything about it:
I want to use an ActiveX control if it is available, e.g. the "Microsoft Excel 11.0 Object Library" to export some data shown on the screen to Excel and show the new document. If the ActiveX control is not installed, so probably office is not installed, I do not want to offer that feature in that installation.
What is the right way to approach this scenario
use ActiveX ctrl if available
Ashwin Jayamohan
Having created a helper class by importing the object, I'd have thought you could try to create an instance and if it works, then it's available
Ramakrishna Neela
Andrew Raymond
I looked into generating XML directly for the Office Apps, too, and Excel is OK. But after looking at the XML format for Word I felt kind of sick...
To me it seems that if you just want to spit out 10 lines and/or a simple table in Word or Excel the pseudo macro approach through the ActiveX component seems to be much easier. Just to take the Excel example with bogus data:
Excel.ApplicationClass excel = new Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
for (int col = 1; col < 10; col++ )
excel.Cells[1, col] = "Column #"+col;
for (int row = 1; row < 20; row++ )
for (int col = 1; col < 10; col++)
excel.Cells[row, col] = string.Format("Data ({0},{1})", col, row);
excel.Visible = true;
Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
worksheet.Activate();
That does already the job of filling a table in Excel and opening the worksheet. If there is any easier way, I am always open to suggestions...
i2adnan
But why don't you use ADO.NET for your export to Excel, then you don't depend on the Excel installation or just write a XSL file yourself
Here is a little example how to export an DataSet to an Excel file:
public void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, ExportFormat FormatType)
{
try
{
string ns = "http://www.w3.org/1999/XSL/Transform";
writer.Formatting = Formatting.Indented;
writer.WriteStartDocument( );
writer.WriteStartElement("xsl","stylesheet",ns);
writer.WriteAttributeString("version","1.0");
writer.WriteStartElement("xsl:output");
writer.WriteAttributeString("method","text");
writer.WriteAttributeString("version","4.0");
writer.WriteEndElement( );
// xsl-template
writer.WriteStartElement("xsl:template");
writer.WriteAttributeString("match","/");
// xsl:value-of for headers
for(int i=0; i< sHeaders.Length; i++)
{
writer.WriteString("\"");
writer.WriteStartElement("xsl:value-of");
writer.WriteAttributeString("select", "'" + sHeaders
writer.WriteEndElement( ); // xsl:value-of
writer.WriteString("\"");
if (i != sFileds.Length - 1) writer.WriteString( (FormatType == ExportFormat.CSV ) "," : " " );
}
// xsl:for-each
writer.WriteStartElement("xsl:for-each");
writer.WriteAttributeString("select","Export/Values");
writer.WriteString("\r\n");
// xsl:value-of for data fields
for(int i=0; i< sFileds.Length; i++)
{
writer.WriteString("\"");
writer.WriteStartElement("xsl:value-of");
writer.WriteAttributeString("select", sFileds
writer.WriteEndElement( ); // xsl:value-of
writer.WriteString("\"");
if (i != sFileds.Length - 1) writer.WriteString( (FormatType == ExportFormat.CSV ) "," : " " );
}
writer.WriteEndElement( ); // xsl:for-each
writer.WriteEndElement( ); // xsl-template
writer.WriteEndElement( ); // xsl:stylesheet
writer.WriteEndDocument( );
}
catch(Exception Ex)
{
throw Ex;
}
}
private void Export(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
{
using(MemoryStream stream = new MemoryStream())
using(XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8))
{
// XSLT to use for transforming this dataset.
CreateStylesheet(writer, sHeaders, sFileds, FormatType);
writer.Flush( );
stream.Seek( 0, SeekOrigin.Begin);
XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
XslTransform xslTran = new XslTransform();
xslTran.Load(new XmlTextReader(stream), null, null);
System.IO.StringWriter sw = new System.IO.StringWriter();
xslTran.Transform(xmlDoc, null, sw, null);
//Writeout the Content
using( StreamWriter strwriter = new StreamWriter(FileName) )
{
strwriter.WriteLine(sw.ToString());
strwriter.Close();
}
}
}
office of technology
Hi John,
If so, I think you may use try catch block to create an Excel Object.
If there is no Excel COM available, the CreateObject Job will failed.
If you still have any concern, please feel free to post here.
Best regards,
Peter Huang