• c#使用NPOI导出到excel


    1、添加程序包。

    在项目名右键。

    选择管理NuGet程序包,浏览处搜索NPOI并安装。

    2、代码引用。

    using System.IO;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using NPOI.XSSF.UserModel;

    3、导出Excel方法:

    public void ExportDataToExcel(DataTable TableName, string FileName)
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                //设置文件标题
                saveFileDialog.Title = "导出Excel文件";
                //设置文件类型
                saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls";
                //设置默认文件类型显示顺序  
                saveFileDialog.FilterIndex = 1;
                //是否自动在文件名中添加扩展名
                saveFileDialog.AddExtension = true;
                //是否记忆上次打开的目录
                saveFileDialog.RestoreDirectory = true;
                //设置默认文件名
                saveFileDialog.FileName = FileName;
                //按下确定选择的按钮  
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    //获得文件路径 
                    string localFilePath = saveFileDialog.FileName.ToString();
    
                    //数据初始化
                    int TotalCount;     //总行数
                    int RowRead = 0;    //已读行数
                    int Percent = 0;    //百分比
    
                    TotalCount = TableName.Rows.Count;
                    lblStatus.Text = "共有" + TotalCount + "条数据";
                    lblStatus.Visible = true;
                    barStatus.Visible = true;
    
                    //NPOI
                    IWorkbook workbook;
                    string FileExt = Path.GetExtension(localFilePath).ToLower();
                    if (FileExt == ".xlsx")
                    {
                        workbook = new XSSFWorkbook();
                    }
                    else if (FileExt == ".xls")
                    {
                        workbook = new HSSFWorkbook();
                    }
                    else
                    {
                        workbook = null;
                    }
                    if (workbook == null)
                    {
                        return;
                    }
                    ISheet sheet = string.IsNullOrEmpty(FileName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(FileName);
    
    
                    //秒钟
                    Stopwatch timer = new Stopwatch();
                    timer.Start();
    
                    try
                    {
                        //读取标题  
                        IRow rowHeader = sheet.CreateRow(0);
                        for (int i = 0; i < TableName.Columns.Count; i++)
                        {
                            ICell cell = rowHeader.CreateCell(i);
                            cell.SetCellValue(TableName.Columns[i].ColumnName);
                        }
    
                        //读取数据  
                        for (int i = 0; i < TableName.Rows.Count; i++)
                        {
                            IRow rowData = sheet.CreateRow(i + 1);
                            for (int j = 0; j < TableName.Columns.Count; j++)
                            {
                                ICell cell = rowData.CreateCell(j);
                                cell.SetCellValue(TableName.Rows[i][j].ToString());
                            }
                            //状态栏显示
                            RowRead++;
                            Percent = (int)(100 * RowRead / TotalCount);
                            barStatus.Maximum = TotalCount;
                            barStatus.Value = RowRead;
                            lblStatus.Text = "共有" + TotalCount + "条数据,已读取" + Percent.ToString() + "%的数据。";
                            Application.DoEvents();
                        }
    
                        //状态栏更改
                        lblStatus.Text = "正在生成Excel...";
                        Application.DoEvents();
    
                        //转为字节数组  
                        MemoryStream stream = new MemoryStream();
                        workbook.Write(stream);
                        var buf = stream.ToArray();
    
                        //保存为Excel文件  
                        using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
                        {
                            fs.Write(buf, 0, buf.Length);
                            fs.Flush();
                            fs.Close();
                        }
    
                        //状态栏更改
                        lblStatus.Text = "生成Excel成功,共耗时" + timer.ElapsedMilliseconds + "毫秒。";
                        Application.DoEvents();
                        
                        //关闭秒钟
                        timer.Reset();
                        timer.Stop();
    
                        //成功提示
                        if (MessageBox.Show("导出成功,是否立即打开?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                        {
                            System.Diagnostics.Process.Start(localFilePath);
                        }
    
                        //赋初始值
                        lblStatus.Visible = false;
                        barStatus.Visible = false;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    finally
                    {
                        //关闭秒钟
                        timer.Reset();
                        timer.Stop();
                        //赋初始值
                        lblStatus.Visible = false;
                        barStatus.Visible = false;
                    }
                }
            }

    4、结果演示:

    转 : https://www.cnblogs.com/zxh1314/p/8342066.html

    https://www.cnblogs.com/lazyneal/p/6148912.html

  • 相关阅读:
    Xenserver中SR、VBD和VDI之间的关系
    rabbitmq镜像模式设置策略以及高可用
    MySQL主从复制配置详解
    xen 虚拟机挂了,宿主机假死的问题追终,全思路
    XenServer中备份正在运行的虚拟机
    理解 Docker 容器退出码
    prometheus 监控之 进程监控(processexporter)
    XenServer 常见故障处理
    httpCurl封装
    工作中不要为了用系统而用系统
  • 原文地址:https://www.cnblogs.com/fps2tao/p/14912047.html
Copyright © 2020-2023  润新知