Export DataSet to Multiple Excel Sheets

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
	}
}