Imports ExcelCreator
Imports System.IO

'**************************************************************************
' Name       :MyExcel
'--------------------------------------------------------------------------
' Comment    :ExcelCreator連携クラス
'--------------------------------------------------------------------------
' Design On  :2013/01/31
'        By  :NCS)T.Kikuchi
'--------------------------------------------------------------------------
' Update On  :2019/08/25
'        By  :MEC)T.Hashimoto
' Comment    :
'**************************************************************************
Public Class MyExcel
    Implements IDisposable

    Private WithEvents L_XlsCreator1 As XlsCreator = Nothing

#Region "コンストラクタ／デストラクタ"
    Public Sub New()
        L_XlsCreator1 = New XlsCreator()

        ' TMPフォルダの掃除
        Dim dirInfo As DirectoryInfo = New DirectoryInfo(MyProperty.TEMPフォルダパス)
        Dim fileInfo() As FileInfo = dirInfo.GetFiles()
        For Each file As FileInfo In fileInfo
            If file.Extension = ".bak" Then
                ' 2時間以上前のファイルは削除する。
                If (DateTime.Now - file.LastWriteTime).TotalHours > 2 Then
                    Try
                        file.Delete()
                    Catch ex As Exception
                        ' ここのエラーは無視する。
                    End Try
                End If
            End If
        Next
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        L_XlsCreator1.Dispose()
    End Sub
#End Region

#Region "Output集計結果報告書"
    Public Sub Output集計結果報告書(ByVal saveFilePath As String, ByVal baseFilePath As String, ByVal ds As DataSet1, ByVal votePlace As String)
        With L_XlsCreator1

            'Excelオープン
            Dim openSts As Integer = .OpenBook(saveFilePath, baseFilePath)
            If openSts < 0 Then
                Throw New Exception(MyMessage.管理者画面.テンプレートファイル取得エラー)
            End If

            '検索キーワードの取得
            Dim posNow As System.Drawing.Size = .GetVarNamePos("**作成日時", 0)
            Dim pos選挙名称 As System.Drawing.Size = .GetVarNamePos("**選挙名称", 0)
            'Dim pos選挙コード As System.Drawing.Size = .GetVarNamePos("**選挙コード", 0)
            'Dim pos選挙コード名称 As System.Drawing.Size = .GetVarNamePos("**選挙コード名称", 0)
            'Dim pos投票所コード As System.Drawing.Size = .GetVarNamePos("**投票所コード", 0)
            Dim pos投票所名 As System.Drawing.Size = .GetVarNamePos("**投票所名称", 0)
            Dim posNo As System.Drawing.Size = .GetVarNamePos("**No", 0)
            Dim posKoho As System.Drawing.Size = .GetVarNamePos("**候補者名称", 0)
            Dim posCnt As System.Drawing.Size = .GetVarNamePos("**投票数", 0)
            Dim pos得票順 As System.Drawing.Size = .GetVarNamePos("**番号", 0)
            Dim pos出力順 As System.Drawing.Size = .GetVarNamePos("**出力順", 0)

            '明細行の開始行
            Dim startCnt As Integer = posNo.Height

            Dim baseSheetNo As Integer = 0
            Dim i As Integer = 0
            Dim now As String = DateTime.Now.ToString("yyyy年MM月dd日 HH時mm分")
            .SheetNo = 0
            For Each row As DataSet1.選挙MRow In ds.選挙M
                'シートをコピー
                .CopySheet(baseSheetNo, baseSheetNo + i + 1, row.選挙名称)
                'カレントシートをコピーしたシートに変更
                .SheetNo = baseSheetNo + i + 1

                '投票所名称
                .Pos(pos投票所名.Width, pos投票所名.Height).Str = votePlace

                '作成日時
                .Pos(posNow.Width, posNow.Height).Str = now

                ''コード
                '.Pos(pos選挙コード.Width, pos選挙コード.Height).Str = MyProperty.システムＭ.選挙コード
                ''.Pos(pos選挙コード名称.Width, pos選挙コード名称.Height).Str = MyProperty.システムＭ.選挙コード名称
                'If Not isAll Then
                '    .Pos(pos投票所コード.Width, pos投票所コード.Height).Str = MyProperty.システムＭ.投票所コード
                'End If

                '選挙名
                .Pos(pos選挙名称.Width, pos選挙名称.Height).Str = row.選挙名称
                '得票順／届出番号順_Hashimoto
                Dim t As String = "順位"
                .Pos(pos得票順.Width, pos得票順.Height).Str = t
                t = "（得票順）"
                .Pos(pos出力順.Width, pos出力順.Height).Str = t

                ' LINQで明細データを抽出＆並び替え（白票以外）
                Dim senkyoId As String = row.選挙ID.TrimEnd
                Dim query = From n In ds.集計結果報告書T _
                          Select n _
                          Where n.選挙ID = senkyoId And n.候補者名称 IsNot MyConstant.白票名称
                          Order By n.投票数 Descending

                Dim TotalSuu As Decimal = 0D  '合計用
                Dim endCnt As Integer = 0  '最終行番号
                Dim j As Integer = 0
                Dim p As Integer = 0
                Dim q As Integer = 0

                For Each row2 As DataSet1.集計結果報告書TRow In query
                    endCnt = startCnt + j

                    '書式のコピー
                    .Pos(posNo.Width, posNo.Height, posCnt.Width, posCnt.Height).Copy()
                    .Pos(posNo.Width, endCnt).Paste()

                    '値の設定
                    .Pos(posNo.Width, endCnt).Str = j + 1  'No
                    row2.候補者名称 = Replace(row2.候補者名称, "↓", "")
                    .Pos(posKoho.Width, endCnt).Str = row2.候補者名称  '候補者名
                    .Pos(posCnt.Width, endCnt).Long = row2.投票数  '投票数

                    ' 「同数」の表示_Hashimoto
                    p = row2.投票数
                    If p = q And p <> 0 And q <> 0 Then
                        t = "同数"
                        .Pos(posCnt.Width + 1, endCnt).Str = "同数"
                        .Pos(posCnt.Width + 1, endCnt - 1).Str = "同数"
                    End If

                    TotalSuu += row2.投票数

                    j += 1

                    q = row2.投票数

                Next

                If query.Count > 0 Then
                    ' 白票データ
                    endCnt += 1
                    Dim query2 = From n In ds.集計結果報告書T _
                              Select n _
                              Where n.選挙ID = senkyoId And n.候補者名称 = MyConstant.白票名称
                    '書式のコピー
                    .Pos(posNo.Width, posNo.Height, posCnt.Width, posCnt.Height).Copy()
                    .Pos(posNo.Width, endCnt).Paste()
                    '値の設定
                    .Pos(posNo.Width, endCnt).Str = ""
                    .Pos(posKoho.Width, endCnt).Str = query2(0).候補者名称
                    .Pos(posCnt.Width, endCnt).Long = query2(0).投票数
                    TotalSuu += query2(0).投票数

                    '-----------------------------------------------------
                    '合計行
                    endCnt += 1
                    '書式のコピー
                    .Pos(posNo.Width, posNo.Height, posCnt.Width, posCnt.Height).Copy()
                    .Pos(posNo.Width, endCnt).Paste()
                    '値のセット
                    .Pos(posNo.Width, endCnt).Str = ""
                    .Pos(posKoho.Width, endCnt).Str = "合計"
                    .Pos(posCnt.Width, endCnt).Long = TotalSuu

                    '合計関数の埋め込み
                    'Dim ColNMSuu As String = ConvertToLetter(cnt.Width + 1)
                    '.Cell(ColNMSuu & (RowEnd + 1).ToString).Func( _
                    '    "=sum(" & ColNMSuu & (startCnt + 1).ToString & ":" & _
                    '    ColNMSuu & RowEnd.ToString & ")", TotalSuu)

                    '書式        
                    '.Pos(cnt.Width, cnt.Height, _
                    '              cnt.Width, RowEnd).Attr.Format = "###,###,###,###"  'カンマ区切り
                    '-----------------------------------------------------
                End If

                '罫線
                With .Pos(posNo.Width, posNo.Height, posCnt.Width, endCnt).Attr
                    .LineTop = ExcelCreator.xlLineStyle.lsNormal
                    .LineBottom = ExcelCreator.xlLineStyle.lsNormal
                    .LineLeft = ExcelCreator.xlLineStyle.lsNormal
                    .LineRight = ExcelCreator.xlLineStyle.lsNormal
                End With

                i += 1
            Next

            'ひな形シートを削除    
            .DelSheet(.SheetNo2("テンプレート"), 1)
            '保存   
            .CloseBook(True)
        End With
    End Sub

    Public Sub Output集計結果報告書_候補者ID順(ByVal saveFilePath As String, ByVal baseFilePath As String, ByVal ds As DataSet1, ByVal votePlace As String)
        With L_XlsCreator1

            'Excelオープン
            Dim openSts As Integer = .OpenBook(saveFilePath, baseFilePath)
            If openSts < 0 Then
                Throw New Exception(MyMessage.管理者画面.テンプレートファイル取得エラー)
            End If

            '検索キーワードの取得
            Dim posNow As System.Drawing.Size = .GetVarNamePos("**作成日時", 0)
            Dim pos選挙名称 As System.Drawing.Size = .GetVarNamePos("**選挙名称", 0)
            'Dim pos選挙コード As System.Drawing.Size = .GetVarNamePos("**選挙コード", 0)
            'Dim pos投票所コード As System.Drawing.Size = .GetVarNamePos("**投票所コード", 0)
            Dim pos投票所名 As System.Drawing.Size = .GetVarNamePos("**投票所名称", 0)
            Dim posNo As System.Drawing.Size = .GetVarNamePos("**No", 0)
            Dim posKoho As System.Drawing.Size = .GetVarNamePos("**候補者名称", 0)
            Dim posCnt As System.Drawing.Size = .GetVarNamePos("**投票数", 0)
            Dim pos得票順 As System.Drawing.Size = .GetVarNamePos("**番号", 0)
            Dim pos出力順 As System.Drawing.Size = .GetVarNamePos("**出力順", 0)

            '明細行の開始行
            Dim startCnt As Integer = posNo.Height

            Dim baseSheetNo As Integer = 0
            Dim i As Integer = 0
            Dim now As String = DateTime.Now.ToString("yyyy年MM月dd日 HH時mm分")
            .SheetNo = 0
            For Each row As DataSet1.選挙MRow In ds.選挙M
                'シートをコピー
                .CopySheet(baseSheetNo, baseSheetNo + i + 1, row.選挙名称)
                'カレントシートをコピーしたシートに変更
                .SheetNo = baseSheetNo + i + 1

                '投票所名称
                .Pos(pos投票所名.Width, pos投票所名.Height).Str = votePlace

                '作成日時
                .Pos(posNow.Width, posNow.Height).Str = now

                ''コード
                '.Pos(pos選挙コード.Width, pos選挙コード.Height).Str = MyProperty.システムＭ.選挙コード
                ''.Pos(pos選挙コード名称.Width, pos選挙コード名称.Height).Str = MyProperty.システムＭ.選挙コード名称
                'If Not isAll Then
                '    .Pos(pos投票所コード.Width, pos投票所コード.Height).Str = MyProperty.システムＭ.投票所コード
                'End If

                '選挙名
                .Pos(pos選挙名称.Width, pos選挙名称.Height).Str = row.選挙名称
                '得票順／届出番号順_Hashimoto
                Dim t As String = "届出番号"
                .Pos(pos得票順.Width, pos得票順.Height).Str = t
                t = "（届出順）"
                .Pos(pos出力順.Width, pos出力順.Height).Str = t

                ' LINQで明細データを抽出＆並び替え（白票以外）
                Dim senkyoId As String = row.選挙ID.TrimEnd
                Dim query = From n In ds.集計結果報告書T _
                          Select n _
                          Where n.選挙ID = senkyoId And n.候補者名称 IsNot MyConstant.白票名称
                          Order By n.候補者ID

                Dim TotalSuu As Decimal = 0D  '合計用
                Dim endCnt As Integer = 0  '最終行番号
                Dim j As Integer = 0
                For Each row2 As DataSet1.集計結果報告書TRow In query
                    endCnt = startCnt + j

                    '書式のコピー
                    .Pos(posNo.Width, posNo.Height, posCnt.Width, posCnt.Height).Copy()
                    .Pos(posNo.Width, endCnt).Paste()

                    '値の設定
                    .Pos(posNo.Width, endCnt).Str = j + 1  'No
                    row2.候補者名称 = Replace(row2.候補者名称, "↓", "")
                    .Pos(posKoho.Width, endCnt).Str = row2.候補者名称  '候補者名
                    .Pos(posCnt.Width, endCnt).Long = row2.投票数  '投票数

                    TotalSuu += row2.投票数
                    j += 1
                Next

                If query.Count > 0 Then
                    ' 白票データ
                    endCnt += 1
                    Dim query2 = From n In ds.集計結果報告書T _
                              Select n _
                              Where n.選挙ID = senkyoId And n.候補者名称 = MyConstant.白票名称
                    '書式のコピー
                    .Pos(posNo.Width, posNo.Height, posCnt.Width, posCnt.Height).Copy()
                    .Pos(posNo.Width, endCnt).Paste()
                    '値の設定
                    .Pos(posNo.Width, endCnt).Str = ""
                    .Pos(posKoho.Width, endCnt).Str = query2(0).候補者名称
                    .Pos(posCnt.Width, endCnt).Long = query2(0).投票数
                    TotalSuu += query2(0).投票数
                    '-----------------------------------------------------
                    '合計行
                    endCnt += 1
                    '書式のコピー
                    .Pos(posNo.Width, posNo.Height, posCnt.Width, posCnt.Height).Copy()
                    .Pos(posNo.Width, endCnt).Paste()
                    '値のセット
                    .Pos(posNo.Width, endCnt).Str = ""
                    .Pos(posKoho.Width, endCnt).Str = "合計"
                    .Pos(posCnt.Width, endCnt).Long = TotalSuu

                    '合計関数の埋め込み
                    'Dim ColNMSuu As String = ConvertToLetter(cnt.Width + 1)
                    '.Cell(ColNMSuu & (RowEnd + 1).ToString).Func( _
                    '    "=sum(" & ColNMSuu & (startCnt + 1).ToString & ":" & _
                    '    ColNMSuu & RowEnd.ToString & ")", TotalSuu)

                    '書式        
                    '.Pos(cnt.Width, cnt.Height, _
                    '              cnt.Width, RowEnd).Attr.Format = "###,###,###,###"  'カンマ区切り
                    '-----------------------------------------------------
                End If

                '罫線
                With .Pos(posNo.Width, posNo.Height, posCnt.Width, endCnt).Attr
                    .LineTop = ExcelCreator.xlLineStyle.lsNormal
                    .LineBottom = ExcelCreator.xlLineStyle.lsNormal
                    .LineLeft = ExcelCreator.xlLineStyle.lsNormal
                    .LineRight = ExcelCreator.xlLineStyle.lsNormal
                End With

                i += 1
            Next

            'ひな形シートを削除    
            .DelSheet(.SheetNo2("テンプレート"), 1)
            '保存   
            .CloseBook(True)
        End With
    End Sub

    Public Sub Output集計結果報告書日計(ByVal saveFilePath As String, ByVal baseFilePath As String, ByVal ds As DataSet1, ByVal isHourly As Boolean, ByVal votePlace As String)
        With L_XlsCreator1

            'Excelオープン
            Dim openSts As Integer = .OpenBook(saveFilePath, baseFilePath)
            If openSts < 0 Then
                Throw New Exception(MyMessage.管理者画面.テンプレートファイル取得エラー)
            End If

            '検索キーワードの取得
            Dim posNow As System.Drawing.Size = .GetVarNamePos("**作成日時", 0)
            Dim pos選挙名称 As System.Drawing.Size = .GetVarNamePos("**選挙名称", 0)
            Dim pos選挙コード As System.Drawing.Size = .GetVarNamePos("**選挙コード", 0)
            'Dim pos選挙コード名称 As System.Drawing.Size = .GetVarNamePos("**選挙コード名称", 0)
            'Dim pos投票所コード As System.Drawing.Size = .GetVarNamePos("**投票所コード", 0)
            Dim pos投票所名 As System.Drawing.Size = .GetVarNamePos("**投票所名称", 0)
            Dim posDate As System.Drawing.Size = .GetVarNamePos("**日付", 0)
            Dim posCnt As System.Drawing.Size = .GetVarNamePos("**投票数", 0)

            '明細行の開始行
            Dim startCnt As Integer = posDate.Height

            Dim baseSheetNo As Integer = 0
            Dim i As Integer = 0
            Dim now As String = DateTime.Now.ToString("yyyy年MM月dd日 HH時mm分")
            .SheetNo = 0
            For Each row As DataSet1.選挙MRow In ds.選挙M
                'シートをコピー
                .CopySheet(baseSheetNo, baseSheetNo + i + 1, row.選挙名称)
                'カレントシートをコピーしたシートに変更
                .SheetNo = baseSheetNo + i + 1

                '作成日時
                .Pos(posNow.Width, posNow.Height).Str = now

                'コード
                .Pos(pos選挙コード.Width, pos選挙コード.Height).Str = MyProperty.システムＭ.選挙コード
                '.Pos(pos選挙コード名称.Width, pos選挙コード名称.Height).Str = MyProperty.システムＭ.選挙コード名称
                'If Not isAll Then
                '    .Pos(pos投票所コード.Width, pos投票所コード.Height).Str = MyProperty.システムＭ.投票所コード
                'End If

                '投票所名称
                .Pos(pos投票所名.Width, pos投票所名.Height).Str = votePlace

                '選挙名
                .Pos(pos選挙名称.Width, pos選挙名称.Height).Str = row.選挙名称

                ' LINQで抽出＆並び替え
                Dim query = From n In ds.集計結果日時T _
                          Select n _
                          Where n.選挙ID = row.選挙ID.TrimEnd
                          Order By n.日時 Ascending

                Dim TotalSuu As Decimal = 0D  '合計用
                Dim endCnt As Integer = 0  '最終行番号
                Dim j As Integer = 0
                For Each row2 As DataSet1.集計結果日時TRow In query
                    endCnt = startCnt + j

                    '書式のコピー
                    .Pos(posDate.Width, posDate.Height, posCnt.Width, posCnt.Height).Copy()
                    .Pos(posDate.Width, endCnt).Paste()

                    '値の設定
                    Dim d As String = row2.日時.Substring(0, 4) + "/" + row2.日時.Substring(4, 2) + "/" + row2.日時.Substring(6, 2)
                    If (isHourly) Then
                        d += " " + row2.日時.Substring(8, 2) + ":00-" + row2.日時.Substring(8, 2) + ":59"
                    End If
                    .Pos(posDate.Width, endCnt).Str = d  '日付
                    .Pos(posCnt.Width, endCnt).Long = row2.投票数  '投票数

                    TotalSuu += row2.投票数
                    j += 1
                Next

                '-----------------------------------------------------
                '合計行
                If query.Count > 0 Then
                    endCnt += 1
                    '書式のコピー
                    .Pos(posDate.Width, posDate.Height, posCnt.Width, posCnt.Height).Copy()
                    .Pos(posDate.Width, endCnt).Paste()
                    '値のセット
                    .Pos(posDate.Width, endCnt).Str = "合計"
                    .Pos(posCnt.Width, endCnt).Long = TotalSuu
                End If

                '罫線
                With .Pos(posDate.Width, posDate.Height, posCnt.Width, endCnt).Attr
                    .LineTop = ExcelCreator.xlLineStyle.lsNormal
                    .LineBottom = ExcelCreator.xlLineStyle.lsNormal
                    .LineLeft = ExcelCreator.xlLineStyle.lsNormal
                    .LineRight = ExcelCreator.xlLineStyle.lsNormal
                End With

                i += 1
            Next

            'ひな形シートを削除    
            .DelSheet(.SheetNo2("テンプレート"), 1)
            '保存   
            .CloseBook(True)
        End With
    End Sub
#End Region

#Region "OutputM"
    Public Sub OutputシステムM(ByVal saveFilePath As String, ByVal baseFilePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1

            'Excelオープン
            Dim openSts As Integer = .OpenBook(saveFilePath, baseFilePath)
            If openSts < 0 Then
                Throw New Exception(MyMessage.管理者画面.テンプレートファイル取得エラー)
            End If

            '検索キーワードの取得
            Dim posMeisai As System.Drawing.Size = .GetVarNamePos("**データ行", 0)

            '明細行の開始行
            Dim startCnt As Integer = posMeisai.Height

            '最終列
            Dim endCol As Integer = 3
            '最終行番号
            Dim endCnt As Integer = 0
            Dim i As Integer = 0
            For Each row As DataSet1.システムMRow In ds.システムM
                endCnt = startCnt + i

                '書式のコピー
                .Pos(posMeisai.Width, posMeisai.Height, endCol, posMeisai.Height).Copy()
                .Pos(posMeisai.Width, endCnt).Paste()

                '値のセット
                .Pos(0, endCnt).Str = row.項目名.TrimEnd
                .Pos(1, endCnt).Str = row.値
                .Pos(2, endCnt).Str = row.備考
                .Pos(3, endCnt).Long = row.表示順
                i += 1
            Next

            '罫線
            With .Pos(posMeisai.Width, posMeisai.Height, endCol, endCnt).Attr
                .LineTop = ExcelCreator.xlLineStyle.lsNormal
                .LineBottom = ExcelCreator.xlLineStyle.lsNormal
                .LineLeft = ExcelCreator.xlLineStyle.lsNormal
                .LineRight = ExcelCreator.xlLineStyle.lsNormal
            End With

            '保存   
            .CloseBook(True)
        End With
    End Sub

    Public Sub Output選挙M(ByVal saveFilePath As String, ByVal baseFilePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1

            'Excelオープン
            Dim openSts As Integer = .OpenBook(saveFilePath, baseFilePath)
            If openSts < 0 Then
                Throw New Exception(MyMessage.管理者画面.テンプレートファイル取得エラー)
            End If

            '検索キーワードの取得
            Dim posMeisai As System.Drawing.Size = .GetVarNamePos("**データ行", 0)

            '明細行の開始行
            Dim startCnt As Integer = posMeisai.Height

            '最終列
            Dim endCol As Integer = 8
            '最終行番号
            Dim endCnt As Integer = 0
            Dim i As Integer = 0
            For Each row As DataSet1.選挙MRow In ds.選挙M
                endCnt = startCnt + i

                '書式のコピー
                .Pos(posMeisai.Width, posMeisai.Height, endCol, posMeisai.Height).Copy()
                .Pos(posMeisai.Width, endCnt).Paste()

                '値のセット
                .Pos(0, endCnt).Str = row.選挙ID.TrimEnd
                .Pos(1, endCnt).Str = row.選挙名称
                .Pos(2, endCnt).Long = row.表示フラグ
                .Pos(3, endCnt).Long = row.表示順
                .Pos(4, endCnt).Long = row.一覧表示パターン
                .Pos(5, endCnt).Long = row.単票表示パターン
                .Pos(6, endCnt).Str = row.予備１
                .Pos(7, endCnt).Str = row.予備２
                .Pos(8, endCnt).Str = row.予備３
                i += 1
            Next

            '罫線
            With .Pos(posMeisai.Width, posMeisai.Height, endCol, endCnt).Attr
                .LineTop = ExcelCreator.xlLineStyle.lsNormal
                .LineBottom = ExcelCreator.xlLineStyle.lsNormal
                .LineLeft = ExcelCreator.xlLineStyle.lsNormal
                .LineRight = ExcelCreator.xlLineStyle.lsNormal
            End With

            '保存   
            .CloseBook(True)
        End With
    End Sub

    Public Sub Output政党M(ByVal saveFilePath As String, ByVal baseFilePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1

            'Excelオープン
            Dim openSts As Integer = .OpenBook(saveFilePath, baseFilePath)
            If openSts < 0 Then
                Throw New Exception(MyMessage.管理者画面.テンプレートファイル取得エラー)
            End If

            '検索キーワードの取得
            Dim posMeisai As System.Drawing.Size = .GetVarNamePos("**データ行", 0)

            '明細行の開始行
            Dim startCnt As Integer = posMeisai.Height

            '最終列
            Dim endCol As Integer = 8
            '最終行番号
            Dim endCnt As Integer = 0
            Dim i As Integer = 0
            For Each row As DataSet1.政党MRow In ds.政党M
                endCnt = startCnt + i

                '書式のコピー
                .Pos(posMeisai.Width, posMeisai.Height, endCol, posMeisai.Height).Copy()
                .Pos(posMeisai.Width, endCnt).Paste()

                '値のセット
                .Pos(0, endCnt).Str = row.政党ID.TrimEnd
                .Pos(1, endCnt).Str = row.政党名称
                .Pos(2, endCnt).Str = row.政党名略称
                .Pos(3, endCnt).Long = row.表示フラグ
                .Pos(4, endCnt).Long = row.表示順
                .Pos(5, endCnt).Str = row.画像ファイル名
                .Pos(6, endCnt).Str = row.予備１
                .Pos(7, endCnt).Str = row.予備２
                .Pos(8, endCnt).Str = row.予備３
                i += 1
            Next

            '罫線
            With .Pos(posMeisai.Width, posMeisai.Height, endCol, endCnt).Attr
                .LineTop = ExcelCreator.xlLineStyle.lsNormal
                .LineBottom = ExcelCreator.xlLineStyle.lsNormal
                .LineLeft = ExcelCreator.xlLineStyle.lsNormal
                .LineRight = ExcelCreator.xlLineStyle.lsNormal
            End With

            '保存   
            .CloseBook(True)
        End With
    End Sub

    Public Sub Output候補者M(ByVal saveFilePath As String, ByVal baseFilePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1

            'Excelオープン
            Dim openSts As Integer = .OpenBook(saveFilePath, baseFilePath)
            If openSts < 0 Then
                Throw New Exception(MyMessage.管理者画面.テンプレートファイル取得エラー)
            End If

            '検索キーワードの取得
            Dim posMeisai As System.Drawing.Size = .GetVarNamePos("**データ行", 0)

            '明細行の開始行
            Dim startCnt As Integer = posMeisai.Height

            '最終列
            Dim endCol As Integer = 14
            '最終行番号
            Dim endCnt As Integer = 0
            Dim i As Integer = 0
            For Each row As DataSet1.候補者MRow In ds.候補者M
                endCnt = startCnt + i

                '書式のコピー
                .Pos(posMeisai.Width, posMeisai.Height, endCol, posMeisai.Height).Copy()
                .Pos(posMeisai.Width, endCnt).Paste()

                '値のセット
                .Pos(0, endCnt).Str = row.選挙ID.TrimEnd
                .Pos(1, endCnt).Str = row.候補者ID.TrimEnd
                .Pos(2, endCnt).Str = row.候補者名称
                .Pos(3, endCnt).Str = row.候補者名称かな
                .Pos(4, endCnt).Str = row.政党ID.TrimEnd
                .Pos(5, endCnt).Long = row.表示フラグ
                .Pos(6, endCnt).Long = row.表示順
                .Pos(7, endCnt).Str = row.画像ファイル名
                .Pos(8, endCnt).Str = row.画像ファイル名２
                .Pos(9, endCnt).Str = row.画像ファイル名３
                .Pos(10, endCnt).Str = row.候補者名称２
                .Pos(11, endCnt).Str = row.候補者名称３
                .Pos(12, endCnt).Str = row.予備１
                .Pos(13, endCnt).Str = row.予備２
                .Pos(14, endCnt).Str = row.予備３
                i += 1
            Next

            '罫線
            With .Pos(posMeisai.Width, posMeisai.Height, endCol, endCnt).Attr
                .LineTop = ExcelCreator.xlLineStyle.lsNormal
                .LineBottom = ExcelCreator.xlLineStyle.lsNormal
                .LineLeft = ExcelCreator.xlLineStyle.lsNormal
                .LineRight = ExcelCreator.xlLineStyle.lsNormal
            End With

            '保存   
            .CloseBook(True)
        End With
    End Sub

    Public Sub Output投票券T(ByVal saveFilePath As String, ByVal baseFilePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1

            'Excelオープン
            Dim openSts As Integer = .OpenBook(saveFilePath, baseFilePath)
            If openSts < 0 Then
                Throw New Exception(MyMessage.管理者画面.テンプレートファイル取得エラー)
            End If

            '検索キーワードの取得
            Dim posMeisai As System.Drawing.Size = .GetVarNamePos("**データ行", 0)

            '明細行の開始行
            Dim startCnt As Integer = posMeisai.Height

            '最終列
            Dim endCol As Integer = 7
            '最終行番号
            Dim endCnt As Integer = 0
            Dim i As Integer = 0
            For Each row As DataSet1.投票券TRow In ds.投票券T
                endCnt = startCnt + i

                '書式のコピー
                .Pos(posMeisai.Width, posMeisai.Height, endCol, posMeisai.Height).Copy()
                .Pos(posMeisai.Width, endCnt).Paste()

                '値のセット
                .Pos(0, endCnt).Str = row.投票券番号.TrimEnd
                .Pos(1, endCnt).Long = row.投票数
                .Pos(2, endCnt).Long = row.発行回数
                .Pos(3, endCnt).Str = row.投票券発行日時
                .Pos(4, endCnt).Str = row.予備１
                .Pos(5, endCnt).Str = row.予備２
                .Pos(6, endCnt).Str = row.予備３
                .Pos(7, endCnt).Str = row.発券取消日時
                i += 1
            Next

            '罫線
            With .Pos(posMeisai.Width, posMeisai.Height, endCol, endCnt).Attr
                .LineTop = ExcelCreator.xlLineStyle.lsNormal
                .LineBottom = ExcelCreator.xlLineStyle.lsNormal
                .LineLeft = ExcelCreator.xlLineStyle.lsNormal
                .LineRight = ExcelCreator.xlLineStyle.lsNormal
            End With

            '保存   
            .CloseBook(True)
        End With
    End Sub

    Public Sub Output会員M(ByVal saveFilePath As String, ByVal baseFilePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1

            'Excelオープン
            Dim openSts As Integer = .OpenBook(saveFilePath, baseFilePath)
            If openSts < 0 Then
                Throw New Exception(MyMessage.管理者画面.テンプレートファイル取得エラー)
            End If

            '検索キーワードの取得
            Dim posMeisai As System.Drawing.Size = .GetVarNamePos("**データ行", 0)

            '明細行の開始行
            Dim startCnt As Integer = posMeisai.Height

            '最終列
            Dim endCol As Integer = 5
            '最終行番号
            Dim endCnt As Integer = 0
            Dim i As Integer = 0
            For Each row As DataSet1.会員MRow In ds.会員M
                endCnt = startCnt + i

                '書式のコピー
                .Pos(posMeisai.Width, posMeisai.Height, endCol, posMeisai.Height).Copy()
                .Pos(posMeisai.Width, endCnt).Paste()

                '値のセット
                .Pos(0, endCnt).Str = row.ユーザID.TrimEnd
                .Pos(1, endCnt).Str = row.パスワード
                .Pos(2, endCnt).Long = row.投票数
                .Pos(3, endCnt).Str = row.予備１
                .Pos(4, endCnt).Str = row.予備２
                .Pos(5, endCnt).Str = row.予備３
                i += 1
            Next

            '罫線
            With .Pos(posMeisai.Width, posMeisai.Height, endCol, endCnt).Attr
                .LineTop = ExcelCreator.xlLineStyle.lsNormal
                .LineBottom = ExcelCreator.xlLineStyle.lsNormal
                .LineLeft = ExcelCreator.xlLineStyle.lsNormal
                .LineRight = ExcelCreator.xlLineStyle.lsNormal
            End With

            '保存   
            .CloseBook(True)
        End With
    End Sub

    Public Sub Output投票T(ByVal saveFilePath As String, ByVal baseFilePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1

            'Excelオープン
            Dim openSts As Integer = .OpenBook(saveFilePath, baseFilePath)
            If openSts < 0 Then
                Throw New Exception(MyMessage.管理者画面.テンプレートファイル取得エラー)
            End If

            '検索キーワードの取得
            Dim posMeisai As System.Drawing.Size = .GetVarNamePos("**データ行", 0)

            '明細行の開始行
            Dim startCnt As Integer = posMeisai.Height

            '最終列
            Dim endCol As Integer = 7
            '最終行番号
            Dim endCnt As Integer = 0
            Dim i As Integer = 0
            For Each row As DataSet1.投票TRow In ds.投票T
                endCnt = startCnt + i

                '書式のコピー
                .Pos(posMeisai.Width, posMeisai.Height, endCol, posMeisai.Height).Copy()
                .Pos(posMeisai.Width, endCnt).Paste()

                '値のセット
                .Pos(0, endCnt).Str = row.選挙ID.TrimEnd
                .Pos(1, endCnt).Str = row.投票券番号.TrimEnd
                .Pos(2, endCnt).Str = row.認証日時
                .Pos(3, endCnt).Str = row.投票日時
                .Pos(4, endCnt).Long = row.インターネット投票フラグ
                .Pos(5, endCnt).Str = row.予備１
                .Pos(6, endCnt).Str = row.予備２
                .Pos(7, endCnt).Str = row.予備３
                i += 1
            Next

            '罫線
            With .Pos(posMeisai.Width, posMeisai.Height, endCol, endCnt).Attr
                .LineTop = ExcelCreator.xlLineStyle.lsNormal
                .LineBottom = ExcelCreator.xlLineStyle.lsNormal
                .LineLeft = ExcelCreator.xlLineStyle.lsNormal
                .LineRight = ExcelCreator.xlLineStyle.lsNormal
            End With

            '保存   
            .CloseBook(True)
        End With

    End Sub

    Public Sub Output投票所M(ByVal saveFilePath As String, ByVal baseFilePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1

            'Excelオープン
            Dim openSts As Integer = .OpenBook(saveFilePath, baseFilePath)
            If openSts < 0 Then
                Throw New Exception(MyMessage.管理者画面.テンプレートファイル取得エラー)
            End If

            '検索キーワードの取得
            Dim posMeisai As System.Drawing.Size = .GetVarNamePos("**データ行", 0)

            '明細行の開始行
            Dim startCnt As Integer = posMeisai.Height

            '最終列
            Dim endCol As Integer = 4
            '最終行番号
            Dim endCnt As Integer = 0
            Dim i As Integer = 0

            Dim util As New MyUtility

            For Each row As DataSet1.投票所MRow In ds.投票所M
                endCnt = startCnt + i

                '書式のコピー
                .Pos(posMeisai.Width, posMeisai.Height, endCol, posMeisai.Height).Copy()
                .Pos(posMeisai.Width, endCnt).Paste()

                '値のセット
                .Pos(0, endCnt).Str = row.投票所コード.TrimEnd
                .Pos(1, endCnt).Str = row.投票所名
                .Pos(2, endCnt).Str = util.GetStringValue(row, "予備１")
                .Pos(3, endCnt).Str = util.GetStringValue(row, "予備２")
                .Pos(4, endCnt).Str = util.GetStringValue(row, "予備３")
                i += 1
            Next

            '罫線
            With .Pos(posMeisai.Width, posMeisai.Height, endCol, endCnt).Attr
                .LineTop = ExcelCreator.xlLineStyle.lsNormal
                .LineBottom = ExcelCreator.xlLineStyle.lsNormal
                .LineLeft = ExcelCreator.xlLineStyle.lsNormal
                .LineRight = ExcelCreator.xlLineStyle.lsNormal
            End With

            '保存   
            .CloseBook(True)
        End With
    End Sub

#End Region

#Region "ReadM"
    Public Sub ReadシステムM(ByVal filePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1
            ' ブックオープン(読み取り専用)
            .ReadBook(filePath)

            ' ヘッダを除いた2行目からデータがある分、読み込む
            For i = 1 To GetMaxRowCount(L_XlsCreator1)
                ds.システムM.AddシステムMRow(.Pos(0, i).Str,
                                 .Pos(1, i).Str,
                                 .Pos(2, i).Str,
                                 .Pos(3, i).Long)
            Next

            ' クローズ
            .CloseBook(True)
        End With
    End Sub

    Public Sub Read選挙M(ByVal filePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1
            ' ブックオープン(読み取り専用)
            .ReadBook(filePath)

            ' ヘッダを除いた2行目からデータがある分、読み込む
            For i = 1 To GetMaxRowCount(L_XlsCreator1)
                ds.選挙M.Add選挙MRow(.Pos(0, i).Str,
                                 .Pos(1, i).Str,
                                 .Pos(2, i).Long,
                                 .Pos(3, i).Long,
                                 .Pos(4, i).Long,
                                 .Pos(5, i).Long,
                                 .Pos(6, i).Str,
                                 .Pos(7, i).Str,
                                 .Pos(8, i).Str)
            Next

            ' クローズ
            .CloseBook(True)
        End With
    End Sub

    Public Sub Read政党M(ByVal filePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1
            ' ブックオープン(読み取り専用)
            .ReadBook(filePath)

            ' ヘッダを除いた2行目からデータがある分、読み込む
            For i = 1 To GetMaxRowCount(L_XlsCreator1)
                ds.政党M.Add政党MRow(.Pos(0, i).Str,
                                   .Pos(1, i).Str,
                                   .Pos(2, i).Str,
                                   .Pos(3, i).Long,
                                   .Pos(4, i).Long,
                                   .Pos(5, i).Str,
                                   .Pos(6, i).Str,
                                   .Pos(7, i).Str,
                                   .Pos(8, i).Str)
            Next

            ' クローズ
            .CloseBook(True)
        End With
    End Sub

    Public Sub Read候補者M(ByVal filePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1
            ' ブックオープン(読み取り専用)
            .ReadBook(filePath)

            ' ヘッダを除いた2行目からデータがある分、読み込む
            For i = 1 To GetMaxRowCount(L_XlsCreator1)
                ds.候補者M.Add候補者MRow(.Pos(0, i).Str,
                                   .Pos(1, i).Str,
                                   .Pos(2, i).Str,
                                   .Pos(3, i).Str,
                                   .Pos(4, i).Str,
                                   .Pos(5, i).Long,
                                   .Pos(6, i).Long,
                                   .Pos(7, i).Str,
                                   .Pos(8, i).Str,
                                   .Pos(9, i).Str,
                                   .Pos(10, i).Str,
                                   .Pos(11, i).Str,
                                   .Pos(12, i).Str,
                                   .Pos(13, i).Str,
                                   .Pos(14, i).Str)
            Next

            ' クローズ
            .CloseBook(True)
        End With
    End Sub

    Public Sub Read会員M(ByVal filePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1
            ' ブックオープン(読み取り専用)
            .ReadBook(filePath)

            ' ヘッダを除いた2行目からデータがある分、読み込む
            For i = 1 To GetMaxRowCount(L_XlsCreator1)
                ds.会員M.Add会員MRow(.Pos(0, i).Str,
                                   .Pos(1, i).Str,
                                   .Pos(2, i).Long,
                                   .Pos(3, i).Str,
                                   .Pos(4, i).Str,
                                   .Pos(5, i).Str)
            Next

            ' クローズ
            .CloseBook(True)
        End With
    End Sub

    Public Sub Read投票所M(ByVal filePath As String, ByVal ds As DataSet1)
        With L_XlsCreator1
            ' ブックオープン(読み取り専用)
            .ReadBook(filePath)

            ' ヘッダを除いた2行目からデータがある分、読み込む
            For i = 1 To GetMaxRowCount(L_XlsCreator1)
                ds.投票所M.Add投票所MRow(.Pos(0, i).Str,
                                         .Pos(1, i).Str,
                                         .Pos(2, i).Str,
                                         .Pos(3, i).Str,
                                         .Pos(4, i).Str)
            Next

            ' クローズ
            .CloseBook(True)
        End With
    End Sub

#End Region

#Region "Privateメソッド"
    ''' <summary>
    ''' Excel Creator エラーイベント
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub XlsCreator1_Error(ByVal sender As Object, ByVal e As ExcelCreator.XlsCreatorEventArgs) Handles L_XlsCreator1.Error
        Throw New Exception(e.ErrorNo.ToString & ":" & L_XlsCreator1.ErrorMessage)
    End Sub


    ''' <summary>
    ''' Excelの最大行取得：65,536 行
    ''' </summary>
    ''' <param name="objXlsCreator"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetMaxRowCount(ByVal objXlsCreator As XlsCreator) As Integer
        Dim intRowMax As Integer = 0
        For intRow = 0 To 65536
            If objXlsCreator.Pos(0, intRow).Str = "" Then Exit For
            intRowMax += 1
        Next
        intRowMax = intRowMax - 1

        GetMaxRowCount = intRowMax
    End Function

    ''' <summary>  
    ''' Excelの列番号を英文字に変換する   
    ''' </summary>  
    ''' <param name="iCol">列番号</param>
    ''' <returns></returns>  
    ''' <remarks> 
    ''' マイクロソフトサポートオンラインより引用して変更を加えた  
    ''' http://support.microsoft.com/kb/833402/ja  
    ''' </remarks>  
    Function ConvertToLetter(ByVal iCol As Integer) As String

        Dim rtnLetter As String = ""
        Dim iAlpha As Integer
        Dim iRemainder As Integer
        iAlpha = CInt(Int((iCol - 1) / 26))
        iRemainder = iCol - (iAlpha * 26)
        If iAlpha > 0 Then
            rtnLetter = Chr(iAlpha + 64)
        End If
        If iRemainder > 0 Then
            rtnLetter = rtnLetter & Chr(iRemainder + 64)
        End If
        Return rtnLetter
    End Function
#End Region

End Class
