1、安装NPOI 2.7.2
2、工具类DataTableTool.cs，将list 转成DataTable
using System.Collections;
using System.Data;
using System.Reflection;
namespace Common.Helper
{
public static class DataTableTool
{
/// <summary>
/// 转化一个DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
public static DataTable ToDataTable<T>(this IEnumerable<T> list)
{
//创建属性的集合
List<PropertyInfo> pList = new List<PropertyInfo>();
//获得反射的入口
Type type = typeof(T);
DataTable dt = new DataTable();
//把所有的public属性加入到集合 并添加DataTable的列
Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); });
foreach (var item in list)
{
//创建一个DataRow实例
DataRow row = dt.NewRow();
//给row 赋值
pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
//加入到DataTable
dt.Rows.Add(row);
}
return dt;
}
/// <summary>
/// DataTable 转换为List 集合
/// </summary>
/// <typeparam name="TResult">类型</typeparam>
/// <param name="dt">DataTable</param>
/// <returns></returns>
public static List<T> ToList<T>(this DataTable dt) where T : class, new()
{
//创建一个属性的列表
List<PropertyInfo> prlist = new List<PropertyInfo>();
//获取TResult的类型实例 反射的入口
Type t = typeof(T);
//获得TResult 的所有的Public 属性 并找出TResult属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表
Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });
//创建返回的集合
List<T> oblist = new List<T>();
foreach (DataRow row in dt.Rows)
{
//创建TResult的实例
T ob = new T();
//找到对应的数据 并赋值
prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
//放入到返回的集合中.
oblist.Add(ob);
}
return oblist;
}
/// <summary>
/// 将集合类转换成DataTable
/// </summary>
/// <param name="list">集合</param>
/// <returns></returns>
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)
{
Type colType = pi.PropertyType;
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
result.Columns.Add(pi.Name, colType);
}
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>
/// 将泛型集合类转换成DataTable
/// </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);
}
}
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(obj);
}
}
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}
}
}
View Code
3、execl生成类ExcelTool.cs
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
namespace Common.Helper
{
public static class ExcelTool
{ /// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <param name="fileName">文件名称</param>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <returns>返回的DataTable</returns>
public static DataTable ExcelStreamToDataTable(bool isFirstRowColumn, string fileName, Stream fs, string sheetName = "")
{
if (string.IsNullOrEmpty(fileName))
{
throw new ArgumentNullException(fileName);
}
var data = new DataTable();
IWorkbook workbook = null;
try
{
if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
{
workbook = new HSSFWorkbook(fs);
}
ISheet sheet = null;
if (workbook != null)
{
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheetName == "")
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
}
}
if (sheet == null) return data;
var firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
int startRow;
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
var cell = firstRow.GetCell(i);
var cellValue = cell.ToString();
if (cellValue == null) continue;
var column = new DataColumn(cellValue);
data.Columns.Add(column);
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
var rowCount = sheet.LastRowNum;
for (var i = startRow; i <= rowCount; ++i)
{
var row = sheet.GetRow(i);
//没有数据的行默认是null
if (row == null) continue;
var dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
//同理，没有数据的单元格都默认是null
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
return data;
}
catch (IOException ioex)
{
throw new IOException(ioex.Message);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (fs != null)
{
fs.Close();
}
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <param name="fileName">文件路径</param>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <returns>返回的DataTable</returns>
public static DataTable ExcelToDataTable(bool isFirstRowColumn, string fileName, string sheetName = "")
{
if (string.IsNullOrEmpty(fileName))
{
throw new ArgumentNullException(fileName);
}
var data = new DataTable();
IWorkbook workbook = null;
FileStream fs = null;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
{
workbook = new HSSFWorkbook(fs);
}
ISheet sheet = null;
if (workbook != null)
{
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheetName == "")
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
}
}
if (sheet == null) return data;
var firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
int startRow;
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
var cell = firstRow.GetCell(i);
var cellValue = cell.StringCellValue;
if (cellValue == null) continue;
var column = new DataColumn(cellValue);
data.Columns.Add(column);
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
var rowCount = sheet.LastRowNum;
for (var i = startRow; i <= rowCount; ++i)
{
var row = sheet.GetRow(i);
//没有数据的行默认是null
if (row == null) continue;
var dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
//同理，没有数据的单元格都默认是null
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
return data;
}
catch (IOException ioex)
{
throw new IOException(ioex.Message);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (fs != null)
{
fs.Close();
}
}
}
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <param name="fileName">文件夹路径</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public static int DataTableToExcel(DataTable[] dataArr, string[] sheetNameArr, bool isColumnWritten, string fileName)
{
if (dataArr == null)
{
throw new ArgumentNullException("data");
}
if (dataArr.Length != sheetNameArr.Length)
{
throw new ArgumentNullException("data长度与sheetName不一致");
}
if (string.IsNullOrEmpty(fileName))
{
throw new ArgumentNullException(fileName);
}
IWorkbook workbook = null;
if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
{
workbook = new XSSFWorkbook();
}
else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
{
workbook = new HSSFWorkbook();
}
FileStream fs = null;
try
{
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
ISheet[] sheetArr = new ISheet[sheetNameArr.Length];
for(int n= 0; n < sheetArr.Length; n++)
{
sheetArr[n] = workbook.CreateSheet(sheetNameArr[n]);
}
var cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
// var cellStyle1 = workbook.CreateCellStyle();
// cellStyle1.DataFormat = HSSFDataFormat.GetBuiltinFormat("yyyy年m月d日");
var cellStyle1 = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
cellStyle1.DataFormat = (format.GetFormat("yyyy-mm-dd"));
// cell.setCellStyle(cellStyle);
int j;
int count = 0 ;
//写入DataTable的列名，写入单元格中
for(int m = 0; m < sheetArr.Length; m++)
{
var sheet = sheetArr[m];
DataTable data = dataArr[m];
if (isColumnWritten)
{
var row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
Type types = data.Columns[j].DataType;
if (types.Name == "DateTime")
{
sheet.SetDefaultColumnStyle(j, cellStyle1);
}
else
{
sheet.SetDefaultColumnStyle(j, cellStyle);
}
}
count = 1;
}
else
{
count = 0;
}
//遍历循环datatable具体数据项
int i;
for (i = 0; i < data.Rows.Count; ++i)
{
var row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
}
//将文件流写入到excel
workbook.Write(fs);
return count;
}
catch (IOException ioex)
{
throw new IOException(ioex.Message);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (fs != null)
{
fs.Close();
}
}
}
}
}
View Code
4、使用
[HttpGet]
public async Task<MessageModel<string>> ExportExcel(string key = "", int orderBy = 0)
{
try
{
string fileName = $"{DateTime.Now.ToString("yyyyMMdd_HHmss")}_{_user.ShopID}" + ".xlsx";
string folder = $"execl\\shopbak";
string urlFolder = $"execl/shopbak";
string folderpath = Path.Combine($"{_env.WebRootPath}\\{folder}\\");
string path = folderpath + fileName;
if (!Directory.Exists(folderpath))
{
Directory.CreateDirectory(folderpath);
}
//删除旧文件
foreach (string file in Directory.GetFiles(folderpath))
{
try
{
FileInfo fileInfo = new FileInfo(file);
DateTime localCreationTime = fileInfo.CreationTime; // 获取文件的本地创建时间
Console.WriteLine("文件创建时间（本地）: " + localCreationTime.ToString());
//创建时间与当前时间比较，相差30秒以上则删除
if (DateTime.Now.Subtract(localCreationTime).TotalSeconds > 60)
{
System.IO.File.Delete(file);
}
}
catch(Exception ex)
{
_logger.LogError("删除文件失败" + ex);
}
}
DataTable[] dtArr = new DataTable[2];
string[] sheetNameArr = { "全部会员", "次卡会员"};
#region 全部会员
var result = await Get(page: 1, intPageSize: 5000, key: key, memberType: 0, orderBy: orderBy); //获取数据
if (!result.success)
{
return Failed("查询数据失败");
}
var list = result.response.data.Select(s => new {
s.Name,
s.Phone,
s.Points,
s.Birthday,
s.Sex,
s.CardNO,
s.Detail,
s.StoreCardName,
s.StoreCardBalance,
s.CreateTime
});
DataTable dt = DataTableTool.ToDataTableTow(list.ToList());
dt.Columns["Name"].ColumnName = "名字";
dt.Columns["Phone"].ColumnName = "手机";
dt.Columns["Points"].ColumnName = "积分";
dt.Columns["Birthday"].ColumnName = "生日";
dt.Columns["Sex"].ColumnName = "性别";
dt.Columns["CardNO"].ColumnName = "会员卡号";
dt.Columns["Detail"].ColumnName = "详情";
dt.Columns["StoreCardName"].ColumnName = "储值卡";
dt.Columns["StoreCardBalance"].ColumnName = "储值卡余额";
dt.Columns["CreateTime"].ColumnName = "添加时间";
dtArr[0] = dt;
#endregion
#region 次卡
var timesResult = await Get(page: 1, intPageSize: 5000, key: key, memberType: 1, orderBy: orderBy);
if (!timesResult.success)
{
return Failed("查询数据失败");
}
var timesList = timesResult.response.data.Select(s => new {
s.Name,
s.Phone,
s.Points,
s.Birthday,
s.Sex,
s.CardNO,
s.Detail,
s.TimesCardName,
s.TimesBusinessName,
s.TimesCardRemainCount,
s.CreateTime
});
DataTable timesDT = DataTableTool.ToDataTableTow(timesList.ToList());
timesDT.Columns["Name"].ColumnName = "名字";
timesDT.Columns["Phone"].ColumnName = "手机";
timesDT.Columns["Points"].ColumnName = "积分";
timesDT.Columns["Birthday"].ColumnName = "生日";
timesDT.Columns["Sex"].ColumnName = "性别";
timesDT.Columns["CardNO"].ColumnName = "会员卡号";
timesDT.Columns["Detail"].ColumnName = "详情";
timesDT.Columns["TimesCardName"].ColumnName = "次卡";
timesDT.Columns["TimesBusinessName"].ColumnName = "次卡项目";
timesDT.Columns["TimesCardRemainCount"].ColumnName = "剩余次数";
timesDT.Columns["CreateTime"].ColumnName = "添加时间";
dtArr[1] = timesDT;
#endregion
int count = ExcelTool.DataTableToExcel(dtArr, sheetNameArr, true, path);
if (System.IO.File.Exists(path))
{
var imgUrl = $"{Request.Scheme}://{Request.Host}/{urlFolder}/{fileName}";
return Success(imgUrl, "成功");
}
return Failed("导出失败");
}
catch(Exception ex)
{
_logger.LogError("导出execl异常" + ex);
return Failed("异常");
}
}
标签：execl,sheet,C#,workbook,var,new,DataTable,Columns From： https://www.cnblogs.com/handsomeziff/p/18594248