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