Monday, 26 January 2009

Export SQL Server tables to Excel in c#

This piece of code executes a sql query on a sql server database and returns the result into a excel workbook (with column headings included).

Remember to add the following references (Project > Add Reference > COM)

Microsoft Excel 12.0 Object Library (or an older version)


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();
}





}

}


This code is based on the following links:

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

10 comments:

Vijay said...

First, Thank you bcoz the code helped me and i much appreciated..

I have used your code. I installed MS Office 2003 on developer machine. I want to publich the program and run on ohter pc which not having MS office 2003 or later version of MS office.

Is it necessay to installe MS excell 2003 on each computer to get working the code.

Please help me how to use Microsoft.Office.Interop.Excel in project deployment so ,no need to install MS excel on client machine.

Thanks
vijay

Alex said...

I like to use ms excel fles. Because usually they are quite reliable and have a few problems. But one day I had an objectionable problem with my excel files, but through good fortune I could worked out this condition and perhaps the this utility would be usable for other complicated troubles with excel files - microsoft excel restore old copy.

Josh said...

I keep getting a runtime index out of bounds error at:
ExportToExcel export = new ExportToExcel(args[0]);


Any thoughts?

thanks

Anonymous said...

This is awesome. Great work!

SM said...

Hi Josh, you need to set a command line argument which specifies the the output folder where the excel file will be saved

Anonymous said...

SM, can you elaborate? I'm receiving the same error and specified a folder to output it to.

Sourabh said...

Thank you Boss, you really help me...

Sourabh said...

Explain these 2 lines:

1: Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.Add(Missing.Value, Missing.Value, 1, Excel.XlSheetType.xlWorksheet);

2: 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);

Anonymous said...

The best one! Very much ready to use..

Unknown said...

This was awesome. You are a hero.