NPOI设置单元格数字格式

vb.net 专栏收录该内容
14 篇文章 0 订阅
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports NPOI.HSSF.UserModel
Imports NPOI.SS.Formula.Eval
Imports NPOI.SS.Formula.Functions
Imports NPOI.SS.UserModel
Imports NPOI.XSSF.UserModel
Imports NPOI.POIFS.FileSystem
Imports NPOI.HPSF
Imports System.IO
Imports NPOI.SS.Util
Imports System.Drawing
Imports NPOI.HSSF.Util
Public Class NopiDataFormat
    Private Shared Sub Main(args As String())
        '说明:设置数字格式

        '1.创建EXCEL中的Workbook        
        Dim myworkbook As IWorkbook = New XSSFWorkbook()

        '2.创建Workbook中的Sheet       
        Dim mysheet As ISheet = myworkbook.CreateSheet("sheet1")
        mysheet.SetColumnWidth(0, 20 * 256)
        mysheet.SetColumnWidth(1, 20 * 256)

        '3.创建Row中的Cell并赋值
        Dim row0 As IRow = mysheet.CreateRow(0)
        row0.CreateCell(0).SetCellValue(2013.143)
        row0.CreateCell(1).SetCellValue("转化为汉字大写")
        Dim row1 As IRow = mysheet.CreateRow(1)
        row1.CreateCell(0).SetCellValue(123152013.143)
        row1.CreateCell(1).SetCellValue("改变小数精度")
        Dim row2 As IRow = mysheet.CreateRow(2)
        row2.CreateCell(0).SetCellValue(123152013.143)
        row2.CreateCell(1).SetCellValue("分段添加,号")
        Dim row3 As IRow = mysheet.CreateRow(3)
        row3.CreateCell(0).SetCellValue(123152013.143)
        row3.CreateCell(1).SetCellValue("科学计数法")
        Dim row4 As IRow = mysheet.CreateRow(4)
        row4.CreateCell(0).SetCellValue(-123152013.143)
        row4.CreateCell(1).SetCellValue("正数与负数的区分(负数红色)")
        Dim row5 As IRow = mysheet.CreateRow(5)
        row5.CreateCell(0).SetCellValue(123152013.77)
        row5.CreateCell(1).SetCellValue("整数部分+分数")
        Dim row6 As IRow = mysheet.CreateRow(6)
        row6.CreateCell(0).SetCellValue(123152013.77)
        row6.CreateCell(1).SetCellValue("分数")
        Dim row7 As IRow = mysheet.CreateRow(7)
        row7.CreateCell(0).SetCellValue(0.333)
        row7.CreateCell(1).SetCellValue("百分数")

        '4.创建CellStyle与DataFormat并加载格式样式
        Dim dataformat As IDataFormat = myworkbook.CreateDataFormat()

        Dim style0 As ICellStyle = myworkbook.CreateCellStyle()
        style0.DataFormat = dataformat.GetFormat("[DbNum2][$-804]General")
        '转化为汉字大写
        Dim style1 As ICellStyle = myworkbook.CreateCellStyle()
        style1.DataFormat = dataformat.GetFormat("0.0")
        '改变小数精度【小数点后有几个0表示精确到小数点后几位】
        Dim style2 As ICellStyle = myworkbook.CreateCellStyle()
        style2.DataFormat = dataformat.GetFormat("#,##0.0")
        '分段添加,号
        Dim style3 As ICellStyle = myworkbook.CreateCellStyle()
        style3.DataFormat = dataformat.GetFormat("0.00E+00")
        '科学计数法
        Dim style4 As ICellStyle = myworkbook.CreateCellStyle()
        style4.DataFormat = dataformat.GetFormat("0.00;[Red]-0.00")
        '正数与负数的区分
        Dim style5 As ICellStyle = myworkbook.CreateCellStyle()
        style5.DataFormat = dataformat.GetFormat("# ??/??")
        '整数部分+分数
        Dim style6 As ICellStyle = myworkbook.CreateCellStyle()
        style6.DataFormat = dataformat.GetFormat("??/??")
        '分数
        Dim style7 As ICellStyle = myworkbook.CreateCellStyle()
        style7.DataFormat = dataformat.GetFormat("0.00%")
        '百分数【小数点后有几个0表示精确到显示小数点后几位】
        '5.将CellStyle应用于具体单元格
        row0.GetCell(0).CellStyle = style0
        row1.GetCell(0).CellStyle = style1
        row2.GetCell(0).CellStyle = style2
        row3.GetCell(0).CellStyle = style3
        row4.GetCell(0).CellStyle = style4
        row5.GetCell(0).CellStyle = style5
        row6.GetCell(0).CellStyle = style6
        row7.GetCell(0).CellStyle = style7

        '6.保存      
        Dim file As New FileStream("E:\myworkbook7.xlsx", FileMode.Create)
        myworkbook.Write(file)
        file.Close()
    End Sub
End Class

 

  • 0
    点赞
  • 0
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值