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