Winform中使用NPOI实现Excel导入并赋值给DataTable

た入场券 2024-04-17 06:00 26阅读 0赞

## 场景 ##

首先打开一个excel文件，然后获取其路径，获取第一行作为DataTable的标题栏。

然后从第二行到最后一行作为显示的数据。

### 参考Excel的导出 ###

[https://blog.csdn.net/BADAO\_LIUMANG\_QIZHI/article/details/100063457][https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_100063457]

### NPOI相关Lib中各种dll文件下载 ###

[https://download.csdn.net/download/badao\_liumang\_qizhi/11608830][https_download.csdn.net_download_badao_liumang_qizhi_11608830]

### 示例代码下载 ###

[https://download.csdn.net/download/badao\_liumang\_qizhi/11612098][https_download.csdn.net_download_badao_liumang_qizhi_11612098]

## 实现 ##

在上面进行Excel的导出时，只添加了NPOI的引用，在进行导入时还要添加如下引用。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70][]

然后添加一个按钮，用来打开要导入的excel文件。

在其点击事件中

private void button3_Click(object sender, EventArgs e)
            {
    
    
                OpenFileDialog fileDialog = new OpenFileDialog();
                fileDialog.Multiselect = true;
                fileDialog.Title = "请选择文件";
                fileDialog.Filter = "所有文件(*xls*)|*.xls*"; //设置要选择的文件的类型
                if (fileDialog.ShowDialog() == DialogResult.OK)
                {
                    localFilePath = fileDialog.FileName;//返回文件的完整路径               
                }
               
                MessageBox.Show("打开成功要导入的文件路径为："+localFilePath);
               
            }

其中localFilePath 是全局变量用来存储要导入的文件路径。

string localFilePath = "";

要导入的文件内容

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1][]

### 效果 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 2][]

然后在下面导入Excel的按钮的点击事件中

private void button2_Click(object sender, EventArgs e)
            {
                ISheet sheet = null;
                DataTable data = new DataTable();
                IWorkbook workbook = null;
                int startRow = 0;
                try
                {
    
                    //获取文件名，不带路径
                    string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1);
                    //以文件流的形式打开文件
                    fs = new FileStream(localFilePath, FileMode.Open, FileAccess.Read);
                    // 2007版本
                    if (fileNameExt.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本
                    else if (fileNameExt.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);
    
                    if (fileNameExt != null)
                    {
                        sheet = workbook.GetSheet("sheet1");
                        //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                        if (sheet == null)
                        {
                            sheet = workbook.GetSheetAt(0);
                        }
                    }
                    else
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    if (sheet != null)
                    {
                        IRow firstRow = sheet.GetRow(0);
                        //第一行最后一个cell的编号 即第一行总的列数
                        int cellCount = firstRow.LastCellNum;
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                ICell cell = firstRow.GetCell(i);
                                if (cell != null)
                                {
                                    string cellValue = cell.StringCellValue;
                                    if (!string.IsNullOrEmpty(cellValue))
                                    {
                                    //设置DataTable的第一行的显示内容即标题行
                                        DataColumn column = new DataColumn(cellValue);
                                        data.Columns.Add(column);
                                    }
                                }
                            }
    
                         //excel表格中的第一行作为dataTable的标题
                         //所以从第二行开始
                        startRow = sheet.FirstRowNum+1;
                        //获取最后一行的标号
                        int rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            IRow row = sheet.GetRow(i);
                            //没有数据的行默认是null　　
                            if (row == null) continue; 　　　　　
    
                            DataRow dataRow = data.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                //同理，没有数据的单元格都默认是null
                                if (row.GetCell(j) != null)
                                    dataRow[j] = row.GetCell(j).ToString();
                            }
    
                            data.Rows.Add(dataRow);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                  
                }
                //设置数据源
                this.dataGridView1.DataSource = data;
            }

### 效果 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 3][]

[https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_100063457]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/100063457
[https_download.csdn.net_download_badao_liumang_qizhi_11608830]: https://download.csdn.net/download/badao_liumang_qizhi/11608830
[https_download.csdn.net_download_badao_liumang_qizhi_11612098]: https://download.csdn.net/download/badao_liumang_qizhi/11612098
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/8ddd3240fea04ecfab79ee8f9b92562d.jpg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/45917b5ef92f42a3981db0acb3759126.jpg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/71f3ab9312f04e5b8ba05ffe46152a20.jpg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/ee647b3597ba43a09ec9961e33576b3b.jpg