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