public List <List <object>[]> ReadFileJvnlp(StreamReader fileMemoryStream, string fileName)
{
IExcelDataReader bookExcel = null;
// register provide encoding 1252 to Excel
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
try
{
//Reading from a binary Excel file ('97-2003 format; *.xls)
if (fileName.Split(".").LastOrDefault().Equals("xls"))
{
bookExcel = ExcelReaderFactory.CreateBinaryReader(fileMemoryStream.BaseStream);
}
//Reading from a OpenXml Excel file (2007 format; *.xlsx)
if (fileName.Split(".").LastOrDefault().Equals("xlsx"))
{
bookExcel = ExcelReaderFactory.CreateOpenXmlReader(fileMemoryStream.BaseStream);
}
//DataSet - The result of each spreadsheet will be created in the result.Tables
var tableJvnlp = bookExcel?.AsDataSet();
CreateTablesJvnlpAsync(tableJvnlp);
if (Drugs == null || RemovedDrugs == null)
{
return(new List <List <object>[]>());
}
var drugs = Drugs.Rows.Cast <DataRow>().Select(x => x.ItemArray.ToList()).ToArray();
var rmDrugs = RemovedDrugs.Rows.Cast <DataRow>().Select(x => x.ItemArray.ToList()).ToArray();
RemoveNullColumns(drugs, rmDrugs);
bookExcel?.Close();
fileMemoryStream.Close();
LoadDateUpdate();
return(new List <List <object>[]>
{
drugs,
rmDrugs
});
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
protected virtual void DisposeInternal()
{
try
{
_reader?.Close();
_reader?.Dispose();
_reader = null;
_schemaTable = null;
_columns = null;
_columnIndexes = null;
_totalRows = 0;
}
catch
{
}
}
public IHttpActionResult UploadFile()
{
ResponseModel response = new ResponseModel();
#region Save the Uploaded document
string accessToken = Request.Headers.Authorization.Parameter;
if (_googleServiceNew.CheckTokenExpired(accessToken))
{
response = _googleServiceNew.GetNewAccessToken(accessToken);
accessToken = response.AccessToken;
}
response.IsSuccess = true;
var httpRequest = System.Web.HttpContext.Current.Request;
var emailSubject = httpRequest.Form["emailSubject"];
var emailFrom = httpRequest.Form["emailFrom"];
var emailBody = httpRequest.Unvalidated.Form.Get("emailBody");
if (emailBody.Contains("<s"))
{
if (emailBody.Contains("<sc") && emailBody.Contains("t>"))
{
response.IsSuccess = false;
response.Message = "<script> tags are not allowed. Please remove <Script> tags from Email Body.";
response.Content = "";
response.AccessToken = accessToken;
return(Ok(response));
}
}
if (httpRequest.Files.Count > 0)
{
var docfiles = new List <string>();
foreach (string file in httpRequest.Files)
{
var postedFile = httpRequest.Files[file];
var yourFileName = postedFile.FileName;
string[] primePath = yourFileName.Split('.');
yourFileName = primePath[0].ToString() + DateTime.Now.ToString("yyyyMMddHHmmssfff") + "." + primePath[1];
string targetFolder = HttpContext.Current.Server.MapPath("~/FileUpload");
string targetPath = Path.Combine(targetFolder, yourFileName);
postedFile.SaveAs(targetPath);
//var filePath = HttpContext.Current.Server.MapPath("~/" + postedFile.FileName);
var filePath = HttpContext.Current.Server.MapPath("FileUpload") + "\\" + postedFile.FileName;
var Url = "http://lead411chromeex.azurewebsites.net/FileUpload/";
//var Url = "http://stagingwin.com/lead411/FileUpload/";
var fileName = Url + postedFile.FileName;
Stream stream = postedFile.InputStream;
IExcelDataReader reader = null;
DataSet res = new DataSet();
if (postedFile.FileName.EndsWith(".xls"))
{
//reads the excel file with .xls extension
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (postedFile.FileName.EndsWith(".xlsx"))
{
//reads excel file with .xlsx extension
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (postedFile.FileName.EndsWith(".csv"))
{
string[] allLines = File.ReadAllLines(@targetPath);
DataSet ds = new DataSet();
var tbl = new DataTable();
DataColumn column;
DataRow row;
//
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Employee Id";
tbl.Columns.Add(column);
//
column = new DataColumn();
column.DataType = Type.GetType("System.String");
column.ColumnName = "Email";
tbl.Columns.Add(column);
//
column = new DataColumn();
column.DataType = Type.GetType("System.String");
column.ColumnName = "Firstname";
tbl.Columns.Add(column);
for (int i = 1; i < allLines.Count(); i++)
{
string aline = allLines[i].ToString();
string[] data = aline.Split(',');
row = tbl.NewRow();
row["Employee Id"] = data[0];
row["Email"] = data[1];
row["Firstname"] = data[2];
tbl.Rows.Add(row);
}
ds.Tables.Add(tbl);
res = ds;
//rer
}
else
{
//Shows error if uploaded file is not Excel file
return(BadRequest("This file format is not supported"));
}
if ((postedFile.FileName.EndsWith(".xlsx")) || (postedFile.FileName.EndsWith(".xls")))
{
//treats the first row of excel file as Coluymn Names
reader.IsFirstRowAsColumnNames = true;
//Adding reader data to DataSet()
res = reader.AsDataSet();
reader.Close();
}
fileName = postedFile.FileName;
var filepath = Url + yourFileName;
#endregion
#region Code to save in Local DB
response = Task.Run(() => _iCommonService.PushMailInDB(emailSubject, emailBody, res, fileName, emailFrom, filepath)).Result;
#endregion
response.AccessToken = accessToken;
return(Ok(response));
}
}
else
{
return(BadRequest("No file Selected"));
}
response.AccessToken = accessToken;
return(Ok(response));
}
public ActionResult ReadFile(int EmployeeID, string filePath, string filename)
{
var lstDocument = new List <Document>();
// DOCUMENT HEADER
string strHEADER_SORTCENTER = string.Empty;
string strHEADER_REC_DATEFROM = string.Empty;
string strHEADER_PREPAREDBY = string.Empty;
//PRODUCTIVITY
//**100n**////**201u**////**300u**////**400u**//
double _Productivity_100n = 0;
double _Productivity_201u = 0;
double _Productivity_300u = 0;
double _Productivity_400u = 0;
// UR/SC RATIO
double _URSC_BL_Pallet = 0;
double _URSC_UR_Pallet = 0;
double _URSC_SC_Pallet = 0;
double _URSC_BL_Composite = 0;
double _URSC_UR_Composite = 0;
double _URSC_SC_Composite = 0;
double _URSC_BL_HollowProfile = 0;
double _URSC_UR_HollowProfile = 0;
double _URSC_SC_HollowProfile = 0;
double _URSC_BL_TopFrames = 0;
double _URSC_UR_TopFrames = 0;
double _URSC_SC_TopFrames = 0;
//TRUCK MOVEMENT RATIO
double _Truck_Movement_IN = 0;
double _Truck_Movement_OUT = 0;
double _Truck_Movement_Ratio = 0;
//PACKED ITEM RECEIVED
//**100n**////**201u**////**300u**////**400u**//
double _PackedItemsRec_100n_Pallet_TotalRec = 0;
double _PackedItemsRec_201u_Composite_TotalRec = 0;
double _PackedItemsRec_300u_BlackHollowProfile_TotalRec = 0;
double _PackedItemsRec_400u_TopFrames_TotalRec = 0;
//PACKED ITEM DISPATCHED
//**100n**////**201u**////**300u**////**400u**//
double _PackedItemsDisp_100n_Pallet_TotalDisp = 0;
double _PackedItemsDisp_201u_Composite_TotalDisp = 0;
double _PackedItemsDisp_300u_BlackHollowProfile_TotalDisp = 0;
double _PackedItemsDisp_400u_TopFrames_TotalDisp = 0;
//STOCK ON HAND - QI
string strStock_QI_Pallet = string.Empty;
string strStock_QI_Composite = string.Empty;
string strStock_QI_Hprofile300 = string.Empty;
string strStock_QI_Hprofile301 = string.Empty;
string strStock_QI_Smartpads = string.Empty;
string strStock_QI_TopFrames = string.Empty;
//STOCK ON HAND - UR
string strStock_UR_Pallet = string.Empty;
string strStock_UR_Composite = string.Empty;
string strStock_UR_Hprofile300 = string.Empty;
string strStock_UR_Hprofile301 = string.Empty;
string strStock_UR_Smartpads = string.Empty;
string strStock_UR_TopFrames = string.Empty;
//STOCK ON HAND - BL
string strStock_BL_Pallet = string.Empty;
string strStock_BL_Composite = string.Empty;
string strStock_BL_Hprofile300 = string.Empty;
string strStock_BL_Hprofile301 = string.Empty;
string strStock_BL_Smartpads = string.Empty;
string strStock_BL_TopFrames = string.Empty;
var stream = System.IO.File.Open(filePath, FileMode.Open, FileAccess.Read);
//Reading from a OpenXml Excel file (2007 format; *.xlsx)
// FileStream stream = new FileStream("../../myxlsx/sample.xlsx", FileMode.Open);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//Data Reader methods
foreach (DataTable table in result.Tables)
{
if (table.TableName == "Weekly Report")
{
//HEADER
strHEADER_SORTCENTER = table.Rows[5][2].ToString();
strHEADER_REC_DATEFROM = table.Rows[6][2].ToString();
strHEADER_PREPAREDBY = table.Rows[7][2].ToString();
// PRODUCTIVITY
_Productivity_100n = Math.Round(Convert.ToDouble(table.Rows[77][2]), 0);
_Productivity_201u = Math.Round(Convert.ToDouble(table.Rows[77][3]), 0);
_Productivity_300u = Math.Round(Convert.ToDouble(table.Rows[77][4]), 0);
_Productivity_400u = Math.Round(Convert.ToDouble(table.Rows[77][5]), 0);
// UR/SC RATIO
_URSC_BL_Pallet = Math.Round(Convert.ToDouble(table.Rows[69][2]) * 100, 1);
_URSC_UR_Pallet = Math.Round(Convert.ToDouble(table.Rows[70][2]) * 100, 1);
_URSC_SC_Pallet = Math.Round(Convert.ToDouble(table.Rows[71][2]) * 100, 1);
_URSC_BL_Composite = Math.Round(Convert.ToDouble(table.Rows[69][3]) * 100, 1);
_URSC_UR_Composite = Math.Round(Convert.ToDouble(table.Rows[70][3]) * 100, 1);
_URSC_SC_Composite = Math.Round(Convert.ToDouble(table.Rows[71][3]) * 100, 1);
_URSC_BL_HollowProfile = Math.Round(Convert.ToDouble(table.Rows[69][4]) * 100, 1);
_URSC_UR_HollowProfile = Math.Round(Convert.ToDouble(table.Rows[70][4]) * 100, 1);
_URSC_SC_HollowProfile = Math.Round(Convert.ToDouble(table.Rows[71][4]) * 100, 1);
_URSC_BL_TopFrames = Math.Round(Convert.ToDouble(table.Rows[69][5]) * 100, 1);
_URSC_UR_TopFrames = Math.Round(Convert.ToDouble(table.Rows[70][5]) * 100, 1);
_URSC_SC_TopFrames = Math.Round(Convert.ToDouble(table.Rows[71][5]) * 100, 1);
//PACKED ITEM RECEIVED
//**100n**////**201u**////**300u**////**400u**//
_PackedItemsRec_100n_Pallet_TotalRec = (double)table.Rows[16][2];
_PackedItemsRec_201u_Composite_TotalRec = (double)table.Rows[16][3];
_PackedItemsRec_300u_BlackHollowProfile_TotalRec = (double)table.Rows[16][4];
_PackedItemsRec_400u_TopFrames_TotalRec = (double)table.Rows[16][5];
//PACKED ITEM DISPATCHED
//**100n**////**201u**////**300u**////**400u**//
_PackedItemsDisp_100n_Pallet_TotalDisp = (double)table.Rows[20][2];
_PackedItemsDisp_201u_Composite_TotalDisp = (double)table.Rows[20][3];
_PackedItemsDisp_300u_BlackHollowProfile_TotalDisp = (double)table.Rows[20][4];
_PackedItemsDisp_400u_TopFrames_TotalDisp = (double)table.Rows[20][5];
}
}
//Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
//MAPPING FROM EXCEL TO MODEL ENTITY
var document = new Document()
{
Filename = filename,
FilePath = "~\\UploadedFiles\\",
SortCenter = strHEADER_SORTCENTER,
ReceivedDateFrom = Convert.ToDateTime(strHEADER_REC_DATEFROM),
PreparedBy = strHEADER_PREPAREDBY,
DateUploaded = DateTime.Now,
EmployeeId = EmployeeID,
//PRODUCTIVITY
Productivity_100n = _Productivity_100n,
Productivity_201u = _Productivity_201u,
Productivity_300u = _Productivity_300u,
Productivity_400u = _Productivity_400u,
//UR SC RATIO
URSC_BL_Pallet = _URSC_BL_Pallet,
URSC_BL_Composite = _URSC_BL_Composite,
URSC_BL_HollowProfile = _URSC_BL_HollowProfile,
URSC_BL_TopFrames = _URSC_BL_TopFrames,
URSC_UR_Pallet = _URSC_UR_Pallet,
URSC_UR_Composite = _URSC_UR_Composite,
URSC_UR_HollowProfile = _URSC_UR_HollowProfile,
URSC_UR_TopFrames = _URSC_UR_TopFrames,
URSC_SC_Pallet = _URSC_SC_Pallet,
URSC_SC_Composite = _URSC_SC_Composite,
URSC_SC_HollowProfile = _URSC_SC_HollowProfile,
URSC_SC_TopFrames = _URSC_SC_TopFrames,
//TRUCK MOVEMENT RATIO
Truck_Movement_IN = _Truck_Movement_IN,
Truck_Movement_OUT = _Truck_Movement_OUT,
Truck_Movement_Ratio = _Truck_Movement_Ratio,
//PACKED ITEM RECEIVED
PackedItemsRec_100n_Pallet_TotalRec = _PackedItemsRec_100n_Pallet_TotalRec,
PackedItemsRec_201u_Composite_TotalRec = _PackedItemsRec_201u_Composite_TotalRec,
PackedItemsRec_300u_BlackHollowProfile_TotalRec = _PackedItemsRec_300u_BlackHollowProfile_TotalRec,
PackedItemsRec_400u_TopFrames_TotalRec = _PackedItemsRec_400u_TopFrames_TotalRec,
//PACKED ITEM DISPATCHED
PackedItemsDisp_100n_Pallet_TotalDisp = _PackedItemsDisp_100n_Pallet_TotalDisp,
PackedItemsDisp_201u_Composite_TotalDisp = _PackedItemsDisp_201u_Composite_TotalDisp,
PackedItemsDisp_300u_BlackHollowProfile_TotalDisp = _PackedItemsDisp_300u_BlackHollowProfile_TotalDisp,
PackedItemsDisp_400u_TopFrames_TotalDisp = _PackedItemsDisp_400u_TopFrames_TotalDisp,
ObjectState = ObjectState.Added,
};
_documentRepositoryAsync.GetRepository <Document>().Insert(document);
_unitOfWorkAsync.SaveChanges();
return(RedirectToAction("UploadForm", "Document"));
}
// Add some programmatically-generated objects to the data store
// Can write one method, or many methods - your decision
// The important idea is that you check for existing data first
// Call this method from a controller action/method
public bool LoadData()
{
// User name
var user = HttpContext.Current.User.Identity.Name;
// Monitor the progress
bool done = false;
// ############################################################
// RoleClaim
if (ds.RoleClaims.Count() == 0)
{
// Add role claims
//ds.SaveChanges();
//done = true;
}
// ############################################################
// Player
// Attention - 4 - Method to load data from the XLSX file, with ExcelDataReader add-on
if (ds.Players.Count() == 0)
{
// Add players
// Path to the XLSX file
var path = HttpContext.Current.Server.MapPath("~/App_Data/NFLPlayoffTeams.xlsx");
// Load the workbook into a System.Data.DataSet "sourceData"
var stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
DataSet sourceData = reader.AsDataSet();
reader.Close();
// At this point in time, sourceData holds ALL the data from the XLSX in memory
// Worksheet name
string worksheetName;
// Load the first worksheet...
// ===========================
// Get worksheet by its name
worksheetName = "DEN";
var worksheet = sourceData.Tables[worksheetName];
// Convert it to a collection of the desired type
List <PlayerAdd> items = worksheet.DataTableToList <PlayerAdd>();
// Go through the collection, and add the items to the data context
foreach (var item in items)
{
// Fill in the team name
item.Team = worksheetName;
ds.Players.Add(mapper.Map <Player>(item));
}
// Save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
// Get worksheet by its name
worksheetName = "CAR";
worksheet = sourceData.Tables[worksheetName];
// Convert it to a collection of the desired type
items = worksheet.DataTableToList <PlayerAdd>();
// Go through the collection, and add the items to the data context
foreach (var item in items)
{
item.Team = worksheetName;
ds.Players.Add(mapper.Map <Player>(item));
}
// Save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
// Get worksheet by its name
worksheetName = "NE";
worksheet = sourceData.Tables[worksheetName];
// Convert it to a collection of the desired type
items = worksheet.DataTableToList <PlayerAdd>();
// Go through the collection, and add the items to the data context
foreach (var item in items)
{
item.Team = worksheetName;
ds.Players.Add(mapper.Map <Player>(item));
}
// Save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
// Get worksheet by its name
worksheetName = "ARI";
worksheet = sourceData.Tables[worksheetName];
// Convert it to a collection of the desired type
items = worksheet.DataTableToList <PlayerAdd>();
// Go through the collection, and add the items to the data context
foreach (var item in items)
{
item.Team = worksheetName;
ds.Players.Add(mapper.Map <Player>(item));
}
// Save changes
ds.SaveChanges();
done = true;
}
return(done);
}
protected void btnSubir_Click(object sender, EventArgs e)
{
if (!CargaArchivo.HasFile)
{
MostrarExcepcion(Constantes.MENSAJE_CAMPO_REQUERIDO_TITULO, "Es requerido subir un archivo");
return;
}
String ext = System.IO.Path.GetExtension(CargaArchivo.PostedFile.FileName);
log.Info(String.Format("El usuario: [{0}] ha cargado el archivo de excel [{1}] para su insercion masiva", UsuarioActual.Nombre, CargaArchivo.PostedFile.FileName));
if (!ext.ToLower().Equals(Constantes.EXTENSION_EXCEL_97) && !ext.ToLower().Equals(Constantes.EXTENSION_EXCEL_2007))
{
MostrarExcepcion(Constantes.MENSAJE_CAMPO_REQUERIDO_TITULO, "Solo son permitidos los archivos de Excel");
return;
}
CajaRegistro.Items.Clear();
CajaRegistro.Items.Add(NuevoListItem("Iniciando Proceso", Constantes.TEXTO_BLANCO));
try
{
IExcelDataReader excelReader = null;
if (ext.ToLower().Equals(Constantes.EXTENSION_EXCEL_2007))
{
CajaRegistro.Items.Add(NuevoListItem("Leyendo Archivo de Excel XLSX 2007", Constantes.TEXTO_BLANCO));
excelReader = ExcelReaderFactory.CreateOpenXmlReader(CargaArchivo.PostedFile.InputStream);
}
else
{
if (ext.ToLower().Equals(Constantes.EXTENSION_EXCEL_97))
{
CajaRegistro.Items.Add(NuevoListItem("Leyendo Archivo de Excel XLS 97-2003", Constantes.TEXTO_BLANCO));
excelReader = ExcelReaderFactory.CreateBinaryReader(CargaArchivo.PostedFile.InputStream);
}
}
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
int i = 0;
while (excelReader.Read())
{
i++;
String nomina = excelReader.GetString(0);
String time = excelReader.GetString(1);
String deviceid = excelReader.GetString(2);
String status = excelReader.GetString(3);
int numeroNumina = 0;
Boolean encontroError = false;
DateTime fechaInicidencia;
if (i == 1)
{
encontroError = true;
}
if (!Int32.TryParse(nomina, out numeroNumina) && !encontroError)
{
numeroNumina = 0;
CajaRegistro.Items.Add(NuevoListItem(String.Format("ERROR FILA: [{0}], la columna de Nomina no es numerica", i), Constantes.TEXTO_BLANCO));
encontroError = true;
}
if (numeroNumina <= 0 && !encontroError)
{
CajaRegistro.Items.Add(NuevoListItem(String.Format("ERROR FILA: [{0}], la columna carece de numero nomina", i), Constantes.TEXTO_BLANCO));
encontroError = true;
}
Empleado emp = encontroError ? null : empleadoDao.GetByNumeroEmpleado(Int32.Parse(nomina));
if (emp == null && !encontroError)
{
CajaRegistro.Items.Add(NuevoListItem(String.Format("ERROR FILA: [{0}], este empleado no existe en la base de datos", i), Constantes.TEXTO_BLANCO));
encontroError = true;
}
ControlAcceso ctrl = encontroError ? null : controlAccesoDao.GetByIdControl(Int32.Parse(deviceid));
if (ctrl == null && !encontroError)
{
CajaRegistro.Items.Add(NuevoListItem(String.Format("ERROR FILA: [{0}], este control de acceso no existe en la base de datos", i), Constantes.TEXTO_BLANCO));
encontroError = true;
}
if (!DateTime.TryParse(time, out fechaInicidencia) && !encontroError)
{
CajaRegistro.Items.Add(NuevoListItem(String.Format("ERROR FILA: [{0}], esta fecha no puede ser interpretada correctamente", i), Constantes.TEXTO_BLANCO));
encontroError = true;
}
if (!encontroError)
{
try
{
Incidencia obj = incidenciaDao.GetByEmpleadoFecha(emp, fechaInicidencia);
if (obj != null)
{
CajaRegistro.Items.Add(NuevoListItem(String.Format("ADV FILA: [{0}], esta incidencia ya existe, se actualizara el registro", i), Constantes.TEXTO_BLANCO));
}
if (obj == null)
{
obj = new Incidencia();
}
obj.ControlAcceso = ctrl;
obj.Empleado = emp;
obj.EnviadoWs = 0;
obj.FechaAlta = DateTime.Now;
obj.FechaHoraIncidencia = fechaInicidencia;
obj.InOutMode = Int32.Parse(status);
incidenciaDao.SaveOrUpdate(obj);
CajaRegistro.Items.Add(NuevoListItem(String.Format("OK FILA: [{0}], registro guardado correctamente", i), Constantes.TEXTO_BLANCO));
}
catch (Exception ex)
{
log.Error(String.Format("Error al momento de intentar insertar una incidencia importada de Excel. Mensaje: [{0}]", ex.Message));
CajaRegistro.Items.Add(NuevoListItem(String.Format("ERROR FILA: [{0}], error en el proceso. Mensaje: [{1}]", i, ex.Message), Constantes.TEXTO_BLANCO));
}
}
}
CajaRegistro.Items.Add(NuevoListItem(String.Format("PROCESO OK: Se completaron: [{0}] registros", i), Constantes.TEXTO_BLANCO));
excelReader.Close();
}
catch (Exception ex)
{
CajaRegistro.Items.Add(NuevoListItem(String.Format("Error en el proceso. Detalles: [{0}] ", ex.Message), Constantes.TEXTO_BLANCO));
log.Error(String.Format("Error al momento de intentar importar la hoja de excel de Asistencia. Mensaje [{0}]", ex.Message));
ManejarExcepcion(ex);
}
}
static void Main(string[] args)
{
FileStream stream = File.Open(args[0], FileMode.Open, FileAccess.Read);
//Choose one of either 1 or 2
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
// # IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//Choose one of either 3, 4, or 5
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
//# DataSet result = excelReader.AsDataSet();
//4. DataSet - Create column names from first row
// works fine
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
DataTable TOCTable = result.Tables["azure-content-pr"];
MakeTreeViewItemTree(result, TOCTable);
TOC toc = new TOC();
/*
* foreach (DataColumn column in TOCTable.Columns)
* {
* //Console.WriteLine("{0}, type: {1}", column.ColumnName, column.DataType);
* /*
* ID, type: System.String
* TOC Files, type: System.Double
* File (H1 heading), type: System.String
* Title, type: System.String
* Service, type: System.String
* Service Slug, type: System.String
* Author, type: System.String
* Pillar, type: System.String
* Directory (In case you have multiple TOC), type: System.String
* Top Node, type: System.String
* Node 2 TOC, type: System.String
* Node 3 TOC, type: System.String
* Comments/Notes, type: System.String
*/
/*
* }
*/
foreach (DataRow row in TOCTable.Rows)
{
if (row.Field <System.String>("Service") != "App Service")
{
continue;
}
// ID = row.Field<System.String>("ID");
/// TODO: Console.WriteLine("{0}: {1}", row.Table.Columns["TOC Files"], row.Field<System.Double>("TOC Files"));
/* FileName = row.Field<System.String>("File (H1 heading)");
* Title = row.Field<System.String>("Title");
* Service = row.Field<System.String>("Service");
* ServiceSlug = row.Field<System.String>("Service Slug");
* Author = row.Field<System.String>("Author");
* Pillar = row.Field<System.String>("Pillar");
* Directory = row.Field<System.String>("Directory (In case you have multiple TOC)");
* RootNode = row.Field<System.String>("Top Node");
* Node2 = row.Field<System.String>("Node 2 TOC");
* Node3 = row.Field<System.String>("Node 3 TOC");
*/
StringBuilder builder = new StringBuilder();
builder.Append("# [" + row.Field <System.String>("Top Node") + "]");
if (row.Field <System.String>("Node 2 TOC") != null && !row.Field <System.String>("Node 2 TOC").Equals(String.Empty))
{
builder.Insert(0, '#');
builder.Append("[" + row.Field <System.String>("Node 2 TOC") + "]");
}
if (row.Field <System.String>("Node 3 TOC") != null && !row.Field <System.String>("Node 3 TOC").Equals(String.Empty))
{
builder.Insert(0, '#');
builder.Append("[" + row.Field <System.String>("Node 3 TOC") + "]");
}
builder.Append("[" + row.Field <System.String>("Title") + "]");
builder.Append("(" + row.Field <System.String>("File (H1 heading)") + ")");
//Console.WriteLine(builder.ToString());
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
}
}
public bool fromXLStoSQL(string filePath)
{
Logger.Debug(filePath);
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = null;
if (filePath.EndsWith("xls"))
{
Logger.Debug("Reading from a binary Excel file ('97-2003 format; *.xls)");
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (filePath.EndsWith("xlsx"))
{
Logger.Debug("Reading from a OpenXml Excel file (2007 format; *.xlsx)");
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
// wrong file extension (can't happen, but...)
stream.Close();
return(false);
}
while (excelReader != null && excelReader.Read())
{
int numColumns = excelReader.FieldCount;
// Logger.Debug("Found " + numColumns + " columns in excel file");
if (numColumns < 2 || numColumns > 3)
{
// file format not supported
excelReader.Close();
stream.Close();
return(false);
}
string [] columns = new string [numColumns];
try
{
for (int i = 0; i < numColumns; i++)
{
columns[i] = excelReader.GetString(i);
// Logger.Debug("Column " + i.ToString() + ": " + columns[i]);
}
}
catch (Exception e)
{
Logger.Fatal("Can't read xls file: " + e.ToString());
excelReader.Close();
stream.Close();
return(false);
}
string nom = null;
string nhc = null;
string referencia = null;
if (numColumns >= 3)
{
referencia = columns[0];
nhc = columns[1];
nom = columns[2];
}
else
{
nhc = columns[0];
nom = columns[1];
}
if (nom != null && nhc != null)
{
StringDictionary sql = new StringDictionary();
setSQLTableDefaults("SCAPersona", ref sql);
sql.Add("Nombre", nom);
sql.Add("Nombre1", nom);
sql.Add("NHC", nhc);
int lastInsertRowId = -1;
if (storeSQL("SCAPersona", sql, ref lastInsertRowId) == false)
{
excelReader.Close();
stream.Close();
return(false);
}
if (numColumns >= 3 && referencia != null && lastInsertRowId != -1)
{
/*
* Si el xls té tres columnes, a part d’emplenar
* la taula SCAPersona tal i com ja ho fa,
* hauries d’emplenar també la taula SCAMuestra
* on la primera columna seria la referencia de la mostra.
* Tots els altres camps de SCAMuestra els hauries de posar
* amb un valor per defecte
*/
sql.Clear();
setSQLTableDefaults("SCAMuestra", ref sql);
sql.Add("Referencia", referencia);
sql.Add("IdPersona", lastInsertRowId.ToString());
if (storeSQL("SCAMuestra", sql) == false)
{
excelReader.Close();
stream.Close();
return(false);
}
}
}
else
{
Logger.Debug("Empty fields or wrong number of them. Going to next row.");
}
}
excelReader.Close();
stream.Close();
return(true);
}
public static void DoOpenOfficeTest(IExcelDataReader excelReader)
{
Assert.IsTrue(excelReader.IsValid);
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual("column a", excelReader.GetString(0));
Assert.AreEqual(" column b", excelReader.GetString(1));
Assert.AreEqual(" column b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetString(3));
Assert.AreEqual("column e", excelReader.GetString(4));
Assert.AreEqual(" column b", excelReader.GetString(5));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(2, excelReader.GetInt32(0));
Assert.AreEqual("b", excelReader.GetString(1));
Assert.AreEqual("c", excelReader.GetString(2));
Assert.AreEqual("d", excelReader.GetString(3));
Assert.AreEqual(" e ", excelReader.GetString(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(3, excelReader.GetInt32(0));
Assert.AreEqual(2, excelReader.GetInt32(1));
Assert.AreEqual(3, excelReader.GetInt32(2));
Assert.AreEqual(4, excelReader.GetInt32(3));
Assert.AreEqual(5, excelReader.GetInt32(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(4, excelReader.GetInt32(0));
Assert.AreEqual(new DateTime(2012, 10, 13), excelReader.GetDateTime(1));
Assert.AreEqual(new DateTime(2012, 10, 14), excelReader.GetDateTime(2));
Assert.AreEqual(new DateTime(2012, 10, 15), excelReader.GetDateTime(3));
Assert.AreEqual(new DateTime(2012, 10, 16), excelReader.GetDateTime(4));
for (int i = 4; i < 34; i++)
{
excelReader.Read();
Assert.AreEqual(i + 1, excelReader.GetInt32(0));
Assert.AreEqual(i + 2, excelReader.GetInt32(1));
Assert.AreEqual(i + 3, excelReader.GetInt32(2));
Assert.AreEqual(i + 4, excelReader.GetInt32(3));
Assert.AreEqual(i + 5, excelReader.GetInt32(4));
}
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
//test dataset
DataSet result = excelReader.AsDataSet(true);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual(6, result.Tables[0].Columns.Count);
Assert.AreEqual(33, result.Tables[0].Rows.Count);
Assert.AreEqual("column a", result.Tables[0].Columns[0].ColumnName);
Assert.AreEqual(" column b", result.Tables[0].Columns[1].ColumnName);
Assert.AreEqual(" column b_1", result.Tables[0].Columns[2].ColumnName);
Assert.AreEqual("Column3", result.Tables[0].Columns[3].ColumnName);
Assert.AreEqual("column e", result.Tables[0].Columns[4].ColumnName);
Assert.AreEqual(" column b_2", result.Tables[0].Columns[5].ColumnName);
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[0][0]));
Assert.AreEqual("b", result.Tables[0].Rows[0][1]);
Assert.AreEqual("c", result.Tables[0].Rows[0][2]);
Assert.AreEqual("d", result.Tables[0].Rows[0][3]);
Assert.AreEqual(" e ", result.Tables[0].Rows[0][4]);
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][0]));
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[1][1]));
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][2]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[1][3]));
Assert.AreEqual(5, Convert.ToInt32(result.Tables[0].Rows[1][4]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[2][0]));
Assert.AreEqual(new DateTime(2012, 10, 13), result.Tables[0].Rows[2][1]);
Assert.AreEqual(new DateTime(2012, 10, 14), result.Tables[0].Rows[2][2]);
Assert.AreEqual(new DateTime(2012, 10, 15), result.Tables[0].Rows[2][3]);
Assert.AreEqual(new DateTime(2012, 10, 16), result.Tables[0].Rows[2][4]);
for (int i = 4; i < 33; i++)
{
Assert.AreEqual(i + 2, Convert.ToInt32(result.Tables[0].Rows[i][0]));
Assert.AreEqual(i + 3, Convert.ToInt32(result.Tables[0].Rows[i][1]));
Assert.AreEqual(i + 4, Convert.ToInt32(result.Tables[0].Rows[i][2]));
Assert.AreEqual(i + 5, Convert.ToInt32(result.Tables[0].Rows[i][3]));
Assert.AreEqual(i + 6, Convert.ToInt32(result.Tables[0].Rows[i][4]));
}
excelReader.Close();
}
public static ImportDataModel GetImportModels(string path)
{
try
{
var model = new ImportDataModel();
var stream = File.OpenRead(path);
IExcelDataReader excelReader = null;
//Reading from a binary Excel file ('97-2003 format; *.xls)
if (Path.GetExtension(path).Equals(".xls", StringComparison.InvariantCultureIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
//Reading from a OpenXml Excel file (2007 format; *.xlsx)
if (Path.GetExtension(path).Equals(".xlsx", StringComparison.InvariantCultureIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (excelReader == null)
{
return(model);
}
var pageIndex = 0;
do
{
pageIndex++;
var items = new List <ImportModel>();
var category = (CategoryCode)pageIndex;
var rowIndex = 0;
while (excelReader.Read())
{
rowIndex++;
if (rowIndex < StartRowIndex)
{
continue;
}
if (excelReader.IsDBNull(3))
{
continue;
}
var item = new ImportModel();
item.V1 = excelReader.IsDBNull(0) ? (int?)null : excelReader.GetInt32(0);
item.V2 = excelReader.GetString(1);
item.V3 = excelReader.GetString(2);
item.V4 = excelReader.GetString(3);
item.V5 = excelReader.GetString(4);
item.V6 = excelReader.GetString(5);
item.V7 = excelReader.GetString(6);
item.V8 = excelReader.GetString(7);
item.V9 = excelReader.GetString(8);
item.V10 = excelReader.GetString(9);
item.V11 = excelReader.IsDBNull(10) ? (DateTime?)null : excelReader.GetDateTime(10);
item.V12 = excelReader.IsDBNull(11) ? (double?)null : excelReader.GetDouble(11);
item.V13 = excelReader.IsDBNull(12) ? (double?)null : excelReader.GetDouble(12);
item.V14 = excelReader.IsDBNull(13) ? (double?)null : excelReader.GetDouble(13);
item.V15 = excelReader.IsDBNull(14) ? (double?)null : excelReader.GetDouble(14);
item.V16 = excelReader.IsDBNull(15) ? (double?)null : excelReader.GetDouble(15);
item.V17 = excelReader.IsDBNull(16) ? (double?)null : excelReader.GetDouble(16);
item.V18 = excelReader.IsDBNull(17) ? (double?)null : excelReader.GetDouble(17);
item.V19 = excelReader.IsDBNull(18) ? (double?)null : excelReader.GetDouble(18);
item.V20 = excelReader.IsDBNull(19) ? (double?)null : excelReader.GetDouble(19);
item.V21 = excelReader.IsDBNull(20) ? (double?)null : excelReader.GetDouble(20);
item.V22 = excelReader.IsDBNull(21) ? (double?)null : excelReader.GetDouble(21);
item.V23 = excelReader.IsDBNull(22) ? (double?)null : excelReader.GetDouble(22);
item.V24 = excelReader.IsDBNull(23) ? (double?)null : excelReader.GetDouble(23);
item.V25 = excelReader.IsDBNull(24) ? (DateTime?)null : excelReader.GetDateTime(24);
item.V26 = excelReader.IsDBNull(25) ? (double?)null : excelReader.GetDouble(25);
item.V27 = excelReader.IsDBNull(26) ? (DateTime?)null : excelReader.GetDateTime(26);
item.V28 = excelReader.IsDBNull(27) ? (DateTime?)null : excelReader.GetDateTime(27);
item.V29 = excelReader.IsDBNull(28) ? (DateTime?)null : excelReader.GetDateTime(28);
item.V30 = excelReader.IsDBNull(29) ? (DateTime?)null : excelReader.GetDateTime(29);
item.V31 = excelReader.IsDBNull(30) ? (DateTime?)null : excelReader.GetDateTime(30);
item.V32 = excelReader.IsDBNull(31) ? (DateTime?)null : excelReader.GetDateTime(31);
item.V33 = excelReader.IsDBNull(32) ? (DateTime?)null : excelReader.GetDateTime(32);
item.V34 = excelReader.GetString(33);
item.V35 = excelReader.GetString(34);
item.V36 = excelReader.GetString(35);
item.V37 = excelReader.IsDBNull(36) ? (DateTime?)null : excelReader.GetDateTime(36);
item.V38 = excelReader.GetString(37);
item.V39 = excelReader.IsDBNull(38) ? (DateTime?)null : excelReader.GetDateTime(38);
item.V40 = excelReader.IsDBNull(39) ? (double?)null : excelReader.GetDouble(39);
items.Add(item);
}
model.Items.Add(category, items);
model.PageRows.Add(category, rowIndex);
} while (excelReader.NextResult());
excelReader.Close();
return(model);
}
catch (Exception ex)
{
throw ex;
}
}
public void importfileExcel(string tenbang)
{
ExcelSuport exsp = new ExcelSuport();
List <string> tencot = exsp.LayTenCot(tenbang);
try
{
OpenFileDialog open = new OpenFileDialog();
open.Filter = "Chọn file Excel |*.xls; *.xlsx; *.xlsm";
if (open.ShowDialog() == DialogResult.Cancel)
{
return;
}
FileStream stream = new FileStream(open.FileName, FileMode.Open);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
string celldautien = "";
foreach (DataTable table in result.Tables)
{
if (table.Columns.Count != tencot.Count)
{
XtraMessageBox.Show("File không hợp lệ! Vui lòng kiểm tra lại!!");
break;
}
else
{
foreach (DataRow dr in table.Rows)
{
if (table.Rows.IndexOf(dr) == 0)
{
celldautien = dr[0].ToString();
continue;
}
else
{
if (celldautien == "MaNV")
{
this.ImportNV(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(),
dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), Convert.ToDateTime(dr[8]));
excelReader.Close();
stream.Close();
XtraMessageBox.Show("Import file thành công", "Thông báo");
}
else
{
if (celldautien == "MaKH")
{
this.ImportKH(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), Convert.ToDateTime(dr[3]), dr[4].ToString(),
dr[5].ToString(), dr[6].ToString(), dr[7].ToString());
excelReader.Close();
stream.Close();
XtraMessageBox.Show("Import file thành công", "Thông báo");
}
else
{
this.ImportHD(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), Convert.ToDateTime(dr[5]));
excelReader.Close();
stream.Close();
XtraMessageBox.Show("Import file thành công", "Thông báo");
}
}
//kh.ImportKH(Convert.ToString(dr[0]), Convert.ToString(dr[1]), Convert.ToString(dr[2]), Convert.ToDateTime(dr[3]),
// Convert.ToBoolean(dr[4]), Convert.ToString(dr[5]), Convert.ToString(dr[6]), Convert.ToString(dr[7]));
}
}
}
}
}
catch (Exception ex)
{
XtraMessageBox.Show("Có lỗi xảy ra, xin chọn lại!", "Thông báo");
}
}
public ActionResult Emitir(HttpPostedFileBase upload)
{
if (ModelState.IsValid)
{
v_codigocotizacion = System.Web.HttpContext.Current.Session["codigocotizacion"].ToString();
v_empresanombre = System.Web.HttpContext.Current.Session["empresanombre"].ToString();
v_empresaruc = System.Web.HttpContext.Current.Session["empresaruc"].ToString();
v_codigocliente = System.Web.HttpContext.Current.Session["codigocliente"].ToString();
v_detmontoplanillaadm = Convert.ToDouble(System.Web.HttpContext.Current.Session["detmontoplanillaadm"]);
v_detmontoplanillaope = Convert.ToDouble(System.Web.HttpContext.Current.Session["detmontoplanillaope"]);
ViewData["txtEmpresaRUC"] = v_empresaruc;
ViewData["txtEmpresaNombre"] = v_empresanombre;
ViewData["txtCodigoCotizacion"] = v_codigocotizacion;
ViewData["txtCodigoCliente"] = v_codigocliente;
ViewData["txtPlanillaMensual"] = v_detmontoplanillaadm + v_detmontoplanillaope;
if (upload != null && upload.ContentLength > 0)
{
// ExcelDataReader works with the binary Excel file, so it needs a FileStream
// to get started. This is how we avoid dependencies on ACE or Interop:
Stream stream = upload.InputStream;
// We return the interface, so that
IExcelDataReader reader = null;
if (upload.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (upload.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
ModelState.AddModelError("File", "Formato de archivo incorrecto.");
return(View());
}
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = false
}
};
DataTable dt = new DataTable();
DataSet result = reader.AsDataSet();
DataSet resultordenado = new DataSet();//Data to be sorted.
//Sort data.
result.Tables[0].DefaultView.Sort = "Column5 ASC";
//Store in new Dataset
resultordenado.Tables.Add(result.Tables[0].DefaultView.ToTable());
string sMensaje = "";
dt = resultordenado.Tables[0];
dt.Columns["Column0"].ColumnName = "Nombres";
dt.Columns["Column1"].ColumnName = "Paterno";
dt.Columns["Column2"].ColumnName = "Materno";
dt.Columns["Column3"].ColumnName = "TipoTrab";
dt.Columns["Column4"].ColumnName = "TipoDoc";
dt.Columns["Column5"].ColumnName = "NroDoc";
dt.Columns["Column6"].ColumnName = "Sexo";
dt.Columns["Column7"].ColumnName = "EstadoCivil";
dt.Columns["Column8"].ColumnName = "Direccion";
dt.Columns["Column9"].ColumnName = "Telefono";
dt.Columns["Column10"].ColumnName = "FechaNac";
dt.Columns["Column11"].ColumnName = "Correo";
dt.Columns["Column12"].ColumnName = "Moneda";
dt.Columns["Column13"].ColumnName = "Sueldo";
dt.Columns["Column14"].ColumnName = "Estado";
int j = 0;
int k = 0;
for (int i = 0; i < dt.Rows.Count - 1; i++)
{
string datoNombres = dt.Rows[i]["Nombres"].ToString();
string datoPaterno = dt.Rows[i]["Paterno"].ToString();
string datoMaterno = dt.Rows[i]["Materno"].ToString();
string datoTipoTrab = dt.Rows[i]["TipoTrab"].ToString();
string datoTipoDoc = dt.Rows[i]["TipoDoc"].ToString();
string datoNroDoc = dt.Rows[i]["NroDoc"].ToString();
string datoNroDocAnt = "";
if (i > 0)
{
datoNroDocAnt = dt.Rows[i - 1]["NroDoc"].ToString();
}
DataRow dr = dt.Rows[i];
if (datoNombres == "Nombres" | datoPaterno == "Paterno")
{
dr.Delete();
dt.AcceptChanges();
}
if (datoNombres == "" | datoPaterno == "" | datoMaterno == "" | datoTipoTrab == "" | datoTipoDoc == "" | datoNroDoc == "")
{
dt.Rows[i]["Estado"] = "Error: Faltan Datos ";
j = j + 1;
}
else if (datoNroDoc == datoNroDocAnt)
{
dt.Rows[i]["Estado"] = "Error : NroDoc Duplicado";
dt.Rows[i - 1]["Estado"] = "Error : NroDoc Duplicado";
k = k + 1;
}
else
{
dt.Rows[i]["Estado"] = "Correcto";
}
}
reader.Close();
if (j == 0 & k == 0)
{
sMensaje = "Datos Correctos";
}
else
{
sMensaje = "Existen errores en los datos, corrija y vuelva a cargar el archivo";
}
ViewBag.Mensaje = sMensaje;
ViewBag.Message = sMensaje;
//ViewBag.showSuccessAlert = true;
return(PartialView(dt));
}
else
{
ModelState.AddModelError("File", "Seleccione un archivo");
}
}
return(PartialView());
}
static void Main(string[] args)
{
string path = System.Environment.CurrentDirectory; //获取当前目录
//在当前目录创建存放csv和txt格式文件的文件夹
string csvDir = path + "\\csv";
string txtDir = path + "\\txt";
if (!Directory.Exists(csvDir))
{
Directory.CreateDirectory(csvDir);
}
else
{
Directory.Delete(csvDir, true);
Directory.CreateDirectory(csvDir);
}
if (!Directory.Exists(txtDir))
{
Directory.CreateDirectory(txtDir);
}
else
{
Directory.Delete(txtDir, true);
Directory.CreateDirectory(txtDir);
}
//获取当前目录下所有文件，查找到xlsx文件
DirectoryInfo dir = new DirectoryInfo(path);
FileInfo[] fileInfos = dir.GetFiles(); //获取当前目录下的所有文件
//遍历目录下所有excel文件，将其名称存放到excelFileList。将其全路径存放到excelFullPath
ArrayList excelFileList = new ArrayList();
ArrayList excelFullPath = new ArrayList();
for (int i = 0; i < fileInfos.Length; ++i)
{
if (fileInfos[i].Name.EndsWith(".xlsx") || fileInfos[i].Name.EndsWith(".xls"))
{
excelFileList.Add(fileInfos[i].ToString());
excelFullPath.Add(path + "\\" + fileInfos[i].ToString());
}
}
for (int i = 0; i < excelFullPath.Count; ++i)
{
//Console.WriteLine("excelFileList==="+ excelFileList.Count+"|");
//Console.WriteLine("excelFullPath===" + excelFullPath.Count + "|");
//Console.WriteLine(excelFullPath[i]);
FileStream stream = File.Open(excelFullPath[i].ToString(), FileMode.Open, FileAccess.Read);
IExcelDataReader excelDataReader = excelFullPath[i].ToString().Contains(".xlsx") ? ExcelReaderFactory.CreateOpenXmlReader(stream) : ExcelReaderFactory.CreateBinaryReader(stream);
DataSet result = excelDataReader.AsDataSet();
excelDataReader.IsFirstRowAsColumnNames = true;
//DataTable at = result.Tables[0];
//Console.WriteLine("DataTable=" + at.Rows[0].ItemArray[0].ToString());
//遍历表格中数据，按行写入csv文件和txt文件
FileStream temStreamCsv = new FileStream(csvDir + "\\" + excelFileList[i].ToString().Replace(".xlsx", ".csv"), FileMode.OpenOrCreate, FileAccess.ReadWrite);
FileStream temStreamTxt = new FileStream(txtDir + "\\" + excelFileList[i].ToString().Replace(".xlsx", ".txt"), FileMode.OpenOrCreate, FileAccess.ReadWrite);
StreamWriter sw = new StreamWriter(temStreamCsv);
StreamWriter swTxt = new StreamWriter(temStreamTxt, Encoding.UTF8);
for (int ii = 0; ii < result.Tables[0].Rows.Count; ++ii)
{
string rowStr = String.Empty;
for (int j = 0; j < result.Tables[0].Columns.Count; ++j)
{
if (j != result.Tables[0].Columns.Count - 1)
{
rowStr += result.Tables[0].Rows[ii][j].ToString() + ",";
}
else
{
rowStr += result.Tables[0].Rows[ii][j].ToString();
}
}
sw.WriteLine(rowStr);
swTxt.WriteLine(rowStr);
}
Console.WriteLine(excelFileList[i]);
Console.WriteLine("表格行数：" + result.Tables[0].Rows.Count);
Console.WriteLine("转换OK了！！");
Console.WriteLine("---------------------------------------------------------------------");
sw.Close();
swTxt.Close();
temStreamCsv.Close();
temStreamTxt.Close();
excelDataReader.Close();
stream.Close();
}
Console.ReadKey();
}
public async Task <ActionResult> DataFromExcel(ExcelUploadEntities bie, HttpPostedFileBase file2)
{
try {
//if (ModelState.IsValid)
//{
if (file2 != null && file2.ContentLength > 0)
{
// ExcelDataReader works with the binary Excel file, so it needs a FileStream
// to get started. This is how we avoid dependencies on ACE or Interop:
Stream stream = file2.InputStream;
// We return the interface, so that
IExcelDataReader reader = null;
if (file2.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (file2.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
ModelState.AddModelError("File", "This file format is not supported");
return(View());
}
reader.IsFirstRowAsColumnNames = true;
DataSet result = reader.AsDataSet();
//for (int i = 1; i < result.Tables[0].Rows.Count; i++)
//{
// DataRow data = result.Tables[0].Rows[i];
// // string CellValue = result.Tables[0].Rows[i][i];
// //System.Diagnostics.Debug.Write(data.Table.Rows[i]["Column header name"]);
//}
int ap = 0;
int an = 0;
int bp = 0;
int bn = 0;
int op = 0;
int on = 0;
int abp = 0;
int abn = 0;
for (int i = 0; i < result.Tables[0].Rows.Count; i++)
{
bie.EMPLOYEE_CODE = null;
bie.DESIGNATION = null;
bie.EMPLOYEE_FIRSTNAME = null;
string fullname = null;
bie.EMPLOYEE_MIDDLENAME = null;
bie.EMPLOYEE_LASTNAME = null;
bie.DATE_OF_BIRTH = null;
bie.AGE = null;
bie.SEX = null;
bie.DOMICILE_STATUS = null;
bie.MATERIAL_STATUS = null;
bie.WEDDING_ANNIVERSARY = null;
bie.REPORTING_TO = null;
bie.GRADE = null;
bie.BLOOD_GROUP = null;
bie.HEIGHT = null;
bie.WEIGHT = null;
bie.MEDICLAIM_NUMBER = null;
bie.ESIC_NO = null;
bie.PF_UNID_NUMBER = null;
bie.INDUCTION = null;
bie.IDENTIFICATION_MARK = null;
bie.LAST_MAJOR_ILLNESS_SURGERY = null;
bie.ALLERGY_HISTORY = null;
bie.HIRING_TYPE = null;
bie.CV_TYPE = null;
bie.YEAR_OF_EXPERIENCE = null;
bie.EXIT_DATE = null;
bie.EXIT_TYPE = null;
bie.REFERENCE = null;
bie.FULL_N_FINAL_STATUS = null;
bie.DIVISION = null;
bie.SUBDIVISION = null;
bie.SECTION = null;
bie.DEPARTMENT = null;
bie.EMPLOYEE_IMAGE = null;
bie.JOINING_DATE = null;
bie.RELIGION = null;
bie.SUBDIVISION = null;
bie.WORKLOCATION = null;
bie.MOTHERTOUNGE = null;
bie.STATUS = null;
bie.Account = null;
bie.Relationship = null;
bie.Communication = null;
bie.qualification = null;
bie.Employment = null;
//string conn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
//SqlConnection con = new SqlConnection(conn);
// string query = "Insert into Person(Name,Email,Mobile) Values('" +
//ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() +
//"','" + ds.Tables[0].Rows[i][2].ToString() + "')";
//result.Tables[0].Rows[i]
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][1].ToString()))
{
bie.EMPLOYEE_CODE = Convert.ToString(result.Tables[0].Rows[i][1]).Trim();
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][2].ToString()))
{
if (result.Tables[0].Rows[i][2].ToString() == "NESCO - VAI - Balasore")
{
bie.WORKLOCATION = 2;
}
else if (result.Tables[0].Rows[i][2].ToString() == "Bhubaneswar")
{
bie.WORKLOCATION = 1;
}
else if (result.Tables[0].Rows[i][2].ToString() == "Mumbai")
{
bie.WORKLOCATION = 10;
}
else if (result.Tables[0].Rows[i][2].ToString() == "Project office")
{
bie.WORKLOCATION = 7;
}
else if (result.Tables[0].Rows[i][2].ToString() == "Puri")
{
bie.WORKLOCATION = 11;
}
else if (result.Tables[0].Rows[i][2].ToString() == "Khordha")
{
bie.WORKLOCATION = 5;
}
else if (result.Tables[0].Rows[i][2].ToString() == "Deputation - Gurgaon")
{
bie.WORKLOCATION = 9;
}
else if (result.Tables[0].Rows[i][2].ToString() == "Kandhamal - NRI")
{
bie.WORKLOCATION = 8;
}
else if (result.Tables[0].Rows[i][2].ToString() == "WESCO - VAI - Rourkela")
{
bie.WORKLOCATION = 14;
}
else if (result.Tables[0].Rows[i][2].ToString() == "Deputation - Bhubaneswar")
{
bie.WORKLOCATION = 12;
}
else if (result.Tables[0].Rows[i][2].ToString() == "Deputation - Mumbai")
{
bie.WORKLOCATION = 15;
}
else if (result.Tables[0].Rows[i][2].ToString() == "WB")
{
bie.WORKLOCATION = 13;
}
else if (result.Tables[0].Rows[i][2].ToString() == "Khordha 1" || result.Tables[0].Rows[i][2].ToString() == "Khordha-1")
{
bie.WORKLOCATION = 2;
bie.DIVISION = "000001";
}
else if (result.Tables[0].Rows[i][2].ToString() == "Khordha 2" || result.Tables[0].Rows[i][2].ToString() == "Khordha-2")
{
bie.WORKLOCATION = 2;
bie.DIVISION = "000007";
}
else if (result.Tables[0].Rows[i][2].ToString() == "Nayagarh")
{
bie.WORKLOCATION = 2;
bie.DIVISION = "000002";
}
else if (result.Tables[0].Rows[i][2].ToString() == "Balugaon")
{
bie.WORKLOCATION = 2;
bie.DIVISION = "000003";
}
else if (result.Tables[0].Rows[i][2].ToString() == "Puri Urban")
{
bie.WORKLOCATION = 2;
bie.DIVISION = "000004";
}
else if (result.Tables[0].Rows[i][2].ToString() == "Puri Rural")
{
bie.WORKLOCATION = 2;
bie.DIVISION = "000006";
}
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][4].ToString()))
{
bie.WORKLOCATION = 3;
bie.SUBDIVISION = Convert.ToString(result.Tables[0].Rows[i][4].ToString()).Trim();
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][5].ToString()))
{
bie.WORKLOCATION = 4;
bie.SECTION = Convert.ToString(result.Tables[0].Rows[i][5].ToString()).Trim();
}
fullname = result.Tables[0].Rows[i][6].ToString().Trim();
//char[] delimiters1 = new[] { "", StringSplitOptions.RemoveEmptyEntries};
//string[] data = fullname.Split(new []{null}, StringSplitOptions.RemoveEmptyEntries);
var data = fullname.Split(new[] { " " }, StringSplitOptions.RemoveEmptyEntries);
if (data.ToList().Count == 3)
{
if (!string.IsNullOrEmpty(data[0].ToString()))
{
bie.EMPLOYEE_FIRSTNAME = Convert.ToString(data[0]).Trim();
}
if (!string.IsNullOrEmpty(data[1].ToString()))
{
bie.EMPLOYEE_MIDDLENAME = Convert.ToString(data[1]).Trim();
}
if (!string.IsNullOrEmpty(data[2].ToString()))
{
bie.EMPLOYEE_LASTNAME = Convert.ToString(data[2]).Trim();
}
}
if (data.ToList().Count == 2)
{
if (!string.IsNullOrEmpty(data[0].ToString()))
{
bie.EMPLOYEE_FIRSTNAME = Convert.ToString(data[0]).Trim();
}
if (!string.IsNullOrEmpty(data[1].ToString()))
{
bie.EMPLOYEE_LASTNAME = Convert.ToString(data[1]).Trim();
}
}
if (data.ToList().Count == 1)
{
if (!string.IsNullOrEmpty(data[0].ToString()))
{
bie.EMPLOYEE_FIRSTNAME = Convert.ToString(data[0]).Trim();
}
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][7].ToString()))
{
bie.designationname = Convert.ToString(result.Tables[0].Rows[i][7]).Trim();
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][8].ToString()))
{
bie.departmentname = Convert.ToString(result.Tables[0].Rows[i][8]).Trim();
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][9].ToString()))
{
bie.gradename = Convert.ToString(result.Tables[0].Rows[i][9]).Trim();
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][10].ToString()))
{
bie.REPORTING_TO = Convert.ToString(result.Tables[0].Rows[i][10]);
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][11].ToString()))
{
//double joining = Convert.ToDouble(result.Tables[0].Rows[i][8]);
//DateTime dt = DateTime.FromOADate(joining);
bie.JOINING_DATE = Convert.ToDateTime(result.Tables[0].Rows[i][11]);
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][12].ToString()))
{
bie.STATUS = Convert.ToString(result.Tables[0].Rows[i][12]);
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][17].ToString()))
{
//double dob = Convert.ToDouble(result.Tables[0].Rows[i][14]);
//DateTime dt = DateTime.FromOADate(dob);
bie.DATE_OF_BIRTH = Convert.ToDateTime(result.Tables[0].Rows[i][17]);
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][18].ToString()))
{
bie.MATERIAL_STATUS = Convert.ToString(result.Tables[0].Rows[i][18]);
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][19].ToString()))
{
//double wed = Convert.ToDouble(result.Tables[0].Rows[i][16]);
//DateTime dt = DateTime.FromOADate(wed);
bie.WEDDING_ANNIVERSARY = Convert.ToDateTime(result.Tables[0].Rows[i][19]);
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][26].ToString()))
{
//bie.bloodgroup = Convert.ToString(result.Tables[0].Rows[i][25]);
if (result.Tables[0].Rows[i][26].ToString() == "A +ve")
{
ap++;
bie.BLOOD_GROUP = 7;
}
else if (result.Tables[0].Rows[i][26].ToString() == "A -ve")
{
an++;
bie.BLOOD_GROUP = 1;
}
else if (result.Tables[0].Rows[i][26].ToString() == "B -ve")
{
bn++;
bie.BLOOD_GROUP = 2;
}
else if (result.Tables[0].Rows[i][26].ToString() == "B +ve" || result.Tables[0].Rows[i][26].ToString() == "B +ve ")
{
bp++;
bie.BLOOD_GROUP = 8;
}
else if (result.Tables[0].Rows[i][26].ToString() == "O +ve")
{
op++;
bie.BLOOD_GROUP = 3;
}
else if (result.Tables[0].Rows[i][26].ToString() == "O -ve")
{
on++;
bie.BLOOD_GROUP = 4;
}
else if (result.Tables[0].Rows[i][26].ToString() == "AB +ve")
{
abp++;
bie.BLOOD_GROUP = 5;
}
else if (result.Tables[0].Rows[i][26].ToString() == "AB -ve")
{
abn++;
bie.BLOOD_GROUP = 6;
}
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][32].ToString()))
{
string[] yrdata = result.Tables[0].Rows[i][32].ToString().Split(null);
if (yrdata.ToList().Count > 1)
{
if (yrdata[1].Contains("months") || yrdata[1].Contains("Month") || yrdata[1].Contains("month") || yrdata[1].Contains("Months"))
{
decimal?YROFEXP = Convert.ToDecimal(yrdata[0]) / 100;
bie.YEAR_OF_EXPERIENCE = Convert.ToString(YROFEXP);
}
if (yrdata[1].Contains("Year") || yrdata[1].Contains("Years") || yrdata[0].Contains("year") || yrdata[1].Contains("years") || yrdata[1].Contains("yrs") || yrdata[1].Contains("Yrs"))
{
bie.YEAR_OF_EXPERIENCE = Convert.ToString(yrdata[0]);
}
//else
//{
// bie.YEAR_OF_EXPERIENCE = Convert.ToDecimal(yrdata[0]);
//}
}
if (yrdata[0].Contains("months") || yrdata[0].Contains("Month") || yrdata[0].Contains("month") || yrdata[0].Contains("Months"))
{
bie.YEAR_OF_EXPERIENCE = "0";
}
if (yrdata[0].Contains("Year") || yrdata[0].Contains("Years") || yrdata[0].Contains("year") || yrdata[0].Contains("years") || yrdata[0].Contains("yrs") || yrdata[0].Contains("Yrs"))
{
bie.YEAR_OF_EXPERIENCE = "0";
}
foreach (string str in yrdata)
{
if (str.Contains("fresher"))
{
bie.YEAR_OF_EXPERIENCE = "0";
}
//else if (str.Contains("month") || str.Contains("Month") || str.Contains("months") || str.Contains("Months"))
//{
// if (yrdata[0].Contains("months") || yrdata[0].Contains("Month") || yrdata[0].Contains("month") || yrdata[0].Contains("Months"))
// {
// }
// else
// {
// bie.YEAR_OF_EXPERIENCE = Convert.ToDecimal(yrdata[0]) / 100;
// }
//}
//else if (str.Contains("Year") || str.Contains("Years") || str.Contains("year") || str.Contains("years") || str.Contains("yrs") || str.Contains("Yrs"))
//{
// if (yrdata[0].Contains("Year") || yrdata[0].Contains("Years") || yrdata[0].Contains("year") || yrdata[0].Contains("years") || yrdata[0].Contains("yrs") || yrdata[0].Contains("Yrs"))
// {
// }
// else
// {
// bie.YEAR_OF_EXPERIENCE = Convert.ToDecimal(yrdata[0]);
// }
//}
//else
//{
// bie.YEAR_OF_EXPERIENCE = Convert.ToDecimal(yrdata[0]);
//}
}
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][40].ToString()))
{
bie.ESIC_NO = Convert.ToString(result.Tables[0].Rows[i][40]);
}
List <RelationshipDetailEntities> bi = new List <RelationshipDetailEntities>();
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][20].ToString()))
{
//List<RelationshipDetailEntities> spouse = new List<RelationshipDetailEntities>();
RelationshipDetailEntities spouse = new RelationshipDetailEntities();
spouse.RELATIONSHIP = "Husband/Spouse";
string[] data1 = result.Tables[0].Rows[i][20].ToString().Split(',');
if (data1.ToList().Count > 0)
{
spouse.RELATIVE_NAME = Convert.ToString(data1[0]);
}
if (data1.ToList().Count > 1)
{
spouse.RELATIVE_NAME = Convert.ToString(data1[0]);
spouse.DATE_OF_BIRTH = Convert.ToDateTime(data1[1]);
}
bi.Add(spouse);
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][21].ToString()))
{
RelationshipDetailEntities Child1 = new RelationshipDetailEntities();
Child1.RELATIONSHIP = "Child1";
string[] data1 = result.Tables[0].Rows[i][21].ToString().Split(',');
if (data1.ToList().Count > 0)
{
Child1.RELATIVE_NAME = Convert.ToString(data1[0]);
}
if (data1.ToList().Count > 1)
{
Child1.RELATIVE_NAME = Convert.ToString(data1[0]);
Child1.DATE_OF_BIRTH = Convert.ToDateTime(data1[1]);
}
bi.Add(Child1);
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][22].ToString()))
{
RelationshipDetailEntities Child2 = new RelationshipDetailEntities();
Child2.RELATIONSHIP = "Child2";
char[] delimiters = new[] { ',', ';' };
string[] data1 = result.Tables[0].Rows[i][22].ToString().Split(delimiters);
if (data1.ToList().Count > 0)
{
Child2.RELATIVE_NAME = Convert.ToString(data1[0]);
}
if (data1.ToList().Count > 1)
{
Child2.RELATIVE_NAME = Convert.ToString(data1[0]);
Child2.DATE_OF_BIRTH = Convert.ToDateTime(data1[1]);
}
bi.Add(Child2);
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][23].ToString()))
{
RelationshipDetailEntities Father = new RelationshipDetailEntities();
Father.RELATIONSHIP = "Father/Mother";
Father.RELATIVE_NAME = Convert.ToString(result.Tables[0].Rows[i][23]);
string[] data1 = result.Tables[0].Rows[i][23].ToString().Split();
//if (data1.ToList().Count > 0)
//{
// Father.RELATIVE_NAME = Convert.ToString(data1[0]);
//}
foreach (string str in data1)
{
if (str.Contains("Mother") || str.Contains("mother"))
{
Father.RELATIONSHIP = "Mother";
}
}
bi.Add(Father);
}
bie.Relationship = bi;
List <CommunicationDetailsEntities> com = new List <CommunicationDetailsEntities>();
CommunicationDetailsEntities obj = new CommunicationDetailsEntities();
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][14].ToString()))
{
obj.MOBILE_NUMBER = Convert.ToString(result.Tables[0].Rows[i][14].ToString().Trim());
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][15].ToString()))
{
obj.EMERGENCY_CONTACT_NUMBER = Convert.ToString(result.Tables[0].Rows[i][15].ToString()).Trim();
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][16].ToString()))
{
obj.OFFICIAL_MOBILE_NUMBER = Convert.ToString(result.Tables[0].Rows[i][16].ToString()).Trim();
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][28].ToString()))
{
obj.PANCARD_NUMBER = Convert.ToString(result.Tables[0].Rows[i][28].ToString()).Trim();
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][27].ToString()))
{
obj.PERMANENT_ADDRESS = Convert.ToString(result.Tables[0].Rows[i][27].ToString());
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][25].ToString()))
{
obj.PERSONAL_EMAIL = Convert.ToString(result.Tables[0].Rows[i][25].ToString());
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][24].ToString()))
{
obj.OFFICIAL_EMAIL = Convert.ToString(result.Tables[0].Rows[i][24].ToString());
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][29].ToString()))
{
obj.AADHAR_NUMBER = Convert.ToString(result.Tables[0].Rows[i][29].ToString());
}
com.Add(obj);
bie.Communication = com;
List <EmpAcountEntity> acc = new List <EmpAcountEntity>();
EmpAcountEntity acount = new EmpAcountEntity();
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][35].ToString()))
{
acount.BANKNAME = result.Tables[0].Rows[i][35].ToString();
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][36].ToString()))
{
acount.ACCOUNTNO = result.Tables[0].Rows[i][36].ToString();
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][37].ToString()))
{
acount.IFSCCODE = result.Tables[0].Rows[i][37].ToString();
}
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][38].ToString()))
{
acount.BRANCH = result.Tables[0].Rows[i][38].ToString();
}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][36].ToString()))
//{
// acount.CTC = result.Tables[0].Rows[i][36].ToString();
//}
acc.Add(acount);
bie.Account = acc;
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][40].ToString()))
{
bie.ESIC_NO = result.Tables[0].Rows[i][40].ToString();
}
List <QualificationDetailsEntities> qualificationdtls = new List <QualificationDetailsEntities>();
QualificationDetailsEntities qual = new QualificationDetailsEntities();
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][30].ToString()))
{
qual.EXAM_DEGREE_TYPE = result.Tables[0].Rows[i][30].ToString();
}
qualificationdtls.Add(qual);
bie.qualification = qualificationdtls;
List <EmploymentEntities> Employmentdtls = new List <EmploymentEntities>();
EmploymentEntities emp = new EmploymentEntities();
if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][33].ToString()))
{
emp.ORGANISATION_NAME = result.Tables[0].Rows[i][33].ToString();
}
Employmentdtls.Add(emp);
bie.Employment = Employmentdtls;
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][33].ToString()))
//{
// acount.ct = result.Tables[0].Rows[i][33].ToString();
//}
//foreach (string name in data)
//{
// foreach (ListItem li in DropDownList1.Items)
// {
// if (li.Value == word)
// {
// DropDownList2.Items.Add(new ListItem(li.Value));
// }
// }
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][1].ToString()))
//{
// bie.EMPLOYEE_CODE = Convert.ToString(result.Tables[0].Rows[i][1]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][2].ToString()))
//{
// bie.DESIGNATION = Convert.ToDecimal(result.Tables[0].Rows[i][2]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][3].ToString()))
//{
// bie.EMPLOYEE_FIRSTNAME = Convert.ToString(result.Tables[0].Rows[i][3]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][4].ToString()))
//{
// bie.EMPLOYEE_MIDDLENAME = Convert.ToString(result.Tables[0].Rows[i][4]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][5].ToString()))
//{
// bie.EMPLOYEE_LASTNAME = Convert.ToString(result.Tables[0].Rows[i][5]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][6].ToString()))
//{
// double dob = Convert.ToDouble(result.Tables[0].Rows[i][6]);
// DateTime dt = DateTime.FromOADate(dob);
// bie.DATE_OF_BIRTH = dt;
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][7].ToString()))
//{
// bie.AGE = Convert.ToDecimal(result.Tables[0].Rows[i][7]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][8].ToString()))
//{
// bie.SEX = Convert.ToString(result.Tables[0].Rows[i][8]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][9].ToString()))
//{
// bie.DOMICILE_STATUS = Convert.ToString(result.Tables[0].Rows[i][9]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][10].ToString()))
//{
// bie.MATERIAL_STATUS = Convert.ToString(result.Tables[0].Rows[i][10]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][11].ToString()))
//{
// double dob = Convert.ToDouble(result.Tables[0].Rows[i][11]);
// DateTime dt = DateTime.FromOADate(dob);
// bie.WEDDING_ANNIVERSARY = dt;
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][12].ToString()))
//{
// bie.REPORTING_TO = Convert.ToString(result.Tables[0].Rows[i][12]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][13].ToString()))
//{
// bie.GRADE = Convert.ToDecimal(result.Tables[0].Rows[i][13]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][14].ToString()))
//{
// bie.BLOOD_GROUP = Convert.ToDecimal(result.Tables[0].Rows[i][14]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][15].ToString()))
//{
// bie.HEIGHT = Convert.ToDecimal(result.Tables[0].Rows[i][15]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][16].ToString()))
//{
// bie.WEIGHT = Convert.ToDecimal(result.Tables[0].Rows[i][16]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][17].ToString()))
//{
// bie.MEDICLAIM_NUMBER = Convert.ToString(result.Tables[0].Rows[i][17]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][18].ToString()))
//{
// bie.ESIC_NO = Convert.ToString(result.Tables[0].Rows[i][18]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][19].ToString()))
//{
// bie.PF_UNID_NUMBER = Convert.ToString(result.Tables[0].Rows[i][19]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][20].ToString()))
//{
// bie.INDUCTION = Convert.ToString(result.Tables[0].Rows[i][20]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][21].ToString()))
//{
// bie.IDENTIFICATION_MARK = Convert.ToString(result.Tables[0].Rows[i][21]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][22].ToString()))
//{
// bie.LAST_MAJOR_ILLNESS_SURGERY = Convert.ToString(result.Tables[0].Rows[i][22]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][23].ToString()))
//{
// bie.ALLERGY_HISTORY = Convert.ToString(result.Tables[0].Rows[i][23]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][24].ToString()))
//{
// bie.PHYSICAL_DISABILITY = Convert.ToString(result.Tables[0].Rows[i][24]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][25].ToString()))
//{
// bie.HIRING_TYPE = Convert.ToString(result.Tables[0].Rows[i][25]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][26].ToString()))
//{
// bie.CV_TYPE = Convert.ToString(result.Tables[0].Rows[i][26]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][27].ToString()))
//{
// bie.YEAR_OF_EXPERIENCE = Convert.ToDecimal(result.Tables[0].Rows[i][27]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][28].ToString()))
//{
// double dob = Convert.ToDouble(result.Tables[0].Rows[i][28]);
// DateTime dt = DateTime.FromOADate(dob);
// bie.EXIT_DATE = dt;
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][29].ToString()))
//{
// bie.EXIT_TYPE = Convert.ToDecimal(result.Tables[0].Rows[i][29]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][30].ToString()))
//{
// bie.REFERENCE = Convert.ToString(result.Tables[0].Rows[i][30]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][31].ToString()))
//{
// bie.FULL_N_FINAL_STATUS = Convert.ToString(result.Tables[0].Rows[i][31]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][36].ToString()))
//{
// bie.DIVISION = Convert.ToString(result.Tables[0].Rows[i][36]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][37].ToString()))
//{
// bie.SUBDIVISION = Convert.ToString(result.Tables[0].Rows[i][37]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][38].ToString()))
//{
// bie.SECTION = Convert.ToString(result.Tables[0].Rows[i][38]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][39].ToString()))
//{
// bie.DEPARTMENT = Convert.ToDecimal(result.Tables[0].Rows[i][39]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][44].ToString()))
//{
// double dob = Convert.ToDouble(result.Tables[0].Rows[i][44]);
// DateTime dt = DateTime.FromOADate(dob);
// bie.JOININGDATE = dt.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
//}
////bie.DATE_OF_BIRTH = Convert.ToDateTime(result.Tables[0].Rows[i][6]);
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][45].ToString()))
//{
// bie.RELIGION = Convert.ToString(result.Tables[0].Rows[i][45]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][46].ToString()))
//{
// bie.WORKLOCATION = Convert.ToDecimal(result.Tables[0].Rows[i][46]);
//}
//if (!string.IsNullOrEmpty(result.Tables[0].Rows[i][47].ToString()))
//{
// bie.MOTHERTOUNGE = Convert.ToString(result.Tables[0].Rows[i][47]);
//}
//HttpResponseMessage responseMessage = await client.PostAsJsonAsync(url + "excelupload", bie);
//if (responseMessage.IsSuccessStatusCode)
//{
// TempData["successmsg"] = "saved";
// return RedirectToAction("Index");
//}
//con.Open();
//SqlCommand cmd = new SqlCommand(query, con);
//cmd.ExecuteNonQuery();
//con.Close();
}
reader.Close();
TempData["successmsg"] = "saved";
return(RedirectToAction("Index"));
}
TempData["warningmsg"] = "warn";
}
catch (Exception ex)
{
var outputLines = new List <string>();
outputLines.Add(string.Format(
"{0}: Entity of type \"{1}\" in state \"{2}\" has the following validation errors:", DateTime.Now,
ex.Message, ex.StackTrace, ex.InnerException, ex.HelpLink));
System.IO.File.AppendAllLines(@"D:\errors.txt", outputLines);
TempData["errormsg"] = "error";
return(RedirectToAction("Index"));
}
//}
//else
//{
// ModelState.AddModelError("File", "Please Upload Your file");
//}
//}
return(RedirectToAction("Index"));
}
public void CloseFile()
{
_excelDataReader?.Close();
_fileStream?.Close();
}
public MessageResponse ReadDataFromExcel()
{
MessageResponse res = new MessageResponse();
var outPayment = new List <string>();
MemoryStream reader = new MemoryStream(_byteArr);
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(reader);
Logger.Info("rowsZip");
//4. DataSet - Create column names from first row
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
DataTable tblResult = result.Tables[0];
tblResult.Columns.Add("LineNo", typeof(int));
for (int y = 0; y < tblResult.Rows.Count; y++)
{
tblResult.Rows[y]["LineNo"] = y + 1;
}
DataTable dtCloned = tblResult.Clone();
dtCloned.Columns["Vendor ID"].DataType = typeof(string);
foreach (DataRow row in tblResult.Rows)
{
if (row[0] != DBNull.Value)
{
dtCloned.ImportRow(row);
}
}
//validate
int check = 0;
DataTable selectedTableVendorID = null;
var rowsVendorID = dtCloned.AsEnumerable()
.Where(r => string.IsNullOrEmpty(r.Field <string>("Vendor ID")) == true);
DataTable selectedTableVendorName = null;
var rowsVendorName = dtCloned.AsEnumerable()
.Where(r => string.IsNullOrEmpty(r.Field <string>("Name")) == true);
var rowsVendorIDLess9 = dtCloned.AsEnumerable()
.Where(r => r.Field <string>("Vendor ID").Length < 9);
if (rowsVendorID.Any())
{
selectedTableVendorID = rowsVendorID.CopyToDataTable();
foreach (DataRow row in selectedTableVendorID.Rows)
{
res.messages.Add("Vendor ID required at line : " + row["LineNo"].ToString());
}
check++;
}
if (rowsVendorName.Any())
{
selectedTableVendorName = rowsVendorName.CopyToDataTable();
foreach (DataRow row in selectedTableVendorName.Rows)
{
res.messages.Add("Vendor Name required at line : " + row["LineNo"].ToString());
}
check++;
}
for (int i = dtCloned.Rows.Count - 1; i >= 0; i--)
{
DataRow dr = dtCloned.Rows[i];
if (string.IsNullOrEmpty(dr["Address - 1"].ToString()) && string.IsNullOrEmpty(dr["Address - 2"].ToString()) && string.IsNullOrEmpty(dr["Address - 3"].ToString()))
{
dr.Delete();
dtCloned.AcceptChanges();
}
else if (string.IsNullOrEmpty(dr["City"].ToString()))
{
dr.Delete();
dtCloned.AcceptChanges();
}
else if (string.IsNullOrEmpty(dr["State"].ToString()))
{
dr.Delete();
dtCloned.AcceptChanges();
}
else if (string.IsNullOrEmpty(dr["Zip + 4 + 2"].ToString()))
{
dr.Delete();
dtCloned.AcceptChanges();
}
else if (dr["Vendor ID"].ToString().Length < 9)
{
dr.Delete();
dtCloned.AcceptChanges();
}
}
if (check > 0)
{
res.code = (int)HttpStatusCode.InternalServerError;
}
else
{
Trace.WriteLine(H_TITLE);
Trace.WriteLine(D_TITLE);
outPayment.Add(H_TITLE);
outPayment.Add(D_TITLE);
string Address1 = string.Empty;
foreach (DataRow row in dtCloned.Rows)
{
Vendor V = new Vendor();
VendorAddress A = new VendorAddress();
Address1 = string.Empty;
foreach (DataColumn dc in dtCloned.Columns)
{
if (!string.IsNullOrEmpty(dc.ColumnName))
{
switch (dc.ColumnName)
{
case "Vendor ID":
V.VendorId = row[dc].ToString();
break;
case "Name":
V.Name = row[dc].ToString();
break;
case "Address - 1":
Address1 = row[dc].ToString().Replace(Convert.ToChar(System.Convert.ToInt32(160)), ' ');
A.Address1 = Address1;
break;
case "Address - 2":
A.Address2 = row[dc].ToString();
break;
case "Address - 3":
A.Address3 = row[dc].ToString();
break;
case "City":
A.City = row[dc].ToString().Trim().TrimEnd(',');
break;
case "State":
A.StateProvince = row[dc].ToString();
break;
case "Zip + 4 + 2":
A.PostalCode = row[dc].ToString();
break;
case "Contact Name":
A.Contact = row[dc].ToString();
break;
case "Phone - 1":
A.PhoneNumber = row[dc].ToString();
break;
}
}
}
outPayment.Add(@"""VR""" + "," + V.ToString());
outPayment.Add(@"""AR""" + "," + A.ToString());
Trace.WriteLine(@"""VR""" + "," + V.ToString());
Trace.WriteLine(@"""AR""" + "," + A.ToString());
}
StringBuilder sb = new StringBuilder();
foreach (var line in outPayment)
{
sb.AppendFormat("{0}{1}", line, Environment.NewLine);
}
//var outputBytes = Encoding.UTF8.GetBytes(sb.ToString());
string DecryptText = AESHelper.EncryptAES(sb.ToString(), Vault.Current.AESKeyBLOB);
//BlobHelper.UploadFile(ConfigHelper.GetConnectionString("storage.payment.conversion"), "stamforddev-export", "vendorlisting.csv", outputBytes);
res.code = (int)HttpStatusCode.OK;
res.data = DecryptText;
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
return(res);
}
// Add some programmatically-generated objects to the data store
// Can write one method, or many methods - your decision
// The important idea is that you check for existing data first
// Call this method from a controller action/method
public bool LoadData()
{
// User name
var user = HttpContext.Current.User.Identity.Name;
// Monitor the progress
bool done = false;
// ############################################################
// RoleClaims
if (ds.RoleClaims.Count() == 0)
{
//Add Roles
ds.RoleClaims.Add(new RoleClaim {
Name = "Manager"
});
ds.RoleClaims.Add(new RoleClaim {
Name = "General Manager"
});
ds.RoleClaims.Add(new RoleClaim {
Name = "Coach"
});
ds.RoleClaims.Add(new RoleClaim {
Name = "Player"
});
ds.RoleClaims.Add(new RoleClaim {
Name = "Referee"
});
ds.RoleClaims.Add(new RoleClaim {
Name = "League Rep"
});
ds.SaveChanges();
done = true;
}
if (ds.Positions.Count() == 0)
{
ds.Positions.Add(new Position {
PositionName = "Left Wing"
});
ds.Positions.Add(new Position {
PositionName = "Right Wing"
});
ds.Positions.Add(new Position {
PositionName = "Centre"
});
ds.Positions.Add(new Position {
PositionName = "Defenseman"
});
ds.Positions.Add(new Position {
PositionName = "Goalie"
});
}
if (ds.Teams.Count() == 0)
{
//Add teams
//Path to the XLSX file
var path = HttpContext.Current.Server.MapPath("~/App_Data/TeamData.xlsx");
//Load workbook into a System.Data.Dataset "sourceData"
var stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
DataSet sourceData = reader.AsDataSet();
reader.Close();
//Worksheet name
string worksheetName;
//Get workseet by its name
worksheetName = "Teams";
var worksheet = sourceData.Tables[worksheetName];
//Conver it to a collection of team add
List <TeamAdd> items = worksheet.DataTableToList <TeamAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
// Double fixDate = double.Parse(item.StartDate);
ds.Teams.Add(Mapper.Map <Team>(item));
}
//save changes
ds.SaveChanges();
//if got this far make done = true
done = true;
}
if (ds.Coaches.Count() == 0)
{
//Add teams
//Path to the XLSX file
var path = HttpContext.Current.Server.MapPath("~/App_Data/CoachData.xlsx");
//Load workbook into a System.Data.Dataset "sourceData"
var stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
DataSet sourceData = reader.AsDataSet();
reader.Close();
//Worksheet name
string worksheetName;
//Get workseet by its name
worksheetName = "Coach";
var worksheet = sourceData.Tables[worksheetName];
//Conver it to a collection of team add
List <CoachAdd> items = worksheet.DataTableToList <CoachAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
var addedCoach = ds.Coaches.Add(Mapper.Map <Coach>(item));
var addToTeam = ds.Teams.SingleOrDefault(t => t.TeamName == item.TeamName);
addToTeam.Coaches.Add(addedCoach);
//save changes in loop to ensure Coaches collection is filled properly
ds.SaveChanges();
}
//if got this far make done = true
done = true;
}
if (ds.Players.Count() == 0)
{
//Add Players
//Path to the XLSX file
var path = HttpContext.Current.Server.MapPath("~/App_Data/PlayerData.xlsx");
//Load workbook into a System.Data.Dataset "sourceData"
var stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
DataSet sourceData = reader.AsDataSet();
reader.Close();
//Worksheet name
string worksheetName;
//Get workseet by its name
worksheetName = "CAN";
var worksheet = sourceData.Tables[worksheetName];
//Conver it to a collection of team add
List <PlayerAdd> items = worksheet.DataTableToList <PlayerAdd>();
var montreal = ds.Teams.SingleOrDefault(t => t.TeamName == "Montreal Canadiens");
//go thorugh collection and add items
foreach (var item in items)
{
var addedPlayer = ds.Players.Add(Mapper.Map <Player>(item));
montreal.Players.Add(addedPlayer);
addedPlayer.TeamName = montreal.TeamName;
}
//save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
//Get workseet by its name
worksheetName = "TOR";
worksheet = sourceData.Tables[worksheetName];
var toronto = ds.Teams.SingleOrDefault(t => t.TeamName == "Toronto Maple Leafs");
//Conver it to a collection of team add
items = worksheet.DataTableToList <PlayerAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
var addedPlayer = ds.Players.Add(Mapper.Map <Player>(item));
toronto.Players.Add(addedPlayer);
addedPlayer.TeamName = toronto.TeamName;
}
//save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
//Get workseet by its name
worksheetName = "BOS";
worksheet = sourceData.Tables[worksheetName];
var boston = ds.Teams.SingleOrDefault(t => t.TeamName == "Boston Bruins");
//Conver it to a collection of team add
items = worksheet.DataTableToList <PlayerAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
var addedPlayer = ds.Players.Add(Mapper.Map <Player>(item));
boston.Players.Add(addedPlayer);
addedPlayer.TeamName = boston.TeamName;
}
//save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
//Get workseet by its name
worksheetName = "NYR";
worksheet = sourceData.Tables[worksheetName];
var newyork = ds.Teams.SingleOrDefault(t => t.TeamName == "New York Rangers");
//Conver it to a collection of team add
items = worksheet.DataTableToList <PlayerAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
var addedPlayer = ds.Players.Add(Mapper.Map <Player>(item));
newyork.Players.Add(addedPlayer);
addedPlayer.TeamName = newyork.TeamName;
}
//save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
//Get workseet by its name
worksheetName = "RED";
worksheet = sourceData.Tables[worksheetName];
var red = ds.Teams.SingleOrDefault(t => t.TeamName == "Detroit Red Wings");
//Conver it to a collection of team add
items = worksheet.DataTableToList <PlayerAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
var addedPlayer = ds.Players.Add(Mapper.Map <Player>(item));
red.Players.Add(addedPlayer);
addedPlayer.TeamName = red.TeamName;
}
//save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
//Get workseet by its name
worksheetName = "CHG";
worksheet = sourceData.Tables[worksheetName];
var chicago = ds.Teams.SingleOrDefault(t => t.TeamName == "Chicago Black Hawks");
//Conver it to a collection of team add
items = worksheet.DataTableToList <PlayerAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
var addedPlayer = ds.Players.Add(Mapper.Map <Player>(item));
chicago.Players.Add(addedPlayer);
addedPlayer.TeamName = chicago.TeamName;
}
//save changes
ds.SaveChanges();
//if got this far make done = true
done = true;
}
return(done);
}
public ActionResult UploadExcel(HttpPostedFileBase upload)
{
if (ModelState.IsValid)
{
if (upload != null && upload.ContentLength > 0)
{
// ExcelDataReader works with the binary Excel file, so it needs a FileStream
// to get started. This is how we avoid dependencies on ACE or Interop:
Stream stream = upload.InputStream;
// We return the interface, so that
IExcelDataReader reader = null;
if (upload.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (upload.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
ModelState.AddModelError("File", "This file format is not supported");
return(View());
}
reader.IsFirstRowAsColumnNames = true;
DataSet result = reader.AsDataSet();
reader.Close();
var list = result.Tables[0].AsEnumerable()
.Select(q => new
{
SuperVisorOfSupplyCode = q.Field <string>("کد سرپرست توزیع"),
SuperVisorOfSaleCode = q.Field <string>("کد سرپرست فروش"),
VisitorCode = q.Field <string>("کد ویزیتور"),
DelivererCode = q.Field <string>("کد تحویل دهنده"),
CustomerCode = q.Field <string>("کد مشتری"),
CustomerName = q.Field <string>("نام مشتری"),
TeamOfSale = q.Field <string>("تیم فروش"),
Price = q.Field <string>("مبلغ فاکتور"),
BranchCode = q.Field <string>("کد شعبه"),
CreatedDate = q.Field <DateTime>("تاریخ"),
});
var listUsers = database.USERS;
var list2 = from p in list
select new BaniChav_Total
{
ID = Guid.NewGuid(),
SuperVisorOfSupplyCode = p.SuperVisorOfSupplyCode,
SuperVisorOfSaleCode = p.SuperVisorOfSaleCode,
VisitorCode = p.VisitorCode,
Price = Convert.ToInt32(p.Price),
CreatedDate = p.CreatedDate,
BranchCode = p.BranchCode,
CustomerCode = p.CustomerCode,
CustomerName = p.CustomerName,
TeamOfSale = p.TeamOfSale,
DelivererID = (from u in listUsers where (u.Code == p.DelivererCode) select u.User_Id).FirstOrDefault(),
SuperVisorOfSupplyID = (from u in listUsers where (u.Code == p.SuperVisorOfSupplyCode) select u.User_Id).FirstOrDefault(),
SuperVisorOfSaleID = (from u in listUsers where (u.Code == p.SuperVisorOfSaleCode) select u.User_Id).FirstOrDefault(),
VisitorID = (from u in listUsers where (u.Code == p.VisitorCode) select u.User_Id).FirstOrDefault(),
DelivererCode = p.DelivererCode,
};
database.BaniChav_Total.AddRange(list2.ToList());
database.SaveChanges();
ViewBag.data = list2;
return(View(result.Tables[0]));
}
else
{
ModelState.AddModelError("File", "Please Upload Your file");
}
}
return(View());
}
public ActionResult ImportToExcel()
{
DataSet ds = new DataSet();
try
{
if (Request.Files.Count > 0)
{
var file = Request.Files[0];
if (file != null && file.ContentLength > 0)
{
string fileExtension = System.IO.Path.GetExtension(file.FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = Server.MapPath("~/App_Data/");
//if (System.IO.File.Exists(fileLocation))
//{
// System.IO.File.Delete(fileLocation);
//}
string strDay = string.Format("{0}_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), file.FileName);
string pathString = System.IO.Path.Combine(fileLocation, strDay);
file.SaveAs(pathString);
// đọc file excel tại đây
FileStream stream = System.IO.File.Open(pathString, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
ds = excelReader.AsDataSet();
if (ds == null)
{
return(null);
}
//5. Data Reader methods
excelReader.Read();
//while (excelReader.Read())
//{
//}
int countc = ds.Tables[0].Rows.Count;
for (int i = 2; i < countc; i++)
{
var c1 = ""; string c2 = null; string c3 = ""; string c4 = ""; string c5 = ""; string c6 = ""; string c7 = ""; string c8 = ""; string c9 = ""; string c10 = ""; string c11 = ""; string c12 = ""; string c13 = ""; string c14 = ""; string c15 = "";
var _date = ds.Tables[0].Rows[i][0] != null ? ds.Tables[0].Rows[i][0].ToString() : "";
try
{
c1 = _date != "" ? DateTime.ParseExact(_date, "dd/MM/yyyy", null).ToString("yyyy-MM-dd HH:mm") : "";
}
catch
{
c1 = "";
}
c2 = ds.Tables[0].Rows[i][1] != null ? ds.Tables[0].Rows[i][1].ToString() : "";
c3 = ds.Tables[0].Rows[i][2] != null ? ds.Tables[0].Rows[i][2].ToString() : "";
c4 = ds.Tables[0].Rows[i][3] != null ? ds.Tables[0].Rows[i][3].ToString() : "";
var _date2 = ds.Tables[0].Rows[i][4] != null ? ds.Tables[0].Rows[i][4] : null;
if (_date2 != null)
{
try
{
c5 = DateTime.ParseExact(_date2.ToString(), "dd/MM/yyyy hh:mm tt", null).ToString("yyyy-MM-dd hh:mm");
}
catch
{
c5 = "";
}
}
var _date3 = ds.Tables[0].Rows[i][5] != null ? ds.Tables[0].Rows[i][5] : null;
if (_date3 != null)
{
try
{
c6 = DateTime.ParseExact(_date3.ToString(), "dd/MM/yyyy hh:mm tt", null).ToString("yyyy-MM-dd hh:mm");
}
catch
{
c6 = "";
}
}
c7 = ds.Tables[0].Rows[i][6] != null ? ds.Tables[0].Rows[i][6].ToString() : null;
c8 = ds.Tables[0].Rows[i][7] != null ? ds.Tables[0].Rows[i][7].ToString() : null;
c9 = ds.Tables[0].Rows[i][8] != null ? ds.Tables[0].Rows[i][8].ToString() : null;
c10 = ds.Tables[0].Rows[i][9] != null ? ds.Tables[0].Rows[i][9].ToString() : null;
c11 = ds.Tables[0].Rows[i][10] != null ? ds.Tables[0].Rows[i][10].ToString() : null;
c12 = ds.Tables[0].Rows[i][11] != null ? ds.Tables[0].Rows[i][11].ToString() : null;
c13 = ds.Tables[0].Rows[i][12] != null ? ds.Tables[0].Rows[i][12].ToString() : null;
c14 = ds.Tables[0].Rows[i][13] != null ? ds.Tables[0].Rows[i][13].ToString() : null;
c15 = ds.Tables[0].Rows[i][14] != null ? ds.Tables[0].Rows[i][14].ToString() : null;
var bcheckempty = c1 == "" && c2 == "" && c3 == "" && c4 == "";
if (!bcheckempty)
{
try
{
string sql2 = @"INSERT INTO invoices(date, customer_name, sex, phone, time_to_pick, time_to_pay, pickup, paypoints, so_cho, form, driver, price, vat, sum, note) SELECT '" + c1 + "',N'" + c2 + "',N'" + c3 + "','" + c4 + "','" + c5 + "','" + c6 + "',N'" + c7 + "',N'" + c8 + "','" + c9 + "',N'" + c10 + "',N'" + c11 + "','" + c12 + "','" + c13 + "','" + c14 + "',N'" + c15 + "' WHERE NOT EXISTS (SELECT date, customer_name, sex, phone FROM invoices WHERE date = '" + c1 + "' AND customer_name = N'" + c2 + "' AND sex = N'" + c3 + "' AND phone = '" + c4 + "')";
int noOfRowInserted = db.Database.ExecuteSqlCommand(sql2);
}
catch
{
continue;
}
}
else
{
continue;
}
if (i == countc - 1)
{
excelReader.Close();
break;
}
}
//6. Free resources (IExcelDataReader is IDisposable)
}
}
}
TempData["Updated"] = "Thêm dữ liệu thành công";
}
catch (Exception ex)
{
TempData["Error"] = "Đã xảy ra lỗi khi thêm mới. " + ex.ToString();
}
return(RedirectToAction("bangke"));
}
public ActionResult ExcelUpload()
{
var Message = "Success";
try
{
var file = Request.Form.Files[0];
var folderName = Path.Combine("Resources", "TempFile");
var pathToSave = Path.Combine(Directory.GetCurrentDirectory(), folderName);
if (file.Length > 0)
{
var fileName = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"');
var fullPath = Path.Combine(pathToSave, fileName);
var dbPath = Path.Combine(folderName, fileName);
IExcelDataReader reader = null;
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream); //Copy to directory
if (file.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (file.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
DataSet excelRecords = reader.AsDataSet();
reader.Close();
var finalRecords = excelRecords.Tables[0];
for (int i = 0; i < finalRecords.Rows.Count; i++)
{
Customer oCustomer = new Customer();
oCustomer.Name = finalRecords.Rows[i][0].ToString();
oCustomer.Profession = finalRecords.Rows[i][1].ToString();
oCustomer.MonthlyIncome = Convert.ToDouble(finalRecords.Rows[i][2].ToString());
oCustomer.EducatonLevel = EducationLavel(finalRecords.Rows[i][3].ToString());
EnumSection myStatus;
Enum.TryParse(finalRecords.Rows[i][4].ToString(), out myStatus); //Convert string to enum
oCustomer.Section = Convert.ToInt16(myStatus); //Convert enum to int
oCustomer.Latitude = Convert.ToDouble(finalRecords.Rows[i][5]);
oCustomer.Longitude = Convert.ToDouble(finalRecords.Rows[i][6]);
_oCustomer = oCustomer.Save(oCustomer);
}
}
return(Ok(new { Message }));
}
else
{
Message = "Sorry, There is No File";
return(Ok(new { Message }));
}
}
catch (Exception e)
{
Message = e.Message;
}
return(Ok(new { Message }));
}
public Decimal Import_file_excel(HttpPostedFileBase fileImport)
{
try
{
DataSet _ds = new DataSet();
if (fileImport != null && fileImport.ContentLength > 0)
{
if (!fileImport.FileName.EndsWith(".xls") && !fileImport.FileName.EndsWith(".xlsx"))
{
return(-3);
}
else
{
Stream stream = fileImport.InputStream;
IExcelDataReader reader = null;
if (fileImport.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (fileImport.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
_ds = reader.AsDataSet();
reader.Close();
}
}
if (_ds != null)
{
_ds.Tables[0].Rows[0].Delete();//xóa dòng title
_ds.AcceptChanges();
if (_ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in _ds.Tables[0].Rows)
{
SupplierInfo obj_info = new SupplierInfo();
obj_info.Name = dr[0] == null ? "" : dr[0].ToString();
obj_info.Code = dr[1] == null ? "" : dr[1].ToString();
obj_info.Phone = dr[2] == null ? "" : dr[2].ToString();
obj_info.Fax = dr[3] == null ? "" : dr[3].ToString();
obj_info.Email = dr[4] == null ? "" : dr[4].ToString();
obj_info.Notes = dr[5] == null ? "" : dr[5].ToString();
obj_info.Created_date = DateTime.Now;
obj_info.Created_by = "";
SupplierBL _bl = new SupplierBL();
_bl.Insert(obj_info);
}
}
else
{
return(-4);//không có dữ liệu
}
}
return(1);
}
catch (Exception ex)
{
NaviCommon.Common.log.Error(ex.ToString());
return(-1);
}
}
private void newListToolStripMenuItem_Click(object sender, EventArgs e)
{
if (openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
FileStream stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read);
var ext = Path.GetExtension(openFileDialog.FileName);
if (ext == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (ext == ".xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (excelReader == null) // || !excelReader.IsValid)
{
MessageBox.Show("Selected file is not valid Excel file");
return;
}
/*
* excelReader.IsFirstRowAsColumnNames = true;
* result = excelReader.AsDataSet();
* mslGridControl.DataSource = result.Tables[0];
* mslGridView.OptionsView.ColumnAutoWidth = false;
*/
CultureInfo culture = new CultureInfo("en-US");
this.opsDataSet.ZMSL.Clear();
int i = 0;
excelReader.Read();
while (excelReader.Read())
{
i++;
OpsDataSet.ZMSLRow row = opsDataSet.ZMSL.NewZMSLRow();
//row.ID = i++;
// var bbb = excelReader.GetOrdinal("Template"); Calismiyor
row.TEMPLATE = excelReader.GetString(0);
//var aaa = excelReader.GetString(1);
//var ccc = Convert.ToDateTime(excelReader.GetValue(1), culture);
//var bbb = excelReader.GetDateTime(1);
row.LOADINGDATE = Convert.ToDateTime(excelReader.GetValue(1), culture); //excelReader.GetDateTime(1); //.ToString(); //Convert.ToDateTime(excelReader.GetString(1).Substring(0, 10));
//if (excelReader.GetValue(1).GetType().Name != "DateTime")
// MessageBox.Show(excelReader.GetValue(1).GetType().Name);
//row.Status = excelReader.GetString(2);
row.PROTOCOL = excelReader.GetString(3);
row.SPONSOR = excelReader.GetString(4);
row.STUDY = excelReader.GetString(5);
row.SITE = excelReader.GetString(6);
row.CONSIGNEENAME = excelReader.GetString(18);
row.PICKUPNAME = excelReader.GetString(50);
row.PICKUPCOMPANY = excelReader.GetString(51);
row.PICKUPDEPARTMENT = excelReader.GetString(52);
row.PICKUPCITY = excelReader.GetString(55);
row.PICKUPPHONE = excelReader.GetString(59);
opsDataSet.ZMSL.AddZMSLRow(row);
}
excelReader.Close();
}
}
public Decimal Import_file_excel(HttpPostedFileBase fileImport)
{
try
{
DataSet _ds = new DataSet();
if (fileImport != null && fileImport.ContentLength > 0)
{
if (!fileImport.FileName.EndsWith(".xls") && !fileImport.FileName.EndsWith(".xlsx"))
{
return(-3);
}
else
{
Stream stream = fileImport.InputStream;
IExcelDataReader reader = null;
if (fileImport.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (fileImport.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
_ds = reader.AsDataSet();
reader.Close();
}
}
if (_ds != null)
{
_ds.Tables[0].Rows[0].Delete();//xóa dòng title
_ds.AcceptChanges();
if (_ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in _ds.Tables[0].Rows)
{
Product_Info _obj = new Product_Info();
_obj.Product_Code = dr[0] == null ? "" : dr[0].ToString();
_obj.Product_Name = dr[1] == null ? "" : dr[1].ToString();
_obj.Bravo_Code = dr[2] == null ? "" : dr[2].ToString();
_obj.Note = dr[3] == null ? "" : dr[3].ToString();
string Product_Group_ID = dr[4] == null ? "" : dr[4].ToString();
if (Product_Group_ID != "")
{
_obj.Product_Group_Id = Convert.ToDecimal(Product_Group_ID);
}
if (_obj.Product_Code != "" && _obj.Bravo_Code != "")
{
ProductBL.Product_Insert(_obj);
}
}
}
else
{
return(-4);//không có dữ liệu
}
}
return(1);
}
catch (Exception ex)
{
NaviCommon.Common.log.Error(ex.ToString());
return(-1);
}
}
protected void btnSubir_Click(object sender, EventArgs e)
{
if (!CargaArchivo.HasFile)
{
MostrarExcepcion(Constantes.MENSAJE_CAMPO_REQUERIDO_TITULO, "Es requerido subir un archivo");
return;
}
String ext = System.IO.Path.GetExtension(CargaArchivo.PostedFile.FileName);
log.Info(String.Format("El usuario: [{0}] ha cargado el archivo de excel [{1}] para su insercion masiva", UsuarioActual.Nombre, CargaArchivo.PostedFile.FileName));
if (!ext.ToLower().Equals(Constantes.EXTENSION_EXCEL_97) && !ext.ToLower().Equals(Constantes.EXTENSION_EXCEL_2007))
{
MostrarExcepcion(Constantes.MENSAJE_CAMPO_REQUERIDO_TITULO, "Solo son permitidos los archivos de Excel");
return;
}
CajaRegistro.Items.Clear();
CajaRegistro.Items.Add(NuevoListItem("Iniciando Proceso", Constantes.TEXTO_BLANCO));
try
{
IExcelDataReader excelReader = null;
if (ext.ToLower().Equals(Constantes.EXTENSION_EXCEL_2007))
{
CajaRegistro.Items.Add(NuevoListItem("Leyendo Archivo de Excel XLSX 2007", Constantes.TEXTO_BLANCO));
excelReader = ExcelReaderFactory.CreateOpenXmlReader(CargaArchivo.PostedFile.InputStream);
}
else
{
if (ext.ToLower().Equals(Constantes.EXTENSION_EXCEL_97))
{
CajaRegistro.Items.Add(NuevoListItem("Leyendo Archivo de Excel XLS 97-2003", Constantes.TEXTO_BLANCO));
excelReader = ExcelReaderFactory.CreateBinaryReader(CargaArchivo.PostedFile.InputStream);
}
}
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
int i = 0;
while (excelReader.Read())
{
i++;
String nomina = excelReader.GetString(0);
String nombre = excelReader.GetString(1);
String plaza = excelReader.GetString(2);
int numeroNumina = 0;
Boolean encontroError = false;
if (i == 1)
{
encontroError = true;
}
if (!Int32.TryParse(nomina, out numeroNumina) && !encontroError)
{
numeroNumina = 0;
CajaRegistro.Items.Add(NuevoListItem(String.Format("ERROR FILA: [{0}], la columna de Nomina no es numerica", i), Constantes.TEXTO_BLANCO));
encontroError = true;
}
if (!ValidarObjeto(nombre) && !encontroError)
{
CajaRegistro.Items.Add(NuevoListItem(String.Format("ERROR FILA: [{0}], la columna carece de nombre ", i), Constantes.TEXTO_BLANCO));
encontroError = true;
}
if (!ValidarObjeto(plaza) && !encontroError)
{
CajaRegistro.Items.Add(NuevoListItem(String.Format("ERROR FILA: [{0}], la columna carece de plaza", i), Constantes.TEXTO_BLANCO));
encontroError = true;
}
if (numeroNumina <= 0 && !encontroError)
{
CajaRegistro.Items.Add(NuevoListItem(String.Format("ERROR FILA: [{0}], la columna carece de numero nomina", i), Constantes.TEXTO_BLANCO));
encontroError = true;
}
if (!encontroError)
{
try
{
Empleado obj = empleadoDao.GetByNumeroEmpleado(numeroNumina);
Plaza objPlaza = plazaDao.GetByNombre(plaza);
if (obj != null)
{
CajaRegistro.Items.Add(NuevoListItem(String.Format("ADV FILA: [{0}], este numero de nomina ya existe, se actualizara el registro", i), Constantes.TEXTO_BLANCO));
}
if (objPlaza == null)
{
CajaRegistro.Items.Add(NuevoListItem(String.Format("ADV FILA: [{0}], la plaza [{1}] NO existe en la base de datos.", i, plaza), Constantes.TEXTO_BLANCO));
}
if (obj == null)
{
obj = new Empleado();
obj.Usuario_creado_por = UsuarioActual;
obj.Usuario_cve_usuario_alta = UsuarioActual;
}
obj.Usuario_modificado_por = UsuarioActual;
obj.FechaModificacion = DateTime.Now;
obj.Nombre = nombre;
obj.NoEmpleado = numeroNumina;
if (objPlaza != null)
{
obj.Plaza = objPlaza;
}
empleadoDao.SaveOrUpdate(obj);
CajaRegistro.Items.Add(NuevoListItem(String.Format("OK FILA: [{0}], registro guardado correctamente", i), Constantes.TEXTO_BLANCO));
}
catch (Exception ex)
{
log.Error(String.Format("Error al momento de intentar insertar un empleado importado de Excel. Mensaje: [{0}]", ex.Message));
CajaRegistro.Items.Add(NuevoListItem(String.Format("ERROR FILA: [{0}]. Error en el proceso. Detalles: [{1}]", i, ex.Message), Constantes.TEXTO_BLANCO));
}
}
}
CajaRegistro.Items.Add(NuevoListItem(String.Format("PROCESO OK: Se completaron: [{0}] registros", i), Constantes.TEXTO_BLANCO));
excelReader.Close();
}
catch (Exception ex)
{
CajaRegistro.Items.Add(NuevoListItem(String.Format("ERROR: Error en el proceso. Detalles: [{0}] ", ex.Message), Constantes.TEXTO_BLANCO));
log.Error(String.Format("Error al momento de intentar importar la hoja de excel de Empleados. Mensaje [{0}]", ex.Message));
ManejarExcepcion(ex);
}
}
private static void ReadExcelFile()
{
string filePath = System.AppDomain.CurrentDomain.BaseDirectory;
filePath = Path.Combine(filePath, "agemko.xlsx");
if (File.Exists(filePath))
{
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
//5. Data Reader methods
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
if (result.Tables.Count > 0)
{
DataTable tbl = result.Tables[0];
for (int x = 5; x > -1; x--)
{
tbl.Rows.RemoveAt(x);
}
tbl.AcceptChanges();
foreach (DataRow row in tbl.Rows)
{
selectedBusiness = new Businesses();
foreach (DataColumn column in tbl.Columns)
{
switch (column.Ordinal)
{
case 0:
RegistryType(row[column]);
break;
case 1:
IndividualCategory(row[column]);
break;
case 3:
Status(row[column]);
break;
case 9:
MainActivity(row[column]);
break;
case 11:
Region(row[column]);
break;
case 12:
RegionalUnity(row[column.Ordinal - 1], row[column]);
break;
case 13:
Municipality(row[column.Ordinal - 1], row[column]);
break;
case 15:
Representative(row[column]);
break;
}
}
Business(row);
}
}
}
}
/// <summary>
/// Opens the specified file
/// </summary>
/// <param name="fileName">Path to file.</param>
/// <returns>Feature set from file data.</returns>
/// <exception cref="Exception">Throws if no sheets in the file, or
/// Latitude or Longitude column not found.</exception>
public IDataSet Open(string fileName)
{
var fs = new FeatureSet
{
Name = Path.GetFileNameWithoutExtension(fileName),
Filename = fileName
};
var extension = Path.GetExtension(fileName);
IExcelDataReader reader = null;
var stream = File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.Read);
try
{
switch (extension)
{
case ".xls":
reader = ExcelReaderFactory.CreateBinaryReader(stream);
break;
case ".xlsx":
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
break;
default:
goto case ".xls";
}
reader.IsFirstRowAsColumnNames = true;
var ds = reader.AsDataSet();
if (ds.Tables.Count == 0)
{
throw new Exception("There is no sheets in the file");
}
// Find first table (sheet) with Latitude/Longitude columns.
DataTable table = null;
int latColumnIndex = -1;
int lngColumnIndex = -1;
for (int i = 0; i < ds.Tables.Count; i++)
{
table = ds.Tables[i];
latColumnIndex = table.Columns.IndexOf("Latitude");
lngColumnIndex = table.Columns.IndexOf("Longitude");
if (latColumnIndex >= 0 && lngColumnIndex >= 0)
{
break;
}
}
if (latColumnIndex == -1 || lngColumnIndex == -1)
{
throw new Exception("Latitude or Longitude column not found");
}
Debug.Assert(table != null);
for (int i = 0; i < table.Columns.Count; i++)
{
string sFieldName = table.Columns[i].ColumnName;
Type type = table.Columns[i].DataType;
int uniqueNumber = 1;
string uniqueName = sFieldName;
while (fs.DataTable.Columns.Contains(uniqueName))
{
uniqueName = sFieldName + uniqueNumber;
uniqueNumber++;
}
fs.DataTable.Columns.Add(new DataColumn(uniqueName, type));
}
reader.Read();
while (reader.Read())
{
var lat = reader.GetDouble(latColumnIndex);
var lng = reader.GetDouble(lngColumnIndex);
IGeometry geometry = new Point(lng, lat);
IFeature feature = new Feature(geometry);
feature.DataRow = fs.DataTable.NewRow();
for (int j = 0; j < reader.FieldCount; j++)
{
object value = reader.GetValue(j);
if (value == null)
{
value = DBNull.Value;
}
feature.DataRow[j] = value;
}
fs.Features.Add(feature);
}
}finally
{
if (reader != null)
{
reader.Close();
}
stream.Close();
}
fs.Projection = ProjectionInfo.FromEsriString(Resources.wgs_84_esri_string);
return(fs);
}
private static void ReadExcelFileForSqlScript()
{
AGEMKOContext _context = new AGEMKOContext();
string filePath = System.AppDomain.CurrentDomain.BaseDirectory;
filePath = Path.Combine(filePath, "NewReceivedExcel.xlsx");
DataSet result = new DataSet();
if (File.Exists(filePath))
{
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
//5. Data Reader methods
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
foreach (DataRow row in result.Tables[0].Rows)
{
if (!row.IsNull(10))
{
MainTable record = new MainTable();
string katigoria = row.IsNull(1) ? string.Empty : row[1].ToString();
string katastasi = row.IsNull(3) ? string.Empty : row[3].ToString();
string afm = row.IsNull(5) ? string.Empty : row[5].ToString();
string epwnumia = row[6].ToString().Trim();
string diakritosTitlos = row.IsNull(7) ? string.Empty : row[7].ToString();
string drastiriotita = row.IsNull(9) ? string.Empty : row[9].ToString();
string address = row[10].ToString().Trim();
string email = row.IsNull(14) ? string.Empty : row[14].ToString();
DateTime year = row.IsNull(16) ? DateTime.Now : Convert.ToDateTime(row[16].ToString());
string telephone = row.IsNull(14) ? string.Empty : row[18].ToString().Replace(',', '|');
record.Catigoria = katigoria.Trim();
record.Katastasi = katastasi.Trim();;
record.Afm = afm.Trim();
record.Epwnumia = epwnumia.Trim();
record.DiakritosTitlos = diakritosTitlos.Trim();
record.Drastiriotita = drastiriotita.Trim();
record.Address = address.Trim();
record.Email = email.Trim();
record.Year = year;
record.Telephone = telephone;
_context.MainTable.Add(record);
_context.SaveChanges();
}
}
}
DataSet result2 = new DataSet();
string filePath2 = System.AppDomain.CurrentDomain.BaseDirectory;
filePath2 = Path.Combine(filePath2, "All.xlsx");
if (File.Exists(filePath2))
{
FileStream stream = File.Open(filePath2, FileMode.Open, FileAccess.Read);
//Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
result2 = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
//5. Data Reader methods
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
foreach (DataRow row in result2.Tables[0].Rows)
{
string addresskey = row[0].ToString();
string lat = row[1].ToString();
string longitude = row[2].ToString();
if (!row.IsNull(0))
{
MainTable record = _context.MainTable.FirstOrDefault(x => x.Address.Equals(row[0].ToString()));
if (record != default(MainTable))
{
record.Latitude = lat;
record.Longitude = longitude;
_context.SaveChanges();
}
else
{
if (!row.IsNull(5) && !row.IsNull(6))
{
record = _context.MainTable.FirstOrDefault(x => x.Address.Equals(row[6].ToString().Trim()) && x.DiakritosTitlos.Equals(row[5].ToString().Trim()));
if (record != default(MainTable))
{
record.Latitude = lat;
record.Longitude = longitude;
_context.SaveChanges();
}
}
}
}
}
}
}
/// <summary>
/// Reads the first row to determine which column is located on which column-index.
/// After that HostEntries will be created using those indexes and added to the internal
/// list of HostEntries.
/// </summary>
private void parse(IExcelDataReader reader)
{
int ipIndex = -1;
int urlIndex = -1;
int protocolIndex = -1;
int rankingIndex = -1;
int fingerPrintIndex = -1;
int expirationIndex = -1;
int protocolVersionsIndex = -1;
int RC4Index = -1;
int beastIndex = -1;
int forwardSecrecyIndex = -1;
int heartbleedIndex = -1;
int signatureAlgoIndex = -1;
int poodleIndex = -1;
int extendedValidIndex = -1;
int openSSLCCSIndex = -1;
int HTTPServerSigIndex = -1;
int serverHostnameIndex = -1;
int _3DESCipherIndex = -1;
// Get headers
reader.Read();
int columnIndex = 0;
try
{
while (reader.GetString(columnIndex) != null)
{
string cmp = reader.GetString(columnIndex);
#region Column finding
if (cmp.Equals("IP") && ipIndex == -1) ipIndex = columnIndex;
else if (cmp.Contains("URL") && urlIndex == -1) urlIndex = columnIndex;
else if (cmp.ToLower().Contains("protocol versions") && protocolVersionsIndex == -1)
protocolVersionsIndex = columnIndex;
else if (cmp.Contains("RC4") && RC4Index == -1) RC4Index = columnIndex;
else if (cmp.ToLower().Contains("ranking") && rankingIndex == -1)
rankingIndex = columnIndex;
else if (cmp.ToLower().Equals("protocol") && protocolIndex == -1)
protocolIndex = columnIndex;
else if (cmp.ToLower().Contains("fingerprint") && fingerPrintIndex == -1)
fingerPrintIndex = columnIndex;
else if (cmp.ToLower().Contains("expiration") && expirationIndex == -1)
expirationIndex = columnIndex;
else if (cmp.ToLower().Contains("beast") && beastIndex == -1)
beastIndex = columnIndex;
else if (cmp.ToLower().Contains("forward secrecy") && forwardSecrecyIndex == -1)
forwardSecrecyIndex = columnIndex;
else if (cmp.ToLower().Contains("heartbleed") && heartbleedIndex == -1)
heartbleedIndex = columnIndex;
else if (cmp.ToLower().Contains("signature algorithm") && signatureAlgoIndex == -1)
signatureAlgoIndex = columnIndex;
else if (cmp.ToLower().Contains("poodle") && poodleIndex == -1)
poodleIndex = columnIndex;
else if (cmp.ToLower().Contains("extended validation") && extendedValidIndex == -1)
extendedValidIndex = columnIndex;
else if (cmp.ToLower().Contains("openssl ccs") && openSSLCCSIndex == -1)
openSSLCCSIndex = columnIndex;
else if (cmp.ToLower().Contains("http server sig") && HTTPServerSigIndex == -1)
HTTPServerSigIndex = columnIndex;
else if (cmp.ToLower().Contains("server host name") && serverHostnameIndex == -1)
serverHostnameIndex = columnIndex;
else if (cmp.ToLower().Contains("3des cipher presence") && _3DESCipherIndex == -1)
_3DESCipherIndex = columnIndex;
else
{
_customAttributes[columnIndex] = cmp;
}
#endregion
columnIndex += 1;
}
}
catch (Exception ex)
{
Debug.WriteLine(string.Format("Excel header reading touched outer bounds: {0}", ex.Message));
}
// Get rows and add them as children of each header
while (reader.Read())
{
HostEntry h = new HostEntry(getColumn(reader, urlIndex),
getColumn(reader, protocolIndex));
h.SetIP(getColumn(reader, ipIndex));
h.SetRanking(getColumn(reader, rankingIndex));
h.SetFingerPrintCert(getColumn(reader, fingerPrintIndex));
h.SetExpirationDate(getColumn(reader, expirationIndex));
h.SetProtocolVersions(getColumn(reader, protocolVersionsIndex));
h.SetBeastVulnerarbility(getColumn(reader, beastIndex));
h.SetForwardSecrecy(getColumn(reader, forwardSecrecyIndex));
h.SetHeartbleedVulnerability(getColumn(reader, heartbleedIndex));
h.SetSignatureAlgorithm(getColumn(reader, signatureAlgoIndex));
h.SetPoodleVulnerability(getColumn(reader, poodleIndex));
h.SetExtendedValidation(getColumn(reader, extendedValidIndex));
h.SetOpenSSLCCSVulnerable(getColumn(reader, openSSLCCSIndex));
h.SetHTTPServerSignature(getColumn(reader, HTTPServerSigIndex));
h.SetServerHostName(getColumn(reader, serverHostnameIndex));
h.Set3DESPresence(getColumn(reader, _3DESCipherIndex));
foreach (DictionaryEntry entry in _customAttributes)
{
h.AddCustomAttribute((string) entry.Value,
getColumn(reader, (int) entry.Key));
}
if (!h.IsEmpty()) entries.Add(h);
}
reader.Close();
ParserDelegator.CallOnParseComplete();
}
public static void test(string testloc)
{
AllStatsRow stat;
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);
SqlCommand command = new SqlCommand("", conn);
//SqlDataReader read = null;
FileStream stream = File.Open(testloc, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(stream);
DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = true,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true,
FilterRow = (rowReader) =>
{
return(true);
}
}
}
);
reader.Close();
//need to remove the top three rows
result.Tables[0].Rows.RemoveAt(0);
result.Tables[0].Rows.RemoveAt(0);
result.Tables[0].Rows.RemoveAt(0);
List <Ticket> ticket_list = new List <Ticket>();
Ticket temp;
foreach (DataRow r in result.Tables[0].Rows)
{
temp = new Ticket(r[1].ToString(), Convert.ToDateTime(r[3]),
((r[1].ToString() == "Closed" || r[1].ToString() == "Cancelled") ? Convert.ToDateTime(r[4]) : DateTime.MinValue),
((DateTime.Now - Convert.ToDateTime(r[3].ToString())).Days), r[0].ToString(),
"NULL", r[2].ToString(), r[5].ToString());
ticket_list.Add(temp);
}
//info manipulations
int open_c = 0;
int ass_c = 0;
int seven_create = 0;
int thirt_create = 0;
int ninet_create = 0;
int seven_close = 0;
int thirt_close = 0;
int nint_close = 0;
int seven_age = 0;
int eight_age = 0;
int fifteen_age = 0;
int twenty_age = 0;
int thirt_age = 0;
foreach (Ticket tick in ticket_list)
{
if (tick.Age < 8)
{
seven_create++;
}
if (tick.Age < 31)
{
thirt_create++;
}
if (tick.Age < 91)
{
ninet_create++;
}
if (tick.Status == "Closed" || tick.Status == "Cancelled")
{
if (tick.Age < 8)
{
seven_close++;
}
if (tick.Age < 31)
{
thirt_close++;
}
if (tick.Age < 91)
{
nint_close++;
}
}
else
{
open_c++;
if (tick.Age < 8)
{
seven_age++;
}
else if (tick.Age >= 8 && tick.Age < 15)
{
eight_age++;
}
else if (tick.Age >= 15 && tick.Age < 22)
{
fifteen_age++;
}
else if (tick.Age >= 22 && tick.Age < 31)
{
twenty_age++;
}
else
{
thirt_age++;
}
if (tick.AssignedName != "Unassigned")
{
ass_c++;
}
}
}
//stat = new AllStatsRow( new DateTime(2018, 2, 21).ToString("MM-dd-yy"), open_c, ass_c, seven_create, thirt_create, ninet_create, seven_close, thirt_close, nint_close, seven_age, eight_age, fifteen_age, twenty_age, thirt_age);
stat = new AllStatsRow(DateTime.Now.ToString("MM-dd-yy"), open_c, ass_c, seven_create, thirt_create, ninet_create, seven_close, thirt_close, nint_close, seven_age, eight_age, fifteen_age, twenty_age, thirt_age);
try
{
Console.WriteLine("Inserting to Velocity table");
conn.Open();
command = new SqlCommand("Velocity_Insert", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
//insert information from stat
command.Parameters.Add("@reportdate", SqlDbType.DateTime).Value = stat.Report_Date;
command.Parameters.Add("@opencount", SqlDbType.Int).Value = stat.Open_count;
command.Parameters.Add("@assignedcount", SqlDbType.Int).Value = stat.Assigned_count;
command.Parameters.Add("@sevendaycreate", SqlDbType.Int).Value = stat.Seven_Day_Created_Count;
command.Parameters.Add("@thirtydaycreate", SqlDbType.Int).Value = stat.Thirty_Day_Created_Count;
command.Parameters.Add("@ninetydaycreate", SqlDbType.Int).Value = stat.Ninety_Day_Created_Count;
command.Parameters.Add("@sevendayclose", SqlDbType.Int).Value = stat.Seven_Day_Closed_Count;
command.Parameters.Add("@thirtydayclose", SqlDbType.Int).Value = stat.Thirty_Day_Closed_Count;
command.Parameters.Add("@ninetydayclose", SqlDbType.Int).Value = stat.Ninety_Day_Closed_Count;
command.Parameters.Add("@sevenage", SqlDbType.Int).Value = stat.Less_Than_Seven_Days_Old;
command.Parameters.Add("@eightage", SqlDbType.Int).Value = stat.Eight_To_Fourteen_Days_Old;
command.Parameters.Add("@fifteenage", SqlDbType.Int).Value = stat.Fifteen_To_Twenty_Days_Old;
command.Parameters.Add("@twentdayage", SqlDbType.Int).Value = stat.TwentyTwo_To_Thirty_Days_Old;
command.Parameters.Add("@thirtydayage", SqlDbType.Int).Value = stat.More_Than_Thirty_Day_Old;
int tp = command.ExecuteNonQuery();
//means that one row was not affected on the one row insert, therefore there was a error
if (tp != -1)
{
throw new Exception("Insert Velocity did not insert correctly");
}
Console.WriteLine("Insert to Velocity table successfully completed");
conn.Close();
//Run the snapshot clear
Console.WriteLine("Clearing snapshot");
conn.Open();
command = new SqlCommand("Ticket_Clear", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
////this should have a check around it after the first couple times to make sure its fully cleared out
command.ExecuteNonQuery();
conn.Close();
Console.WriteLine("Snapshot cleared");
//insert for the ticket snap shot
Console.WriteLine("Inserting snapshot");
foreach (Ticket t in ticket_list)
{
conn.Open();
command = new SqlCommand("Ticket_Insert", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("@assignedname", SqlDbType.VarChar).Value = t.AssignedName;
command.Parameters.Add("@stat", SqlDbType.VarChar).Value = t.Status;
command.Parameters.Add("@prior", SqlDbType.VarChar).Value = t.Priority;
command.Parameters.Add("@dater", SqlDbType.DateTime).Value = t.Requested;
command.Parameters.Add("@support", SqlDbType.VarChar).Value = t.Support_Group;
if (t.Status == "Closed")
{
command.Parameters.Add("@datec", SqlDbType.DateTime).Value = t.Completed;
}
else if (t.Status == "Cancelled")
{
command.Parameters.Add("@datec", SqlDbType.DateTime).Value = t.Completed;
}
else
{
//nullable on database so I dont pass it
command.Parameters.Add("@datec", SqlDbType.DateTime).Value = DBNull.Value;
}
if (command.ExecuteNonQuery() != -1)
{
throw new Exception("Insert Ticket did not insert correctly");
}
conn.Close();
}
Console.WriteLine("Insert snapshot completed");
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
Console.WriteLine("wait");
}
public void close()
{
reader.Close();
}
public void GetExcelData()
{
//string filePath = @"D:\Kanagavel\sample.xlsx";
//string filePath = @"D:\Kanagavel\SEPTEMBER SIN MING - FINAL.xlsx";
//string filePath = @"C:\Users\itluser\Desktop\Sep 2017.xlsx";
// string filePath = @"C:\Users\itluser\Desktop\Oct 2017.xlsx";
//string filePath = @"C:\Users\itluser\Desktop\Nov1.xlsx";
string filePath = @"C:\Users\itluser\Desktop\New folder\SIN MING AVE -FINAL1.xlsx";
//string filePath = @"C:\Users\itluser\Desktop\New folder\SIN MING AVE -FINAL2.xlsx";
try
{
FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
Stream stream = fileStream;
// We return the interface, so that
IExcelDataReader reader = null;
//filePath.Substring(filePath.IndexOf('.') + 1)
if (filePath.Contains(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (filePath.Contains(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
Console.WriteLine("File Files found.");
}
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
reader.Close();
var _bioMetricData = new List <RawBioMetricData>();
if (result.Tables[0].Rows.Count > 0)
{
foreach (DataTable table in result.Tables)
{
foreach (DataRow dr in table.Rows)
{
try
{
var bioMetricData = new RawBioMetricData();
bioMetricData.Id = Convert.ToInt16(dr["ACNo"]);
bioMetricData.EmployeeName = dr["Name"].ToString();
bioMetricData.EntryDateTime = Convert.ToDateTime(dr["Time"]);
bioMetricData.Status = dr["Status"].ToString();
_bioMetricData.Add(bioMetricData);
Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(bioMetricData));
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
}
}
}
var rawData = _bioMetricData;
var orderedData = rawData.OrderBy(i => i.EntryDateTime).OrderBy(i => i.Status).ToList();
var dates = orderedData.Select(i => i.EntryDateTime.Date).Distinct().ToList();
var ids = orderedData.OrderBy(i => i.Id).Select(i => i.Id).Distinct().ToList();
var formattedBioData = new List <BioMetricData>();
foreach (var id in ids)
{
foreach (var date in dates)
{
var data = orderedData.Where(i => i.EntryDateTime.Date == date && i.Id == id).ToList();
if (data != null && data.Count > 0)
{
Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(data));
var _formattedBioData = new BioMetricData();
_formattedBioData.Id = data[0].Id;
_formattedBioData.EmployeeName = data[0].EmployeeName;
_formattedBioData.AttendanceDate = data[0].EntryDateTime.Date;
var CheckinTime = data.Where(i => i.Status == "C/In").Select(x => x.EntryDateTime).ToList();
if (CheckinTime.Count > 0)
{
if (CheckinTime[0].ToString("tt") == "AM")
{
_formattedBioData.InTime = $"{CheckinTime[0].Hour.ToString("D2")}{CheckinTime[0].Minute.ToString("D2")}";
var CheckOutObject = data.Where(i => i.Status == "C/O").Select(x => x.EntryDateTime).ToList();
if (CheckOutObject.Count > 0)
{
var CheckOutTime = CheckOutObject.Count > 1 ? CheckOutObject[CheckOutObject.Count - 1] : CheckOutObject[0];
_formattedBioData.OutTime = $"{CheckOutTime.Hour.ToString("D2")}{CheckOutTime.Minute.ToString("D2")}";
}
}
if (CheckinTime[0].ToString("tt") == "PM")
{
var lookupDate = date;
_formattedBioData.InTime = $"{CheckinTime[0].Hour.ToString("D2")}{CheckinTime[0].Minute.ToString("D2")}";
var CheckOutObject = orderedData.Where(i => i.EntryDateTime.Date == lookupDate.AddDays(1) && i.Status == "C/O" && i.Id == id).Select(x => x.EntryDateTime).ToList();
if (CheckOutObject.Count > 0)
{
var CheckOutTime = CheckOutObject.Count > 1 ? CheckOutObject[CheckOutObject.Count - 1] : CheckOutObject[0];
_formattedBioData.OutTime = $"{CheckOutTime.Hour.ToString("D2")}{CheckOutTime.Minute.ToString("D2")}";
}
_formattedBioData.ShiftType = 1;
}
}
//var CheckOutTime = data.Where(i => i.Status == "C/O").ToList();
// Get AM or PM CheckinTime[0].EntryDateTime.ToString("tt");
//if (data.Count > 1)
//{
// _formattedBioData.OutTime = $"{data[1].EntryDateTime.Hour.ToString("D2")}{data[1].EntryDateTime.Minute.ToString("D2")}";
//}
formattedBioData.Add(_formattedBioData);
}
}
}
foreach (var attendance in formattedBioData)
{
if (!(attendance?.InTime == null && attendance?.OutTime == null))
{
this.InsertFormatedBioMetricData(attendance);
}
}
//var result1 = formattedBioData.Where(i => i.OutTime != null).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
}
public static void DoOpenOfficeTest(IExcelDataReader excelReader)
{
Assert.IsTrue(excelReader.IsValid);
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual("column a", excelReader.GetString(0));
Assert.AreEqual(" column b", excelReader.GetString(1));
Assert.AreEqual(" column b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetString(3));
Assert.AreEqual("column e", excelReader.GetString(4));
Assert.AreEqual(" column b", excelReader.GetString(5));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(2, excelReader.GetInt32(0));
Assert.AreEqual("b", excelReader.GetString(1));
Assert.AreEqual("c", excelReader.GetString(2));
Assert.AreEqual("d", excelReader.GetString(3));
Assert.AreEqual(" e ", excelReader.GetString(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(3, excelReader.GetInt32(0));
Assert.AreEqual(2, excelReader.GetInt32(1));
Assert.AreEqual(3, excelReader.GetInt32(2));
Assert.AreEqual(4, excelReader.GetInt32(3));
Assert.AreEqual(5, excelReader.GetInt32(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(4, excelReader.GetInt32(0));
Assert.AreEqual(new DateTime(2012, 10, 13), excelReader.GetDateTime(1));
Assert.AreEqual(new DateTime(2012, 10, 14), excelReader.GetDateTime(2));
Assert.AreEqual(new DateTime(2012, 10, 15), excelReader.GetDateTime(3));
Assert.AreEqual(new DateTime(2012, 10, 16), excelReader.GetDateTime(4));
for (int i = 4; i < 34; i++)
{
excelReader.Read();
Assert.AreEqual(i + 1, excelReader.GetInt32(0));
Assert.AreEqual(i + 2, excelReader.GetInt32(1));
Assert.AreEqual(i + 3, excelReader.GetInt32(2));
Assert.AreEqual(i + 4, excelReader.GetInt32(3));
Assert.AreEqual(i + 5, excelReader.GetInt32(4));
}
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
//test dataset
DataSet result = excelReader.AsDataSet(true);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual(6, result.Tables[0].Columns.Count);
Assert.AreEqual(33, result.Tables[0].Rows.Count);
Assert.AreEqual("column a", result.Tables[0].Columns[0].ColumnName);
Assert.AreEqual(" column b", result.Tables[0].Columns[1].ColumnName);
Assert.AreEqual(" column b_1", result.Tables[0].Columns[2].ColumnName);
Assert.AreEqual("Column3", result.Tables[0].Columns[3].ColumnName);
Assert.AreEqual("column e", result.Tables[0].Columns[4].ColumnName);
Assert.AreEqual(" column b_2", result.Tables[0].Columns[5].ColumnName);
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[0][0]));
Assert.AreEqual("b", result.Tables[0].Rows[0][1]);
Assert.AreEqual("c", result.Tables[0].Rows[0][2]);
Assert.AreEqual("d", result.Tables[0].Rows[0][3]);
Assert.AreEqual(" e ", result.Tables[0].Rows[0][4]);
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][0]));
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[1][1]));
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][2]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[1][3]));
Assert.AreEqual(5, Convert.ToInt32(result.Tables[0].Rows[1][4]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[2][0]));
Assert.AreEqual(new DateTime(2012, 10, 13), result.Tables[0].Rows[2][1]);
Assert.AreEqual(new DateTime(2012, 10, 14), result.Tables[0].Rows[2][2]);
Assert.AreEqual(new DateTime(2012, 10, 15), result.Tables[0].Rows[2][3]);
Assert.AreEqual(new DateTime(2012, 10, 16), result.Tables[0].Rows[2][4]);
for (int i = 4; i < 33; i++)
{
Assert.AreEqual(i + 2, Convert.ToInt32(result.Tables[0].Rows[i][0]));
Assert.AreEqual(i + 3, Convert.ToInt32(result.Tables[0].Rows[i][1]));
Assert.AreEqual(i + 4, Convert.ToInt32(result.Tables[0].Rows[i][2]));
Assert.AreEqual(i + 5, Convert.ToInt32(result.Tables[0].Rows[i][3]));
Assert.AreEqual(i + 6, Convert.ToInt32(result.Tables[0].Rows[i][4]));
}
excelReader.Close();
}
/// <summary>
/// Data the formatting and validations.
/// </summary>
/// <param name="uploadFile">The upload file.</param>
/// <param name="excelReader">The excel reader.</param>
/// <param name="visitorPresenter">The visitor presenter.</param>
/// <returns>
/// Import Details for Data provided.
/// </returns>
private static ImportDetails DataFormattingAndValidations(HttpPostedFileBase uploadFile, ref IExcelDataReader excelReader, VisitorPresenter visitorPresenter)
{
var importedFileData = new ImportDetails
{
FileName = uploadFile.FileName,
FileModifiedDate = BaseController.GenerateLocalDateTime(DateTime.UtcNow)
};
try
{
if (uploadFile.FileName.EndsWith(ExcelLatestExtension, StringComparison.OrdinalIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(uploadFile.InputStream);
}
else if (uploadFile.FileName.EndsWith(ExcelNormalExtension, StringComparison.OrdinalIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(uploadFile.InputStream);
}
using (var result = excelReader.AsDataSet())
{
result.Locale = CultureInfo.CurrentCulture;
importedFileData = ConvertToCSV(result, importedFileData);
excelReader.Close();
}
if (string.IsNullOrEmpty(importedFileData.VisitorDetail))
{
visitorPresenter.IsImportFailed = true;
visitorPresenter.ImportException = string.Format(CultureInfo.CurrentCulture, "{0}{1}.", Resource.ShipNameNotCorrect, SessionData.Instance.MasterData.Ship.Name);
}
}
catch (Exception ex)
{
if (!string.IsNullOrEmpty(ex.Message))
{
visitorPresenter.IsImportFailed = true;
visitorPresenter.ImportException = Resource.VisitorImportException;
}
}
importedFileData.RejectedReason = visitorPresenter.ImportException;
return importedFileData;
}