hytos / DTI_PID / SPPIDConverter / CGembox.vb @ 9b5d4527
이력 | 보기 | 이력해설 | 다운로드 (47.5 KB)
| 1 | 6ba3e887 | Gyusu | Imports GemBox.Spreadsheet |
|---|---|---|---|
| 2 | Imports System.Drawing |
||
| 3 | Imports System.IO |
||
| 4 | |||
| 5 | Public Class CGembox |
||
| 6 | |||
| 7 | Public _ExcelFile As ExcelFile |
||
| 8 | Dim _Index_Cnt As Integer = 2 |
||
| 9 | Dim _LicenseKey As String = "EXK0-W4HZ-N518-IMEW" |
||
| 10 | Sub New() |
||
| 11 | setLicense() |
||
| 12 | End Sub |
||
| 13 | Private Sub setLicense() |
||
| 14 | SpreadsheetInfo.SetLicense(_LicenseKey) |
||
| 15 | _ExcelFile = New ExcelFile |
||
| 16 | ' _ExcelFile = New ExcelFile |
||
| 17 | End Sub |
||
| 18 | |||
| 19 | #Region "엑셀 공용 함수" |
||
| 20 | Dim EXCEL_HEAD As Integer = 0 |
||
| 21 | Dim EXCEL_START As Integer = 1 |
||
| 22 | |||
| 23 | Function SaveFileFunc(ByVal mWorksheet As String, ByVal mPath As String, ByVal mDT As DataTable) As Boolean |
||
| 24 | Try |
||
| 25 | SetCell(_ExcelFile.Worksheets(mWorksheet).Rows(EXCEL_HEAD).Cells(0), "HEAD") |
||
| 26 | SetCell(_ExcelFile.Worksheets(mWorksheet).Rows(EXCEL_START).Cells(0), "START") |
||
| 27 | For mColcnt = 0 To mDT.Columns.Count - 1 |
||
| 28 | SetCell(_ExcelFile.Worksheets(mWorksheet).Rows(EXCEL_HEAD).Cells(mColcnt + 1), mDT.Columns(mColcnt).ColumnName) |
||
| 29 | Next |
||
| 30 | If mDT IsNot Nothing Then |
||
| 31 | For mRowcnt = 0 To mDT.Rows.Count - 1 |
||
| 32 | SetCell(_ExcelFile.Worksheets(mWorksheet).Rows(EXCEL_START + 1 + mRowcnt).Cells(0), String.Empty) |
||
| 33 | For mColcnt = 0 To mDT.Columns.Count - 1 |
||
| 34 | SetCell(_ExcelFile.Worksheets(mWorksheet).Rows(EXCEL_START + mRowcnt).Cells(mColcnt + 1), mDT.Rows(mRowcnt).Item(mColcnt).ToString) |
||
| 35 | Next |
||
| 36 | Next |
||
| 37 | End If |
||
| 38 | SetCell(_ExcelFile.Worksheets(mWorksheet).Rows(mDT.Rows.Count + EXCEL_START).Cells(0), "END") |
||
| 39 | SetSaveFile(_ExcelFile, mPath) |
||
| 40 | Catch ex As Exception |
||
| 41 | Return False |
||
| 42 | End Try |
||
| 43 | Return True |
||
| 44 | End Function |
||
| 45 | |||
| 46 | Function LoadFileFunc() As DataSet |
||
| 47 | Dim oDs As New DataSet |
||
| 48 | ' Dim oExcelFile As New ExcelFile |
||
| 49 | _TempExcel = New ExcelFile |
||
| 50 | ' _TempFileName = DateTime.Now.Year & "_" & DateTime.Now.Month & "_" & DateTime.Now.Day & " " & DateTime.Now.Hour & "_" & DateTime.Now.Minute & "_" & DateTime.Now.Second |
||
| 51 | For Each oSheet In _ExcelFile.Worksheets |
||
| 52 | |||
| 53 | If oSheet.Name = _Sheet_Equipment Or |
||
| 54 | oSheet.Name = _Sheet_EquipmentComponents Or |
||
| 55 | oSheet.Name = _Sheet_Instrumentation Or |
||
| 56 | oSheet.Name = _Sheet_Piping Then |
||
| 57 | _TempExcel.Worksheets.AddCopy(oSheet.Name, oSheet) |
||
| 58 | Dim oDt As New DataTable |
||
| 59 | oDt.TableName = oSheet.Name |
||
| 60 | |||
| 61 | Dim mFindHeadInt As Integer = 0 ' GetFindRow(_ExcelFile.Worksheets(msheetName), "HEAD") |
||
| 62 | Dim mStartInt As Integer = 1 'GetFindRow(_ExcelFile.Worksheets(msheetName), "START") |
||
| 63 | Dim mLastColInt As Integer = oSheet.CalculateMaxUsedColumns 'GetFindColumnCount(_ExcelFile.Worksheets(oSheet.Name), mFindHeadInt) |
||
| 64 | |||
| 65 | Dim mInputColInt As Integer = 0 |
||
| 66 | If mFindHeadInt <> -1 Or mStartInt <> -1 Or mLastColInt <> -1 Then |
||
| 67 | For mCellcnt = 1 To mLastColInt |
||
| 68 | If _ExcelFile.Worksheets(oSheet.Name).Rows(mFindHeadInt).Cells(mCellcnt).Value <> "" Then |
||
| 69 | Dim oCheckColumnName As Boolean = False |
||
| 70 | For i = 0 To oDt.Columns.Count - 1 |
||
| 71 | If oDt.Columns(i).ColumnName = _ExcelFile.Worksheets(oSheet.Name).Rows(mFindHeadInt).Cells(mCellcnt).Value Then |
||
| 72 | oCheckColumnName = True |
||
| 73 | Exit For |
||
| 74 | End If |
||
| 75 | Next |
||
| 76 | If oCheckColumnName = False Then |
||
| 77 | '/ 컬럼이름을 데이터테이블에 저장. 그리드뷰에 출력하기 위해서 |
||
| 78 | Dim oCol As DataColumn = oDt.Columns.Add(_ExcelFile.Worksheets(oSheet.Name).Rows(mFindHeadInt).Cells(mCellcnt).Value) |
||
| 79 | oCol.Namespace = mCellcnt |
||
| 80 | mInputColInt = mCellcnt |
||
| 81 | End If |
||
| 82 | End If |
||
| 83 | Next |
||
| 84 | |||
| 85 | Dim oIndexCol As DataColumn = oDt.Columns.Add(_Column_Index) |
||
| 86 | oIndexCol.Namespace = mInputColInt |
||
| 87 | oIndexCol.ColumnMapping = MappingType.Hidden |
||
| 88 | '/END 열을 찾아서 START 부터 END 까지 데이터를 데이터테이블에 입력 |
||
| 89 | Dim mFindEndInt As Integer = _ExcelFile.Worksheets(oSheet.Name).Rows.Count 'GetFindRow(_ExcelFile.Worksheets(oSheet.Name), " ") |
||
| 90 | For mRowcnt = 1 To mFindEndInt - 1 |
||
| 91 | Try |
||
| 92 | |||
| 93 | Dim mDataRow As DataRow = oDt.NewRow |
||
| 94 | For mCellCnt = 1 To mLastColInt - 1 |
||
| 95 | Try |
||
| 96 | Dim oColName As String = _ExcelFile.Worksheets(oSheet.Name).Rows(mFindHeadInt).Cells(mCellCnt).Value |
||
| 97 | If oColName <> Nothing Then |
||
| 98 | |||
| 99 | Dim oValve As String = _ExcelFile.Worksheets(oSheet.Name).Rows(mRowcnt).Cells(mCellCnt).Value |
||
| 100 | If oValve IsNot Nothing Then |
||
| 101 | If oValve = "" Then |
||
| 102 | mDataRow(oColName) = "" |
||
| 103 | Else |
||
| 104 | Dim sPreValue As String = mDataRow(oColName).ToString() |
||
| 105 | |||
| 106 | If sPreValue <> "" Then |
||
| 107 | mDataRow(oColName) = sPreValue & "\" & oValve |
||
| 108 | Else |
||
| 109 | mDataRow(oColName) = oValve |
||
| 110 | End If |
||
| 111 | End If |
||
| 112 | Else |
||
| 113 | If mDataRow(oColName) IsNot Nothing Then |
||
| 114 | If mDataRow(oColName).ToString() = "" Then |
||
| 115 | mDataRow(oColName) = "" |
||
| 116 | End If |
||
| 117 | End If |
||
| 118 | End If |
||
| 119 | End If |
||
| 120 | Catch ex As Exception |
||
| 121 | End Try |
||
| 122 | Next |
||
| 123 | mDataRow(_Column_Index) = mRowcnt |
||
| 124 | If mDataRow("SYM FILE NAME").ToString() <> "" Then
|
||
| 125 | oDt.Rows.Add(mDataRow) |
||
| 126 | End If |
||
| 127 | Catch ex As Exception |
||
| 128 | End Try |
||
| 129 | Next |
||
| 130 | End If |
||
| 131 | oDs.Tables.Add(oDt) |
||
| 132 | End If |
||
| 133 | Next |
||
| 134 | Return oDs |
||
| 135 | End Function |
||
| 136 | |||
| 137 | Function AddImageLibrary(ByVal oDs As DataSet, ByVal oPIDSymbol_Dt As DataTable) As DataTable |
||
| 138 | |||
| 139 | Dim oImageSymbol_Dt As DataTable = ImageSymbol_Dt() |
||
| 140 | Dim iColcnt As Integer = 0 |
||
| 141 | Dim bCheckSheet As Boolean = False |
||
| 142 | Dim oSymbolSheet As ExcelWorksheet = Nothing |
||
| 143 | For Each oSheet As ExcelWorksheet In _ExcelFile.Worksheets |
||
| 144 | If oSheet.Name = _Sheet_ImageSymbol Then |
||
| 145 | bCheckSheet = True |
||
| 146 | oSymbolSheet = oSheet |
||
| 147 | oSymbolSheet.Clear() |
||
| 148 | Exit For |
||
| 149 | End If |
||
| 150 | Next |
||
| 151 | If bCheckSheet = False Then |
||
| 152 | oSymbolSheet = _ExcelFile.Worksheets.Add(_Sheet_ImageSymbol) |
||
| 153 | End If |
||
| 154 | If oSymbolSheet IsNot Nothing Then |
||
| 155 | For Each oDt As DataTable In oDs.Tables |
||
| 156 | oSymbolSheet.Cells(0, iColcnt).Value = oDt.TableName |
||
| 157 | oSymbolSheet.Columns(iColcnt).AutoFit() |
||
| 158 | Dim oDataRows() As DataRow = oPIDSymbol_Dt.Select("category = '" & oDt.TableName & "'")
|
||
| 159 | Dim iRowcnt As Integer = 1 |
||
| 160 | For Each oRow In oDataRows |
||
| 161 | Dim sCategory As String = oRow("name").ToString()
|
||
| 162 | oSymbolSheet.Cells(iRowcnt, iColcnt).Value = sCategory |
||
| 163 | iRowcnt = iRowcnt + 1 |
||
| 164 | Next |
||
| 165 | Dim sStartCell As String = ConvertAlphabet(iColcnt) & 2 |
||
| 166 | Dim sEndCell As String = ConvertAlphabet(iColcnt) & iRowcnt |
||
| 167 | |||
| 168 | Dim oAddRow As DataRow = oImageSymbol_Dt.NewRow() |
||
| 169 | oAddRow(_Sheet_ImageSymbol_Type) = oDt.TableName |
||
| 170 | oAddRow(_Sheet_ImageSymbol_StartCell) = sStartCell |
||
| 171 | oAddRow(_Sheet_ImageSymbol_EndCell) = sEndCell |
||
| 172 | oImageSymbol_Dt.Rows.Add(oAddRow) |
||
| 173 | iColcnt = iColcnt + 1 |
||
| 174 | Next |
||
| 175 | End If |
||
| 176 | Return oImageSymbol_Dt |
||
| 177 | End Function |
||
| 178 | |||
| 179 | Function ConvertAlphabet(ByVal iCol As Integer) As String |
||
| 180 | If iCol = 0 Then |
||
| 181 | Return "A" |
||
| 182 | ElseIf iCol = 1 Then |
||
| 183 | Return "B" |
||
| 184 | ElseIf iCol = 2 Then |
||
| 185 | Return "C" |
||
| 186 | ElseIf iCol = 3 Then |
||
| 187 | Return "D" |
||
| 188 | ElseIf iCol = 4 Then |
||
| 189 | Return "E" |
||
| 190 | ElseIf iCol = 5 Then |
||
| 191 | Return "F" |
||
| 192 | ElseIf iCol = 6 Then |
||
| 193 | Return "G" |
||
| 194 | ElseIf iCol = 7 Then |
||
| 195 | Return "H" |
||
| 196 | ElseIf iCol = 8 Then |
||
| 197 | Return "I" |
||
| 198 | ElseIf iCol = 9 Then |
||
| 199 | Return "J" |
||
| 200 | ElseIf iCol = 10 Then |
||
| 201 | Return "K" |
||
| 202 | ElseIf iCol = 11 Then |
||
| 203 | Return "L" |
||
| 204 | ElseIf iCol = 12 Then |
||
| 205 | Return "M" |
||
| 206 | ElseIf iCol = 13 Then |
||
| 207 | Return "N" |
||
| 208 | ElseIf iCol = 14 Then |
||
| 209 | Return "O" |
||
| 210 | ElseIf iCol = 15 Then |
||
| 211 | Return "P" |
||
| 212 | ElseIf iCol = 16 Then |
||
| 213 | Return "Q" |
||
| 214 | ElseIf iCol = 17 Then |
||
| 215 | Return "R" |
||
| 216 | ElseIf iCol = 18 Then |
||
| 217 | Return "S" |
||
| 218 | End If |
||
| 219 | Return "" |
||
| 220 | End Function |
||
| 221 | |||
| 222 | |||
| 223 | Function SaveSymbolLibrary(ByVal oDt As DataTable, ByVal oImageSymbol_Dt As DataTable) As Boolean |
||
| 224 | Dim oUpdateFlag As Boolean = True |
||
| 225 | |||
| 226 | Dim mColumnCount As Integer = oDt.Columns.Count |
||
| 227 | Try |
||
| 228 | Dim sSheetName As String = oDt.TableName |
||
| 229 | _ExcelFile.Worksheets(sSheetName).DataValidations.Clear() |
||
| 230 | For Each oCol As DataColumn In oDt.Columns |
||
| 231 | |||
| 232 | If IsNumeric(oCol.Namespace) Then |
||
| 233 | Dim sColindex As Integer = oCol.Namespace |
||
| 234 | Dim sExcelColName As String = _ExcelFile.Worksheets(sSheetName).Cells(0, sColindex).Value |
||
| 235 | Dim sColName As String = oCol.ColumnName |
||
| 236 | If sExcelColName <> sColName And sColName <> _Column_Index Then |
||
| 237 | _ExcelFile.Worksheets(sSheetName).Cells(0, sColindex).Value = sColName |
||
| 238 | End If |
||
| 239 | End If |
||
| 240 | Next |
||
| 241 | Dim sAlphabet As String = "" |
||
| 242 | Dim iLastRow As Integer = 0 |
||
| 243 | For j = 0 To oDt.Columns.Count - 1 |
||
| 244 | If oDt.Columns(j).ColumnName = _Column_ImageSymbol Then |
||
| 245 | |||
| 246 | Dim iColindex As Integer = -1 |
||
| 247 | If IsNumeric(oDt.Columns(j - 1).Namespace) Then |
||
| 248 | iColindex = oDt.Columns(j - 1).Namespace + 1 |
||
| 249 | End If |
||
| 250 | If iColindex > 0 Then |
||
| 251 | sAlphabet = ConvertAlphabet(iColindex) |
||
| 252 | _ExcelFile.Worksheets(sSheetName).Columns(iColindex).AutoFit() |
||
| 253 | For i = 0 To oDt.Rows.Count - 1 |
||
| 254 | If IsNumeric(oDt.Rows(i)(_Column_Index).ToString()) Then |
||
| 255 | Dim iRowindex As Integer = oDt.Rows(i)(_Column_Index).ToString() |
||
| 256 | Dim sImgSymbolName As String = oDt.Rows(i)(_Column_ImageSymbol).ToString() |
||
| 257 | |||
| 258 | ' .ErrorMessage = "Value should be a date between 2011-01-01 and 2011-12-31." |
||
| 259 | iLastRow = iRowindex |
||
| 260 | _ExcelFile.Worksheets(sSheetName).Cells(iRowindex, iColindex).Value = sImgSymbolName |
||
| 261 | Dim oFindCell As DataRow() = oImageSymbol_Dt.Select("Type = '" & sSheetName & "'")
|
||
| 262 | If oFindCell.Length = 1 Then |
||
| 263 | Dim sStartcell As String = oFindCell(0)(_Sheet_ImageSymbol_StartCell).ToString() |
||
| 264 | Dim sEndcell As String = oFindCell(0)(_Sheet_ImageSymbol_EndCell).ToString() |
||
| 265 | _ExcelFile.Worksheets(sSheetName).DataValidations.Add(New DataValidation(_ExcelFile.Worksheets(sSheetName), sAlphabet & iLastRow + 1) With {
|
||
| 266 | .Type = DataValidationType.List, |
||
| 267 | .Formula1 = "=" & _Sheet_ImageSymbol & "!" & sStartcell & ":" & sEndcell, |
||
| 268 | .InputMessageTitle = "Image Symbol 선택", |
||
| 269 | .InputMessage = "SPPID Symbol과 일치하는 ImageSymbol을 선택해주세요", |
||
| 270 | .ErrorStyle = DataValidationErrorStyle.Information, |
||
| 271 | .ErrorTitle = "Invalid Symbol"}) |
||
| 272 | End If |
||
| 273 | |||
| 274 | End If |
||
| 275 | Next |
||
| 276 | End If |
||
| 277 | |||
| 278 | |||
| 279 | End If |
||
| 280 | |||
| 281 | Next |
||
| 282 | |||
| 283 | 'Dim oFindCell As DataRow() = oImageSymbol_Dt.Select("Type = '" & sSheetName & "'")
|
||
| 284 | 'If oFindCell.Length = 1 Then |
||
| 285 | ' Dim sStartcell As String = oFindCell(0)(_Sheet_ImageSymbol_StartCell).ToString() |
||
| 286 | ' Dim sEndcell As String = oFindCell(0)(_Sheet_ImageSymbol_EndCell).ToString() |
||
| 287 | ' _ExcelFile.Worksheets(sSheetName).DataValidations.Add(New DataValidation(_ExcelFile.Worksheets(sSheetName).Cells.GetSubrange(sAlphabet & "1", sAlphabet & iLastRow)) With {
|
||
| 288 | '.Type = DataValidationType.List, |
||
| 289 | '.Formula1 = "=" & _Sheet_ImageSymbol & "!" & sStartcell & ":" & sEndcell, |
||
| 290 | '.InputMessageTitle = "Image Symbol 선택", |
||
| 291 | '.InputMessage = "SPPID Symbol과 일치하는 ImageSymbol을 선택해주세요", |
||
| 292 | '.ErrorStyle = DataValidationErrorStyle.Information, |
||
| 293 | '.ErrorTitle = "Invalid Symbol"}) |
||
| 294 | 'End If |
||
| 295 | |||
| 296 | Catch ex As Exception |
||
| 297 | End Try |
||
| 298 | End Function |
||
| 299 | |||
| 300 | |||
| 301 | |||
| 302 | Function outputToExcelFile(ByVal mdt As DataTable, ByVal mReportSubject As String, ByVal mSavePath As String) As String |
||
| 303 | ' Dim mExcelFile As New ExcelFile |
||
| 304 | Try |
||
| 305 | Dim mEwSheet As ExcelWorksheet = _ExcelFile.Worksheets.Add(mReportSubject) |
||
| 306 | Dim mColumnCount As Integer = mdt.Columns.Count |
||
| 307 | ' 제목 |
||
| 308 | mEwSheet.Cells.GetSubrangeAbsolute(0, 0, 0, mColumnCount - 2).Merged = True |
||
| 309 | mEwSheet.Cells(0, 0).Style.Font.Size = 18 * 20 |
||
| 310 | mEwSheet.Cells(0, 0).Style.Font.Weight = ExcelFont.BoldWeight |
||
| 311 | mEwSheet.Cells(0, 0).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center |
||
| 312 | mEwSheet.Cells(0, 0).Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 313 | |||
| 314 | mEwSheet.Cells(0, 0).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 315 | mEwSheet.Cells(0, 0).Value = mReportSubject |
||
| 316 | Dim mDateTime As Date = DateTime.Now |
||
| 317 | ' 날짜 |
||
| 318 | mEwSheet.Cells(0, mColumnCount - 1).Value = mDateTime.Year.ToString() + "-" + String.Format("{0:d2}", mDateTime.Month) + "-" + String.Format("{0:d2}", mDateTime.Day)
|
||
| 319 | mEwSheet.Cells(0, mColumnCount - 1).Style.HorizontalAlignment = HorizontalAlignmentStyle.Right |
||
| 320 | mEwSheet.Cells(0, mColumnCount - 1).Style.Font.Size = 14 * 20 |
||
| 321 | mEwSheet.Cells(0, mColumnCount - 1).Style.Font.Weight = ExcelFont.BoldWeight |
||
| 322 | mEwSheet.Cells(0, mColumnCount - 1).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 323 | |||
| 324 | '--------------------------------------------------------------- |
||
| 325 | ' 헤더 설정 |
||
| 326 | '-------------------------------------------------------------- |
||
| 327 | For i = 0 To mColumnCount - 1 |
||
| 328 | '-------------------------------------------------------------------------------- |
||
| 329 | '이름. |
||
| 330 | '--------------------------------------------------------------------------------- |
||
| 331 | mEwSheet.Cells(1, i).Value = mdt.Columns(i).ToString() |
||
| 332 | '--------------------------------------------------------------------------------- |
||
| 333 | ' 색깔. |
||
| 334 | '--------------------------------------------------------------------------------- |
||
| 335 | mEwSheet.Cells(1, i).Style.FillPattern.SetSolid(Color.CornflowerBlue) |
||
| 336 | '--------------------------------------------------------------------------------- |
||
| 337 | ' 글씨. |
||
| 338 | '--------------------------------------------------------------------------------- |
||
| 339 | mEwSheet.Cells(1, i).Style.Font.Color = Color.White |
||
| 340 | mEwSheet.Cells(1, i).Style.Font.Weight = ExcelFont.BoldWeight |
||
| 341 | '--------------------------------------------------------------------------------- |
||
| 342 | ' 줄. |
||
| 343 | '--------------------------------------------------------------------------------- |
||
| 344 | mEwSheet.Cells(1, i).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 345 | '--------------------------------------------------------------------------------- |
||
| 346 | ' 세로 정렬 |
||
| 347 | '--------------------------------------------------------------------------------- |
||
| 348 | mEwSheet.Cells(1, i).Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 349 | '--------------------------------------------------------------------------------- |
||
| 350 | ' 가로 정렬 |
||
| 351 | '--------------------------------------------------------------------------------- |
||
| 352 | mEwSheet.Cells(1, i).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center |
||
| 353 | Next |
||
| 354 | '--------------------------------------------------------------- |
||
| 355 | ' 리포트 행 데이터 입력. |
||
| 356 | '--------------------------------------------------------------- |
||
| 357 | Dim mblankRow As Integer = 2 |
||
| 358 | |||
| 359 | For i = 0 To mdt.Rows.Count - 1 |
||
| 360 | For j = 0 To mdt.Columns.Count - 1 |
||
| 361 | ' 값 입력 |
||
| 362 | mEwSheet.Cells(i + mblankRow, j).Value = mdt.Rows(i)(j).ToString() |
||
| 363 | '--------------------------------------------------------------------------------- |
||
| 364 | ' 줄. |
||
| 365 | '--------------------------------------------------------------------------------- |
||
| 366 | mEwSheet.Cells(i + mblankRow, j).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 367 | '--------------------------------------------------------------------------------- |
||
| 368 | ' 세로 정렬 |
||
| 369 | '--------------------------------------------------------------------------------- |
||
| 370 | mEwSheet.Cells(i + mblankRow, j).Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 371 | '--------------------------------------------------------------------------------- |
||
| 372 | ' 가로 정렬 |
||
| 373 | '--------------------------------------------------------------------------------- |
||
| 374 | mEwSheet.Cells(i + mblankRow, j).Style.HorizontalAlignment = HorizontalAlignmentStyle.Left |
||
| 375 | Next |
||
| 376 | Next |
||
| 377 | '--------------------------------------------------------------------------------- |
||
| 378 | ' 핏 |
||
| 379 | '--------------------------------------------------------------------------------- |
||
| 380 | For i = 0 To mColumnCount - 1 |
||
| 381 | mEwSheet.Columns(i).AutoFit() |
||
| 382 | Next |
||
| 383 | '--------------------------------------------------------------------------------- |
||
| 384 | ' 저장 |
||
| 385 | '--------------------------------------------------------------------------------- |
||
| 386 | SetSaveFile(_ExcelFile, mSavePath) |
||
| 387 | |||
| 388 | Catch ex As Exception |
||
| 389 | MsgBox(ex.Message, MsgBoxStyle.Critical) |
||
| 390 | Return "" |
||
| 391 | End Try |
||
| 392 | Return mSavePath |
||
| 393 | End Function |
||
| 394 | |||
| 395 | |||
| 396 | 'Function OutputToExcelFile(ByVal mUltragrid As UltraGrid, ByVal mReportSubject As String, ByVal mSavePath As String) As String |
||
| 397 | ' Dim mExcelFile As New ExcelFile |
||
| 398 | ' Try |
||
| 399 | ' Dim mEwSheet As ExcelWorksheet = mExcelFile.Worksheets.Add(mReportSubject) |
||
| 400 | ' Dim mColumnCount As Integer = mUltragrid.DisplayLayout.Bands(0).Columns.Count |
||
| 401 | ' ' 제목 |
||
| 402 | ' mEwSheet.Cells.GetSubrangeAbsolute(0, 0, 0, mColumnCount - 2).Merged = True |
||
| 403 | ' mEwSheet.Cells(0, 0).Style.Font.Size = 18 * 20 |
||
| 404 | ' mEwSheet.Cells(0, 0).Style.Font.Weight = ExcelFont.BoldWeight |
||
| 405 | ' mEwSheet.Cells(0, 0).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center |
||
| 406 | ' mEwSheet.Cells(0, 0).Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 407 | ' mEwSheet.Cells(0, 0).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 408 | ' mEwSheet.Cells(0, 0).Value = mReportSubject |
||
| 409 | ' Dim mDateTime As Date = DateTime.Now |
||
| 410 | ' ' 날짜 |
||
| 411 | ' mEwSheet.Cells(0, mColumnCount - 1).Value = mDateTime.Year.ToString() + "-" + String.Format("{0:d2}", mDateTime.Month) + "-" + String.Format("{0:d2}", mDateTime.Day)
|
||
| 412 | ' mEwSheet.Cells(0, mColumnCount - 1).Style.HorizontalAlignment = HorizontalAlignmentStyle.Right |
||
| 413 | ' mEwSheet.Cells(0, mColumnCount - 1).Style.Font.Size = 14 * 20 |
||
| 414 | ' mEwSheet.Cells(0, mColumnCount - 1).Style.Font.Weight = ExcelFont.BoldWeight |
||
| 415 | ' mEwSheet.Cells(0, mColumnCount - 1).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 416 | |||
| 417 | ' '--------------------------------------------------------------- |
||
| 418 | ' ' 헤더 설정 |
||
| 419 | ' '-------------------------------------------------------------- |
||
| 420 | ' For i = 0 To mColumnCount - 1 |
||
| 421 | ' '-------------------------------------------------------------------------------- |
||
| 422 | ' '이름. |
||
| 423 | ' '--------------------------------------------------------------------------------- |
||
| 424 | ' mEwSheet.Cells(1, i).Value = mUltragrid.DisplayLayout.Bands(0).Columns(i).ToString() |
||
| 425 | ' '--------------------------------------------------------------------------------- |
||
| 426 | ' ' 색깔. |
||
| 427 | ' '--------------------------------------------------------------------------------- |
||
| 428 | ' mEwSheet.Cells(1, i).Style.FillPattern.SetSolid(Color.CornflowerBlue) |
||
| 429 | ' '--------------------------------------------------------------------------------- |
||
| 430 | ' ' 글씨. |
||
| 431 | ' '--------------------------------------------------------------------------------- |
||
| 432 | ' mEwSheet.Cells(1, i).Style.Font.Color = Color.White |
||
| 433 | ' mEwSheet.Cells(1, i).Style.Font.Weight = ExcelFont.BoldWeight |
||
| 434 | ' '--------------------------------------------------------------------------------- |
||
| 435 | ' ' 줄. |
||
| 436 | ' '--------------------------------------------------------------------------------- |
||
| 437 | ' mEwSheet.Cells(1, i).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 438 | ' '--------------------------------------------------------------------------------- |
||
| 439 | ' ' 세로 정렬 |
||
| 440 | ' '--------------------------------------------------------------------------------- |
||
| 441 | ' mEwSheet.Cells(1, i).Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 442 | ' '--------------------------------------------------------------------------------- |
||
| 443 | ' ' 가로 정렬 |
||
| 444 | ' '--------------------------------------------------------------------------------- |
||
| 445 | ' mEwSheet.Cells(1, i).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center |
||
| 446 | ' Next |
||
| 447 | ' '--------------------------------------------------------------- |
||
| 448 | ' ' 리포트 행 데이터 입력. |
||
| 449 | ' '--------------------------------------------------------------- |
||
| 450 | ' Dim mGridRow() As UltraGridRow = mUltragrid.Rows.GetFilteredInNonGroupByRows() |
||
| 451 | ' Dim mblankRow As Integer = 2 |
||
| 452 | |||
| 453 | ' For i = 0 To mGridRow.Length - 1 |
||
| 454 | ' For j = 0 To mGridRow.Length - 1 |
||
| 455 | ' ' 값 입력 |
||
| 456 | ' mEwSheet.Cells(i + mblankRow, j).Value = mGridRow(i).Cells(j).ToString() |
||
| 457 | |||
| 458 | ' '--------------------------------------------------------------------------------- |
||
| 459 | ' ' 셀 색. |
||
| 460 | ' '--------------------------------------------------------------------------------- |
||
| 461 | ' If (mGridRow(i).Cells(j).Appearance.BackColor = Color.Empty) Then |
||
| 462 | ' mEwSheet.Cells(i + mblankRow, j).Style.FillPattern.SetSolid(Color.Transparent) |
||
| 463 | ' Else |
||
| 464 | ' mEwSheet.Cells(i + mblankRow, j).Style.FillPattern.SetSolid(mGridRow(i).Cells(j).Appearance.BackColor) |
||
| 465 | ' End If |
||
| 466 | |||
| 467 | ' '--------------------------------------------------------------------------------- |
||
| 468 | ' ' 줄. |
||
| 469 | ' '--------------------------------------------------------------------------------- |
||
| 470 | ' mEwSheet.Cells(i + mblankRow, j).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 471 | ' '--------------------------------------------------------------------------------- |
||
| 472 | ' ' 세로 정렬 |
||
| 473 | ' '--------------------------------------------------------------------------------- |
||
| 474 | ' mEwSheet.Cells(i + mblankRow, j).Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 475 | ' '--------------------------------------------------------------------------------- |
||
| 476 | ' ' 가로 정렬 |
||
| 477 | ' '--------------------------------------------------------------------------------- |
||
| 478 | ' mEwSheet.Cells(i + mblankRow, j).Style.HorizontalAlignment = HorizontalAlignmentStyle.Left |
||
| 479 | ' Next |
||
| 480 | ' Next |
||
| 481 | ' '--------------------------------------------------------------------------------- |
||
| 482 | ' ' 핏 |
||
| 483 | ' '--------------------------------------------------------------------------------- |
||
| 484 | ' For i = 0 To mColumnCount - 1 |
||
| 485 | ' mEwSheet.Columns(i).AutoFit() |
||
| 486 | ' Next |
||
| 487 | ' '--------------------------------------------------------------------------------- |
||
| 488 | ' ' 저장 |
||
| 489 | ' '--------------------------------------------------------------------------------- |
||
| 490 | ' If (mSavePath.Contains(".xlsx")) Then
|
||
| 491 | ' mExcelFile.SaveXlsx(mSavePath) |
||
| 492 | ' Else |
||
| 493 | ' mExcelFile.SaveXls(mSavePath) |
||
| 494 | ' System.Diagnostics.Process.Start(mSavePath) |
||
| 495 | ' End If |
||
| 496 | ' Catch ex As Exception |
||
| 497 | ' MsgBox(ex.Message, MsgBoxStyle.Critical) |
||
| 498 | ' Return "" |
||
| 499 | ' End Try |
||
| 500 | ' Return mSavePath |
||
| 501 | 'End Function |
||
| 502 | |||
| 503 | '/엑셀파일 여러개 로드할때 |
||
| 504 | Function isLoadFile(ByVal mExcelFileList As List(Of ExcelFile), ByVal mFilepath() As String) |
||
| 505 | Try |
||
| 506 | mExcelFileList.Clear() |
||
| 507 | For mcnt = 0 To mFilepath.Length - 1 |
||
| 508 | mExcelFileList.Add(New ExcelFile) |
||
| 509 | _ExcelFile = GemBox.Spreadsheet.ExcelFile.Load(mFilepath(mcnt)) |
||
| 510 | Next |
||
| 511 | Return True |
||
| 512 | Catch ex As Exception |
||
| 513 | MsgBox(ex.Message, MsgBoxStyle.Critical) |
||
| 514 | Return False |
||
| 515 | End Try |
||
| 516 | End Function |
||
| 517 | '/엑셀파일 하나만 로드할때 |
||
| 518 | Function isLoadFile(ByVal mFilepath As String) As Boolean |
||
| 519 | Try |
||
| 520 | _ExcelFile = GemBox.Spreadsheet.ExcelFile.Load(mFilepath) |
||
| 521 | Return True |
||
| 522 | Catch ex As Exception |
||
| 523 | MsgBox(ex.Message, MsgBoxStyle.Critical) |
||
| 524 | Return False |
||
| 525 | End Try |
||
| 526 | End Function |
||
| 527 | '엑셀 파일 저장하기 |
||
| 528 | Function SetSaveFile(ByVal mExcelFile As ExcelFile, ByVal outputFilepath As String) As Boolean |
||
| 529 | Try |
||
| 530 | mExcelFile.Save(outputFilepath) |
||
| 531 | |||
| 532 | Return True |
||
| 533 | Catch ex As Exception |
||
| 534 | MsgBox(ex.Message, MsgBoxStyle.Critical) |
||
| 535 | Return False |
||
| 536 | End Try |
||
| 537 | End Function |
||
| 538 | |||
| 539 | Function SetSaveFile(ByVal outputFilepath As String) As Boolean |
||
| 540 | Try |
||
| 541 | _ExcelFile.Save(outputFilepath) |
||
| 542 | |||
| 543 | Return True |
||
| 544 | Catch ex As Exception |
||
| 545 | MsgBox(ex.Message, MsgBoxStyle.Critical) |
||
| 546 | Return False |
||
| 547 | End Try |
||
| 548 | End Function |
||
| 549 | '엑셀 셀 데이터 가져오기 |
||
| 550 | Function GetCellValue(ByVal mData As ExcelCell) As String |
||
| 551 | If mData.Value Is Nothing Then |
||
| 552 | Return String.Empty |
||
| 553 | Else |
||
| 554 | Return mData.Value.ToString |
||
| 555 | End If |
||
| 556 | End Function |
||
| 557 | '엑셀 셀 데이터 소문자로 가져오기 |
||
| 558 | Function GetCellValueToLower(ByVal mData As ExcelCell) As String |
||
| 559 | '소문자 변환 |
||
| 560 | Return GetCellValue(mData).ToLower |
||
| 561 | End Function |
||
| 562 | |||
| 563 | '검색스트링으로 엑셀파일 A(첫번째컬럼)의 Row찾기 |
||
| 564 | Function GetFindRow(ByVal mExcel As ExcelWorksheet, ByVal mName As String) As Integer |
||
| 565 | For mCnt = 1 To mExcel.Rows.Count - 1 |
||
| 566 | If GetCellValueToLower(mExcel.Rows(mCnt).Cells(0)) = mName.ToLower Then |
||
| 567 | Return mCnt |
||
| 568 | End If |
||
| 569 | Next |
||
| 570 | Return -1 |
||
| 571 | End Function |
||
| 572 | |||
| 573 | 'row열에 해당하는 마지막 Interface(컬럼) 인덱스 가져오기 |
||
| 574 | |||
| 575 | Function GetFindColumnCount(ByVal mExcel As ExcelWorksheet, ByVal mRowCnt As Integer) As Integer |
||
| 576 | If mRowCnt = -1 Then |
||
| 577 | Return -1 |
||
| 578 | End If |
||
| 579 | For mcnt = 1 To mExcel.CalculateMaxUsedColumns |
||
| 580 | If GetCellValue(mExcel.Rows(mRowCnt).Cells(mcnt)) = String.Empty Then |
||
| 581 | Return mcnt - 1 |
||
| 582 | End If |
||
| 583 | Next |
||
| 584 | Return -1 |
||
| 585 | End Function |
||
| 586 | |||
| 587 | '검색스트링으로 row에 컬럼들중에서 스트링과 같은 문자열인 컬럼 인덱스 가져오기 |
||
| 588 | |||
| 589 | Function getFindColumnIndexFromColumnName(ByVal mExcel As ExcelWorksheet, ByVal mrowcnt As Integer, ByVal mData As String, ByVal mColumnCount As Integer) As Integer |
||
| 590 | For mcnt = 1 To mColumnCount |
||
| 591 | If GetCellValueToLower(mExcel.Rows(mrowcnt).Cells(mcnt)) = mData.ToLower Then |
||
| 592 | Return mcnt |
||
| 593 | End If |
||
| 594 | Next |
||
| 595 | Return -1 |
||
| 596 | End Function |
||
| 597 | ' 검색스트링으로 row에 컬럼들중에서 스트링에 포함하는 컬럼 인덱스 가져오기 |
||
| 598 | '시작부터 돌면 = true, 끝에서 부터 돌면 = false |
||
| 599 | |||
| 600 | Function getFindColumnIndexFormContainColumnName(ByVal mExcel As ExcelWorksheet, ByVal mRowcnt As Integer, ByVal mData As String, ByVal mColumnCount As Integer, ByVal IsStandardSearch As Boolean) As Integer |
||
| 601 | If IsStandardSearch Then |
||
| 602 | ' true이면 시작점 부터 |
||
| 603 | For mcnt = 1 To mColumnCount |
||
| 604 | If GetCellValueToLower(mExcel.Rows(mRowcnt).Cells(mcnt)).StartsWith(mData.ToLower) Then |
||
| 605 | Return mcnt |
||
| 606 | End If |
||
| 607 | Next |
||
| 608 | Else |
||
| 609 | For mcnt = mColumnCount To 1 Step -1 |
||
| 610 | If GetCellValueToLower(mExcel.Rows(mRowcnt).Cells(mcnt)).StartsWith(mData.ToLower) Then |
||
| 611 | Return mcnt |
||
| 612 | End If |
||
| 613 | |||
| 614 | |||
| 615 | Next |
||
| 616 | End If |
||
| 617 | Return -1 |
||
| 618 | End Function |
||
| 619 | '해당 Row가 빈행인지 확인 |
||
| 620 | Function isBlankCheck(ByVal mExcel As ExcelWorksheet, ByVal mrowcnt As Integer, ByVal mColumnCnt As Integer) As Boolean |
||
| 621 | Dim mBlanckCheck As Boolean = True |
||
| 622 | For mcnt = 0 To mColumnCnt |
||
| 623 | If GetCellValue(mExcel.Rows(mrowcnt).Cells(mcnt)) <> String.Empty Then |
||
| 624 | mBlanckCheck = False |
||
| 625 | End If |
||
| 626 | Next |
||
| 627 | Return mBlanckCheck |
||
| 628 | End Function |
||
| 629 | |||
| 630 | '셀에 값넣기(가로, 세로 중앙 fit) |
||
| 631 | Public Sub SetCell(ByVal mExcelCell As ExcelCell, ByVal mValue As String) |
||
| 632 | mExcelCell.Value = mValue |
||
| 633 | mExcelCell.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center |
||
| 634 | mExcelCell.Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 635 | End Sub |
||
| 636 | |||
| 637 | |||
| 638 | '셀에 값 넣기(가로, 세로 중앙fit + 글자 진하게) |
||
| 639 | Public Sub SetCellBold(ByVal mExcelCell As ExcelCell, ByVal mValue As String) |
||
| 640 | SetCell(mExcelCell, mValue) |
||
| 641 | mExcelCell.Style.Font.Weight = ExcelFont.BoldWeight |
||
| 642 | End Sub |
||
| 643 | |||
| 644 | |||
| 645 | ' 셀에 값 넣기(가로, 세로 중앙fit + 셀 박스) |
||
| 646 | Public Sub SetCellBox(ByVal mExcelCell As ExcelCell, ByVal mValue As String) |
||
| 647 | SetCell(mExcelCell, mValue) |
||
| 648 | mExcelCell.SetBorders(MultipleBorders.Outside, System.Drawing.Color.Black, LineStyle.Thin) |
||
| 649 | End Sub |
||
| 650 | |||
| 651 | '셀에 값 넣기(가로, 세로 중앙fit + 글자 진하게 + 셀 박스) |
||
| 652 | Public Sub SetCellBoldAndBox(ByVal mExcelCell As ExcelCell, ByVal mValue As String) |
||
| 653 | SetCellBold(mExcelCell, mValue) |
||
| 654 | mExcelCell.SetBorders(MultipleBorders.Outside, System.Drawing.Color.Black, LineStyle.Thin) |
||
| 655 | End Sub |
||
| 656 | #End Region |
||
| 657 | |||
| 658 | Sub Init_Setting_Save_Piping_Material_Spec_Report(ByVal mEwSheet As ExcelWorksheet, ByVal mLastColumn As Integer, |
||
| 659 | ByVal mFirstRow As Integer, ByVal mValue As String, ByVal mHeaderName As String, ByVal mFirstColumn As Integer, ByVal mFontSize As Integer, |
||
| 660 | ByVal mRelativeCol As Integer) |
||
| 661 | |||
| 662 | mEwSheet.Cells(mFirstRow, mRelativeCol).Style.Font.Size = mFontSize |
||
| 663 | mEwSheet.Cells(mFirstRow, mRelativeCol).Style.Font.Weight = ExcelFont.BoldWeight |
||
| 664 | mEwSheet.Cells(mFirstRow, mRelativeCol).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center |
||
| 665 | mEwSheet.Cells(mFirstRow, mRelativeCol).Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 666 | mEwSheet.Cells(mFirstRow, mRelativeCol).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 667 | mEwSheet.Cells(mFirstRow, mRelativeCol).Style.FillPattern.SetSolid(Color.Orange) |
||
| 668 | mEwSheet.Cells(mFirstRow, mRelativeCol).Value = mHeaderName |
||
| 669 | mEwSheet.Cells.GetSubrangeAbsolute(mFirstRow, mFirstColumn, mFirstRow, mLastColumn).Merged = True |
||
| 670 | mEwSheet.Cells(mFirstRow, mRelativeCol + 1).Style.Font.Size = mFontSize |
||
| 671 | mEwSheet.Cells(mFirstRow, mRelativeCol + 1).Style.Font.Weight = ExcelFont.BoldWeight |
||
| 672 | mEwSheet.Cells(mFirstRow, mRelativeCol + 1).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center |
||
| 673 | mEwSheet.Cells(mFirstRow, mRelativeCol + 1).Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 674 | mEwSheet.Cells(mFirstRow, mRelativeCol + 1).Style.FillPattern.SetSolid(Color.Orange) |
||
| 675 | mEwSheet.Cells(mFirstRow, mRelativeCol + 1).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 676 | |||
| 677 | mEwSheet.Cells(mFirstRow, mRelativeCol + 1).Value = mValue |
||
| 678 | |||
| 679 | 'For mcnt = 0 To mEwSheet.Columns.Count - 1 |
||
| 680 | ' mEwSheet.Columns(mcnt).AutoFit() |
||
| 681 | 'Next |
||
| 682 | End Sub |
||
| 683 | |||
| 684 | Function Index_Input(ByVal mName As String) As Boolean |
||
| 685 | If _ExcelFile.Worksheets.Count = 0 Then |
||
| 686 | Dim mEwSheet As ExcelWorksheet = _ExcelFile.Worksheets.Add("Index")
|
||
| 687 | mEwSheet.Cells(0, 0).Value = "!Index" |
||
| 688 | Init_Setting_Save_Piping_Material_Spec_Report(mEwSheet, 1, 1, mName, "No", 1, 14 * 16, 1) |
||
| 689 | Return True |
||
| 690 | End If |
||
| 691 | Return False |
||
| 692 | End Function |
||
| 693 | |||
| 694 | |||
| 695 | Sub Init_Main_Column(ByVal mEwSheet As ExcelWorksheet, ByVal mRowInt As Integer, ByVal mColInt As Integer, |
||
| 696 | ByVal mFontSize As Integer, ByVal mBold As Integer, |
||
| 697 | ByVal mHorizontalAlighment As Integer, ByVal mVerticalAlighment As Integer, |
||
| 698 | ByVal mOutsize As Integer, ByVal mOutsideColor As Color, ByVal mOutsideLine As Integer, |
||
| 699 | ByVal mFillpattern As Color, ByVal mValue As String) |
||
| 700 | |||
| 701 | mEwSheet.Cells(mRowInt, mColInt).Style.Font.Size = mFontSize |
||
| 702 | mEwSheet.Cells(mRowInt, mColInt).Style.Font.Weight = mBold |
||
| 703 | mEwSheet.Cells(mRowInt, mColInt).Style.HorizontalAlignment = mHorizontalAlighment |
||
| 704 | mEwSheet.Cells(mRowInt, mColInt).Style.VerticalAlignment = mVerticalAlighment |
||
| 705 | mEwSheet.Cells(mRowInt, mColInt).SetBorders(mOutsize, mOutsideColor, mOutsideLine) |
||
| 706 | mEwSheet.Cells(mRowInt, mColInt).Style.FillPattern.SetSolid(mFillpattern) |
||
| 707 | mEwSheet.Cells(mRowInt, mColInt).Value = mValue |
||
| 708 | mEwSheet.Columns(mColInt).AutoFit() |
||
| 709 | End Sub |
||
| 710 | |||
| 711 | Sub Excel_Report_GRIDVIEW_Input(ByVal mHeaderInt As Integer, ByVal mUltragrid As DataGridView, ByVal mEwSheet As ExcelWorksheet) |
||
| 712 | Dim mRowcnt As Integer = mUltragrid.Rows.Count |
||
| 713 | Dim mColCnt As Integer = mUltragrid.Columns.Count |
||
| 714 | Dim mMainHeadercnt As Integer = mHeaderInt |
||
| 715 | '--------------------------------------------------------------- |
||
| 716 | ' 헤더 설정 |
||
| 717 | '-------------------------------------------------------------- |
||
| 718 | For i = 0 To mColCnt - 1 |
||
| 719 | '-------------------------------------------------------------------------------- |
||
| 720 | '이름. |
||
| 721 | '--------------------------------------------------------------------------------- |
||
| 722 | mEwSheet.Cells(mMainHeadercnt, i).Value = mUltragrid.Columns(i).ToString() |
||
| 723 | '--------------------------------------------------------------------------------- |
||
| 724 | ' 색깔. |
||
| 725 | '--------------------------------------------------------------------------------- |
||
| 726 | mEwSheet.Cells(mMainHeadercnt, i).Style.FillPattern.SetSolid(Color.CornflowerBlue) |
||
| 727 | '--------------------------------------------------------------------------------- |
||
| 728 | ' 글씨. |
||
| 729 | '--------------------------------------------------------------------------------- |
||
| 730 | mEwSheet.Cells(mMainHeadercnt, i).Style.Font.Color = Color.White |
||
| 731 | mEwSheet.Cells(mMainHeadercnt, i).Style.Font.Weight = ExcelFont.BoldWeight |
||
| 732 | '--------------------------------------------------------------------------------- |
||
| 733 | ' 줄. |
||
| 734 | '--------------------------------------------------------------------------------- |
||
| 735 | mEwSheet.Cells(mMainHeadercnt, i).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 736 | '--------------------------------------------------------------------------------- |
||
| 737 | ' 세로 정렬 |
||
| 738 | '--------------------------------------------------------------------------------- |
||
| 739 | mEwSheet.Cells(mMainHeadercnt, i).Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 740 | '--------------------------------------------------------------------------------- |
||
| 741 | ' 가로 정렬 |
||
| 742 | '--------------------------------------------------------------------------------- |
||
| 743 | mEwSheet.Cells(mMainHeadercnt, i).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center |
||
| 744 | Next |
||
| 745 | mMainHeadercnt += 1 |
||
| 746 | '--------------------------------------------------------------- |
||
| 747 | ' 리포트 행 데이터 입력. |
||
| 748 | '--------------------------------------------------------------- |
||
| 749 | ' Dim mGridRow() As UltraGridRow = mUltragrid.Rows.GetFilteredInNonGroupByRows() |
||
| 750 | |||
| 751 | ' Dim mblankRow As Integer = 2 |
||
| 752 | |||
| 753 | For i = 0 To mRowcnt - 1 |
||
| 754 | For j = 0 To mColCnt - 1 |
||
| 755 | Try |
||
| 756 | mEwSheet.Cells(mMainHeadercnt, j).Value = mUltragrid.Rows(i).Cells(j).Value |
||
| 757 | |||
| 758 | '--------------------------------------------------------------------------------- |
||
| 759 | ' 셀 색. |
||
| 760 | '--------------------------------------------------------------------------------- |
||
| 761 | If (mUltragrid.Rows(i).Cells(j).Style.BackColor = Color.Empty) Then |
||
| 762 | mEwSheet.Cells(mMainHeadercnt, j).Style.FillPattern.SetSolid(Color.Transparent) |
||
| 763 | Else |
||
| 764 | mEwSheet.Cells(mMainHeadercnt, j).Style.FillPattern.SetSolid(mUltragrid.Rows(i).Cells(j).Style.BackColor) |
||
| 765 | End If |
||
| 766 | |||
| 767 | '--------------------------------------------------------------------------------- |
||
| 768 | ' 줄. |
||
| 769 | '--------------------------------------------------------------------------------- |
||
| 770 | mEwSheet.Cells(mMainHeadercnt, j).Style.Borders.SetBorders(MultipleBorders.None, Color.Black, LineStyle.None) |
||
| 771 | '--------------------------------------------------------------------------------- |
||
| 772 | ' 세로 정렬 |
||
| 773 | '--------------------------------------------------------------------------------- |
||
| 774 | mEwSheet.Cells(mMainHeadercnt, j).Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 775 | '--------------------------------------------------------------------------------- |
||
| 776 | ' 가로 정렬 |
||
| 777 | '--------------------------------------------------------------------------------- |
||
| 778 | mEwSheet.Cells(mMainHeadercnt, j).Style.HorizontalAlignment = HorizontalAlignmentStyle.Left |
||
| 779 | Catch ex As Exception |
||
| 780 | |||
| 781 | End Try |
||
| 782 | ' 값 입력 |
||
| 783 | |||
| 784 | Next |
||
| 785 | mMainHeadercnt += 1 |
||
| 786 | Next |
||
| 787 | '--------------------------------------------------------------------------------- |
||
| 788 | ' 핏 |
||
| 789 | '--------------------------------------------------------------------------------- |
||
| 790 | For i = 0 To mColCnt - 1 |
||
| 791 | mEwSheet.Columns(i).AutoFit() |
||
| 792 | Next |
||
| 793 | |||
| 794 | End Sub |
||
| 795 | |||
| 796 | |||
| 797 | |||
| 798 | Sub Excel_Report_Datatable_Input(ByVal mHeaderInt As Integer, ByVal mdt As DataTable, ByVal mEwSheet As ExcelWorksheet) |
||
| 799 | |||
| 800 | Dim mRowcnt As Integer = mdt.Rows.Count |
||
| 801 | Dim mColCnt As Integer = mdt.Columns.Count |
||
| 802 | '--------------------------------------------------------------- |
||
| 803 | ' SpecName 설정 |
||
| 804 | '-------------------------------------------------------------- |
||
| 805 | Dim mMainHeadercnt As Integer = mHeaderInt |
||
| 806 | '--------------------------------------------------------------- |
||
| 807 | ' 헤더 설정 |
||
| 808 | '-------------------------------------------------------------- |
||
| 809 | For i = 0 To mColCnt - 1 |
||
| 810 | '-------------------------------------------------------------------------------- |
||
| 811 | '이름. |
||
| 812 | '--------------------------------------------------------------------------------- |
||
| 813 | mEwSheet.Cells(mMainHeadercnt, i).Value = mdt.Columns(i).ToString() |
||
| 814 | '--------------------------------------------------------------------------------- |
||
| 815 | ' 색깔. |
||
| 816 | '--------------------------------------------------------------------------------- |
||
| 817 | mEwSheet.Cells(mMainHeadercnt, i).Style.FillPattern.SetSolid(Color.Purple) |
||
| 818 | '--------------------------------------------------------------------------------- |
||
| 819 | ' 글씨. |
||
| 820 | '--------------------------------------------------------------------------------- |
||
| 821 | mEwSheet.Cells(mMainHeadercnt, i).Style.Font.Color = Color.White |
||
| 822 | mEwSheet.Cells(mMainHeadercnt, i).Style.Font.Weight = ExcelFont.BoldWeight |
||
| 823 | '--------------------------------------------------------------------------------- |
||
| 824 | ' 줄. |
||
| 825 | '--------------------------------------------------------------------------------- |
||
| 826 | mEwSheet.Cells(mMainHeadercnt, i).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 827 | '--------------------------------------------------------------------------------- |
||
| 828 | ' 세로 정렬 |
||
| 829 | '--------------------------------------------------------------------------------- |
||
| 830 | mEwSheet.Cells(mMainHeadercnt, i).Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 831 | '--------------------------------------------------------------------------------- |
||
| 832 | ' 가로 정렬 |
||
| 833 | '--------------------------------------------------------------------------------- |
||
| 834 | mEwSheet.Cells(mMainHeadercnt, i).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center |
||
| 835 | mEwSheet.Columns(i).AutoFit() |
||
| 836 | Next |
||
| 837 | mMainHeadercnt += 1 |
||
| 838 | '--------------------------------------------------------------- |
||
| 839 | ' 리포트 행 데이터 입력. |
||
| 840 | '--------------------------------------------------------------- |
||
| 841 | ' Dim mblankRow As Integer = 2 |
||
| 842 | |||
| 843 | For i = 0 To mdt.Rows.Count - 1 |
||
| 844 | For j = 0 To mdt.Columns.Count - 1 |
||
| 845 | 'If j = 0 Then |
||
| 846 | ' mEwSheet.Cells(mMainHeadercnt, j).Value = i + 1 |
||
| 847 | 'Else |
||
| 848 | ' ' 값 입력 |
||
| 849 | ' mEwSheet.Cells(mMainHeadercnt, j).Value = mdt.Rows(i)(j).ToString() |
||
| 850 | ' '--------------------------------------------------------------------------------- |
||
| 851 | 'End If |
||
| 852 | |||
| 853 | ' 값 입력 |
||
| 854 | mEwSheet.Cells(mMainHeadercnt, j).Value = mdt.Rows(i)(j).ToString() |
||
| 855 | |||
| 856 | ' 줄. |
||
| 857 | '--------------------------------------------------------------------------------- |
||
| 858 | mEwSheet.Cells(mMainHeadercnt, i).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin) |
||
| 859 | '--------------------------------------------------------------------------------- |
||
| 860 | ' 세로 정렬 |
||
| 861 | '--------------------------------------------------------------------------------- |
||
| 862 | mEwSheet.Cells(mMainHeadercnt, j).Style.VerticalAlignment = VerticalAlignmentStyle.Center |
||
| 863 | '--------------------------------------------------------------------------------- |
||
| 864 | ' 가로 정렬 |
||
| 865 | '--------------------------------------------------------------------------------- |
||
| 866 | mEwSheet.Cells(mMainHeadercnt, j).Style.HorizontalAlignment = HorizontalAlignmentStyle.Left |
||
| 867 | mEwSheet.Columns(j).AutoFit() |
||
| 868 | Next |
||
| 869 | mMainHeadercnt += 1 |
||
| 870 | Next |
||
| 871 | '--------------------------------------------------------------------------------- |
||
| 872 | ' 핏 |
||
| 873 | '--------------------------------------------------------------------------------- |
||
| 874 | 'For i = 0 To mColCnt - 1 |
||
| 875 | ' mEwSheet.Columns(i).AutoFit() |
||
| 876 | 'Next |
||
| 877 | End Sub |
||
| 878 | |||
| 879 | Sub HyperLink(ByVal mEwSheet As ExcelWorksheet) |
||
| 880 | '----------------------------------------- |
||
| 881 | ' 하이퍼 링크 |
||
| 882 | '---------------------------------------- |
||
| 883 | mEwSheet.Cells("A1").Value = "!index"
|
||
| 884 | mEwSheet.Cells("A1").Style.Font.UnderlineStyle = UnderlineStyle.Single
|
||
| 885 | mEwSheet.Cells("A1").Style.Font.Color = Color.Blue
|
||
| 886 | mEwSheet.Cells("A1").Hyperlink.Location = ("Index" & "!A1")
|
||
| 887 | Init_Setting_Save_Piping_Material_Spec_Report(_ExcelFile.Worksheets(0), 1, _Index_Cnt, mEwSheet.Name, _Index_Cnt - 1, 1, 14 * 16, 1) |
||
| 888 | '_ExcelFile.Worksheets(0).Cells("A" & _Index_Cnt).Value = _Index_Cnt - 1
|
||
| 889 | _ExcelFile.Worksheets(0).Cells("B" & _Index_Cnt + 1).Style.Font.UnderlineStyle = UnderlineStyle.Single
|
||
| 890 | _ExcelFile.Worksheets(0).Cells("B" & _Index_Cnt + 1).Style.Font.Color = Color.Blue
|
||
| 891 | _ExcelFile.Worksheets(0).Cells("B" & _Index_Cnt + 1).Hyperlink.Location = (mEwSheet.Name & "!A1")
|
||
| 892 | _ExcelFile.Worksheets(0).Columns(1).Width = 5000 |
||
| 893 | '_ExcelFile.Worksheets(0).Cells("B" & _Index_Cnt).Value = mEwSheet.Name
|
||
| 894 | _ExcelFile.Worksheets(0).Cells("C" & _Index_Cnt + 1).Style.Font.UnderlineStyle = UnderlineStyle.Single
|
||
| 895 | _ExcelFile.Worksheets(0).Cells("C" & _Index_Cnt + 1).Style.Font.Color = Color.Blue
|
||
| 896 | _ExcelFile.Worksheets(0).Cells("C" & _Index_Cnt + 1).Hyperlink.Location = (mEwSheet.Name & "!A1")
|
||
| 897 | _ExcelFile.Worksheets(0).Columns(2).Width = 9000 |
||
| 898 | ' _ExcelFile.Worksheets(0).Columns(1).AutoFit() |
||
| 899 | _Index_Cnt += 1 |
||
| 900 | End Sub |
||
| 901 | |||
| 902 | |||
| 903 | |||
| 904 | |||
| 905 | |||
| 906 | |||
| 907 | |||
| 908 | End Class |
||
| 909 |
