Excel情報出力(Claude)
Sub AnalyzeSheetStructure()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim output As String
Dim lastRow As Long
Dim lastCol As Long
Dim i As Long, j As Long
Dim filePath As String
' 出力先ファイルパス（Excelファイルと同じフォルダに出力）
filePath = ThisWorkbook.Path & "\sheet_analysis.txt"
Dim fileNo As Integer
fileNo = FreeFile
Open filePath For Output As #fileNo
Print #fileNo, "=========================================="
Print #fileNo, "Excelファイル構造分析レポート"
Print #fileNo, "ファイル名: " & ThisWorkbook.Name
Print #fileNo, "出力日時: " & Now()
Print #fileNo, "=========================================="
Print #fileNo, ""
Print #fileNo, "シート数: " & ThisWorkbook.Sheets.Count
Print #fileNo, ""
' 全シートをループ
For Each ws In ThisWorkbook.Worksheets
Print #fileNo, "=========================================="
Print #fileNo, "【シート名】" & ws.Name
Print #fileNo, "=========================================="
' 使用範囲を取得
lastRow = ws.UsedRange.Rows.Count + ws.UsedRange.Row - 1
lastCol = ws.UsedRange.Columns.Count + ws.UsedRange.Column - 1
Print #fileNo, "使用範囲: " & ws.UsedRange.Address
Print #fileNo, "最終行: " & lastRow & " / 最終列: " & lastCol & _
" (" & ColNumToLetter(lastCol) & ")"
Print #fileNo, ""
' --- セル結合情報 ---
Print #fileNo, "--- 結合セル一覧 ---"
Dim mergeArea As Range
Dim mergeCount As Long
mergeCount = 0
For Each mergeArea In ws.UsedRange.MergeAreas
If mergeArea.Count > 1 Then
mergeCount = mergeCount + 1
Print #fileNo, " " & mergeArea.Address & _
" (値: """ & CStr(mergeArea.Cells(1, 1).Value) & """)"
End If
Next mergeArea
If mergeCount = 0 Then Print #fileNo, " (結合セルなし)"
Print #fileNo, ""
' --- 全セルの値一覧 ---
Print #fileNo, "--- セル値一覧（空白セル除く）---"
Print #fileNo, String(60, "-")
Print #fileNo, PadRight("アドレス", 8) & PadRight("行", 5) & _
PadRight("列", 5) & PadRight("列名", 6) & "値"
Print #fileNo, String(60, "-")
For i = ws.UsedRange.Row To lastRow
For j = ws.UsedRange.Column To lastCol
Dim c As Range
Set c = ws.Cells(i, j)
Dim cellVal As String
cellVal = CStr(c.Value)
' 空白・空文字は除外
If Trim(cellVal) <> "" Then
' 改行を置換して1行に収める
cellVal = Replace(cellVal, Chr(10), " ↵ ")
cellVal = Replace(cellVal, Chr(13), "")
' 長い場合は切り詰め
If Len(cellVal) > 60 Then
cellVal = Left(cellVal, 57) & "..."
End If
Print #fileNo, PadRight(c.Address(False, False), 8) & _
PadRight(CStr(i), 5) & _
PadRight(CStr(j), 5) & _
PadRight(ColNumToLetter(j), 6) & _
cellVal
End If
Next j
Next i
Print #fileNo, ""
' --- 行ごとのサマリー（どの列に値があるか）---
Print #fileNo, "--- 行ごとのデータ分布サマリー ---"
For i = ws.UsedRange.Row To lastRow
Dim rowSummary As String
rowSummary = ""
For j = ws.UsedRange.Column To lastCol
If Trim(CStr(ws.Cells(i, j).Value)) <> "" Then
rowSummary = rowSummary & ColNumToLetter(j) & " "
End If
Next j
If rowSummary <> "" Then
Print #fileNo, " 行" & PadRight(CStr(i), 4) & ": [" & Trim(rowSummary) & "]"
End If
Next i
Print #fileNo, ""
Next ws
Close #fileNo
MsgBox "分析完了！" & Chr(10) & filePath & Chr(10) & "に出力しました。", vbInformation
End Sub
' 列番号をアルファベットに変換（例: 1→A, 27→AA）
Function ColNumToLetter(colNum As Long) As String
Dim result As String
Dim n As Long
n = colNum
Do While n > 0
Dim rem As Long
rem = (n - 1) Mod 26
result = Chr(65 + rem) & result
n = (n - 1) \ 26
Loop
ColNumToLetter = result
End Function
' 文字列を指定幅で右パディング
Function PadRight(s As String, width As Long) As String
PadRight = s & Space(Application.Max(0, width - Len(s)))
End Function
Excel情報出力(ChatGPT)
Option Explicit
Sub DumpWorkbookCellInfo()
Dim ws As Worksheet
Dim outWs As Worksheet
Dim r As Long
Dim cell As Range
Dim usedRng As Range
Dim mergedAddress As String
Dim commentText As String
Dim formulaText As String
Dim displayText As String
Dim rawValue As String
Dim isMerged As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
' 出力用シートを作り直す
On Error Resume Next
ThisWorkbook.Worksheets("CellDump").Delete
On Error GoTo 0
Set outWs = ThisWorkbook.Worksheets.Add
outWs.Name = "CellDump"
' ヘッダ
r = 1
With outWs
.Cells(r, 1).Value = "SheetName"
.Cells(r, 2).Value = "CellAddress"
.Cells(r, 3).Value = "DisplayText"
.Cells(r, 4).Value = "RawValue"
.Cells(r, 5).Value = "Formula"
.Cells(r, 6).Value = "NumberFormat"
.Cells(r, 7).Value = "HasComment"
.Cells(r, 8).Value = "CommentText"
.Cells(r, 9).Value = "IsMerged"
.Cells(r, 10).Value = "MergedArea"
.Cells(r, 11).Value = "Row"
.Cells(r, 12).Value = "Column"
End With
r = r + 1
' 全シート走査
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> outWs.Name Then
On Error Resume Next
Set usedRng = ws.UsedRange
On Error GoTo 0
If Not usedRng Is Nothing Then
For Each cell In usedRng.Cells
' 完全空白はスキップ
If Len(cell.Formula) > 0 Or Len(cell.Text) > 0 Then
' 表示文字列
displayText = cell.Text
' 生の値
On Error Resume Next
rawValue = CStr(cell.Value)
On Error GoTo 0
' 数式
If cell.HasFormula Then
formulaText = cell.Formula
Else
formulaText = ""
End If
' コメント
commentText = ""
If Not cell.Comment Is Nothing Then
commentText = cell.Comment.Text
End If
' 結合セル
If cell.MergeCells Then
isMerged = "Yes"
mergedAddress = cell.MergeArea.Address(False, False)
Else
isMerged = "No"
mergedAddress = ""
End If
' 出力
outWs.Cells(r, 1).Value = ws.Name
outWs.Cells(r, 2).Value = cell.Address(False, False)
outWs.Cells(r, 3).Value = displayText
outWs.Cells(r, 4).Value = rawValue
outWs.Cells(r, 5).Value = formulaText
outWs.Cells(r, 6).Value = cell.NumberFormat
outWs.Cells(r, 7).Value = IIf(commentText <> "", "Yes", "No")
outWs.Cells(r, 8).Value = commentText
outWs.Cells(r, 9).Value = isMerged
outWs.Cells(r, 10).Value = mergedAddress
outWs.Cells(r, 11).Value = cell.Row
outWs.Cells(r, 12).Value = cell.Column
r = r + 1
End If
Next cell
End If
Set usedRng = Nothing
End If
Next ws
' 見やすく整形
With outWs
.Rows(1).Font.Bold = True
.Columns("A:L").EntireColumn.AutoFit
.Range("A1:L1").AutoFilter
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "セル情報の出力が完了しました。シート 'CellDump' を確認してください。", vbInformation
End Sub
Gemini 3 AWS Signature V4 BeanShell 実装
// ==========================================
// 設定項目
// ==========================================
String accessKey = "YOUR_ACCESS_KEY";
String secretKey = "YOUR_SECRET_KEY";
String region = "ap-northeast-1";
String service = "s3";
String method = "GET";
String host = "example-bucket.s3.ap-northeast-1.amazonaws.com";
String uri = "/";
String query = ""; // クエリがある場合は "param1=value1" (要URLエンコード)
String payload = ""; // ボディ。GETなら空文字
try {
// ------------------------------------------
// 1. 日付の準備 (ISO8601 Basic Format)
// ------------------------------------------
java.util.Date now = new java.util.Date();
java.text.SimpleDateFormat amzDateFormat = new java.text.SimpleDateFormat("yyyyMMdd'T'HHmmss'Z'");
amzDateFormat.setTimeZone(java.util.TimeZone.getTimeZone("UTC"));
String amzDate = amzDateFormat.format(now); // 例: 20231005T120000Z
java.text.SimpleDateFormat dateStampFormat = new java.text.SimpleDateFormat("yyyyMMdd");
dateStampFormat.setTimeZone(java.util.TimeZone.getTimeZone("UTC"));
String dateStamp = dateStampFormat.format(now); // 例: 20231005
// ------------------------------------------
// 2. Payload Hash (SHA-256) の計算
// ------------------------------------------
java.security.MessageDigest md = java.security.MessageDigest.getInstance("SHA-256");
md.update(payload.getBytes("UTF-8"));
byte payloadHashBytes = md.digest();
java.lang.StringBuilder sb = new java.lang.StringBuilder();
for (byte b : payloadHashBytes) {
sb.append(String.format("%02x", b));
}
String payloadHash = sb.toString();
// ------------------------------------------
// 3. Canonical Request の作成
// ------------------------------------------
// ヘッダーはアルファベット順にソート済みである前提
String canonicalHeaders = "host:" + host + "\n" +
"x-amz-content-sha256:" + payloadHash + "\n" +
"x-amz-date:" + amzDate + "\n";
String signedHeaders = "host;x-amz-content-sha256;x-amz-date";
String canonicalRequest = method + "\n" +
uri + "\n" +
query + "\n" +
canonicalHeaders + "\n" +
signedHeaders + "\n" +
payloadHash;
// Canonical Request のハッシュ化
md.reset();
md.update(canonicalRequest.getBytes("UTF-8"));
byte canonicalRequestHashBytes = md.digest();
sb = new java.lang.StringBuilder();
for (byte b : canonicalRequestHashBytes) {
sb.append(String.format("%02x", b));
}
String canonicalRequestHash = sb.toString();
// ------------------------------------------
// 4. String to Sign の作成
// ------------------------------------------
String algorithm = "AWS4-HMAC-SHA256";
String credentialScope = dateStamp + "/" + region + "/" + service + "/aws4_request";
String stringToSign = algorithm + "\n" +
amzDate + "\n" +
credentialScope + "\n" +
canonicalRequestHash;
// ------------------------------------------
// 5. 署名キー (Signing Key) の生成
// kSecret = "AWS4" + kSecret
// kDate = Hmac(dateStamp, kSecret)
// kRegion = Hmac(region, kDate)
// kService = Hmac(service, kRegion)
// kSigning = Hmac("aws4_request", kService)
// ------------------------------------------
String macAlgo = "HmacSHA256";
javax.crypto.Mac mac = javax.crypto.Mac.getInstance(macAlgo);
// kDate
byte kSecret = ("AWS4" + secretKey).getBytes("UTF-8");
mac.init(new javax.crypto.spec.SecretKeySpec(kSecret, macAlgo));
byte kDate = mac.doFinal(dateStamp.getBytes("UTF-8"));
// kRegion
mac.init(new javax.crypto.spec.SecretKeySpec(kDate, macAlgo));
byte kRegion = mac.doFinal(region.getBytes("UTF-8"));
// kService
mac.init(new javax.crypto.spec.SecretKeySpec(kRegion, macAlgo));
byte kService = mac.doFinal(service.getBytes("UTF-8"));
// kSigning
mac.init(new javax.crypto.spec.SecretKeySpec(kService, macAlgo));
byte kSigning = mac.doFinal("aws4_request".getBytes("UTF-8"));
// ------------------------------------------
// 6. 最終署名 (Signature) の計算
// ------------------------------------------
mac.init(new javax.crypto.spec.SecretKeySpec(kSigning, macAlgo));
byte signatureBytes = mac.doFinal(stringToSign.getBytes("UTF-8"));
sb = new java.lang.StringBuilder();
for (byte b : signatureBytes) {
sb.append(String.format("%02x", b));
}
String signature = sb.toString();
// ------------------------------------------
// 7. Authorization ヘッダーの組み立て
// ------------------------------------------
String authorizationHeader = algorithm + " " +
"Credential=" + accessKey + "/" + credentialScope + ", " +
"SignedHeaders=" + signedHeaders + ", " +
"Signature=" + signature;
// ==========================================
// 出力
// ==========================================
print("--- Result ---");
print("x-amz-date: " + amzDate);
print("x-amz-content-sha256: " + payloadHash);
print("Authorization: " + authorizationHeader);
// JMeter用変数セット (必要な場合)
// vars.put("aws_x_amz_date", amzDate);
// vars.put("aws_x_amz_content_sha256", payloadHash);
// vars.put("aws_authorization", authorizationHeader);
} catch (Exception e) {
print("Error: " + e);
e.printStackTrace();
}
Claude4.5 AWS test
#!/usr/bin/env python3
# aws_sign.py - AWS Signature V4 署名生成
import sys
import hmac
import hashlib
def sign(key, msg):
return hmac.new(key, msg.encode('utf-8'), hashlib.sha256).digest()
def get_signature_key(secret, date, region, service):
k_date = sign(('AWS4' + secret).encode('utf-8'), date)
k_region = sign(k_date, region)
k_service = sign(k_region, service)
k_signing = sign(k_service, 'aws4_request')
return k_signing
def main():
if len(sys.argv) != 6:
print("Usage: aws_sign.py <secret> <date> <region> <service> <string_to_sign>", file=sys.stderr)
sys.exit(1)
secret = sys.argv[1]
date = sys.argv[2]
region = sys.argv[3]
service = sys.argv[4]
string_to_sign = sys.argv[5]
signing_key = get_signature_key(secret, date, region, service)
signature = hmac.new(signing_key, string_to_sign.encode('utf-8'), hashlib.sha256).hexdigest()
print(signature)
if __name__ == '__main__':
main()
xml形式更新(GPT-5)
#!/bin/bash
# 使い方:
# ./replace_defaults.sh input.xml output.xml "REST_URL値" "ClientID値" "SecretID値" "UserID値" "Password値"
if [ $# -ne 7 ]; then
echo "Usage: $0 input.xml output.xml REST_URL REST_ClientID REST_SecretID UserID Password"
exit 1
fi
INPUT_FILE="$1"
OUTPUT_FILE="$2"
REST_URL="$3"
REST_ClientID="$4"
REST_SecretID="$5"
USER_ID="$6"
PASSWORD="$7"
sed -E \
-e "s@(name=\"REST_URL\"[^>]*default=\")[^\"]*@\1${REST_URL}@" \
-e "s@(name=\"REST_ClientID\"[^>]*default=\")[^\"]*@\1${REST_ClientID}@" \
-e "s@(name=\"REST_SecretID\"[^>]*default=\")[^\"]*@\1${REST_SecretID}@" \
-e "s@(name=\"UserID\"[^>]*default=\")[^\"]*@\1${USER_ID}@" \
-e "s@(name=\"Password\"[^>]*default=\")[^\"]*@\1${PASSWORD}@" \
"$INPUT_FILE" > "$OUTPUT_FILE"
awk(grok3)
#!/bin/bash
# 入力ファイルと出力ファイルを指定
input_file=\$1
output_file=\$2
# 入力ファイルが存在するかチェック
if [ ! -f "$input_file" ]; then
echo "エラー: 入力ファイル '$input_file' が見つかりません。"
exit 1
fi
# 出力ファイルが指定されていない場合はデフォルトで一時ファイルを使用
if [ -z "$output_file" ]; then
output_file="${input_file}_modified.csv"
fi
# 一時ファイルを作成
temp_file=$(mktemp)
# 各行の先頭に3つのカラム（例: "A","B","C"）を追加
while IFS= read -r line; do
echo "A,B,C,$line"
done < "$input_file" > "$temp_file"
# 一時ファイルを最終的な出力ファイルに移動
mv "$temp_file" "$output_file"
echo "処理が完了しました。結果は '$output_file' に保存されています。"
