M071.git - Gitblit

			@@ -4,10 +4,34 @@
			using System.Collections.Generic;
			using System.ComponentModel;
			using System.Data;
			using System.IO;
			using System.Linq;

			namespace Bro.M071.DBManager
			{

			public class ExcelExportSet
			{
			public List<string> Worksheets { get; set; } = new List<string>();

			/// <summary>
			/// Key： Worksheet的名称 Value:Worksheet对应的列名集合(key 为要导出的列名 value 为导出后显示的列名)
			/// </summary>
			public Dictionary<string, Dictionary<string, string>> WorksheetColumns { get; set; } = new Dictionary<string, Dictionary<string, string>>();

			public Dictionary<string, Dictionary<string, string>> WorksheetRows { get; set; } = new Dictionary<string, Dictionary<string, string>>();

			public Dictionary<string, DataTable> WorksheetDataTable { get; set; } = new Dictionary<string, DataTable>();

			//public ExcelExportSet()
			//{
			// Worksheets = new List<string>();
			// WorksheetColumns = new Dictionary<string, Dictionary<string, string>>();
			// WorksheetDataTable = new Dictionary<string, DataTable>();
			//}

			}

			/// <summary>
			/// Excel导出帮助类
			/// </summary>
			@@ -20,21 +44,27 @@
			/// <typeparam name="T"></typeparam>
			/// <param name="data"></param>
			/// <returns></returns>
			public static DataTable ListToDataTable<T>(List<T> data)
			public static DataTable ListToDataTable<T>(List<T> data, Dictionary<string, string> worksheetColumns)
			{
			PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
			DataTable dataTable = new DataTable();
			for (int i = 0; i < properties.Count; i++)
			Dictionary<string, string> tempColumns = new Dictionary<string, string>();
			foreach (var column in worksheetColumns)
			{
			PropertyDescriptor property = properties[i];
			dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
			PropertyDescriptor property = properties.Find(column.Key, true);
			if (property != null)
			{
			dataTable.Columns.Add(column.Value, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
			tempColumns[column.Key] = column.Value;
			}
			}
			object[] values = new object[properties.Count];
			object[] values = new object[tempColumns.Count];
			foreach (T item in data)
			{
			for (int i = 0; i < values.Length; i++)
			for (int i = 0; i < tempColumns.Count; i++)
			{
			values[i] = properties[i].GetValue(item);
			PropertyDescriptor property = properties.Find(tempColumns.ElementAt(i).Key, true);
			values[i] = property.GetValue(item);
			}
			dataTable.Rows.Add(values);
			}
			@@ -45,99 +75,200 @@
			/// 导出Excel
			/// </summary>
			/// <param name="dataTable">数据源</param>
			/// <param name="heading">工作簿Worksheet</param>
			/// <param name="worksheet">工作簿Worksheet</param>
			/// <param name="showSrNo">//是否显示行编号</param>
			/// <param name="columnsToTake">要导出的列</param>
			/// <returns></returns>
			public static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake)
			public static byte[] ExportExcel(ExcelExportSet excelExportDto, bool showSrNo = false)
			{
			byte[] result;
			//ExcelPackage.LicenseContext = LicenseContext.Commercial; 5.0以上版本需要授权
			byte[] result = null;
			using (ExcelPackage package = new ExcelPackage())
			{
			ExcelWorksheet workSheet = package.Workbook.Worksheets.Add($"{heading}Data");
			int startRowFrom = string.IsNullOrEmpty(heading) ? 1 : 3; //开始的行
			//是否显示行编号
			if (showSrNo)
			foreach (var worksheet in excelExportDto.Worksheets)
			{
			DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
			dataColumn.SetOrdinal(0);
			int index = 1;
			foreach (DataRow item in dataTable.Rows)
			var dataTable = excelExportDto.WorksheetDataTable[worksheet];
			ExcelWorksheet workSheet = package.Workbook.Worksheets.Add($"{worksheet}");
			//int startRowFrom = string.IsNullOrEmpty(worksheet) ? 1 : 3; //开始的行
			int startRowFrom = 1; //开始的行
			//是否显示行编号
			if (showSrNo)
			{
			item[0] = index;
			index++;
			DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
			dataColumn.SetOrdinal(0);
			int index = 1;
			foreach (DataRow item in dataTable.Rows)
			{
			item[0] = index;
			index++;
			}
			}
			}
			//Add Content Into the Excel File
			workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
			// autofit width of cells with small content
			int columnIndex = 1;
			foreach (DataColumn item in dataTable.Columns)
			{
			ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
			int maxLength = columnCells.Max(cell => cell.Value.ToString().Count());
			if (maxLength < 150)
			//Add Content Into the Excel File
			workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);

			for (int columnIndex = 1; columnIndex <= dataTable.Columns.Count; columnIndex++)
			{
			workSheet.Column(columnIndex).AutoFit();
			// autofit width of cells with small content
			ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
			int maxLength = columnCells.Max(cell => (cell.Value ?? "").ToString().Count());
			if (maxLength < 150)
			{
			workSheet.Column(columnIndex).AutoFit();
			}
			// 设置NG单元格样式
			for (int rowIndex = 1; rowIndex <= dataTable.Rows.Count; rowIndex++)
			{
			if (columnCells[rowIndex, columnIndex].Text == "NG")
			{
			workSheet.Cells[rowIndex, columnIndex].Style.Font.Bold = true;//字体为粗体
			workSheet.Cells[rowIndex, columnIndex].Style.Fill.PatternType = ExcelFillStyle.Solid;
			workSheet.Cells[rowIndex, columnIndex].Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#E33E33"));// 设置填充样式
			}
			}
			}
			columnIndex++;
			}
			// format header - bold, yellow on black
			using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
			{
			r.Style.Font.Color.SetColor(System.Drawing.Color.White);
			r.Style.Font.Bold = true;
			r.Style.Fill.PatternType = ExcelFillStyle.Solid;
			r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
			}
			// format cells - add borders
			using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
			{
			r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
			r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
			r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
			r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
			r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
			r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
			r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
			r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
			}
			// removed ignored columns
			for (int i = dataTable.Columns.Count - 1; i >= 0; i--)
			{
			if (i == 0 && showSrNo)
			// format header - bold, yellow on black 设置表头样式
			using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
			{
			continue;
			r.Style.Font.Color.SetColor(System.Drawing.Color.White);
			r.Style.Font.Bold = true;
			r.Style.Fill.PatternType = ExcelFillStyle.Solid;
			r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
			}
			if (!columnsToTake.Contains(dataTable.Columns[i].ColumnName))
			// format cells - add borders
			using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
			{
			workSheet.DeleteColumn(i + 1);
			r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
			r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
			r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
			r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
			r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
			r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
			r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
			r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
			}
			}
			if (!string.IsNullOrEmpty(heading))
			{
			workSheet.Cells["A1"].Value = heading;
			workSheet.Cells["A1"].Style.Font.Size = 20;
			workSheet.InsertColumn(1, 1);
			workSheet.InsertRow(1, 1);
			workSheet.Column(1).Width = 5;
			//将sheet写入表中
			//if (!string.IsNullOrEmpty(worksheet))
			//{
			// workSheet.Cells["A1"].Value = worksheet;
			// workSheet.Cells["A1"].Style.Font.Size = 20;
			// workSheet.InsertColumn(1, 1);
			// workSheet.InsertRow(1, 1);
			// workSheet.Column(1).Width = 5;
			//}
			}
			result = package.GetAsByteArray();
			}
			return result;
			}

			/// <summary>
			/// 导出Excel
			/// 新建或追加写入Excel
			/// </summary>
			/// <typeparam name="T"></typeparam>
			/// <param name="data"></param>
			/// <param name="heading"></param>
			/// <param name="isShowSlNo"></param>
			/// <param name="columnsToTake"></param>
			/// <param name="excelExportDto"></param>
			/// <param name="fileName">要写入的文件名(全路径名)</param>
			/// <param name="showSrNo"></param>
			/// <returns></returns>
			public static byte[] ExportExcel<T>(List<T> data, string heading = "", bool isShowSlNo = false, params string[] columnsToTake)
			public static byte[] CreateOrAppendExcel(ExcelExportSet excelExportDto, string fileName, bool showSrNo = false)
			{
			return ExportExcel(ListToDataTable(data), heading, isShowSlNo, columnsToTake);
			byte[] result = null;
			bool isExist = File.Exists(fileName);
			ExcelExportSet newExcelSet = new ExcelExportSet();

			if (isExist)
			{
			var oldWorksheetDataTable = WorksheetToTable(fileName, excelExportDto.Worksheets);
			newExcelSet.Worksheets = excelExportDto.Worksheets;
			newExcelSet.WorksheetColumns = excelExportDto.WorksheetColumns;

			foreach (var sheet in excelExportDto.Worksheets)
			{
			var oldTable = oldWorksheetDataTable[sheet];
			newExcelSet.WorksheetDataTable[sheet] = oldTable;
			if (excelExportDto.WorksheetDataTable[sheet].Rows.Count > 0)
			{
			//合并两个 datatable
			newExcelSet.WorksheetDataTable[sheet].Merge(excelExportDto.WorksheetDataTable[sheet]);
			}
			}
			}
			else
			{
			newExcelSet = excelExportDto;
			}

			result = ExportExcel(newExcelSet, showSrNo);
			return result;
			}

			/// <summary>
			///将指定的Excel的文件转换成DataTable （Excel的指定sheet）
			/// </summary>
			/// <param name="fullFielPath">文件的绝对路径</param>
			/// <returns></returns>
			public static Dictionary<string, DataTable> WorksheetToTable(string fullFielPath, List<string> worksheets)
			{
			try
			{
			var resultWorksheetDataTable = new Dictionary<string, DataTable>();
			FileInfo existingFile = new FileInfo(fullFielPath);
			ExcelPackage package = new ExcelPackage(existingFile);
			foreach (var sheet in worksheets)
			{
			ExcelWorksheet worksheet = package.Workbook.Worksheets[sheet];//选定指定页
			var dataTable = WorksheetToTable(worksheet);
			resultWorksheetDataTable[sheet] = dataTable;
			}
			return resultWorksheetDataTable;
			}
			catch (Exception)
			{
			throw;
			}
			}

			/// <summary>
			/// 将worksheet转成datatable
			/// </summary>
			/// <param name="worksheet">待处理的worksheet</param>
			/// <returns>返回处理后的datatable</returns>
			public static DataTable WorksheetToTable(ExcelWorksheet worksheet)
			{
			//获取worksheet的行数
			int rows = worksheet.Dimension.End.Row;
			//获取worksheet的列数
			int cols = worksheet.Dimension.End.Column;

			DataTable dt = new DataTable(worksheet.Name);
			DataRow dr = null;
			for (int i = 1; i <= rows; i++)
			{
			if (i > 1)
			dr = dt.Rows.Add();

			for (int j = 1; j <= cols; j++)
			{
			//默认将第一行设置为datatable的标题
			if (i == 1)
			dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
			//剩下的写入datatable
			else
			dr[j - 1] = GetString(worksheet.Cells[i, j].Value);
			}
			}
			return dt;
			}
			private static string GetString(object obj)
			{
			try
			{
			return (obj ?? "").ToString();
			}
			catch (Exception)
			{
			return "";
			}
			}
			}


			}