///
/// Excel文档导入到数据库
///
/// Excel文档流
/// 插入语句
/// 更新到数据库的方法
/// 表索引号，如第一个表为0
/// 标题行索引号，如第一行为0
///
public static int RenderToDb(Stream excelFileStream, string insertSql, GlobalObject.DelegateCollection.DBAction dbAction, int sheetIndex, int headerRowIndex)
{
int rowAffected = 0;
using (excelFileStream)
{
IWorkbook workbook = new HSSFWorkbook(excelFileStream);
ISheet sheet = workbook.GetSheetAt(sheetIndex);
StringBuilder builder = new StringBuilder();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
builder.Append(insertSql);
builder.Append(" values (");
for (int j = row.FirstCellNum; j < cellCount; j++)
{
builder.AppendFormat("'{0}',", GetCellValue(row.GetCell(j)).Replace("'", "''"));
}
builder.Length = builder.Length - 1;
builder.Append(");");
}
if ((i % 50 == 0 || i == rowCount) && builder.Length > 0)
{
//每50条记录一次批量插入到数据库
rowAffected += dbAction(builder.ToString());
builder.Length = 0;
}
}
}
return rowAffected;
}
///
/// Excel文档导入到数据库
/// 默认取Excel的第一个表
/// 第一行必须为标题行
///
/// Excel文档流
/// 插入语句
/// 更新到数据库的方法
///
public static int RenderToDb(Stream excelFileStream, string insertSql, GlobalObject.DelegateCollection.DBAction dbAction)
{
return RenderToDb(excelFileStream, insertSql, dbAction, 0, 0);
}