VS2019 C# NPOI导入导出Excel表格

71 阅读 0 评论 47 点赞

我是靠谱客的博主从容未来，最近开发中收集的这篇文章主要介绍VS2019 C# NPOI导入导出Excel表格，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

根据Visual Studio版本安装NuGet.Tools

网址：https://www.nuget.org/downloads

2.工具→NuGet包管理器→程序包管理器控制台

标题3.输入"Install-Package NPOI"，回车

4.此时会发现项目下的“引用”多了NPOI相关引用

从数据库导出表格

	class NPIOExcel
    {   /// <summary>
    /// 导出表格
    /// </summary>
    /// <param name="data">要导出的表格</param>
    /// <param name="fileName">导出文件名带路径</param>
        public static  void OutPutExcel(DataTable data,string fileName)
        {
            string sheetName = "测试数据";
            //创建一个工作簿对象
            IWorkbook workBook = new HSSFWorkbook();
            //创建一个工作表的实例
           
            ISheet sheet = string.IsNullOrEmpty(sheetName) ? workBook.CreateSheet("sheet1") : workBook.CreateSheet(sheetName);
            int rowIndex = 0;
            if (data.Columns.Count > 0)
            {
             
                IRow header = sheet.GetRow(rowIndex); //创建第一行
                                                      //设置列名
                if (header == null)
                {
                    header = sheet.CreateRow(rowIndex);
                }
                for (int i = 0; i < data.Columns.Count; i++)
                {
                    ICell cell = header.CreateCell(i); //创建单元格
                    cell.SetCellValue(data.Columns[i].ColumnName);//设置单元格的值
                }
            }
            //添加数据
            if (data.Rows.Count > 0)
            {
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    rowIndex++;
                    IRow row = sheet.CreateRow(rowIndex);
                    for (int j = 0; j < data.Columns.Count; j++)
                    {
                        ICell cell = row.CreateCell(j);//创建单元格
                        cell.SetCellValue(data.Rows[i][j].ToString()); //设置值

                    }
                }
            }
            for (int i = 0; i < data.Rows.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                workBook.Write(fs);   //写入指定的路径 创建excel文件
            }
        }

Excel某sheet中内容导入到DataTable中，在通过DataTable批量导入数据库

  /// <summary>
        /// Excel某sheet中内容导入到DataTable中
        /// 区分xsl和xslx分别处理
        /// </summary>
        /// <param name="filePath">Excel文件路径,含文件全名</param>
        /// <returns></returns>
        public static DataTable ExcelSheetImportToDataTable(string filePath)
        {
            DataTable dt = new DataTable();
            IWorkbook hssfworkbook;
            if (Path.GetExtension(filePath).ToLower() == ".xls".ToLower())
            {//.xls
                #region .xls文件处理:HSSFWorkbook
                try
                {
                    using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }

                //ISheet sheet = hssfworkbook.GetSheet(sheetName);//Excel的名称
                ISheet sheet = hssfworkbook.GetSheetAt(0);

                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);  //第一行也就是字段名

                //第二行最后一个方格的编号 即总的列数
                for (int j = 0; j < (sheet.GetRow(1).LastCellNum); j++)
                {
                    //设置每一列的名字
                    HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
                    dt.Columns.Add(cell.ToString());
                }

                while (rows.MoveNext())
                {
                    IRow row = (HSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();

                    if (row.RowNum == 0) continue;//第一行为标题，不需要导入

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        if (i >= dt.Columns.Count)//每条记录的单元格数量不能大于表格栏位数量
                        {
                            break;
                        }

                        ICell cell = row.GetCell(i);

                        if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
                        {
                            break;
                        }

                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }

                    dt.Rows.Add(dr);
                }
                #endregion
            }
            else
            {//.xlsx
                #region .xlsx文件处理:XSSFWorkbook
                try
                {
                    using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = new XSSFWorkbook(file);
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }

                //ISheet sheet = hssfworkbook.GetSheet(sheetName);//Excel的名称
                ISheet sheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                XSSFRow headerRow = (XSSFRow)sheet.GetRow(1);

                //一行最后一个方格的编号 即总的列数 
                for (int j = 0; j < (sheet.GetRow(1).LastCellNum); j++)
                {
                    //设置每一列的名字
                    XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
                    dt.Columns.Add(cell.ToString());
                }
                while (rows.MoveNext())
                {
                    IRow row = (XSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();

                    if (row.RowNum == 0) continue;//第一行标题，不需要导入

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        if (i >= dt.Columns.Count) //每条记录的单元格数量不能大于表格栏位数量
                        {
                            break;
                        }
                        ICell cell = row.GetCell(i);
                        if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
                        {
                            break;
                        }
                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
                #endregion
            }
            return dt;
        }

    }

批量插入数据库方法

 #region     SqlBulkCopyByDatatable 表中资料批量插入到数据库
        /// <summary>
        /// 将表中资料批量插入到数据库
        /// 转自：
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="TableName"></param>
        /// <param name="dt"></param>
       public  static void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlBulkCopy sqlbulkcopy =
    new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                        }
                        sqlbulkcopy.WriteToServer(dt);
                    }
                    catch (System.Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

        #endregion