NPOI导入excel文件为DataTable，使用SqlBulkCopy添加到数据库表

public DataTable ExcelToDataTable(Stream stream, string fileName)
        {
            DataTable data = new DataTable();
            try
            {
                IWorkbook workbook = null;
                if (fileName.IndexOf(".xlsx") > 0)
                    workbook = new XSSFWorkbook(stream);
                else if (fileName.IndexOf(".xls") > 0)
                    workbook = new HSSFWorkbook(stream);
                //sheet = workbook.GetSheet(sheetName);
                ISheet sheet = workbook.GetSheetAt(0);
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    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 (cellValue != null)
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }

                    int startRow = sheet.FirstRowNum + 1;
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue;

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null)
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
            catch
            {
                return null;
            }
        }

using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data source=.; Database=Test; UID=sa; password=123;"))
                    {
                        conn.Open();
                        using (System.Data.SqlClient.SqlBulkCopy bulk = new System.Data.SqlClient.SqlBulkCopy(conn))
                        {
                            bulk.DestinationTableName = "Import";
                            bulk.BatchSize = dt.Rows.Count;
                            bulk.ColumnMappings.Add("ID", "ID");
                            bulk.ColumnMappings.Add("Sex", "Sex");
                            bulk.ColumnMappings.Add("Name", "Name");
                            bulk.ColumnMappings.Add("Age", "Age");
                            bulk.WriteToServer(dt);
                        }
                    }

表格数据（导入到Test数据库，Import表）：

 

转载于:https://www.cnblogs.com/talentzemin/p/6816778.html