This class uses an ODBC connection to create an Microsoft Excel Workbook from the results of a Microsoft SQL query.
///
/// Export data to an excel workbook
///
///
/// The example below creates an excel workbook with a single worksheet containing information from the Northwind sample database.
///
/// string file = "C:\\workbook.xls";
/// string sql = "SELECT * FROM Customers";
/// string dbConn = "server=(local);database=Northwind;uid=sa;password=;";
///
/// Excel ex = new Excel(file, sql, dbConn);
/// ex.WorksheetName = "Customers";
/// ex.Export();
///
///
public class Excel
{
private string _WorkbookPath, _ExcelConnString, _Worksheet, _SqlQuery, _SqlConnection;
private SortedList _types;
///
/// The name of the worksheet to export data to within the Excel workbook.
///
public string WorksheetName
{
get
{
if (_Worksheet == null) _Worksheet = "Export";
return _Worksheet;
}
set { _Worksheet = value; }
}
///
/// Create a new instance of the Excel class
///
/// The full-physical path (including the file name) of the excel workbook to export to
/// The command text / query that retrieves data from the sql server
/// The connection string to the sql database used to run the query
public Excel(string workbookPath, string query, string sqlConnection)
{
this._WorkbookPath = workbookPath;
this._SqlQuery = query;
this._SqlConnection = sqlConnection;
if (File.Exists(workbookPath))
{
_ExcelConnString = "Driver={Microsoft Excel Driver (*.xls)};READONLY=FALSE;DBQ=" + workbookPath;
}
else
{
_ExcelConnString = "Driver={Microsoft Excel Driver (*.xls)};READONLY=FALSE;CREATE_DB=\"" + workbookPath + "\";DBQ=" + workbookPath;
}
this._types = new SortedList(128);
this._types.Add("System.Boolean", "TEXT");
this._types.Add("System.DateTime", "DATE");
this._types.Add("System.Decimal", "NUMBER");
this._types.Add("System.Double", "NUMBER");
this._types.Add("System.Guid", "TEXT");
this._types.Add("System.Int64", "NUMBER");
this._types.Add("System.Int32", "NUMBER");
this._types.Add("System.Int16", "NUMBER");
this._types.Add("System.String", "TEXT");
}
///
/// Export the data defined in the constructor to the excel workbook specified
///
/// True if export was succesfull; False otherwise
public bool Export()
{
bool returnVal = true, WorkbookExists = false;
OdbcConnection conn = new OdbcConnection(this._ExcelConnString);
SqlConnection SqlConn = new SqlConnection(this._SqlConnection);
string sql = "", colList = "";
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(this._SqlQuery, SqlConn);
SqlConn.Open();
da.Fill(ds);
SqlConn.Close();
da.Dispose();
conn.Open();
//check to see if the worksheet already exists
sql = "SELECT * FROM [" + this.WorksheetName + "$]";
OdbcCommand comm = new OdbcCommand(sql, conn);
try
{
//this will error out if the table doesn't exist
OdbcDataReader dr = comm.ExecuteReader();
dr.Close();
WorkbookExists = true;
}
catch
{
WorkbookExists = false;
}
if (! WorkbookExists)
{
//the worksheet doesn't exist, so create it
sql = "CREATE TABLE " + this.WorksheetName + " (";
colList = "(";
//get a list of columns to create from the sql query
foreach (DataColumn col in ds.Tables[0].Columns)
{
string t = this._types[col.DataType.ToString()].ToString();
sql += "[" + col.ColumnName + "] " + ((t == null || t == string.Empty) ? "TEXT" : t) + ", ";
colList += col.ColumnName + ", ";
}
//remove the last comma from the column lists and close the parenthesis
sql = sql.Remove(sql.LastIndexOf(", "), 2) + ")";
colList = colList.Remove(colList.LastIndexOf(", "), 2) + ")";
//execute the create table command
comm.CommandText = sql;
comm.ExecuteNonQuery();
//iterate through the sql query and insert each row into the excel worksheet
foreach (DataRow row in ds.Tables[0].Rows)
{
sql = "INSERT INTO " + this.WorksheetName + " " + colList + " VALUES (";
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
//iterate through each column in the row and get the value
sql += "'" + row[i].ToString().Replace("'", "''") + "', ";
}
sql = sql.Remove(sql.LastIndexOf(", "), 2) + ")";
//execute the insert command
comm.CommandText = sql;
comm.ExecuteNonQuery();
}
}
else
{
//the worksheet does exist, so change the worksheet name to be unique
this.WorksheetName = this.WorksheetName + "_1";
returnVal = Export();
//throw new Exception("The worksheet name specified already exists in the specified workbook.");
}
ds.Dispose();
comm.Connection.Close();
comm.Dispose();
return returnVal;
}
}