1 /// <summary> 2 /// 将excel导入到datatable 3 /// </summary> 4 /// <param name="filePath">excel路径</param> 5 /// <returns>返回datatable</returns> 6 DataTable ExcelTable = null; 7 FileStream fs = null; 8 DataColumn column = null; 9 DataRow dataRow = null; 10 IWorkbook workbook = null; 11 ISheet sheet = null; 12 IRow row = null; 13 ICell cell = null; 14 int startRow = 0; 15 public bool ExcelToDataTable(string filePath) 16 { 17 18 try 19 { 20 using (fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 21 { 22 // 解决版本兼容 23 //07以前为xls,以后为xlsx 24 if (filePath.IndexOf(".xlsx") > 0) 25 { 26 workbook = new XSSFWorkbook(fs); 27 } 28 else if (filePath.IndexOf(".xls") > 0) 29 { 30 workbook = new HSSFWorkbook(fs); 31 } 32 if (workbook != null) 33 { 34 sheet = workbook.GetSheetAt(0);//读取第一个sheet 35 ExcelTable = new DataTable(); 36 if (sheet != null) 37 { 38 int rowCount = sheet.LastRowNum;//总行数 39 if (rowCount > 0) 40 { 41 IRow firstRow = sheet.GetRow(1);//第二行 42 int cellCount = firstRow.LastCellNum;//列数 43 //创建datatable的列 44 startRow = 2;//因为第一行是中文列名所以直接从第二行开始读取 45 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) 46 { 47 cell = firstRow.GetCell(i); 48 if (cell != null) 49 { 50 if (cell.StringCellValue != null) 51 { 52 column = new DataColumn(cell.StringCellValue); 53 ExcelTable.Columns.Add(column); 54 } 55 } 56 } 57 58 //填充datatable行 59 for (int i = startRow; i <= rowCount; ++i) 60 { 61 row = sheet.GetRow(i); 62 if (row == null) continue; 63 64 dataRow = ExcelTable.NewRow(); 65 for (int j = row.FirstCellNum; j < cellCount; ++j) 66 { 67 cell = row.GetCell(j); 68 if (cell == null) 69 { 70 dataRow[j] = ""; 71 } 72 else 73 { 74 switch (cell.CellType) 75 { 76 case CellType.Blank: 77 dataRow[j] = ""; 78 break; 79 case CellType.Numeric: 80 short format = cell.CellStyle.DataFormat; 81 //对时间格式的处理 82 if (format == 14 || format == 31 || format == 57 || format == 58) 83 dataRow[j] = cell.DateCellValue; 84 else 85 dataRow[j] = cell.NumericCellValue; 86 break; 87 case CellType.String: 88 dataRow[j] = cell.StringCellValue; 89 break; 90 } 91 } 92 } 93 94 ExcelTable.Rows.Add(dataRow); 95 96 } 97 } 98 } 99 } 100 } 101 //由于excel表在删除一张表的时候回再次读取回出现空行的原因 102 //所以需要一个删除空行的方法⇣⇣⇣⇣ 103 List<DataRow> removelist = new List<DataRow>(); 104 for (int i = 0; i < ExcelTable.Rows.Count; i++) 105 { 106 bool IsNull = true; 107 for (int j = 0; j < ExcelTable.Columns.Count; j++) 108 { 109 if (!string.IsNullOrEmpty(ExcelTable.Rows[i][j].ToString().Trim())) 110 { 111 IsNull = false; 112 } 113 } 114 if (IsNull) 115 { 116 removelist.Add(ExcelTable.Rows[i]); 117 } 118 } 119 for (int i = 0; i < removelist.Count; i++) 120 { 121 ExcelTable.Rows.Remove(removelist[i]); 122 } 123 removelist.Clear(); 124 //遍历将datatable内的值存入数据库 125 foreach (DataRow item in ExcelTable.Rows) 126 { 127 128 RT_Community com = new Model.RT_Community(); 129 RT_UserInfo userinfo = new Model.RT_UserInfo(); 130 Guid guid = Guid.NewGuid(); 131 Guid guidu = Guid.NewGuid(); 132 int aid = GetVpnUserName(item[13].ToString()); 133 int query = 0; 134 135 using (var conn = PublicMethod.GetSqlConnection()) 136 { 137 //当小区名称存在时会返回-1 138 query = conn.Execute(@"IF NOT EXISTS 139 (SELECT Name FROM RT_Community WHERE Name=@Name ) 140 INSERT INTO RT_Community 141 (Id,VpnUser_id,Name,Sabb,PropertyName,PropertyUserName,PropertyPhone,Address,IsDelete) values 142 (@Id,@VpnUser_id,@Name,@Sabb,@PropertyName,@PropertyUserName,@PropertyPhone,@Address,@IsDelete)", 143 new { 144 Id = guid, 145 VpnUser_id = aid, 146 Name = item[0].ToString(), 147 Sabb = ChinesePinYin.GetSpellCode(item[0].ToString()), 148 PropertyName = item[1].ToString(), 149 PropertyUserName = item[2].ToString(), 150 PropertyPhone = item[3].ToString(), 151 Address = item[4].ToString(), 152 IsDelete = 0, 153 }); 154 } 155 //当返回-1时调用查询小区id的方法对guid重新赋值 156 if (query == -1) 157 { 158 guid = GetComId(item[0].ToString()); 159 if (guid.ToString() == "00000000-0000-0000-0000-000000000000") 160 { 161 //当返回的guid为0时 162 return false; 163 } 164 } 165 using (var conn = PublicMethod.GetSqlConnection()) 166 { 167 var result = conn.Execute(@"INSERT INTO RT_UserInfo 168 (Id, 169 rt_community_id, 170 UserCode, 171 Name, 172 BuildingNo, 173 UnitNo, 174 Floor, 175 HouseNo, 176 Address, 177 HeatType, 178 Location, 179 Phone, 180 IsDelete) VALUES 181 (@Id, 182 @rt_community_id, 183 @UserCode, 184 @Name, 185 @BuildingNo, 186 @UnitNo, 187 @Floor, 188 @HouseNo, 189 @Address, 190 @HeatType, 191 @Location, 192 @Phone, 193 @IsDelete)", 194 new 195 { 196 197 Id = guidu, 198 rt_community_id = guid, 199 UserCode = item[5].ToString(), 200 Name = item[6].ToString(), 201 BuildingNo = item[7].ToString(), 202 UnitNo = item[8].ToString(), 203 Floor = item[9].ToString(), 204 HouseNo = item[10].ToString(), 205 Location = item[11].ToString(), 206 Address = item[4].ToString(), 207 HeatType = 0, 208 Phone = item[12].ToString(), 209 IsDelete = 0 210 }); 211 } 212 213 } 214 //成功返回true 215 return true; 216 } 217 catch (Exception) 218 { 219 if (fs != null) 220 { 221 fs.Close(); 222 } 223 return false; 224 } 225 }
//使用时直接用nuget包搜索nopi第一个导入这样就全装好了简单又省事