Excel工作簿导入数据库

#region 导入
/// <summary>
/// 读取excel 表格
/// </summary>
/// <param name="fileName"></param>
/// <param name="sheetName">Sheet名称</param>
/// <param name="isFirstRowColumn">第一行是否列名</param>
/// <returns>表格</returns>

public static DataTable ReadExcel(string fileName, /*string sheetName,*/ bool isFirstRowColumn)
{
FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read);

//根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
IWorkbook workbook = null;
if (fileName.ToLower().EndsWith("xls"))
{
workbook = new HSSFWorkbook(file);
}
else
{
workbook = new XSSFWorkbook(file);
}

ISheet sheet = workbook.GetSheetAt(0);

int startRow = -1;
DataTable dataTable = new DataTable();
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);

//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;

if (isFirstRowColumn)
{
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);
dataTable.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}

//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);

//没有数据的行默认是null　
if (row == null) continue;

DataRow dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
ICell cell = row.GetCell(j);
if (cell != null)
{
if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
dataRow[j] = cell.DateCellValue.ToString("yyyy/MM/dd HH:mm:ss");
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}

}
dataTable.Rows.Add(dataRow);
}
}

return dataTable;

}

 

------------------------------------------------------------------------------------------

//大批量导入数据库

try
{
using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(conStr))//引用SqlBulkCopy
{
sqlRevdBulkCopy.DestinationTableName = "BomTest";//数据库中对应的表名
sqlRevdBulkCopy.NotifyAfter = data.Rows.Count;//有几行数据
sqlRevdBulkCopy.WriteToServer(data);//数据导入数据库
sqlRevdBulkCopy.Close();//关闭连接
}
return "添加成功";
}
catch (Exception)
{
return "添加失败";
//throw;
}