NPOI操作Excel常用函数

最新推荐文章于 2024-02-28 11:49:45 发布
我是一只小小鱼~
最新推荐文章于 2024-02-28 11:49:45 发布
阅读量688
点赞数
分类专栏： C#
本文链接：https://blog.csdn.net/qq_42335551/article/details/98970487
版权
C# 专栏收录该内容
28 篇文章 0 订阅
订阅专栏

        /// <summary>
        /// 插入的图片
        /// </summary>
        static  string imgurl = @"C:\Users\chenpeng5\source\repos\Export\Export\Koala.jpg";
        /// <summary>
        /// 模板
        /// </summary>
        static  string url = @"C:\Users\chenpeng5\source\repos\Export\Export\test.xls";
        /// <summary>
        /// 保存路径
        /// </summary>
        static string SaveFile = @"C:\Users\chenpeng5\Desktop\" + DateTime.Now.ToLongDateString() + ".xls";
        static void Main(string[] args)
        {
          
            MemoryStream stream = new MemoryStream();
            stream = SetExcel();
            StreamWriter writer = new StreamWriter(stream);
           
            FileStream file = new FileStream(SaveFile, FileMode.Create);
            stream.WriteTo(file);

            file.Dispose();
            writer.Dispose();
            stream.Dispose();
        }

        public static MemoryStream SetExcel()
        {   ///读取文件
            var file = new FileStream(url, FileMode.Open, FileAccess.Read);
           ///获取excel对象
            var hssfworkbook = new HSSFWorkbook(file);
            ///获取工作簿
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            file.Close();



          //设置单元格颜色

 ICellStyle cellStyle= hssfWorkbook.CreateCellStyle();

cellStyle.FillPattern = FillPattern.SolidForeground; 老版本可能这样写FillPatternType.SOLID_FOREGROUND;

cellStyle.FillForegroundColor = hssFColor.Indexed;

cell.CellStyle = cellStyle;//设置

         //设置单元格的宽
            sheet.SetColumnWidth(0, 100 * 50);
            sheet.SetColumnWidth(2, 100 * 50);
            sheet.DefaultRowHeight = 30;//默认值
            //设置某行某单元格的值
            sheet.GetRow(3).GetCell(0).SetCellValue("你好");
            ///插入图片
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
            SetPicure(patriarch,sheet,hssfworkbook, imgurl,18,1);

            //创建单位格
            IRow row = GetRow(sheet, 19);
           
            ICell cell= GetCell(row, 0);
            cell.SetCellValue("批示");
            
            SetCellRangeAddress(sheet, 19, 20, 0, 13);

            cell.CellStyle.SetFont(GetFontStyle(hssfworkbook, "宋体", HSSFColor.RED.index, 11,false));

//同一单元格设置不同的颜色
string FileId = "第2019-2046号 ";
  HSSFRichTextString ts = new HSSFRichTextString(FileId);
            ts.ApplyFont(0, FileId.IndexOf('第')+1, GetFontStyle(hssfworkbook, "宋体", RED.index, 11, false));
            ts.ApplyFont(FileId.IndexOf('号'), FileId.IndexOf('号') + 1, GetFontStyle(hssfworkbook, "宋体", RED.index, 11, false));
            sheet.GetRow(1).GetCell(1).SetCellValue(ts);

//插入三行
   sheet.ShiftRows(11, sheet.LastRowNum,3, true, false);

            var stream = new MemoryStream();
            hssfworkbook.Write(stream);
            return stream;
        }

        /// <summary>
        /// 创建行
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        public static IRow GetRow(ISheet sheet, int rowIndex)
        {
            IRow row = sheet.GetRow(rowIndex);
            if (row == null)
            {
                row = sheet.CreateRow(rowIndex);
            }
            return row;
        }

        /// <summary>
        /// 创建列
        /// </summary>
        /// <param name="row"></param>
        /// <param name="cellIndex"></param>
        /// <returns></returns>
        public static ICell GetCell(IRow row, int cellIndex)
        {
            ICell cell = row.GetCell(cellIndex);
            if (cell == null)
            {
                cell = row.CreateCell(cellIndex);
            }
            return cell;
        }
        /// <summary>
        /// 插入图片
        /// </summary>
        /// <param name="patriarch"></param>
        /// <param name="sheet"></param>
        /// <param name="workbook"></param>
        /// <param name="imgurl"></param>
        /// <param name="row"></param>
        /// <param name="col"></param>
        public static void SetPicure(HSSFPatriarch patriarch, ISheet sheet, HSSFWorkbook workbook,string imgurl, int row, int col) {
            try
            {
                  
                byte[] bytes = GetPictureData(imgurl);//System.IO.File.ReadAllBytes(fileurl);
             
                    int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
                    //patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 80, 39, col, row, col + 1, row + 1);
                    //##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为80，高为39
                    HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                    //pict.Resize();//这句话一定不要，这是用图片原始大小来显示
                
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// 将图片转化成byte数组
        /// </summary>
        /// <param name="imagePath"></param>
        /// <returns></returns>
        private static byte[] GetPictureData(string imagePath)
        {
            string WebAddress = imagePath;
            WebRequest webRequest = WebRequest.Create(WebAddress);
            WebResponse webResponse = webRequest.GetResponse();
            Stream stream = webResponse.GetResponseStream();
            MemoryStream mem = new MemoryStream();
            BufferedStream bfs = new BufferedStream(stream);
            int len = 0;
            byte[] buffer = new byte[4096];

            do
            {
                len = bfs.Read(buffer, 0, buffer.Length);
                if (len > 0)
                    mem.Write(buffer, 0, len);
            }
            while (len > 0);
            bfs.Close();
            byte[] picbytes = mem.ToArray();
            mem.Close();
            return picbytes;
        }


        /// <summary>
        /// 合并单元格
        /// </summary>
        private static void MergeRowCell(HSSFWorkbook hssfworkbook, ISheet sheet) {



            IRow row = GetRow(sheet, 24);

            row.HeightInPoints = 35;//行高
            ICell cell = GetCell(row,0);//创建第一列
            cell.SetCellValue("Word");///设置单元格的值


            居中样式
            //ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
            //cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            //cellStyle.Alignment = HorizontalAlignment.CENTER;



        }

        /// <summary>
        /// 获取字体样式
        /// </summary>
        /// <param name="hssfworkbook">Excel操作类</param>
        /// <param name="fontname">字体名</param>
        /// <param name="fontcolor">字体颜色</param>
        /// <param name="fontsize">字体大小</param>
        /// <param name="fontsize">是否为斜体</param>
        /// <returns></returns>
        public static IFont GetFontStyle(HSSFWorkbook hssfworkbook, string fontfamily, short fontcolor, int fontsize,bool IsItalic)
        {
            IFont font1 = hssfworkbook.CreateFont();
            if (string.IsNullOrEmpty(fontfamily))
            {
                font1.FontName = fontfamily;
            }
            font1.Boldweight = 700;
            font1.Color = fontcolor;
            font1.IsItalic = IsItalic;
            font1.FontHeightInPoints = (short)fontsize;
            return font1;
        }



        




        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            sheet.AddMergedRegion(cellRangeAddress);
        }

        public static ICellStyle Align(HSSFWorkbook hssfworkbook , HorizontalAlignment horizontalAlignment , string fontfamily, short fontcolor, int fontsize, bool IsItalic,bool isleftborder)
        {

            ICellStyle style3 = hssfworkbook.CreateCellStyle();
            style3.Alignment = horizontalAlignment;//居中
            style3.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 
            style3.WrapText = true;//自动换行   
            style3.SetFont(GetFontStyle(hssfworkbook, fontfamily, fontcolor, fontsize, IsItalic));      
            /// 添加下边框和右边框 并设置为红色
            style3.BorderBottom = CellBorderType.THIN;
            style3.BottomBorderColor= HSSFColor.RED.index;
            if (isleftborder)
            {
                style3.BorderLeft = CellBorderType.THIN;
                style3.LeftBorderColor = HSSFColor.RED.index;
            }          
            return style3;
        }
参考：https://www.cnblogs.com/yxhblog/p/7084025.html
https://www.jb51.net/article/36002.htm
https://www.cnblogs.com/ike_li/p/6349114.html