public void Execute(Controller controller) { int start = int.Parse(controller.Params["start"]); int limit = int.Parse(controller.Params["limit"]); TabularQuery query = TabularQuery.Find(int.Parse(controller.Params["table"])); CustomQuery customQuery = new CustomQuery(query.SelectStatement); customQuery.Logger = GlobalApplication.CreateLogger(typeof(CustomQuery)); QueryResults results = customQuery .StartAtRow(start) .LimitResultsTo(limit) .ConfigureFilters(controller.Params) .Execute(); ArrayList rows = new ArrayList(results.Rows.Count); foreach (object[] row in results.Rows) { Hashtable attributes = new Hashtable(results.Columns.Length); for (int i = 0; i < results.Columns.Length; i++) { attributes[results.Columns[i]] = row[i]; } rows.Add(attributes); } controller.Context.Response.ContentType = "text/javascript"; controller.RenderText( "{ results: " + results.TotalRowCount.ToString() + ", rows: " + JavaScriptConvert.SerializeObject(rows) + " }" ); }
public void Execute(Controller controller) { try { string filename = Path.GetTempFileName(); logger.Info("Saving tabular data into " + filename); controller.PropertyBag["filename"] = filename; TabularQuery query = TabularQuery.Find(int.Parse(controller.Params["table"])); CustomQuery customQuery = new CustomQuery(query.SelectStatement); customQuery.Logger = GlobalApplication.CreateLogger(typeof(CustomQuery)); QueryResults results = customQuery .ConfigureFilters(controller.Params) .Execute(); /* * IMPORTANT: To make sure the Excel applicaton quits properly follow the below steps: * SEE: http://support.microsoft.com/default.aspx?scid=kb;en-us;317109 * * 1. each COM object should be a new variable * 2. use System.Runtime.InteropServices.Marshal.ReleaseComObject when finished with an object * 3. set variable to null * 4. call the Quit method of the application * 5. run garbage collection methods */ logger.Debug("initializing Excel application"); Application excel = new Application(); Workbooks books = excel.Workbooks; Workbook book = books.Add(Missing.Value) as Workbook; Sheets sheets = book.Worksheets as Sheets; Worksheet sheet; excel.Visible = true; logger.Debug("removing unneeded sheets"); while (sheets.Count > 1) { sheet = book.ActiveSheet as Worksheet; sheet.Delete(); } sheet = book.ActiveSheet as Worksheet; sheet.Name = query.Title; // first row is column names for (int i = 0; i < results.Columns.Length; i++) { ((Range)sheet.Cells[1, i + 1]).Font.Bold = true; sheet.Cells[1, i + 1] = results.Columns[i]; } // subsequent rows are values for (int i = 0; i < results.Rows.Count; i++) { object[] row = results.Rows[i]; for (int j = 0; j < row.Length; j++) { sheet.Cells[i + 2, j + 1] = row[j]; } } logger.Debug("saving excel workbook"); book.SaveCopyAs(filename); book.Saved = true; books.Close(); logger.Debug("cleaning up COM objects"); Marshal.ReleaseComObject(sheet); Marshal.ReleaseComObject(sheets); Marshal.ReleaseComObject(book); Marshal.ReleaseComObject(books); sheet = null; sheets = null; book = null; books = null; logger.Debug("closing excel application"); excel.Quit(); logger.Debug("final clean up and freeing of resources"); Marshal.ReleaseComObject(excel); excel = null; GC.GetTotalMemory(false); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.GetTotalMemory(true); logger.Debug("sending content"); controller.CancelView(); controller.CancelLayout(); IResponse response = controller.Context.Response; string attachment = String.Format("attachment; filename=\"{0}.xlsx\"", query.Title); string contentLength = new FileInfo(filename).Length.ToString(); response.Clear(); response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; response.AppendHeader("Content-Disposition", attachment); response.AppendHeader("Content-Length", contentLength); response.WriteFile(filename); } catch (Exception e) { logger.Error("Could not export to Excel", e); controller.Response.StatusCode = 500; } }