- 我这里使用的是Sql Server数据库
- EXCEL文件里面的标题字段必须和代码写的字段一样否则查找不到该字段
- 数据库字段的限制：比如说你的数据库有5个字段，但是插入数据时候EXCEL只有四个字段，那么空的那个字段，一定要允许为空哦！！否则会插不进去的！！
- 大家只需要修改参数和引用，其他逻辑方法不用修改，如果您有把握可以修改再修改
- 如果您修改完参数和数据后还有方法报错，应该是您没有引用方法的命名空间，如有其他方法缺少请下方留言或私我我会更新
- 还是以我的练习数据库为例已下为大家详细展示
1、首先大家要有一个上传文件委托配置的类，大家可以新建一个类，名字大家自己取，类里面的代码内容我给大家写到了下方直接复制粘贴即可！不需要做更改直接贴！！，记得添加引用！！
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Runtime.CompilerServices;
namespace ARC.Common.Help
{
/// <summary>
/// 上传文件委托配置类，配置的委托将用于全局
/// </summary>
public class UploadHelp
{
/// <summary>
/// 文件上传本地路径委托
/// </summary>
private static Func<byte[], string, UploadFileType, string> func = null;
/// <summary>
/// 文件上传Azure委托
/// </summary>
private static Func<byte[], string, UploadFileType, string> funcBlob = null;
/// <summary>
/// 分片上传--云存储
/// </summary>
private static Func<byte[], string, UploadFileType, string> _funcBlobBlack = null;
/// <summary>
/// 分片上传--本地
/// </summary>
private static Func<byte[], string, UploadFileType, string> _funcLocationBlack = null;
/// <summary>
/// 合并分片--云存储
/// </summary>
private static Func<List<string>, string, string, UploadFileType, string> _mergeBlobBlack = null;
/// <summary>
/// 合并分片--本地
/// </summary>
private static Func<List<string>, string, string, UploadFileType, string> _mergeLocationBlack = null;
#region 文件流操作
private static Func<Stream, string, UploadFileType, string> funcBlobStream = null;
/// <summary>
/// 文件上传本地路径委托
/// </summary>
private static Func<Stream, string, UploadFileType, string> funcStream = null;
/// <summary>
/// 分片上传--云存储
/// </summary>
private static Func<Stream, string, UploadFileType, string> _funcBlobBlackStream = null;
/// <summary>
/// 分片上传--本地
/// </summary>
private static Func<Stream, string, UploadFileType, string> _funcLocationBlackStream = null;
#endregion
/// <summary>
/// 默认上传方式
/// </summary>
private static UploadFunEnum defaultFuncEnum;
/// <summary>
/// 默认分片上传方式
/// </summary>
private static UploadFunEnum defaultBlackEnum;
#region 二进制流
/// <summary>
/// 初始化委托
/// </summary>
/// <param name="_func"></param>
public static void Init(Func<byte[], string, UploadFileType, string> _func, UploadFunEnum funcEnum = UploadFunEnum.location)
{
switch (funcEnum)
{
case UploadFunEnum.location:
func = _func;
break;
case UploadFunEnum.blob:
funcBlob = _func;
break;
case UploadFunEnum.LocationBlack:
_funcLocationBlack = _func;
break;
case UploadFunEnum.BlobBlack:
_funcBlobBlack = _func;
break;
case UploadFunEnum.defalut:
break;
}
}
/// <summary>
/// 初始化默认上传方式
/// </summary>
/// <param name="_defaultFuncEnum"></param>
public static void InitDefaultUpload(UploadFunEnum _defaultFuncEnum)
{
defaultFuncEnum = _defaultFuncEnum;
//是否为云存储，非云存储直接使用初始化本地分片上传
defaultBlackEnum = _defaultFuncEnum == UploadFunEnum.blob ? UploadFunEnum.BlobBlack : UploadFunEnum.LocationBlack;
}
/// <summary>
/// 通过委托上传文件
/// </summary>
/// <param name="bytes">字节数组</param>
/// <param name="fileName">文件名</param>
/// <param name="fileType">文件类型</param>
/// <returns></returns>
public static string Execute(byte[] bytes, string fileName, UploadFileType fileType, UploadFunEnum funcEnum = UploadFunEnum.defalut)
{
var funcBytes = funcEnum switch
{
UploadFunEnum.blob => funcBlob,
UploadFunEnum.location => func,
_ => defaultBlackEnum == UploadFunEnum.location ? func : funcBlob
};
if (funcBytes == null) throw new Exception("请先初始化上传委托");
return funcBytes.Invoke(bytes, fileName, fileType);
}
/// <summary>
/// 分片上传处理
/// </summary>
/// <param name="bytes"></param>
/// <param name="fileDir"></param>
/// <param name="fileType"></param>
/// <param name="funEnum"></param>
/// <returns></returns>
public static string ExecuteBlack(byte[] bytes, string fileDir, UploadFileType fileType, UploadFunEnum funEnum = UploadFunEnum.defalut)
{
funEnum = funEnum == UploadFunEnum.defalut ? defaultBlackEnum : funEnum;
if (funEnum != UploadFunEnum.BlobBlack && funEnum != UploadFunEnum.LocationBlack)
{
return Execute(bytes, fileDir, fileType, funEnum);
}
if ((funEnum == UploadFunEnum.LocationBlack && (_funcLocationBlack == null || _mergeLocationBlack == null)) ||
(funEnum == UploadFunEnum.BlobBlack && (_funcBlobBlack == null || _mergeBlobBlack == null)))
{
throw new Exception("请先初始化分片上传委托");
}
var result = string.Empty;
if (funEnum == UploadFunEnum.LocationBlack)
{
result = _funcLocationBlack.Invoke(bytes, fileDir, fileType);
}
else
{
result = _funcBlobBlack.Invoke(bytes, fileDir, fileType);
}
return result;
}
#endregion
#region 文件流
public static void InitStream(Func<Stream, string, UploadFileType, string> _func, UploadFunEnum funEnum = UploadFunEnum.location)
{
switch (funEnum)
{
case UploadFunEnum.blob:
funcBlobStream = _func;
break;
case UploadFunEnum.location:
funcStream = _func;
break;
case UploadFunEnum.LocationBlack:
_funcLocationBlackStream = _func;
break;
case UploadFunEnum.BlobBlack:
_funcBlobBlackStream = _func;
break;
case UploadFunEnum.defalut:
break;
}
}
/// <summary>
/// 通过委托上传文件
/// </summary>
/// <param name="stream">文件流</param>
/// <param name="fileName">文件名</param>
/// <param name="fileType">文件类型</param>
/// <returns></returns>
public static string Execute(Stream stream, string fileName, UploadFileType fileType, UploadFunEnum funcEnum = UploadFunEnum.defalut)
{
var func = funcEnum switch
{
UploadFunEnum.blob => funcBlobStream,
UploadFunEnum.location => funcStream,
_ => defaultBlackEnum == UploadFunEnum.location ? funcStream : funcBlobStream
};
if (func == null) throw new Exception("请先初始化上传委托");
return func.Invoke(stream, fileName, fileType);
}
/// <summary>
/// 分片上传处理
/// </summary>
/// <param name="stream"></param>
/// <param name="fileDir"></param>
/// <param name="fileType"></param>
/// <param name="funEnum"></param>
/// <returns></returns>
public static string ExecuteBlack(Stream stream, string fileDir, UploadFileType fileType, UploadFunEnum funEnum = UploadFunEnum.defalut)
{
funEnum = funEnum == UploadFunEnum.defalut ? defaultBlackEnum : funEnum;
if (funEnum != UploadFunEnum.BlobBlack && funEnum != UploadFunEnum.LocationBlack)
{
return Execute(stream, fileDir, fileType, funEnum);
}
if ((funEnum == UploadFunEnum.LocationBlack && (_funcLocationBlackStream == null || _mergeLocationBlack == null)) ||
(funEnum == UploadFunEnum.BlobBlack && (_funcBlobBlackStream == null || _mergeBlobBlack == null)))
{
throw new Exception("请先初始化分片上传委托");
}
var result = funEnum == UploadFunEnum.LocationBlack
? _funcLocationBlackStream.Invoke(stream, fileDir, fileType)
: _funcBlobBlackStream.Invoke(stream, fileDir, fileType);
return result;
}
#endregion
/// <summary>
/// 合并分片内容的初始化委托
/// </summary>
/// <param name="_func"></param>
/// <param name="funcEnum"></param>
public static void InitMerge(Func<List<string>, string, string, UploadFileType, string> _func, UploadFunEnum funcEnum = UploadFunEnum.LocationBlack)
{
switch (funcEnum)
{
case UploadFunEnum.LocationBlack:
_mergeLocationBlack = _func;
break;
case UploadFunEnum.BlobBlack:
_mergeBlobBlack = _func;
break;
}
}
/// <summary>
/// 合并分片，本地存储开集群的模式下不建议开启分片上传
/// </summary>
/// <param name="fileDir"></param>
/// <param name="fileName"></param>
/// <param name="blockList"></param>
/// <param name="fileType"></param>
/// <param name="funEnum"></param>
/// <returns></returns>
public static string MergeBlack(string fileDir, string fileName, List<string> blockList, UploadFileType fileType, UploadFunEnum funEnum = UploadFunEnum.defalut)
{
funEnum = funEnum == UploadFunEnum.defalut ? defaultBlackEnum : funEnum;
var result = funEnum == UploadFunEnum.LocationBlack
? _mergeLocationBlack.Invoke(blockList, fileName, fileDir, fileType)
: _mergeBlobBlack.Invoke(blockList, fileName, fileDir, fileType);
return result;
}
}
/// <summary>
/// 文件枚举
/// </summary>
public enum UploadFileType
{
[Description("Face")]
Face = 1,
[Description("Voice")]
Voice = 2,
[Description("Image")]
Image = 3,
[Description("Video")]
Video = 4,
[Description("File")]
File = 5,
[Description("QrCode")]
QrCode = 6
}
public enum UploadFunEnum
{
defalut = 0,
location = 1,//本地
blob = 2,//Azure云上传
/// <summary>
/// 本地分片上传
/// </summary>
LocationBlack = 3,
/// <summary>
/// 云分片上传
/// </summary>
BlobBlack = 4
}
}
2、需要一个类CovertDataTableHelper，里面有一个方法会用得到，当然大家也可以把这个方法写道其他里面，但是下面调用的时候记得改就行了。
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
namespace ARC.Common.Help
{
public static class CovertDataTableHelper
{
public static DataTable ToDataTableTow(IList list)
{
DataTable result = new DataTable();
if (list.Count > 0)
{
PropertyInfo[] propertys = list[0].GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
result.Columns.Add(pi.Name, pi.PropertyType);
}
for (int i = 0; i < list.Count; i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}
/// <summary>
/// 泛型转换list
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
public static DataTable ToDataTable<T>(IList<T> list)
{
return ToDataTable<T>(list, null);
}
/// <summary>
/// 将泛型集合类转换成DataTable
/// </summary>
/// <typeparam name="T">集合项类型</typeparam>
/// <param name="list">集合</param>
/// <param name="propertyName">需要返回的列的列名</param>
/// <returns>数据集(表)</returns>
public static DataTable ToDataTable<T>(IList<T> list, params string[] propertyName)
{
List<string> propertyNameList = new List<string>();
if (propertyName != null)
propertyNameList.AddRange(propertyName);
DataTable result = new DataTable();
if (list.Count > 0)
{
PropertyInfo[] propertys = list[0].GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
if (propertyNameList.Count == 0)
{
result.Columns.Add(pi.Name, pi.PropertyType);
}
else
{
if (propertyNameList.Contains(pi.Name))
result.Columns.Add(pi.Name, pi.PropertyType == typeof(DateTime) ? typeof(string) : pi.PropertyType);
}
}
for (int i = 0; i < list.Count; i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
if (propertyNameList.Count == 0)
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
else
{
if (propertyNameList.Contains(pi.Name))
{
object obj = pi.GetValue(list[i], null);
tempList.Add(pi.PropertyType == typeof(DateTime) ? (obj == null ? "" : Convert.ToDateTime(obj).ToString("yyyy-MM-dd HH:mm:ss")) : obj);
}
}
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}
}
}
3、我们除了上方的那个类之外我们还需要另外一个类，这个类里面是EXCEL导入成DataTable的类，和对EXCEL中表格进行判断等一系列操作的类。名字大家也可以自己取，如果缺少引用一定要添加引用！！（代码我都标有注释大家自己看），同样是直接复制不要嫌长，直接复制粘贴进去就好！！
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
namespace ARC.Common.Help
{
public class ExcelHelper2 : IDisposable
{
private string fileName = null; //文件名
private IWorkbook workbook = null;
private FileStream fs = null;
private bool disposed;
public ExcelHelper2(string fileName)
{
this.fileName = fileName;
disposed = false;
}
#region Data To Excel
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
return -1;
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
workbook.Write(fs); //写入到excel
return count;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return -1;
}
}
/// <summary>
/// 将List集合转为Datatable并生成Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="List"></param>
/// <param name="Headline"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public int ListToExcel<T>(List<T> List, string[] Headline, string sheetName, string[] cloumns = null, string excelTemplateName = "")
{
//string[] Headline = { "姓名", "审核状态","签到状态", "签到方式", "邮箱", "手机","城市", "公司", "职位", "性别", "行业", "票号", "报名时间",
// "身份证号" ,"是否14日晚在酒店用餐","是否需要无烟房","是否参加16日拓展活动","身高尺码","ShareRoom","说明","去程日期","去程交通","返程日期","返程交通","更新时间"};
//int i = 0;
int j = 0;
int count = 1;
ISheet sheet = null;
//加载模板文件路径
FileStream file = new FileStream(excelTemplateName, FileMode.Open, FileAccess.Read);//读入excel模板
//HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。
//file
HSSFWorkbook workbook = new HSSFWorkbook();
//FileStream fs = File.Open(excelTemplateName, FileMode.Open,
//FileAccess.Read, FileShare.ReadWrite);
//if (fileName.IndexOf(".xlsx") > 0) // 2007版本
// if (string.IsNullOrEmpty(excelTemplateName))
// {
// workbook = new XSSFWorkbook();
// }
// else
// {
// workbook = new XSSFWorkbook(fs);
// fs.Close();
// }
//else if (fileName.IndexOf(".xls") > 0) // 2003版本
// workbook = new HSSFWorkbook();
try
{
if (workbook != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.CreateSheet(sheetName);
}
}
else
{
return -1;
}
DataTable dataTable = CovertDataTableHelper.ToDataTable(List, cloumns);
for (int i = 1; i <= List.Count; i++)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < Headline.Length; ++j)
{
row.CreateCell(j).SetCellValue(dataTable.Rows[i - 1][j].ToString());
}
++count;
}
FileStream fsnew = new FileStream(fileName, FileMode.Create);
workbook.Write(fsnew); //写入到excel
return count;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return -1;
}
}
#endregion
#region Excle To Data
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, int cellnum)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
//int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
int cellCount = cellnum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
//ItemHelper.WriteTxTLogs("cellValue:" + cellValue);
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//ItemHelper.WriteTxTLogs("data.Columns.Count:" + data.Columns.Count);
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
bool result = false;
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
//读取每列
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j); //一个单元格
dataRow[j] = GetCellValue(cell); //获取单元格的值
//全为空则不取
if (dataRow[j].ToString() != "")
{
result = true;
}
}
if (result == true)
{
data.Rows.Add(dataRow); //把每行追加到DataTable
}
}
}
return data;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
//对单元格进行判断取值
private static string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.Blank: //空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)
return string.Empty;
case CellType.Boolean: //bool类型
return cell.BooleanCellValue.ToString();
case CellType.Error:
return cell.ErrorCellValue.ToString();
case CellType.Numeric: //数字类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
return cell.DateCellValue.ToString();
}
else //其它数字
{
return cell.NumericCellValue.ToString();
}
case CellType.Unknown: //无法识别类型
default: //默认类型
return cell.ToString();//
case CellType.String: //string 类型
return cell.StringCellValue;
case CellType.Formula: //带公式类型
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(row.FirstCellNum) != null && row.GetCell(row.FirstCellNum).ToString() != "")
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
dataRow[j] = row.GetCell(j).ToString();
}
}
else
{
dataRow[j] = "";
//return data;
}
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <param name="isHaveTableTips">是否有提示行</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable2(string sheetName, bool isFirstRowColumn, bool isHaveTableTips)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
//获取第一行,isHaveTableTips 若有提示行 +1
IRow firstRow = isHaveTableTips ? sheet.GetRow(1) : sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
//创建列
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
cell.SetCellType(CellType.String);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
if (data.Columns.Contains(cellValue))
{
cellValue = cellValue + i;
}
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = isHaveTableTips ? sheet.FirstRowNum + 2 : sheet.FirstRowNum + 1;
}
else
{
startRow = isHaveTableTips ? sheet.FirstRowNum + 1 : sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
//读取每行,从第二行起
for (int i = startRow; i <= rowCount; ++i)
{
bool result = false;
//获取当前行
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
//读取每列
//for (int j = row.FirstCellNum; j < cellCount; ++j)
//{
// if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
// if (row.GetCell(j).CellType == CellType.Numeric)
// {
// if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
// {
// dataRow[j] = row.GetCell(j).DateCellValue;
// }
// else
// {
// dataRow[j] = row.GetCell(j).NumericCellValue;
// }
// }
// else
// {
// dataRow[j] = row.GetCell(j).ToString();
// }
//}
//data.Rows.Add(dataRow);
//读取每列
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j); //一个单元格
dataRow[j] = GetCellValue(cell); //获取单元格的值
//全为空则不取
if (!string.IsNullOrEmpty(dataRow[j].ToString()))
{
result = true;
}
}
if (result == true)
{
data.Rows.Add(dataRow); //把每行追加到DataTable
}
}
}
return data;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
#endregion
#region Excel Helper
/// <summary>
/// 获取Cell样式
/// </summary>
/// <param name="wk"></param>
/// <param name="fontfamily"></param>
/// <param name="fontcolor"></param>
/// <param name="fontsize"></param>
/// <param name="bgColor"></param>
/// <returns></returns>
public static ICellStyle GetCellStyle(IWorkbook workbook, string fontfamily, short fontcolor, int fontsize, short bgColor)
{
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.SetFont(GetFontStyle(workbook, fontfamily, fontcolor, fontsize));
cellstyle.FillPattern = FillPattern.SolidForeground;
cellstyle.FillForegroundColor = bgColor;
cellstyle.VerticalAlignment = VerticalAlignment.Top;
return cellstyle;
}
/// <summary>
/// Excel字体样式
/// </summary>
/// <param name="hssfworkbook"></param>
/// <param name="fontfamily"></param>
/// <param name="fontcolor"></param>
/// <param name="fontsize"></param>
/// <returns></returns>
public static IFont GetFontStyle(IWorkbook hssfworkbook, string fontfamily, short fontcolor, int fontsize)
{
IFont font1 = hssfworkbook.CreateFont();
if (string.IsNullOrEmpty(fontfamily))
{
font1.FontName = fontfamily;
}
if (fontcolor != null)
{
font1.Color = fontcolor;
}
font1.IsItalic = false;
font1.Boldweight = (short)FontBoldWeight.Bold;
font1.FontHeightInPoints = (short)fontsize;
return font1;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
if (fs != null)
fs.Close();
}
fs = null;
disposed = true;
}
}
#endregion
}
}
4、接下来我们就可以再我们的控制器中写接口方法了，上面的两个类缺一不可不要嫌长，直接复制就好，基本不用做修改只需添加引用即可！！！
首先我们需要封装一个获取文件后缀名的方法，非常简单：
private string GetFileExtension(string fileName)
{
//从指定字符开始看是否大于0
if (fileName.IndexOf(".") > 0)
{
//从点开始取但是不包括这个点 获取.最后一次出现的位置
return fileName.Substring(fileName.LastIndexOf("."));
}
return "";
}其次我们开始写主方法，返回类型是上面的返回类型库里面的哦。上面那个方法必须写哦！否则在主方法里面用不了！！：
[HttpPost]
public string ImportXls()
{
var t = UploadFileType.File;
var files = HttpContext.Request.Form.Files;
if (files.Count > 0)
{
var fileFile = files[0];
if (fileFile != null)
{
string ext = GetFileExtension(fileFile.FileName);
if (!(ext.ToLower() == ".xls" || ext.ToLower() == ".xlsx"))
{
//return ApiRespHelp.getError(-100, "文件格式不允许");
return "文件格式不允许";
}
}
if (fileFile.FileName != "")
{
string nameStr = Guid.NewGuid().ToString();
string newFileName = $"{nameStr}{GetFileExtension(fileFile.FileName)}";
string fileDir = $"{AppDomain.CurrentDomain.BaseDirectory}/{AppConfig.UploadConfig.Directory}/{t.ToString()}";
if (!Directory.Exists(fileDir))
{
Directory.CreateDirectory(fileDir);
}
string resourcepath = Path.Combine(fileDir, newFileName);
var fileBytes = StreamToBytes(fileFile.OpenReadStream());
System.IO.File.WriteAllBytes(resourcepath, fileBytes);
ExcelHelper2 help = new ExcelHelper2(resourcepath);
var dt = help.ExcelToDataTable("sheet1", true);
try
{
if (dt != null && dt.Rows.Count > 0)
{
//邮箱
var emails = (from DataRow item in dt.Rows
group item by item["邮箱"] into itemKey
select itemKey.Key.ToString()).ToList();
emails = (from email in emails where !string.IsNullOrEmpty(email) select email).ToList();
//手机号
var mobiles = (from DataRow item in dt.Rows
group item by item["电话"] into itemKey
select itemKey.Key.ToString()).ToList();
mobiles = (from mobile in mobiles where !string.IsNullOrEmpty(mobile) select mobile).ToList();
if (emails.Count < 1)
{
return "文件内不存在邮箱，无法导入";
}
if (mobiles.Count < 1)
{
return "文件内不存在手机号，无法导入";
}
var regUser = new List<User_tb>();
using (var dbContext = new MADbContext())
{
//邮箱
var userEmails = dbContext.User_tbs.Where(m => emails.Contains(m.Email) && m.userid != -1)
.GroupBy(m => m.Email).Select(m => m.Key.ToLower()).ToList();
//手机号
var userMobile = dbContext.User_tbs.Where(m => mobiles.Contains(m.Telephone) && m.userid!= -1)
.GroupBy(m => m.Telephone).Select(m => m.Key).ToList();
//邮箱重复
int repeatCount = 0;
int repeatNullCount = 0;
string repeatInfo = string.Empty;
int i = 1;
foreach (DataRow item in dt.Rows)
{
//记录循环次数
i++;
//判断里面的数据是不是空的
if (string.IsNullOrEmpty(item["用户ID"].ToString()))
{
repeatInfo += $"第{i}条必填数据不能为空<br>";
repeatNullCount++;
continue;
}
if (string.IsNullOrEmpty(item["姓名"].ToString()))
{
repeatInfo += $"第{i}条必填数据不能为空<br>";
repeatNullCount++;
continue;
}
if (string.IsNullOrEmpty(item["电话"].ToString()))
{
repeatInfo += $"第{i}条必填数据不能为空<br>";
repeatNullCount++;
continue;
}
if (string.IsNullOrEmpty(item["密码"].ToString()))
{
repeatInfo += $"第{i}条必填数据不能为空<br>";
repeatNullCount++;
continue;
}
if (string.IsNullOrEmpty(item["公司名称"].ToString()))
{
repeatInfo += $"第{i}条必填数据不能为空<br>";
repeatNullCount++;
continue;
}
if (string.IsNullOrEmpty(item["公司规模"].ToString()))
{
repeatInfo += $"第{i}条必填数据不能为空<br>";
repeatNullCount++;
continue;
}
if (string.IsNullOrEmpty(item["部门"].ToString()))
{
repeatInfo += $"第{i}条必填数据不能为空<br>";
repeatNullCount++;
continue;
}
if (string.IsNullOrEmpty(item["职位"].ToString()))
{
repeatInfo += $"第{i}条必填数据不能为空<br>";
repeatNullCount++;
continue;
}
if (string.IsNullOrEmpty(item["图片"].ToString()))
{
repeatInfo += $"第{i}条必填数据不能为空<br>";
repeatNullCount++;
continue;
}
if (string.IsNullOrEmpty(item["邮箱"].ToString()))
{
repeatInfo += $"第{i}条必填数据不能为空<br>";
repeatNullCount++;
continue;
}
if (string.IsNullOrEmpty(item["注册时间"].ToString()))
{
repeatInfo += $"第{i}条必填数据不能为空<br>";
repeatNullCount++;
continue;
}
if (string.IsNullOrEmpty(item["状态"].ToString()))
{
repeatInfo += $"第{i}条必填数据不能为空<br>";
repeatNullCount++;
continue;
}
//获取exceel里面的值保存到新的对象里
var user = new User_tb()
{
Id = Guid.NewGuid(),
Name = item["姓名"].ToString(),
Email = item["邮箱"].ToString(),
Telephone = item["电话"].ToString(),
CompanyName = item["公司名称"].ToString(),
Department = item["部门"].ToString(),
Position = item["职位"].ToString(),
Password = item["密码"].ToString(),
ImgUrl = item["图片"].ToString(),
Scale = item["公司规模"].ToString() == "20人以下" ? "10148" : (item["公司规模"].ToString() == "50-100人" ? "10149" : (item["公司规模"].ToString() == "100-500人" ? "10154" : "10155"))
};
//邮箱重复
if (userEmails.Contains(user.Email.ToLower()))
{
repeatInfo += $"第{i}条数据重复 重复邮箱:{user.Email}<br>";
repeatCount++;
continue;
}
//手机号重复
if (userMobile.Contains(user.Telephone))
{
repeatInfo += $"第{i}条数据重复 重复手机号:{user.Telephone}<br>";
repeatCount++;
continue;
}
if (user.Telephone.Length != 11)
{
repeatInfo += $"第{i}条数据 手机号格式不对:{user.Telephone}<br>"; //验证手机号格式
continue;
}
regUser.Add(user);
userEmails.Add(user.Email);
userMobile.Add(user.Telephone);
}
// .Any() 如果有数据返回true 否则返回false
/*IsSignUp = dbContext.Act_SignUp.Any(m => m.UserId == regUserAny.Id && m.ActivityId == ActivityId && m.State > -1);*/
dbContext.User_tbs.AddRange(regUser);
dbContext.SaveChanges();
if (string.IsNullOrEmpty(repeatInfo))
{
repeatInfo = "无";
}
string result = $"数据导入完成！<br/>插入条目：{regUser.Count}，重复条目：{repeatCount}， <br/>数据必填项为空条目：{repeatNullCount}。<br/>导入失败数据：<br/>{repeatInfo} <br/>";
System.IO.File.Delete(resourcepath);
return result;
}
}
return "未发现需要导入的数据，请使用指定模板导入。";
}
catch (Exception ex)
{
LogHelper.Error($"导入用户数据出错：{ex.Message}---{ex.Source}--{ex.StackTrace}");
return "请使用模版导入";
}
}
}
return "未发现文件";
}
863
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
