我们看看这里有很多块， 有台头标题的处理和 逐行添加的处理， 再者就是求和的处理。
多运行一下这些代码
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 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
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
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 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
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
