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                            
    }
}