//public static string GetDocumentNo(string processCode, short year = 0, bool onlyNumber = false)
//{
// string documentNo = "";
// short yearSel = year;
// if (yearSel == 0 &&
// (!onlyNumber))
// {
// yearSel = Convert.ToInt16(DateTime.Now.Year);
// }
// try
// {
// var appDoc = new AppDocumentNo();
// using (var context = new PYMFEEEntities())
// {
// //disable detection of changes to improve performance
// context.Configuration.AutoDetectChangesEnabled = false;
// appDoc = context.AppDocumentNoes.Where(m => m.ProcessCode == processCode && m.Year == yearSel).FirstOrDefault();
// if (appDoc == null)
// {
// throw new Exception(String.Format(ValidatorMessage.cannot_found_processcode, processCode, yearSel.ToString()));
// }
// }
// using (var context = new PYMFEEEntities())
// {
// appDoc.LastRunningNo += 1;
// context.Entry(appDoc).State = System.Data.Entity.EntityState.Modified;
// //context.AppDocumentNoes.Attach(appDoc);
// //then perform the update
// context.SaveChanges();
// }
// if (onlyNumber)
// {
// if (appDoc != null)
// {
// var runningNo = appDoc.LastRunningNo ?? 1;
// var runningNoTxt = runningNo.ToString();
// documentNo = runningNoTxt.PadLeft(appDoc.RunningNoDigit ?? 0, '0');
// }
// }
// else
// {
// //Get Year Generate
// var yearStr = (yearSel + 543).ToString();
// var year2digi = yearStr.Substring(2, 2);
// //Return Current Document Number
// documentNo = appDoc.LastRunningNo.ToString();
// var numberFormat = new String('0', appDoc.RunningNoDigit ?? 0);
// documentNo = appDoc.DocumentNoPrefix + year2digi + (Convert.ToInt32(documentNo)).ToString(numberFormat);
// }
// }
// catch (Exception ex)
// {
// throw ex;
// }
// return documentNo;
//}
public static ValidationWithReturnResult <DateTime> ConvertStringToDateTime(string text)
{
ValidationWithReturnResult <DateTime> result = new ValidationWithReturnResult <DateTime>();
try
{
//Remove all comma
string[] strDate = text.Split('/');
if (strDate[0].Trim().Length == 1)
{
strDate[0] = "0" + strDate[0];
}
if (strDate[1].Trim().Length == 1)
{
strDate[1] = "0" + strDate[1];
}
string dateInput = string.Concat(strDate[2].Trim(), '-', strDate[1].Trim(), '-', strDate[0].Trim());
result.ReturnResult = DateTime.ParseExact(dateInput, "yyyy-MM-dd",
System.Globalization.CultureInfo.CreateSpecificCulture("en-EN"));
//DateTime.ParseExact("2009-05-08 14:40:52,531", "yyyy-MM-dd HH:mm:ss,fff",
// System.Globalization.CultureInfo.InvariantCulture);
}
catch (Exception ex)
{
result.ErrorFlag = true;
result.Message = ex.Message;
AppLogService.Log(ex.ToString() + "/" + text, "");
}
return(result);
}
public static ValidationWithReturnResult <short> ConvertStringToShort(string text)
{
ValidationWithReturnResult <short> result = new ValidationWithReturnResult <short>();
try
{
//Remove all comma
text = text.Replace(",", "").Trim();
result.ReturnResult = short.Parse(text);
}
catch (Exception ex)
{
result.ErrorFlag = true;
result.Message = ex.Message;
AppLogService.Log(ex.ToString() + "/" + text, "");
}
return(result);
}
public static ValidationWithReturnResult <DataSet> ConvertExcelToDataSet1(string filePath, string fileName, string sheet)
{
ValidationWithReturnResult <DataSet> result = new ValidationWithReturnResult <DataSet>();
DataSet ds = new DataSet();
try
{
string fullPath = Path.Combine(filePath, fileName);
string[] sSheetNameList;
string sConnection;
DataTable dtTablesList;
OleDbCommand oleExcelCommand;
OleDbConnection oleExcelConnection;
sConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullPath + @";Extended Properties=""Excel 12.0;HDR=No;IMEX=1;""";
/////
string fileExtension = System.IO.Path.GetExtension(fileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = fullPath;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return(null);
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
string query = string.Format("SELECT * FROM [{0}]", excelSheets[0]);
////////////////////////////////////////////TEST///////////////////////////////////////////////////////////////////////////////////
//string query = string.Format("SELECT * INTO [FSM].[temp_DFS_Akustik] FROM [{0}]", excelSheets[0]);
////////////////////////////////////////////TEST///////////////////////////////////////////////////////////////////////////////////
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
////////////////////////////////////////////TEST///////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////TEST///////////////////////////////////////////////////////////////////////////////////
}
}
if (fileExtension.ToString().ToLower().Equals(".xml"))
{
string fileLocation = fullPath;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
//Request.Files["FileUpload"].SaveAs(fileLocation);
XmlTextReader xmlreader = new XmlTextReader(fileLocation);
// DataSet ds = new DataSet();
ds.ReadXml(xmlreader);
xmlreader.Close();
}
//for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
//{
// string conn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
// SqlConnection con = new SqlConnection(conn);
// string query = "INSERT INTO [fanselect_man].[FSM].[DFS_Akustik](MessID,KL_ID,MP_ID,LwLin50ss,LwLin63ss,LwLin80ss,LwLin100ss,LwLin125ss,LwLin160ss,LwLin200ss,LwLin250ss,LwLin315ss,LwLin400ss,LwLin500ss,LwLin630ss,LwLin800ss,LwLin1000ss,LwLin1250ss,LwLin1600ss,LwLin2000ss,LwLin2500ss,LwLin3150ss,LwLin4000ss,LwLin5000ss,LwLin6300ss,LwLin8000ss,LwLin10000ss,LwLin12500ss,LwLin16000ss,LwLin20000ss,LwLin50ds,LwLin63ds,LwLin80ds,LwLin100ds,LwLin125ds,LwLin160ds,LwLin200ds,LwLin250ds,LwLin315ds,LwLin400ds,LwLin500ds,LwLin630ds,LwLin800ds,LwLin1000ds,LwLin1250ds,LwLin1600ds,LwLin2000ds,LwLin2500ds,LwLin3150ds,LwLin4000ds,LwLin5000ds,LwLin6300ds,LwLin8000ds,LwLin10000ds,LwLin12500ds,LwLin16000ds,LwLin20000ds) VALUES ('" + ds.Tables[0].Rows[i][0].ToString() + "', '" + ds.Tables[0].Rows[i][1].ToString() + "', '" + ds.Tables[0].Rows[i][2].ToString() + "', '" + ds.Tables[0].Rows[i][3].ToString() + "', '" + ds.Tables[0].Rows[i][4].ToString() + "', '" + ds.Tables[0].Rows[i][5].ToString() + "', '" + ds.Tables[0].Rows[i][6].ToString() + "', '" + ds.Tables[0].Rows[i][7].ToString() + "', '" + ds.Tables[0].Rows[i][8].ToString() + "', '" + ds.Tables[0].Rows[i][9].ToString() + "', '" + ds.Tables[0].Rows[i][10].ToString() + "', '" + ds.Tables[0].Rows[i][11].ToString() + "', '" + ds.Tables[0].Rows[i][12].ToString() + "', '" + ds.Tables[0].Rows[i][13].ToString() + "', '" + ds.Tables[0].Rows[i][14].ToString() + "', '" + ds.Tables[0].Rows[i][15].ToString() + "', '" + ds.Tables[0].Rows[i][16].ToString() + "', '" + ds.Tables[0].Rows[i][17].ToString() + "', '" + ds.Tables[0].Rows[i][18].ToString() + "', '" + ds.Tables[0].Rows[i][19].ToString() + "', '" + ds.Tables[0].Rows[i][20].ToString() + "', '" + ds.Tables[0].Rows[i][21].ToString() + "', '" + ds.Tables[0].Rows[i][22].ToString() + "', '" + ds.Tables[0].Rows[i][23].ToString() + "', '" + ds.Tables[0].Rows[i][24].ToString() + "', '" + ds.Tables[0].Rows[i][25].ToString() + "', '" + ds.Tables[0].Rows[i][26].ToString() + "', '" + ds.Tables[0].Rows[i][27].ToString() + "', '" + ds.Tables[0].Rows[i][28].ToString() + "', '" + ds.Tables[0].Rows[i][29].ToString() + "', '" + ds.Tables[0].Rows[i][30].ToString() + "', '" + ds.Tables[0].Rows[i][31].ToString() + "', '" + ds.Tables[0].Rows[i][32].ToString() + "', '" + ds.Tables[0].Rows[i][33].ToString() + "', '" + ds.Tables[0].Rows[i][34].ToString() + "', '" + ds.Tables[0].Rows[i][35].ToString() + "', '" + ds.Tables[0].Rows[i][36].ToString() + "', '" + ds.Tables[0].Rows[i][37].ToString() + "', '" + ds.Tables[0].Rows[i][38].ToString() + "', '" + ds.Tables[0].Rows[i][39].ToString() + "', '" + ds.Tables[0].Rows[i][40].ToString() + "', '" + ds.Tables[0].Rows[i][41].ToString() + "', '" + ds.Tables[0].Rows[i][42].ToString() + "', '" + ds.Tables[0].Rows[i][43].ToString() + "', '" + ds.Tables[0].Rows[i][44].ToString() + "', '" + ds.Tables[0].Rows[i][45].ToString() + "', '" + ds.Tables[0].Rows[i][46].ToString() + "', '" + ds.Tables[0].Rows[i][47].ToString() + "', '" + ds.Tables[0].Rows[i][48].ToString() + "', '" + ds.Tables[0].Rows[i][49].ToString() + "', '" + ds.Tables[0].Rows[i][50].ToString() + "', '" + ds.Tables[0].Rows[i][51].ToString() + "', '" + ds.Tables[0].Rows[i][52].ToString() + "', '" + ds.Tables[0].Rows[i][53].ToString() + "', '" + ds.Tables[0].Rows[i][54].ToString() + "', '" + ds.Tables[0].Rows[i][55].ToString() + "', '" + ds.Tables[0].Rows[i][56].ToString() + "')";
// con.Open();
// SqlCommand cmd = new SqlCommand(query, con);
// cmd.ExecuteNonQuery();
// con.Close();
//}
/////
oleExcelConnection = new OleDbConnection(sConnection);
oleExcelConnection.Open();
dtTablesList = oleExcelConnection.GetSchema("Tables");
List <string> listSheet = new List <string>();
foreach (DataRow drSheet in dtTablesList.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
{
listSheet.Add(drSheet["TABLE_NAME"].ToString());
}
}
if (listSheet.Any())
{
sheet = "[" + listSheet[0] + "]";
}
else
{
sheet = "[" + sheet + "$]";
}
if (dtTablesList.Rows.Count > 0)
{
sSheetNameList = new string[dtTablesList.Rows.Count];
oleExcelCommand = new OleDbCommand(("Select * From " + sheet), oleExcelConnection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(oleExcelCommand);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
ds.Tables.Add(dt);
}
oleExcelConnection.Close();
dtTablesList.Clear();
dtTablesList.Dispose();
result.ReturnResult = ds;
}
catch (Exception ex)
{
result.ErrorFlag = true;
result.Message = ex.Message;
AppLogService.Log(ex.ToString(), "");
}
return(result);
}
public static ValidationWithReturnResult <DataSet> ConvertExcelToDataSet(string filePath, string fileName, string sheet)
{
ValidationWithReturnResult <DataSet> result = new ValidationWithReturnResult <DataSet>();
DataSet ds = new DataSet();
try
{
string fullPath = Path.Combine(filePath, fileName);
string[] sSheetNameList;
string sConnection;
DataTable dtTablesList;
OleDbCommand oleExcelCommand;
OleDbConnection oleExcelConnection;
sConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullPath + @";Extended Properties=""Excel 12.0;HDR=No;IMEX=1;""";
oleExcelConnection = new OleDbConnection(sConnection);
oleExcelConnection.Open();
dtTablesList = oleExcelConnection.GetSchema("Tables");
List <string> listSheet = new List <string>();
foreach (DataRow drSheet in dtTablesList.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
{
listSheet.Add(drSheet["TABLE_NAME"].ToString());
}
}
if (listSheet.Any())
{
sheet = "[" + listSheet[0] + "]";
}
else
{
sheet = "[" + sheet + "$]";
}
if (dtTablesList.Rows.Count > 0)
{
sSheetNameList = new string[dtTablesList.Rows.Count];
oleExcelCommand = new OleDbCommand(("Select * From " + sheet), oleExcelConnection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(oleExcelCommand);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
ds.Tables.Add(dt);
}
oleExcelConnection.Close();
dtTablesList.Clear();
dtTablesList.Dispose();
result.ReturnResult = ds;
}
catch (Exception ex)
{
result.ErrorFlag = true;
result.Message = ex.Message;
AppLogService.Log(ex.ToString(), "");
}
return(result);
}
//[ActionName("Index")]
public ActionResult Index_Post(string fileName, string sheetName)
{
//Fill dataset with records
AdjustRefundUploadService service = new AdjustRefundUploadService();
string tempFilePath = ConfigurationManager.AppSettings["TempFilePath"];
tempFilePath = System.Web.HttpContext.Current.Server.MapPath(tempFilePath);
//DataSet excelDS = ExcelService.ConvertExcelToDataSet(tempFilePath, fileName, 1);
ValidationWithReturnResult <DataSet> ds = new ValidationWithReturnResult <DataSet>();
ds = service.ConvertExcelToDataSet(tempFilePath, fileName, sheetName);
DataSet dataSet = new DataSet();
dataSet = ds.ReturnResult;
//DataSet dataSet = GetRecordsFromDatabase();
StringBuilder sb = new StringBuilder();
sb.Append("<table>");
//LINQ to get Column names
var columnName = dataSet.Tables[0].Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
sb.Append("<tr>");
//Looping through the column names
foreach (var col in columnName)
{
sb.Append("<td>" + col + "</td>");
}
sb.Append("</tr>");
//Looping through the records
foreach (DataRow dr in dataSet.Tables[0].Rows)
{
sb.Append("<tr>");
foreach (DataColumn dc in dataSet.Tables[0].Columns)
{
sb.Append("<td>" + dr[dc] + "</td>");
}
sb.Append("</tr>");
}
sb.Append("</table>");
//Writing StringBuilder content to an excel file.
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.Charset = "";
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment;filename=UserReport.xls");
Response.Write(sb.ToString());
Response.Flush();
Response.Close();
AdjustrefundUploadViewModel model = new AdjustrefundUploadViewModel();
return(View("~/Views/Adjustrefund/AdjustrefundUploadDetail.cshtml", model));
}
