重工电子论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
热搜: 活动 交友 discuz
查看: 24143|回复: 2

[C#] C# 用NPOI操作excel表格

[复制链接]

286

主题

666

帖子

5519

积分

学生管理组

Rank: 8Rank: 8

积分
5519
发表于 2016-3-27 18:04:47 | 显示全部楼层 |阅读模式
最近在做表格,一大堆SQL已经把我压垮。。
转一个C#操作表格的例程  用的NPOI
[C#] syntaxhighlighter_viewsource syntaxhighlighter_copycode
//
        // GET: /Excel/
        public ActionResult Write()
        {
            var workbook = new HSSFWorkbook();//从流内容创建Workbook对象
            ISheet sheet = ((HSSFWorkbook)workbook).CreateSheet("sheetOne");//创建工作表

            IRow row = sheet.CreateRow(0);//在工作表中添加一行
            ICell cell = row.CreateCell(1);//在行中添加一列
            cell.SetCellValue("test");//设置列的内容
            setCellStyle(workbook, cell);
            mergeCell(sheet, 0, 0, 1, 4);

            sheet = ((HSSFWorkbook)workbook).CreateSheet("sheet2");//创建工作表
            setCellDropdownlist(sheet);
            setCellInputNumber(sheet);

            string filePath = Server.MapPath("~/ExportFiles/test.xls");
            FileStream fs = new FileStream(filePath, FileMode.Create);
            workbook.Write(fs);
            fs.Close();

            return null;
        }

        /// <summary>
        /// 设置单元格为下拉框并限制输入值
        /// </summary>
        /// <param name="sheet"></param>
        private void setCellDropdownlist(ISheet sheet)
        {
            //设置生成下拉框的行和列
            var cellRegions = new CellRangeAddressList(0, 65535, 0, 0);

            //设置 下拉框内容
            DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(
                new string[] { "itemA", "itemB", "itemC" });

            //绑定下拉框和作用区域,并设置错误提示信息
            HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
            dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
            dataValidate.ShowPromptBox = true;

            sheet.AddValidationData(dataValidate);
        }

        /// <summary>
        /// 设置单元格只能输入数字
        /// </summary>
        /// <param name="sheet"></param>
        private void setCellInputNumber(ISheet sheet)
        {
            //设置生成下拉框的行和列
            var cellRegions = new CellRangeAddressList(0, 65535, 1, 1);

            //第二个参数int comparisonOperator  参考源码获取
            //https://github.com/tonyqus/npoi
            //NPOITest项目
            DVConstraint constraint = DVConstraint.CreateNumericConstraint(
                ValidationType.INTEGER, OperatorType.BETWEEN, "0", "100");
            
            HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
            dataValidate.CreateErrorBox("输入不合法", "请输入1~100的数字。");
            //dataValidate.PromptBoxTitle = "ErrorInput";

            sheet.AddValidationData(dataValidate);
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="firstRow"></param>
        /// <param name="lastRow"></param>
        /// <param name="firstCell"></param>
        /// <param name="lastCell"></param>
        private void mergeCell(ISheet sheet, int firstRow, int lastRow, int firstCell, int lastCell)
        {
            sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCell, lastCell));//2.0使用 2.0以下为Region
        }

        /// <summary>
        /// 设置单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="cell"></param>
        private void setCellStyle(HSSFWorkbook workbook, ICell cell)
        {
            HSSFCellStyle fCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            HSSFFont ffont = (HSSFFont)workbook.CreateFont();
            ffont.FontHeight = 20 * 20;
            ffont.FontName = "宋体";
            ffont.Color = HSSFColor.Red.Index;
            fCellStyle.SetFont(ffont);

            fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
            cell.CellStyle = fCellStyle;
        }
回复

使用道具 举报

286

主题

666

帖子

5519

积分

学生管理组

Rank: 8Rank: 8

积分
5519
 楼主| 发表于 2016-8-20 14:58:01 | 显示全部楼层
本帖最后由 李维强-15级 于 2016-8-20 14:59 编辑

[C#] syntaxhighlighter_viewsource syntaxhighlighter_copycode
//创建一个常用的xls文件
        private void button3_Click(object sender, EventArgs e)
        {          
            IWorkbook wb = new HSSFWorkbook();
            //创建表  
            ISheet sh = wb.CreateSheet("zhiyuan");
            //设置单元的宽度  
            sh.SetColumnWidth(0, 15 * 256);
            sh.SetColumnWidth(1, 35 * 256);
            sh.SetColumnWidth(2, 15 * 256);
            sh.SetColumnWidth(3, 10 * 256);
            int i = 0;
            #region 练习合并单元格
            sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3));

            //CellRangeAddress()该方法的参数次序是:开始行号,结束行号,开始列号,结束列号。
  
            IRow row0 = sh.CreateRow(0);
            row0.Height = 20 * 20;
            ICell icell1top0 = row0.CreateCell(0);
            icell1top0.CellStyle = Getcellstyle(wb, stylexls.头);
            icell1top0.SetCellValue("标题合并单元");
            #endregion
            i++;
            #region 设置表头
            IRow row1 = sh.CreateRow(1);
            row1.Height = 20 * 20;

            ICell icell1top = row1.CreateCell(0);
            icell1top.CellStyle = Getcellstyle(wb, stylexls.头);
            icell1top.SetCellValue("网站名");

            ICell icell2top = row1.CreateCell(1);
            icell2top.CellStyle = Getcellstyle(wb, stylexls.头);
            icell2top.SetCellValue("网址");

            ICell icell3top = row1.CreateCell(2);
            icell3top.CellStyle = Getcellstyle(wb, stylexls.头);
            icell3top.SetCellValue("百度快照");

            ICell icell4top = row1.CreateCell(3);
            icell4top.CellStyle = Getcellstyle(wb, stylexls.头);
            icell4top.SetCellValue("百度收录");
            #endregion  
  
            using(FileStream stm=File.OpenWrite(@"c:/myMergeCell.xls"))
            {
                wb.Write(stm); 
                MessageBox.Show("提示:创建成功!");
            }
        }



        #region 定义单元格常用到样式的枚举
        public enum stylexls
        {
            头,
            url,
            时间,
            数字,
            钱,
            百分比,
            中文大写,
            科学计数法,
            默认
        }
        #endregion


        #region 定义单元格常用到样式
        static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
        {
            ICellStyle cellStyle = wb.CreateCellStyle();

            //定义几种字体  
            //也可以一种字体,写一些公共属性,然后在下面需要时加特殊的  
            IFont font12 = wb.CreateFont();
            font12.FontHeightInPoints = 10;
            font12.FontName = "微软雅黑";


            IFont font = wb.CreateFont();
            font.FontName = "微软雅黑";
            //font.Underline = 1;下划线  


            IFont fontcolorblue = wb.CreateFont();
            fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index;
            fontcolorblue.IsItalic = true;//下划线  
            fontcolorblue.FontName = "微软雅黑";


            //边框  
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED;
            //边框颜色  
            cellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
            cellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;

            //背景图形,我没有用到过。感觉很丑  
            //cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;  
            //cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;  
            cellStyle.FillForegroundColor = HSSFColor.WHITE.index;
            // cellStyle.FillPattern = FillPatternType.NO_FILL;  
            cellStyle.FillBackgroundColor = HSSFColor.BLUE.index;

            //水平对齐  
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;

            //垂直对齐  
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

            //自动换行  
            cellStyle.WrapText = true;

            //缩进;当设置为1时,前面留的空白太大了。希旺官网改进。或者是我设置的不对  
            cellStyle.Indention = 0;

            //上面基本都是设共公的设置  
            //下面列出了常用的字段类型  
            switch (str)
            {
                case stylexls.头:
                    // cellStyle.FillPattern = FillPatternType.LEAST_DOTS;  
                    cellStyle.SetFont(font12);
                    break;
                case stylexls.时间:
                    IDataFormat datastyle = wb.CreateDataFormat();

                    cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.数字:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.钱:
                    IDataFormat format = wb.CreateDataFormat();
                    cellStyle.DataFormat = format.GetFormat("¥#,##0");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.url:
                    fontcolorblue.Underline = 1;
                    cellStyle.SetFont(fontcolorblue);
                    break;
                case stylexls.百分比:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.中文大写:
                    IDataFormat format1 = wb.CreateDataFormat();
                    cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.科学计数法:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.默认:
                    cellStyle.SetFont(font);
                    break;
            }
            return cellStyle;


        }
        #endregion
回复 支持 反对

使用道具 举报

286

主题

666

帖子

5519

积分

学生管理组

Rank: 8Rank: 8

积分
5519
 楼主| 发表于 2019-5-13 00:33:50 | 显示全部楼层
本帖最后由 李维强-15级 于 2019-5-13 00:36 编辑

https://www.cnblogs.com/zqyw/p/7462561.html

另外的库 aspose spire
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|cqutlab ( 渝ICP备15004556号

GMT+8, 2024-3-29 04:07 , Processed in 0.154545 second(s), 30 queries .

Powered by Discuz! X3.1

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表