private void CreateTable(string SheetName, VAN_OA.Model.TB_EXCEL model, int allSheets)
{
if (allSheets == 0)
{
string updateSql = "update TB_EXCEL set Table_Name='" + sqlTableName + "',SheetName='" + SheetName.Replace("'", "").Replace("$", "") + "' where id=" + model.Id;
ExeCommand(updateSql);
}
else
{
model.SheetName = SheetName.Replace("'", "").Replace("$", "");
model.Table_Name = sqlTableName;
excelSer.Add(model);
}
lblMess.Text = "创建主表......";
Application.DoEvents();
string sql = string.Format(@"CREATE TABLE [dbo].[{0}](
[Ids] [int] IDENTITY(1,1) NOT NULL,
[ExcelJosnContent] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED
(
[Ids] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];", sqlTableName);
//sql += string.Format("insert TB_EXCEL values('{0}',getdate(),'{1}','{2}') ", txtName.Text, sqlTableName, SheetName.Replace("'", "").Replace("$",""));
ExeCommand(sql);
}
private void DoWork(VAN_OA.Model.TB_EXCEL model)
{
try
{
Doing = true;
lblMess.Text = "准备导入......";
Application.DoEvents();
var start = DateTime.Now;
Save(model);
var end = DateTime.Now;
var a = end - start;
lblMess.Text = string.Format("导入成功!");
Application.DoEvents();
//MessageBox.Show(string.Format("导入成功:开始时间:{0},结束时间：{1},总时间：{2}", start.ToString("yyyy-MM-dd HH:MM:ss"), end.ToString("yyyy-MM-dd HH:MM:ss"), a.TotalMinutes));
}
catch (Exception)
{
}
finally
{
Doing = false;
}
}
private void Save(VAN_OA.Model.TB_EXCEL model)
{
lblMess.Text = "正在读取Excel sheet信息......";
Application.DoEvents();
string FileAddress = FileName + model.FileType;
string sheetName = "";
int Com = 0;
ArrayList al = new ArrayList();
string strConn;
strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FileAddress + "; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
int allSheets = 0;
foreach (DataRow rowTable in sheetNames.Rows)
{
var start = DateTime.Now;
sqlTableName = DateTime.Now.ToString("yyyyMMdd_HH_mm_ss") + "_" + allSheets;
sheetName = rowTable["TABLE_NAME"].ToString();
CreateTable(sheetName, model, allSheets);
lblMess.Text = string.Format("正在导入sheet[{0}] 数据......", sheetName);
Application.DoEvents();
int allCount = 0;
if (allSheets != 0)
{
conn.Close();
conn = new OleDbConnection(strConn);
conn.Open();
}
OleDbCommand objCommand = new OleDbCommand(string.Format("select * from [" + sheetName + "]"), conn);
using (OleDbDataReader dataReader = objCommand.ExecuteReader())
{
DataTable dataTable = GetTableSchema();
while (dataReader.Read())
{
Com = dataReader.FieldCount;
var EXCEL = new MyExcel();
#region 列赋值
if (Com > 0)
{
EXCEL.A = dataReader[0].ToString();
if (Com > 1)
{
EXCEL.B = dataReader[1].ToString();
if (Com > 2)
{
EXCEL.C = dataReader[2].ToString();
if (Com > 3)
{
EXCEL.D = dataReader[3].ToString();
if (Com > 4)
{
EXCEL.E = dataReader[4].ToString();
if (Com > 5)
{
EXCEL.F = dataReader[5].ToString();
if (Com > 6)
{
EXCEL.G = dataReader[6].ToString();
if (Com > 7)
{
EXCEL.H = dataReader[7].ToString();
if (Com > 8)
{
EXCEL.I = dataReader[8].ToString();
if (Com > 9)
{
EXCEL.J = dataReader[9].ToString();
if (Com > 10)
{
EXCEL.K = dataReader[11].ToString();
if (Com > 12)
{
EXCEL.L = dataReader[12].ToString();
if (Com > 13)
{
EXCEL.M = dataReader[13].ToString();
if (Com > 14)
{
EXCEL.N = dataReader[14].ToString();
if (Com > 15)
{
EXCEL.O = dataReader[15].ToString();
if (Com > 16)
{
EXCEL.P = dataReader[16].ToString();
if (Com > 17)
{
EXCEL.Q = dataReader[17].ToString();
if (Com > 18)
{
EXCEL.R = dataReader[18].ToString();
if (Com > 19)
{
EXCEL.S = dataReader[19].ToString();
if (Com > 20)
{
EXCEL.T = dataReader[20].ToString();
if (Com > 21)
{
EXCEL.U = dataReader[21].ToString();
if (Com > 22)
{
EXCEL.V = dataReader[22].ToString();
if (Com > 23)
{
EXCEL.W = dataReader[23].ToString();
if (Com > 24)
{
EXCEL.X = dataReader[24].ToString();
if (Com > 25)
{
EXCEL.Y = dataReader[25].ToString();
if (Com > 26)
{
EXCEL.Z = dataReader[26].ToString();
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
#endregion
var dataRow = dataTable.NewRow();
dataRow[1] = JsonConvert.SerializeObject(EXCEL);
dataTable.Rows.Add(dataRow);
if (allCount % 10000 == 0)
{
SaveData(dataTable);
dataTable = GetTableSchema();
}
allCount++;
lblMess.Text = string.Format("正在导入sheet[{0}] 数据 第{1}行......", sheetName, allCount);
Application.DoEvents();
}
if (allCount % 10000 != 0)
{
SaveData(dataTable);
dataTable = GetTableSchema();
}
}
string updateSql = string.Format("update TB_EXCEL set UpState=3 where Table_Name='{0}';", sqlTableName);
ExeCommand(updateSql);
allSheets++;
}
conn.Close();
}
