选中想要生成的列，套用表格格式，选中表包含标题的选项确定，然后在最右边的一列第二行处，点击函数功能，选择CONCATENATE，在文本里输入想要的结构即可
| 代码如下 | 复制代码 |
| ，=CONCATENATE("('",[@id],"','",[@name],"'),") | |
这样生成的之后的语句可以写为
| 代码如下 | 复制代码 |
| insert table1 (id,name) values ('1', "测试1"),('2', "测试2")... | |
| 代码如下 | 复制代码 |
| using System; using System.Collections.Generic;using System.Linq;using System.Text;using System.IO;using System.Reflection;using Excel = Microsoft.Office.Interop.Excel;using System.Data;using System.Data.OleDb;using System.Windows.Forms;namespace ReadXlsxData{ static class ParseXlsx { public static readonly int COMMENT_INDEX=4; //字段说明行下标 public static readonly int KEY_INDEX = 5; //主键行下标 public static readonly int TYPE_INDEX = 6; //字段类型行下标 public static readonly int SQLNAME_INDEX = 7; //字段名行下标 public static readonly int VALUE_INDEX = 8; //value 行下标 public static StringBuilder objectData = new StringBuilder(); public static DataTable ToDataSet(string filePath) { string connStr = ""; string fileType = System.IO.Path.GetExtension(filePath); if (string.IsNullOrEmpty(fileType)) return null; if (fileType == ".xls") connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties="Excel 8.0;HDR=NO;IMEX=1""; else connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties="Excel 12.0;HDR=NO;IMEX=1""; string sql_F = "Select * FROM [{0}]"; OleDbConnection conn = null; OleDbDataAdapter da = null; DataTable dataTable = new DataTable(); try { // 初始化连接，并打开 conn = new OleDbConnection(connStr); conn.Open(); da = new OleDbDataAdapter(); da.SelectCommand = new OleDbCommand(String.Format(sql_F, "Sheet1$"), conn); da.Fill(dataTable); } (Exception ex) {} finally { // 关闭连接 if (conn.State == ConnectionState.Open) { conn.Close(); da.Dispose(); conn.Dispose(); } } conn.Close(); da.Dispose(); conn.Dispose(); return dataTable; } public static string ReadExcelFile(string namef, string sqlfile, string sqlcomment) { objectData.Clear(); DataTable dt = ToDataSet(namef); string temp, key,temp1,temp2; List<int> index = new List<int>(); //创建表头 objectData.Append("DROP TABLE IF EXISTS `" + sqlfile + "`;n"); objectData.Append("CREATE TABLE `" + sqlfile + "` (n"); int columnSize = dt.Columns.Count; int rowSize = dt.Rows.Count; DataColumn dc; DataRow dr; temp = string.Empty; key = string.Empty; temp1 = string.Empty; temp2 = string.Empty; DataRow dr5 = dt.Rows[COMMENT_INDEX],dr9=dt.Rows[SQLNAME_INDEX],dr8=dt.Rows[TYPE_INDEX]; for (int i = 1; i < columnSize; i++) { dc = dt.Columns[i]; temp2 = dr5[dc].ToString(); temp1 = dr9[dc].ToString(); if (temp2 == string.Empty)//空列判断 break; else if (temp1.ToString() != string.Empty) //字段 { index.Add(i); temp = dr8[dc].ToString(); if (temp.Contains("vachar")) objectData.Append("t`" + temp1 + "` " + temp + " NOT NULL DEFAULT '' COMMENT '" + temp2 + "',n"); else objectData.Append("t`" + temp1 + "` " + temp + " NOT NULL DEFAULT '0' COMMENT '" + temp2 + "',n"); temp = dt.Rows[KEY_INDEX][dc].ToString(); if (temp != null && temp.Contains("key")) { key += "`" + temp1 + "` ";} } } if(key!=string.Empty) objectData.Append("tPRIMARY KEY (" + key + ")n"); objectData.Append(") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='" + sqlcomment + "';n"); for (int i = VALUE_INDEX; i < rowSize; i++) //读取数据记录 { objectData.Append("INSERT INTO `" + sqlfile + "` VALUES ('"); dr = dt.Rows[i]; int length = index.Count; for (int j = 0; j < length; j++) { objectData.Append(dr[index[j]] + "','"); } objectData.Remove(objectData.Length - 3, 2); objectData.Append(");n"); } return objectData.ToString(); } } | |
}
最终导出结果如下
你可能感兴趣的文章