当前位置:网站首页 / C#开发 / 正文

AY NPOI Excel DEMO 自己笔记2

时间:2020年05月14日 | 作者 : aaronyang | 分类 : C#开发 | 浏览: 120次 | 评论 0

DEMO4  链接样式文本

  private void 链接_Click(object sender, RoutedEventArgs e)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Ay 链接");

            //蓝色字体,下面带一条线
            ICellStyle hlink_style = workbook.CreateCellStyle();
            IFont hlink_font = workbook.CreateFont();
            hlink_font.Underline = FontUnderlineType.Single;
            hlink_font.Color = HSSFColor.Blue.Index;
            hlink_style.SetFont(hlink_font);

            var cell = sheet.CreateRow(0).CreateCell(0);
            cell.SetCellValue("URL链接");
            XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
            link.Address = ("http://www.ayjs.net");
            cell.Hyperlink = link;
            cell.CellStyle = hlink_style;



            using (FileStream sw = File.Create(Dir + "链接.xlsx"))
            {
                workbook.Write(sw);
            }

        }

image.png


DEMO5 本地文件链接,邮件链接,跳转到sheet文件

 private void 链接_Click(object sender, RoutedEventArgs e)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Ay 链接");

            //蓝色字体,下面带一条线
            ICellStyle hlink_style = workbook.CreateCellStyle();
            IFont hlink_font = workbook.CreateFont();
            hlink_font.Underline = FontUnderlineType.Single;
            hlink_font.Color = HSSFColor.Blue.Index;
            hlink_style.SetFont(hlink_font);

            var cell = sheet.CreateRow(0).CreateCell(0);
            cell.SetCellValue("URL链接");
            XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
            link.Address = ("http://www.ayjs.net");
            cell.Hyperlink = link;
            cell.CellStyle = hlink_style;



            cell = sheet.CreateRow(1).CreateCell(0);
            cell.SetCellValue("文件 链接");
            link = new XSSFHyperlink(HyperlinkType.File);
            link.Address = ("创建行单元格数据.xlsx");
            cell.Hyperlink = link;
            cell.CellStyle = hlink_style;


            cell = sheet.CreateRow(2).CreateCell(0);
            cell.SetCellValue("Email 链接");
            link = new XSSFHyperlink(HyperlinkType.Email);
            link.Address = ("mailto:ay2015@qq.com");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);


            //跳转到其他sheet
            ISheet sheet2 = workbook.CreateSheet("ay 目标sheet");
            sheet2.CreateRow(0).CreateCell(0).SetCellValue("目标单元格");
            cell = sheet.CreateRow(3).CreateCell(0);
            cell.SetCellValue("跳转到 ay 目标sheet");
            link = new XSSFHyperlink(HyperlinkType.Document);
            link.Address = ("'ay 目标sheet'!A1");
            cell.Hyperlink = link;
            cell.CellStyle = hlink_style;


            using (FileStream sw = File.Create(Dir + "链接.xlsx"))
            {
                workbook.Write(sw);
            }

        }

image.png

image.png


image.png


链接地址注意: 使用URLEncode 加密下



DEMO6 字体设置

      private void 字体_Click(object sender, RoutedEventArgs e)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Ay 字体");

            IFont font1 = workbook.CreateFont();
            font1.Color = IndexedColors.Red.Index;
            font1.IsItalic = true;
            font1.Underline = FontUnderlineType.Double;
            font1.FontHeightInPoints = 20;

            ICellStyle style1 = workbook.CreateCellStyle();
            style1.SetFont(font1);

            ICell cell1 = sheet.CreateRow(1).CreateCell(1);
            cell1.SetCellValue("Hello World!");
            cell1.CellStyle = style1;

            //样式2

            IFont font2 = workbook.CreateFont();
            font2.Color = IndexedColors.OliveGreen.Index;
            font2.IsStrikeout = true;
            font2.FontHeightInPoints = 15;
            font2.FontName = "隶书";

            ICellStyle style2 = workbook.CreateCellStyle();
            style2.SetFont(font2);

            ICell cell2 = sheet.CreateRow(3).CreateCell(1);
            cell2.SetCellValue("早上好!");
            cell2.CellStyle = style2;

            //富文本
            XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM");
            IFont font4 = workbook.CreateFont();
            font4.FontHeightInPoints = 12;
            richtext.ApplyFont(0, 16, font4);

            IFont font3 = workbook.CreateFont();
            font3.TypeOffset = FontSuperScript.Super;
            font3.IsItalic = true;
            font3.Color = IndexedColors.Blue.Index;
            font3.FontHeightInPoints = 8;
            richtext.ApplyFont(16, 18, font3);

            ICell cell3 = sheet.CreateRow(5).CreateCell(1);
            cell3.SetCellValue(richtext);


            using (FileStream sw = File.Create(Dir + "字体.xlsx"))
            {
                workbook.Write(sw);
            }

        }

image.png



DEMO7 边框

   private void 边框_Click(object sender, RoutedEventArgs e)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Ay 边框");

            IRow row = sheet.CreateRow(1);
            ICell cell = row.CreateCell(1);
            cell.SetCellValue(4);

            ICellStyle style = workbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.Thin;
            style.BottomBorderColor = IndexedColors.Black.Index;
            style.BorderLeft = BorderStyle.DashDotDot;
            style.LeftBorderColor = IndexedColors.Green.Index;
            style.BorderRight = BorderStyle.Hair;
            style.RightBorderColor = IndexedColors.Blue.Index;
            style.BorderTop = BorderStyle.MediumDashed;
            style.TopBorderColor = IndexedColors.Orange.Index;

            style.BorderDiagonalLineStyle = BorderStyle.Medium; //this property必须在BorderDiagonal and BorderDiagonalColor之前设置
            style.BorderDiagonal = BorderDiagonal.Forward;
            style.BorderDiagonalColor = IndexedColors.Gold.Index;
            cell.CellStyle = style;

            ICell cell2 = row.CreateCell(2);
            cell2.SetCellValue(5);
            ICellStyle style2 = workbook.CreateCellStyle();
            style2.BorderDiagonalLineStyle = BorderStyle.Medium;
            style2.BorderDiagonal = BorderDiagonal.Backward;
            style2.BorderDiagonalColor = IndexedColors.Red.Index;
            cell2.CellStyle = style2;



            using (FileStream sw = File.Create(Dir + "边框.xlsx"))
            {
                workbook.Write(sw);
            }

        }

image.png



DEMO8 单元格stringformat

private void 单元格格式化_Click(object sender, RoutedEventArgs e)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Ay 单元格格式化");

            //increase the width of Column A
            sheet.SetColumnWidth(0, 5000);
            //create the format instance
            IDataFormat format = workbook.CreateDataFormat();

            // Create a row and put some cells in it. Rows are 0 based.
            ICell cell = sheet.CreateRow(0).CreateCell(0);
            //number format with 2 digits after the decimal point - "1.20"
            SetValueAndFormat(workbook, cell, 1.2, HSSFDataFormat.GetBuiltinFormat("0.00"));

            //RMB currency format with comma    -   "¥20,000"
            ICell cell2 = sheet.CreateRow(1).CreateCell(0);
            SetValueAndFormat(workbook, cell2, 20000, format.GetFormat("¥#,##0"));

            //scentific number format   -   "3.15E+00"
            ICell cell3 = sheet.CreateRow(2).CreateCell(0);
            SetValueAndFormat(workbook, cell3, 3.151234, format.GetFormat("0.00E+00"));

            //percent format, 2 digits after the decimal point    -  "99.33%"
            ICell cell4 = sheet.CreateRow(3).CreateCell(0);
            SetValueAndFormat(workbook, cell4, 0.99333, format.GetFormat("0.00%"));

            //phone number format - "021-65881234"
            ICell cell5 = sheet.CreateRow(4).CreateCell(0);
            SetValueAndFormat(workbook, cell5, 02165881234, format.GetFormat("000-00000000"));

            //Chinese capitalized character number - 壹贰叁 元
            ICell cell6 = sheet.CreateRow(5).CreateCell(0);
            SetValueAndFormat(workbook, cell6, 123, format.GetFormat("[DbNum2][$-804]0 元"));

            //Chinese date string
            ICell cell7 = sheet.CreateRow(6).CreateCell(0);
            SetValueAndFormat(workbook, cell7, new DateTime(2004, 5, 6), format.GetFormat("yyyy年m月d日"));
            cell7.SetCellValue(new DateTime(2004, 5, 6));

            //Chinese date string
            ICell cell8 = sheet.CreateRow(7).CreateCell(0);
            SetValueAndFormat(workbook, cell8, new DateTime(2005, 11, 6), format.GetFormat("yyyy年m月d日"));

            //formula value with datetime style 
            ICell cell9 = sheet.CreateRow(8).CreateCell(0);
            cell9.CellFormula = "DateValue(\"2005-11-11\")+TIMEVALUE(\"11:11:11\")";
            ICellStyle cellStyle9 = workbook.CreateCellStyle();
            cellStyle9.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
            cell9.CellStyle = cellStyle9;

            //display current time
            ICell cell10 = sheet.CreateRow(9).CreateCell(0);
            SetValueAndFormat(workbook, cell10, DateTime.Now, format.GetFormat("[$-409]h:mm:ss AM/PM;@"));



            using (FileStream sw = File.Create(Dir + "单元格格式化.xlsx"))
            {
                workbook.Write(sw);
            }

        }
        void SetValueAndFormat(IWorkbook workbook, ICell cell, int value, short formatId)
        {
            cell.SetCellValue(value);
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.DataFormat = formatId;
            cell.CellStyle = cellStyle;
        }

        void SetValueAndFormat(IWorkbook workbook, ICell cell, double value, short formatId)
        {
            cell.SetCellValue(value);
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.DataFormat = formatId;
            cell.CellStyle = cellStyle;
        }
        void SetValueAndFormat(IWorkbook workbook, ICell cell, DateTime value, short formatId)
        {
            //set value for the cell
            if (value != null)
                cell.SetCellValue(value);

            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.DataFormat = formatId;
            cell.CellStyle = cellStyle;
        }

image.png


    cellStyle.DataFormat = formatId;


单元格表达式

            ICell cell9 = sheet.CreateRow(8).CreateCell(0);

            cell9.CellFormula = "DateValue(\"2005-11-11\")+TIMEVALUE(\"11:11:11\")";





 AYUI       www.ayjs.net      AY         杨洋原创编写,请不要转载谢谢






今天先到这里










推荐您阅读更多有关于“excel,”的文章

猜你喜欢

额 本文暂时没人评论 来添加一个吧

发表评论

必填

选填

选填

必填

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

  查看权限

抖音号:wpfui,可以看到我的很多作品效果

AYUI8社区版Github地址:前往获取

作者:杨洋(AaronYang简称AY,安徽六安人)目前是个人,还没公司AY唯一QQ:875556003和AY交流

高中学历,2015年1月17日开始,兴趣学习研究WPF,目前工作繁忙,不接任何活

声明:AYUI7个人与商用免费,源码可购买。部分DEMO不免费.AY主要靠卖技术服务挣钱

不是从我处购买的ayui7源码,我不提供任何技术服务,如果你举报从哪里买的,我可以帮你转正为我的客户,并送demo

查看捐赠

AYUI7.X MVC教程 更新如下:

第一课 第二课 程序加密教程

兼容XP到win10,vs2015/2017/2019,最新AYUI:7.6.5.5

vs2015 企业版密钥HM6NR-QXX7C-DFW2Y-8B82K-WTYJV

vs2017 企业版密钥NJVYC-BMHX2-G77MM-4XJMR-6Q8QF

标签列表