Export SQL to Excel

Question

How can I export my data from SQL Server 2008 to Excel 2010 or later?

I tried using the SQL method:

sp_configure 'show advanced options', 0; GO RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 0; GO RECONFIGURE; GO INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\testing.xls;Extended Properties=EXCEL 12.0;HDR=YES', 'SELECT NO_ORDRE, Date FROM [Sheet1$]') SELECT [NO_ORDRE], GETDATE() FROM ORDRE GO 

Sure, I get the error: OLE DB provider "Microsoft.Jet.OLEDB.4.0" cannot be used for distributed queries because the provider is configured to run in STA mode.

and then I tried in C #:

  public class ExportToExcel { private Excel.Application app; private Excel.Workbook workbook; private Excel.Worksheet previousWorksheet; // private Excel.Range workSheet_range; private string folder; public ExportToExcel(string folder) { this.folder = folder; this.app = null; this.workbook = null; this.previousWorksheet = null; // this.workSheet_range = null; createDoc(); } private void createDoc() { try { app = new Excel.Application(); app.Visible = false; workbook = app.Workbooks.Add(1); } catch (Exception excThrown) { throw new Exception(excThrown.Message); } finally { } } public void shutDown() { try { workbook = null; app.Quit(); } catch (Exception excThrown) { throw new Exception(excThrown.Message); } finally { } } public void ExportTable(string query, string sheetName) { SqlDataReader myReader = null; try { using (var connectionWrapper = new Connexion()) { var connectedConnection = connectionWrapper.GetConnected(); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.Add(Missing.Value, Missing.Value, 1, Excel.XlSheetType.xlWorksheet); worksheet.Name = sheetName; previousWorksheet = worksheet; SqlCommand myCommand = new SqlCommand(query, connectionWrapper.conn); myReader = myCommand.ExecuteReader(); int columnCount = myReader.FieldCount; for (int n = 0; n < columnCount; n++) { //Console.Write(myReader.GetName(n) + "\t"); createHeaders(worksheet, 1, n + 1, myReader.GetName(n)); } int rowCounter = 2; while (myReader.Read()) { for (int n = 0; n < columnCount; n++) { //Console.WriteLine(); //Console.Write(myReader[myReader.GetName(n)].ToString() + "\t"); addData(worksheet, rowCounter, n + 1, myReader[myReader.GetName(n)].ToString()); } rowCounter++; } } } catch (Exception e) { Console.WriteLine(e.ToString()); } finally { if (myReader != null && !myReader.IsClosed) { myReader.Close(); } myReader = null; } } public void createHeaders(Excel.Worksheet worksheet, int row, int col, string htext) { worksheet.Cells[row, col] = htext; } public void addData(Excel.Worksheet worksheet, int row, int col, string data) { worksheet.Cells[row, col] = data; } public void SaveWorkbook() { String folderPath = "C:\\My Files\\" + this.folder; if (!System.IO.Directory.Exists(folderPath)) { System.IO.Directory.CreateDirectory(folderPath); } string fileNameBase = "db"; String fileName = fileNameBase; string ext = ".xlsx"; int counter = 1; while (System.IO.File.Exists(folderPath + fileName + ext)) { fileName = fileNameBase + counter; counter++; } fileName = fileName + ext; string filePath = folderPath + fileName; try { workbook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } catch (Exception e) { Console.WriteLine(e.ToString()); } } } 

Unfortunately, I got an error: Getting the factory COM-class for a component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

Any idea how I can export SQL to Excel?

Answer 1

It’s best to just write it to a CSV. Excel registers itself as a file handler for CSV files, so by default it will be opened in excel.

For instance:

 private void SQLToCSV(string query, string Filename) { SqlConnection conn = new SqlConnection(connection); conn.Open(); SqlCommand cmd = new SqlCommand(query, conn); SqlDataReader dr = cmd.ExecuteReader(); using (System.IO.StreamWriter fs = new System.IO.StreamWriter(Filename)) { // Loop through the fields and add headers for (int i = 0; i < dr.FieldCount; i++) { string name = dr.GetName(i); if (name.Contains(",")) name = "\"" + name + "\""; fs.Write(name + ","); } fs.WriteLine(); // Loop through the rows and output the data while (dr.Read()) { for (int i = 0; i < dr.FieldCount; i++) { string value = dr[i].ToString(); if (value.Contains(",")) value = "\"" + value + "\""; fs.Write(value + ","); } fs.WriteLine(); } fs.Close(); } } 

Answer 2

C # SQL in Excel

Call you SP from the database

 public DataTable GetDrugUtilizationReport_IndividualGenerateFile(long pharmacyId, DateTime from, DateTime to, long DrugNameId, int sortBy) { var parameters = new Dictionary<string, object> { { "PharmacyId", pharmacyId }, { "DateFrom", from }, { "DateTo", to }, { "DrugNameId", DrugNameId }, { "SortBy", sortBy } }; return ExecuteQuery("RPT_DrugUtilizationReportByIndividualGenerateFile", CommandType.StoredProcedure, parameters); } 

Use in C # code

 private void OnCreateFileCommand(object obj) { string path, parameterLabel; path = ConfigurationManager.AppSettings["VSSPORTEXELExportPath"]; parameterLabel = FromDate.ToString("yyyy-MM-dd") + "_" + ToDate.ToString("yyyy-MM-dd"); try { path = ExcelUtlity.ExportDataToExcel( dataTable: context.GetDrugUtilizationReport_IndividualGenerateFile(GlobalVar.Pharminfo.pharminfo_PK, FromDate, ToDate, SelectedDrug != null ? SelectedDrug.drugnameid_PK : 0, sortBy: SortBy + 1), directoryPath: path, fileName_withoutExt: "DrugUtilizationReport" + "__" + parameterLabel, skipComplexObjects: true, skipInheritedProps: true); DXMessageBox.Show("Data exported successfully at \"" + path + "\".", GlobalVar.MessageTitle, MessageBoxButton.OK, MessageBoxImage.Information); } catch (Exception ex) { string errorMessage = ExceptionHelper.ProcessException(ex); DXMessageBox.Show(errorMessage, GlobalVar.MessageTitle, MessageBoxButton.OK, MessageBoxImage.Error); } } 

Excel Utility

 public static string ExportDataToExcel(DataTable dataTable, string directoryPath, string fileName_withoutExt, bool skipComplexObjects, bool skipInheritedProps, string[] skipProps = null) { if (directoryPath[directoryPath.Length - 1] == '\\') // no need to check for >0 length. let it throw an exection for that directoryPath = directoryPath + "\\"; using (var spreadSheet = new SpreadsheetControl()) { // Create new excel document and import the datatable to the worksheet spreadSheet.CreateNewDocument(); spreadSheet.BeginUpdate(); var worksheet = spreadSheet.Document.Worksheets.ActiveWorksheet; worksheet.Import(source: dataTable, addHeader: true, firstRowIndex: 0, firstColumnIndex: 0); // applying style on header Range range = worksheet.Range["A1:" + worksheet.Columns[worksheet.Columns.LastUsedIndex].Heading+"1"]; Formatting rangeFormatting = range.BeginUpdateFormatting(); rangeFormatting.Fill.BackgroundColor = System.Drawing.Color.LightSteelBlue; rangeFormatting.Font.FontStyle = SpreadsheetFontStyle.Bold; range.AutoFitColumns(); range.EndUpdateFormatting(rangeFormatting); spreadSheet.EndUpdate(); fileName_withoutExt += ".xlsx"; Directory.CreateDirectory(directoryPath); // if directory already exists, CreateDirectory will do nothing spreadSheet.SaveDocument(directoryPath + fileName_withoutExt, DocumentFormat.OpenXml); return directoryPath + fileName_withoutExt; } } 

Using Microsoft Office dll

 public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType) { Microsoft.Office.Interop.Excel.Application excel; Microsoft.Office.Interop.Excel.Workbook excelworkBook; Microsoft.Office.Interop.Excel.Worksheet excelSheet; Microsoft.Office.Interop.Excel.Range excelCellrange; try { // Start Excel and get Application object. excel = new Microsoft.Office.Interop.Excel.Application(); // for making Excel visible excel.Visible = false; excel.DisplayAlerts = false; // Creation a new Workbook excelworkBook = excel.Workbooks.Add(Type.Missing); // Workk sheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet; excelSheet.Name = worksheetName; excelSheet.Cells[1, 1] = ReporType; excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString(); // loop through each row and add values to our sheet int rowcount = 2; foreach (DataRow datarow in dataTable.Rows) { rowcount += 1; for (int i = 1; i <= dataTable.Columns.Count; i++) { // on the first iteration we add the column headers if (rowcount == 3) { excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName; excelSheet.Cells.Font.Color = System.Drawing.Color.Black; } excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString(); //for alternate rows if (rowcount > 3) { if (i == dataTable.Columns.Count) { if (rowcount % 2 == 0) { excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false); } } } } } // now we resize the columns excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; excelCellrange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); //now save the workbook and exit Excel excelworkBook.SaveAs(saveAsLocation); ; excelworkBook.Close(); excel.Quit(); return true; } catch (Exception ex) { DXMessageBox.Show(ex.Message); return false; } finally { excelSheet = null; excelCellrange = null; excelworkBook = null; } } /// <summary> /// FUNCTION FOR FORMATTING EXCEL CELLS /// </summary> /// <param name="range"></param> /// <param name="HTMLcolorCode"></param> /// <param name="fontColor"></param> /// <param name="IsFontbool"></param> public void FormattingExcelCells(Microsoft.Office.Interop.Excel.Range range, string HTMLcolorCode, System.Drawing.Color fontColor, bool IsFontbool) { range.Interior.Color = System.Drawing.ColorTranslator.FromHtml(HTMLcolorCode); range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor); if (IsFontbool == true) { range.Font.Bold = IsFontbool; } } 

Answer 3

I have a modified code that was listed above and it works. Edit according to your requirements.

 namespace ExcelExport { public class ExportToExcel { string strCon = ConfigurationManager.ConnectionStrings["SafewayGVDemoDBContext"].ConnectionString; private Microsoft.Office.Interop.Excel.Application app; private Microsoft.Office.Interop.Excel.Workbook workbook; private Microsoft.Office.Interop.Excel.Worksheet previousWorksheet; // private Excel.Range workSheet_range; private string folder; public ExportToExcel(string folder) { this.folder = folder; this.app = null; this.workbook = null; this.previousWorksheet = null; // this.workSheet_range = null; createDoc(); } private void createDoc() { try { app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; workbook = app.Workbooks.Add(1); } catch (Exception excThrown) { throw new Exception(excThrown.Message); } finally { } } public void shutDown() { try { workbook = null; app.Quit(); } catch (Exception excThrown) { throw new Exception(excThrown.Message); } finally { } } public void ExportTable(string procName, string sheetName) { SqlDataReader myReader = null; try { Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.Add(Missing.Value, Missing.Value, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet); using (SqlConnection Sqlcon = new SqlConnection(strCon)) { SqlCommand cmd = new SqlCommand(); Sqlcon.Open(); cmd.Connection = Sqlcon; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = procName; cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50)); cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output; cmd.Parameters["@pvchAction"].Value = "select"; worksheet.Name = sheetName; previousWorksheet = worksheet; myReader = cmd.ExecuteReader(); int columnCount = myReader.FieldCount; for (int n = 0; n < columnCount; n++) { //Console.Write(myReader.GetName(n) + "\t"); createHeaders(worksheet, 1, n + 1, myReader.GetName(n)); } int rowCounter = 2; while (myReader.Read()) { for (int n = 0; n < columnCount; n++) { //Console.WriteLine(); //Console.Write(myReader[myReader.GetName(n)].ToString() + "\t"); addData(worksheet, rowCounter, n + 1, myReader[myReader.GetName(n)].ToString()); } rowCounter++; } } } catch (Exception e) { Console.WriteLine(e.ToString()); } finally { if (myReader != null && !myReader.IsClosed) { myReader.Close(); } myReader = null; } } public void createHeaders(Microsoft.Office.Interop.Excel.Worksheet worksheet, int row, int col, string htext) { worksheet.Cells[row, col] = htext; } public void addData(Microsoft.Office.Interop.Excel.Worksheet worksheet, int row, int col, string data) { worksheet.Cells[row, col] = data; } public void SaveWorkbook() { String folderPath = @"C:\My Files\" + this.folder; if (!System.IO.Directory.Exists(folderPath)) { System.IO.Directory.CreateDirectory(folderPath); } string fileNameBase = "db"; String fileName = fileNameBase; string ext = ".xlsx"; int counter = 1; //System.IO.File.Open(folderPath + fileName + ext, System.IO.FileMode.Open); while (System.IO.File.Exists(folderPath + @"\"+ fileName + ext)) { fileName = fileNameBase + counter; counter++; } fileName = fileName + ext; string filePath = folderPath +@ "\"+ fileName; try { workbook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } catch (Exception e) { Console.WriteLine(e.ToString()); } } } 

Answer 4

2 simple options:

1) use the SQL Server Import and Export Wizard, which you can use to export any table from your database to Excel (just make sure the display is correct)

2) It would just run your sql statement, and then in the results window below, select everything and right-click and do "Copy with headers", and then just paste the results into Excel

Answer 5

Since you are using Excel 2010, the easiest solution is to download PowerPivot from Microsoft and execute the SQL query directly. This will create an updatable data connection that pulls the data from the query into the pivot table.

http://www.microsoft.com/bi/en-us/Solutions/Pages/PowerPivot.aspx

Answer 6

Link your data to the grid ... and use the following code .....

 protected void ImageButton1_Click1(object sender, ImageClickEventArgs e) { string attachment = "attachment; filename=Contacts.xls"; Response.ClearContent(); Response.AddHeader("content-disposition", attachment); Response.ContentType = "application/ms-excel"; StringWriter sw = new StringWriter();HtmlTextWriter htw = new HtmlTextWriter(sw); GridView2.RenderControl(htw); Response.Write(sw.ToString()); Response.End(); } 

Answer 7

This 100% works on VS-2013 Premium for C # to test the coded interface. just copy and paste the code What is this. Its work for SQL Server database. You can save the data in xls, xlsx or csv and use the same csv for parameterization. You need to install the following packages to make it work.

using System.Data.SqlClient

using Excel = Microsoft.Office.Interop.Excel

using SQL = System.Data p>

/// *** Copy here and paste it under / To generate code for this test, select "Generate Code for Coded User Interface" from the context menu and select one of the menu items.

  SqlConnection cnn; string connectionstring = null; string sql = null; string data = null; int i = 0; int j = 0; ////*** Preparing excel Application Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; ///*** Opening Excel application xlApp = new Microsoft.Office.Interop.Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(@"C:\Users\MM18100\Documents\Visual Studio 2013\Projects\SQL\SQL\Book1.csv"); xlWorkSheet = (Excel.Worksheet)(xlWorkBook.ActiveSheet as Excel.Worksheet); ////*** It will always remove the prvious result from the CSV file so that we can get always the updated data xlWorkSheet.UsedRange.Select(); xlWorkSheet.UsedRange.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); xlApp.DisplayAlerts = false; //xlWorkBook.Save(); /////***Opening SQL Database connectionstring = "Integrated Security = SSPI;Initial Catalog=Exascale; Data Source=DCNA-Q-SQL-07;"; cnn = new SqlConnection(connectionstring); cnn.Open(); ////** Write your Sql Query here sql = "SELECT TOP 10 [FirstName],[MiddleName],[LastName],[Email],[AltEmail],[Phone],[AltPhoneNumber],[Mobile],[Fax],[CompanyName],[AuthorizedUserName],[AuthorizedUserPhone],[CreatedDate],[ModifiedDate],[VERSION],[LanguageID],[TaxID],[CustomerType]FROM [Exascale].[dbo].[Customer] Where [FirstName] = 'Automation'"; ///*** Preparing to retrieve value from the database SQL.DataTable dtable = new SQL.DataTable(); SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn); SQL.DataSet ds = new SQL.DataSet(); dscmd.Fill(dtable); ////*** Generating the column Names here string[] colNames = new string[dtable.Columns.Count]; int col = 0; foreach (SQL.DataColumn dc in dtable.Columns) colNames[col++] = dc.ColumnName; char lastColumn = (char)(65 + dtable.Columns.Count - 1); xlWorkSheet.get_Range("A1", lastColumn + "1").Value2 = colNames; xlWorkSheet.get_Range("A1", lastColumn + "1").Font.Bold = true; xlWorkSheet.get_Range("A1", lastColumn + "1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; /////*** Inserting the Column and Values into Excel file for (i = 0 ; i <= dtable.Rows.Count - 1; i++) { for (j = 0; j <= dtable.Columns.Count-1; j++) { data = dtable.Rows[i].ItemArray[j].ToString(); xlWorkSheet.Cells[i + 2, j + 1] = data; } } ///**Saving the csv file without notification. xlApp.DisplayAlerts = false; xlWorkBook.Save(); //xlWorkBook.SaveAs("Book1.csv", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); ////MessageBox.Show("Excel file created , you can find the file C:\\Users\\MM18100\\Documents\\informations.xls"); } private void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; MessageBox.Show("Exception Occured while releasing object " + ex.ToString()); } finally { GC.Collect(); } }