Keskustelu: Ohjelmointikysymykset: [VB.NET]Päivämäärät oikein Excelistä DataGridVieweriin

Sam76 [03.07.2009 13:20:50]

Taas ongelmia näitten päivämäärien kanssa, elikkäs nyt seuraavanlainen:

DatagridViewer-taulukko jos on tekstiä, päivämääriä tms. Tämän vieminen Excel2007:seen onnistuu oikein hyvin mutta jos haluan tuoda ks. taulukon takaisin niin päivämäärät muuttuvat 1.1.2009 -> 1#1#2009. Elikkä tässä on taas tälläinen jenkki vs. suomi päivämääräformaatti-taistelu. Miten voin määrittää tämän päivämäärän suomalaiseksi?

Excelin tuonti tapahtuu näin:

        Dim connectionStringTemplate As String = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" + _
    "Data Source={0};" + _
    "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"""
        Dim XLSPath As String = OpenFileDialog1.FileName
        Dim connectionString As String = String.Format(connectionStringTemplate, XLSPath)
        Dim sqlSelect As String = "SELECT * FROM [Taul1$];"
        Dim workbook As DataSet = New DataSet()
        Dim excelAdapter As System.Data.Common.DataAdapter = New
System.Data.OleDb.OleDbDataAdapter(sqlSelect, connectionString)
        Try
            excelAdapter.Fill(workbook)
            Dim worksheet As DataTable = workbook.Tables(0)
            DataGridView1.DataSource = worksheet
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)

        End Try

neau33 [09.07.2009 14:57:49]

Moikka taas Sam76!

tämän pitäisi toimia edellyttäen, että Pvm sarake on Excel-taulussa tekstiä...

' väännetty SharpDevelop 3.0'lla
Imports System.Data
Imports System.Data.OleDb

Public Partial Class MainForm: Inherits Form

   Dim workbook As DataSet = New DataSet()
   Dim xladapter As OleDbDataAdapter
   Dim builder As OleDbCommandBuilder
   Dim connstr As String = String.Empty
   Dim sql As String = String.Empty

   Public Sub New()
      Me.InitializeComponent()
   End Sub

   Sub MainFormLoad(sender As Object, e As EventArgs)

   End Sub

   Sub Button1Click(sender As Object, e As EventArgs)

       connstr = _
       "Provider=Microsoft.ACE.OLEDB.12.0;" + _
       "Data Source=C:\xlsamples\xltaulukko.xls;" + _
       "Extended Properties=""Excel 12.0 Xml;HDR=Yes"""
        sql = "SELECT * FROM [Taul1$];"
        xladapter = New OleDbDataAdapter(sql, connstr)

        Try
            xladapter.Fill(workbook,"Taul1")
            DataGridView1.DataSource = workbook.Tables("Taul1")
            button2.Visible = True
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

   End Sub

   Sub Button2Click(sender As Object, e As EventArgs)

      Dim i As Integer = 0
      Dim Year As Integer = 2009
      Dim Month As Integer = 1
      Dim xlconn As New OleDbConnection(connstr)

      xlconn.Open()

      xladapter.AcceptChangesDuringUpdate = True
      xladapter.UpdateCommand = New OleDbCommand()
      xladapter.UpdateCommand.Connection = xlconn

      For Each row As DataRow In workbook.Tables("Taul1").Rows
      i += 1

      Dim Day As Integer = i

      Dim theDate As Date = DateSerial(Year, Month, Day)

      Try
         sql = "UPDATE [Taul1$] SET Pvm = '" _
         + theDate.ToShortDateString +  _
         "' WHERE Id = '" + row("Id") _
         + "' And Pvm = '" +  row("Pvm") + "'"
         xladapter.UpdateCommand.CommandText = sql
         row("Pvm") = theDate.ToShortDateString
         xladapter.update(workbook,"Taul1")
      Catch ex As Exception
         MessageBox.Show(ex.Message)
      End Try
      Next

      xlconn.Close()

   End Sub

End Class

neau33 [09.07.2009 21:24:06]

Moikka taas Sam76!

mutta oheinen esimerkki on ehkä edellistä parempi..

' väännetty SharpDevelop 3.0'lla
Imports System.Data
Imports System.Data.OleDb

Public Partial Class MainForm: Inherits Form

   ' Formille:
   ' 1 DataGridView-kontrolli (DataGridView1)
   ' & 3 nappia (Button1, Button2, Button3)
   Dim workbook As DataSet = New DataSet()
   Dim xladapter As OleDbDataAdapter
   Dim connstr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + _
    "Data Source=C:\xlsamples\xltaulukko.xls;" + _
    "Extended Properties=""Excel 12.0 Xml;HDR=Yes;"""
   Dim sql As String = String.Empty

   Dim rowcnt As Long = 0

   Public Sub New()
      Me.InitializeComponent()
   End Sub

   Sub MainFormLoad(sender As Object, e As EventArgs)
      Button1.Text = "tuo taulu"
      Button2.Text = "lisää"
      Button3.Text = "päivitä"
      Button2.Enabled = False
      Button3.Enabled = False
   End Sub

   Sub Button1Click(sender As Object, e As EventArgs)
        GetFromExcel
   End Sub

   Sub Button2Click(sender As Object, e As EventArgs)

        Button1.Enabled  = False
        Button3.Enabled = False

        Dim ctltxt As String = sender.text
        Select Case Button2.Text

         Case "lisää"
            DataGridView1.AllowUserToAddRows = True
            Button2.Text = " vie "
            Button3.Text = "peru"
            DataGridView1.CurrentCell = _
            DataGridView1.Rows.Item(rowcnt).Cells(0)
            button3.Enabled = True: Exit Sub
         Case " vie "
             Dim col1 As Object = Nothing
             Dim col2 As Object = Nothing

             Try
               col1 = _
               DataGridView1.Rows.Item(rowcnt + 1).Cells(0).Value
               col2 = _
               DataGridView1.Rows.Item(rowcnt + 1).Cells(1).Value

               If col1 = "" Or col1 Is Nothing or _
               col2 = "" Or col2 Is Nothing Then
               Else
                   rowcnt += 1
                   Dim xlconn As New OleDbConnection(connstr)
                    xlconn.Open()
                       Dim cmd As New OleDbCommand
                       cmd.Connection = xlconn
                       sql = "INSERT INTO [Taul1$] " + _
                       "(ID, Pvm) Values('" + col1 + "','" + col2 + "')"
                       cmd.CommandText = sql
                  cmd.ExecuteNonQuery()
                  xlconn.Close()
               End If

            Catch exx As Exception
             End Try

            ClearExtRows
            GetFromExcel

        End Select

   End Sub


   Sub Button3Click(sender As Object, e As EventArgs)

      If Button3.Text = "peru" Then
         ClearExtRows
         GetFromExcel
          Exit Sub
      End If

      Dim i As Integer = 0
      Dim Year As Integer = 0
      Dim Month As Integer = 0
      Dim Day As Integer = 0

      Dim xlconn As New OleDbConnection(connstr)
      xlconn.Open()

      xladapter.AcceptChangesDuringUpdate = True
      xladapter.UpdateCommand = New OleDbCommand()
      xladapter.UpdateCommand.Connection = xlconn

      For Each row As DataRow In workbook.Tables("Taul1").Rows
         i += 1

          Dim DateParts() As String = Split(row(1),".")
          Year = DateParts(2)
            Month = DateParts(1)
            Day  = DateParts(0)

         Dim theDate As Date = DateSerial(Year, Month, Day)

         Try
            sql = "UPDATE [Taul1$] SET Pvm = '" _
            + theDate.ToShortDateString +  _
            "' WHERE ID = '" + row("ID") + "'"
            xladapter.UpdateCommand.CommandText = sql
            row("Pvm") = theDate.ToShortDateString
            xladapter.update(workbook,"Taul1")

         Catch ex As Exception
            MessageBox.Show(ex.Message)
         End Try
      Next

      xlconn.Close()

   End Sub

   Sub GetFromExcel()

      sql = "SELECT * FROM [Taul1$];"
        xladapter = New OleDbDataAdapter(sql, connstr)

        Try
            workbook.Tables.Clear
            xladapter.Fill(workbook,"Taul1")
            Dim keys(1) As DataColumn
            keys(0) = workbook.Tables("Taul1").Columns("ID")
            keys(0).Unique = True
            workbook.Tables("Taul1").PrimaryKey=keys
            DataGridView1.DataSource = workbook.Tables("Taul1")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        For Each dgvcol As DataGridViewColumn _
        In DataGridView1.Columns
            dgvcol.HeaderCell.Style.Alignment _
            = DataGridViewContentAlignment.MiddleCenter
        Next
        DataGridView1.Columns("ID").DefaultCellStyle _
       .Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGridView1.Columns("Pvm").DefaultCellStyle _
       .Alignment = DataGridViewContentAlignment.MiddleRight
        rowcnt = DataGridView1.Rows.Count - 1
        button2.Enabled  = True
        button3.Enabled = True
        DataGridView1.CurrentCell = _
      DataGridView1.Rows.Item(rowcnt).Cells(0)
   End Sub

   Sub ClearExtRows()
      For rw As Integer = _
      rowcnt To DataGridview1.Rows.Count
         Try
            DataGridView1.Rows.Item(rw).Dispose
         Catch ex As Exception
         End Try
      Next
      Button1.Enabled = True
      Button2.Text = "lisää"
      Button3.Text = "päivitä"
      DataGridView1.AllowUserToAddRows = False
   End Sub

   Sub DataGridView1CellEndEdit(sender As Object, _
   e As DataGridViewCellEventArgs)
      DataGridView1.Rows.Item(e.RowIndex) _
      .Cells(0).Value = Cstr(e.RowIndex + 1)
   End Sub

   Sub DataGridView1CellEnter(sender As Object, _
   e As DataGridViewCellEventArgs)
      DataGridView1.Rows.Item(e.RowIndex) _
      .Cells(0).Value = Cstr(e.RowIndex + 1)
   End Sub

End Class

Sam76 [09.07.2009 22:55:33]

Kiitos taas Nea. Minun varmaan pitäisi pikkuhiljaa alkaa maksamaan sulle näistä ohjeista:)

ps. välillä tuntuu että ymmärtäisi jotain vb-koodin päälle mutta tälläisissä tilanteissa huomaan osaavani: en mitään... mutta ehkä joskus tulevaisuudessa sitten.

Vastaus

Aihe on jo aika vanha, joten et voi enää vastata siihen.

Kirjautuminen

Haku