/// <summary>
/// 将excel导入到datatable
/// </summary>
/// <param name="filePath">excel路径</param>
/// <returns>返回datatable</returns>
DataTable ExcelTable = null;
FileStream fs = null;
DataColumn column = null;
DataRow dataRow = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
int startRow = ;
public bool ExcelToDataTable(string filePath)
{
try
{
using (fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
// 解决版本兼容
//07以前为xls,以后为xlsx
if (filePath.IndexOf(".xlsx") > )
{
workbook = new XSSFWorkbook(fs);
}
else if (filePath.IndexOf(".xls") > )
{
workbook = new HSSFWorkbook(fs);
}
if (workbook != null)
{
sheet = workbook.GetSheetAt();//读取第一个sheet
ExcelTable = new DataTable();
if (sheet != null)
{
int rowCount = sheet.LastRowNum;//总行数
if (rowCount > )
{
IRow firstRow = sheet.GetRow();//第二行
int cellCount = firstRow.LastCellNum;//列数
//创建datatable的列
startRow = ;//因为第一行是中文列名所以直接从第二行开始读取
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
cell = firstRow.GetCell(i);
if (cell != null)
{
if (cell.StringCellValue != null)
{
column = new DataColumn(cell.StringCellValue);
ExcelTable.Columns.Add(column);
}
}
}
//填充datatable行
for (int i = startRow; i <= rowCount; ++i)
{
row = sheet.GetRow(i);
if (row == null) continue;
dataRow = ExcelTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
}
else
{
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//对时间格式的处理
if (format == || format == || format == || format == )
dataRow[j] = cell.DateCellValue;
else
dataRow[j] = cell.NumericCellValue;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
}
}
}
ExcelTable.Rows.Add(dataRow);
}
}
}
}
}
//由于excel表在删除一张表的时候回再次读取回出现空行的原因
//所以需要一个删除空行的方法⇣⇣⇣⇣
List<DataRow> removelist = new List<DataRow>();
for (int i = ; i < ExcelTable.Rows.Count; i++)
{
bool IsNull = true;
for (int j = ; j < ExcelTable.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(ExcelTable.Rows[i][j].ToString().Trim()))
{
IsNull = false;
}
}
if (IsNull)
{
removelist.Add(ExcelTable.Rows[i]);
}
}
for (int i = ; i < removelist.Count; i++)
{
ExcelTable.Rows.Remove(removelist[i]);
}
removelist.Clear();
//遍历将datatable内的值存入数据库
foreach (DataRow item in ExcelTable.Rows)
{
RT_Community com = new Model.RT_Community();
RT_UserInfo userinfo = new Model.RT_UserInfo();
Guid guid = Guid.NewGuid();
Guid guidu = Guid.NewGuid();
int aid = GetVpnUserName(item[].ToString());
int query = ;
using (var conn = PublicMethod.GetSqlConnection())
{
//当小区名称存在时会返回-1
query = conn.Execute(@"IF NOT EXISTS
(SELECT Name FROM RT_Community WHERE Name=@Name )
INSERT INTO RT_Community
(Id,VpnUser_id,Name,Sabb,PropertyName,PropertyUserName,PropertyPhone,Address,IsDelete) values
(@Id,@VpnUser_id,@Name,@Sabb,@PropertyName,@PropertyUserName,@PropertyPhone,@Address,@IsDelete)",
new {
Id = guid,
VpnUser_id = aid,
Name = item[].ToString(),
Sabb = ChinesePinYin.GetSpellCode(item[].ToString()),
PropertyName = item[].ToString(),
PropertyUserName = item[].ToString(),
PropertyPhone = item[].ToString(),
Address = item[].ToString(),
IsDelete = ,
});
}
//当返回-1时调用查询小区id的方法对guid重新赋值
if (query == -)
{
guid = GetComId(item[].ToString());
if (guid.ToString() == "00000000-0000-0000-0000-000000000000")
{
//当返回的guid为0时
return false;
}
}
using (var conn = PublicMethod.GetSqlConnection())
{
var result = conn.Execute(@"INSERT INTO RT_UserInfo
(Id,
rt_community_id,
UserCode,
Name,
BuildingNo,
UnitNo,
Floor,
HouseNo,
Address,
HeatType,
Location,
Phone,
IsDelete) VALUES
(@Id,
@rt_community_id,
@UserCode,
@Name,
@BuildingNo,
@UnitNo,
@Floor,
@HouseNo,
@Address,
@HeatType,
@Location,
@Phone,
@IsDelete)",
new
{
Id = guidu,
rt_community_id = guid,
UserCode = item[].ToString(),
Name = item[].ToString(),
BuildingNo = item[].ToString(),
UnitNo = item[].ToString(),
Floor = item[].ToString(),
HouseNo = item[].ToString(),
Location = item[].ToString(),
Address = item[].ToString(),
HeatType = ,
Phone = item[].ToString(),
IsDelete =
});
}
}
//成功返回true
return true;
}
catch (Exception)
{
if (fs != null)
{
fs.Close();
}
return false;
}
}
//使用时直接用nuget包搜索nopi第一个导入这样就全装好了简单又省事