EpPlus读取生成Excel帮助类+读取csv帮助类+Aspose.Cells生成Excel帮助类

版权声明：作者：真爱无限 出处：http://blog.csdn.net/pukuimin1226 本文为博主原创文章版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接.

大部分功能逻辑都在，少量自定义异常类和扩展方法 ，可用类似代码自己替换

//EpPlus读取生成Excel帮助类+读取csv帮助类，epplus只支持开放的Excel文件格式：xlsx，不支持 xls格式

[csharp] view plain copy print?
  1. /* ============================================================================== 
  2.  * 功能描述：EppHelper   
  3.  * 创 建 者：蒲奎民 
  4.  * 创建日期：2016-07-21 14:30:35 
  5.  * CLR Version :4.0.30319.42000 
  6.  * ==============================================================================*/  
  7. using OfficeOpenXml;  
  8. using OfficeOpenXml.Style;  
  9. using System;  
  10. using System.Collections.Concurrent;  
  11. using System.Collections.Generic;  
  12. using System.Data;  
  13. using System.Drawing;  
  14. using System.IO;  
  15. using System.Linq;  
  16. using System.Linq.Expressions;  
  17. using System.Reflection;  
  18. using System.Text;  
  19. using System.Text.RegularExpressions;  
  20. using System.Threading.Tasks;  
  21. using YCF.HRMS.Utilities.ExceptionHeper;  
  22. using YCF.HRMS.Utilities.ExtensionHelper;  
  23. /* 
  24.  * 引用文件： 
  25.  * packages\EPPlus.4.1.0\lib\net40\EPPlus.dll 
  26.  * packages\EPPlus.Extensions.1.0.0.0\lib\net40\EPPlus.Extensions.dll 
  27.  */  
  28. namespace YCF.HRMS.Utilities.CommomHelper  
  29. {  
  30.     /// <summary>  
  31.     /// EpPlus读取Excel帮助类+读取csv帮助类  
  32.     /// </summary>  
  33.     public class EppHelper  
  34.     {  
  35.         #region 由List创建简单Exel.列头取字段的Description或字段名  
  36.         /// <summary>  
  37.         /// 由List创建简单Exel.列头取字段的Description或字段名  
  38.         /// </summary>  
  39.         /// <typeparam name="T"></typeparam>  
  40.         /// <param name="filePath">The file path.</param>  
  41.         /// <param name="dataList">The data list.</param>  
  42.         public static void CreateExcelByList<T>(string filePath, List<T> dataList) where T : class  
  43.         {  
  44.   
  45.             string dirPath = Path.GetDirectoryName(filePath);  
  46.             string fileName = Path.GetFileName(filePath);  
  47.             FileInfo newFile = new FileInfo(filePath);  
  48.             if (newFile.Exists)  
  49.             {  
  50.                 newFile.Delete();  // ensures we create a new workbook  
  51.                 newFile = new FileInfo(filePath);  
  52.             }  
  53.             PropertyInfo[] properties = null;  
  54.             if (dataList.Count > 0)  
  55.             {  
  56.                 Type type = dataList[0].GetType();  
  57.                 properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);  
  58.                 var filedDescriptions = CommonFunctions.GetPropertyDescriptions<T>(true);//字段与excel列名对应关系  
  59.                 using (ExcelPackage package = new ExcelPackage(newFile))  
  60.                 {  
  61.                     ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");  
  62.                     //设置表头单元格格式  
  63.                     using (var range = worksheet.Cells[1, 1, 1, properties.Length])  
  64.                     {  
  65.                         range.Style.Font.Bold = true;  
  66.                         range.Style.Fill.PatternType = ExcelFillStyle.Solid;  
  67.                         range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);  
  68.                         range.Style.Font.Color.SetColor(Color.White);  
  69.                     }  
  70.                     int row = 1, col;  
  71.                     object objColValue;  
  72.                     string colValue;  
  73.                     //表头  
  74.                     for (int j = 0; j < properties.Length; j++)  
  75.                     {  
  76.                         row = 1;  
  77.                         col = j + 1;  
  78.                         var description = filedDescriptions.Where(o => o.Key == properties[j].Name).Select(o => o.Value).FirstOrDefault();  
  79.                         worksheet.Cells[row, col].Value = (description == null || description.Description.IsNullOrEmpty()) ? properties[j].Name : description.Description;  
  80.                     }  
  81.                     worksheet.View.FreezePanes(row + 1, 1); //冻结表头  
  82.                     //各行数据  
  83.                     for (int i = 0; i < dataList.Count; i++)  
  84.                     {  
  85.                         row = i + 2;  
  86.                         for (int j = 0; j < properties.Length; j++)  
  87.                         {  
  88.                             col = j + 1;  
  89.                             objColValue = properties[j].GetValue(dataList[i], null);  
  90.                             colValue = objColValue == null ? "" : objColValue.ToString();  
  91.                             worksheet.Cells[row, col].Value = colValue;  
  92.                         }  
  93.                     }  
  94.                     package.Save();  
  95.                 }  
  96.   
  97.             }  
  98.         }  
  99.         #endregion  
  100.  
  101.         #region 读取Excel数据到DataSet  
  102.         /// <summary>  
  103.         /// 读取Excel数据到DataSet  
  104.         /// </summary>  
  105.         /// <param name="filePath">The file path.</param>  
  106.         /// <returns></returns>  
  107.         public static DataSet ReadExcelToDataSet(string filePath)  
  108.         {  
  109.             DataSet ds = new DataSet("ds");  
  110.             DataRow dr;  
  111.             object objCellValue;  
  112.             string cellValue;  
  113.             using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))  
  114.             using (ExcelPackage package = new ExcelPackage())  
  115.             {  
  116.                 package.Load(fs);  
  117.                 foreach (var sheet in package.Workbook.Worksheets)  
  118.                 {  
  119.                     if (sheet.Dimension == null) continue;  
  120.                     var columnCount = sheet.Dimension.End.Column;  
  121.                     var rowCount = sheet.Dimension.End.Row;  
  122.                     if (rowCount > 0)  
  123.                     {  
  124.                         DataTable dt = new DataTable(sheet.Name);  
  125.                         for (int j = 0; j < columnCount; j++)//设置DataTable列名  
  126.                         {  
  127.                             objCellValue = sheet.Cells[1, j + 1].Value;  
  128.                             cellValue = objCellValue == null ? "" : objCellValue.ToString();  
  129.                             dt.Columns.Add(cellValue, typeof(string));  
  130.                         }  
  131.                         for (int i = 2; i <= rowCount; i++)  
  132.                         {  
  133.                             dr = dt.NewRow();  
  134.                             for (int j = 1; j <= columnCount; j++)  
  135.                             {  
  136.                                 objCellValue = sheet.Cells[i, j].Value;  
  137.                                 cellValue = objCellValue == null ? "" : objCellValue.ToString();  
  138.                                 dr[j - 1] = cellValue;  
  139.                             }  
  140.                             dt.Rows.Add(dr);  
  141.                         }  
  142.                         ds.Tables.Add(dt);  
  143.                     }  
  144.                 }  
  145.             }  
  146.             return ds;  
  147.   
  148.         }  
  149.         #endregion  
  150.  
  151.         #region 读取csv数据到List<T>,列头与字段的Description对应  
  152.         /// <summary>  
  153.         /// 读取csv数据到List<T>,列头与字段的Description对应  
  154.         /// </summary>  
  155.         /// <typeparam name="T">输出类型</typeparam>  
  156.         /// <param name="filePath">文件路径</param>  
  157.         /// <returns></returns>  
  158.         public static List<T> ReadCsvToModelList<T>(string filePath, string uploadMonth, long userId) where T : class  
  159.         {  
  160.             List<T> list = new List<T>();  
  161.             object objCellValue;  
  162.             string cellValue;  
  163.             string columnName;  
  164.             var filedDescriptions = CommonFunctions.GetPropertyDescriptions<T>(false);//字段与excel列名对应关系  
  165.             var fieldIndexs = new List<KeyValuePair<int, FieldDescriptionModel>>();//Excel列索引与字段名对应关系  
  166.             int lineCount = 1;  
  167.             string mes = "";  
  168.             Type type = typeof(T);  
  169.             int iUserId = int.Parse(userId.ToString());  
  170.             var properties = type.GetProperties();  
  171.             using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))  
  172.             using (StreamReader sr = new StreamReader(fs, Encoding.Default))  
  173.             {  
  174.   
  175.                 string line;  
  176.                 var columnCount = 0;  
  177.                 bool isEmptyCellValue = false;//是否有必须填写但实际没填写的数据  
  178.                 while (true)  
  179.                 {  
  180.                     isEmptyCellValue = false;  
  181.                     line = sr.ReadLine();  
  182.                     if (string.IsNullOrWhiteSpace(line))  
  183.                     {  
  184.                         break;  
  185.                     }  
  186.                     string[] split = SplitCsvLine(line);  
  187.                     if (lineCount == 1)//列头  
  188.                     {  
  189.                         columnCount = split.Length;  
  190.                         for (int j = 0; j < columnCount; j++)//设置DataTable列名  
  191.                         {  
  192.                             objCellValue = split[j];  
  193.                             cellValue = objCellValue == null ? "" : objCellValue.ToString();  
  194.                             var cellFieldName = filedDescriptions.Where(o => o.Key == cellValue).Select(o => o.Value).FirstOrDefault();  
  195.                             fieldIndexs.Add(new KeyValuePair<int, FieldDescriptionModel>(j, cellFieldName));  
  196.                         }  
  197.                         lineCount++;  
  198.                         continue;  
  199.                     }  
  200.   
  201.                     //当第一列为空时退出csv读取  
  202.                     if (string.IsNullOrWhiteSpace(split[0]))  
  203.                     {  
  204.                         break;  
  205.                     }  
  206.                     if (split.Length > columnCount)  
  207.                     {  
  208.                         mes += lineCount.ToString() + ","; //string.Format("第{0}行读取有误，数据列数{1}大于标题列数{2}，请检查该行单元格内是否有逗号<\br>", lineCount, split.Length, dataTypes.Length);  
  209.                         lineCount++;  
  210.                         continue;  
  211.                     }  
  212.                     if (split.Length < columnCount)  
  213.                     {  
  214.                         mes += lineCount.ToString() + ",";//string.Format("第{0}行数据读取有误，数据列数{1}小于标题列数{2}，请检查该行单元格内是否有逗号<\br>", lineCount, split.Length, dataTypes.Length);  
  215.                         lineCount++;  
  216.                         continue;  
  217.                     }  
  218.                     T model = Activator.CreateInstance<T>();  
  219.   
  220.                     for (int j = 0; j < columnCount; j++)  
  221.                     {  
  222.                         objCellValue = split[j];  
  223.                         var field = fieldIndexs.First(o => o.Key == j).Value;  
  224.                         columnName = field.FieldName;  
  225.                         if (columnName.IsNullOrEmpty()) continue;  
  226.                         PropertyInfo p = properties.FirstOrDefault(o => string.Equals(o.Name, columnName, StringComparison.InvariantCultureIgnoreCase));  
  227.                         if (p == null) continue;  
  228.                         SetPropertyValue<T>(ref model, ref p, ref objCellValue, ref field, ref isEmptyCellValue);  
  229.                     }  
  230.                     if (isEmptyCellValue)  
  231.                     {  
  232.                         continue;  
  233.                     }  
  234.                     SetPropertyValueForKnowColumns<T>(ref model, ref properties, ref uploadMonth, ref userId, ref iUserId);  
  235.                     list.Add(model);  
  236.                     lineCount++;  
  237.                 }  
  238.             }  
  239.             if (mes != "") throw new BusinessException("第" + mes.TrimEnd(',') + "行读取有误，请检查该行单元格内是否有逗号");  
  240.             #region 判断第一行数据是否合格，Excel列名和字段对不上的情况，检查一个就等于检查全部  
  241.             var firstModel = list.FirstOrDefault();  
  242.             CheckModelRequiredData<T>(ref firstModel, ref properties, ref filedDescriptions);  
  243.             #endregion  
  244.             return list;  
  245.         }  
  246.  
  247.         #endregion  
  248.  
  249.         #region 设置字段属性的值  
  250.         /// <summary>  
  251.         /// 设置字段属性的值  
  252.         /// </summary>  
  253.         /// <typeparam name="T"></typeparam>  
  254.         /// <param name="model">The model.</param>  
  255.         /// <param name="p">The p.</param>  
  256.         /// <param name="objCellValue">The object cell value.</param>  
  257.         /// <param name="field">The field.</param>  
  258.         /// <param name="isEmptyCellValue">if set to <c>true</c> [is empty cell value].</param>  
  259.         /// <exception cref="BusinessException">出错，字段名： + p.Name + ,类型： + p.PropertyType.ToString() + ,数据： + (objCellValue == null ?  : objCellValue.ToString()) + ,错误信息： + ex.Message</exception>  
  260.         private static void SetPropertyValue<T>(ref T model, ref PropertyInfo p, ref object objCellValue, ref FieldDescriptionModel field, ref bool isEmptyCellValue) where T : class  
  261.         {  
  262.             var propertyType = p.PropertyType.ToString();  
  263.             switch (propertyType)  
  264.             {  
  265.                 case "System.String":  
  266.                     {  
  267.                         if (objCellValue is string == false)  
  268.                         {  
  269.                             if (objCellValue == null) objCellValue = "";  
  270.                             else objCellValue = objCellValue.ToString();  
  271.                             if (field.IsRequire && objCellValue.ToString() == "")  
  272.                             {  
  273.                                 isEmptyCellValue = true;  
  274.                                 break;  
  275.                             }  
  276.                         }  
  277.                         if (objCellValue != null) objCellValue = objCellValue.ToString().Replace("\r\n", "").Trim();  
  278.                     }  
  279.                     break;  
  280.                 case "System.Decimal":  
  281.                 case "System.Nullable`1[System.Decimal]":  
  282.                     {  
  283.                         if (objCellValue is decimal == false)  
  284.                         {  
  285.                             if (objCellValue != null)  
  286.                             {  
  287.                                 if (objCellValue.ToString().EndsWith("%"))  
  288.                                 {  
  289.                                     objCellValue = Convert.ToDecimal(objCellValue.ToString().TrimEnd('%')) / 100M;  
  290.                                 }  
  291.                                 else objCellValue = Convert.ToDecimal(objCellValue.ToString());  
  292.                             }  
  293.                         }  
  294.                     }  
  295.                     break;  
  296.                 case "System.Int32":  
  297.                 case "System.Nullable`1[System.Int32]":  
  298.                     {  
  299.                         if (objCellValue is int == false)  
  300.                         {  
  301.                             if (objCellValue != null) objCellValue = Convert.ToInt32(objCellValue);  
  302.                         }  
  303.                     }  
  304.                     break;  
  305.                 case "System.Int64":  
  306.                 case "System.Nullable`1[System.Int64]":  
  307.                     {  
  308.                         if (objCellValue is long == false)  
  309.                         {  
  310.                             if (objCellValue != null) objCellValue = Convert.ToInt64(objCellValue);  
  311.                         }  
  312.                     }  
  313.                     break;  
  314.                 case "System.DateTime":  
  315.                 case "System.Nullable`1[System.DateTime]":  
  316.                     {  
  317.                         if (objCellValue is DateTime == false)  
  318.                         {  
  319.                             if (objCellValue != null) objCellValue = ToDateTimeValue(objCellValue.ToString());  
  320.                         }  
  321.                     }  
  322.                     break;  
  323.                 case "System.Boolean":  
  324.                 case "System.Nullable`1[System.Boolean]":  
  325.                     {  
  326.                         if (objCellValue is bool == false)  
  327.                         {  
  328.   
  329.                             if (objCellValue != null)  
  330.                             {  
  331.                                 var tempValue = objCellValue.ToString().Trim();  
  332.                                 if (tempValue == "#N/A") tempValue = "";  
  333.                                 else if (tempValue == "是") tempValue = "True";  
  334.                                 else if (tempValue == "否") tempValue = "False";  
  335.                                 if (tempValue != "") objCellValue = Convert.ToBoolean(tempValue);  
  336.                                 else objCellValue = null;  
  337.                             }  
  338.                         }  
  339.                     }  
  340.                     break;  
  341.             }  
  342.             try  
  343.             {  
  344.                 p.SetValue(model, objCellValue, null);  
  345.             }  
  346.             catch (Exception ex)  
  347.             {  
  348.                 throw new BusinessException("出错，字段名：" + p.Name + ",类型：" + p.PropertyType.ToString() + ",数据：" + (objCellValue == null ? "" : objCellValue.ToString()) + ",错误信息：" + ex.Message);  
  349.             }  
  350.         }  
  351.         #endregion  
  352.  
  353.         #region 其他已知属性赋默认值  
  354.         /// <summary>  
  355.         /// 其他已知属性赋默认值  
  356.         /// </summary>  
  357.         /// <typeparam name="T"></typeparam>  
  358.         /// <param name="model">The model.</param>  
  359.         /// <param name="properties">The properties.</param>  
  360.         /// <param name="uploadMonth">The upload month.</param>  
  361.         /// <param name="userId">The user identifier.</param>  
  362.         /// <param name="iUserId">The i user identifier.</param>  
  363.         private static void SetPropertyValueForKnowColumns<T>(ref T model, ref PropertyInfo[] properties, ref string uploadMonth, ref long userId, ref int iUserId)  
  364.         {  
  365.             var monthProperty = properties.FirstOrDefault(o => string.Equals(o.Name, "Month", StringComparison.InvariantCultureIgnoreCase));  
  366.             if (monthProperty != null)  
  367.             {  
  368.                 monthProperty.SetValue(model, uploadMonth, null);  
  369.             }  
  370.             var createTimeProperty = properties.FirstOrDefault(o => string.Equals(o.Name, "CreationTime", StringComparison.InvariantCultureIgnoreCase));  
  371.             if (createTimeProperty != null)  
  372.             {  
  373.                 createTimeProperty.SetValue(model, DateTime.Now, null);  
  374.             }  
  375.             var modifyTimeProperty = properties.FirstOrDefault(o => string.Equals(o.Name, "LastModificationTime", StringComparison.InvariantCultureIgnoreCase));  
  376.             if (modifyTimeProperty != null)  
  377.             {  
  378.                 modifyTimeProperty.SetValue(model, DateTime.Now, null);  
  379.             }  
  380.             var createUserIdProperty = properties.FirstOrDefault(o => string.Equals(o.Name, "CreatorUserId", StringComparison.InvariantCultureIgnoreCase));  
  381.             if (createUserIdProperty != null)  
  382.             {  
  383.                 if (createTimeProperty.PropertyType.ToString() == "System.Int32") createUserIdProperty.SetValue(model, iUserId, null);  
  384.                 else createUserIdProperty.SetValue(model, userId, null);  
  385.             }  
  386.         }  
  387.         #endregion  
  388.  
  389.         #region 最后判断第一行数据是否合格，Excel列名和字段对不上的情况，检查一个就等于检查全部  
  390.         /// <summary>  
  391.         /// 最后判断第一行数据是否合格，Excel列名和字段对不上的情况，检查一个就等于检查全部  
  392.         /// </summary>  
  393.         /// <typeparam name="T"></typeparam>  
  394.         /// <param name="model">The model.</param>  
  395.         /// <param name="filedDescriptions">The filed descriptions.</param>  
  396.         private static void CheckModelRequiredData<T>(ref T firstModel, ref PropertyInfo[] properties, ref List<KeyValuePair<string, FieldDescriptionModel>> filedDescriptions)  
  397.         {  
  398.             if (firstModel != null)  
  399.             {  
  400.                 var fieldNameList = filedDescriptions.Where(o => o.Value != null).Select(o => o.Value).ToList();  
  401.   
  402.                 foreach (var p in properties)  
  403.                 {  
  404.                     var fieldNameModel = fieldNameList.FirstOrDefault(o => string.Equals(o.FieldName, p.Name, StringComparison.InvariantCultureIgnoreCase));  
  405.                     if (fieldNameModel == null || fieldNameModel.IsRequire == false) continue;//为空或没有Require标记，跳过  
  406.                     object objCellValue = p.GetValue(firstModel);  
  407.                     if (objCellValue == null || objCellValue.ToString() == "")  
  408.                     {  
  409.                         throw new BusinessException("出错，字段名：" + p.Name + ",类型：" + p.PropertyType.ToString() + " 必填字段数据为空！");  
  410.                     }  
  411.                 }  
  412.             }  
  413.         }   
  414.         #endregion  
  415.  
  416.         #region 读取Excel数据到List<T>,列头与字段的Description对应  
  417.         /// <summary>  
  418.         /// 读取Excel数据到List<T>,列头与字段的Description对应  
  419.         /// </summary>  
  420.         /// <typeparam name="T"></typeparam>  
  421.         /// <param name="filePath">The file path.</param>  
  422.         /// <returns></returns>  
  423.         public static List<T> ReadExcelToModelList<T>(string filePath, string uploadMonth, long userId, out string log, int titleRow = 1) where T : class  
  424.         {  
  425.             ExcelPackage package = null;  
  426.             FileStream fs = null;  
  427.             try  
  428.             {  
  429.                 //ConcurrentBag<T> list = new ConcurrentBag<T>();  
  430.                 List<T> list = new List<T>();  
  431.                 log = string.Format("{0:yyyy-MM-dd HH:mm:ss}开始载入文件{1}\r\n", DateTime.Now, filePath);  
  432.                 var filedDescriptions = CommonFunctions.GetPropertyDescriptions<T>(false);//字段与excel列名对应关系  
  433.                 var fieldIndexs = new List<KeyValuePair<int, FieldDescriptionModel>>();//Excel列索引与字段名对应关系  
  434.                 fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite);  
  435.                 package = new ExcelPackage();  
  436.                 package.Load(fs);  
  437.                 log += string.Format("{0:yyyy-MM-dd HH:mm:ss}完成载入文件{1}，开始解析\r\n", DateTime.Now, filePath);  
  438.                 if (package.Workbook.Worksheets.Count == 0)  
  439.                 {  
  440.                     throw new BusinessException("读取的Excel中sheet数量为0，请检查文件格式是否为xlsx！");  
  441.                 }  
  442.                 var sheet = package.Workbook.Worksheets[1];  
  443.   
  444.                 if (sheet.Dimension == null) return list.ToList();  
  445.                 var columnCount = sheet.Dimension.End.Column;  
  446.                 var rowCount = sheet.Dimension.End.Row;  
  447.                 if (rowCount == 0) return list.ToList();  
  448.                 //DataTable dt = new DataTable(sheet.Name);  
  449.                 for (int j = 1; j <= columnCount; j++)//列名与字段名对应关系  
  450.                 {  
  451.                     object objCellValue = sheet.Cells[titleRow, j].Value;  
  452.                     string cellValue = objCellValue == null ? "" : objCellValue.ToString();  
  453.                     if (cellValue.Len() == 0) continue;  
  454.                     var cellFieldName = filedDescriptions.Where(o => o.Key == cellValue).Select(o => o.Value).FirstOrDefault();  
  455.                     fieldIndexs.Add(new KeyValuePair<int, FieldDescriptionModel>(j, cellFieldName));  
  456.                     //dt.Columns.Add(cellValue, typeof(string));  
  457.                 }  
  458.                 Type type = typeof(T);  
  459.                 int iUserId = int.Parse(userId.ToString());  
  460.                 var properties = type.GetProperties();  
  461.                 //List<int> rowIndexList = Enumerable.Range(titleRow + 1, rowCount - titleRow).ToList();//数据行的行号集合  
  462.                 //Parallel.ForEach(rowIndexList, (i) =>  
  463.                 for (int i = titleRow + 1; i <= rowCount; i++)  
  464.                 {  
  465.                     #region 处理Excel每行数据  
  466.                     object objCellValue = null;  
  467.                     string columnName = null;  
  468.                     bool isEmptyCellValue = false;//是否有必须填写但实际没填写的数据  
  469.                     T model = Activator.CreateInstance<T>();  
  470.                     for (int j = 1; j <= columnCount; j++)  
  471.                     {  
  472.                         objCellValue = sheet.Cells[i, j].Value;  
  473.                         var fieldPair = fieldIndexs.FirstOrDefault(o => o.Key == j);  
  474.                         var field = fieldPair.Value;  
  475.                         columnName = field == null ? "" : field.FieldName;  
  476.                         if (columnName.IsNullOrEmpty()) continue;  
  477.                         PropertyInfo p = properties.FirstOrDefault(o => string.Equals(o.Name, columnName, StringComparison.InvariantCultureIgnoreCase));  
  478.                         if (p == null) continue;  
  479.                         SetPropertyValue<T>(ref model, ref p, ref objCellValue, ref field, ref isEmptyCellValue);  
  480.   
  481.                     }  
  482.                     if (!isEmptyCellValue)  
  483.                     {  
  484.                         SetPropertyValueForKnowColumns<T>(ref model, ref properties, ref uploadMonth, ref userId, ref iUserId);  
  485.                         list.Add(model);  
  486.                     }  
  487.  
  488.                     #endregion  
  489.                 }  
  490.                 //);  
  491.                 #region 判断第一行数据是否合格，Excel列名和字段对不上的情况，检查一个就等于检查全部  
  492.                 var firstModel = list.FirstOrDefault();  
  493.                 CheckModelRequiredData<T>(ref firstModel, ref properties, ref filedDescriptions);  
  494.                 #endregion  
  495.                 log += string.Format("{0:yyyy-MM-dd HH:mm:ss}完成解析文件{1}\r\n", DateTime.Now, filePath);  
  496.                 return list;  
  497.             }  
  498.             finally  
  499.             {  
  500.                 if (package != null) package.Dispose();//释放Excel对象资源  
  501.                 if (fs != null)//关闭和释放文件流资源  
  502.                 {  
  503.                     fs.Close(); fs.Dispose();  
  504.                 }  
  505.             }  
  506.   
  507.         }  
  508.         #endregion  
  509.  
  510.         #region Splits the CSV line.  
  511.         /// <summary>  
  512.         /// Splits the CSV line.  
  513.         /// </summary>  
  514.         /// <param name="s">The s.</param>  
  515.         /// <returns></returns>  
  516.         private static string[] SplitCsvLine(string s)  
  517.         {  
  518.             Regex regex = new Regex("\".*?\"");  
  519.             var a = regex.Matches(s).Cast<Match>().Select(m => m.Value).ToList();  
  520.             var b = regex.Replace(s, "%_%");  
  521.             var c = b.Split(',');  
  522.             for (int i = 0, j = 0; i < c.Length && j < a.Count; i++)  
  523.             {  
  524.                 if (c[i] == "%_%")  
  525.                 {  
  526.                     c[i] = a[j++];  
  527.                 }  
  528.             }  
  529.             return c;  
  530.         }  
  531.         #endregion  
  532.  
  533.         #region Excel中数字时间转换成时间格式  
  534.         /// <summary>  
  535.         /// Excel中数字时间转换成时间格式  
  536.         /// </summary>  
  537.         /// <param name="timeStr">数字,如:42095.7069444444/0.650694444444444</param>  
  538.         /// <returns>日期/时间格式</returns>  
  539.         public static DateTime ToDateTimeValue(string strNumber)  
  540.         {  
  541.             if (!string.IsNullOrWhiteSpace(strNumber))  
  542.             {  
  543.                 Decimal tempValue;  
  544.                 DateTime tempret;  
  545.                 if (DateTime.TryParse(strNumber, out tempret))  
  546.                 {  
  547.                     return tempret;  
  548.                 }  
  549.                 if (strNumber.Length == 8 && strNumber.Contains(".") == false)//20160430  
  550.                 {  
  551.   
  552.                     strNumber = strNumber.Insert(4, "-").Insert(6 + 1, "-");  
  553.                     if (DateTime.TryParse(strNumber, out tempret))  
  554.                     {  
  555.                         return tempret;  
  556.                     }  
  557.                     else return default(DateTime);  
  558.                 }  
  559.                 //先检查 是不是数字;  
  560.                 if (Decimal.TryParse(strNumber, out tempValue))  
  561.                 {  
  562.                     //天数,取整  
  563.                     int day = Convert.ToInt32(Math.Truncate(tempValue));  
  564.                     //这里也不知道为什么. 如果是小于32,则减1,否则减2  
  565.                     //日期从1900-01-01开始累加   
  566.                     // day = day < 32 ? day - 1 : day - 2;  
  567.                     DateTime dt = new DateTime(1900, 1, 1).AddDays(day < 32 ? (day - 1) : (day - 2));  
  568.   
  569.                     //小时:减掉天数,这个数字转换小时:(* 24)   
  570.                     Decimal hourTemp = (tempValue - day) * 24;//获取小时数  
  571.                     //取整.小时数  
  572.                     int hour = Convert.ToInt32(Math.Truncate(hourTemp));  
  573.                     //分钟:减掉小时,( * 60)  
  574.                     //这里舍入,否则取值会有1分钟误差.  
  575.                     Decimal minuteTemp = Math.Round((hourTemp - hour) * 60, 2);//获取分钟数  
  576.                     int minute = Convert.ToInt32(Math.Truncate(minuteTemp));  
  577.   
  578.                     //秒:减掉分钟,( * 60)  
  579.                     //这里舍入,否则取值会有1秒误差.  
  580.                     Decimal secondTemp = Math.Round((minuteTemp - minute) * 60, 2);//获取秒数  
  581.                     int second = Convert.ToInt32(Math.Truncate(secondTemp));  
  582.                     if (second >= 60)  
  583.                     {  
  584.                         second -= 60;  
  585.                         minute += 1;  
  586.                     }  
  587.                     if (minute >= 60)  
  588.                     {  
  589.                         minute -= 60;  
  590.                         hour += 1;  
  591.                     }  
  592.   
  593.                     //时间格式:00:00:00  
  594.                     string resultTimes = string.Format("{0}:{1}:{2}",  
  595.                             (hour < 10 ? ("0" + hour) : hour.ToString()),  
  596.                             (minute < 10 ? ("0" + minute) : minute.ToString()),  
  597.                             (second < 10 ? ("0" + second) : second.ToString()));  
  598.                     var str = string.Format("{0} {1}", dt.ToString("yyyy-MM-dd"), resultTimes);  
  599.                     try  
  600.                     {  
  601.                         return DateTime.Parse(str);  
  602.                     }  
  603.                     catch (Exception ex)  
  604.                     {  
  605.                         throw new Exception("DateTime.Parse出错,str：" + str, ex);  
  606.                     }  
  607.   
  608.                 }  
  609.             }  
  610.             return default(DateTime);  
  611.         }  
  612.         #endregion  
  613.   
  614.     }  
  615. }  



 

//Aspose.Cells生成xlsx文件帮助类

[csharp] view plain copy print?
  1. using Aspose.Cells;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.ComponentModel;  
  5. using System.ComponentModel.DataAnnotations;  
  6. using System.Linq;  
  7. using System.Linq.Expressions;  
  8. using System.Reflection;  
  9. using System.Text;  
  10. using System.Threading.Tasks;  
  11.   
  12. namespace YCF.HRMS.Utilities.CommomHelper  
  13. {  
  14.     /// <summary>  
  15.     /// Aspose.Cells生成xlsx文件  
  16.     /// </summary>  
  17.     public static class AsposeCellsHelper  
  18.     {  
  19.         public static void CreateExcel<T>(IEnumerable<T> content, string filePath, string sheetName, int startRow,bool setBorder = false, string getColorMethodName = null)  
  20.         {  
  21.             var columnsAccessor = CreateColumnsAccessor<T>();  
  22.             var colInvoker = ExpressionHelper.GetMemberAccessor(columnsAccessor);  
  23.             var t = typeof(T);  
  24.   
  25.             Workbook workbook = new Workbook();  
  26.   
  27.             var sheet = workbook.Worksheets[0]; ;  
  28.             sheet.Name = sheetName;  
  29.             var freezeAttr = t.GetCustomAttribute<ExcelFreezeAttribute>();  
  30.             //冻结表头  
  31.             if (freezeAttr != null)  
  32.                 sheet.FreezePanes(startRow + 1, 0 + 1, freezeAttr.FreezeRowIndex + startRow + 1, freezeAttr.FreezeColumnIndex + 1);  
  33.   
  34.             var cells = workbook.Worksheets[0].Cells;  
  35.             int rowIndex = startRow;  
  36.             int colIndex = 0;  
  37.             for (int i = 0; i < colInvoker.Count(); i++)  
  38.             {  
  39.                 var col = colInvoker.ElementAt(i);  
  40.                 //删除隐藏属性  
  41.                 var widthAttr = col.Key.GetCustomAttribute<ExcelColumnAttribute>();  
  42.                 if (widthAttr != null)  
  43.                 {  
  44.   
  45.                     if (widthAttr.Hide)  
  46.                     {  
  47.                         colInvoker.Remove(col.Key);  
  48.                         i--;  
  49.                         continue;  
  50.                     }  
  51.                 }  
  52.                 var title = col.Key.Name;  
  53.                 var desc = col.Key.GetCustomAttribute<DescriptionAttribute>();  
  54.                 if (desc != null)  
  55.                     title = desc.Description;  
  56.                 else  
  57.                 {  
  58.                     var display = col.Key.GetCustomAttribute<DisplayAttribute>();  
  59.                     if (display != null)  
  60.                         title = display.Name;  
  61.                 }  
  62.                 var myCell = sheet.Cells[rowIndex, colIndex];  
  63.                 Style style = myCell.GetStyle();  
  64.                 myCell.PutValue(title);  
  65.                 style.Font.IsBold = true;  
  66.                 style.HorizontalAlignment = TextAlignmentType.Center;  
  67.                 style.Font.Name = "宋体";  
  68.                 style.Font.Size = 10;  
  69.                 if (setBorder)  
  70.                 {  
  71.                     style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;  
  72.                     style.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;  
  73.                     style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;  
  74.                     style.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;  
  75.                     style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;  
  76.                     style.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;  
  77.                     style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;  
  78.                     style.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;  
  79.                 }  
  80.                 myCell.SetStyle(style);  
  81.                 cells.SetColumnWidth(colIndex, 15);  
  82.                 colIndex++;  
  83.             }  
  84.   
  85.             rowIndex++;  
  86.   
  87.             PropertyInfo[] properties = colInvoker.Keys.OfType<PropertyInfo>().ToArray();  
  88.             Func<T, string> getHtmlColorFunc = null;  
  89.             if(getColorMethodName != null)  
  90.             {  
  91.                 MethodInfo getColorMethod = typeof(T).GetMethod(getColorMethodName);  
  92.                 if (getColorMethod != null)  
  93.                 {  
  94.                     var parameter = Expression.Parameter(typeof(T));  
  95.                     getHtmlColorFunc = Expression.Lambda<Func<T, string>>(Expression.Call(parameter, getColorMethod), parameter).Compile();  
  96.                 }  
  97.             }  
  98.   
  99.             //格式  
  100.             Func<PropertyInfo, Cell, Style> colunmnStyles = (p, cell) =>  
  101.              {  
  102.                  Style style = cell.GetStyle();  
  103.                  var type = p.PropertyType;  
  104.                  if (type.IsGenericType)  
  105.                  {  
  106.                      type = type.GetGenericArguments()[0];  
  107.                  }  
  108.                  if (type == typeof(DateTime))  
  109.                  {  
  110.                      style.Custom = "yyyyMMdd";  
  111.                  }  
  112.                  else if (type == typeof(double) || type == typeof(decimal))  
  113.                  {  
  114.                      style.Custom = "0.00";  
  115.                  }  
  116.                  style.Font.Name = "宋体";  
  117.                  style.Font.Size = 10;  
  118.                  return style;  
  119.              };  
  120.             var propertyValueSetter = properties.ToDictionary(p => p, p =>  
  121.             {  
  122.                 Action<object, Cell> valueSetter;  
  123.                 var type = p.PropertyType;  
  124.                 if (type.IsGenericType)  
  125.                 {  
  126.                     type = type.GetGenericArguments()[0];  
  127.                 }  
  128.                 if (type == typeof(DateTime))  
  129.                 {  
  130.                     valueSetter = (o, cell) =>  
  131.                     {  
  132.                         if (o != null)  
  133.                         {  
  134.                             cell.PutValue(Convert.ToDateTime(o));  
  135.                         }  
  136.                     };  
  137.                 }  
  138.                 else if (type == typeof(double) || type == typeof(decimal) || type == typeof(int))  
  139.                 {  
  140.                     valueSetter = (o, cell) =>  
  141.                     {  
  142.                         if (o != null)  
  143.                         {  
  144.                             cell.PutValue(Convert.ToDouble(o));  
  145.                         }  
  146.                     };  
  147.                 }  
  148.                 else  
  149.                 {  
  150.                     valueSetter = (o, cell) =>  
  151.                     {  
  152.                         if (o != null)  
  153.                         {  
  154.                             cell.PutValue(o.ToString());  
  155.                         }  
  156.                     };  
  157.                 }  
  158.                 return valueSetter;  
  159.             });  
  160.   
  161.   
  162.             foreach (var item in content)  
  163.             {  
  164.                 colIndex = 0;  
  165.                   
  166.                 foreach (var propertyInfo in properties)  
  167.                 {  
  168.                     object value = propertyInfo.GetValue(item);  
  169.                     var cell = sheet.Cells[rowIndex, colIndex++];  
  170.                     propertyValueSetter[propertyInfo](value, cell);  
  171.                     var style = colunmnStyles(propertyInfo, cell);  
  172.                     if (getHtmlColorFunc != null)  
  173.                     {  
  174.   
  175.                         style.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(getHtmlColorFunc(item));  
  176.                         style.Pattern = BackgroundType.Solid;  
  177.                     }  
  178.                     if (setBorder)  
  179.                     {  
  180.                         style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;  
  181.                         style.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;  
  182.                         style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;  
  183.                         style.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;  
  184.                         style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;  
  185.                         style.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;  
  186.                         style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;  
  187.                         style.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;  
  188.                     }  
  189.                     cell.SetStyle(style);  
  190.                 }  
  191.                 rowIndex++;  
  192.             }  
  193.   
  194.             workbook.Save(filePath, new Aspose.Cells.OoxmlSaveOptions(Aspose.Cells.SaveFormat.Xlsx));  
  195.             workbook.Worksheets.Clear();  
  196.         }  
  197.   
  198.         internal static Expression<Func<T, object>> CreateColumnsAccessor<T>()  
  199.         {  
  200.             var type = typeof(T);  
  201.             var ps = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);  
  202.             var bindings = new List<MemberBinding>();  
  203.             var parameter = Expression.Parameter(type, "p");  
  204.             foreach (var p in ps)  
  205.             {  
  206.                 bindings.Add(Expression.Bind(p, Expression.MakeMemberAccess(parameter, p)));  
  207.             }  
  208.             var creator = Expression.MemberInit(Expression.New(type), bindings);  
  209.             var columnsAccessor = Expression.Lambda<Func<T, object>>(creator, parameter);  
  210.             return columnsAccessor;  
  211.         }  
  212.     }  
  213.     /// <summary>  
  214.     /// 可加的列宽属性  
  215.     /// </summary>  
  216.     /// <seealso cref="System.Attribute" />  
  217.     [AttributeUsage(AttributeTargets.Property)]  
  218.     public class ExcelColumnAttribute : Attribute  
  219.     {  
  220.         public int Width { get; set; }  
  221.         public bool Hide { get; set; }  
  222.     }  
  223.     /// <summary>  
  224.     /// 可加颜色属性  
  225.     /// </summary>  
  226.     /// <seealso cref="System.Attribute" />  
  227.     [AttributeUsage(AttributeTargets.Class)]  
  228.     public class ExcelCellStyleAttribute : Attribute  
  229.     {  
  230.         public string HtmlColorAccessor { get; set; }  
  231.     }  
  232.     /// <summary>  
  233.     /// 可加冻结属性  
  234.     /// </summary>  
  235.     /// <seealso cref="System.Attribute" />  
  236.     [AttributeUsage(AttributeTargets.Class)]  
  237.     public class ExcelFreezeAttribute : Attribute  
  238.     {  
  239.         public int FreezeColumnIndex { get; set; }  
  240.         public int FreezeRowIndex { get; set; }  
  241.     }  
  242. }  

aspose引用：packages\Aspose.Cells\7.0.3(2011-11-12)\Aspose.Cells.dll

之前写过npoi的读取excel的文章，现在epplus也可以。

 

对比：

npoi可读写xls和xlsx，epplus只支持读写xlsx. 读写效率应该都差不了多少，写入少量数据可用这两个，大量数据写的话最好要用aspose。

aspose写入速度比npoi和epplus快。

posted @ 2017-07-25 17:03  superstar  阅读(1554)  评论(0)  编辑  收藏  举报