public static DataTable ReadExcel(this Stream stream) { DataTable dt = new DataTable(); ISheet sheet; //stream.Position = 0; //if (sFileExtension == ".xls") //{ // HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats // sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook //} //else //{ XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook //} IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { DataRow dr = dt.NewRow(); IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { try { dr[j] = row.GetCell(j); } catch { } } } dt.Rows.Add(dr); } return(dt); }
public void GetOrder() { //加载数据 var workBook = NPOIHelper.LoadWorkbook(@"D:\Work\项目\香烟\香烟\资料文件参考\订单分拣数据(慧联系统)\订单分拣数据(慧联系统)\分拣订单数据 - 副本.xls"); var sheet = workBook.GetSheetAt(0); //获得第一个sheet var rows = sheet.GetRowEnumerator(); //获得所有行 OrderTable = new DataTable(); rows.MoveNext(); //移动到第一行, var rowfirst = (IRow)rows.Current; //获得当前行, for (int i = 0; i < rowfirst.LastCellNum; i++) //遍历,把第一行数据,当做行加入表中 { OrderTable.Columns.Add(rowfirst.GetCell(i).ToString()); } while (rows.MoveNext())//然后循环遍历 { var row = (IRow)rows.Current; DataRow dr = OrderTable.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i);//取得单元格 if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } OrderTable.Rows.Add(dr); } }
public ArrayList GetExcelSheetHeaders(ISheet sheet, int headerRowIndex = 0) { ArrayList headers = new ArrayList(); IRow headerRow = sheet.GetRow(headerRowIndex); //Get Header Row int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } headers.Add(cell.ToString().Trim().ToUpper()); } return(headers); }
/// <summary> /// /// </summary> /// <param name="fileName"></param> /// <returns></returns> public static List <List <List <string> > > ReadExcel(string fileName) { //打开Excel工作簿 XSSFWorkbook hssfworkbook = null; try { using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new XSSFWorkbook(file); } } catch (Exception e) { //LogHandler.LogWrite(string.Format("文件{0}打开失败,错误:{1}", new string[] { fileName, e.ToString() })); } //循环Sheet页 int sheetsCount = hssfworkbook.NumberOfSheets; List <List <List <string> > > workBookContent = new List <List <List <string> > >(); for (int i = 0; i < sheetsCount; i++) { //Sheet索引从0开始 ISheet sheet = hssfworkbook.GetSheetAt(i); //循环行 List <List <string> > sheetContent = new List <List <string> >(); int rowCount = sheet.PhysicalNumberOfRows; for (int j = 0; j < rowCount; j++) { //Row(逻辑行)的索引从0开始 IRow row = sheet.GetRow(j); //循环列(各行的列数可能不同) List <string> rowContent = new List <string>(); int cellCount = row.PhysicalNumberOfCells; for (int k = 0; k < cellCount; k++) { //ICell cell = row.GetCell(k); NPOI.SS.UserModel.ICell cell = row.Cells[k]; if (cell == null) { rowContent.Add("NIL"); } else { rowContent.Add(cell.ToString()); //rowContent.Add(cell.StringCellValue); } } //添加行到集合中 sheetContent.Add(rowContent); } //添加Sheet到集合中 workBookContent.Add(sheetContent); } return(workBookContent); }
private static DataTable ReadFromFile(string filePath) { IWorkbook hssfworkbook; #region 初始化信息 using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = WorkbookFactory.Create(file) as IWorkbook; } #endregion NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); rows.MoveNext(); IRow row = rows.Current as IRow; for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { //将第一列作为列表头 DataColumn dtc = new DataColumn(row.GetCell(j).ToString(), typeof(string)); dt.Columns.Add(dtc); } while (rows.MoveNext()) { try { row = (IRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); Console.WriteLine(cell == null ? "" : cell + "\t\t"); Console.WriteLine(); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } catch (Exception ex) { continue; // throw; } } return(dt); }
/// <summary>读取excel /// 默认第一行为标头 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <returns></returns> public static DataTable Import(string strFileName) { DataTable dt = new DataTable(); // HSSFWorkbook hssfworkbook; IWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { string fileExt = Path.GetExtension(strFileName); if (fileExt == ".xls") { hssfworkbook = new HSSFWorkbook(file); } else if (fileExt == ".xlsx") { hssfworkbook = new XSSFWorkbook(file); } else { return(new DataTable()); } } NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { NPOI.SS.UserModel.IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = row.GetCell(j).ToString(); } } dt.Rows.Add(dataRow); } return(dt); }
/// <summary> /// xlsx /// </summary> /// <param name="Postfile"></param> /// <param name="dt"></param> /// <param name="iStartRow"></param> /// <param name="iStartColumn"></param> /// <returns></returns> private static string ImportExcelFileXSSF(HttpPostedFileBase Postfile, DataTable dt, int iStartRow, int iStartColumn) { XSSFWorkbook Xssfworkbook; #region//初始化信息 try { //.xlsx应该XSSFWorkbook workbook = new XSSFWorkbook(file); //而xls应该用 HSSFWorkbook workbook = new HSSFWorkbook(file); Stream file = Postfile.InputStream; Xssfworkbook = new XSSFWorkbook(file); //HSSFWorkbook workbook = new HSSFWorkbook(file); } catch (Exception e) { throw e; } #endregion NPOI.SS.UserModel.ISheet sheet = Xssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); for (int i = 0; i < iStartRow; i++) { rows.MoveNext(); } while (rows.MoveNext()) { XSSFRow row = (XSSFRow)rows.Current; DataRow dr = dt.NewRow(); int j = 0; for (int i = iStartColumn; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[j] = null; } else { if (dt.Columns[j].DataType.FullName == "System.Int32") { dr[j] = Convert.ToInt32(cell.ToString()); } else { dr[j] = cell; } } j++; } dt.Rows.Add(dr); } return(JsonHelper.DataTable2Json(dt)); }
public static DataTable GetData(string filePath) { IWorkbook workbook; #region//初始化信息 try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { //hssfworkbook = new HSSFWorkbook(file); workbook = WorkbookFactory.Create(file); } } catch (Exception e) { throw e; } #endregion NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); rows.MoveNext(); //HSSFRow row = (HSSFRow)rows.Current; IRow row = (IRow)rows.Current; for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); //将第一列作为列表头 dt.Columns.Add(row.GetCell(j).ToString()); } while (rows.MoveNext()) { row = (IRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } return(dt); }
public static DataTable ImportExcelFileXSSF_Org(HttpFileCollectionBase files) { XSSFWorkbook hssfworkbook; #region//初始化信息 try { //.xlsx应该XSSFWorkbook workbook = new XSSFWorkbook(file); //而xls应该用 HSSFWorkbook workbook = new HSSFWorkbook(file); Stream file = files[0].InputStream; hssfworkbook = new XSSFWorkbook(file); //HSSFWorkbook workbook = new HSSFWorkbook(file); } catch (Exception e) { throw e; } #endregion NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); rows.MoveNext(); XSSFRow row = (XSSFRow)rows.Current; for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { //将第一列作为列表头 dt.Columns.Add(row.GetCell(j).ToString()); } while (rows.MoveNext()) { row = (XSSFRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } return(dt); }
/// <summary> /// 读取excel ,默认第一行为标头 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <returns></returns> public static DataTable ExcelImport(string strFileName) { DataTable dt = new DataTable(); ISheet sheet = null; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { if (strFileName.IndexOf(".xlsx") == -1)//2003 { HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); sheet = hssfworkbook.GetSheetAt(0); } else//2007 { XSSFWorkbook xssfworkbook = new XSSFWorkbook(file); sheet = xssfworkbook.GetSheetAt(0); } } System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = row.GetCell(j).ToString(); } } dt.Rows.Add(dataRow); } return(dt); }
/// <summary> /// 将excel的数据加载到datatable中 /// </summary> /// <param name="path"></param> /// <author>wangwei</author> /// <returns></returns> public static DataTable ExcelToTable(string path) { HSSFWorkbook hssfworkbook; #region//初始化信息 try { using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } } catch (Exception e) { throw e; } #endregion NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dtss = new DataTable(); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { dtss.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); } while (rows.MoveNext()) { HSSFRow row = (HSSFRow)rows.Current; DataRow dr = dtss.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dtss.Rows.Add(dr.ItemArray); } return(dtss); }
/// <summary> /// 由Excel导入DataTable /// </summary> /// <param name="excelFilePath">Excel文件路径,为物理路径。</param> /// <param name="sheetName">Excel工作表名称</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <returns>DataTable</returns> public static DataTable ImportDataTableFromExcel(string excelFilePath) { HSSFWorkbook hssfworkbook; //excelFilePath = excelFilePath.Replace("http://","").Replace("/","\\"); using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); for (int j = 0; j < 4; j++) { dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); } while (rows.MoveNext()) { NPOI.SS.UserModel.IRow row = (HSSFRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } return(dt); } }
private static void createColumn(DataTable dt, NPOI.SS.UserModel.ICell cell, HSSFRow row, DataRow dr1, int start, int end) { DataRow dr = dt.NewRow(); int j = 0; for (int i = start; i <= end; i++) { cell = row.GetCell(i); if (cell == null) { dr[j] = 0; } else { dr[j] = stringToInt(cell.ToString()); } j++; } dt.Rows.Add(dr); }
private static DataTable GetDataTableByXls(string excelFilePath, DataTable dt) { HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); int r = 0; while (rows.MoveNext()) { r++; if (r == 1) { continue; } NPOI.SS.UserModel.IRow row = (HSSFRow)rows.Current; DataRow rw = dt.NewRow(); for (int i = 1; i <= row.LastCellNum - 1; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { continue; } else { rw[i - 1] = cell.ToString(); } } dt.Rows.Add(rw); } return(dt); } }
private object GetCellValue(NPOI.SS.UserModel.ICell cell) { object value = null; switch (cell.CellType) { case CellType.Blank: value = string.Empty; break; case CellType.Boolean: value = cell.BooleanCellValue; break; case CellType.Error: value = cell.ErrorCellValue; break; case CellType.Formula: value = cell.CellFormula; break; case CellType.Numeric: value = cell.NumericCellValue; break; case CellType.String: value = cell.StringCellValue; break; case CellType.Unknown: default: value = cell.StringCellValue ?? cell.ToString(); break; } return(value); }
///<summary> /// #region 少量excel数据导入数据库 /// </summary> public static async Task <DataTable> ImExport(DataTable dt, IWorkbook hssfworkbook) { NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString()); } while (rows.MoveNext()) { XSSFRow row = (XSSFRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } dt.Rows.RemoveAt(0); #region 往数据库表添加数据 using (WuLinEntities1 db = new WuLinEntities1()) { if (dt != null && dt.Rows.Count != 0) { for (int i = 0; i < dt.Rows.Count; i++) { string 时间 = dt.Rows[i]["时间"].ToString(); string 国产进口 = dt.Rows[i]["国产/进口"].ToString(); string 省 = dt.Rows[i]["省"].ToString(); string 市 = dt.Rows[i]["市"].ToString(); string 县 = dt.Rows[i]["县"].ToString(); string 制造商 = dt.Rows[i]["制造商"].ToString(); string 车辆型号 = dt.Rows[i]["车辆型号"].ToString(); string 品牌 = dt.Rows[i]["品牌"].ToString(); string 车型 = dt.Rows[i]["车型"].ToString(); string 排量 = dt.Rows[i]["排量"].ToString(); string 变速器 = dt.Rows[i]["变速器"].ToString(); string 车辆类型 = dt.Rows[i]["车辆类型"].ToString(); string 车身型式 = dt.Rows[i]["车身型式"].ToString(); string 燃油类型 = dt.Rows[i]["燃油类型"].ToString(); string 使用性质 = dt.Rows[i]["使用性质"].ToString(); string 所有权 = dt.Rows[i]["所有权"].ToString(); string 抵押标记 = dt.Rows[i]["抵押标记"].ToString(); string 性别 = dt.Rows[i]["性别"].ToString(); string 年龄 = dt.Rows[i]["年龄"].ToString(); string 车身颜色 = dt.Rows[i]["车身颜色"].ToString(); string 发动机型号 = dt.Rows[i]["发动机型号"].ToString(); string 功率 = dt.Rows[i]["功率"].ToString(); string 排放标准 = dt.Rows[i]["排放标准"].ToString(); string 轴距 = dt.Rows[i]["轴距"].ToString(); string 轮胎规格 = dt.Rows[i]["轮胎规格"].ToString(); string 车外廓长 = dt.Rows[i]["车外廓长"].ToString(); string 车外廓宽 = dt.Rows[i]["车外廓宽"].ToString(); string 车外廓高 = dt.Rows[i]["车外廓高"].ToString(); string 准确排量 = dt.Rows[i]["准确排量"].ToString(); string 核定载客 = dt.Rows[i]["核定载客"].ToString(); string 总质量 = dt.Rows[i]["总质量"].ToString(); string 整备质量 = dt.Rows[i]["整备质量"].ToString(); string 轴数 = dt.Rows[i]["轴数"].ToString(); string 前轮距 = dt.Rows[i]["前轮距"].ToString(); string 后轮距 = dt.Rows[i]["后轮距"].ToString(); string 保有量 = dt.Rows[i]["保有量"].ToString();; //int.TryParse(dt.Rows[i]["保有量"].ToString(), out 保有量); PassengerVehicle pv = new PassengerVehicle(); pv.Id = Guid.NewGuid(); pv.使用性质 = 使用性质; pv.保有量 = Convert.ToInt32(保有量); pv.准确排量 = (准确排量); pv.制造商 = 制造商; pv.前轮距 = (前轮距); pv.功率 = (功率); pv.县 = 县; pv.发动机型号 = 发动机型号; pv.变速器 = 变速器; pv.后轮距 = (后轮距); pv.品牌 = 品牌; pv.国产_进口 = 国产进口; pv.市 = 市; pv.年龄 = (年龄); pv.性别 = 性别; pv.总质量 = (总质量); pv.所有权 = 所有权; pv.抵押标记 = 抵押标记; pv.排放标准 = 排放标准; pv.排量 = (排量); pv.整备质量 = (整备质量); pv.时间 = 时间; pv.核定载客 = (核定载客); pv.燃油类型 = 燃油类型; pv.省 = 省; pv.车型 = 车型; pv.车外廓宽 = (车外廓宽); pv.车外廓长 = (车外廓长); pv.车外廓高 = (车外廓高); pv.车身型式 = 车身型式; pv.车身颜色 = 车身颜色; pv.车辆型号 = 车辆型号; pv.车辆类型 = 车辆类型; pv.轮胎规格 = 轮胎规格; pv.轴数 = (轴数); pv.轴距 = (轴距); db.PassengerVehicles.Add(pv); try { await db.SaveChangesAsync(); } catch (Exception e) { Console.WriteLine("第" + i + "条:" + e.Message); } } } #endregion } return(dt); }
public async Task <ActionResult> OnPostImporttest([FromForm] test _test) { var value = HttpContext.Session.GetString(SessionToken); if (string.IsNullOrEmpty(value)) { string token = await getToken(); HttpContext.Session.SetString(SessionToken, token); value = HttpContext.Session.GetString(SessionToken); } IFormFile file = Request.Form.Files[0]; string folderName = "Upload"; string webRootPath = _hostingEnvironment.WebRootPath; string newPath = Path.Combine(webRootPath, folderName); StringBuilder sb = new StringBuilder(); if (!Directory.Exists(newPath)) { Directory.CreateDirectory(newPath); } if (file.Length > 0) { string sFileExtension = Path.GetExtension(file.FileName).ToLower(); ISheet sheet; string fullPath = Path.Combine(newPath, file.FileName); using (var stream = new FileStream(fullPath, FileMode.Create)) { file.CopyTo(stream); stream.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; sb.Append("<table class='table'><tr>"); for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } sb.Append("<th>" + cell.ToString() + "</th>"); } sb.Append("</tr>"); sb.AppendLine("<tr>"); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { User _in = new User(); IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } _in.Team = new Team(); _in.Contact = new List <Contact>(); _in.UserName = row.GetCell(4).ToString(); _in.Password = "******"; _in.LookUpUserTypeId = 4; _in.LookUpUserStatusId = 1; _in.RecordStatus = "Active"; Team _inTeam = new Team(); _inTeam.TeamName = row.GetCell(1).ToString(); _inTeam.TeamDescription = null; _inTeam.TeamLogo = null; _in.Team = _inTeam; List <Contact> _inConL = new List <Contact>(); Contact _inCon = new Contact(); _inCon.LookUpContactTypeId = 1; _inCon.Value = row.GetCell(4).ToString(); _inConL.Add(_inCon); Contact _inConP = new Contact(); _inConP.LookUpContactTypeId = 2; _inConP.Value = row.GetCell(5).ToString(); _inConL.Add(_inConP); _in.Contact = _inConL; User _out = await _login.AddUserDetails(value, _in); Citizen _citizen = new Citizen(); _citizen.UserId = (int)_out.UserId; _citizen.CaptainName = row.GetCell(0).ToString(); _citizen.PlayerName = row.GetCell(1).ToString(); _citizen.CompanyName = row.GetCell(2).ToString(); _citizen.Designation = row.GetCell(3).ToString(); _citizen.DOB = Convert.ToDateTime(row.GetCell(6).ToString()); _citizen.BloodGroup = row.GetCell(7).ToString(); _citizen.HREmail = row.GetCell(8).ToString(); _citizen.HRPhone = row.GetCell(9).ToString(); _citizen.CompanyEmail = row.GetCell(4).ToString(); bool _outData = await _user.PostCitizenDetails(value, _citizen); //sb.Append("<td>" + row.GetCell(j).ToString() + "</td>"); sb.AppendLine("</tr>"); } sb.Append("</table>"); } } return(this.Content(sb.ToString())); }
//private readonly IHostingEnvironment _hostingEnvironment; //public UploadExcelDetails() //{ //} //public UploadExcelDetails(IHostingEnvironment hostingEnvironment) //{ // _hostingEnvironment = hostingEnvironment; //} public void UploadVendors(string Path, Int64?CompanyID, string FileName, string CompanyName) { try { DataTable dt = new DataTable(); string JSONString = string.Empty; var memory = new MemoryStream(); string sFileExtension = FileName.Split('.')[1]; // var FileName = CompanyName + DateTime.Now.ToString("yyyyMMddhhmmss"); ISheet sheet; //string fullPath = Path.Combine(FileName + sFileExtension); using (var stream = new FileStream(Path, FileMode.Open)) { stream.CopyTo(memory); memory.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(memory); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(memory); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } dt.Columns.Add(headerRow.GetCell(j).ToString()); } dt.Columns.Add("Message"); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { DataRow dr = dt.NewRow(); IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dr[j] = row.GetCell(j).ToString(); } } dt.Rows.Add(dr); if (dt.Rows[i - 1]["Vendor Name"].ToString() != "" && dt.Rows[i - 1]["Pan Number"].ToString() != "" && dt.Rows[i - 1]["Contact Person Name"].ToString() != "" && dt.Rows[i - 1]["Email ID"].ToString() != "" && dt.Rows[i - 1]["Contact Number"].ToString() != "") { if (!Regex.IsMatch(dt.Rows[i - 1]["Pan Number"].ToString(), @"^[a-zA-Z]{5}[0-9]{4}[a-zA-Z]{1}$")) { dt.Rows[i - 1]["Message"] = "Pan Number is not valid"; continue; } if (dt.Rows[i - 1]["MSME (Yes/No)"].ToString().ToLower() != "") { if (dt.Rows[i - 1]["MSME (Yes/No)"].ToString().ToLower() == "yes") { if (dt.Rows[i - 1]["UAM Number"].ToString() == "") { dt.Rows[i - 1]["Message"] = "UAM Number should not be blank"; continue; } } } if (dt.Rows[i - 1]["UAM Number"].ToString() != "") { if (dt.Rows[i - 1]["MSME (Yes/No)"].ToString().ToLower() == "") { dt.Rows[i - 1]["Message"] = "MSME should not be blank"; continue; } } if (!Regex.IsMatch(dt.Rows[i - 1]["Email ID"].ToString(), @"^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$")) { dt.Rows[i - 1]["Message"] = "Email ID is not valid"; continue; } else { dt.Rows[i - 1]["Message"] = "Success"; } } else { if (dt.Rows[i - 1]["Vendor Name"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Vendor Name should not be blank"; continue; } if (dt.Rows[i - 1]["Pan Number"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Pan Number should not be blank"; continue; } if (dt.Rows[i - 1]["Contact Person Name"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Contact Person Name should not be blank"; continue; } if (dt.Rows[i - 1]["Email ID"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Email ID should not be blank"; continue; } if (dt.Rows[i - 1]["Contact Number"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Contact Number should not be blank"; continue; } } string randomPassword = GeneratePassword(); string Password = SecurityHelperService.Encrypt(randomPassword); var Result = InsertVendorRecord(dr, CompanyID, Password); if (Convert.ToInt32(Result.Value) > 0) { //string Template = string.Empty; string Template = GetVendorRegisterMailTemplate(); string path = Template; string EMAIL_TOKEN_PAYMENT_LINK = "##$$PAYMENT_LINK$$##"; string paymentLink = "http://dotnet.brainvire.com/Finocart/Account/AdminLogin";///change url //string MailStatus = string.Empty; string emailToAddress = dr[6].ToString(); string subject = "Vendor registration"; WebClient client = new WebClient(); string startupPath = Environment.CurrentDirectory; string body = path; // string body = client.DownloadString(startupPath + "/Views/Template/EmailTemplate.cshtml"); body = body.Replace("@@User@@", dr[0].ToString()); body = body.Replace("@@PanNumber@@", dr[1].ToString()); body = body.Replace("@@ProjectName@@", "Finocart"); body = body.Replace("@@VendorName@@", dt.Rows[i - 1]["Vendor Name"].ToString()); body = body.Replace("@@AnchorCompanyname@@", CompanyName); body = body.Replace(EMAIL_TOKEN_PAYMENT_LINK, paymentLink); body = body.Replace("@@PanNumber@@", dt.Rows[i - 1]["Pan Number"].ToString()); body = body.Replace("@@Password@@", randomPassword); IEnumerable <LookupDetail> lookupDetails = getLookupDetailByKey("SMTPInfo"); SendEmail(lookupDetails, emailToAddress, subject, body, true); } if (Convert.ToInt32(Result.Value) == -1) { dt.Rows[i - 1]["Message"] = "Pan Number already exists"; continue; } } //GetLog(dt); } //} JSONString = JsonConvert.SerializeObject(dt); GetLog(JSONString, "Vendor", CompanyID, CompanyName, FileName); //HttpContext.Session.SetString("Excel", JSONString); //return Json(new { result = dt }); } catch (Exception ex) { throw ex; } }
public void UploadInvoice(string Path, Int16?CompanyID, string FileName, string AnchCompanyName) { string ErrorMessage = string.Empty; try { DataTable dt = new DataTable(); string JSONString = string.Empty; var memory = new MemoryStream(); string sFileExtension = FileName.Split('.')[1]; var EmailID = ""; var VendorName = ""; bool sendMail = false; ISheet sheet; using (var stream = new FileStream(Path, FileMode.Open)) { stream.CopyTo(memory); memory.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(memory); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(memory); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } dt.Columns.Add(headerRow.GetCell(j).ToString()); } dt.Columns.Add("Message"); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { DataRow dr = dt.NewRow(); IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dr[j] = row.GetCell(j).ToString(); } } dt.Rows.Add(dr); DateTime date; if (dt.Rows[i - 1]["PO Number"].ToString() != "" && dt.Rows[i - 1]["Invoice Date (DD/MM/YYYY)"].ToString() != "" && dt.Rows[i - 1]["Invoice Number"].ToString() != "" && dt.Rows[i - 1]["Invoice Amount"].ToString() != "" && dt.Rows[i - 1]["Payment Due Date (DD/MM/YYYY)"].ToString() != "" && dt.Rows[i - 1]["Approved Amount"].ToString() != "") { if (!DateTime.TryParseExact(dt.Rows[i - 1]["Invoice Date (DD/MM/YYYY)"].ToString(), "dd-MMM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out date)) { dt.Rows[i - 1]["Message"] = "Invoice Date should be in DD/MM/YYYY format"; continue; } if (Convert.ToInt64(dt.Rows[i - 1]["Invoice Amount"].ToString()) < Convert.ToInt64(dt.Rows[i - 1]["Approved Amount"].ToString())) { dt.Rows[i - 1]["Message"] = "Approved amount should be less than invoice amount"; continue; } if (!DateTime.TryParseExact(dt.Rows[i - 1]["Payment Due Date (DD/MM/YYYY)"].ToString(), "dd-MMM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal, out date)) { dt.Rows[i - 1]["Message"] = "Payment Due Date should be in DD/MM/YYYY format"; continue; } else { dt.Rows[i - 1]["Message"] = "Success"; } } else { if (dt.Rows[i - 1]["PO Number"].ToString() == "") { dt.Rows[i - 1]["Message"] = "PO Number should not be blank"; continue; } if (dt.Rows[i - 1]["Invoice Date (DD/MM/YYYY)"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Invoice Date should not be blank"; continue; } if (dt.Rows[i - 1]["Invoice Number"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Invoice Number should not be blank"; continue; } if (dt.Rows[i - 1]["Invoice Amount"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Invoice Amount should not be blank"; continue; } if (dt.Rows[i - 1]["Payment Due Date (DD/MM/YYYY)"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Payment Due Date should not be blank"; continue; } if (dt.Rows[i - 1]["Approved Amount"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Approved Amount should not be blank"; continue; } } Company objDatawithSP = CheckEmailID(row.Cells[6].ToString()); if (objDatawithSP != null) { EmailID = objDatawithSP.Contact_email; VendorName = objDatawithSP.Company_name; } else { dt.Rows[i - 1]["Message"] = "Pan Number is not valid"; continue; } var Result = InsertInvoiceRecord(row, CompanyID); if (Convert.ToInt32(Result.Value) == -1) { dt.Rows[i - 1]["Message"] = "Invoice Number already Exists"; continue; } sendMail = true; } //sending email if (sendMail) { string Template = GetInvoiceRegisterMailTemplate(); string path = Template; //string tag = lstAwaitedInvVendorsView.ElementAt(0).TableTag; string EMAIL_TOKEN_PAYMENT_LINK = "##$$PAYMENT_LINK$$##"; string paymentLink = "http://dotnet.brainvire.com/Finocart/Account/AdminLogin";///change url string MailStatus = string.Empty; string subject = "Vendor registration"; WebClient client = new WebClient(); string startupPath = Environment.CurrentDirectory; string body = path; StringBuilder sb = new StringBuilder(); //IEnumerable<GetUploadVendorListModel1> lstAwaitedInvVendorsView1 = _companyRepository.GetUploadVendorList1(VendorID); //for (int i = 1; i < 5; i++) //{ body = body.Replace("@@VendorName@@", VendorName); body = body.Replace("@@AnchorName@@", AnchCompanyName); body = body.Replace(EMAIL_TOKEN_PAYMENT_LINK, paymentLink); //body = body.Replace("@@PODate(MM/DD/YYYY)@@", "Abc"); //body = body.Replace("@@InvoiceNumber@@", "Abc"); //body = body.Replace("@@InvoiceAmount@@", "Abc"); //body = body.Replace("@@PaymentDueDate(MM/DD/YYYY)@@", "Abc"); //body = body.Replace("@@ApprovedAmount@@", "Abc"); //body = body.Replace("@@PaymentDays@@", "Abc"); IEnumerable <LookupDetail> lookupDetails = getLookupDetailByKey("SMTPInfo"); SendEmail(lookupDetails, EmailID, subject, body, true); } } JSONString = JsonConvert.SerializeObject(dt); GetLog(JSONString, "Invoice", CompanyID, AnchCompanyName, FileName); //HttpContext.Session.SetString("Excel", JSONString); //if (file.FileName == "InvoiceTemplate.xlsx") //{ // file.FileName.Remove(1); //} //return Json(new { result = dt }); } catch (Exception ex) { var st = new StackTrace(ex, true); var frame = st.GetFrame(0); int ErrorLine = frame.GetFileLineNumber(); //var Result = _CommonRepository.LogManagement(ControllerName, ActionName, ex.Message, ErrorLine, UserID); throw ex; } }
public async Task <IActionResult> UploadFile() { IFormFile file = Request.Form.Files[0]; ISheet sheet = ImportExcelCommon.GetSheetFromFile(file, "Product"); if (sheet != null) { IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; int startRow = sheet.FirstRowNum + 1; int lastRow = sheet.LastRowNum; for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } } for (int i = startRow; i <= lastRow; i++) //Read Excel File { IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } int startCell = row.FirstCellNum; ProductModel p = new ProductModel(); p.Code = row.GetCell(startCell++).ToString(); p.Name = row.GetCell(startCell++).ToString(); p.Note = ""; p.Preservation = new PreservationModel(); p.OtherUnitOfProduct = new List <ProductUnitModel>(); UnitModel unit = _UnitRepository.GetByName(row.GetCell(startCell++).ToString().ToLower()); if (unit == null) { continue; } p.UnitId = unit.ID; int wpu = -1; Int32.TryParse(row.GetCell(startCell).ToString(), out wpu); if (wpu == -1) { continue; } p.WeightPerUnit = wpu; AddNewProductRequest request = new AddNewProductRequest() { Product = p }; await _ProductAppService.addNewProduct(request); } return(Ok()); } return(BadRequest()); }
public List <T> ImportFromExcel(IFormFile file, string DestinationPath, string[] allowedColumns, setColumnValueDel setval) { if (!Directory.Exists(DestinationPath)) { Directory.CreateDirectory(DestinationPath); } if (file.Length == 0) { return(null); } string sFileExtension = Path.GetExtension(file.FileName).ToLower(); NPOI.SS.UserModel.ISheet sheet; string fullPath = Path.Combine(DestinationPath, file.FileName); using (var stream = new FileStream(fullPath, FileMode.Create)) { file.CopyTo(stream); stream.Position = 0; if (sFileExtension == ".xls")//This will read the Excel 97-2000 formats { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); sheet = hssfwb.GetSheetAt(0); } else //This will read 2007 Excel format { XSSFWorkbook hssfwb = new XSSFWorkbook(stream); sheet = hssfwb.GetSheetAt(0); } IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; // Start creating the html which would be displayed in tabular format on the screen //sb.Append("<table class='table'><tr>"); List <string> columnNames = new List <string>(); List <int> indexList = new List <int>(); for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } //sb.Append("<th>" + cell.ToString() + "</th>"); string actualCellName = cell.ToString().ToLower().Trim(); string cellName = Regex.Replace(actualCellName, @"\s+", ""); if (allowedColumns.Contains(cellName)) { columnNames.Add(cellName); indexList.Add(j); } } List <T> bugs = new List <T>(); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { //QueryBuilder queryBuilder = new QueryBuilder("developer"); IRow row = sheet.GetRow(i); T bug = (T)Activator.CreateInstance(typeof(T)); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { // tanveer int index = indexList.IndexOf(j); var column = row.GetCell(j); var celltype = column.CellType; var columnValue = ""; if (celltype == CellType.Numeric && DateUtil.IsCellDateFormatted(column)) { DateTime date = column.DateCellValue; ICellStyle style = column.CellStyle; // Excel uses lowercase m for month whereas .Net uses uppercase string format = style.GetDataFormatString().Replace('m', 'M'); columnValue = date.ToString(format); } else { columnValue = column.ToString(); } if (index >= 0) { string coulumnName = columnNames[index]; setval(coulumnName, columnValue, bug); string colValue = columnValue.Replace("\'", string.Empty) .Replace("{", string.Empty) .Replace("}", string.Empty); InsertIntoColumn(coulumnName, colValue, bug); } } } bugs.Add(bug); //sb.AppendLine("</tr>"); } return(bugs); } }
public async Task <ActionResult> OnPostImportAsync() { IFormFile file = Request.Form.Files[0]; string folderName = "Upload"; string webRootPath = _hostingEnvironment.WebRootPath; string newPath = Path.Combine(webRootPath, folderName); List <LoginViewModel> UsersList = new List <LoginViewModel>(); if (!Directory.Exists(newPath)) { Directory.CreateDirectory(newPath); } if (file.Length > 0) { string sFileExtension = Path.GetExtension(file.FileName).ToLower(); ISheet sheet; string fullPath = Path.Combine(newPath, file.FileName); using (var stream = new FileStream(fullPath, FileMode.Create)) { file.CopyTo(stream); stream.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; string[] arr = new string[2]; //string emailCol = null; for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } arr[j] = cell.ToString(); } UsersList.Add(new LoginViewModel { UserName = arr[0], Email = arr[0], Password = arr[1] }); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { arr[j] = row.GetCell(j).ToString(); } } UsersList.Add(new LoginViewModel { UserName = arr[0], Email = arr[0], Password = arr[1] }); } } } ActivityUser user; IdentityResult result; foreach (LoginViewModel i in UsersList) { user = new ActivityUser() { UserName = i.Email, Email = i.Email }; result = await _userManager.CreateAsync(user, i.Password); } return(Ok()); }
public IActionResult ViewSchedule() { var user = db.Users.FirstOrDefault(u => u.Login == HttpContext.User.Identity.Name); if (user != null) { StringBuilder sb = new StringBuilder(); if (System.IO.File.Exists(path) && fileInfo.Length > 0) { using (var stream = new FileStream(path, FileMode.Open)) { var memory = new MemoryStream(); stream.CopyTo(memory); memory.Position = 0; ISheet sheet; string fileExtension = fileInfo.Extension; if (fileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(memory); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook xssfwb = new XSSFWorkbook(memory); //This will read 2007 Excel format sheet = xssfwb.GetSheetAt(0); //get first sheet from workbook } sb.Append("<table class='table'><tr>"); IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; for (int i = 0; i < cellCount; i++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(i); if (cell == null || String.IsNullOrWhiteSpace(cell.ToString())) { continue; } sb.Append("<th colspan='2'>" + cell.ToString() + "</th>"); } sb.Append("</tr>"); for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) //Read Excel File { sb.AppendLine("<tr>"); IRow row = sheet.GetRow(i); if (row == null || row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } for (int j = row.FirstCellNum; j <= cellCount; j++) { if (row.GetCell(j) != null) { sb.Append("<td>" + row.GetCell(j).ToString() + "</td>"); } } sb.AppendLine("</tr>"); } sb.Append("</table>"); } ViewData["FileExist"] = true; ViewData["Grid"] = sb.ToString(); } else { ViewData["FileExist"] = false; ViewData["Grid"] = ""; } ViewData["UserType"] = user.UserType; } else { ViewData["FileExist"] = false; ViewData["Grid"] = ""; } return(View()); }
public static DataTable ImportExcelFile(string filePath, string sheetName) { IWorkbook hssfworkbook; #region//初始化信息 try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new XSSFWorkbook(file); } } catch (Exception e) { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } } #endregion NPOI.SS.UserModel.ISheet sheet = !string.IsNullOrEmpty(sheetName) ? hssfworkbook.GetSheet(sheetName) : hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); rows.MoveNext(); IRow row = null; try { row = (HSSFRow)rows.Current; } catch { row = (XSSFRow)rows.Current; } for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); //将第一列作为列表头 dt.Columns.Add(row.GetCell(j).ToString()); } while (rows.MoveNext()) { try { row = (HSSFRow)rows.Current; } catch { row = (XSSFRow)rows.Current; } DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { switch (cell.CellType) { case CellType.NUMERIC: if (HSSFDateUtil.IsCellDateFormatted(cell)) { dr[i] = cell.DateCellValue.ToString(); } else { dr[i] = cell.ToString(); } break; default: dr[i] = cell.ToString(); break; } } } dt.Rows.Add(dr); } return(dt); }
public async Task <JsonResult> Import() { IFormFile file = Request.Form.Files[0]; string folderName = "Upload"; string webRootPath = _hostingEnvironment.WebRootPath; string newPath = Path.Combine(webRootPath, folderName); //string result = "failed"; StringBuilder sb = new StringBuilder(); if (!Directory.Exists(newPath)) { Directory.CreateDirectory(newPath); } if (file.Length > 0) { string sFileExtension = Path.GetExtension(file.FileName).ToLower(); ISheet sheet; string fullPath = Path.Combine(newPath, file.FileName); using (var stream = new FileStream(fullPath, FileMode.Create)) { file.CopyTo(stream); stream.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row if (headerRow == null) { return(Json("failed")); } int cellCount = headerRow.LastCellNum; //sb.Append("<table class='table'><tr>"); for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } //sb.Append("<th>" + cell.ToString() + "</th>"); } //sb.Append("</tr>"); //sb.AppendLine("<tr>"); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { bool upt = false; Assess_Info m = new Assess_Info(); IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } for (int j = row.FirstCellNum; j < cellCount; j++) { //var r = _context.Assess.Where(c => c.UIC == row.GetCell((j + 1)).ToString()).FirstOrDefault(); //if (r == null && upt == false) //{ if (j == 1) { m.UIC = row.GetCell(j).ToString(); } if (j == 2) { m.CTEUIC = row.GetCell(j).ToString(); } if (j == 3) { m.CIPCode = row.GetCell(j).ToString(); } if (j == 4) { m.OBNo = row.GetCell(j).ToString(); } if (j == 5) { m.SchoolName = row.GetCell(j).ToString(); } if (j == 6) { m.FANo = row.GetCell(j).ToString(); } if (j == 7) { m.TestCode = row.GetCell(j).ToString(); } if (j == 8) { m.TestName = row.GetCell(j).ToString(); } try { if (j == 9) { m.TestDate = Convert.ToDateTime(row.GetCell(j).ToString()); } } catch (Exception ex) { var mx = ex; } if (j == 10) { m.Teacher = row.GetCell(j).ToString(); } if (j == 11) { m.Cluster = row.GetCell(j).ToString(); } if (j == 12) { m.LastName = row.GetCell(j).ToString(); } if (j == 13) { m.FirstName = row.GetCell(j).ToString(); } try { if (j == 14) { m.AssessScore = Convert.ToDecimal(row.GetCell(j).ToString()); } } catch (Exception ex) { var mx = ex; } if (j == 15) { m.AssessYear = row.GetCell(j).ToString(); } if (j == 16) { m.PassFail = row.GetCell(j).ToString(); } try { if (j == 17) { if (row.GetCell(j) == null) { m.UpdateDate = new DateTime(1 / 1 / 0001); } m.UpdateDate = Convert.ToDateTime(row.GetCell(j).ToString()); } } catch (Exception ex) { var mx = ex; } if (j == 18) { m.UpdateBy = row.GetCell(j).ToString(); } if (j == 19) { m.Comment = row.GetCell(j).ToString(); } try { if (j == 20) { m.encLastName = Convert.ToByte(row.GetCell(j).ToString()); } } catch (Exception ex) { var mx = ex; } try { if (j == 21) { m.encFirstName = Convert.ToByte(row.GetCell(j).ToString()); } } catch (Exception ex) { var mx = ex; } try { if (j == 22) { m.Accepted = Convert.ToChar(row.GetCell(j).ToString()); } } catch (Exception ex) { var mx = ex; } if (j >= 23) { _context.Add(m); await _context.SaveChangesAsync(); } //} //else if (upt == false) //{ // r.CTEUIC = row.GetCell(2).ToString(); // r.CIPCode = row.GetCell(3).ToString(); // r.OBNo = row.GetCell(4).ToString(); // r.SchoolName = row.GetCell(5).ToString(); // r.FANo = row.GetCell(6).ToString(); // r.TestCode = row.GetCell(7).ToString(); // r.TestName = row.GetCell(8).ToString(); // try // { // r.TestDate = Convert.ToDateTime(row.GetCell(9).ToString()); // } // catch (Exception ex) // { // var mx = ex; // } // r.Teacher = row.GetCell(10).ToString(); // r.Cluster = row.GetCell(11).ToString(); // r.LastName = row.GetCell(12).ToString(); // r.FirstName = row.GetCell(13).ToString(); // try { r.AssessScore = Convert.ToDecimal(row.GetCell(14).ToString()); } // catch (Exception ex) { var mx = ex; } // r.AssessYear = row.GetCell(15).ToString(); // r.PassFail = row.GetCell(16).ToString(); // try // { // { // if (row.GetCell(17) == null) { r.UpdateDate = new DateTime(1 / 1 / 0001); } // else { r.UpdateDate = Convert.ToDateTime(row.GetCell(17).ToString()); } // } // } // catch (Exception ex) { var mx = ex; } // r.UpdateBy = row.GetCell(18).ToString(); // r.Comment = row.GetCell(19).ToString(); // try { r.encLastName = Convert.ToByte(row.GetCell(20).ToString()); } // catch (Exception ex) { var mx = ex; } // try { r.encFirstName = Convert.ToByte(row.GetCell(21).ToString()); } // catch (Exception ex) { var mx = ex; } // try { r.Accepted = Convert.ToChar(row.GetCell(22).ToString()); } // catch (Exception ex) { var mx = ex; } // _context.Update(r); // _context.SaveChanges(); // upt = true; //} } } } } var Assess = (from a in _context.Assess select a).ToList(); AssessmentViewModel AVM = new AssessmentViewModel(); AVM.Assessments = Assess; return(Json(AVM)); }
private void OnPostImport(Importer import) { IFormFile file = import.File; string folderName = "Upload"; string webRootPath = _hostingEnvironment.WebRootPath; string newPath = Path.Combine(webRootPath, folderName); StringBuilder sb = new StringBuilder(); if (!Directory.Exists(newPath)) { Directory.CreateDirectory(newPath); } if (file.Length > 0) { string sFileExtension = Path.GetExtension(file.FileName).ToLower(); ISheet sheet; string fullPath = Path.Combine(newPath, file.FileName); Debug.WriteLine(file.ToString()); Debug.WriteLine(fullPath); CentricsContext context = HttpContext.RequestServices.GetService(typeof(Centrics.Models.CentricsContext)) as CentricsContext; using (var stream = new FileStream(fullPath, FileMode.Create)) { Debug.WriteLine(stream.ToString()); Debug.WriteLine(stream.CanRead); file.CopyTo(stream); stream.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; //sb.Append("<table class='table'><tr>"); // notneeded? for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } //sb.Append("<th>" + cell.ToString() + "</th>"); } //sb.Append("</tr>"); //sb.AppendLine("<tr>"); List <ClientAddress> ListCA = context.getAllClientAddress(); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } //if (context.GetClientAddressList(row.GetCell(0).ToString()).ClientCompany != "") continue; bool skip = false; // for ignoring //for (int k = 0; k<ListCA.Count; k++) //{ // if (row.GetCell(0).ToString() == ListCA[i].ClientCompany) // { // skip = true; // } else if (row.GetCell(1).ToString() == ListCA[i].CustomerID) // { // skip = true; // } //} //if (skip) //{ // continue; //} ClientAddress cA = new ClientAddress(); cA.EmailList = new List <string>(); cA.TitleList = new List <string>(); cA.Addresslist = new List <string>(); cA.ContactList = new List <string>(); cA.ContactNoList = new List <string>(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { //trying to get the value of an empty cell as empty cell is neither 0 nor null nor "". row.CreateCell(200); if (row.GetCell(j).ToString() == row.GetCell(200).ToString()) { //assuming nothing is at 200 if not get fked? continue; } if (j == 0) { cA.ClientCompany = row.GetCell(j).ToString(); } else if (j == 1) { cA.CustomerID = row.GetCell(j).ToString(); } else if (j == 2) { string stringer = ""; stringer = row.GetCell(j).ToString(); cA.Addresslist.Add(stringer); } else if (j == 3) { cA.TitleList.Add(row.GetCell(j).ToString()); } else if (j == 4) { cA.ContactList.Add(row.GetCell(j).ToString()); } else if (j == 5) { cA.ContactNoList.Add(row.GetCell(j).ToString()); } else if (j == 6) { cA.EmailList.Add(row.GetCell(j).ToString()); } else if (j == 7) { cA.TitleList.Add(row.GetCell(j).ToString()); } else if (j == 8) { cA.ContactList.Add(row.GetCell(j).ToString()); } else if (j == 9) { cA.ContactNoList.Add(row.GetCell(j).ToString()); } else if (j == 10) { cA.EmailList.Add(row.GetCell(j).ToString()); } } } //sb.AppendLine("</tr>"); Debug.WriteLine("i am done" + "1"); context.Imported(cA); } //sb.Append("</table>"); } } //return this.Content(sb.ToString()); }
public IActionResult ImportFile(IFormFile formFile) { string folderName = "UploadExcel"; string webRootPath = hostingEnvironment.ContentRootPath; string newPath = Path.Combine(webRootPath, folderName); StringBuilder sb = new StringBuilder(); if (!Directory.Exists(newPath)) { Directory.CreateDirectory(newPath); } if (formFile.Length > 0) { string sFileExtension = Path.GetExtension(formFile.FileName).ToLower(); ISheet sheet; string fullPath = Path.Combine(newPath, formFile.FileName); using (var stream = new FileStream(fullPath, FileMode.Create)) { formFile.CopyTo(stream); stream.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; sb.Append("<table class='table table-bordered'><tr>"); for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } sb.Append("<th>" + cell.ToString() + "</th>"); } sb.Append("</tr>"); sb.AppendLine("<tr>"); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { sb.Append("<td>" + row.GetCell(j).ToString() + "</td>"); } } sb.AppendLine("</tr>"); } sb.Append("</table>"); } } return(this.Content(sb.ToString())); }
public static string DisplayTable(IFormFile file) { StringBuilder sb = new StringBuilder(); string sFileExtension = Path.GetExtension(file.FileName).ToLower(); if (file.Length > 0) { ISheet sheet; using (var stream = file.OpenReadStream()) { stream.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; sb.Append("<table class='table table-bordered'><tr>"); for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } sb.Append("<th>" + cell.ToString() + "</th>"); } sb.Append("</tr>"); sb.AppendLine("<tr>"); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == NPOI.SS.UserModel.CellType.Blank)) { continue; } for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { sb.Append("<td>" + row.GetCell(j).ToString() + "</td>"); } } sb.AppendLine("</tr>"); } sb.Append("</table>"); } } return(sb.ToString()); }
/// <summary> /// 导入excel文件 /// </summary> /// <param name="filePath"></param> /// <returns></returns> private static string ImportExcel(string filePath) { string result = ""; NPOI.SS.UserModel.ISheet sheet = null; try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); sheet = hssfworkbook.GetSheetAt(0); } } catch (Exception) { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { NPOI.XSSF.UserModel.XSSFWorkbook hssfworkbook2 = new NPOI.XSSF.UserModel.XSSFWorkbook(file); sheet = hssfworkbook2.GetSheetAt(0); } } System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); } while (rows.MoveNext()) { NPOI.SS.UserModel.IRow row = (NPOI.SS.UserModel.IRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } if (row.LastCellNum != -1) { if ((dr[0] == null | dr[0].ToString() == "") && (dr[1] == null | dr[1].ToString() == "") && (dr[2] == null | dr[2].ToString() == "")) { } else { dt.Rows.Add(dr); } } } dt = Distinct(dt, new string[] { "A", "B", "C" }); DataView myDataView = new DataView(dt); string[] strComuns = { "C" }; if (myDataView.ToTable(true, strComuns).Rows.Count < dt.Rows.Count) { result = "用户表存在相同的账号数据"; return(result); } DbHelper dp = new DbHelper(); foreach (DataRow dr in dt.Rows) { string strsql = "insert into hs_user1 values ('" + dr["B"] + "','" + dr["C"] + "')"; dp.RunTxt(strsql); } result = "导入成功"; return(result); }
//public static Tuple<List<string>, List<string>> ReturnMultipleList(int[] array, int number) //{ // return Tuple.Create(list1, list2); //} public static List <Item> CreateItem(Caterer model) { _tempList = new List <Item>(); _temp = new Item(); string json = null; var stream2 = new MemoryStream(model.Document); IFormFile file = new FormFile(stream2, 0, model.Document.Length, "name", "filename.xlsx"); string folderName = "UploadExcel"; string sFileExtension = Path.GetExtension(file.FileName).ToLower(); List <string> ItemLista = new List <string>(); List <string> IngredientsLista = new List <string>(); if (file.Length > 0) { ISheet sheet; using (var stream = file.OpenReadStream()) { //json = ReadExcelasJSON(stream); stream.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == NPOI.SS.UserModel.CellType.Blank)) { continue; } for (int celNumber = row.FirstCellNum; celNumber < cellCount; celNumber++) { if (row.GetCell(celNumber) != null) { var data = row.GetCell(celNumber).ToString(); switch (celNumber) { case 0: _temp.Name = data; break; case 1: _temp.Price = int.Parse(data); break; case 2: _temp.Ingredents = parseToList(data, _DELIMITER); break; default: return(null); } } } _tempList.Add(_temp); } } } return(_tempList); }