TXT

form1

By Victor Shaw,2014-10-18 02:01
10 views 0
Imports System Imports System.Drawing Imports System.Collections Imports System.ComponentModel Imports System.Windows.Forms Imports System.Data Imports System.IO Imports System.Xml Imports System.Reflection Imports System.Data.OleDb Public Class Form1 Dim excelFilePath As String Private Sub Button5_Click(ByVal sender As S..

Imports System

    Imports System.Drawing

    Imports System.Collections

    Imports System.ComponentModel

    Imports System.Windows.Forms

    Imports System.Data

    Imports System.IO

    Imports System.Xml

    Imports System.Reflection

    Imports System.Data.OleDb

Public Class Form1

     Dim excelFilePath As String

     Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

     If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.Cancel) Then

     Return

     End If

     excelFilePath = OpenFileDialog1.FileName '设置打开的Excel文件路径

     Dim strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"

     Dim OleConn As OleDbConnection = New OleDbConnection(strConn)

     Dim i As Integer

     Dim dt As DataTable

     OleConn.Open() '打开excel文件

     dt = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) '获得表

     ComboBox1.Items.Clear() '清空列表

     '添加列表

     For i = 0 To dt.Rows.Count() - 1

     ComboBox1.Items.Add(dt.Rows(i)("TABLE_NAME").ToString())

     Next

     ComboBox1.SelectedIndex = 0 '选中第一项

     OleConn.Close()

     TextBox1.Text = excelFilePath

     End Sub

     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

     End Sub

     Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

     If excelFilePath = "" Then

     MsgBox("请先选择Excel文件")

     Button5_Click(sender, e)

     Return

     End If

     If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.Cancel Then

     Return

     Else

     '开始输出为xml

     Dim xmlFilePath = SaveFileDialog1.FileName '得到保存的文件名

     Dim strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"

     Dim OleConn As OleDbConnection = New OleDbConnection(strConn)

     Dim strExcel As String

     Dim excel_ds As DataSet = New DataSet

     Dim i As Integer

     Dim excelCommand As OleDbDataAdapter

     strExcel = "select * from [" + ComboBox1.Text + "]"

     OleConn.Open()

     excelCommand = New OleDbDataAdapter(strExcel, OleConn)

     excelCommand.Fill(excel_ds, "exdtSource")

     excel_ds.WriteXml(xmlFilePath)

     MsgBox("保存完成", MsgBoxStyle.Information, "提示")

     End If

     End Sub

     Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal

e As System.EventArgs)

     End Sub

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

     End

     End Sub

     Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

     If OpenFileDialog2.ShowDialog() = Windows.Forms.DialogResult.Cancel Then

     Return

     Else

     TextBox2.Text = OpenFileDialog2.FileName

     End If

     End Sub

     Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

     If SaveFileDialog2.ShowDialog() = Windows.Forms.DialogResult.Cancel Then

     Return

     Else

     TextBox3.Text = SaveFileDialog2.FileName

     End If

     End Sub

     Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

     If TextBox2.Text = "" Then

     MsgBox("请选择要转换的XML文件~~")

     Button2_Click(sender, e)

     Return

     End If

     If TextBox3.Text = "" Then

     MsgBox("请设置输出的文件路径~~~")

     Button6_Click(sender, e)

     Return

     End If

     FileCopy(Application.StartupPath() + "\Template.xls", TextBox3.Text) '

先拷贝模板文件

     Dim ds As DataSet = New DataSet()

     ds.ReadXml(TextBox2.Text) '读取xml文件

     '===========================================

     '下面开始向Excel文件输出了

     Dim strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + TextBox3.Text + ";Extended Properties='Excel 8.0;HDR=NO'"

     Dim OleConn As OleDbConnection = New OleDbConnection(strConn)

     Dim command As OleDbCommand = New OleDbCommand()

     OleConn.Open()

     Dim i As Integer

     Dim j As Integer

     Dim sql As String

     Dim C_Inf As String

     sql = "CREATE TABLE Sheet1 ("

     C_Inf = "("

     For i = 0 To ds.Tables(0).Columns.Count() - 1

     sql = sql + ds.Tables(0).Columns(i).ColumnName + " Text"

     C_Inf = C_Inf + ds.Tables(0).Columns(i).ColumnName

     If i <> ds.Tables(0).Columns.Count() - 1 Then

     sql = sql + ","

     C_Inf = C_Inf + ","

     Else

     sql = sql + ")"

     C_Inf = C_Inf + ")"

     End If

     Next

     command.Connection = OleConn

     command.CommandText = sql

     command.ExecuteNonQuery() '创建表

     For i = 0 To ds.Tables(0).Rows.Count() - 1

     sql = "insert into [Sheet1$] " + "" + "values ("

     For j = 0 To ds.Tables(0).Columns().Count() - 1

     sql = sql + "'" + ds.Tables(0).Rows(i).Item(j).ToString() + "'"

     If j <> ds.Tables(0).Columns().Count() - 1 Then '如果不是最后一个

     sql = sql + ","

     Else '是最后一个

     sql = sql + ")"

     End If

     Next

     command.Connection = OleConn

     command.CommandText = sql

     command.ExecuteNonQuery() '执行

     Next

     OleConn.Close()

     MsgBox("输出完成~~~", MsgBoxStyle.Information)

     End Sub

    End Class

Report this document

For any questions or suggestions please email
cust-service@docsford.com