Export DataSet to Multiple Excel Sheets

Ming_Lu

Rate me:

4.80/5 (42 votes)

12 Dec 2008CPOL

276.4K

8.1K

Exporting multiple tables in a DataSet to multiple sheets in an Excel file

Download source code - 16.65 KB

Introduction

I was in need of exporting multiple tables in a DataSet to an Excel file with multiple sheets, and I couldn't find anything that actually works. So, I wrote one to help people who might be in the same situation. The complete code in ExcelHelper.cs is shown below.

This only works for Excel 2003 and later versions. If a table in the dataset has more than 65,000 rows, it will break it into multiple sheets for the table with sheet name (tableNameX). replaceXmlChar() function is added to escape XML reserved characters.

Limitations of the Code

It does not handle data tables with more than 256 columns (Excel 2003 column limit), and when data tables have very large rows count, it might throw OutOfMemory exception.

Using the Code

To export a DataSet, just call the ExcelHelper.ToExcel() function as follows:

var ds = new DataSet();
           var dt = new DataTable("TableName For Sheet1");
           dt.Columns.Add("col1");
           dt.Columns.Add("col2");
           dt.Rows.Add("Value1", "Value2");

           var dt2 = new DataTable("TableName For Sheet2");
           dt2.Columns.Add("col1");
           dt2.Columns.Add("col2");
           dt2.Rows.Add("Value1", "Value2");
           ds.Tables.Add(dt);
           ds.Tables.Add(dt2);
           ExcelHelper.ToExcel(ds, "test.xls", Page.Response);

Here is the code that does the exporting:

//ExcelHelper.cs

public class ExcelHelper
{
    //Row limits older Excel version per sheet
        const int rowLimit = 65000;

        private static string getWorkbookTemplate()
        {
            var sb = new StringBuilder();
            sb.Append("<xml version>\r\n<Workbook
		xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
            sb.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n
		xmlns:x=\"urn:schemas- microsoft-com:office:excel\"\r\n
		xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n");
            sb.Append(" <Styles>\r\n
		<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n
		<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>");
            sb.Append("\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>\r\n
		<Protection/>\r\n </Style>\r\n
		<Style ss:ID=\"BoldColumn\">\r\n <Font ");
            sb.Append("x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n
		<Style ss:ID=\"s62\">\r\n <NumberFormat");
            sb.Append(" ss:Format=\"@\"/>\r\n </Style>\r\n
		<Style ss:ID=\"Decimal\">\r\n
		<NumberFormat ss:Format=\"0.0000\"/>\r\n </Style>\r\n ");
            sb.Append("<Style ss:ID=\"Integer\">\r\n
		<NumberFormat ss:Format=\"0\"/>\r\n </Style>\r\n
		<Style ss:ID=\"DateLiteral\">\r\n <NumberFormat ");
            sb.Append("ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n
		<Style ss:ID=\"s28\">\r\n");
            sb.Append("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Top\"
		ss:ReadingOrder=\"LeftToRight\" ss:WrapText=\"1\"/>\r\n");
            sb.Append("<Font x:CharSet=\"1\" ss:Size=\"9\"
		ss:Color=\"#808080\" ss:Underline=\"Single\"/>\r\n");
            sb.Append("<Interior ss:Color=\"#FFFFFF\" ss:Pattern=\"Solid\"/>

		</Style>\r\n</Styles>\r\n {0}</Workbook>");
            return sb.ToString();
        }

        private static string replaceXmlChar(string input)
        {
            input = input.Replace("&", "&");
            input = input.Replace("<", "<");
            input = input.Replace(">", ">");
            input = input.Replace("\"", "&quot;");
            input = input.Replace("'", "&apos;");
            return input;
        }

        private static string getWorksheets(DataSet source)
        {
            var sw = new StringWriter();
            if (source == null || source.Tables.Count == 0)
            {
                sw.Write("<Worksheet ss:Name=\"Sheet1\"><Table><Row>

		<Cell  ss:StyleID=\"s62\"><Data ss:Type=\"String\"></Data>
		</Cell></Row></Table></Worksheet>");
                return sw.ToString();
            }
            foreach (DataTable dt in source.Tables)
            {
                if (dt.Rows.Count == 0)
                    sw.Write("<Worksheet ss:Name=\"" + replaceXmlChar(dt.TableName) +
			"\"><Table><Row><Cell  ss:StyleID=\"s62\">

			<Data ss:Type=\"String\"></Data></Cell></Row>
			</Table></Worksheet>");
                else
                {
                    //write each row data
                    var sheetCount = 0;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if ((i % rowLimit) == 0)
                        {
                            //add close tags for previous sheet of the same data table
                            if ((i / rowLimit) > sheetCount)
                            {
                                sw.Write("</Table></Worksheet>");
                                sheetCount = (i / rowLimit);
                            }
                            sw.Write("<Worksheet ss:Name=\"" +
				replaceXmlChar(dt.TableName) +
                                     (((i / rowLimit) == 0) ? "" :
				Convert.ToString(i / rowLimit)) + "\"><Table>");
                            //write column name row
                            sw.Write("<Row>");
                            foreach (DataColumn dc in dt.Columns)
                                sw.Write(
                                    string.Format(
                                        "<Cell ss:StyleID=\"BoldColumn\">

				<Data ss:Type=\"String\">{0}</Data></Cell>",
                                        replaceXmlChar(dc.ColumnName)));
                            sw.Write("</Row>\r\n");
                        }
                        sw.Write("<Row>\r\n");
                        foreach (DataColumn dc in dt.Columns)
                            sw.Write(
                                string.Format(
                                    "<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">

					{0}</Data></Cell>",
                                    		replaceXmlChar
					(dt.Rows[i][dc.ColumnName].ToString())));
                        sw.Write("</Row>\r\n");
                    }
                    sw.Write("</Table></Worksheet>");
                }
            }

            return sw.ToString();
        }
        public static string GetExcelXml(DataTable dtInput, string filename)
        {
            var excelTemplate = getWorkbookTemplate();
            var ds = new DataSet();
            ds.Tables.Add(dtInput.Copy());
            var worksheets = getWorksheets(ds);
            var excelXml = string.Format(excelTemplate, worksheets);
            return excelXml;
        }

        public static string GetExcelXml(DataSet dsInput, string filename)
        {
            var excelTemplate = getWorkbookTemplate();
            var worksheets = getWorksheets(dsInput);
            var excelXml = string.Format(excelTemplate, worksheets);
            return excelXml;
        }

        public static void ToExcel
		(DataSet dsInput, string filename, HttpResponse response)
        {
            var excelXml = GetExcelXml(dsInput, filename);
            response.Clear();
            response.AppendHeader("Content-Type", "application/vnd.ms-excel");
            response.AppendHeader
		("Content-disposition", "attachment; filename=" + filename);
            response.Write(excelXml);
            response.Flush();
            response.End();
        }

        public static void ToExcel
		(DataTable dtInput, string filename, HttpResponse response)
        {
            var ds = new DataSet();
            ds.Tables.Add(dtInput.Copy());
            ToExcel(ds, filename, response);
        }
    }

History

7^th December, 2008: Initial post
8^th December, 2008: Source code and article updated
11^th December, 2008: Source code and article updated

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Ming_Lu

Software Developer Oliver Wyman Groups

United States

Education: Masters in Applied mathematics

Certification:
MCP in Asp.net
MCP in SQL Server 2008 Implementation.

Working Experience In .Net since 2005

Comments and Discussions

I already send to you. thanks.

see below my coding

using Microsoft.Office.Core; // Office 11.0
using Excels = Microsoft.Office.Interop.Excel; //Office 11.0
namespace ImportExportApp.Classes
{
    class ImportExport : IDisposable
    {
        #region variables
        string sqlConnStr = String.Empty;
        DBManager _dbManager;   
       int rowLimit = 10000;
        string excelVersion = "8.0";
        Hashtable myHashtable;
        //for priviledge control
        public string message = string.Empty;
	#endregion
	public ImportExport()
        {
            _dbManager = new DBManager(_loginID, _SCID);
            rowLimit = Convert.ToInt32(ConfigurationManager.AppSettings["rowLimit"].ToString());
            excelVersion = ConfigurationManager.AppSettings["excelVersion"].ToString().Trim();
       }
	private string GetExcelConStr(string fileName)
	{
		string result = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + @";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""";
		if (excelVersion != "8.0")				
			result = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + @";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";";
			//    result = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + @";Extended Properties=""Excel 12.0;HDR=YES;"""; 
			return result;
		}
        #region Import Excel Data To SQL Server
        public void ImportExcelData(string fileName)
        {
            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            // Connection String to Excel Workbook
	//string excelConStr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + @";Extended Properties=""Excel 8.0;HDR=YES;""";
	string excelConStr = this.GetExcelConStr(fileName);
	//Extended Properties=HTML Import
            DbDataAdapter adapter = factory.CreateDataAdapter();
            DbCommand selectCommand = factory.CreateCommand();
            // Create Connection to Excel Workbook
            DbConnection connection = factory.CreateConnection();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            int totalRecords = 0;
            int successfulRecords = 0;
            int errorRecords = 0;
            int duplicateRecords = 0;

            try
            {
                if (fileName != null && fileName != "")
                {
                    #region GetListOfWorkSheets
                    DataTable sheets = new DataTable();
                    sheets = this.GetWorkSheets(fileName);
                    #endregion
                    #region Retrieve from ExcelWorkSheets
                    foreach (DataRow sheet in sheets.Rows)
                    {
                        string sheetName = sheet["TABLE_NAME"].ToString();
                            //selectCommand.CommandText = "SELECT * FROM [Sheet1$]";
                            //selectCommand.CommandText = "SELECT * FROM [" + workSheetName + "$]";
			     selectCommand.CommandText = "SELECT * FROM [" + sheetName + "]";
	//selectCommand.CommandText = "SELECT * FROM [" + sheetName + "] where [ID] is not null";
	                    if (dt != null) dt = null;
                            dt = new DataTable();
                            //Connect to Excel WorkBook
                            connection.ConnectionString = excelConStr;
                            selectCommand.Connection = connection;
                            adapter.SelectCommand = selectCommand;
                            adapter.Fill(dt);
			if (dt.Rows.Count >= 1)
                            {
				if (!string.IsNullOrEmpty(dt.Rows[0][0].ToString()))
				{
				  totalRecords += dt.Rows.Count-1;
                                    this.WriteDataToSQL(dt, sheetName, ref errorRecords, ref successfulRecords, ref totalRecords, ref duplicateRecords);
				//totalRecords = successfulRecords + errorRecords;
				}
                            }   
                    }
                    if (sheets.Rows.Count>=0) 
                     this.WriteMessage(totalRecords, successfulRecords, errorRecords, duplicateRecords);
                    if (sheets != null) sheets.Dispose();
                    #endregion
                }
            }
            catch (Exception ex)
            {
                
            }
            finally
            {
                if (factory != null) factory = null;
                excelConStr = null;
                if (adapter != null) adapter.Dispose();
                if (selectCommand != null) selectCommand.Dispose();
                if (connection != null) connection.Dispose();
                if (ds != null) ds.Dispose();
                if (dt != null) dt.Dispose();
                totalRecords = 0;
                successfulRecords = 0;
                errorRecords = 0;
                duplicateRecords = 0;
                if (_dbManager != null) { _dbManager.Dispose(); _dbManager = null; }
            }
        }

        private void WriteDataToSQL(DataTable dt, string sheetName, ref int errorRecords, ref int successfulRecords, ref int totalRecords, ref int duplicateRecords)
        {
            #region Variables
            string ID = String.Empty;
            int AccessID = 0;
            string PassNo = String.Empty;
            string NRIC = String.Empty;
            string Name = String.Empty;
            int CompanyID = 0;
            string Nationality = String.Empty;
            string Race = String.Empty;
            string Country = String.Empty;
            string State = String.Empty;
            string PostalCode = String.Empty;
            string _address = String.Empty;
            string _gender = "G";
            string _dOB = String.Empty;
            byte[] _photo = null;
            bool _gTFlag = false;
            #endregion
            #region For Loop
            for (int row = 1; row < dt.Rows.Count; row++)
            {  try{
inputData = String.Empty;
			activeData = String.Empty;
			//read data from datatable using 
			ID = dt.Rows[row][0].ToString().Trim().Replace("'", "");
			if (String.IsNullOrEmpty(dt.Rows[row][1].ToString()) == false)
				AccessID = Convert.ToInt32(dt.Rows[row][1].ToString());
			PassNo = CheckStringIsNullOrEmptyReturnString(dt.Rows[row][2].ToString());
NRIC = CheckStringIsNullOrEmptyReturnString(dt.Rows[row][3].ToString());
			Name = CheckStringIsNullOrEmptyReturnString(dt.Rows[row][4].ToString());
			if (String.IsNullOrEmpty(dt.Rows[row][5].ToString()) == false)
				char outputChar = '0';
                        Nationality = CheckStringIsNullOrEmptyReturnString(dt.Rows[row][6].ToString());
			Race = CheckStringIsNullOrEmptyReturnString(dt.Rows[row][7].ToString());
			Country = CheckStringIsNullOrEmptyReturnString(dt.Rows[row][8].ToString());
			State = CheckStringIsNullOrEmptyReturnString(dt.Rows[row][9].ToString());
			PostalCode = CheckStringIsNullOrEmptyReturnString(dt.Rows[row][10].ToString());
			_address = CheckStringIsNullOrEmptyReturnString(dt.Rows[row][11].ToString());
			_gender = CheckStringIsNullOrEmptyReturnString(dt.Rows[row][12].ToString()).ToUpper();
			if (_gender != null && _gender != "")
					{
						if (_gender.Length > 1)
						{
							if (_gender.StartsWith("M"))
								_gender = "M"; //Male
							else if (_gender.StartsWith("F"))
								_gender = "F"; //Female
							else if (_gender.StartsWith("G"))
								_gender = "G"; //All     
							else _gender = "A";
						}
					}
_dOB = CheckStringIsNullOrEmptyReturnString(dt.Rows[row][13].ToString());					
			photoPath = dt.Rows[row][24].ToString();
			_photo = Utilities.GetImageFromFile(photoPath);
			//query to insert to sqlserver database
			//For DeleteFlag = 1 : delete, 0 - don't delete
			int result = 0;
			string output = "0";
			string outputResult = String.Empty;
			result = _dbManager.ImportCAUUser(ID, AccessID, PassNo, NRIC, Name, CompanyID, Nationality, Race, Country, State, _address, PostalCode, _gender, _dOB,  _photo, "0", ref output);
			#region Clear Variables
					ID = String.Empty;
					AccessID = 0;
					PassNo = String.Empty;
					NRIC = String.Empty;
					Name = String.Empty;
					CompanyID = 0;
					Nationality = String.Empty;
					Race = String.Empty;
					Country = String.Empty;
					State = String.Empty;
					PostalCode = String.Empty;
					_address = String.Empty;
					_gender = "O";
					_dOB = String.Empty;				

		#endregion
				}
				catch (Exception forEx)
				{
					
				}
            }
            #endregion
        }

        private DataTable GetWorkSheets(string fileName)
        {
            CheckExcellProcesses();
            //string conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My_Documents\Data.xls; Jet OLEDB:Engine Type=5;Extended Properties=Excel 8.0;";
	//string conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + @"; Jet OLEDB:Engine Type=5;Extended Properties=Excel 8.0;";
	string conStr = GetExcelConStr(fileName);
          DataSet excelData = new DataSet();
           OleDbConnection con = new OleDbConnection(conStr);
                  con.Open();
            DataTable sheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { });
            con.Close();
            // kill the right process after export completed
            KillExcel();
            if (excelData != null) excelData.Dispose();
            if (con != null) con.Dispose();
            return sheets;
        }        
        #endregion

        #region Export SQL Data To Excel Format
        public void ExportSQLDataToExcelFormat(string fileName, string path, string imageFilePath)
        {
            // get process ids before running the excel codes
            CheckExcellProcesses();
            Excels.Application xlsApp = null;
            Excels.Workbook xlsWorkBook = null;
            Excels.Worksheet xlsWorkSheet = null;
            object oMissing = System.Reflection.Missing.Value;
            DataSet ds = new DataSet();
            int totalRows = 0;
            int index = 1;
            short j = 1;
            try
            {

                #region Excel File Location
		string[] tmpFileName = fileName.Trim().Split(new char[] { '.' }, 1);
		fileName = tmpFileName[0];

		//For MS Office 97-2003
		if (fileName.Contains(".xls") == false)
			fileName = fileName + ".xls";
		if (path.EndsWith("\\") == false)
			path = path + "\\";
		if (File.Exists(path + fileName))
					{

						if (fileName.EndsWith(".xls") == true)
							fileName = fileName.Remove(fileName.Length - 4, 4);
						fileName = fileName + "_" + DateTime.Today.ToString("ddMMyyyy") + ".xls";
					}
	#endregion
                #region CreateExcelApplication
                try
                {
                    //create excel application
                    xlsApp = new Excels.Application();
                    xlsApp.DisplayAlerts = false;

                    //create new workbook
                    xlsWorkBook = xlsApp.Workbooks.Add(true);

                    //Excels.Worksheets xlsWorksheets ;
                    //xlsWorkSheet = new Excels.Worksheet();

                    //Add a worksheet to the workbook.
                    xlsWorkSheet = (Excels.Worksheet)xlsApp.Worksheets.Add(oMissing, oMissing, oMissing, oMissing);

                    //Name the sheet
                    xlsWorkSheet.Name = "Sheet_" + j;
                    j++;

                    ////Get a reference to the first sheet of the workbook.
                    //xlsWorkSheet = (Excels.Worksheet)xlsWorkBook.Worksheets.get_Item(1);

                    //Activate current worksheet
                    //xlsWorkSheet.Activate();
                    ((Excels._Worksheet)xlsWorkSheet).Activate();
                }
                catch (Exception eee)
                {
                    message = "Export Failed.";
                    message += "\n\n\n";
                    message += eee.Message.ToString();
                }
                #endregion

               
                    ds = _dbManager.GetUserList();
                    totalRows = ds.Tables[0].Rows.Count;
              #endregion

                //Write Column Name in Excel
                this.WriteColumnNameInExcelWorkSheet( xlsWorkSheet);

                //Format header row (bold, extra row height, autofit width)
                this.FormatHeaderRowInExcelWorkSheet(xlsWorkSheet, xlsApp);

                #region Write Data To Excel File
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    index++;
                    if (index <= rowLimit + 1)
                    {
                        //do your insert
                        this.WriteDataToExcelWorkSheet(imageFilePath, ds.Tables[0].Rows[i], index, xlsWorkSheet);
                    }
                    else
                    {
                        // create a new worksheet and do your insert here

                        //Add a worksheet to the workbook.
                        xlsWorkSheet = (Excels.Worksheet)xlsApp.Worksheets.Add(oMissing, oMissing, oMissing, oMissing);

                        //Name the sheet
                        xlsWorkSheet.Name = "Sheet_" + j;
                        j++;

                        //Activate current worksheet
						//xlsWorkSheet.Activate();
						((Excels._Worksheet)xlsWorkSheet).Activate();

                        this.WriteColumnNameInExcelWorkSheet(xlsWorkSheet);

                        this.FormatHeaderRowInExcelWorkSheet(xlsWorkSheet, xlsApp);

                        index = 2;
                        this.WriteDataToExcelWorkSheet(imageFilePath, ds.Tables[0].Rows[i], index, xlsWorkSheet);

                    }
                }
                #endregion

                //FormatDataRowsInExcelWorkSheet
                this.FormatDataRowsInExcelWorkSheet(xlsWorkSheet, index);

                #region Make excel workbook visible to user after all data has been added to worksheet
                try
                {
		xlsApp.DisplayAlerts = false;
                    xlsWorkBook.Close(true, path + fileName, null);

                    log.AuditLogActivity(Log.AccessType.CAUUser.ToString(), Log.ActionType.Export.ToString(), "Export User Completed. Total records exported : " + totalRows.ToString(), "", _loginID, _SCID);

                    message = "Export Completed.";
                    message += "\n\n\n";
                    message += "Total records exported : " + totalRows.ToString() + "\n\n";
}
                catch (Exception ee)
                {
                    
                }
                #endregion

                #region Export data to client machine
                //strhdr = "attachment;filename=" + strFile;
                //Response.Clear();
                //Response.ContentType = "application/vnd.ms-excel";
                //Response.ContentEncoding = System.Text.Encoding.Default;
                //Response.AppendHeader("Content-Disposition", strhdr);
                //Response.WriteFile(filename);
                //Response.Flush();
                //Response.Clear();
                //Response.Close();
                #endregion

            }
            catch (Exception ex)
            {
                
            }
            finally
            {
                // kill the right process after export completed
                KillExcel();
                try
                {
                    if (xlsApp != null)
                    {
                        xlsApp.Quit();
                        xlsApp = null;
                    }
                    if (xlsWorkBook != null) xlsWorkBook = null;
                    if (xlsWorkSheet != null) xlsWorkSheet = null;
                    if (ds != null) ds = null;
                    if (oMissing != null) oMissing = null;
                }
                catch { }
                if (_dbManager != null) { _dbManager.Dispose(); _dbManager = null; }
            }
        }

        private void CheckExcellProcesses()
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            myHashtable = new Hashtable();
            int iCount = 0;

            foreach (Process ExcelProcess in AllProcesses)
            {
                myHashtable.Add(ExcelProcess.Id, iCount);
                iCount = iCount + 1;
            }
            if (AllProcesses != null) { AllProcesses = null; }
        }

        private void KillExcel()
        {
            Process[] AllProcesses = Process.GetProcessesByName("EXCEL");

            // check to kill the right process
            foreach (Process ExcelProcess in AllProcesses)
            {
                if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
                    ExcelProcess.Kill();
            }

            if (AllProcesses != null) { AllProcesses = null; }
        }

        #region Write Column Name in Excel WorkSheet
        private void WriteColumnNameInExcelWorkSheet(Excels.Worksheet xlsWorkSheet)
        {
            try
            {
                //Add table headers to worksheet
                xlsWorkSheet.Cells[1, 1] = "ID";
                xlsWorkSheet.Cells[1, 2] = "AccessID";
                xlsWorkSheet.Cells[1, 3] = "CSN";
                xlsWorkSheet.Cells[1, 4] = "NRIC";
                xlsWorkSheet.Cells[1, 5] = "Name";
                xlsWorkSheet.Cells[1, 6] = "Company Name";
                xlsWorkSheet.Cells[1, 7] = "Nationality";
                xlsWorkSheet.Cells[1, 8] = "Race";
                xlsWorkSheet.Cells[1, 9] = "Country";
                xlsWorkSheet.Cells[1, 10] = "State";
                xlsWorkSheet.Cells[1, 11] = "Postal Code";
                xlsWorkSheet.Cells[1, 12] = "Address";
                xlsWorkSheet.Cells[1, 13] = "Gender";
                xlsWorkSheet.Cells[1, 14] = "DOB";
                xlsWorkSheet.Cells[1, 15] = "Photo";
            }
            catch (Exception e1){ }
        }
        #endregion
        #region Format header row (bold, extra row height, autofit width)
        private void FormatHeaderRowInExcelWorkSheet(Excels.Worksheet xlsWorkSheet, Excels.Application xlsApp)
        {
            xlsWorkSheet.get_Range("A1", "BA1").Font.Bold = true;
	xlsWorkSheet.get_Range("A1", "BA1").Rows.RowHeight = 1.5 * xlsWorkSheet.StandardHeight;
	xlsWorkSheet.get_Range("A1", "BA1").EntireRow.AutoFit();
            //Freeze the columm headers
	xlsWorkSheet.get_Range("A2", "BA2").Select();
            xlsApp.ActiveWindow.FreezePanes = true;
        }
        #endregion
		#region Write data to Excel worksheet
		private void WriteDataToExcelWorkSheet(string imageFilePath, DataRow dr, int index, Excels.Worksheet xlsWorkSheet)
		{
			try
			{
				string ID = null;
				if (!Convert.IsDBNull(dr[0])) ID = dr[0].ToString().Trim();
				xlsWorkSheet.Cells[index, 1] =  "'" + ID;

				if (!Convert.IsDBNull(dr[1])) xlsWorkSheet.Cells[index, 2] = dr[1];
				if (!Convert.IsDBNull(dr[2])) xlsWorkSheet.Cells[index, 3] = dr[2].ToString().Trim();
				if (!Convert.IsDBNull(dr[3])) xlsWorkSheet.Cells[index, 4] = dr[3].ToString().Trim();
				if (!Convert.IsDBNull(dr[4])) xlsWorkSheet.Cells[index, 5] = dr[4].ToString().Trim();
				//for CompanyID
				if (!Convert.IsDBNull(dr[5])) //xlsWorkSheet.Cells[index, 6] = dr[5].ToString().Trim();
					xlsWorkSheet.Cells[index, 6] = _dbManager.GetCompanyNameByID(dr[5].ToString().Trim());

				if (!Convert.IsDBNull(dr[6])) xlsWorkSheet.Cells[index, 7] = dr[6].ToString().Trim();
				if (!Convert.IsDBNull(dr[7])) xlsWorkSheet.Cells[index, 8] = dr[7].ToString().Trim();
				if (!Convert.IsDBNull(dr[8])) xlsWorkSheet.Cells[index, 9] = dr[8].ToString().Trim();
				if (!Convert.IsDBNull(dr[9])) xlsWorkSheet.Cells[index, 10] = dr[9].ToString().Trim();
				if (!Convert.IsDBNull(dr[10])) xlsWorkSheet.Cells[index, 11] = dr[10].ToString().Trim();
				if (!Convert.IsDBNull(dr[11])) xlsWorkSheet.Cells[index, 12] = dr[11].ToString().Trim();
				//For Gender
				if (!Convert.IsDBNull(dr[12]))
				{
					string gender = dr[12].ToString().Trim().ToUpper();
					switch (gender)
					{
						case "F":
							xlsWorkSheet.Cells[index, 13] = "Female";
							gender = null;
							break;
						case "M":
							xlsWorkSheet.Cells[index, 13] = "Male";
							gender = null;
							break;
						case "G":
							xlsWorkSheet.Cells[index, 13] = "All";
							gender = null;
							break;
						default:
							xlsWorkSheet.Cells[index, 13] = "General";
							gender = null;
							break;
					}
				}
				if (!Convert.IsDBNull(dr[13])) xlsWorkSheet.Cells[index, 14] = dr[13];
				if (!Convert.IsDBNull(dr[14])) xlsWorkSheet.Cells[index, 15] = dr[14].ToString().Trim();

				#region for Photo
				try
				{
					byte[] imageByte = null;
					if (!Convert.IsDBNull(dr[24]))
					{
						//imageByte = dr[24].GetSqlBytes(24).Value;
						imageByte = (byte[])dr[24];
						//save the image to imageFileLocation
						string imgFileName = imageFilePath + cardID.ToString() + "_Photo.png";
						//Image image = Utilities.GetImageFromDB(imageByte);
						////image.Save(imgFileName, System.Drawing.Imaging.ImageFormat.Jpeg);
						//image.Save(imgFileName);
						Bitmap bmp = Utilities.GetImageFromDB(imageByte);
						bmp.Save(imgFileName, System.Drawing.Imaging.ImageFormat.Png);
						bmp.Dispose();
						xlsWorkSheet.Cells[index, 15] = imgFileName; //dr[0].GetSqlBytes(24).Value;
						imageByte = null;
						if (bmp !=null) bmp = null;
						imgFileName = null;
					}
				}
				catch (Exception photoEx){				}
				#endregion
			}
			catch (Exception e)			{	}
		}
		#endregion

        #region Format data rows (align to center and left, autofit width and height)
        private void FormatDataRowsInExcelWorkSheet(Excels.Worksheet xlsWorkSheet, int index)
        {
           xlsWorkSheet.get_Range("A1", "BA" + index.ToString()).VerticalAlignment = Excels.XlVAlign.xlVAlignCenter;
		   xlsWorkSheet.get_Range("A1", "BA" + index.ToString()).HorizontalAlignment = Excels.XlHAlign.xlHAlignLeft;
		   xlsWorkSheet.get_Range("A1", "BA" + index.ToString()).EntireColumn.AutoFit();
		   xlsWorkSheet.get_Range("A1", "BA" + index.ToString()).EntireRow.AutoFit();
        }
        #endregion
        #endregion

        #region Write the Error record into Error log
        public void WriteToErrorLogFile(string fileName, string inputLogFile, string inputData)
        {
            string errorFile = errorFolder + fileName + ".err";// "ImportUser.err";

            StreamWriter swError = new StreamWriter(@errorFile, true);
            swError.WriteLine(inputLogFile);
            swError.WriteLine(inputData);
            swError.WriteLine(" ");
            swError.Flush();
            swError.Close();
        }
        #endregion

        #region Write the successful record into activity log
        public void WriteToAcitvityLogFile(string fileName, string inputData)
        {
            string logFile = activeFolder + fileName + ".log";
            StreamWriter swError1;
            swError1 = new StreamWriter(@logFile, true);
            swError1.WriteLine(inputData);
            swError1.WriteLine(" ");
            swError1.Flush();
            swError1.Close();
        }
        #endregion

        #region Validation
        private int CheckStringIsNullOrEmpty(string str)
        {
            int result = 0;
            if (String.IsNullOrEmpty(str) == false)
                result = Convert.ToInt32(str);
            return result;
        }

        private string CheckStringIsNullOrEmptyReturnString(string str)
        {
            string result =String.Empty ;
            if (String.IsNullOrEmpty(str) == false)
                result = Convert.ToString(str).Trim().Replace("'","`");
            return result;
        }

        private bool CheckStringIsNullOrEmptyReturnBool(string str)
        {
            bool result = false;
            if (String.IsNullOrEmpty(str) == false)
                result = Convert.ToBoolean(str);
            return result;
        }

        #endregion

        #region IDisposable Members
        /// <summary>
        /// Dispose Method
        /// </summary>
        public void Dispose()
        {
            sqlConnStr = String.Empty;
            if (_dbManager != null) {_dbManager.Dispose(); _dbManager=null;}
            if (debug != null) {debug.Dispose(); debug =null;}
            if (log != null) { log.Dispose(); log = null; }

            folderPath = String.Empty;
            errorFolder = String.Empty;
            activeFolder = String.Empty;
            rowLimit = 0;
            //workSheetName = null;

            if (myHashtable != null) myHashtable = null;
        }
        #endregion
	}
}

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.