• NPOI导出excel表格应用


    最近接到一个需求,在原有系统上做二次开发 ,要求导出DataGridView数据到Excel表格中。要求如下:

    1. 兼容所有excel版本;
    2. 导出后excel各列的样式,字段类型不变。

    成型如下:

    具体代码实现如下:

    1. girdview数据绑定
            public Form1()
            {
                InitializeComponent();
                Load += Form1_Load;
            }
           
            /// <summary>
            /// 给DataGridView绑定数据
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            void Form1_Load(object sender, EventArgs e)
            {
                List<Jacket> lst = new List<Jacket>();
                for (int i = 0; i < 3; i++)
                {
                    Jacket j = new Jacket();
                    j.Type = "男款";
                    j.Color = "black";
                    j.Size = "S";
                    j.price = 55.4M + i;
                    lst.Add(j);
                }
    
                for (int i = 0; i < 2; i++)
                {
                    Jacket j = new Jacket();
                    j.Type = "男款";
                    j.Color = "blue";
                    j.Size = "L";
                    j.price = 60.4M + i;
                    lst.Add(j);
                }
    
                for (int i = 0; i < 4; i++)
                {
                    Jacket j = new Jacket();
                    j.Type = "女款";
                    j.Color = "red";
                    j.Size = "S";
                    j.price = 60.4M + i;
                    lst.Add(j);
                }
    
                dataGridView1.DataSource = lst;
            }

      2.导出excel表格

         /// <summary>
            /// 导出操作
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void button1_Click(object sender, EventArgs e)
            {
                ExportExcel("d:\1.xls", dataGridView1, "宋体", 8);
            }
    
            /// <summary>
            /// 导出excel
            /// </summary>
            /// <param name="fileName">导出路径</param>
            /// <param name="dgv">数据grilview</param>
            /// <param name="fontName">字体</param>
            /// <param name="fontSize">大小</param>
            void ExportExcel(string fileName, DataGridView dgv, string fontName, short fontSize)
            {
                //检测是否有数据
                //if (dgv.SelectedRows.Count == 0) return;
                //创建主要对象
                HSSFWorkbook workbook = new HSSFWorkbook();
                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Weight");
                //设置字体,大小,对齐方式
                HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
                HSSFFont font = (HSSFFont)workbook.CreateFont();
                font.FontName = fontName;
                font.FontHeightInPoints = fontSize;
                style.SetFont(font);
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //居中对齐
                //添加表头
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
                for (int i = 0; i < dgv.Columns.Count; i++)
                {
                    dataRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);
                    dataRow.GetCell(i).CellStyle = style;
                }
                //添加列及内容
                for (int i = 0; i < dgv.Rows.Count; i++)
                {
                    dataRow = (HSSFRow)sheet.CreateRow(i + 1);
                    for (int j = 0; j < dgv.Columns.Count; j++)
                    {
                        string ValueType = dgv.Rows[i].Cells[j].Value.GetType().ToString();
                        string Value = dgv.Rows[i].Cells[j].Value.ToString();
                        switch (ValueType)
                        {
                            case "System.String"://字符串类型
                                dataRow.CreateCell(j).SetCellValue(Value);
                                break;
                            case "System.DateTime"://日期类型
                                System.DateTime dateV;
                                System.DateTime.TryParse(Value, out dateV);
                                dataRow.CreateCell(j).SetCellValue(dateV);
                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(Value, out boolV);
                                dataRow.CreateCell(j).SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(Value, out intV);
                                dataRow.CreateCell(j).SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(Value, out doubV);
                                dataRow.CreateCell(j).SetCellValue(doubV);
                                break;
                            case "System.DBNull"://空值处理
                                dataRow.CreateCell(j).SetCellValue("");
                                break;
                            default:
                                dataRow.CreateCell(j).SetCellValue("");
                                break;
                        }
                        dataRow.GetCell(j).CellStyle = style;
                        //设置宽度
                        sheet.SetColumnWidth(j, (Value.Length + 10) * 256);
                    }
                }
                //保存文件
                string saveFileName = "";
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.DefaultExt = "xls";
                saveDialog.Filter = "Excel文件|*.xls";
                saveDialog.FileName = fileName;
                MemoryStream ms = new MemoryStream();
                if (saveDialog.ShowDialog() == DialogResult.OK)
                {
                    saveFileName = saveDialog.FileName;
                    if (!CheckFiles(saveFileName))
                    {
                        MessageBox.Show("文件被站用,请关闭文件 " + saveFileName);
                        workbook = null;
                        ms.Close();
                        ms.Dispose();
                        return;
                    }
                    FileStream file = new FileStream(saveFileName, FileMode.Create);
                    workbook.Write(file);
                    file.Close();
                    MessageBox.Show(fileName + " 保存成功", "提示", MessageBoxButtons.OK);
                }
                else
                {
                    workbook = null;
                    ms.Close();
                    ms.Dispose();
                }
            }
    
            /// <summary>
            /// 检测文件被占用 
            /// </summary>
            /// <param name="FileNames">要检测的文件路径</param>
            /// <returns></returns>
            public bool CheckFiles(string FileNames)
            {
                if (!File.Exists(FileNames))
                {
                    //文件不存在
                    return false;
                }
                else
                {
                    IntPtr vHandle = _lopen(FileNames, OF_READWRITE | OF_SHARE_DENY_NONE);
                    if (vHandle == HFILE_ERROR)
                    {
                        return false;
                    }
                    CloseHandle(vHandle);
                }
                return true;
            }

      3.定义jacket类

        public class Jacket
        {
            public string Type { get; set; }
            public string Color { get; set; }
            public string Size { get; set; }
            public decimal price { get; set; }
        }

      4.判定文件是否打开

            [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 readonly IntPtr HFILE_ERROR = new IntPtr(-1);    
  • 相关阅读:
    pandas DataFrame.shift()函数
    pandas DataFrame applymap()函数
    pandas的qcut()方法
    pandas DataFrame(3)-轴
    pandas DataFrame(4)-向量化运算
    pandas通过皮尔逊积矩线性相关系数(Pearson's r)计算数据相关性
    pandas DataFrame(2)-行列索引及值的获取
    pandas DataFrame(1)
    numpy数组(5)-二维数组的轴
    numpy数组(4)-二维数组
  • 原文地址:https://www.cnblogs.com/tuqun/p/4155400.html
Copyright © 2020-2023  润新知