|
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.111com.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 index = new List();
//创建表头
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();
}
}
|
