/// <summary> /// 把一行数据转换成一个对象,每一列是一个属性 /// </summary> private object ConvertRowData(DataRow row) { var rowData = new Dictionary <string, object>(); foreach (DataColumn column in options.Data.Columns) { var data = row[column]; var type = typeRow[column.Ordinal].ToString(); rowData[GetColumnName(column)] = ExportManager.GetColumnValue(data, type); } return(rowData); }
/// <summary> /// 将表单内容转换成INSERT语句 /// </summary> private string GetTableContentSQL() { StringBuilder sbContent = new StringBuilder(); StringBuilder sbNames = new StringBuilder(); StringBuilder sbValues = new StringBuilder(); sbContent.AppendFormat("TRUNCATE TABLE `{0}`;\n", options.Name); sbContent.AppendFormat("LOCK TABLES `{0}` WRITE;\n", options.Name); var sheet = options.Data; //-- 字段名称列表 foreach (DataColumn column in sheet.Columns) { if (sbNames.Length > 0) { sbNames.Append(", "); } sbNames.Append("`" + column.ToString() + "`"); } //-- 逐行转换数据 for (int i = 0; i < sheet.Rows.Count; i++) { DataRow row = sheet.Rows[i]; if (i != 0) { sbValues.Append(", \n"); } sbValues.Append("("); for (int j = 0; j < sheet.Columns.Count; j++) { var column = sheet.Columns[j]; if (j != 0) { sbValues.Append(", "); } var value = ExportManager.GetColumnValue(row[column], typeRow[column.Ordinal].ToString()); if (value.GetType() == typeof(string)) { sbValues.AppendFormat("'{0}'", value); } else { sbValues.AppendFormat("{0}", value); } } sbValues.Append(")"); } sbContent.AppendFormat("INSERT DELAYED INTO `{0}` ({1}) VALUES \n{2};\n", options.Name, sbNames.ToString(), sbValues.ToString()); sbContent.AppendLine("UNLOCK TABLES;\n"); return(sbContent.ToString()); }