M071.git - Gitblit

			@@ -4,6 +4,7 @@
			using System.Collections.Generic;
			using System.ComponentModel;
			using System.Data;
			using System.IO;
			using System.Linq;

			namespace Bro.M071.DBManager
			@@ -85,8 +86,9 @@
			{
			var dataTable = excelExportDto.WorksheetDataTable[worksheet];
			ExcelWorksheet workSheet = package.Workbook.Worksheets.Add($"{worksheet}");
			int startRowFrom = string.IsNullOrEmpty(worksheet) ? 1 : 3; //开始的行
			//是否显示行编号
			//int startRowFrom = string.IsNullOrEmpty(worksheet) ? 1 : 3; //开始的行
			int startRowFrom = 1; //开始的行
			//是否显示行编号
			if (showSrNo)
			{
			DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
			@@ -100,19 +102,28 @@
			}
			//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)

			for (int columnIndex = 1; columnIndex <= dataTable.Columns.Count; columnIndex++)
			{
			// 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());
			int maxLength = columnCells.Max(cell => (cell.Value ?? "").ToString().Count());
			if (maxLength < 150)
			{
			workSheet.Column(columnIndex).AutoFit();
			}
			columnIndex++;
			// 设置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"));// 设置填充样式
			}
			}
			}
			// format header - bold, yellow on black
			// 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);
			@@ -132,34 +143,128 @@
			r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
			r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
			}
			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;
			}
			//将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
			///// </summary>
			///// <typeparam name="T"></typeparam>
			///// <param name="data"></param>
			///// <param name="heading"></param>
			///// <param name="isShowSlNo"></param>
			///// <param name="columnsToTake"></param>
			///// <returns></returns>
			//public static byte[] ExportExcel<T>(List<T> data, string heading = "", bool isShowSlNo = false, params string[] columnsToTake)
			//{
			// ExcelExportSet excelExport = new ExcelExportSet();
			// excelExport.
			// return ExportExcel(ListToDataTable(data), heading, isShowSlNo, columnsToTake);
			//}
			/// <summary>
			/// 新建或追加写入Excel
			/// </summary>
			/// <param name="excelExportDto"></param>
			/// <param name="fileName">要写入的文件名(全路径名)</param>
			/// <param name="showSrNo"></param>
			/// <returns></returns>
			public static byte[] CreateOrAppendExcel(ExcelExportSet excelExportDto, string fileName, bool showSrNo = false)
			{
			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 "";
			}
			}

			}
			}