BeryEretusere
New Member
All,I have a datatable in C# that I would like to Export to Excel. I have the following code:\[code\] public void ExportToExcel(DataTable dt) { this.Visible = true; this.StatusBar = "Importing Data from DAS Application"; if (string.IsNullOrEmpty(dt.TableName)) dt.TableName = "Name"; if (dt.Rows.Count == 1) dt.Rows.Add(new object[dt.Columns.Count]); //Forces headers to be displayed in Excel if only one row if (dt.DataSet == null) //I should point out that in my code I am creating the DataTable from some kind of IEnumerable dynamically. Obviously, if I was including a DataTable from a different DataSet, I probably would want to create a copy of the DataTable to isolate the datat first and avoid any side-effects on the original data. { DataSet ds = new DataSet(); ds.Tables.Add(dt); ds.DataSetName = "ds"; } this.AddWorkbook(); using (XMLMaps mp = new XMLMaps(this)) { mp.AddXMLMaps(dt.DataSet.GetXmlSchema()); this.XmlImportXML(dt.DataSet.GetXml(), mp[1]); } this.StatusBar = false; } private class XMLMaps:IDisposable { private cExportToExcel Parent { get; set; } public XMLMaps(cExportToExcel parent) { Parent = parent; } internal object this[int i] { get { return XMLMap.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, XMLMap, new object[] { i }); } } private object XMLMap { get { return Parent.ActiveWorkbook.GetType().InvokeMember("XmlMaps", BindingFlags.GetProperty, null, Parent.ActiveWorkbook, null); } } internal void AddXMLMaps(string str) { XMLMap.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, XMLMap, new object[] { str }); } public void Dispose() { Parent = null; } } private void XmlImportXML(string data, object map) { dynamic rng = this.Range(this.Cells(1, 1), this.Cells(1, 1)); this.ActiveWorkbook.GetType().InvokeMember("XmlImportXML", BindingFlags.InvokeMethod, null, this.ActiveWorkbook, new object[] { data, map, true, rng }); rng = null; }\[/code\]This is part of a class I created below to interact with Excel COM objects via reflection (developing against multiple versions of Excel--in hindsight, I could have just as easily used dynamic for most of this, but I digress).Here is the rest of the class (in case it helps and/or you want to use it/improve it)\[code\]public class cExportToExcel:IDisposable{ private object _objExApp; public cExportToExcel() { Type Excel = Type.GetTypeFromProgID("Excel.Application"); try { //try to get active object, will catch exception if none _objExApp = Marshal.GetActiveObject("Excel.Application"); //try to set Application.ScreenUpdating property, test for Application Busy. Will throw exception of Application is busy, which will instantiate a new instance of the Excel.Application type this.ScreenUpdating = false; this.ScreenUpdating = true; } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.Message); _objExApp = null; _objExApp = Activator.CreateInstance(Excel); this.ScreenUpdating = true; } this.Visible = true; } private object Workbooks { get { return _objExApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, _objExApp, null); } } public bool ScreenUpdating { get { return (bool)_objExApp.GetType().InvokeMember("ScreenUpdating", BindingFlags.GetProperty, null, _objExApp, null); } set { object[] args = new object[1]; args[0] = value; _objExApp.GetType().InvokeMember("ScreenUpdating", BindingFlags.SetProperty, null, _objExApp, args); } } public object Range(object objUpper, object objLower) { object[] args = { objUpper, objLower }; object objRange; objRange = ActiveSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, ActiveSheet, args); args = null; return objRange; } public object ActiveWorkbook { get { return _objExApp.GetType().InvokeMember("ActiveWorkbook", BindingFlags.GetProperty, null, _objExApp, null); } } public void AddWorkbook() { object objWbooks = Workbooks; objWbooks.GetType().InvokeMember("Add",BindingFlags.InvokeMethod,null,objWbooks,null); objWbooks = null; } public object ActiveSheet { get { return _objExApp.GetType().InvokeMember("ActiveSheet",BindingFlags.GetProperty,null,_objExApp,null); } } public object Cells(int intRow, int intCol) { object[] args = new object[2]; object objActSheet = this.ActiveSheet; args[0] = intRow; args[1] = intCol; object objCells = objActSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, objActSheet, args); args = null; objActSheet = null; return objCells; } public void Activate() { this.ActiveWorkbook.GetType().InvokeMember("Activate", BindingFlags.InvokeMethod, null, this.ActiveWorkbook, null); } public object StatusBar { get { return _objExApp.GetType().InvokeMember("StatusBar", BindingFlags.GetProperty, null, _objExApp, null); } set { _objExApp.GetType().InvokeMember("StatusBar", BindingFlags.SetProperty, null, _objExApp, new object[] { value }); } } public void Dispose() { System.Diagnostics.Debug.WriteLine(Marshal.ReleaseComObject(_objExApp)); _objExApp = null; }}\[/code\]I have noticed that even though I have an XML Map, and even though I am explicitly passing the XML Map to XMLImportXML, that whenever retrieving the XML Data Excel complains that the Xml source does not refer to a schema, and if I have any blank columns in the first row of my data row, I end up with columns out of order. I also noticed that Excel decides to create its own map instead of using mine.Is there any way, using XMLImport or some other Excel XML Reader, to associate the XML with the correct columns in the correct order?Thanks.