Anonymní profil GAM – Programujte.com

 

     Dim en As New CultureInfo("en-US")
            Thread.CurrentThread.CurrentCulture = en

            ' Creates a DateTime for the local time.
            Dim dt = DateTime.Now

            ' Converts the local DateTime to the UTC time.
            Dim utcdt As DateTime = dt.ToUniversalTime()

            ' Defines a custom string format to display the DateTime.
            ' zzz specifies the full time zone offset.

            Dim format As [String] = "dd-MM-yyyy"
            Dim SaveFormat As [String] = "yyyy_MM_dd"
            Dim FilterForm As [String] = "dd.M.yyyy"

            ' Converts the local time to a string
            ' using the custom format string and display.

            Dim str As [String] = dt.ToString(format)
            Dim strSave As [String] = dt.ToString(SaveFormat)
            Dim strAFilter As [String] = dt.ToString(FilterForm)

            Label1.Text = "Otevírám batchexport-134-" & str & ".xlsx"
            Dim oExcel As Object
            oExcel = CreateObject("Excel.Application")

            Dim fileTest As String = "D:\Download\batchexport-134-" & str & ".xlsx"

            'On Error GoTo NEEXISTUJE
            oExcel.Workbooks.Open(fileTest)

            Dim oBook As Microsoft.Office.Interop.Excel.Workbook
            Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet

            oBook = oExcel.ActiveWorkbook
            oSheet = oExcel.Worksheets(1)

            ' Úprava souboru mazaní sloupců, upravování formátu v buňkách doplnění textu
            Label1.Text = "Mažu sloupce..."
            oSheet.Range("A:A").EntireColumn.Delete()
            oSheet.Range("A:A").EntireColumn.Delete()
            oSheet.Range("A:A").EntireColumn.Delete()
            oSheet.Range("A:A").EntireColumn.Delete()
            oSheet.Range("C:C").EntireColumn.Delete()
            oSheet.Range("F:F").EntireColumn.Delete()
            oSheet.Range("H:H").EntireColumn.Delete()
            oSheet.Range("H:H").EntireColumn.Delete()
            oSheet.Range("H:H").EntireColumn.Delete()
            oSheet.Range("I:I").EntireColumn.Delete()
            oSheet.Range("I:I").EntireColumn.Delete()
            oSheet.Range("I:I").EntireColumn.Delete()
            oSheet.Range("K:K").EntireColumn.Delete()
            oSheet.Range("L:L").EntireColumn.Delete()
            oSheet.Range("L:L").EntireColumn.Delete()
            oSheet.Range("M:M").EntireColumn.Delete()
            oSheet.Range("M:M").EntireColumn.Delete()
            oSheet.Range("M:M").EntireColumn.Delete()
            oSheet.Range("M:M").EntireColumn.Delete()
            oSheet.Range("M:M").EntireColumn.Delete()
            oSheet.Range("M:M").EntireColumn.Delete()
            oSheet.Range("M:M").EntireColumn.Delete()
            oSheet.Range("M:M").EntireColumn.Clear()

            '' AutoFiltry in Excel
            oSheet.Range("A:N").AutoFilter(Field:=1, [Operator]:=XlAutoFilterOperator.xlFilterDynamic, [Criteria1]:=XlDataSeriesDate.xlDay)
            oSheet.Range("A:N").AutoFilter(Field:=4, Criteria1:="prodáno 1. balíček")

            '' nadpis buňky
            oSheet.Range("M1").Value = "e-mail"
            '' přidání listu
            Label1.Text = "Přidávám list"
            oBook.Worksheets.Add(After:=oBook.Sheets(oBook.Sheets.Count)).Name = ("batchexports-134-" & str) ' Přidání listu za existující
            'oBook.Sheets.Add() (jednoduché přidání listu)

            Clipboard.Clear()

            '' vybrání a zkopírování dat z listu na list listu
            Label1.Text = "Kopíruji data do nového listu a zpět..."
            oBook.Sheets("batchexport-134-" & str).Range("A:N").Copy
            oBook.Sheets("batchexports-134-" & str).Range("A:N").PasteSpecial(XlPasteType.xlPasteValues, , False, False)
            oBook.Sheets("batchexport-134-" & str).Select
            Label1.Text = "Vypínám filtry..."
            oBook.Sheets("batchexport-134-" & str).AutoFilterMode = False
            Label1.Text = "Mažu napotřebná data..."
            oSheet.Range("A:N").Clear()
            oBook.Sheets("batchexports-134-" & str).Range("A:N").Copy
            oBook.Sheets("batchexport-134-" & str).Range("A:N").PasteSpecial(XlPasteType.xlPasteValues, , False, False)
            oBook.Worksheets(2).Delete()
            oBook.Sheets("batchexport-134-" & str).Select
            oSheet.Range("A1").Select()

            Label1.Text = "Formátuji buňky..."
            oSheet.Range("A:A").EntireColumn.NumberFormat = "d/m/yyyy h:mm;@"
            oSheet.Range("E:E").EntireColumn.NumberFormat = "d/m/yyyy h:mm;@"
            oSheet.Range("H:H").EntireColumn.NumberFormat = "0"
            oSheet.Range("L:L").Replace(" ", "")
            oSheet.Range("M:M").EntireColumn.ColumnWidth = 20
            ' Automatická šíře buňky
            oSheet.Range("A:A").EntireColumn.AutoFit()
            oSheet.Range("D:D").EntireColumn.AutoFit()

            'My.Computer.Clipboard.SetText(Clipboard.ContainsText, System.Windows.Forms.TextDataFormat.Rtf)

            ' Uložení souboru
            Label1.Text = "Ukládám soubor" & str & "_prodeje.xls"
            oExcel.DisplayAlerts = False
            oBook.SaveAs("D:\Download\" & strSave & "_prodeje.xlsx", 51) ' 51 == xlsx
            oBook.Close()
            oBook = Nothing
            Label1.Text = "Uloženo jako D:\Download\" & str & "_prodeje.xls"
            Label1.Text = "Report byl úspěšně vytvořen. Nezapomeň dodat e-maily!"
            GoTo KONEC
    NEEXISTUJE:
            Label1.Text = "Chyba v běhu programu."
            MessageBox.Show("Chyba v běhu programu. Číslo chyby: " & Err.Number & " " & Err.Description, "Report Excel Creator", MessageBoxButtons.OK, MessageBoxIcon.Information)
            REM End
    KONEC:

Moje práce s časem a excelem ve Visual Studio 2015. Třeba to někomu pomůže.