Nuget EPPlus的使用

EPPlus：网站

 

Supported Functions

 

 

Excel Merge Operate

    public class ExcelMergeOperate
    {
        private static Logger _logger = LogManager.GetCurrentClassLogger();

        #region private method

        private static FileInfo CreateNewExcleFile(string excelPath)
        {
            FileInfo newFile = new FileInfo(excelPath);
            if (newFile.Exists)
            {
                newFile.Delete();  // ensures we create a new workbook
                newFile = new FileInfo(excelPath);
            }
            return newFile;
        }

        private static void AddSheet(ExcelWorksheets fromSheets ,ExcelWorksheets toSheets,string defualtSheetName="")
        {
            foreach (var sheet in fromSheets)
            {
                //check name of worksheet, in case that worksheet with same name already exist exception will be thrown by EPPlus
                string workSheetName = defualtSheetName != null?defualtSheetName:( sheet.Name!=null?sheet.Name : DateTime.Now.ToString("yyyyMMddhhssmmm"));
                foreach (var masterSheet in toSheets)
                {
                    if (sheet.Name == masterSheet.Name)
                    {
                        workSheetName = string.Format("{0}_{1}", workSheetName, DateTime.Now.ToString("yyyyMMddhhssmmm"));
                    }
                }
                sheet.ConditionalFormatting.RemoveAll();
                //add new sheet
                toSheets.Add(workSheetName, sheet);
            }
        }

        private static void MergeExcelsFromList<T>(List<T> date, string mergeExcelPath, string defualtSheetName = "date", bool create = false) where T : class
        {

            try
            {
                FileInfo newFile = create ? CreateNewExcleFile(mergeExcelPath) : new FileInfo(mergeExcelPath); ;
                using (ExcelPackage masterPackage = new ExcelPackage(newFile))
                {
                    //Create the Worksheet
                    var sheet = masterPackage.Workbook.Worksheets.Add(defualtSheetName);
                    //Create the format object to describe the text file
                    var format = new ExcelTextFormat();
                    format.TextQualifier = '"';
                    format.SkipLinesBeginning = 2;
                    format.SkipLinesEnd = 1;
                    //Now read the file into the sheet. Start from cell A1. Create a table with style 27. First row contains the header.
                    Console.WriteLine("Load the text file...");
                    //Load directories ordered by Name...
                    var range = sheet.Cells["A1"].LoadFromCollection(
                        from line in date
                        select line,
                        true, TableStyles.Medium9);
                    masterPackage.Save();
                }
            }
            catch (Exception ex)
            {
                _logger.Error(ex.ToString());
            }
        }

        #endregion


        #region public method

        public static void MergeExcelsFromMutiExcelPath(List<string> excelPaths,string mergeExcelPath,bool create=false)
        {
            try
            {
                //excelPaths = new string[] { @"D:\Jimmy Team Project\Doc\2017-11-20日报表汇总\fang_Westwin Report _GlasessShop.xlsx", @"D:\Jimmy Team Project\Doc\2017-11-20日报表汇总\总结new2017-11-20.xlsx" };
                //mergeExcelPath = @"D:\result.xlsx";
                FileInfo newFile = create ? CreateNewExcleFile(mergeExcelPath): new FileInfo(mergeExcelPath);
                using (ExcelPackage masterPackage = new ExcelPackage(newFile))
                {
                    foreach (var file in excelPaths)
                    {

                        using (ExcelPackage pckg = new ExcelPackage(new FileInfo(file)))
                        {
                            AddSheet(pckg.Workbook.Worksheets, masterPackage.Workbook.Worksheets);
                        }
                    }
                    masterPackage.Save();
                }
            }
            catch (Exception ex)
            {
                _logger.Error(ex.ToString());
            }
        }

        public static void MergeExcelsFromMutiExcelStream(List<Stream> streams, string mergeExcelPath, bool create = false)
        {
            try
            {
                FileInfo newFile = create ? CreateNewExcleFile(mergeExcelPath) : new FileInfo(mergeExcelPath);
                using (ExcelPackage masterPackage = new ExcelPackage(newFile))
                {
                    foreach (var stream in streams)
                    {
                        using (ExcelPackage pckg = new ExcelPackage(stream))
                        {
                            AddSheet(pckg.Workbook.Worksheets, masterPackage.Workbook.Worksheets);
                        }
                    }
                    masterPackage.Save();
                }
            }
            catch (Exception ex)
            {
                _logger.Error(ex.ToString());
            }
        }

        

        public static void MergeExcelsFromCSVFile<T, M>(string csvPath, string mergeExcelPath, string defualtSheetName = "csv", bool create = false, string csvDelimiter = ",") where T : class where M : CsvClassMap<T>
        {
            using (FileStream fs = new FileStream(csvPath, FileMode.Open, FileAccess.Read))
            {
                List<T> date = CSVHelper<T>.GetEntityFromCSV<M>(fs, csvDelimiter);
                MergeExcelsFromList(date, mergeExcelPath, defualtSheetName, create);
            }
        }

        public static void MergeExcelsFromCSVFile<T>(string csvPath, string mergeExcelPath, string defualtSheetName = "csv", bool create = false, string csvDelimiter = ",") where T : class
        {
            using (FileStream fs = new FileStream(csvPath, FileMode.Open, FileAccess.Read))
            {
                List<T> date = CSVHelper<T>.GetEntityFromCSV(fs, csvDelimiter);
                MergeExcelsFromList(date, mergeExcelPath, defualtSheetName, create);
            }
        }

        #endregion


    }

 

 

general sheet from list

         public void GenalralSheetFromList<T>(List<T> list, ref ExcelWorksheet ws, string dateFormate= "yyyy-mm-dd HH:mm:ss") where T:class
        {
            if (list == null || list.Count == 0) return;
            var startIndex = 1;
            var row = 1;
            PropertyInfo[] props = list[0].GetType().GetProperties();
            foreach (PropertyInfo pi in props)
            {
                ws.Cells[row, startIndex].Value = pi.Name;
                if (pi.PropertyType == typeof(DateTime)|| pi.PropertyType == typeof(DateTime?))
                {
                    ws.Column(startIndex).Style.Numberformat.Format = dateFormate;
                }
                startIndex++;
            }
            foreach (T item in list)
            {
                startIndex = 1;
                row++;
                foreach (PropertyInfo pi in props)
                {
                    ws.Cells[row, startIndex++].Value = item.GetType()
                                                 .GetProperty(pi.Name)
                                                 .GetValue(item, null);

                }
            }
            return;
        }