Excel上传找到错误数据类型

Excel上传找到错误数据类型

一：查询数据库表中字段的类型语句

SELECT CASE WHEN col.colorder = 1 THEN obj.name ELSE '' END AS 表名, col.colorder AS 序号 , col.name AS 列名 , ISNULL(ep.[value], '') AS 列说明 , t.name AS 数据类型 , col.length AS 长度 , ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 , CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '1' ELSE '' END AS 标识 , CASE WHEN EXISTS ( SELECT 1 FROM dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK' WHERE sc.id = col.id AND sc.colid = col.colid ) THEN '1' ELSE '' END AS 主键 , CASE WHEN col.isnullable = 1 THEN '1' ELSE '' END AS 允许空 , ISNULL(comm.text, '') AS 默认值FROM dbo.syscolumns col LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype inner JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0 LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description' LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id AND epTwo.minor_id = 0 AND epTwo.name = 'MS_Description'WHERE obj.name = ''--表名ORDER BY col.colorder ;

效果图：

二：帮助类

主要判断Excel表格中字段类型和数据库中的字段类型 ，（使用插件aspose.cells）

public class DataTypeHelper { public static bool IsCanConvert(string str, DataColumn col) { if (col.DataType.Name.ToLower() == "decimal") { try { if (str == "IsDecimal" || str == "IsNumeric") { return true; } return false; } catch (Exception) { return false; } } if (col.DataType.Name.ToLower() == "string") { try { if (str == "IsString") { return true; } return false; } catch (Exception) { return false; } } if (col.DataType.Name.ToLower() == "int") { try { if (str == "IsInt") { return true; } return false; } catch (Exception) { return false; } } if (col.DataType.Name.ToLower() == "datetime") { try { if (str == "IsDateTime") { return true; } return false; } catch (Exception) { return false; } } return false; } }

三：业务处理代码：

1.拼接一条查询返回Excel对应表中的第一条数据：

string dd = Context.Config.InsertSql; string[] a1 = dd.Split(')'); string[] b = a1[0].Split('('); string c = b[0].Replace("insert", "").Replace("into", "").Trim(); //查询出一条记录 string strSql = "select top 1 " + b[1] + " from " + c;

2.循环判断：

DataTable dt1 = new DataTable(); dt1 = ISS.DataAccess.DbHelper.ExecuteDataSetBySql("SheetConncetionKey", strSql).Tables[0]; //取到Sheet中的值 循环列 int i1 = 0; for (int j = Context.Config.DataStartCol; j < Context.Config.DataEndCol + 1; j++) { for (int m = Context.Config.DataStartRow + 1; m < Context.Config.DataEndRow + 1; m++) { if (endRows > Context.Config.DataStartRow + 1) { string ss = Context.Worksheet.Cells[m, j].Type.ToString(); if (Context.Worksheet.Cells.Columns[j].IsHidden) continue; if (Context.Worksheet.Cells[Context.Config.DataStartRow, j].StringValue.Trim().ToLower() == "name".ToLower()) { //找到财务费用的行 赋值给j 13 continue; } if (ss == "IsNull") { continue; } DataColumn dc = dt1.Columns[i1]; if (!DataTypeHelper.IsCanConvert(Context.Worksheet.Cells[m, j].Type.ToString(), dt1.Columns[i1]) && Context.Worksheet.Cells[m, j].Type.ToString() != "IsNull") { errorList.Add(wb.Worksheets[i].Name + "单元格中" + (Convert.ToInt32(m) + 1) + "行" + Context.Worksheet.Cells[m, j].Name + "列" + "数据格式不匹配,内容为：" + Context.Worksheet.Cells[m, j].Value.ToString()); } } } i1++; }

效果：

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。