/// <summary> /// 导出List<T>为Excel文件 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <param name="sheetName"></param> /// <param name="fileName"></param> public static string ExportMultiToFile(this IDictionary <string, IEnumerable <ExcelBase> > dic, string cacheDirectory, string excelName = "") { var dateTime = DateTime.Now.ToString(_DateTimeFormatStr); if (excelName == "") { excelName = _DefaultExcelName; } if (excelName.IndexOf(".") > -1) { excelName = excelName.Substring(0, excelName.IndexOf(".")); } var fileName = string.Format(_ExportExcelNameTemplate, excelName, dateTime); DirectoryInfo dire = new DirectoryInfo(cacheDirectory); if (!dire.Exists) { dire.Create(); } string downFilePath = Path.Combine(cacheDirectory, fileName); using (ExcelPackage package = new ExcelPackage(new FileInfo(downFilePath))) { foreach (var item in dic) { var types = item.Value.GetType().GetGenericArguments(); if (types != null && types.Length > 0) { var exportSetting = new ExportExcelSetting(types.First()); if (exportSetting.ExportColumns.Count > 0) { var worksheet = package.Workbook.Worksheets.Add(item.Key); FillExcelWorksheet <ExcelBase>(worksheet, item.Value, exportSetting); } } } package.Save(); return(downFilePath); } }
/// <summary> /// 导出List<T>为byte[]数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dic"></param> /// <param name="sheetName"></param> /// <returns></returns> public static byte[] ExportMultiToBytes(this IDictionary <string, IEnumerable <ExcelBase> > dic) { byte[] excelBuffer = null; using (ExcelPackage package = new ExcelPackage()) { foreach (var item in dic) { var types = item.Value.GetType().GetGenericArguments(); if (types != null && types.Length > 0) { var exportSetting = new ExportExcelSetting(types.First()); if (exportSetting.ExportColumns.Count > 0) { var worksheet = package.Workbook.Worksheets.Add(item.Key); FillExcelWorksheet <ExcelBase>(worksheet, item.Value, exportSetting); } } } excelBuffer = package.GetAsByteArray(); } return(excelBuffer); }
/// <summary> /// 导出List<T>为Stream数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dic"></param> /// <returns></returns> public static MemoryStream ExportMultiToStream(this IDictionary <string, IEnumerable <ExcelBase> > dic) { MemoryStream stream = new MemoryStream(); using (ExcelPackage package = new ExcelPackage()) { foreach (var item in dic) { var types = item.Value.GetType().GetGenericArguments(); if (types != null && types.Length > 0) { var exportSetting = new ExportExcelSetting(types.First()); if (exportSetting.ExportColumns.Count > 0) { var worksheet = package.Workbook.Worksheets.Add(item.Key); FillExcelWorksheet <ExcelBase>(worksheet, item.Value, exportSetting); } } } package.SaveAs(stream); } return(stream); }
private static void FillExcelWorksheet <T>(ExcelWorksheet sheet, IEnumerable <T> list, ExportExcelSetting exportSetting) where T : ExcelBase { if (sheet == null) { return; } Type type = null; var types = list.GetType().GetGenericArguments(); if (types != null && types.Length > 0) { type = types.First(); } else { type = typeof(T); } Color titleColor = Color.White; Color headColor = Color.White; Color contentColor = Color.White; int titleRowCount = 1; int startRow = 1; int startCol = 1; int endRow = 1; int endCol = 1; if (exportSetting.ExportStyle != null) { var title = exportSetting.ExportStyle.Title; endCol = (exportSetting.ExportColumns?.Count) ?? 1; if (exportSetting.ExportStyle.TitleColor != null) { titleColor = exportSetting.ExportStyle.TitleColor; } if (exportSetting.ExportStyle.HeadColor != null) { headColor = exportSetting.ExportStyle.HeadColor; } if (exportSetting.ExportStyle.ContentColor != null) { contentColor = exportSetting.ExportStyle.ContentColor; } if (!string.IsNullOrEmpty(title) && endCol != 1) { int titleEndCol = endCol; if (exportSetting.ExportStyle.TitleColumnSpan > 0) { titleEndCol = exportSetting.ExportStyle.TitleColumnSpan; } sheet.Cells[startRow, startCol, endRow, titleEndCol].Merge = true; sheet.Cells[startRow, startCol, endRow, titleEndCol].Value = title; sheet.Cells[startRow, startCol, endRow, titleEndCol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Cells[startRow, startCol, endRow, titleEndCol].Style.VerticalAlignment = ExcelVerticalAlignment.Center; sheet.Cells[startRow, startCol, endRow, titleEndCol].Style.Font.Bold = exportSetting.ExportStyle.IsTitleBold; if (exportSetting.ExportStyle.TitleFontSize > 0) { sheet.Cells[startRow, startCol, endRow, titleEndCol].Style.Font.Size = exportSetting.ExportStyle.TitleFontSize; } sheet.Cells[startRow, startCol, endRow, titleEndCol].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells[startRow, startCol, endRow, titleEndCol].Style.Fill.BackgroundColor.SetColor(titleColor); sheet.Cells[startRow, startCol, endRow, titleEndCol].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); } titleRowCount++; } //写入数据 int mergeRowCount = 0; if (exportSetting.ExportColumns.GroupBy(o => o.MergeText).Any(o => o.Count() > 1 && o.First().MergeText != null && o.First().MergeText != null)) { mergeRowCount = 1; } int dataStartRow = titleRowCount + mergeRowCount; int dataStartCol = 1; for (var i = 0; i < exportSetting.ExportColumns.Count; i++) { if (mergeRowCount >= 1 && string.IsNullOrEmpty(exportSetting.ExportColumns[i].MergeText)) { sheet.Cells[dataStartRow - 1, i + dataStartCol, dataStartRow, i + dataStartCol].Merge = true; sheet.Cells[dataStartRow - 1, i + dataStartCol].Value = exportSetting.ExportColumns[i].Text; sheet.Cells[dataStartRow - 1, i + dataStartCol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Cells[dataStartRow - 1, i + dataStartCol].Style.VerticalAlignment = ExcelVerticalAlignment.Center; sheet.Cells[dataStartRow - 1, i + dataStartCol].Style.Font.Bold = exportSetting.ExportStyle.IsHeadBold; if (exportSetting.ExportStyle.HeadFontSize > 0) { sheet.Cells[dataStartRow - 1, i + dataStartCol].Style.Font.Size = exportSetting.ExportStyle.HeadFontSize; } sheet.Cells[dataStartRow - 1, i + dataStartCol].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells[dataStartRow - 1, i + dataStartCol].Style.Fill.BackgroundColor.SetColor(headColor); sheet.Cells[dataStartRow - 1, i + dataStartCol, dataStartRow, i + dataStartCol].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); } else if (mergeRowCount >= 1 && !string.IsNullOrEmpty(exportSetting.ExportColumns[i].MergeText)) { var mergeList = exportSetting.MergeList.Where(o => o.Key == exportSetting.ExportColumns[i].MergeText)?.ToList(); if (mergeList != null && mergeList.Count > 0) { sheet.Cells[dataStartRow - mergeRowCount, i + dataStartCol, dataStartRow - mergeRowCount, i + dataStartCol + mergeList.First().Value - 1].Merge = true; sheet.Cells[dataStartRow - mergeRowCount, i + dataStartCol].Value = exportSetting.ExportColumns[i].MergeText; sheet.Cells[dataStartRow - mergeRowCount, i + dataStartCol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Cells[dataStartRow - mergeRowCount, i + dataStartCol].Style.VerticalAlignment = ExcelVerticalAlignment.Center; sheet.Cells[dataStartRow - mergeRowCount, i + dataStartCol].Style.Font.Bold = exportSetting.ExportStyle.IsHeadBold; if (exportSetting.ExportStyle.HeadFontSize > 0) { sheet.Cells[dataStartRow - mergeRowCount, i + dataStartCol].Style.Font.Size = exportSetting.ExportStyle.HeadFontSize; } sheet.Cells[dataStartRow - mergeRowCount, i + dataStartCol].Style.Fill.PatternType = ExcelFillStyle.Solid;; sheet.Cells[dataStartRow - mergeRowCount, i + dataStartCol].Style.Fill.BackgroundColor.SetColor(headColor); sheet.Cells[dataStartRow - mergeRowCount, i + dataStartCol, dataStartRow - mergeRowCount, i + dataStartCol + mergeList.First().Value - 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); exportSetting.MergeList.Remove(mergeList.First()); } sheet.Cells[dataStartRow, i + dataStartCol].Value = exportSetting.ExportColumns[i].Text; sheet.Cells[dataStartRow, i + dataStartCol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Cells[dataStartRow, i + dataStartCol].Style.VerticalAlignment = ExcelVerticalAlignment.Center; sheet.Cells[dataStartRow, i + dataStartCol].Style.Font.Bold = exportSetting.ExportStyle.IsHeadBold; if (exportSetting.ExportStyle.HeadFontSize > 0) { sheet.Cells[dataStartRow, i + dataStartCol].Style.Font.Size = exportSetting.ExportStyle.HeadFontSize; } sheet.Cells[dataStartRow, i + dataStartCol].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells[dataStartRow, i + dataStartCol].Style.Fill.BackgroundColor.SetColor(headColor); sheet.Cells[dataStartRow, i + dataStartCol].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); } else { sheet.Cells[dataStartRow, i + dataStartCol].Value = exportSetting.ExportColumns[i].Text; sheet.Cells[dataStartRow, i + dataStartCol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Cells[dataStartRow, i + dataStartCol].Style.VerticalAlignment = ExcelVerticalAlignment.Center; sheet.Cells[dataStartRow, i + dataStartCol].Style.Font.Bold = exportSetting.ExportStyle.IsHeadBold; if (exportSetting.ExportStyle.HeadFontSize > 0) { sheet.Cells[dataStartRow, i + dataStartCol].Style.Font.Size = exportSetting.ExportStyle.HeadFontSize; } sheet.Cells[dataStartRow, i + dataStartCol].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells[dataStartRow, i + dataStartCol].Style.Fill.BackgroundColor.SetColor(headColor); sheet.Cells[dataStartRow, i + dataStartCol].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); } int j = 0; foreach (var item in list) { object value = null; PropertyInfo propertyInfo = type.GetProperty(exportSetting.ExportColumns[i].Value); value = propertyInfo.GetValue(item, null); if (value != null && value is string) { value = ((string)value).TrimStart(exportSetting.ExportColumns[i].Prefix.ToArray()).TrimEnd(exportSetting.ExportColumns[i].Suffix.ToArray()); } //try //{ // PropertyInfo propertyInfo = type.GetProperty(exportSetting.ExportColumns[i].Value); // value = propertyInfo.GetValue(item, null); //} //catch (Exception ex) //{ // value = ""; //} if (value != null && value.ToString() != "") { value = exportSetting.ExportColumns[i].Prefix + value + exportSetting.ExportColumns[i].Suffix; } sheet.Cells[j + dataStartRow + 1, i + dataStartCol].Value = value ?? ""; sheet.Cells[j + dataStartRow + 1, i + dataStartCol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Cells[j + dataStartRow + 1, i + dataStartCol].Style.VerticalAlignment = ExcelVerticalAlignment.Center; sheet.Cells[j + dataStartRow + 1, i + dataStartCol].Style.Font.Bold = exportSetting.ExportStyle.IsContentBold; if (exportSetting.ExportStyle.ContentFontSize > 0) { sheet.Cells[j + dataStartRow + 1, i + dataStartCol].Style.Font.Size = exportSetting.ExportStyle.ContentFontSize; } sheet.Cells[j + dataStartRow + 1, i + dataStartCol].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells[j + dataStartRow + 1, i + dataStartCol].Style.Fill.BackgroundColor.SetColor(contentColor); sheet.Cells[j + dataStartRow + 1, i + dataStartCol].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); j++; } sheet.Column(i + 1).AutoFit(); } }