I already send to you. thanks.
see below my coding
using Microsoft.Office.Core;
using Excels = Microsoft.Office.Interop.Excel;
namespace ImportExportApp.Classes
{
class ImportExport : IDisposable
{
#region variables
string sqlConnStr = String.Empty;
DBManager _dbManager;
int rowLimit = 10000;
string excelVersion = "8.0";
Hashtable myHashtable;
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"";";
return result;
}
#region Import Excel Data To SQL Server
public void ImportExcelData(string fileName)
{
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
string excelConStr = this.GetExcelConStr(fileName);
DbDataAdapter adapter = factory.CreateDataAdapter();
DbCommand selectCommand = factory.CreateCommand();
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 [" + sheetName + "]";
if (dt != null) dt = null;
dt = new DataTable();
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);
}
}
}
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;
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";
else if (_gender.StartsWith("F"))
_gender = "F";
else if (_gender.StartsWith("G"))
_gender = "G";
else _gender = "A";
}
}
_dOB = CheckStringIsNullOrEmptyReturnString(dt.Rows[row][13].ToString());
photoPath = dt.Rows[row][24].ToString();
_photo = Utilities.GetImageFromFile(photoPath);
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 = GetExcelConStr(fileName);
DataSet excelData = new DataSet();
OleDbConnection con = new OleDbConnection(conStr);
con.Open();
DataTable sheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { });
con.Close();
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)
{
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];
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
{
xlsApp = new Excels.Application();
xlsApp.DisplayAlerts = false;
xlsWorkBook = xlsApp.Workbooks.Add(true);
xlsWorkSheet = (Excels.Worksheet)xlsApp.Worksheets.Add(oMissing, oMissing, oMissing, oMissing);
xlsWorkSheet.Name = "Sheet_" + j;
j++;
((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
this.WriteColumnNameInExcelWorkSheet( xlsWorkSheet);
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)
{
this.WriteDataToExcelWorkSheet(imageFilePath, ds.Tables[0].Rows[i], index, xlsWorkSheet);
}
else
{
xlsWorkSheet = (Excels.Worksheet)xlsApp.Worksheets.Add(oMissing, oMissing, oMissing, oMissing);
xlsWorkSheet.Name = "Sheet_" + j;
j++;
((Excels._Worksheet)xlsWorkSheet).Activate();
this.WriteColumnNameInExcelWorkSheet(xlsWorkSheet);
this.FormatHeaderRowInExcelWorkSheet(xlsWorkSheet, xlsApp);
index = 2;
this.WriteDataToExcelWorkSheet(imageFilePath, ds.Tables[0].Rows[i], index, xlsWorkSheet);
}
}
#endregion
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
#endregion
}
catch (Exception ex)
{
}
finally
{
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");
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
{
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();
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();
if (!Convert.IsDBNull(dr[5]))
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();
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 = (byte[])dr[24];
string imgFileName = imageFilePath + cardID.ToString() + "_Photo.png";
Bitmap bmp = Utilities.GetImageFromDB(imageByte);
bmp.Save(imgFileName, System.Drawing.Imaging.ImageFormat.Png);
bmp.Dispose();
xlsWorkSheet.Cells[index, 15] = imgFileName;
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";
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
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;
if (myHashtable != null) myHashtable = null;
}
#endregion
}
}
|