- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- 合并单元格问题 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=128781)
-- 发布时间：2018/12/14 20:53:00
-- 合并单元格问题
Dim dt As Table = Tables("固定资产明细表")
Dim Book As New XLS.Book
Dim Sheet As XLS.Sheet = Book.Sheets(0) \'引用工作簿的第一个工作表
Dim c1 As String = e.Form.Controls("DateTimePicker1").value
Dim c2 As String = c1.SubString(0,4) & " 年 " & c1.SubString(5,2) & " 月 "
Dim Style As XLS.Style = Book.NewStyle() \'定义新样式
Dim Style1 As XLS.Style = Book.NewStyle()
Dim Style2 As XLS.Style = Book.NewStyle()
style2.Format = "0.00"
Sheet.Rows(0).Height = 40 \'第一行高设为40
Sheet.MergeCell(0,0,1,10) \'合并第一行共15列
Sheet(0,0).Value = "固 定 资 产 明 细 表"
sheet.MergeCell(1,0,1,10) \'合并第二行共15列
sheet(1,0).value = c2
style.Font = New Font("宋体", 18, FontStyle.Bold)
Style.AlignVert = XLS.AlignVertEnum.Center \'垂直对齐
Style.AlignHorz = XLS.AlignHorzEnum.Center \'水平对齐
sheet(0,0).Style = style
style1.Font = New Font("宋体", 12, FontStyle.Bold)
Style1.AlignVert = XLS.AlignVertEnum.Center \'垂直对齐
Style1.AlignHorz = XLS.AlignHorzEnum.Center \'水平对齐
sheet(1,0).Style = style1
sheet.Cols(8).Style = style2 \'设第9列保留2位小数 ####这行代码设定后出现第一行和每二行合并单元格从原来的a-o的合并,增加到a-r的合并?????
\'sheet.Cols(11).Style = style2
\'sheet.Cols(12).Style = style2
\'sheet.Cols(13).Style = style2
For c As Integer = 0 To dt.Cols.Count -1 \'添加列标题
Sheet(2, c).Value = dt.Cols(c).Name
Next
For r As Integer = 0 To dt.Rows.Count - 1 \'填入数据
For c As Integer = 0 To dt.Cols.Count -1
Sheet(r +3, c).Value = dt.rows(r)(c)
Next
Next
Dim St2 As XLS.Style = Book.NewStyle
St2.Format = "yyyy-MM-dd"
Sheet.Cols(dt.Cols("购建时间").Index).Style = st2
Book.Save("d:\\test.xls")
Dim Proc As New Process
Proc.File = "d:\\test.xls"
Proc.Start()
请教老师，上述代码中加粗这行代码设定后，出现第一行和每二行合并单元格从原来的a-o的合并,增加到a-r的合并，如何更正？
-- 发布时间：2018/12/14 20:58:00
--
设置样式不可能会影响合并。具体上传实例说明
-- 发布时间：2018/12/14 21:09:00
--
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:固定资产2018.zip |
-- 发布时间：2018/12/14 21:10:00
--
在窗口“固定资产明细表”
-- 发布时间：2018/12/14 21:23:00
--
把设置放到合并前
Dim dt As Table = Tables("固定资产明细表")
Dim Book As New XLS.Book
Dim Sheet As XLS.Sheet = Book.Sheets(0) \'引用工作簿的第一个工作表
Dim c1 As String = e.Form.Controls("DateTimePicker1").value
Dim c2 As String = c1.SubString(0,4) & " 年 " & c1.SubString(5,2) & " 月 "
Dim Style As XLS.Style = Book.NewStyle() \'定义新样式
Dim Style1 As XLS.Style = Book.NewStyle()
Dim Style2 As XLS.Style = Book.NewStyle()
style2.Format = "0.00"
Sheet.Rows(0).Height = 40 \'第一行高设为40
sheet.Cols(8).Style = style2 \'设第9列保留2位小数 ####这行代码设定后出现第一行和每二行合并单元格从原来的a-o的合并,增加到a-r的合并?????
sheet.Cols(11).Style = style2
sheet.Cols(12).Style = style2
sheet.Cols(13).Style = style2
Sheet.MergeCell(0,0,1,10) \'合并第一行共15列
Sheet(0,0).Value = "固 定 资 产 明 细 表"
sheet.MergeCell(1,0,1,10) \'合并第二行共15列
sheet(1,0).value = c2
……
-- 发布时间：2018/12/14 22:32:00
--
Dim dt As Table = Tables("固定资产明细表")
Dim Book As New XLS.Book
Dim Sheet As XLS.Sheet = Book.Sheets(0) \'引用工作簿的第一个工作表
Dim St2 As XLS.Style = Book.NewStyle
St2.Format = "yyyy-MM-dd"
Sheet.Cols(dt.Cols("购建时间").Index).Style = st2
Dim c1 As String = e.Form.Controls("DateTimePicker1").value
Dim c2 As String = c1.SubString(0,4) & " 年 " & c1.SubString(5,2) & " 月 "
Dim Style As XLS.Style = Book.NewStyle() \'定义新样式
Dim Style1 As XLS.Style = Book.NewStyle()
Dim Style2 As XLS.Style = Book.NewStyle()
style2.Format = "0.00"
Dim Style3 As XLS.Style = Book.NewStyle() \'设定居中,设定边框
Style3.AlignHorz = XLS.AlignHorzEnum.Center \'水平对齐
Style3.BorderTop = XLS.LineStyleEnum.Thin
Style3.BorderBottom = XLS.LineStyleEnum.Thin
Style3.BorderLeft = XLS.LineStyleEnum.Thin
Style3.BorderRight = XLS.LineStyleEnum.Thin
Style3.BorderColorTop = Color.black
Style3.BorderColorBottom = Color.black
Style3.BorderColorLeft = Color.black
Style3.BorderColorRight = Color.black
Dim Style4 As XLS.Style = Book.NewStyle() \'设定靠右
style4.AlignHorz =XLS.AlignHorzEnum.Right \'靠右
Dim Style5 As XLS.Style = Book.NewStyle() \'设定边框
Style5.BorderTop = XLS.LineStyleEnum.Thin
Style5.BorderBottom = XLS.LineStyleEnum.Thin
Style5.BorderLeft = XLS.LineStyleEnum.Thin
Style5.BorderRight = XLS.LineStyleEnum.Thin
Style5.BorderColorTop = Color.black
Style5.BorderColorBottom = Color.black
Style5.BorderColorLeft = Color.black
Style5.BorderColorRight = Color.black
style5.Format = "0.00"
sheet.Cols(8).Style = style2 \'设第9列保留2位小数
sheet.Cols(11).Style = style2
sheet.Cols(12).Style = style2
sheet.Cols(13).Style = style2
Sheet.Rows(0).Height = 40 \'第一行高设为40
Sheet.MergeCell(0,0,1,15) \'合并第一行共15列
Sheet(0,0).Value = "固 定 资 产 明 细 表"
sheet.MergeCell(1,0,1,15) \'合并第二行共15列
sheet(1,0).value = c2
style.Font = New Font("宋体", 18, FontStyle.Bold)
Style.AlignVert = XLS.AlignVertEnum.Center \'垂直对齐
Style.AlignHorz = XLS.AlignHorzEnum.Center \'水平对齐
sheet(0,0).Style = style
style1.Font = New Font("宋体", 12, FontStyle.Bold)
Style1.AlignVert = XLS.AlignVertEnum.Center \'垂直对齐
Style1.AlignHorz = XLS.AlignHorzEnum.Center \'水平对齐
sheet(1,0).Style = style1
For c As Integer = 0 To dt.Cols.Count -1 \'添加列标题
Sheet(2, c).Value = dt.Cols(c).Name
sheet(2, c).Style = style3
Next
For r As Integer = 0 To dt.Rows.Count - 1 \'填入数据
For c As Integer = 0 To dt.Cols.Count -1
Sheet(r +3, c).Value = dt.rows(r)(c)
sheet(r +3, c).Style = style5 \'#########此条代码加入后,日期栏无法正常显示
Next
Next
Dim s1 As Integer = dt.Rows.Count + 3
sheet(s1, 0).value = "合 计" \'打印合计
sheet(s1, 0).Style = style5 \'设定边框
sheet(s1, 1).Style = style5 \'设定边框
sheet(s1, 2).Style = style5 \'设定边框
sheet(s1, 3).Style = style5 \'设定边框
sheet(s1, 4).Style = style5 \'设定边框
sheet(s1, 5).Style = style5 \'设定边框
sheet(s1, 6).Style = style5 \'设定边框
sheet(s1, 7).Style = style5 \'设定边框
sheet(s1, 8).Style = style5 \'设定边框
sheet(s1, 9).Style = style5 \'设定边框
sheet(s1, 10).Style = style5 \'设定边框
sheet(s1, 11).Style = style5 \'设定边框
sheet(s1, 12).Style = style5 \'设定边框
sheet(s1, 13).Style = style5 \'设定边框
sheet(s1, 14).Style = style5 \'设定边框
sheet(s1, 6).value = cdbl(format(dt.compute("Sum(购建原值)"),"0.00"))
\'sheet(s1, 6).Style = style4 \'设定靠右
sheet(s1, 8).value = cdbl(format(dt.compute("Sum(残值)"),"0.00"))
\'sheet(s1, 8).Style = style4 \'设定靠右
sheet(s1, 11).value = cdbl(format(dt.compute("Sum(本月计提折旧)"),"0.00"))
\'sheet(s1, 11).Style = style4 \'设定靠右
sheet(s1, 12).value = cdbl(format(dt.compute("Sum(累计计提折旧)"),"0.00"))
\'sheet(s1, 12).Style = style4 \'设定靠右
sheet(s1, 13).value = cdbl(format(dt.compute("Sum(净值)"),"0.00"))
\'sheet(s1, 13).Style = style4 \'设定靠右
sheet.Cols(0).Width = 60
sheet.Cols(1).Width = 80
sheet.Cols(2).Width = 120
sheet.Cols(3).Width = 80
sheet.Cols(4).Width = 100
sheet.Cols(5).Width = 50
sheet.Cols(6).Width = 100
sheet.Cols(7).Width = 50
sheet.Cols(8).Width = 100
sheet.Cols(9).Width = 80
sheet.Cols(10).Width = 90
sheet.Cols(11).Width = 100
sheet.Cols(12).Width = 100
sheet.Cols(13).Width = 100
With Sheet.PrintSetting \'打印设置
.PaperKind = 9 \'设为A4纸
.LandScape = True \'横向打印
.MarginLeft = 20 \'左右边距设为20毫米
.MarginRight = 20
.MarginTop = 15 \'上下边距设为15毫米
.MarginBottom = 15
.AutoScale = True \'自动缩放
\' .FitPagesDown = 1 \'垂直方向缩为1页
.FitPagesAcross = 1 \'水平方向缩为1页
End With
Book.Save("d:\\test.xls")
Dim Proc As New Process
Proc.File = "d:\\test.xls"
Proc.Start()
请教老师，上述代码中加粗这行代码设定后，日期栏不能正常显示，请教如何更正？
-- 发布时间：2018/12/14 22:35:00
--
| 下载信息 [文件大小： 下载次数： ] | |
| 点击浏览该文件:固定资产2018-1.zip |
-- 发布时间：2018/12/14 22:40:00
--
如果一个单元格有多种格式要设置，比如日期+边框+颜色+对齐，那么这几张格式都要设置到一起，同时这个单元格要单独设置，或者放到最后设置
-- 发布时间：2018/12/14 22:55:00
--
Dim St2 As XLS.Style = Book.NewStyle
St2.Format = "yyyy-MM-dd"
Sheet.Cols(dt.Cols("购建时间").Index).Style = st2
我将上述代码移到最后，日期还是不正常显示
-- 发布时间：2018/12/14 23:06:00
--
设置单元格的样式。行、列、单元格都可以设置样式，优先顺序是：单元格>行>列。
For r As Integer = 0 To dt.Rows.Count - 1 \'填入数据
For c As Integer = 0 To dt.Cols.Count -1
Sheet(r +3, c).Value = dt.rows(r)(c)
if c=4 \'日期列的索引
sheet(r +3, c).Style = 日期列的样式
else
sheet(r +3, c).Style = style5 \'#########此条代码加入后,日期栏无法正常显示
end if
Next
Next