C# NOPI 读写 Excel，并插入图片，VS2019

╰+哭是因爲堅強的太久メ 2023-10-09 13:04 20阅读 0赞

#### C\#读写 Excel VS2019 ####

*  一、利用NOPI读写Excel，通过Nuget安装NOPI
 *   *  1.引入包
 *  二、新建工程
 *   *  1.布局设置
     *  2.导出按钮代码：
     *  3.导入按钮代码：
     *  4.Excel 类
     *  5.图片类型转换类
 *  三、程序完成，启动后测试如下：
 *   *  1.操作说明
     *  2.导出后效果如图

## 一、利用NOPI读写Excel，通过Nuget安装NOPI ##

### 1.引入包 ###

1.打开Nuget，工具->NuGet包管理工具->管理解决方案的NuGet程序包  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70]  
2.搜索NOPI，并安装  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 1]  
3.安装完成  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 2]

## 二、新建工程 ##

### 1.布局设置 ###

添加panel、dataGridView、按钮等控件，并为datagridview添加任意列，页面简单布局如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 3]

### 2.导出按钮代码： ###

String filePath = "";
    			//设置保存文件路径
                SaveFileDialog dailog = new SaveFileDialog();
                //设置文件类型 
                dailog.Filter = ".*|*.*|数据文件（*.mdf）|*.mdf|日志文件（*.ldf）|*.ldf";
                //设置默认文件类型显示顺序 
                dailog.FilterIndex = 1;
                //保存对话框是否记忆上次打开的目录 
                dailog.RestoreDirectory = true;
                //点了保存按钮进入 
                if (dailog.ShowDialog() == DialogResult.OK)
                {
        
                    filePath = dailog.FileName.ToString(); //获得文件路径 
                }
                else
                {
        
                    return;
                }
    
                //把datagridview转成datatable
                DataTable dt = new DataTable();
                dt.Clear();
                foreach (DataGridViewColumn headerCell in dataGridView1.Columns)
                {
        
                    dt.Columns.Add(headerCell.HeaderText);
                }
                foreach (DataGridViewRow item in dataGridView1.Rows)
                {
        
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
        
                        if (item.Cells[i].Value != null)
                        {
        
                            dr[i] = item.Cells[i].Value.ToString();
                        }
                    }            
                    dt.Rows.Add(dr);
                }
                Npoi_Excel.Export(dt, filePath);
                System.Diagnostics.Process.Start("explorer.exe", filePath);

### 3.导入按钮代码： ###

String filePath = "";
                OpenFileDialog dialog = new OpenFileDialog();
                //表示可以多选
                dialog.Multiselect = true;
                dialog.Filter = ".*|*.*";//过滤选项设置，文本文件，所有文件。
                dialog.FilterIndex = 1;//当前使用第二个过滤字符串
                dialog.RestoreDirectory = true;//对话框关闭时恢复原目录
                dialog.Title = "请选择文件";
                if (dialog.ShowDialog() == DialogResult.OK)
                {
        
                    for (int i = 1; i <= dialog.FileName.Length; i++)
                    {
        
                        if (dialog.FileName.Substring(dialog.FileName.Length - i, 1).Equals(@"\"))
                        {
                            //更改默认路径为最近打开路径
                            filePath = dialog.FileName;
                        }
                    }
                }
                else
                {
                    return;
                }
    
                DataTable dt = new DataTable();
                dt = Npoi_Excel.Import(filePath,0);
                if (dt != null)
                {
                    MessageBox.Show("122");
                    //dataGridView1.DataSource = table;//通常只写这一句就可以了，但根据你的要求，用下面的循环也可以实现相同功能。
                    dataGridView1.Columns.Clear();//清空列
                    foreach (DataColumn column in dt.Columns)
                    {
        
                        //为datagridview添加列，第一个参数是列名，第二个参数是列标题
                        dataGridView1.Columns.Add(column.ColumnName, column.ColumnName);
                    }
                    dataGridView1.Rows.Clear();//清空行
                    foreach (DataRow line in dt.Rows)
                    {
        
                        //因为列已经一致了，所以直接将datatable的行转成数组就可以添加到datagridview中了
                        dataGridView1.Rows.Add(line.ItemArray);
                    }
                }

### 4.Excel 类 ###

新建一个导入、导出Excel类Npoi\_Excel

using NPOI.HSSF.UserModel;
    using NPOI.HSSF.Util;
    using NPOI.SS.UserModel;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace Learning_Test
    {
        
        class Npoi_Excel
        {
        
    
            /// <summary>
            /// 导出表格数据到 xls
            /// </summary>
            /// <param name="dt"></param>
            /// <param name="filePath"></param>
            public static void Export(DataTable dt, string filePath)
            {
        
                if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
                {
        
                    HSSFWorkbook book = new HSSFWorkbook();
                    ISheet sheet = book.CreateSheet("Test");
    
                    sheet.DefaultColumnWidth = 30;
                    sheet.DefaultRowHeight = 1400;
    
                    IRow row = sheet.CreateRow(0);
    
                    
                    //设置单元格样式
                    ICellStyle style = book.CreateCellStyle();//创建单元格样式对象
                    IFont font = book.CreateFont(); //创建字体样式对象
                    //font.FontName = "方正舒体"; //和excel里面的字体对应
                    //font.IsItalic = true; //斜体
                    font.FontHeightInPoints = 16;//字体大小
                    //font.Boldweight = short.MaxValue;//字体加粗
                    style.SetFont(font); //将字体样式赋给样式对象
                    style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    style.VerticalAlignment = VerticalAlignment.Center;
    
    
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
        
                        ICell cell = row.CreateCell(i);
                        cell.SetCellValue(dt.Columns[i].ColumnName);
                        cell.CellStyle = style;
                    }
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
        
                        row = sheet.CreateRow(i + 1);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
        
                            if (Convert.ToString(dt.Rows[i][j]).StartsWith("http"))
                            {
        
                                String url = Convert.ToString(dt.Rows[i][j]);
                                byte[] bytes = Bitmap_To_Byte.Url_To_Byte(url);
                                row.CreateCell(j);
    
                                                           
                                //第二步：将图片添加到workbook中  指定图片格式 返回图片所在workbook->Picture数组中的索引地址（从1开始）
                                int pictureIdx = book.AddPicture(bytes, PictureType.JPEG);
                                //第三步：在sheet中创建画部
                                IDrawing patriarch = sheet.CreateDrawingPatriarch();
                                //第四步：设置锚点 （在起始单元格的X坐标0-1023，Y的坐标0-255，在终止单元格的X坐标0-1023，Y的坐标0-255，起始			   单元格列数，行数，终止单元格列数，行数）
                                IClientAnchor anchor = patriarch.CreateAnchor(0, 0, 1023, 255, j, i+1, j, i+1);
    
                                //第五步：创建图片
                                IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
    
                                //添加链接
                                //设置单元格的值
                                ICell icell = row.CreateCell(j + 1);
                                icell.SetCellValue("图片");
                                icell.CellStyle = style;
                                //创建URL链接
                                HSSFHyperlink hssfHyperlink = new HSSFHyperlink(HyperlinkType.Url){
        Address = (Convert.ToString(dt.Rows[i][j])) };
                                icell.Hyperlink = hssfHyperlink;
    
    
                            }
                            else
                            {
        
                                ICell cell = row.CreateCell(j);
                                cell.SetCellValue(Convert.ToString(dt.Rows[i][j]));
                                cell.CellStyle = style;
                            }                       
                        }
                    }
                    using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                    {
        
                        fs.Seek(0, SeekOrigin.Begin);
                        book.Write(fs);
                    }
                    book = null;
                }
            }
            
            /// <summary>
            /// 从 Xls文件导入数据到 DataTable
            /// </summary>
            /// <param name="filePath"></param>
            /// <param name="SheetName"></param>
            /// <returns></returns>
            public static DataTable Import(string filePath, String SheetName)
            {
        
                HSSFWorkbook hssfworkbook;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
        
                    hssfworkbook = new HSSFWorkbook(file);
                }
                ISheet sheet = hssfworkbook.GetSheet(SheetName);
                if (sheet == null)
                {
        
                    return new DataTable();
                }
    
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                DataTable dt = new DataTable(SheetName);
    
                for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
                {
        
                    ICell cell = sheet.GetRow(0).Cells[j];
                    dt.Columns.Add(cell.ToString());
                }
                rows.MoveNext();
                while (rows.MoveNext())
                {
        
                    HSSFRow row = (HSSFRow)rows.Current;
                    DataRow Row = dt.NewRow();
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
        
                        ICell cell = row.GetCell(i);
                        Row[i] = cell == null ? null : cell.ToString();
                    }
                    dt.Rows.Add(Row);
                }
                hssfworkbook.Close();
                hssfworkbook = null;
                return dt;
            }
    
            /// <summary>
            /// 从 Xls文件导入数据到 DataTable
            /// </summary>
            /// <param name="filePath"></param>
            /// <param name="SheetName"></param>
            /// <returns></returns>
            public static DataTable Import(string filePath, Int32 SheetIndex)
            {
        
                HSSFWorkbook hssfworkbook;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
        
                    hssfworkbook = new HSSFWorkbook(file);
                }
                ISheet sheet = hssfworkbook.GetSheetAt(SheetIndex);
                String SheetName = hssfworkbook.GetSheetName(SheetIndex);
                if (sheet == null)
                {
        
                    return new DataTable();
                }
    
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                DataTable dt = new DataTable(SheetName);
    
                for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
                {
        
                    ICell cell = sheet.GetRow(0).Cells[j];
                    dt.Columns.Add(cell.ToString());
                }
                rows.MoveNext();
                while (rows.MoveNext())
                {
        
                    HSSFRow row = (HSSFRow)rows.Current;
                    DataRow Row = dt.NewRow();
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
        
                        ICell cell = row.GetCell(i);
                        Row[i] = cell == null ? null : cell.ToString();
                    }
                    dt.Rows.Add(Row);
                }
                hssfworkbook.Close();
                hssfworkbook = null;
                return dt;
            }
    
        }
    }

### 5.图片类型转换类 ###

新建一个将网络图片转为byte数据的类Bitmap\_To\_Byte

using System;
    using System.Collections.Generic;
    using System.Drawing;
    using System.Drawing.Imaging;
    using System.IO;
    using System.Linq;
    using System.Net;
    using System.Security.Policy;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace Learning_Test
    {
        
        class Bitmap_To_Byte
        {
        
            public static byte[] Url_To_Byte(String filePath)
            {
        
                //第一步：读取图片到byte数组
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(filePath);
    
                byte[] bytes;
                using (Stream stream = request.GetResponse().GetResponseStream())
                {
        
                    using (MemoryStream mstream = new MemoryStream())
                    {
        
                        int count = 0;
                        byte[] buffer = new byte[1024];
                        int readNum = 0;
                        while ((readNum = stream.Read(buffer, 0,1024)) > 0)
                        {
        
                            count = count + readNum;
                            mstream.Write(buffer, 0, readNum);
                        }
                        mstream.Position = 0;
                        using (BinaryReader br = new BinaryReader(mstream))
                        {
        
                            bytes = br.ReadBytes(count);
                        }
                    }
                }
                return bytes;
    
    
            }
    
    
    
    
            public static byte[] BitmapByte(Bitmap bitmap)
            {
        
                //Bitmap bitmap = new Bitmap();
                using (MemoryStream stream = new MemoryStream())
                {
        
                    bitmap.Save(stream, ImageFormat.Jpeg);
                    byte[] data = new byte[stream.Length];
                    stream.Seek(0, SeekOrigin.Begin);
                    stream.Read(data, 0, Convert.ToInt32(stream.Length));
                    return data;
                }
            }
    
    
            
    
        }
    }

## 三、程序完成，启动后测试如下： ##

### 1.操作说明 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 4]

### 2.导出后效果如图 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 5]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190928235422402.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190928235727864.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190929000415844.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2019100611455128.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191006122413800.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191006122532564.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70