これは「鳩でも分かるC#」へのアクセス解析の結果です。これをClosedXMLで加工して様々な観点で解析してみることにします。
まずはアクセスが多い順に各ページを並べ替えてみます。
そのまえにデータを管理するクラスを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
public class Data { public Data() { } public Data(string date, string time, string iP, string url, string referer) { Date = date; Time = time; IP = iP; Url = url; Referer = referer; } public string Date { get; protected set; } public string Time { get; protected set; } public string IP { get; protected set; } public string Url { get; protected set; } public string Referer { get; protected set; } } |
そして各セルのデータを読み取ってDataオブジェクトに格納し、Dataオブジェクトのリストを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
using ClosedXML.Excel; using System.IO; public partial class Form1 : Form { List<Data> GetDataFromExcel(string path) { List<Data> datas = new List<Data>(); // Excelファイルを開く using(var workbook = new XLWorkbook(path)) { IXLWorksheet worksheet = workbook.Worksheet("アクセス"); // 位置を指定してセルを取得する int lastRow = worksheet.LastCellUsed().Address.RowNumber; for(int i = 2; i <= lastRow; i++) { try { string date = worksheet.Cell(i, "A").GetString(); string time = worksheet.Cell(i, "B").GetString(); string iP = worksheet.Cell(i, "C").GetString(); string url = worksheet.Cell(i, "D").GetString(); string referer = worksheet.Cell(i, "E").GetString(); // worksheet.Cell(i, "B").GetString();を実行すると // 時刻のまえに"1899/12/30"がくっつくのでこれを削除する time = time.Replace("1899/12/30 ", ""); datas.Add(new Data(date, time, iP, url, referer)); } catch { break; } } } return datas; } } |
あとはこれを並べ替えればデータを加工することができます。
まずはアクセスが多いページ順に並び替えてみましょう。アクセス日時やIPアドレスもわかります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
public partial class Form1 : Form { void OutputOrderByDescendingUrl(string excelFilePath, string outputExcelFilePath) { List<Data> datas = GetDataFromExcel(excelFilePath); var query = datas.GroupBy(x => x.Url).ToList(); query = query.OrderByDescending(x => x.Count()).ToList(); using(XLWorkbook newWorkbook = new XLWorkbook()) { // ワークシートを追加する IXLWorksheet newWorksheet = newWorkbook.Worksheets.Add("解析結果"); newWorksheet.Cell(1, "A").Value = "URL"; newWorksheet.Cell(1, "B").Value = "アクセス数"; newWorksheet.Cell(1, "C").Value = "日"; newWorksheet.Cell(1, "D").Value = "時刻"; newWorksheet.Cell(1, "E").Value = "IP"; newWorksheet.Cell(1, "F").Value = "アクセス元"; int row = 2; foreach(var group in query) { int startRow = row; foreach(Data data in group) { newWorksheet.Cell(row, "C").Value = data.Date; newWorksheet.Cell(row, "D").Value = data.Time; newWorksheet.Cell(row, "E").Value = data.IP; newWorksheet.Cell(row, "F").Value = data.Referer; row++; } int endRow = row - 1; newWorksheet.Range(startRow, 1, endRow, 1).Merge(); newWorksheet.Cell(startRow, "A").Value = group.Key; newWorksheet.Cell(startRow, "A").Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; newWorksheet.Cell(startRow, "A").Style.Alignment.WrapText = true; newWorksheet.Range(startRow, 2, endRow, 2).Merge(); newWorksheet.Cell(startRow, "B").Value = group.Count(); newWorksheet.Cell(startRow, "B").Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; } // ワークブックを保存する newWorkbook.SaveAs(outputExcelFilePath); } } } |
次は単にアクセスが多いページ順に並び替え、アクセス数だけ表示させます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
public partial class Form1 : Form { void OutputOrderByDescendingUrl2(string excelFilePath, string outputExcelFilePath) { List<Data> datas = GetDataFromExcel(excelFilePath); var query = datas.GroupBy(x => x.Url).ToList(); query = query.OrderByDescending(x => x.Count()).ToList(); using(XLWorkbook newWorkbook = new XLWorkbook()) { IXLWorksheet newWorksheet = newWorkbook.Worksheets.Add("解析結果"); newWorksheet.Cell(1, "A").Value = "URL"; newWorksheet.Cell(1, "B").Value = "アクセス数"; int row = 2; foreach(var group in query) { newWorksheet.Cell(row, "A").Value = group.Key; newWorksheet.Cell(row, "B").Value = group.Count(); row++; } newWorkbook.SaveAs(outputExcelFilePath); } } } |
これをみると
https://lets-csharp.com/excel-edit-cs/やhttps://lets-csharp.com/how-to-use-tesseract/へのアクセスが多いことがわかります。
次にIPアドレスで並び替えてみましょう。何度もこのサイトにアクセスしている人がどれくらいいるかがわかります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
public partial class Form1 : Form { void OutputOrderByDescendingUrlIPAdress(string excelFilePath, string outputExcelFilePath) { List<Data> datas = GetDataFromExcel(excelFilePath); using(XLWorkbook newWorkbook = new XLWorkbook()) { IXLWorksheet newWorksheet = newWorkbook.Worksheets.Add("解析結果"); newWorksheet.Cell(1, "A").Value = "URL"; newWorksheet.Cell(1, "B").Value = "アクセス数"; newWorksheet.Cell(1, "C").Value = "IP"; newWorksheet.Cell(1, "D").Value = "日"; newWorksheet.Cell(1, "E").Value = "時刻"; newWorksheet.Cell(1, "F").Value = "アクセス元"; var query1 = datas.GroupBy(x => x.Url).ToList(); query1 = query1.OrderByDescending(x => x.Count()).ToList(); int row = 2; foreach(var group1 in query1) { string url = group1.Key; var query2 = group1.GroupBy(x => x.IP).ToList(); query2 = query2.OrderByDescending(x => x.Count()).ToList(); int startUrl = row; foreach(var group2 in query2) { string ip = group2.Key; int startIp = row; foreach(Data data in group2) { newWorksheet.Cell(row, "D").Value = data.Date; newWorksheet.Cell(row, "E").Value = data.Time; newWorksheet.Cell(row, "F").Value = data.Referer; row++; } int endIp = row - 1; newWorksheet.Range(startIp, 3, endIp, 3).Merge(); newWorksheet.Cell(startIp, "C").Value = group2.Key; newWorksheet.Cell(startIp, "C").Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; } int endUrl = row - 1; newWorksheet.Range(startUrl, 1, endUrl, 1).Merge(); newWorksheet.Cell(startUrl, "A").Value = group1.Key; newWorksheet.Cell(startUrl, "A").Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; newWorksheet.Range(startUrl, 2, endUrl, 2).Merge(); newWorksheet.Cell(startUrl, "B").Value = group1.Count(); newWorksheet.Cell(startUrl, "B").Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; } newWorkbook.SaveAs(outputExcelFilePath); } } } |
次は単にアクセスが多い順に並び替え、アクセス数だけ表示させます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
public partial class Form1 : Form { void OutputOrderByDescendingUrlIPAdress2(string excelFilePath, string outputExcelFilePath) { List<Data> datas = GetDataFromExcel(excelFilePath); using(XLWorkbook newWorkbook = new XLWorkbook()) { IXLWorksheet newWorksheet = newWorkbook.Worksheets.Add("解析結果"); newWorksheet.Cell(1, "A").Value = "URL"; newWorksheet.Cell(1, "B").Value = "アクセス数"; newWorksheet.Cell(1, "C").Value = "IP"; newWorksheet.Cell(1, "D").Value = "アクセス数"; var query1 = datas.GroupBy(x => x.Url).ToList(); query1 = query1.OrderByDescending(x => x.Count()).ToList(); int row = 2; foreach(var group1 in query1) { string url = group1.Key; var query2 = group1.GroupBy(x => x.IP).ToList(); query2 = query2.OrderByDescending(x => x.Count()).ToList(); int startRow = row; foreach(var group2 in query2) { newWorksheet.Cell(row, "C").Value = group2.Key; newWorksheet.Cell(row, "D").Value = group2.Count(); row++; } int endRow = row - 1; newWorksheet.Range(startRow, 1, endRow, 1).Merge(); newWorksheet.Cell(startRow, 1).Value = group1.Key; newWorksheet.Cell(startRow, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; newWorksheet.Range(startRow, 2, endRow, 2).Merge(); newWorksheet.Cell(startRow, 2).Value = group1.Count(); newWorksheet.Cell(startRow, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; } newWorkbook.SaveAs(outputExcelFilePath); } } } |
次はアクセスされたurlとアクセス回数、IPアドレス、アクセス回数のみを表示するメソッドです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
public partial class Form1 : Form { void OutputOrderByDescendingUrlIPAdress(string excelFilePath, string outputExcelFilePath) { List<Data> datas = GetDataFromExcel(excelFilePath); using(XLWorkbook newWorkbook = new XLWorkbook()) { IXLWorksheet newWorksheet = newWorkbook.Worksheets.Add("解析結果"); newWorksheet.Cell(1, "A").Value = "URL"; newWorksheet.Cell(1, "B").Value = "アクセス数"; newWorksheet.Cell(1, "C").Value = "IP"; newWorksheet.Cell(1, "D").Value = "日"; newWorksheet.Cell(1, "E").Value = "時刻"; newWorksheet.Cell(1, "F").Value = "アクセス元"; var query1 = datas.GroupBy(x => x.Url).ToList(); query1 = query1.OrderByDescending(x => x.Count()).ToList(); int row = 2; foreach(var group1 in query1) { string url = group1.Key; var query2 = group1.GroupBy(x => x.IP).ToList(); query2 = query2.OrderByDescending(x => x.Count()).ToList(); int startUrl = row; foreach(var group2 in query2) { string ip = group2.Key; int startIp = row; foreach(Data data in group2) { newWorksheet.Cell(row, "D").Value = data.Date; newWorksheet.Cell(row, "E").Value = data.Time; newWorksheet.Cell(row, "F").Value = data.Referer; row++; } int endIp = row - 1; newWorksheet.Range(startIp, 3, endIp, 3).Merge(); newWorksheet.Cell(startIp, "C").Value = group2.Key; newWorksheet.Cell(startIp, "C").Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; } int endUrl = row - 1; newWorksheet.Range(startUrl, 1, endUrl, 1).Merge(); newWorksheet.Cell(startUrl, "A").Value = group1.Key; newWorksheet.Cell(startUrl, "A").Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; newWorksheet.Cell(startUrl, "A").Style.Alignment.WrapText = true; newWorksheet.Range(startUrl, 2, endUrl, 2).Merge(); newWorksheet.Cell(startUrl, "B").Value = group1.Count(); newWorksheet.Cell(startUrl, "B").Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; } newWorkbook.SaveAs(outputExcelFilePath); } } } |
同じページに同じユーザーが複数回アクセスしていることがわかります。
以下は上記の簡略版で、urlとIPアドレス、アクセス回数のみを表示するメソッドです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
public partial class Form1 : Form { void OutputOrderByDescendingUrlIPAdress2(string excelFilePath, string outputExcelFilePath) { List<Data> datas = GetDataFromExcel(excelFilePath); using(XLWorkbook newWorkbook = new XLWorkbook()) { IXLWorksheet newWorksheet = newWorkbook.Worksheets.Add("解析結果"); newWorksheet.Cell(1, "A").Value = "URL"; newWorksheet.Cell(1, "B").Value = "アクセス数"; newWorksheet.Cell(1, "C").Value = "IP"; newWorksheet.Cell(1, "D").Value = "アクセス数"; var query1 = datas.GroupBy(x => x.Url).ToList(); query1 = query1.OrderByDescending(x => x.Count()).ToList(); int row = 2; foreach(var group1 in query1) { string url = group1.Key; var query2 = group1.GroupBy(x => x.IP).ToList(); query2 = query2.OrderByDescending(x => x.Count()).ToList(); int startRow = row; foreach(var group2 in query2) { newWorksheet.Cell(row, "C").Value = group2.Key; newWorksheet.Cell(row, "D").Value = group2.Count(); row++; } int endRow = row - 1; newWorksheet.Range(startRow, 1, endRow, 1).Merge(); newWorksheet.Cell(startRow, 1).Value = group1.Key; newWorksheet.Cell(startRow, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; newWorksheet.Range(startRow, 2, endRow, 2).Merge(); newWorksheet.Cell(startRow, 2).Value = group1.Count(); newWorksheet.Cell(startRow, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; } newWorkbook.SaveAs(outputExcelFilePath); } } } |
