pipeline-cs/WWPipeLine/WWPipeLine.Commons/ExportDgvToExcel.cs

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;

namespace WWPipeLine.Commons
{
	public class ExportDgvToExcel
	{
		/// <summary>
		///  CANCELRESULTSTRING = "Cancel"
		/// </summary>
		public static readonly string CANCELRESULTSTRING = "Cancel";


		public static string ExportExcel(List<DataGridView> dgvs)
		{
			if (dgvs.Count == 0) { return ("内容为空!"); }
			string saveFileName = "";
			SaveFileDialog sfd = new SaveFileDialog
			{
				DefaultExt = "xlsx",
				Filter = "Excel文件(*.xlsx)|*.xlsx",
				RestoreDirectory = true,
				Title = "Excel文件保存路径"
			};
			if (sfd.ShowDialog() == DialogResult.Cancel) return CANCELRESULTSTRING;
			saveFileName = sfd.FileName;
			if (!CheckFiles(saveFileName)) return "文件被占用,请关闭文件";

			MemoryStream ms = new MemoryStream();
			XSSFWorkbook workbook = new XSSFWorkbook();
			if (workbook == null) return "生成IWorkbook失败";
			//设置单元格样式
			ICellStyle cellStyle = workbook.CreateCellStyle();
			//水平居中对齐和垂直居中对齐
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			//设置字体
			IFont font = workbook.CreateFont();
			font.FontName = "雅黑";//字体名称
			font.FontHeightInPoints = 12;//字号
			font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;//字体颜色
			cellStyle.SetFont(font);

			foreach (DataGridView dgv in dgvs.ToArray())
			{
				if (dgv.Rows.Count > 1000000) { return ("行数超过Excel限制!"); }
				string sheetName = "S" + DateTime.Now.Ticks;
				if (dgv.Tag != null)
					sheetName = dgv.Tag.ToString();
				ISheet sheet = workbook.CreateSheet(sheetName);
				//添加列名
				IRow headRow = sheet.CreateRow(0);
				var cIndex = 0;
				Dictionary<int, int> columnsVisiable = new Dictionary<int, int>();
				foreach (DataGridViewColumn column in dgv.Columns)
				{
					if (!column.Visible) continue;
					headRow.CreateCell(cIndex).SetCellValue(column.HeaderText);
					headRow.GetCell(cIndex).CellStyle = cellStyle;
					columnsVisiable.Add(cIndex, dgv.Columns.IndexOf(column));
					cIndex++;
				}
				//根据类型写入内容
				for (int rowNum = 0; rowNum < dgv.Rows.Count; rowNum++)
				{
					//跳过第一行,第一行为列名
					IRow dataRow = sheet.CreateRow(rowNum + 1);
					foreach (int columnIndex in columnsVisiable.Keys)
					{
						int columnNum = columnsVisiable[columnIndex];

						ICell cell = dataRow.CreateCell(columnIndex);
						if (dgv.Rows[rowNum].Cells[columnNum].Value == null)
						{
							cell.SetCellType(CellType.Blank); continue;
						}

						if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Int32"))
						{
							cell.SetCellValue(Convert.ToInt32(dgv.Rows[rowNum].Cells[columnNum].Value));
						}
						else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.String"))
						{
							cell.SetCellValue(dgv.Rows[rowNum].Cells[columnNum].Value.ToString());
						}
						else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Single"))
						{
							cell.SetCellValue(Convert.ToSingle(dgv.Rows[rowNum].Cells[columnNum].Value));
						}
						else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Double"))
						{
							cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));
						}
						else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Decimal"))
						{
							cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));
						}
						else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DateTime"))
						{
							cell.SetCellValue(Convert.ToDateTime(dgv.Rows[rowNum].Cells[columnNum].Value).ToString("yyyy-MM-dd"));
						}
						else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DBNull"))
						{
							cell.SetCellValue("");
						}
						//单元格样式
						dataRow.GetCell(columnIndex).CellStyle = cellStyle;
					}
				}
			}
			if (!ms.CanRead) return string.Empty;
			//保存为Excel文件
			workbook.Write(ms);
			using (FileStream file = new FileStream(saveFileName, FileMode.Create))
			{
				workbook.Write(file);
				file.Close();
			}
			ms.Close();
			ms.Dispose();
			LogHelper.Info("导出成功");
			return string.Empty;
		}


		public static string ExportExcel(DataGridView dgv)
		{
			if (dgv.Rows.Count == 0) { return ("内容为空!"); }
			if (dgv.Rows.Count > 1000000) { return ("行数超过Excel限制!"); }
			string saveFileName = "";
			SaveFileDialog sfd = new SaveFileDialog
			{
				DefaultExt = "xlsx",
				Filter = "Excel文件(*.xlsx)|*.xlsx",
				RestoreDirectory = true,
				Title = "Excel文件保存路径"
			};
			if (sfd.ShowDialog() == DialogResult.Cancel) return CANCELRESULTSTRING;
			saveFileName = sfd.FileName;
			if (!CheckFiles(saveFileName)) return "文件被占用,请关闭文件";

			MemoryStream ms = new MemoryStream();
			XSSFWorkbook workbook = new XSSFWorkbook();
			if (workbook == null) return "生成IWorkbook失败";
			//设置单元格样式
			ICellStyle cellStyle = workbook.CreateCellStyle();
			//水平居中对齐和垂直居中对齐
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			//设置字体
			IFont font = workbook.CreateFont();
			font.FontName = "雅黑";//字体名称
			font.FontHeightInPoints = 12;//字号
			font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;//字体颜色
			cellStyle.SetFont(font);



			string sheetName = "S" + DateTime.Now.Ticks;
			if (dgv.Tag != null)
				sheetName = dgv.Tag.ToString();
			ISheet sheet = workbook.CreateSheet(sheetName);
			//添加列名
			IRow headRow = sheet.CreateRow(0);
			var cIndex = 0;
			Dictionary<int, int> columnsVisiable = new Dictionary<int, int>();
			foreach (DataGridViewColumn column in dgv.Columns)
			{
				if (!column.Visible) continue;
				headRow.CreateCell(cIndex).SetCellValue(column.HeaderText);
				headRow.GetCell(cIndex).CellStyle = cellStyle;
				columnsVisiable.Add(cIndex, dgv.Columns.IndexOf(column));
				cIndex++;
			}
			//根据类型写入内容
			for (int rowNum = 0; rowNum < dgv.Rows.Count; rowNum++)
			{
				//跳过第一行,第一行为列名
				IRow dataRow = sheet.CreateRow(rowNum + 1);
				foreach (int columnIndex in columnsVisiable.Keys)
				{
					int columnNum = columnsVisiable[columnIndex];

					ICell cell = dataRow.CreateCell(columnIndex);
					if (dgv.Rows[rowNum].Cells[columnNum].Value == null)
					{
						cell.SetCellType(CellType.Blank); continue;
					}

					if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Int32"))
					{
						cell.SetCellValue(Convert.ToInt32(dgv.Rows[rowNum].Cells[columnNum].Value));
					}
					else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.String"))
					{
						cell.SetCellValue(dgv.Rows[rowNum].Cells[columnNum].Value.ToString());
					}
					else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Single"))
					{
						cell.SetCellValue(Convert.ToSingle(dgv.Rows[rowNum].Cells[columnNum].Value));
					}
					else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Double"))
					{
						cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));
					}
					else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Decimal"))
					{
						cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));
					}
					else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DateTime"))
					{
						cell.SetCellValue(Convert.ToDateTime(dgv.Rows[rowNum].Cells[columnNum].Value).ToString("yyyy-MM-dd"));
					}
					else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DBNull"))
					{
						cell.SetCellValue("");
					}
					//单元格样式
					dataRow.GetCell(columnIndex).CellStyle = cellStyle;
				}
			}

			if (!ms.CanRead) return string.Empty;
			//保存为Excel文件
			workbook.Write(ms);
			using (FileStream file = new FileStream(saveFileName, FileMode.Create))
			{
				workbook.Write(file);
				file.Close();
			}
			ms.Close();
			ms.Dispose();
			LogHelper.Info("导出成功");
			return string.Empty;
		}


		//public static string ExportExcel(string fileName, DataGridView dgv, string fontname = "雅黑", short fontsize = 12)
		//{
		//	//判断datagridview中内容是否为空
		//	if (dgv.Rows.Count == 0) { return ("DataGridView中内容为空!"); }
		//	//保存文件
		//	string saveFileName = "";
		//	SaveFileDialog sfd = new SaveFileDialog
		//	{
		//		DefaultExt = "xls",
		//		Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx",
		//		RestoreDirectory = true,
		//		Title = "Excel文件保存路径",
		//		FileName = fileName
		//	};

		//	if (sfd.ShowDialog() == DialogResult.OK)
		//	{
		//		saveFileName = sfd.FileName;
		//	}
		//	else
		//	{
		//		//ms.Close();
		//		//ms.Dispose();
		//		return CANCELRESULTSTRING;
		//	}
		//	//if (string.IsNullOrEmpty(fileName))
		//	//{}
		//	//else
		//	//{
		//	//	saveFileName = fileName;
		//	//}
		//	//**程序开始计时**//
		//	Stopwatch sw = new Stopwatch();
		//	sw.Start();

		//	//检测文件是否被占用
		//	if (!CheckFiles(saveFileName))
		//	{
		//		//ms.Close();
		//		//ms.Dispose();
		//		return "文件被占用,请关闭文件";
		//	}
		//	MemoryStream ms = new MemoryStream(); //MemoryStream

		//	//*** 根据扩展名xls和xlsx来创建对象
		//	IWorkbook workbook;
		//	ISheet sheet;
		//	string fileExt = Path.GetExtension(saveFileName).ToLower();
		//	if (fileExt == ".xlsx")
		//	{
		//		workbook = new XSSFWorkbook();
		//	}
		//	else
		//	{
		//		workbook = new HSSFWorkbook();
		//	}
		//	//***

		//	//创建Sheet
		//	if (workbook == null) return "未知错误";
		//	sheet = workbook.CreateSheet("Sheet1");//Sheet的名称  

		//	//设置单元格样式
		//	ICellStyle cellStyle = workbook.CreateCellStyle();
		//	//水平居中对齐和垂直居中对齐
		//	cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
		//	cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
		//	//设置字体
		//	IFont font = workbook.CreateFont();
		//	font.FontName = fontname;//字体名称
		//	font.FontHeightInPoints = fontsize;//字号
		//	font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;//字体颜色
		//	cellStyle.SetFont(font);

		//	//添加列名
		//	IRow headRow = sheet.CreateRow(0);
		//	var cIndex = 0;
		//	Dictionary<int, int> columnsVisiable = new Dictionary<int, int>();
		//	foreach (DataGridViewColumn column in dgv.Columns)
		//	{
		//		if (!column.Visible) continue;
		//		headRow.CreateCell(cIndex).SetCellValue(column.HeaderText);
		//		headRow.GetCell(cIndex).CellStyle = cellStyle;
		//		columnsVisiable.Add(cIndex, dgv.Columns.IndexOf(column));
		//		cIndex++;
		//	}

		//	//根据类型写入内容
		//	for (int rowNum = 0; rowNum < dgv.Rows.Count; rowNum++)
		//	{
		//		///跳过第一行,第一行为列名
		//		IRow dataRow = sheet.CreateRow(rowNum + 1);
		//		foreach (int columnIndex in columnsVisiable.Keys)
		//		{
		//			int columnNum = columnsVisiable[columnIndex];
		//			int columnWidth = sheet.GetColumnWidth(columnNum) / 256; //列宽

		//			//隐藏行列不导出
		//			if (dgv.Rows[rowNum].Visible == true && dgv.Columns[columnNum].Visible == true)
		//			{
		//				//防止行列超出Excel限制
		//				if (fileExt == ".xls")
		//				{
		//					//03版Excel最大行数是65536行,最大列数是256列
		//					if (rowNum > 65536)
		//					{
		//						return ("行数超过Excel限制!");
		//					}
		//					if (columnNum > 256)
		//					{
		//						return ("列数超过Excel限制!");
		//					}
		//				}
		//				else if (fileExt == ".xlsx")
		//				{
		//					//07版Excel最大行数是1048576行,最大列数是16384列
		//					if (rowNum > 1048576)
		//					{
		//						return ("行数超过Excel限制!");
		//					}
		//					if (columnNum > 16384)
		//					{
		//						return ("列数超过Excel限制!");
		//					}
		//				}

		//				ICell cell = dataRow.CreateCell(columnIndex);
		//				if (dgv.Rows[rowNum].Cells[columnNum].Value == null)
		//				{
		//					cell.SetCellType(CellType.Blank);
		//				}
		//				else
		//				{
		//					if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Int32"))
		//					{
		//						cell.SetCellValue(Convert.ToInt32(dgv.Rows[rowNum].Cells[columnNum].Value));
		//					}
		//					else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.String"))
		//					{
		//						cell.SetCellValue(dgv.Rows[rowNum].Cells[columnNum].Value.ToString());
		//					}
		//					else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Single"))
		//					{
		//						cell.SetCellValue(Convert.ToSingle(dgv.Rows[rowNum].Cells[columnNum].Value));
		//					}
		//					else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Double"))
		//					{
		//						cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));
		//					}
		//					else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Decimal"))
		//					{
		//						cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));
		//					}
		//					else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DateTime"))
		//					{
		//						cell.SetCellValue(Convert.ToDateTime(dgv.Rows[rowNum].Cells[columnNum].Value).ToString("yyyy-MM-dd"));
		//					}
		//					else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DBNull"))
		//					{
		//						cell.SetCellValue("");
		//					}
		//				}

		//				//设置列宽
		//				IRow currentRow;
		//				if (sheet.GetRow(rowNum) == null)
		//				{
		//					currentRow = sheet.CreateRow(rowNum);
		//				}
		//				else
		//				{
		//					currentRow = sheet.GetRow(rowNum);
		//				}

		//				if (currentRow.GetCell(columnNum) != null)
		//				{
		//					ICell currentCell = currentRow.GetCell(columnNum);
		//					int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;

		//					if (columnWidth < length)
		//					{
		//						columnWidth = length + 10; //设置列宽数值
		//					}
		//				}
		//				sheet.SetColumnWidth(columnNum, columnWidth * 256);

		//				//单元格样式
		//				dataRow.GetCell(columnIndex).CellStyle = cellStyle;
		//			}
		//		}
		//	}
		//	if (!ms.CanRead) return string.Empty;
		//	//保存为Excel文件                  
		//	workbook.Write(ms);
		//	FileStream file = new FileStream(saveFileName, FileMode.Create);
		//	workbook.Write(file);
		//	file.Close();
		//	ms.Close();
		//	ms.Dispose();

		//	//**程序结束计时**//
		//	sw.Stop();
		//	double totalTime = sw.ElapsedMilliseconds / 1000.0;

		//	LogHelper.Info(fileName + " 导出成功 耗时" + totalTime + "s");
		//	return string.Empty;
		//}



		#region 检测文件是否被占用
		/// <summary>
		/// 判定文件是否打开
		/// </summary>  
		[DllImport("kernel32.dll")]
		public static extern IntPtr Lopen(string lpPathName, int iReadWrite);
		[DllImport("kernel32.dll")]
		public static extern bool CloseHandle(IntPtr hObject);
		public const int OF_READWRITE = 2;
		public const int OF_SHARE_DENY_NONE = 0x40;
		public static readonly IntPtr HFILE_ERROR = new IntPtr(-1);

		/// <summary>
		/// 检测文件被占用
		/// </summary>
		/// <param name="FileNames">要检测的文件路径</param>
		/// <returns></returns>
		public static bool CheckFiles(string FileNames)
		{
			if (!File.Exists(FileNames)) return true;
			try
			{
				IntPtr vHandle = Lopen(FileNames, OF_READWRITE | OF_SHARE_DENY_NONE);
				if (vHandle == HFILE_ERROR)//文件被占用			 			
					return false;
				//文件没被占用
				CloseHandle(vHandle);
			}
			catch { return false; }
			return true;
		}
		#endregion
	}
}