Excel Export - One Click

I'm using DbVisualizer 9.5.1

I used to use Aqua Data Studio.  That program had a feature called "View as Spreadsheet".  I so miss that feature.  Here's an example of what it did.

First, user writes a query and returns a recordset into the grid.

Second, user clicks an icon on the grid toolbar "View as Spreadsheet".

Third, user is brought to a new Excel workbook with the grid (columns and rows) provided.

That's it!  It's oh so simple but oh so nice.  I realize users can export to a file but this is so handy when you want to do a little bit of analytics on some ad-hoc analysis.  Often I don't need to save my results.  I just want to check something out and Excel is a very powerful tool for that type of work.  What Aqua did to marry their tool up with Excel was genius.  I might urge you all to consider this functionality.

If you want the DbVisualizer's implementation to be even better than Aqua's I would urge the process makes two worksheets in the workbook.  The first worksheet would be the grid results.  The second worksheet would be the code used to generate the results.  THAT WOULD BE AWESOME!!!!

http://www.aquafold.com/d10/docs/10.html

Unbelievable!  This is very exciting.  Thank you very much for taking suggestions so seriously.  I'll have to patiently wait (and encourage) my IT department to deploy it at my company but they'll get there eventually.

Again, thank you.  I'm happy to be getting on board with software that is so receptive to change.

SCORE!!!! My company finally upgraded and I tested this feature.  It's great!  Even better than asked, you all included the source code in the XLSX export.  This is absolutely wonderful.  Thank you for listening to your customers and taking such decisive action.  Ya'll are the best!

Thanks for making this suggestion, Mike. It's one of my most used features already.

I would appreciate the option to turn ExcelIncludeSQLCommand off for the button, the same way you can for file export.

Hi there,

Jumping back on this thread I guess.  Since this might be coming back up in development I thought I'd offer another small observation.  :)

First, the user-option to choose whether or not to include the SQL in the resultant XLSX makes sense to me.  It's yet another option, yes.  It's also super easy to just delete the rows or clear the contents, yes.  But, well, whatever.  Makes sense to me as one of the reasons DbVisualizer is such a powerful tool is that it gives users such control over how they want to work.  Yes, this would be yet one more for users to decide on.

The observation I'd bring up is the fact that the SQL code provided to Excel is only the final statement of one's query and not the entire query that was last executed.  To allow better error debugging my team imposes statement delimiters wherever possible.  That's helped out debugging efforts using DbVisualizers message pane.  But the side effect is that the SQL pushed to excel isn't complete.

Try it yourself to see what I mean.

Example #1:  Run this code and the entire script will fully populate in Excel

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test
SELECT GETDATE() as TodaysDate
INTO #Test

SELECT * FROM #Test

Example #2:  Run this code and you'll see only the final statement populates in Excel

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SET NOCOUNT ON ;

IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test ;
SELECT GETDATE() as TodaysDate
INTO #Test ;

SELECT * FROM #Test ;

Thanks for the reply, Roger.

What I do is select the top three rows and hit Ctrl+(minus) to delete them so the headers start at the top like a normal sheet. I do this for readability -- it lets me hit Select All and use my keyboard shortcut for "autofit all column widths".

The issue is that I end up doing this every single time I click the button, which is a lot.

I can have workarounds: I can use Ctrl+Shift+Home to select only the query results before I autofit the column widths. I could also use the snippet below as a bookmark all the time and then map a hotkey to open C:\tmp\results.xlsx.

But I know you already have the ability to make it a setting because the ExcelIncludeSqlCommand option exists, so I thought I'd ask.

@set maxrows -1;

@export on;

@export set Filename="C:\tmp\results.xlsx" appendfile="clear" format="xls" ExcelIncludeSQLCommand="false" ExcelTitle="";

Thanks, Roger. Just wanted to say that DbVisualizer has more features and timesavers than almost any other tool I use. Your programmers should be very proud. Your forum support is great too.

Well how about that?!  "Execute Buffer" does exactly what you said it does.  That works for me.  Now I just have to read up a bit on what the difference is between "Execute" and "Execute Buffer".  Something else to learn.  :)

Thank you!

Roger,

Including the query in the spreadsheet is really a best practice, I understand why you did it. It enables people to recreate a spreadsheet any time after it was made. Can I suggest that the checkbox offer the options

• include query above grid [current behavior]
  
• include query on separate tab.
  

Just wanted to say that I love this feature and have started using "Export Selection to Excel" in place of "Copy Selection As Formatted Text" when I want to paste into emails!