GIS_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(string path, DataGridView[] dgvs, string fontname, short fontsize)
		{
			var msg = string.Empty;
			foreach (var dgv in dgvs)
			{
				var name = string.Empty;
				if (dgv.DataSource is DataTable dt)
				{
					name = dt.TableName;
				}
				else
				{
					name = dgv.Name + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff");
				}
				name = name.Replace("#", "-");
				name += ".xls";
				msg += ExportExcel(path + "\\" + name, dgv, fontname, fontsize);
				msg += Environment.NewLine;
			}
			return msg;
		}
		#region  NPOI DataGridView 导出 EXCEL
		/// <summary>
		///  NPOI DataGridView 导出 EXCEL
		/// <para>03版Excel-xls最大行数是65536行,最大列数是256列</para> 
		///  <para> 07版Excel-xlsx最大行数是1048576行,最大列数是16384列</para> 
		/// </summary>
		/// <param name="fileName">默认保存文件名</param>
		/// <param name="dgv">DataGridView</param>
		/// <param name="fontname">字体名称</param>
		/// <param name="fontsize">字体大小</param>
		public static string ExportExcel(string fileName, DataGridView dgv, string fontname = "雅黑", short fontsize = 12)
		{
			IWorkbook workbook;
			ISheet sheet;

			//判断datagridview中内容是否为空
			if (dgv.Rows.Count == 0)
			{
				return ("DataGridView中内容为空!");
			}
			//保存文件
			string saveFileName = "";
			SaveFileDialog saveFileDialog = new SaveFileDialog
			{
				DefaultExt = "xls",
				Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx",
				RestoreDirectory = true,
				Title = "Excel文件保存路径",
				FileName = fileName
			};
			MemoryStream ms = new MemoryStream(); //MemoryStream
			if (string.IsNullOrEmpty(fileName))
			{
				if (saveFileDialog.ShowDialog() == DialogResult.OK)
				{
					saveFileName = saveFileDialog.FileName;
				}
				else
				{
					ms.Close();
					ms.Dispose();
					return CANCELRESULTSTRING;
				}
			}
			else
			{
				saveFileName = fileName;
			}
			//**程序开始计时**//
			Stopwatch sw = new Stopwatch();
			sw.Start();

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

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

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

			//设置单元格样式
			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 + " 导出成功\n耗时" + totalTime + "s");
			return string.Empty;
		}
		#endregion


		#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;
			}
			IntPtr vHandle = Lopen(FileNames, OF_READWRITE | OF_SHARE_DENY_NONE);
			if (vHandle == HFILE_ERROR)
			{
				//文件被占用
				return false;
			}
			//文件没被占用
			CloseHandle(vHandle);
			return true;
		}
		#endregion
	}
}