webapi 导入excel处理数据
标签：type create public oda duti 表名 rem click show
参考资料
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
{
///
/// Id的列号
///
private const int EXCEL_COL_PLAT_FORM_INDUSTRY = 14;
//获取列名委托方法
public delegate string GetColumnName(string columnName);
#region Excel2007
///
/// excel文件流转化成datatable
///
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 Listint>();
for (var i = 0; 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 )
{
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;
}
///
/// 将Excel文件中的数据读出到DataTable中(xlsx)
///
///
/// 是否有备注
///
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 Listint>();
for (var i = 0; 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 )
{
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;
}
///
/// (包含判断首行备注是否为空)将Excel文件中的数据读出到DataTable中(xlsx)
///
/// 文件路径
/// excel中第一条数据列名
/// 是否存在填写须知（根据表格内容判断）
/// 是否有备注
///
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 Listint>();
for (var i = 0; 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 )
{
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;
}
///
/// 将Excel文件中的数据读出到DataTable中(xlsx)包括空行
///
/// 是否有备注
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 Listint>();
for (var i = 0; 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 )
{
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;
}
///
/// (包含判断首行备注是否为空)将Excel文件中的数据读出到DataTable中(xls)包括空行
/// 文件路径
/// excel中第一条数据列名
/// 是否存在填写须知（根据表格内容判断）
/// 是否有备注
///
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 Listint>();
for (var i = 0; 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 )
{
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;
}
///
/// 将DataTable数据导出到Excel文件中(xlsx),生成excel文件的字节数组
///
///
///
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 )
{
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 )
{
var row1 = sheet.CreateRow(i + 1);
for (var j = 0; 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;
}
///
/// 将DataTable转化为excel
///
/// DataTable(去掉无用字段的DataTable)
/// 获取列名的方法
/// excel
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 )
{
var cell = row.CreateCell(i);
//列名称,数据库中字段
var columnName = dt.Columns[i].ColumnName;
var convertColumnName = getColumnName(columnName);
cell.SetCellValue(convertColumnName);
}
//数据
for (var i = 0; i )
{
var row1 = sheet.CreateRow(i + 1);
for (var j = 0; 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;
}
///
/// 将DataTable转化为excel
///
/// DataTable(去掉无用字段的DataTable)
/// excel
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 )
{
var cell = row.CreateCell(i);
//列名称,数据库中字段
var columnName = dt.Columns[i].ColumnName;
cell.SetCellValue(columnName);
}
//数据
for (var i = 0; i )
{
var row1 = sheet.CreateRow(i + 1);
for (var j = 0; 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;
}
///
/// 将DataTable转化为excel(招生计划完成率报表使用)
///
/// DataTable(去掉无用字段的DataTable)
/// 获取列名的方法
/// excel
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 )
{
var cell = row.CreateCell(i);
cell.SetCellValue(tableName);
}
//二级表头
var sencondRow = sheet.CreateRow(2);
for (var i = 0; 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 )
{
var row1 = sheet.CreateRow(i + 3);
for (var j = 0; 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;
}
///
/// 将DataTable转化为excel，表头自己填充,招生计划完成率导出使用（未封装）
///
/// DataTable(去掉无用字段的DataTable)
/// excel中数据开始的行数（从0开始）
/// excel
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 )
{
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 )
{
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 )
{
var row1 = sheet.CreateRow(i + 2);
for (var j = 0; 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;
}
///
/// 获取单元格类型(xlsx)
///
///
///
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
///
/// 将Excel文件中的数据读出到DataTable中(xls)
///
///
///
///
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 Listint>();
for (var i = 0; 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 )
{
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)
{
评论