文档章节

NPOI导出Excel封装

o
 osc_mervd488
发布于 2018/04/23 22:11
字数 391
阅读 14
收藏 0

精选30+云产品,助力企业轻松上云!>>>

直接上代码

public class ExcelUtils
    {
        public static ICellStyle CreateStyle(IWorkbook workbook,
            string fontName = "宋体",
            int fontSize = 10,
            bool isBold = false,
            HorizontalAlignment horizontalAlignment = HorizontalAlignment.Left,
            VerticalAlignment verticalAlignment = VerticalAlignment.Center,
            bool wrapText = false,
            BorderStyle left = BorderStyle.Thin,
            BorderStyle right = BorderStyle.Thin,
            BorderStyle top = BorderStyle.Thin,
            BorderStyle bottom = BorderStyle.Thin
            )
        {
            IFont font = workbook.CreateFont();
            font.FontName = fontName;
            font.FontHeightInPoints = (short)fontSize;
            font.IsBold = isBold;
            ICellStyle style = workbook.CreateCellStyle();
            style.SetFont(font);
            style.Alignment = horizontalAlignment;
            style.VerticalAlignment = verticalAlignment;
            style.WrapText = wrapText;
            style.BorderLeft = left;
            style.BorderRight = right;
            style.BorderTop = top;
            style.BorderBottom = bottom;
            return style;
        }
    }

    public class ExcelRow
    {
        /// <summary>
        /// 
        /// </summary>
        public ISheet Sheet { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public IRow Row { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public int RowIndex { get; private set; }

        public int ColumnIndex { get; set; }

        public const int HeightConstant = 20;

        public ExcelRow(ISheet sheet, int height = 20)
        {
            if (sheet.PhysicalNumberOfRows == 0)
            {
                this.RowIndex = 0;
            }
            else
            {
                this.RowIndex = sheet.PhysicalNumberOfRows ;
            }
            this.ColumnIndex = 0;
            this.Sheet = sheet;
            this.Row = this.Sheet.CreateRow(this.RowIndex);
            this.Row.Height = (short)(HeightConstant * height);
        }


    }

    public class ExcelCell
    {
        /// <summary>
        /// 
        /// </summary>
        public ExcelRow Row { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public ICell Cell { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public int ColumnIndex { get; set; }

        public const int WidthConstant = 256;
        public ExcelCell(ExcelRow row, ICellStyle style, int width = 10)
        {
            this.Row = row;
            this.ColumnIndex = this.Row.ColumnIndex++;

            this.Cell = this.Row.Row.CreateCell(this.ColumnIndex, CellType.Blank);
            this.Row.Sheet.SetColumnWidth(this.ColumnIndex, width * WidthConstant);
            this.Cell.CellStyle = style;

        }


        public ExcelCell SetValue(string value)
        {
            this.Cell.SetCellValue(value);
            this.Cell.SetCellType(CellType.String);
            return this;
        }

        public ExcelCell SetValue(bool value)
        {
            this.Cell.SetCellValue(value);
            this.Cell.SetCellType(CellType.Boolean);
            return this;
        }

        public ExcelCell SetValue(double value)
        {
            this.Cell.SetCellValue(value);
            this.Cell.SetCellType(CellType.Numeric);
            return this;
        }

        public ExcelCell SetValue(DateTime value)
        {
            this.Cell.SetCellValue(value);
            return this;
        }

        public ExcelCell SetRowSpan(int rowspan)
        {
            this.Row.Sheet.AddMergedRegion(new CellRangeAddress(this.Row.RowIndex, this.Row.RowIndex + rowspan - 1, this.ColumnIndex, this.ColumnIndex));
            return this;
        }

        public ExcelCell SetColSpan(int colspan)
        {
            this.Row.Sheet.AddMergedRegion(new CellRangeAddress(this.Row.RowIndex, this.Row.RowIndex, this.ColumnIndex, this.ColumnIndex + colspan - 1));
            return this;
        }


    }

调用方法:

MemoryStream stream = new MemoryStream();
            IWorkbook workbook = new HSSFWorkbook();
            ICellStyle headStyle = ExcelUtils.CreateStyle(workbook, "宋体", 10, true);
            ICellStyle bodyStyle = ExcelUtils.CreateStyle(workbook, "宋体", 10, false);


            ExcelRow tr = null;
            ExcelCell td = null;
      

            ISheet sheet = workbook.CreateSheet("Sheet0");

            tr = new ExcelRow(sheet);
            td = new ExcelCell(tr, headStyle);
            td.SetValue("序号");

            td = new ExcelCell(tr, headStyle);
            td.SetValue("姓名");

            td = new ExcelCell(tr, headStyle);
            td.SetValue("金额");

            for (int i = 0; i < 10; i++)
            {
                tr = new ExcelRow(sheet);
                td = new ExcelCell(tr, bodyStyle);
                td.SetValue(i + 1);

                td = new ExcelCell(tr, bodyStyle);
                td.SetValue("姓名");

                td = new ExcelCell(tr, bodyStyle);
                td.SetValue(new Random().Next(1, 100));
            }

            td.SetColSpan(3);

            //fs.Flush();
            // fs.Close();
            workbook.Write(stream);//将Excel写入流
            stream.Flush();
            stream.Position = 0;


            File.WriteAllBytes("a.xls", stream.GetBuffer());

        
            Console.Read();

 

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。

暂无文章

Linux安装redis服务器和部署

Linux安装redis和部署 第一步:下载安装包 wget http://download.redis.io/releases/redis-5.0.5.tar.gz 访问https://redis.io/download 到官网进行下载。这里下载最新的5.0.5版本. 第二步:...

osc_3ytpwpyb
25分钟前
23
0
IF函数,根据条件设定输入内容

if函数通常用于条件判断,根据判断结果执行相应命令。 1.函数解释: IF(logical_test, [value_if_true], [value_if_false]) logical_test 必需。 计算结果为 TRUE 或 FALSE 的任何值或表达式...

osc_sumf8h95
27分钟前
9
0
Pytorch自定义dataloader以及在迭代过程中返回image的name

pytorch官方给的加载数据的方式是已经定义好的dataset以及loader,如何加载自己本地的图片以及label? 形如数据格式为 image1 label1 image2 label2 ... imagen labeln 实验中我采用的数据的...

osc_l8u38961
29分钟前
6
0
灯塔

\[love\ and \ share \] 我怎么感觉变成了好东西推荐呢?算了,本来也差不多 还没写完,想到再更 有好看玩的能不能评论一下,qwq 动漫 大多是些国漫,多在\(b\)站、腾讯视频、盗版小网站能够...

osc_dc6pbw3x
30分钟前
9
0
网易首页 」 网易手机 」 正文 苹果超薄触摸显示技术专利曝光:重新定义轻薄

最近,苹果公司的新屏幕专利技术已经曝光。特别是苹果公司的新型超薄触摸技术,它可以降低显示器的结构水平,消除多余的电路,并使屏幕更薄。该专利表明,这项新技术适用于iPhone,iPad,App...

osc_opzpp18v
31分钟前
7
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部