webapi 导入excel处理数据

显示全部楼层 · 2019-8-16 13:58:23

参考资料

　　　　 https://blog.csdn.net/pan_junbiao/article/details/82935992

　　　　　https://www.cnblogs.com/dansediao/p/5482467.html

　　　　　https://www.cnblogs.com/shiyh/p/7478241.html

excel转成datatable工具类

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
namespace ELearning.Common.Extensions
{
public static class ExcelHelp
{
/// <summary>
/// Id的列号
/// </summary>
private const int EXCEL_COL_PLAT_FORM_INDUSTRY = 14;
//获取列名委托方法
public delegate string GetColumnName(string columnName);
#region Excel2007
/// <summary>
/// excel文件流转化成datatable
/// </summary>
public static DataTable ExcelToTableForXLSX(Stream fileStream, bool haveNote = false)
{
var dt = new DataTable();
using (var fs = fileStream)
{
var xssfworkbook = new XSSFWorkbook(fs);
var sheet = xssfworkbook.GetSheetAt(0);
//表头判断是否包含备注
var firstRowNum = sheet.FirstRowNum;
if (haveNote)
{
firstRowNum += 1;
}
var header = sheet.GetRow(firstRowNum);
var columns = new List<int>();
for (var i = 0; i < header.LastCellNum; i++)
{
var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var dr = dt.NewRow();
var hasValue = false;
if (sheet.GetRow(i) == null)
{
continue;
}
foreach (var j in columns)
{
var cell = sheet.GetRow(i).GetCell(j);
if (cell != null && cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (DateUtil.IsCellDateFormatted(cell)) //日期类型
{
dr[j] = cell.DateCellValue;
}
else //其他数字类型
{
dr[j] = cell.NumericCellValue;
}
}
else
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
}
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xlsx)
/// </summary>
/// <param name="file"></param>
/// <param name="haveNote">是否有备注</param>
/// <returns></returns>
public static DataTable ExcelToTableForXLSX(string file, bool haveNote = false)
{
var dt = new DataTable();
using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
var xssfworkbook = new XSSFWorkbook(fs);
var sheet = xssfworkbook.GetSheetAt(0);
//表头判断是否包含备注
var firstRowNum = sheet.FirstRowNum;
if (haveNote)
{
firstRowNum += 1;
}
var header = sheet.GetRow(firstRowNum);
var columns = new List<int>();
for (var i = 0; i < header.LastCellNum; i++)
{
var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var dr = dt.NewRow();
var hasValue = false;
if (sheet.GetRow(i) == null)
{
continue;
}
foreach (var j in columns)
{
var cell = sheet.GetRow(i).GetCell(j);
if (cell != null && cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (DateUtil.IsCellDateFormatted(cell)) //日期类型
{
dr[j] = cell.DateCellValue;
}
else //其他数字类型
{
dr[j] = cell.NumericCellValue;
}
}
else
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
}
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// (包含判断首行备注是否为空)将Excel文件中的数据读出到DataTable中(xlsx)
/// </summary>
/// <param name="file">文件路径</param>
/// <param name="excelFirstName">excel中第一条数据列名</param>
/// <param name="noteExist">是否存在填写须知（根据表格内容判断）</param>
/// <param name="haveNote">是否有备注</param>
/// <returns></returns>
public static DataTable ExcelToTableForXLSX(string file, string excelFirstName, out bool noteExist, bool haveNote = false)
{
var dt = new DataTable();
using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
var xssfworkbook = new XSSFWorkbook(fs);
var sheet = xssfworkbook.GetSheetAt(0);
//表头判断是否包含备注
var firstRowNum = sheet.FirstRowNum;
noteExist = haveNote;
if (haveNote)
{
firstRowNum += 1;
var obj = GetValueTypeForXLSX(sheet.GetRow(firstRowNum).GetCell(0) as XSSFCell);
if (obj != null && !string.IsNullOrEmpty(excelFirstName))
{
//判断第二行第一列是否包含excel数据第一列列名，假如包含，则表示存在填写须知
if (!obj.ToString().Contains(excelFirstName))
{
noteExist = false;
firstRowNum -= 1;
}
}
}
var header = sheet.GetRow(firstRowNum);
var columns = new List<int>();
for (var i = 0; i < header.LastCellNum; i++)
{
var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var dr = dt.NewRow();
var hasValue = false;
if (sheet.GetRow(i) == null)
{
continue;
}
foreach (var j in columns)
{
var cell = sheet.GetRow(i).GetCell(j);
if (cell != null && cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (DateUtil.IsCellDateFormatted(cell)) //日期类型
{
dr[j] = cell.DateCellValue;
}
else //其他数字类型
{
dr[j] = cell.NumericCellValue;
}
}
else
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
}
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xlsx)包括空行
/// </summary>
/// <param name="haveNote">是否有备注</param>
public static DataTable ExcelToTableWithEmptyRowForXLSX(string file, bool haveNote = false)
{
var dt = new DataTable();
using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
var xssfworkbook = new XSSFWorkbook(fs);
var sheet = xssfworkbook.GetSheetAt(0);
//表头判断是否包含备注
var firstRowNum = sheet.FirstRowNum;
if (haveNote)
{
firstRowNum += 1;
}
var header = sheet.GetRow(firstRowNum);
var columns = new List<int>();
for (var i = 0; i < header.LastCellNum; i++)
{
var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var dr = dt.NewRow();
IRow row = sheet.GetRow(i) as XSSFRow;
foreach (var j in columns)
{
if (row != null)
{
ICell cell = row.GetCell(j) as XSSFCell;
if (cell != null && cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (DateUtil.IsCellDateFormatted(cell)) //日期类型
{
dr[j] = cell.DateCellValue;
}
else //其他数字类型
{
dr[j] = cell.NumericCellValue;
}
}
else
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
}
}
}
dt.Rows.Add(dr);
}
}
return dt;
}
/// <summary>
/// (包含判断首行备注是否为空)将Excel文件中的数据读出到DataTable中(xls)包括空行
/// <param name="file">文件路径</param>
/// <param name="excelFirstName">excel中第一条数据列名</param>
/// <param name="noteExist">是否存在填写须知（根据表格内容判断）</param>
/// <param name="haveNote">是否有备注</param>
/// </summary>
public static DataTable ExcelToTableWithEmptyRowForXLSX(string file, string excelFirstName, out bool noteExist, bool haveNote = false)
{
var dt = new DataTable();
using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
var xssfworkbook = new XSSFWorkbook(fs);
var sheet = xssfworkbook.GetSheetAt(0);
//表头判断是否包含备注
var firstRowNum = sheet.FirstRowNum;
noteExist = haveNote;
if (haveNote)
{
firstRowNum += 1;
var obj = GetValueTypeForXLS(sheet.GetRow(firstRowNum).GetCell(0) as HSSFCell);
if (obj != null && !string.IsNullOrEmpty(excelFirstName))
{
//判断第二行第一列是否包含excel数据第一列列名，假如包含，则表示存在填写须知
if (!obj.ToString().Contains(excelFirstName))
{
noteExist = false;
firstRowNum -= 1;
}
}
}
var header = sheet.GetRow(firstRowNum);
var columns = new List<int>();
for (var i = 0; i < header.LastCellNum; i++)
{
var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var dr = dt.NewRow();
IRow row = sheet.GetRow(i) as XSSFRow;
foreach (var j in columns)
{
if (row != null)
{
ICell cell = row.GetCell(j) as XSSFCell;
if (cell != null && cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (DateUtil.IsCellDateFormatted(cell)) //日期类型
{
dr[j] = cell.DateCellValue;
}
else //其他数字类型
{
dr[j] = cell.NumericCellValue;
}
}
else
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
}
}
}
dt.Rows.Add(dr);
}
}
return dt;
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx),生成excel文件的字节数组
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static byte[] TableToExcelForXLSX(DataTable dt)
{
var xssfworkbook = new XSSFWorkbook();
var sheet = xssfworkbook.CreateSheet("Sheet1");
//表头
var row = sheet.CreateRow(0);
dt.Columns.Remove("SignTime");//去掉signtime字段，留下格式化以后的
for (var i = 0; i < dt.Columns.Count; i++)
{
var cell = row.CreateCell(i);
//列名称,数据库中字段
var columnName = dt.Columns[i].ColumnName;
var convertColumnName = "";
switch (columnName)
{
case "UserName":
convertColumnName = "用户名";
break;
case "Name":
convertColumnName = "真实姓名";
break;
case "LastCourseTime":
convertColumnName = "最后上课日期";
break;
case "FinishRate":
convertColumnName = "看课完成率";
break;
case "Phone":
convertColumnName = "手机号";
break;
case "CardNo":
convertColumnName = "身份证号";
break;
case "RefereeName":
convertColumnName = "推荐人";
break;
case "SignTimeStr":
convertColumnName = "报名日期";
break;
default:
convertColumnName = "无列名";
break;
}
cell.SetCellValue(convertColumnName);
}
//数据
for (var i = 0; i < dt.Rows.Count; i++)
{
var row1 = sheet.CreateRow(i + 1);
for (var j = 0; j < dt.Columns.Count; j++)
{
var cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
var stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
return buf;
}
/// <summary>
/// 将DataTable转化为excel
/// </summary>
/// <param name="dt">DataTable(去掉无用字段的DataTable)</param>
/// <param name="getColumnName">获取列名的方法</param>
/// <returns>excel</returns>
public static byte[] GetExcelForXLSX(DataTable dt, GetColumnName getColumnName)
{
var xssfworkbook = new XSSFWorkbook();
var sheet = xssfworkbook.CreateSheet("Sheet");
//表头
var row = sheet.CreateRow(0);
for (var i = 0; i < dt.Columns.Count; i++)
{
var cell = row.CreateCell(i);
//列名称,数据库中字段
var columnName = dt.Columns[i].ColumnName;
var convertColumnName = getColumnName(columnName);
cell.SetCellValue(convertColumnName);
}
//数据
for (var i = 0; i < dt.Rows.Count; i++)
{
var row1 = sheet.CreateRow(i + 1);
for (var j = 0; j < dt.Columns.Count; j++)
{
var cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
var stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
return buf;
}
/// <summary>
/// 将DataTable转化为excel
/// </summary>
/// <param name="dt">DataTable(去掉无用字段的DataTable)</param>
/// <returns>excel</returns>
public static byte[] GetExcelForXLSX(DataTable dt)
{
var xssfworkbook = new XSSFWorkbook();
var sheet = xssfworkbook.CreateSheet("Sheet");
//表头
var row = sheet.CreateRow(0);
for (var i = 0; i < dt.Columns.Count; i++)
{
var cell = row.CreateCell(i);
//列名称,数据库中字段
var columnName = dt.Columns[i].ColumnName;
cell.SetCellValue(columnName);
}
//数据
for (var i = 0; i < dt.Rows.Count; i++)
{
var row1 = sheet.CreateRow(i + 1);
for (var j = 0; j < dt.Columns.Count; j++)
{
var cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
var stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
return buf;
}
/// <summary>
/// 将DataTable转化为excel(招生计划完成率报表使用)
/// </summary>
/// <param name="dt">DataTable(去掉无用字段的DataTable)</param>
/// <param name="getColumnName">获取列名的方法</param>
/// <returns>excel</returns>
public static byte[] GetExcelForXlsxForRateReport(DataTable dt, GetColumnName getColumnName, string tableName)
{
var xssfworkbook = new XSSFWorkbook();
var sheet = xssfworkbook.CreateSheet("Sheet");
//表头
var row = sheet.CreateRow(0);
//顶级标头
for (var i = 0; i < dt.Columns.Count; i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue(tableName);
}
//二级表头
var sencondRow = sheet.CreateRow(2);
for (var i = 0; i < dt.Columns.Count; i++)
{
var cell = sencondRow.CreateCell(i);
//列名称,数据库中字段
var columnName = dt.Columns[i].ColumnName;
var convertColumnName = getColumnName(columnName);
cell.SetCellValue(convertColumnName);
}
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 6)); //合并招生完成率中表名
//数据
for (var i = 0; i < dt.Rows.Count; i++)
{
var row1 = sheet.CreateRow(i + 3);
for (var j = 0; j < dt.Columns.Count; j++)
{
var cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
var stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
return buf;
}
/// <summary>
/// 将DataTable转化为excel，表头自己填充,招生计划完成率导出使用（未封装）
/// </summary>
/// <param name="dt">DataTable(去掉无用字段的DataTable)</param>
/// <param name="dataStartRow">excel中数据开始的行数（从0开始）</param>
/// <returns>excel</returns>
public static byte[] GetExcelForXLSXForRate(DataTable dt, GetColumnName getColumnName)
{
var xssfworkbook = new XSSFWorkbook();
var style = xssfworkbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Center;
var sheet = xssfworkbook.CreateSheet("Sheet1");
//表头
var row = sheet.CreateRow(0);
//顶级标头
for (var i = 0; i < dt.Columns.Count; i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue("convertColumnName");
var convertColumnName = "";
var columnName = dt.Columns[i].ColumnName;
convertColumnName = getColumnName(columnName);
cell.SetCellValue(convertColumnName);
}
//二级表头
var rowSencond = sheet.CreateRow(1);
for (var i = 2; i < dt.Columns.Count; i++)
{
var cell = rowSencond.CreateCell(i);
//列名称,数据库中字段
if (i % 3 == 2)
{
cell.SetCellValue("完成");
}
else if (i % 3 == 0)
{
cell.SetCellValue("目标");
}
else
{
cell.SetCellValue("完成率");
}
}
//合并表头
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0)); //合并招生完成率中的年份
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 1, 1)); //合并招生完成率中的姓名
var r = 2; //合并单元格开始所在列
for (var range = 0; range < 13; range++) //13是指12个月加上年度合计
{
sheet.AddMergedRegion(new CellRangeAddress(0, 0, r, r + 2));
r += 3;
}
//数据
for (var i = 0; i < dt.Rows.Count; i++)
{
var row1 = sheet.CreateRow(i + 2);
for (var j = 0; j < dt.Columns.Count; j++)
{
var cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
var stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
return buf;
}
/// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
#region Exce97-2003
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xls)
/// </summary>
/// <param name="file"></param>
/// <param name="haveNote"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLS(string file, bool haveNote = false)
{
var dt = new DataTable();
using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
var xssfworkbook = new HSSFWorkbook(fs);
var sheet = xssfworkbook.GetSheetAt(0);
//表头判断是否包含备注
var firstRowNum = sheet.FirstRowNum;
if (haveNote)
{
firstRowNum += 1;
}
var header = sheet.GetRow(firstRowNum);
var columns = new List<int>();
for (var i = 0; i < header.LastCellNum; i++)
{
var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var dr = dt.NewRow();
var hasValue = false;
foreach (var j in columns)
{
var cell = sheet.GetRow(i).GetCell(j);
if (cell != null && cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (DateUtil.IsCellDateFormatted(cell)) //日期类型
{
dr[j] = cell.DateCellValue;
}
else //其他数字类型
{
dr[j] = cell.NumericCellValue;
}
}
else
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
}
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xls)
/// </summary>
/// <param name="file">文件路径</param>
/// <param name="excelFirstName">excel中第一条数据列名</param>
/// <param name="noteExist">是否存在填写须知（根据表格内容判断）</param>
/// <param name="haveNote">是否有备注</param>
/// <returns></returns>
public static DataTable ExcelToTableForXLS(string file, string excelFirstName, out bool noteExist, bool haveNote = false)
{
var dt = new DataTable();
using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
var xssfworkbook = new HSSFWorkbook(fs);
var sheet = xssfworkbook.GetSheetAt(0);
//表头判断是否包含备注
var firstRowNum = sheet.FirstRowNum;
noteExist = haveNote;
if (haveNote)
{
firstRowNum += 1;
var obj = GetValueTypeForXLS(sheet.GetRow(firstRowNum).GetCell(0) as HSSFCell);
if (obj != null && !string.IsNullOrEmpty(excelFirstName))
{
//判断第二行第一列是否包含excel数据第一列列名，假如包含，则表示存在填写须知
if (!obj.ToString().Contains(excelFirstName))
{
noteExist = false;
firstRowNum -= 1;
}
}
}
var header = sheet.GetRow(firstRowNum);
var columns = new List<int>();
for (var i = 0; i < header.LastCellNum; i++)
{
var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var dr = dt.NewRow();
var hasValue = false;
foreach (var j in columns)
{
var cell = sheet.GetRow(i).GetCell(j);
if (cell != null && cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (DateUtil.IsCellDateFormatted(cell)) //日期类型
{
dr[j] = cell.DateCellValue;
}
else //其他数字类型
{
dr[j] = cell.NumericCellValue;
}
}
else
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
}
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
///将Excel文件中的数据读出到DataTable中(xls)包括空行
/// </summary>
public static DataTable ExcelToTableWithEmptyRowForXLS(string file, bool haveNote = false)
{
var dt = new DataTable();
using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
var xssfworkbook = new HSSFWorkbook(fs);
var sheet = xssfworkbook.GetSheetAt(0);
//表头判断是否包含备注
var firstRowNum = sheet.FirstRowNum;
if (haveNote)
{
firstRowNum += 1;
}
var header = sheet.GetRow(firstRowNum);
var columns = new List<int>();
for (var i = 0; i < header.LastCellNum; i++)
{
var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var dr = dt.NewRow();
IRow row = sheet.GetRow(i) as HSSFRow;
foreach (var j in columns)
{
if (row != null)
{
ICell cell = row.GetCell(j) as HSSFCell;
if (cell != null && cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (DateUtil.IsCellDateFormatted(cell)) //日期类型
{
dr[j] = cell.DateCellValue;
}
else //其他数字类型
{
dr[j] = cell.NumericCellValue;
}
}
else
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
}
}
}
dt.Rows.Add(dr);
}
}
return dt;
}
/// <summary>
/// (包含判断首行备注是否为空)将Excel文件中的数据读出到DataTable中(xls)包括空行
/// <param name="file">文件路径</param>
/// <param name="excelFirstName">excel中第一条数据列名</param>
/// <param name="noteExist">是否存在填写须知（根据表格内容判断）</param>
/// <param name="haveNote">是否有备注</param>
/// </summary>
public static DataTable ExcelToTableWithEmptyRowForXLS(string file, string excelFirstName, out bool noteExist, bool haveNote = false)
{
var dt = new DataTable();
using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
var xssfworkbook = new HSSFWorkbook(fs);
var sheet = xssfworkbook.GetSheetAt(0);
//表头判断是否包含备注
var firstRowNum = sheet.FirstRowNum;
noteExist = haveNote;
if (haveNote)
{
firstRowNum += 1;
var obj = GetValueTypeForXLS(sheet.GetRow(firstRowNum).GetCell(0) as HSSFCell);
if (obj != null && !string.IsNullOrEmpty(excelFirstName))
{
//判断第二行第一列是否包含excel数据第一列列名，假如包含，则表示存在填写须知
if (!obj.ToString().Contains(excelFirstName))
{
noteExist = false;
firstRowNum -= 1;
}
}
}
var header = sheet.GetRow(firstRowNum);
var columns = new List<int>();
for (var i = 0; i < header.LastCellNum; i++)
{
var obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var dr = dt.NewRow();
IRow row = sheet.GetRow(i) as HSSFRow;
foreach (var j in columns)
{
if (row != null)
{
ICell cell = row.GetCell(j) as HSSFCell;
if (cell != null && cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (DateUtil.IsCellDateFormatted(cell)) //日期类型
{
dr[j] = cell.DateCellValue;
}
else //其他数字类型
{
dr[j] = cell.NumericCellValue;
}
}
else
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
}
}
}
dt.Rows.Add(dr);
}
}
return dt;
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xls)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcelForXLS(DataTable dt, string file)
{
var xssfworkbook = new HSSFWorkbook();
var sheet = xssfworkbook.CreateSheet("Sheet1");
//表头
var row = sheet.CreateRow(0);
for (var i = 0; i < dt.Columns.Count; i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (var i = 0; i < dt.Rows.Count; i++)
{
var row1 = sheet.CreateRow(i + 1);
for (var j = 0; j < dt.Columns.Count; j++)
{
var cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
var stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (var fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
/// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLS(HSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
#region 转化实体为dataTable
/// <summary>
/// Convert a List{T} to a DataTable.
/// </summary>
public static DataTable ToDataTable<T>(this List<T> items)
{
var tb = new DataTable(typeof(T).Name);
PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo prop in props)
{
Type t = GetCoreType(prop.PropertyType);
tb.Columns.Add(prop.Name, t);
}
foreach (T item in items)
{
var values = new object[props.Length];
for (int i = 0; i < props.Length; i++)
{
values[i] = props[i].GetValue(item, null);
}
tb.Rows.Add(values);
}
return tb;
}
/// <summary>
/// Return underlying type if type is Nullable otherwise return the type
/// </summary>
public static Type GetCoreType(Type t)
{
if (t != null && IsNullable(t))
{
if (!t.IsValueType)
{
return t;
}
else
{
return Nullable.GetUnderlyingType(t);
}
}
else
{
return t;
}
}
/// <summary>
/// Determine of specified type is nullable
/// </summary>
public static bool IsNullable(Type t)
{
return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
}
#endregion
#region datatable to list
/// <summary>
/// DataTable转成List
/// </summary>
public static List<T> ToDataList<T>(this DataTable dt)
{
var list = new List<T>();
var plist = new List<PropertyInfo>(typeof(T).GetProperties());
foreach (DataRow item in dt.Rows)
{
var s = Activator.CreateInstance<T>();
for (var i = 0; i < dt.Columns.Count; i++)
{
var info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
if (info != null)
{
try
{
if (!Convert.IsDBNull(item[i]))
{
object v = null;
if (info.PropertyType.ToString().Contains("System.Nullable"))
{
v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
}
else
{
v = Convert.ChangeType(item[i], info.PropertyType);
}
info.SetValue(s, v, null);
}
}
catch (Exception ex)
{
throw new Exception("字段[" + info.Name + "]转换出错," + ex.Message);
}
}
}
list.Add(s);
}
return list;
}
#endregion
}
}

复制代码

View Code

SqlBulkCopyHelper工具类

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace ELearning.Common.Helpers
{
public class SqlBulkCopyHelper
{
public static void SaveTable(DataTable dtTable)
{
var connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
var sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction) {BulkCopyTimeout = 5000};
try
{
sbc.DestinationTableName = dtTable.TableName;
sbc.WriteToServer(dtTable);
}
catch (Exception ex)
{
//处理异常
}
finally
{
//sqlcmd.Clone();
//srcConnection.Close();
//desConnection.Close();
}
}
}
}

复制代码

对应excel实体类：

/// <summary>
/// 导入用户视图模型
/// </summary>
public class InsertAdminUsersViewModel
{
public string 性别 { set; get; }
public string 出生日期 { set; get; }
public string 身份证号 { set; get; }
public string 经销商名称 { set; get; }
public string 经销商岗位 { set; get; }
public string 更新时间 { set; get; }
}

复制代码

导入信息接口（InsertAdminUsers）：

/// <summary>
/// 导入用户
/// </summary>
[HttpPost, Route("api/user/InsertAdminUsers")]
[AllowAnonymous]
public object InsertAdminUsers()
{
var filelist = HttpContext.Current.Request.Files;
var users = new List<InsertAdminUsersViewModel>();
if (filelist.Count > 0)
{
for (var i = 0; i < filelist.Count; i++)
{
var file = filelist[i];
var dataTable = ExcelHelp.ExcelToTableForXLSX(file.InputStream);//excel转成datatable
users = dataTable.ToDataList<InsertAdminUsersViewModel>();//datatable转成list
}
}
var succe = new List<ESysUser>();
var faile = new List<ESysUser>();
var names = userService.FindList(u => !u.IsDelete).Select(u => u.LoginName).ToList();
//数据list转成数据库实体对应的list
foreach (var u in users)
{
if (string.IsNullOrEmpty(u.状态) || !u.状态.Equals("1"))
continue;
var s = new ESysUser
{
CreateTime = DateTime.Now,
Birthday = DateTime.ParseExact(u.出生日期, "yyyyMMdd", CultureInfo.CurrentCulture),
Email = string.Empty,
IsDelete = false,
ModifyTime = DateTime.ParseExact(u.更新时间, "yyyyMMddHHmmssfff", CultureInfo.CurrentCulture),
UserID = GuidUtil.NewSequentialId(),
UserName = u.职员名称,
UserType = "JXS",
Unumber = u.职员代码,
AgentJobName = u.经销商岗位,
AgentName = u.经销商名称.
CardNo = u.身份证号
};
if (!string.IsNullOrEmpty(s.CardNo) && s.CardNo.Length > 14)
{
var str = s.CardNo.Substring(6, 8);
try
{
s.Birthday = DateTime.ParseExact(str, "yyyyMMdd", CultureInfo.CurrentCulture);
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
var t = names.Where(f => f == s.LoginName);
var p1 = succe.Where(o => o.LoginName == s.LoginName);
if (t.Any() || p1.Any())
{
s.Remark = "登录名重复";
faile.Add(s);
}
else
{
succe.Add(s);
}
}
var dt = succe.ToDataTable();//转成 SqlBulkCopy所需要的类型：datatable
if (string.IsNullOrEmpty(dt.TableName))
dt.TableName = "ESysUser";
var r = succe.Count;
SqlBulkCopyHelper.SaveTable(dt);//批量插入
var list = new { succeed = succe.Take(100).ToList(), failed = faile.Take(100).ToList() }; //数据太多的话，浏览器会崩溃
return OK(list);
}