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 { // 初始化连接,并打开 www.111cn.net conn = new OleDbConnection(connStr); conn.Open(); da = new OleDbDataAdapter(); da.SelectCommand = new OleDbCommand(String.Format(sql_F, "Sheet1$"), conn); da.Fill(dataTable); } catch (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(); } }
|