1、引入使用
#region 读取excel
/// <summary>
/// 根据Excel和Sheet返回DataTable
/// </summary>
/// <param name="filePath">Excel文件地址</param>
/// <param name="sheetIndex">Sheet索引</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableByExcelPath(string filePath, int sheetIndex = 0)
{
DataTable dtl = new DataTable();
IWorkbook fileWorkbook;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (filePath.Last() == 's')
{
try
{
fileWorkbook = new HSSFWorkbook(fs);
}
catch (Exception ex)
{
//logger.Error("打开Excel文件失败！", ex);
throw ex;
}
}
else
{
try
{
fileWorkbook = new XSSFWorkbook(fs);
}
catch
{
fileWorkbook = new HSSFWorkbook(fs);
}
}
}
ISheet sheet = fileWorkbook.GetSheetAt(0);
// 表名
dtl.TableName = sheet.SheetName;
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int j = 0; j < header.LastCellNum; j++)
{
object obj = GetValueTypeForXLS(header.GetCell(j) as ICell);
if (obj == null || obj.ToString() == string.Empty)
{
dtl.Columns.Add(new DataColumn("Columns" + j.ToString()));
}
else
dtl.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(j);
}
//数据
IEnumerator rows = sheet.GetEnumerator();
int RowIndex = sheet.FirstRowNum + 1;
while (rows.MoveNext())
{
if (sheet.GetRow(RowIndex) == null)
{
break;
}
DataRow dr = dtl.NewRow();
bool hasValue = false;
foreach (int K in columns)
{
dr[K] = GetValueTypeForXLS(sheet.GetRow(RowIndex).GetCell(K) as ICell);
if (dr[K] != null && dr[K].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dtl.Rows.Add(dr);
}
RowIndex++;
}
return dtl;
}
/// <summary>
/// 根据Excel返回DataSet
/// </summary>
/// <param name="filePath">Excel文件地址</param>
/// <param name="sheetIndex">Sheet索引，可选，默认返回所有Sheet</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSetByExcelPath(string filePath, int? sheetIndex = null)
{
DataSet ds = new DataSet();
IWorkbook fileWorkbook;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (filePath.Last() == 's')
{
try
{
fileWorkbook = new HSSFWorkbook(fs);
}
catch (Exception ex)
{
//logger.Error("打开Excel文件失败！", ex);
throw ex;
}
}
else
{
try
{
fileWorkbook = new XSSFWorkbook(fs);
}
catch
{
fileWorkbook = new HSSFWorkbook(fs);
}
}
}
for (int i = 0; i < fileWorkbook.NumberOfSheets; i++)
{
if (sheetIndex != null && sheetIndex != i)
continue;
DataTable dt = new DataTable();
ISheet sheet = fileWorkbook.GetSheetAt(i);
// 表名
dt.TableName = sheet.SheetName;
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int j = 0; j < header.LastCellNum; j++)
{
object obj = GetValueTypeForXLS(header.GetCell(j) as ICell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + j.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(j);
}
//数据
IEnumerator rows = sheet.GetEnumerator();
int RowIndex = sheet.FirstRowNum + 1;
while (rows.MoveNext())
{
if (sheet.GetRow(RowIndex) == null)
{
break;
}
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int K in columns)
{
dr[K] = GetValueTypeForXLS(sheet.GetRow(RowIndex).GetCell(K) as ICell);
if (dr[K] != null && dr[K].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
RowIndex++;
}
ds.Tables.Add(dt);
}
return ds;
}
#region 私有函数
/// <summary>
/// 根据单元格将内容返回为对应类型的数据
/// </summary>
/// <param name="cell">单元格</param>
/// <returns>数据</returns>
private static object GetValueTypeForXLS(ICell 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:
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue.ToString("yyyy/MM/dd hh:mm:ss.fff");
}
else
{
return cell.NumericCellValue;
}
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
switch (cell.CachedFormulaResultType)
{
case CellType.Boolean:
return cell.BooleanCellValue;
case CellType.Error:
return ErrorEval.GetText(cell.ErrorCellValue);
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue.ToString("yyyy/MM/dd");
}
else
{
return cell.NumericCellValue;
}
case CellType.String:
string str = cell.StringCellValue;
if (!string.IsNullOrEmpty(str))
{
return str.ToString().Trim();
}
else
{
return string.Empty;
}
case CellType.Unknown:
case CellType.Blank:
default:
return string.Empty;
}
default:
return "=" + cell.CellFormula;
}
}
#endregion 私有函数
#endregion
#region 导出Excel
/// <summary>
/// 保存Excel文件
/// </summary>
/// <param name="pathExcelName">保存路径+Excel文件名</param>
/// <param name="sheetName">Sheet工作表名</param>
/// <param name="data">实体类对象</param>
public static void ExportExcelByList<T>(string pathExcelName, string sheetName, List<T> data)
{
//创建一个Excel文档
IWorkbook workBook = new HSSFWorkbook();
//创建一个工作表Sheet
ISheet sheet = workBook.CreateSheet(sheetName);
sheet.DefaultColumnWidth = 50;
int rowNum = 0;
//LastRowNum记录当前可用写入的行索引
var row = sheet.CreateRow(sheet.LastRowNum);
//获取这个实体对象的所有属性
PropertyInfo[] preInfo = typeof(T).GetProperties();
foreach (var item in preInfo)
{
//获取当前属性的自定义特性列表
object[] objPres = item.GetCustomAttributes(typeof(DescriptionAttribute), true);
if (objPres.Length > 0)
{
for (int i = 0; i < objPres.Length; i++)
{
//创建行，将当前自定义特性写入
row.CreateCell(rowNum).SetCellValue(((DescriptionAttribute)objPres[i]).Description);
rowNum++;
}
}
}
int j = sheet.LastRowNum + 1, columnNum = 0;
foreach (var item in data)
{
columnNum = 0;
row = sheet.CreateRow(j++);
//获取当前对象的属性列表
var itemProps = item.GetType().GetProperties();
foreach (var itemPropSub in itemProps)
{
//获取当前对象特性中的自定义特性[Description("自定义特性")]
var objs = itemPropSub.GetCustomAttributes(typeof(DescriptionAttribute), true);
if (objs.Length > 0)
{
//将当前对象的特性值，插入当前行的第n列单元格
row.CreateCell(columnNum).SetCellValue(itemPropSub.GetValue(item, null) == null ? "" : itemPropSub.GetValue(item, null).ToString());
columnNum++;
}
}
}
//文件流写入
using (MemoryStream ms = new MemoryStream())
{
workBook.Write(ms);
using (FileStream fs = new FileStream(pathExcelName, FileMode.Create, FileAccess.Write))
{
ms.WriteTo(fs);
}
ms.Flush();
ms.Position = 0;
workBook.Close();
}
}
/// <summary>
/// DataTabel导出excel
/// </summary>
/// <param name="tabelDatas"></param>
/// <param name="filePath"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static bool ExportExcelByDataTable(DataTable tabelDatas, string filePath, out string errMsg)
{
errMsg = string.Empty;
if (string.IsNullOrEmpty(filePath))
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
//设置文件标题
saveFileDialog.Title = "导出Excel文件";
//设置文件类型
saveFileDialog.Filter = "Microsoft Office Excel 工作簿(*.xls)|*.xls";
//设置默认文件类型显示顺序
saveFileDialog.FilterIndex = 1;
//是否自动在文件名中添加扩展名
saveFileDialog.AddExtension = true;
//是否记忆上次打开的目录
saveFileDialog.RestoreDirectory = true;
//设置默认文件名
//saveFileDialog.FileName = "";
//按下确定选择的按钮
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
//获得文件路径
filePath = saveFileDialog.FileName.ToString();
}
else
{
errMsg = "没有获取到保存路径";
return false;
}
}
string myDateFormat = "yyyy-MM-dd HH:mm:ss";
XSSFWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow rowHeader = sheet.CreateRow(0);
for (int i = 0; i < tabelDatas.Columns.Count; i++)
{
DataColumn column = tabelDatas.Columns[i];
rowHeader.CreateCell(i).SetCellValue(column.Caption);
}
short decimalformat = HSSFDataFormat.GetBuiltinFormat("0.00");
short dateformat = wb.CreateDataFormat().GetFormat(myDateFormat);
ICellStyle styleDecimal = wb.CreateCellStyle();
styleDecimal.DataFormat = decimalformat;
ICellStyle styleDate = wb.CreateCellStyle();
styleDate.DataFormat = dateformat;
ICellStyle styleNormal = wb.CreateCellStyle();
for (int i = 0; i < tabelDatas.Rows.Count; i++)
{
DataRow dr = tabelDatas.Rows[i];
IRow ir = sheet.CreateRow(i + 1);
for (int j = 0; j < dr.ItemArray.Length; j++)
{
ICell icell = ir.CreateCell(j);
object cellValue = dr[j];
Type type = cellValue.GetType();
if (type == typeof(decimal) || type == typeof(double) || type == typeof(int) || type == typeof(float))
{
icell.SetCellValue(Convert.ToDouble(cellValue));
icell.CellStyle = styleDecimal;
}
else if (type == typeof(DateTime))
{
icell.SetCellValue(Convert.ToDateTime(cellValue).ToString(myDateFormat));
icell.CellStyle = styleNormal;
}
else if (type == typeof(bool))
{
icell.SetCellValue(Convert.ToBoolean(cellValue) ? "是" : "否");
icell.CellStyle = styleNormal;
}
else
{
icell.SetCellValue(cellValue.ToString());
icell.CellStyle = styleNormal;
}
}
}
using (FileStream fs = File.OpenWrite(filePath))
{
wb.Write(fs);
}
return true;
}
/// <summary>
/// DataTable转存为Excel文件【使用NPOI库】
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
public static bool ExportExcelByDataTable1(DataTable dt, string filePath, out string errMsg)
{
errMsg = string.Empty;
try
{
// 创建一个新的Excel工作簿
IWorkbook workbook = new XSSFWorkbook();
// 创建一个新的工作表并命名为“Sheet1”
ISheet worksheet = workbook.CreateSheet("Sheet1");
// 将DataTable的列名写入工作表中
IRow headerRow = worksheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
// 将DataTable的数据写入工作表中
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow dataRow = worksheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = dataRow.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
// 保存Excel文件
using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fileStream);
}
}
catch (Exception ex)
{
errMsg = ex.Message;
return false;
}
return true;
}
public static void ExportExcelByDataGridView(DataGridView dgv, string filePath)
{
// 创建一个新的Excel工作簿
IWorkbook workbook = new XSSFWorkbook();
// 创建一个新的工作表并命名为“Sheet1”
ISheet worksheet = workbook.CreateSheet("Sheet1");
// 将DataTable的列名写入工作表中
IRow headerRow = worksheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(dgv.Columns[i].HeaderText);
}
// 将DataTable的数据写入工作表中
for (int i = 0; i < dgv.Rows.Count; i++)
{
IRow dataRow = worksheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
ICell cell = dataRow.CreateCell(j);
cell.SetCellValue(dgv.Rows[i].Cells[j].ToString());
}
}
// 保存Excel文件
using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fileStream);
}
}
#endregion
#region 读取Csv文件
/// <summary>
/// Csv文件读取到DataTable
/// </summary>
/// <param name="filePath">csv文件路径</param>
/// <param name="n">表示第n行是字段title,第n+1行是记录开始(首行是标题传0)</param>
/// <returns>可选参数表示最后K行不算记录默认0</returns>
public static DataTable GetDataTableByCsvPath(string filePath, int n)
{
DataTable dt = new DataTable();
String csvSplitBy = "(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)";
StreamReader reader = new StreamReader(filePath, System.Text.Encoding.Default, false);
int i = 0, m = 0;
reader.Peek();
while (reader.Peek() > 0)
{
m = m + 1;
string str = reader.ReadLine();
if (m >= n + 1)
{
if (m == n + 1) //如果是字段行，则自动加入字段。
{
MatchCollection mcs = Regex.Matches(str, csvSplitBy);
foreach (System.Text.RegularExpressions.Match mc in mcs)
{
if (dt.Columns.Contains(mc.Value))
{
dt.Columns.Add(mc.Value + "2"); //增加列标题
}
else
{
dt.Columns.Add(mc.Value);
}
}
}
else
{
MatchCollection mcs = Regex.Matches(str, "(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)");
i = 0;
System.Data.DataRow dr = dt.NewRow();
foreach (System.Text.RegularExpressions.Match mc in mcs)
{
dr[i] = mc.Value;
i++;
}
dt.Rows.Add(dr); //DataTable 增加一行
}
}
}
return dt;
}
#endregion 读取Csv文件
#region 导出为CSV文件
/// <summary>
/// 将DataTable导出为Csv文件
/// </summary>
/// <param name="dt">数据</param>
/// <param name="savaPath">保存的路径</param>
/// <param name="strName">文件名称</param>
/// <returns></returns>
public static string ExportCsvByDataTabel(System.Data.DataTable dt, string savaPath, string strName)
{
//保存到本项目文件夹下
//string strPath = Path.GetTempPath() + strName + ".csv";
//保存到指定目录下
string strPath = savaPath + "\\" + strName + ".csv";
if (File.Exists(strPath))
{
File.Delete(strPath);
}
//先打印标头
StringBuilder strColu = new StringBuilder();
StringBuilder strValue = new StringBuilder();
int i = 0;
try
{
StreamWriter sw = new StreamWriter(new FileStream(strPath, FileMode.CreateNew), Encoding.GetEncoding("GB2312"));
for (i = 0; i <= dt.Columns.Count - 1; i++)
{
strColu.Append(dt.Columns[i].ColumnName);
strColu.Append(",");
}
//移出掉最后一个,字符
strColu.Remove(strColu.Length - 1, 1);
sw.WriteLine(strColu);
foreach (DataRow dr in dt.Rows)
{
//移出
strValue.Remove(0, strValue.Length);
for (i = 0; i <= dt.Columns.Count - 1; i++)
{
strValue.Append(dr[i].ToString());
strValue.Append(",");
}
//移出掉最后一个,字符
strValue.Remove(strValue.Length - 1, 1);
sw.WriteLine(strValue);
}
sw.Close();
//打开文件
//System.Diagnostics.Process.Start(strPath);
return strPath;
}
catch (Exception ex)
{
return "";
}
}
#endregion 导出为CSV文件
#region 获取实体集合（将excel，DataTable）
/// <summary>
/// DataTable转成List（根据属性Name和excel列名对应）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <returns></returns>
public static List<T> GetListByNameDataTable<T>(this DataTable dt)
{
var list = new List<T>();
var plist = new List<PropertyInfo>(typeof(T).GetProperties());
foreach (DataRow item in dt.Rows)
{
T s = Activator.CreateInstance<T>();
for (int i = 0; i < dt.Columns.Count; i++)
{
PropertyInfo info = plist.Find(p => p.Name.ToUpper() == dt.Columns[i].ColumnName.ToUpper());
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;
}
/// <summary>
/// DataTable转成List（根据属性描述和excel列名对应）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <returns></returns>
public static List<T> GetListByDescDataTable<T>(this DataTable dt)
{
var dataList = new List<T>();
PropertyInfo[] preInfo = typeof(T).GetProperties(BindingFlags.Public);
foreach (DataRow row in dt.Rows)
{
T item = Activator.CreateInstance<T>();
foreach (DataColumn column in dt.Columns)
{
PropertyInfo prop = null;
foreach (PropertyInfo info in preInfo)
{
//获取当前属性的自定义特性列表
object[] objPres = info.GetCustomAttributes(typeof(DescriptionAttribute), true);
if (objPres.Length > 0)
{
if (column.ColumnName == objPres[0].ToString())
{
prop = info;
break;
}
}
}
if (prop != null && row[column] != DBNull.Value)
{
prop.SetValue(item, Convert.ChangeType(row[column], prop.PropertyType), null);
}
}
dataList.Add(item);
}
return dataList;
}
public static List<T> GetListByNameExcelPath<T>(string excelPath)
{
DataTable dtl = GetDataTableByExcelPath(excelPath);
List<T> datas = GetListByNameDataTable<T>(dtl);
return datas;
}
public static List<T> GetListByDescExcelPath<T>(string excelPath)
{
DataTable dtl = GetDataTableByExcelPath(excelPath);
List<T> datas = GetListByDescDataTable<T>(dtl);
return datas;
}
#endregion
#region DataTable和List实体集合转换
public static List<T> GetListByDataTable<T>(this DataTable dataTable) where T : class, new()
{
List<T> list = new List<T>();
foreach (DataRow row in dataTable.Rows)
{
T obj = new T();
foreach (DataColumn col in dataTable.Columns)
{
PropertyInfo property = typeof(T).GetProperty(col.ColumnName);
if (property != null && row[col] != DBNull.Value)
{
property.SetValue(obj, row[col]);
}
}
list.Add(obj);
}
return list;
}
/// <summary>
/// 实体类集合转table
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetDaTaTableByList(List<T> mList)
{
if (mList == null || mList.Count == 0)
{
return null;
}
//添加DataTable列
DataTable dt = new DataTable(typeof(T).Name);
foreach (PropertyInfo p in typeof(T).GetProperties())
{
dt.Columns.Add(new DataColumn(p.Name, p.PropertyType));
}
//赋值
foreach (T model in mList)
{
DataRow dr = dt.NewRow();
foreach (PropertyInfo p in typeof(T).GetProperties())
{
dr[p.Name] = p.GetValue(model, null);
}
dt.Rows.Add(dr);
}
return dt;
}
#endregion
标签：实体类,return,string,cell,集合,new,dt,DataTable From： https://www.cnblogs.com/yuanshuo/p/17989642