Source Viewer: AutoExcelImport.cs
Font Size:
1
2
3
4
5
6
7
adonet-excel.cs
AutoExcelImport.cs
AutomateExcel.cs
CopyData-SqlToExcel-TwoOledbConnections.cs
ExtractToExcel.cs
ExtractToExcel2.cs
..
// autoExcelImport.cs // // automate Excel, import data from a db query into the Excel spreadsheet. // // references: // http://support.microsoft.com/default.aspx?scid=kb;EN-US;306023 // // Thu, 18 Sep 2003 20:59 // using Excel= Microsoft.Office.Interop.Excel; public class TestExcelImport { static object missing = System.Reflection.Missing.Value; public static void Main() { string strConnect= "OLEDB;Provider=sqloledb;Data Source=dinoch-8;Initial Catalog=ASPXAPPS;Integrated Security=SSPI;" ; string strSql= "select * from ASPX_AccessLog"; // if you want to use the northwind MDB as the source for data: // string northwindMdb= "c:\\Program Files\\Microsoft Office\\Office10\\Samples\\Northwind.mdb"; // string strConnect= "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + // northwindMdb + ";"; // string strSql= "Select * From Orders"; try { System.Console.WriteLine ("Creating new Excel.Application"); Excel.Application App= new Excel.Application(); System.Console.WriteLine ("Making application visible"); App.Visible = true; Excel.Workbooks workbooks = App.Workbooks; Excel._Workbook xlwb = workbooks.Add(missing); Excel.Sheets sheets = xlwb.Worksheets; Excel._Worksheet xlwks; try { // often a new workbook will have 3 or more blank sheets. // here, we remove those we don't want. xlwks = (Excel._Worksheet) sheets.get_Item(3); xlwks.Delete(); xlwks = (Excel._Worksheet) sheets.get_Item(2); xlwks.Delete(); } catch { // ignore } xlwks = (Excel._Worksheet) sheets.get_Item(1); xlwks.Name= "Imported Data"; // rename the worksheet Excel.Range range1= xlwks.get_Range("B3", missing); Excel.QueryTables querytables= xlwks.QueryTables; Excel._QueryTable qt = (Excel._QueryTable) querytables.Add(strConnect, range1, strSql); qt.FieldNames = true; qt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows; // xlInsertDeleteCells qt.RowNumbers = false; qt.FillAdjacentFormulas = false; qt.RefreshOnFileOpen = false; qt.HasAutoFormat = true; qt.BackgroundQuery = false; qt.SavePassword = true; qt.SaveData = true; qt.Refresh(false); // the saved file gets put into My Documents xlwks.SaveAs("ExcelImport-" + System.DateTime.Now.ToString("yyyyMMMdd-HHmmssEDT") + ".xls", missing, // [In, Optional, HasFieldMarshal] System.Object FileFormat, missing, // [In, Optional, HasFieldMarshal] System.Object Password, missing, // [In, Optional, HasFieldMarshal] System.Object WriteResPassword, missing, // [In, Optional, HasFieldMarshal] System.Object ReadOnlyRecommended, missing, // [In, Optional, HasFieldMarshal] System.Object CreateBackup, missing, // [In, Optional, HasFieldMarshal] System.Object AddToMru, missing, // [In, Optional, HasFieldMarshal] System.Object TextCodepage, missing, // [In, Optional, HasFieldMarshal] System.Object TextVisualLayout, missing // [In, Optional, HasFieldMarshal] System.Object Local ); xlwb.Saved= true; // App.Quit(); // if we do not quit, Excel remains open and visible System.Runtime.InteropServices.Marshal.ReleaseComObject(xlwb); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(App); } catch (System.Exception ex1) { System.Console.WriteLine("Exception: " + ex1); } finally { System.GC.Collect(); } } }
The srcview page has been enjoyed 291584 times since 18 September 2003