主页 / user-323507

2b4fITin's questions

Martin Hope
2b4fITin
Asked: 2025-02-09 14:47:10 +0000 UTC
  • 5

问题：使用在其他地方编写的宏（连接数据库；循环遍历 Excel 表中所需的单元格；将循环中获得的单元格值传输到函数；INSERT INTO在函数中；处理 SQL 注入）将表从 Excel（包含空单元格）导出到 MS SQL 后，在 MS SQL 中的表中我们得到的是空单元格，而不是 NULL 值。

问题：如何确保 MS SQL 中的结果表中的空单元格（屏幕上用红点标记）不是“空的”，而是NULL？

在此处输入图片描述

sql
Martin Hope
2b4fITin
Asked: 2022-05-19 18:16:17 +0000 UTC
  • 0

同事！将字符串从数组输出到新工作表时，代码（超出范围）崩溃（我什至不确定数组的形成是否一切正常）：

Sub BKFindDeviations()

Dim Deviations() As Variant
Dim rng As Range
Dim Mnth1 As Date
Dim Mnth2 As Date
Dim MnthRowCounter As Long
Dim LoopCounter As Long
Dim r As Range
Dim k As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Mnth1 = "01.01.2020" 'Mnth1 = InputBox("Ââåäèòå ïåðâîå ÷èñëî ìåñÿöà, êîòîðûé áóäåò ñðàâíèâàòüñÿ ñ ïðîøëûìè ìåñÿöàì (ôîðìàò: dd.mm.yyyy")
Mnth2 = "01.02.2020" 'Mnth2 = InputBox("Ââåäèòå ïåðâîå ÷èñëî ìåñÿöà, êîòîðûé áóäåò ÿâëÿòüñÿ áàçîé äëÿ ñðàâíåíèÿ (ôîðìàò: dd.mm.yyyy")

For Each r In Range("a1", Range("a1").End(xlDown))
    If r.Offset(0, 8).Value = Mnth1 Or r.Offset(0, 8).Value = Mnth2 Then
        MnthRowCounter = MnthRowCounter + 1
        ReDim Preserve Deviations(1 To 17, 1 To MnthRowCounter)
        For LoopCounter = 1 To 17 'Range("a1", Range("a1").End(xlToRight))
            Deviations(LoopCounter, MnthRowCounter) = r.Offset(0, LoopCounter - 1).Value
        Next LoopCounter
    End If
Next r

'Deviations = Range("a1", Range("a1").End(xlToRight).End(xlDown))

Worksheets.Add

Range(ActiveCell, ActiveCell.Offset(UBound(Deviations, 1) - 1, UBound(Deviations, 2) - 1)).Value = Deviations

Erase Deviations

Set rng = Range("a1", Range("a1").End(xlDown).End(xlToRight))

'×èñòèì ôîðìàò
With rng
    .ClearFormats
End With

'Óñòàíàâëèâàåì ôîðìàò "Äàòà" â ñòîëáöå Äàò
For k = 1 To Cells(Rows.Count, 9).End(xlUp).Row
    'If Cells(r, 9) Like "##.##.####" Then
        'Cells(r, 9) = DateSerial(Right(Cells(r, 9), 4), Mid(Cells(r, 9), 4, 2), Left(Cells(r, 9), 2))
        Cells(k, 9).NumberFormat = "dd.mm.yyyy;@"
    'End If
Next k

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic End Sub

“出发”前算子所涉及的变量值：

在此处输入图像描述

更新了代码

excel
Martin Hope
2b4fITin
Asked: 2021-12-30 19:02:35 +0000 UTC
  • 0

同事！帮我弄清楚：我不能以任何方式只插入值​​​​​）。

Sub GetTransactions()

    Dim my_FileNameLong As Variant
    Dim my_FileNameShort As Variant
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim ListName As String
    Dim FinalRow As Integer
    Dim i As Long
    
    Set wb = ThisWorkbook
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculateManual
    
    'Открываем файл (только для чтения) с операциями за текущий месяц + определяем переменную my_FileName
    my_FileNameLong = Application.GetOpenFilename(filefilter:="Excel Files,*.xl*;*.xm*")
    
    If my_FileNameLong <> False Then
        Workbooks.Open Filename:=my_FileNameLong, ReadOnly:=True
    End If
    
    'Определяем короткое имя файла с операциями, чтобы потом (после копирования данных) его закрыть по имени
    my_FileNameShort = ActiveWorkbook.Name
    
    'Определяем имя закладки, с которой будем брать информацию
    ListName = InputBox("Введите имя листа, с которого берём информацию")
    
    Worksheets(ListName).Activate
    
    'Работаем с информацией на выбранной закладке:
    'Определяем последнюю строку на выбранном листе в файле-источнике
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 4 To FinalRow
        'Sheets(ListName).Range("aw" & i & ":cj" & i).Copy wb.Sheets("CAPEX").Range("a" & Rows.Count).End(xlUp).Offset(1)
        Sheets(ListName).Range("aw" & i & ":cj" & i).Copy
        wb.Sheets("CAPEX").Range("a" & Rows.Count + 1 & ":an").PasteSpecial xlPasteValues
    Next i
    
    Application.CutCopyMode = False
    
    'Закрываем ранее открытую книгу, из которой предварительно забрали все данные
    Workbooks(my_FileNameShort).Close SaveChanges:=False
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub
vba
Martin Hope
2b4fITin
Asked: 2021-11-18 04:23:12 +0000 UTC
  • 0

Column1 具有重复值。Column2 具有其他值。对于column1中的重复值，将column2中的对应值逐列展开。我附上截图（比文字更清晰）。

在此处输入图像描述

我写了一些东西-))，但它的工作非常笨拙..认为它不起作用！

    Sub EqualValFromRowToColumn2()
    
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
        
        Dim rng As Range, wb As Workbook
        Dim Lastrow As Long
        Set wb = ActiveWorkbook
        
        Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
                
        Set rng = Range([a1], Range("a" & Rows.Count).End(xlUp))
                
        For i = 1 To Lastrow
            If Cells(i + 1, 1).Value = Cells(i, 1) Then
                Cells(i + 1, 3).Value = 1
            ElseIf Cells(i + 1, 1).Value <> Cells(i, 1) Then
                Cells(i + 1, 3).Value = 0
            End If
        Next i
        
        For j = 1 To Lastrow
            If Cells(j, 3).Value = 1 Then
                Cells(j, 4).Value = Cells(j, 3).Value + Cells(j - 1, 4).Value
            ElseIf Cells(j, 3).Value = 0 Then
                Cells(j, 4).Value = Cells(j, 3).Value
            End If
        Next j
        
        For k = 1 To Lastrow
            If Cells(k + 1, 1).Value = Cells(k, 1) Then
                Cells(k + 1, 5).Value = Cells(k, 2).Value & ";" & Cells(k + 1, 2).Value & ";" & Cells(k + 2, 2).Value & ";" & Cells(k + 3, 2).Value & ";" & Cells(k + 4, 2).Value & ";" & Cells(k + 5, 2).Value
            End If
        Next k
        
    .ScreenUpdating = True
    .EnableEvents = True
    
    End With
    
End Sub

告诉我狗能翻找什么？

vba
Martin Hope
2b4fITin
Asked: 2020-03-11 00:37:54 +0000 UTC
  • 0

同事！有一个代码：

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    Call ThisWorkbook.SaveAs("C:\_Temp\Test2\Version\Test2_" & Format(Now(), "DDMMYY") & "_" & Format(Time(), "hhmmss"), xlWorkbookDefault)
    Application.DisplayAlerts = True
End Sub

我非常希望自动执行此操作，而不是硬编码路径“C:_Temp\Test2\Version\Test2_”（可能以某种方式使用 ThisWorkbook.Path），因此此代码对于任何需要保存的新书都是通用的关闭文件时复制到指定的文件夹（复制此通用代码+创建了该文件所在的文件夹“..\Version..”，仅此而已）。也就是说，在文件夹“..\Version..”中，您将获得类似日志的内容...

你想要更优雅的代码 -)

还有一件事：现在 .xlsx 分辨率的文件被保存到“..\Version..”文件夹中，但必须使用 .xlsm 分辨率

vba
Martin Hope
2b4fITin
Asked: 2020-02-21 20:31:38 +0000 UTC
  • 2

同事，请告诉我： 1. 阅读 .csv（分号分隔符）。我必须使用编码，因为有一个俄语文本 2. 分组和求和 3. 而不是总和，我得到连接

问题：我们需要一个普通的和（.csv = https://transfiles.ru/kq9g5），（.xlsx = https://transfiles.ru/5t38n）而不是串联

清单：

import xlwt
import xlrd
import csv
import codecs
import openpyxl
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

df = pd.read_csv('C:\py3\Test1\Test2.csv', sep=';', encoding = "866")
df.head(10)
#Создаём файл Pivot.xlsx
df1 = df.groupby('Global Dimension 2 Code')['Amount'].sum()
df1.to_csv('C:\py3\Test1\Pivot.csv')
df11 = pd.read_csv('C:\py3\Test1\Pivot.csv')
w11 = pd.ExcelWriter('C:\py3\Test1\Pivot.xlsx')
df11.to_excel(w11, sheet_name='Pivot', index=False, engine='xlsxwriter')
w11.save()
python