EXPORT TABLE SQL SERVER WITH QUERY TO XLSX FILE EXCEL .NET

luca micos
luca micos used Ask the Experts™
on
ciao a tutti, volevo sapere in che modo era possibile esportare dei dati da una tabella di un db su sql server in excel tramite framework .net su vb.net
Fino ad oggi uso questa procedura ma per tabelle con molti dati il procedimento impiega troppo tempo. Importo la tabella su un datatable o un datagridview per visualizzarla al front end, una volta caricata con una funzione che si basa sulla classe interop excel vado a ciclare tutte le righe della tabella e le scrivevo su un file excel.
Mi sono accorto che per tabelle molto grandi l'export dura troppo tempo, allora spesso ricorro all'importazione direttamente da excel sulla scheda dati/nuova query/da database/da database sql server. la scheda mi richiede il server, il database e la query e con pochissimo tempo con il net framework si caricano tabelle con molti dati, già convertite anche per ogni formato cella. La mia domanda è se la stessa procedura può essere richiamata da vb.net, quindi con una funzione che una volta passati i parametri di server,db,query scriva velocemente su un file excel (.xlsx)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Welcome to EE.  English please.

Author

Commented:
hello everyone, I wanted to know how it was possible to export data from a table on sql server database in excel via .net framework with vb.net
Up to now I use this procedure but for tables with a lot of data the process takes too much time. I amount the table on a datatable or a datagridview to display it at the front end, once loaded with a function based on the interop excel class I cycle all the rows of the table and write them to an excel file.
I realized that for very large tables, the export lasts too much time, so I often resort to importing directly from Excel on the data sheet / new query / from database / from sql server database. the board requires me the server, the database and the query and with very little time with the net framework you load tables with many data, already converted for each cell format. My question is if the same procedure can be invoked by vb.net, then with a function that once passed the server parameters, db, query quickly write to an excel file (.xlsx)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I cannot speak for SQL Server but I've used the Open XML SDK with .Net to go from Oracle and create native Word and Excel files.  Cobmine that with LINQ and it seemed pretty efficient.

https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Can't speak for VB.NET but..

Excel PowerQuery is commonly used to connect to date sources such as SQL Server and load into Excel.

You can also use Excel VBA to execute a SQL Server object and load.  Check out my article  Microsoft Excel & SQL Server:  Self service BI to give users the data they want for a demo on how to pull that off.

SQL Server Integration Services (SSIS) can be used to build an ETL package with SQL Server as the source, Excel as the destination, and a Data Flow task performing the data movement between the two.  SQL Server's  Export Data Wizard (right-click on database >  Tasks ... > Export data) is a handy UI that will create a package that does this.

Hope this helps.
Jim

( added Visual Basic.NET zone )

Author

Commented:
I already manually use the export directly from excel on the data sheet / new query / from database / from sql server database. the board requires me the server, the database and the query and with very little time with the net framework you load tables with many data, already converted for each cell format. I need to call this procedure from a function in vb.net. I have to create a connection string from the vb.net server, send the query and write the table in Excel. can someone help me?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>can someone help me?

What is your level of expertise with .Net?

There are MANY examples on the web using LINQ and OpenXML.

>>very little time with the net framework you load tables with many data, already converted for each cell format

I'm not sure I understand this with the language translation issues.  If you are loading .Net DataTables then trying to work with those to convert, I would advise against that.  It doesn't seem like an efficient method.  Go straight from the Query using LINQ to Excel.  No need to intermediate tables.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial