封装Excel读取，导出（实体类集合List、DataTable、DataGridView、实体类集合和DataTable相互转换）
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实体集合转换 /// <summary> /// /// where T : class, new() /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dataTable"></param> /// <returns></returns> public static List<T> GetListByDataTable<T>(this DataTable dataTable) { List<T> list = new List<T>(); foreach (DataRow row in dataTable.Rows) { T obj = Activator.CreateInstance<T>(); foreach (DataColumn col in dataTable.Columns) { PropertyInfo property = typeof(T).GetProperty(col.ColumnName); if (property != null && row[col] != DBNull.Value) { if (property.PropertyType.FullName == "System.Int32") { property.SetValue(obj, int.Parse(row[col].ToString())); } else if (property.PropertyType.FullName == "System.Decimal") { property.SetValue(obj, decimal.Parse(row[col].ToString())); } else { try { property.SetValue(obj, row[col]); } catch { property.SetValue(obj, null); } } } } list.Add(obj); } return list; } /// <summary> /// 实体类集合转table /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetDataTableByList<T>(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)); dt.Columns.Add(new DataColumn(p.Name)); } //赋值 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
