NPOI方法总结 VB 1.4.1版本（预用版本）

我们看看这里有很多块， 有台头标题的处理和   逐行添加的处理，   再者就是求和的处理。

 

多运行一下这些代码

 

1. 台头标题的处理， 看这里就有多表头的处理， 本人就对3行表头和2行表头的1行表头提供了支持。

 

View Code

  1             Dim HeadRow As NPOI.SS.UserModel.Row
  2             Dim HeadCell As Cell = Nothing
  3             Dim cl As Integer = p_dt.Columns.Count
  4             p_HeaderRow = If(p_HeaderRow <=  0 OrElse p_HeaderRow >  3,  1, p_HeaderRow)
  5             For l As Integer =  1 To p_HeaderRow
  6                 HeadRow = npoiSheet.CreateRow(p_StartRow)
  7                 p_StartRow +=  1
  8                 For i = p_StartColumn To cl + (p_StartColumn -  1)
  9                     HeadCell = HeadRow.CreateCell(i)
 10                     Dim car1 As Char() = New Char() { " | "}
 11                      ' 取值符号
 12                      Dim titleArray As String() = p_dt.Columns(i - p_StartColumn).Caption.ToString().Split(car1)
 13 
 14                     If p_HeaderRow =  3 Then
 15                          '  3行 表头
 16                          If titleArray.Length =  3 Then
 17                             If l =  1 Then
 18                                 HeadCell.CellStyle = cellstyleHeader
 19                             Else
 20                                 HeadCell.SetCellValue(titleArray(l -  1))
 21                                 HeadCell.CellStyle = cellstyleHeader
 22                             End If
 23                         ElseIf titleArray.Length =  2 Then
 24                             If l =  2 Then
 25                                 HeadCell.CellStyle = cellstyleHeader
 26                                 HeadCell.SetCellValue(titleArray( 0))
 27                             ElseIf l =  3 Then
 28                                 HeadCell.CellStyle = cellstyleHeader
 29                                 HeadCell.SetCellValue(titleArray( 1))
 30                             ElseIf l =  1 Then
 31                                 HeadCell.CellStyle = cellstyleHeader
 32                             End If
 33                         ElseIf titleArray.Length =  1 Then
 34                             If  3 = l Then
 35                                 HeadCell.SetCellValue(titleArray( 0))
 36                                 HeadCell.CellStyle = cellstyleHeader
 37                             End If
 38                             If l =  1 Then
 39                                 HeadCell.CellStyle = cellstyleHeader
 40                             End If
 41                         Else
 42                         End If
 43                     ElseIf p_HeaderRow =  2 Then
 44                          '  2行 表头 
 45                          If titleArray.Length =  2 Then
 46                             If l =  1 Then
 47                                 HeadCell.CellStyle = cellstyleHeader
 48                                 HeadCell.SetCellValue(titleArray(l -  1))
 49                             Else
 50                                 HeadCell.CellStyle = cellstyleHeader
 51                                 HeadCell.SetCellValue(titleArray(l -  1))
 52                             End If
 53                         ElseIf titleArray.Length =  1 Then
 54                             If  2 = l Then
 55                                 HeadCell.SetCellValue(titleArray( 0))
 56                                 HeadCell.CellStyle = cellstyleHeader
 57                             End If
 58                             If l =  1 Then
 59                                 HeadCell.CellStyle = cellstyleHeader
 60                             End If
 61                         Else
 62                              '  默认 头行  + （中间和尾行）组合 
 63                              If titleArray.Length =  3 Then
 64                                 If l =  1 Then
 65                                     HeadCell.SetCellValue(titleArray( 0))
 66                                     HeadCell.CellStyle = cellstyleHeader
 67                                 ElseIf l =  2 Then
 68                                     HeadCell.SetCellValue(titleArray( 1) & titleArray( 2))
 69                                     HeadCell.CellStyle = cellstyleHeader
 70                                 End If
 71                             ElseIf titleArray.Length =  2 Then
 72                                 If l =  1 Then
 73                                     HeadCell.CellStyle = cellstyleHeader
 74                                     HeadCell.SetCellValue(titleArray( 0))
 75                                 ElseIf l =  2 Then
 76                                     HeadCell.SetCellValue(titleArray( 1))
 77                                     HeadCell.CellStyle = cellstyleHeader
 78                                 End If
 79                             ElseIf titleArray.Length =  1 Then
 80                                 If  2 = l Then
 81                                     HeadCell.SetCellValue(titleArray( 0))
 82                                     HeadCell.CellStyle = cellstyleHeader
 83                                 End If
 84                                 If l =  1 Then
 85                                     HeadCell.CellStyle = cellstyleHeader
 86                                 End If
 87                             Else
 88 
 89                             End If
 90                         End If
 91                     Else
 92                          '  1行 表头
 93                          HeadCell.SetCellValue(p_dt.Columns(i - p_StartColumn).Caption.ToString().Replace( " | ",  ""))
 94                         HeadCell.CellStyle = cellstyleHeader
 95                     End If
 96                     If i = p_StartColumn Then
 97                         HeadCell.CellStyle = cellstyleHeader
 98                     End If
 99 
100                     HeadCell = Nothing
101                 Next
102                 Dim k As Integer
103                  ' 合并列 前1 前2行
104                  If l <> (If(p_HeaderRow =  2,  2, (If(p_HeaderRow =  3,  3,  1)))) Then
105                     For i = p_StartColumn To cl + (p_StartColumn -  1)
106                         HeadCell = HeadRow.GetCell(i)
107                         If  "" <> HeadCell.StringCellValue.ToString() AndAlso HeadCell.StringCellValue.ToString() IsNot Nothing Then
108                             Dim y As Integer =  0
109                             For k = i +  1 To cl + (p_StartColumn -  1)
110                                 Dim compareCell As Cell = Nothing
111                                 compareCell = HeadRow.GetCell(k)
112                                 If HeadCell.StringCellValue.ToString() = compareCell.StringCellValue.ToString() Then
113                                     If  "" <> compareCell.StringCellValue.ToString() AndAlso compareCell.StringCellValue.ToString() IsNot Nothing Then
114                                         y = k
115                                     End If
116                                 Else
117                                     k = cl + p_StartColumn
118                                 End If
119                             Next
120                             If y <>  0 Then
121                                 npoiSheet.AddMergedRegion(New CellRangeAddress(StartRow + l -  1, StartRow + l -  1, i, y))
122                             End If
123                         End If
124                         HeadCell = Nothing
125                     Next
126                 End If
127                 HeadRow = Nothing
128             Next
129 
130              ' 合并空格和添加备注
131              If  1 <> p_HeaderRow Then
132                 For i = p_StartColumn To cl + (p_StartColumn -  1)
133                     For l As Integer =  0 To p_HeaderRow -  1
134                         If l <> p_HeaderRow -  1 Then
135                             HeadRow = npoiSheet.GetRow(StartRow + l)
136                              '  0 , 1 rows
137                              HeadCell = HeadRow.GetCell(i)
138                             If  "" = HeadCell.StringCellValue.ToString() OrElse HeadCell.StringCellValue.ToString() Is Nothing Then
139                                 If p_HeaderRow =  3 Then
140                                     If  "" = npoiSheet.GetRow(StartRow + l +  1).GetCell(i).StringCellValue.ToString() Then
141                                         HeadCell.SetCellValue(npoiSheet.GetRow(StartRow +  2).GetCell(i).StringCellValue.ToString())
142                                         npoiSheet.AddMergedRegion(New CellRangeAddress(StartRow + l, StartRow +  2, i, i))
143                                         l =  2
144                                     Else
145                                         HeadCell.SetCellValue(npoiSheet.GetRow(StartRow +  1).GetCell(i).StringCellValue.ToString())
146                                     End If
147                                 Else
148                                     HeadCell.SetCellValue(npoiSheet.GetRow(StartRow +  1).GetCell(i).StringCellValue.ToString())
149                                     npoiSheet.AddMergedRegion(New CellRangeAddress(StartRow + l, StartRow +  1, i, i))
150                                 End If
151                             End If
152                             HeadRow = Nothing
153                             HeadCell = Nothing
154                         End If
155                     Next
156                 Next
157             End If

 

 2。 逐行添加的处理， 多宽度有最大的限制

 

View Code

 1  Dim DataRow As NPOI.SS.UserModel.Row
 2 
 3                 For Each row As DataRow In p_dt.Rows
 4                     DataRow = npoiSheet.CreateRow(p_StartRow)
 5                     p_StartRow +=  1
 6                     For Each drpt As DataColumn In p_dt.Columns
 7                         cell = DataRow.CreateCell(drpt.Ordinal + p_StartColumn)
 8                         Select Case drpt.DataType.ToString()
 9                             Case  " System.DateTime "
10                                 If Not row(drpt).Equals(System.DBNull.Value) AndAlso  "" <> row(drpt).ToString().Trim() Then
11                                     Dim p_dtime As DateTime
12                                     DateTime.TryParse(row(drpt).ToString(), p_dtime)
13                                     cell.SetCellValue(p_dtime.ToString( " yyyy-MM-dd HH:mm:ss "))
14                                 End If
15                                 cell.CellStyle = cellstyledate
16                                 Dim date1 As DateTime = DateTime.Now
17                                 Dim columnLength As Integer = System.Text.Encoding.Default.GetBytes(date1.ToString()).Length
18                                 npoiSheet.SetColumnWidth(drpt.Ordinal + p_StartColumn, (columnLength +  3) *  256)
19                                 Exit Select
20                             Case  " System.Int16 ",  " System.Int32 "
21                                 Dim doubInt As Integer
22                                 If Not row(drpt).Equals(System.DBNull.Value) AndAlso  "" <> row(drpt).ToString().Trim() Then
23                                     doubInt.TryParse(row(drpt).ToString(), doubInt)
24                                     cell.SetCellValue(doubInt)
25                                 End If
26 
27                                 cell.CellStyle = cellstylebody
28                                 Dim columnLength As Integer = System.Text.Encoding.Default.GetBytes(row(drpt).ToString()).Length
29                                 Dim IntcColumnWidth As Integer = IIf(npoiSheet.GetColumnWidth(drpt.Ordinal) >  3 *  256, npoiSheet.GetColumnWidth(drpt.Ordinal),  3 *  256)
30                                 If IntcColumnWidth <= (columnLength +  3) *  256 Then
31                                     npoiSheet.SetColumnWidth(drpt.Ordinal + p_StartColumn, (columnLength +  6) *  256)
32                                 End If
33                                 Exit Select
34                             Case  " System.Double ",  " System.Decimal "
35                                 Dim doubV As Double
36                                 If Not row(drpt).Equals(System.DBNull.Value) AndAlso  "" <> row(drpt).ToString().Trim() Then
37                                     Double.TryParse(row(drpt).ToString(), doubV)
38                                     cell.SetCellValue(doubV)
39                                 End If
40 
41                                 cell.CellStyle = cellstylebodyDouble
42                                 Dim columnLength As Integer = System.Text.Encoding.Default.GetBytes(row(drpt).ToString()).Length
43                                 Dim IntcColumnWidth As Integer = IIf(npoiSheet.GetColumnWidth(drpt.Ordinal) >  3 *  256, npoiSheet.GetColumnWidth(drpt.Ordinal),  3 *  256)
44                                 If IntcColumnWidth <= (columnLength +  3) *  256 Then
45                                     npoiSheet.SetColumnWidth(drpt.Ordinal + p_StartColumn, (columnLength +  6) *  256)
46                                 End If
47                                 Exit Select
48                             Case Else
49                                 cell.SetCellValue(row(drpt).ToString())
50                                 cell.CellStyle = cellstylebody
51 
52 
53                                  ' 最多宽度 不能超过 10个字符     
54                                  Dim columnLength As Integer = System.Text.Encoding.Default.GetBytes(row(drpt).ToString()).Length
55                                 Dim IntcColumnWidth As Integer = IIf(npoiSheet.GetColumnWidth(drpt.Ordinal) >  3 *  256, npoiSheet.GetColumnWidth(drpt.Ordinal),  3 *  256)
56                                 If IntcColumnWidth <= (columnLength +  3) *  256 Then
57                                     npoiSheet.SetColumnWidth(drpt.Ordinal + p_StartColumn, (columnLength +  3) *  256)
58                                 End If
59 
60                                 If  17 <= (columnLength +  3) AndAlso p_blAutoWF Then
61                                     npoiSheet.SetColumnWidth(drpt.Ordinal + p_StartColumn,  17 *  256)
62                                 End If
63                                 Exit Select
64                         End Select
65 
66                     Next
67                 Next

 

3.再者就是求和的处理， 这个就不计较的复杂了。 考虑到cpu的使用效率的 问题。 采用的是subtotal方法客户端的计算。但是宽度如何处理呢。

方法是有的需要考虑误差范围。

View Code

 1                     For Each column As DataColumn In p_dt.Columns
 2                         Dim cs As Integer = column.Ordinal + p_StartColumn
 3                         Select Case column.DataType.ToString()
 4                              ' 整型  
 5                               ' 浮点型  
 6                              Case  " System.Decimal ",  " System.Double "
 7                                 If cs <> p_StartColumn Then
 8                                     Totalcell = TotalRow.CreateCell(cs, CellType.NUMERIC)
 9                                     letter =  ""
10                                     If (cs \  26 -  1) > - 1 AndAlso (cs \  26 -  1) <  26 Then
11                                         letter = NumberToStr((cs \  26 -  1)) + NumberToStr(cs Mod  26)
12                                     Else
13                                         letter = NumberToStr(cs Mod  26)
14                                     End If
15 
16                                     Totalcell.CellFormula = String.Format( " SUBTOTAL(9,{0}{1}:{2}{3}) ", letter, intStart, letter, p_StartRow)
17                                     Dim IntcColumnWidth As Integer = If(npoiSheet.GetColumnWidth(cs) >  5 *  256, npoiSheet.GetColumnWidth(cs),  5 *  256)
18                                     Dim dl As Integer = Sqrt(arr_total(column.Ordinal))
19 
20                                     If arr_zero(column.Ordinal) Then
21                                         npoiSheet.SetColumnWidth(cs, (IntcColumnWidth + dl *  256))
22                                     End If
23 
24                                 End If
25                                 Exit Select
26                             Case Else
27                                 If cs <> p_StartColumn Then
28                                     Totalcell = TotalRow.CreateCell(cs)
29                                     Totalcell.CellStyle = cellstyleSumNumLast
30                                 End If
31                                 Exit Select
32                         End Select
33                         If (Not Totalcell Is Nothing) Then
34                             Select Case column.DataType.ToString()
35                                  ' 浮点型  
36                                  Case  " System.Decimal ",  " System.Double "
37                                     Totalcell.CellStyle = cellstyleSumNumLast
38                                     Exit Select
39                                 Case Else
40                                     Exit Select
41                             End Select
42                             Totalcell = Nothing
43                         End If
44 
45                     Next

 

方法1去excel列名

 

View Code

 1                     Dim letter As String
 2                     Dim NumberToStr As String() = New String( 25) {}
 3                     NumberToStr( 0) =  " A "
 4                     NumberToStr( 1) =  " B "
 5                     NumberToStr( 2) =  " C "
 6                     NumberToStr( 3) =  " D "
 7                     NumberToStr( 4) =  " E "
 8                     NumberToStr( 5) =  " F "
 9                     NumberToStr( 6) =  " G "
10                     NumberToStr( 7) =  " H "
11                     NumberToStr( 8) =  " I "
12                     NumberToStr( 9) =  " J "
13                     NumberToStr( 10) =  " K "
14                     NumberToStr( 11) =  " L "
15                     NumberToStr( 12) =  " M "
16                     NumberToStr( 13) =  " N "
17                     NumberToStr( 14) =  " O "
18                     NumberToStr( 15) =  " P "
19                     NumberToStr( 16) =  " Q "
20                     NumberToStr( 17) =  " R "
21                     NumberToStr( 18) =  " S "
22                     NumberToStr( 19) =  " T "
23                     NumberToStr( 20) =  " U "
24                     NumberToStr( 21) =  " V "
25                     NumberToStr( 22) =  " W "
26                     NumberToStr( 23) =  " X "
27                     NumberToStr( 24) =  " Y "
28                     NumberToStr( 25) =  " Z "

 

方法2去计算宽度

View Code

 1                     Dim arr_total As Integer() = New Integer(p_dt.Columns.Count -  1) {}
 2                     Dim arr_zero As Boolean() = New Boolean(p_dt.Columns.Count -  1) {}
 3 
 4                     For Each column As DataColumn In p_dt.Columns
 5                          '  column
 6                          Select Case column.DataType.ToString()
 7                              ' 整型  
 8                               ' 浮点型  
 9                              Case  " System.Decimal ",  " System.Double "
10                                 Dim rc As Integer = p_dt.Rows.Count
11                                 Dim arrNum As Integer() = New Integer(rc -  1) {}
12                                 Dim ten As Integer =  0
13                                 For j =  0 To rc -  1
14                                      '  row
15                                      arrNum(j) = p_dt.Rows(j)(column.Ordinal).ToString().Length
16                                     If Not p_dt.Rows(j)(column.Ordinal).Equals(System.DBNull.Value) Then
17 
18                                         Dim doubV As Double =  0
19                                         If Double.TryParse(p_dt.Rows(j)(column.Ordinal).ToString(), doubV) Then
20                                             If doubV >  0 Then
21                                                 arr_zero(column.Ordinal) = True
22                                             End If
23                                         End If
24 
25                                     End If
26 
27                                 Next
28 
29 
30                                 Dim list As New ArrayList(arrNum)
31                                 list.Sort()
32                                 If list.Count >  0 Then
33                                     If Not list(list.Count -  1).Equals(System.DBNull.Value) Then
34                                         Dim max As Integer
35                                         Dim intv As Integer =  0
36                                         If Integer.TryParse(list(list.Count -  1), intv) Then
37                                             max = Convert.ToInt32(list(list.Count -  1))
38                                         End If
39 
40                                         For j = (rc -  1) To  0 Step - 1
41                                              '  row
42                                              Dim min As Integer
43                                             If Not list(j).Equals(System.DBNull.Value) Then
44                                                 If Integer.TryParse(list(j), min) Then
45 
46                                                     If Convert.ToInt32(list(j)) = max Then
47                                                         ten +=  1
48                                                     Else
49                                                         j = - 1
50                                                     End If
51                                                     arr_total(column.Ordinal) = ten
52                                                 End If
53                                             End If
54                                         Next
55 
56                                     End If
57                                 End If
58 
59 
60                                 Exit Select
61                             Case Else
62                                 arr_total(column.Ordinal) =  0
63                                 Exit Select
64                         End Select
65                     Next
66 
67 
68 
69         Private Function Sqrt(ByVal d As Integer) As Integer
70             Dim temp As Integer =  10
71             Dim l As Integer =  0
72             While d > temp
73                 temp *=  10
74                 l +=  1
75             End While
76             l +=  4
77              ' 误差范围
78              Return l
79         End Function

 

 这个有什么不懂可以联系本人 QQ：1839467491

 

转载于:https://www.cnblogs.com/sandy_liao/archive/2011/11/15/2249425.html