using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.OleDb; using System.Drawing; using System.Linq; using System.Text; using System.Windows; using System.Windows.Forms; using System.IO; namespace Client { public partial class Main : Form { public Main() { InitializeComponent(); } private void btnSourceFile_Click(object sender, EventArgs e) { OpenFileDialog opf = new OpenFileDialog(); opf.ShowDialog(); //opf.Filter = "*.xls"; tbSourceFile.Text = opf.FileName; } private void Main_Load(object sender, EventArgs e) { } private void btnSubmit_Click(object sender, EventArgs e) { try { DataTable dt = GetSourceFileData(tbSourceFile.Text); SpliteSourceFile(dt, int.Parse(tbLineCount.Text)); } catch (Exception ex) { MessageBox.Show(ex.Message); } } public string CopyTemplateFile(string destinationPath) { string filename = Guid.NewGuid().ToString() + ".xls"; File.Copy(Path.GetFullPath(Application.StartupPath + @"/Template/Template.xls"), destinationPath + filename); return filename; } public DataTable GetSourceFileData(string filename) { string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1/""; OleDbConnection connection = new OleDbConnection(strConn); string strExcel = ""; OleDbDataAdapter myCommand = null; DataTable dt = new DataTable(); strExcel = "select * from [sheet1$]"; try { connection.Open(); myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(dt); return dt; } finally { connection.Close(); connection.Dispose(); } } protected string GetOutputPath() { DateTime dt = DateTime.Now; string path = Path.GetFullPath(@"Output/" + dt.ToString("yyyyMMddHHmmss") + @"/"); if (Directory.Exists(path) == false) { DirectoryInfo di = Directory.CreateDirectory(path); di.Attributes = FileAttributes.Normal; tbMessage.AppendText(path); tbMessage.AppendText("/r/n"); } return path; } protected void SpliteSourceFile(DataTable dt, int lineCount) { int count = dt.Rows.Count; int fileCount = count/lineCount; if (count % lineCount > 0) fileCount++; string outputPath = GetOutputPath(); for (int i = 0; i < fileCount; i++) { string filename = CopyTemplateFile(outputPath); string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + outputPath + filename + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=0/""; OleDbConnection connection = new OleDbConnection(strConn); OleDbCommand command = null; try { connection.Open(); for (int j = i * lineCount; j < (i + 1) * lineCount && j < count; j++) { DataRow dr = dt.Rows[j]; try { command = connection.CreateCommand(); command.CommandText = "insert into [sheet1$] values(:p0,:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14)"; command.Parameters.AddWithValue("p0", dr[0]); command.Parameters.AddWithValue("p1", dr[1]); command.Parameters.AddWithValue("p2", dr[2]); command.Parameters.AddWithValue("p3", dr[3]); command.Parameters.AddWithValue("p4", dr[4]); command.Parameters.AddWithValue("p5", dr[5]); command.Parameters.AddWithValue("p6", dr[6]); command.Parameters.AddWithValue("p7", dr[7]); command.Parameters.AddWithValue("p8", dr[8]); command.Parameters.AddWithValue("p9", dr[9]); command.Parameters.AddWithValue("p10", dr[10]); command.Parameters.AddWithValue("p11", dr[11]); command.Parameters.AddWithValue("p12", dr[12]); command.Parameters.AddWithValue("p13", dr[13]); command.Parameters.AddWithValue("p14", dr[14]); command.ExecuteNonQuery(); } finally { command.Dispose(); } } } finally { connection.Close(); connection.Dispose(); } } } } } |