Remember to add the following references (Project > Add Reference > COM)
Microsoft Excel 12.0 Object Library (or an older version)
This code is based on the following links:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace ExportDBTablesToExcel
{
class ExportToExcel
{
private Excel.Application app ;
private Excel.Workbook workbook;
private Excel.Worksheet previousWorksheet;
private Excel.Range workSheet_range;
private string folder;
private static string CONNECTION_STR = "Data Source=(local);Database=DATABASE_NAME;"
+ "Integrated Security=SSPI;";
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 e)
{
Console.Write(e.ToString());
}
finally
{
}
}
public void shutDown()
{
try
{
workbook = null;
app.Quit();
}
catch (Exception e)
{
Console.Write(e.ToString());
}
finally
{
}
}
public void ExportTable(string query,string sheetName)
{
SqlConnection myConnection = new SqlConnection(CONNECTION_STR);
SqlDataReader myReader = null;
try
{
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.Add(Missing.Value, Missing.Value, 1, Excel.XlSheetType.xlWorksheet);
worksheet.Name = sheetName;
previousWorksheet = worksheet;
myConnection.Open();
SqlCommand myCommand = new SqlCommand(query,
myConnection);
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();
}
if (myConnection != null)
{
myConnection.Close();
}
myReader = null;
myConnection = 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());
}
}
static void Main(string[] args)
{
ExportToExcel export = new ExportToExcel(args[0]);
export.ExportTable("SELECT * FROM t_table1","t_table1");
export.ExportTable("SELECT * FROM t_table2","t_table2");
export.ExportTable("SELECT * FROM t_table3","t_table3");
export.SaveWorkbook() ;
export.shutDown();
}
}
}
http://www.codeproject.com/KB/cs/Excel_and_C_.aspx
and
http://www.codeproject.com/KB/database/sql_in_csharp.aspx?fid=16002&df=90&mpp=25&noise=3&sort=Position&view=Quick&fr=26&select=1490011
