C#中写Excel并插入图片下载

Alei297

已于 2023-06-30 11:35:33 修改

阅读量718

点赞数

文章标签： c# excel

于 2023-06-29 13:12:32 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_44371385/article/details/131451853

版权

文章介绍了如何在C#环境中利用NPOI库创建和绘制Excel，包括所需DLL引用、代码实现数据导出到Excel的过程，以及图片插入到Excel的具体方法。提供了完整的代码示例，适用于大数据量的Excel绘制场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

提示：文章内容有借鉴有原创，借鉴之处如有侵权请联系删除。

文章目录

前言
废话不多说直接开干

一、绘制Excel所需要的DLL
二、代码
三、总结

前言

废话不多说直接开干

一、绘制Excel所需要的DLL

1.NPOI.dll
2.NPOI.OOXML.dll
3.NPOI.OpenXml4Net.dll
4.NPOI.OpenXmlFormats.dll
支持大数据量绘制

二、代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;

public static void ExportDataToExcel(DataTable table,string biln, string Responsefilename)
{
            try
            {
                if (Directory.Exists(System.Web.HttpContext.Current.Server.MapPath("相对路径")) == false)
                {
                    Directory.CreateDirectory(System.Web.HttpContext.Current.Server.MapPath("相对路径"));
                }
                
                string filePath = System.Web.HttpContext.Current.Server.MapPath("相对路径");//路径
                string fileName = Responsefilename;
                string[] headerNames = new string[] 
                { "绘制所需要的表头" };
                //
                Dictionary<string, List<string>> values = new Dictionary<string, List<string>>();
                int i = 1;//有序号需求则使用
                foreach (DataRow row in table.Rows)
                {
                    valueList.Add("对应表头的值");
                    values.Add(key, valueList);
                    i++;
                }
                DownloadToExcelInsertPicture(headerNames, values, "图片URL", fileName, filePath);
                FileInfo fileInfo = new FileInfo(filePath + fileName + ".xls");//hFileName  
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.ClearHeaders();
                HttpContext.Current.Response.Buffer = false;
                HttpContext.Current.Response.ContentType = "application/octet-stream";
                HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileInfo.Name, System.Text.Encoding.UTF8));
                HttpContext.Current.Response.AppendHeader("Content-Length", fileInfo.Length.ToString());
                HttpContext.Current.Response.WriteFile(fileInfo.FullName);
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                if (File.Exists(System.Web.HttpContext.Current.Server.MapPath("相对路径" + Responsefilename + ".xls")))
                {
                    File.Delete(System.Web.HttpContext.Current.Server.MapPath("相对路径" + Responsefilename + ".xls"));
                }
            }
            finally
            {
                if (File.Exists(System.Web.HttpContext.Current.Server.MapPath("相对路径/" + Responsefilename + ".xls")))
                {
                    File.Delete(System.Web.HttpContext.Current.Server.MapPath("相对路径/" + Responsefilename + ".xls"));
                }
            }
 }

public static string DownloadToExcelInsertPicture(string[] headerNames, Dictionary<string, List<string>> values, string 图片路径,string fileName, string savePath)
{
 			HSSFWorkbook transWorkbook = new HSSFWorkbook();
    		//自定义颜色
    		HSSFPalette palette = transWorkbook.GetCustomPalette();
            // 先加入到色板中 RGB颜色值，第一个值：8~64之间，后面三个值为RGB色值
            palette.SetColorAtIndex((short)8, (byte)0, (byte)80, (byte)110);
            System.Drawing.Color color = System.Drawing.Color.FromArgb(0, 80, 110);
            var myColor = palette.FindColor(color.R, color.G, color.B);
            //表头样式
            ICellStyle transStyle = transWorkbook.CreateCellStyle();
            transStyle.Alignment = HorizontalAlignment.CenterSelection;
            transStyle.WrapText = true;
            IFont headerfont = transWorkbook.CreateFont();
            headerfont.FontName = "微软雅黑";//字体
            headerfont.FontHeightInPoints = 12;//字体大小
            headerfont.Boldweight = 400;//是否加粗
            headerfont.Color = 9; //表头白色
            transStyle.SetFont(headerfont);
            transStyle.BorderBottom = BorderStyle.Thin;
            transStyle.BorderRight = BorderStyle.Thin;
            transStyle.BorderTop = BorderStyle.Thin;
            transStyle.BorderLeft = BorderStyle.Thin;
            transStyle.FillForegroundColor = myColor.GetIndex();
            transStyle.FillPattern = FillPattern.SolidForeground;
            ISheet transSheet = transWorkbook.CreateSheet("sheet1");
            IRow transRowHead = null;
            if(string.IsNullOrEmpty("图片路径")
            {
            	transRowHead = transSheet.CreateRow(0);
			}else{
			   //如果图片是插入在excel文件顶部，预留空白行
			    transRowHead = transSheet.CreateRow(5);
			}
            //存储最大列宽
            Dictionary<int, int> maxWidth = new Dictionary<int, int>();
            for (int i = 0; i < headerNames.Length; i++)
            {
                ICell ccell = transRowHead.CreateCell(i);
                ccell.SetCellValue(headerNames[i]);
                //表格宽度自适应
                getMaxWidth(maxWidth, ccell, i);
                ccell.CellStyle = transStyle;
            }
            //表体样式
            ICellStyle bodyStyle = transWorkbook.CreateCellStyle();
            bodyStyle.Alignment = HorizontalAlignment.CenterSelection;
            IFont bodyFont = transWorkbook.CreateFont();
            bodyFont.FontName = "微软雅黑";//字体
            bodyFont.FontHeightInPoints = 10;
            //bodyFont..Boldweight = 400;;//是否加粗
            bodyStyle.SetFont(bodyFont);
            bodyStyle.BorderBottom = BorderStyle.Thin;
            bodyStyle.BorderRight = BorderStyle.Thin;
            bodyStyle.BorderTop = BorderStyle.Thin;
            bodyStyle.BorderLeft = BorderStyle.Thin;
            var trnsCount = 1;
            List<string> vkeys = values.Keys.ToList();
            foreach (var item in vkeys)
            {
            	 IRow trnsRow = null;
	             if(string.IsNullOrEmpty("图片路径")
	            {
	            	trnsRow =  transSheet.CreateRow(trnsCount);
				}else{
				   //如果图片是插入在excel文件顶部，预留空白行
				    trnsRow =  transSheet.CreateRow(trnsCount+5);
				}
                List<string> valueList = values[item];
                for (int k = 0; k < valueList.Count; k++)
                {
                    ICell createCell = trnsRow.CreateCell(k);
                    createCell.SetCellValue(valueList[k]);
                    //表格宽度自适应
                    getMaxWidth(maxWidth, createCell, k);
                    createCell.CellStyle = bodyStyle;
                }
                trnsCount++;
            }
            if (null != values && values.Count > 0)
            {
                for (int i = 0; i < headerNames.Length; i++)
                {
                    transSheet.SetColumnWidth(i, maxWidth[i]);
                }
            }
            Coordinate nate = new Coordinate(255, 125, 1023, 150, 0, 1, 2, 2);
            InsertImageToExcel(transWorkbook, transSheet, nate, "图片路径");
            if (!Directory.Exists(savePath))
            {
                Directory.CreateDirectory(savePath);
            }
            else
            {//清空文件夹的内容 
                Directory.GetFiles(savePath).ToList().ForEach(item =>
                {
                    File.Delete(item);
                });
            }

            string filePath = savePath + DateTime.Now.Millisecond + ".xls";
            if (!string.IsNullOrEmpty(fileName))
            {
                filePath = savePath + fileName + ".xls";
            }

            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            using (FileStream maurl = File.OpenWrite(filePath))
            {
                transWorkbook.Write(maurl);
                //transWorkbook.Dispose();
                maurl.Close();
            }
            return filePath;           
}

private static void getMaxWidth(Dictionary<int, int> maxWidth, ICell cell, int key)
{
      //表格宽度自适应
      int length = System.Text.Encoding.Default.GetBytes(cell.StringCellValue).Count() * 256 + 800;
      //这里把宽度最大限制到15000
      if (length > 15000)
      {
          length = 15000;
      }
      if (!maxWidth.ContainsKey(key))
      {
           maxWidth.Add(key, Math.Max(length, 0));
      }
      else
      {
           maxWidth[key] = Math.Max(length, maxWidth[key]);
      }     
}

private static void InsertImageToExcel(HSSFWorkbook book, ISheet sheet1 string imgPath)
{
      byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
      int pictureIdx = book.AddPicture(bytes, PictureType.JPEG);
      HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
      HSSFClientAnchor anchor = new HSSFClientAnchor(255, 125, 1023, 150, 0, 1, 2, 2);
      HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
      pict.Resize();//自动调节图片大小      
}

以上代码可以直接使用，参数合理性可以根据具体需求做修改。
InsertImageToExcel方法中需要注意的是
HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);这行代码确定图片在excel中的位子
HSSFClientAnchor anchor = new HSSFClientAnchor(255, 125, 1023, 150, 0, 1, 2, 2);
dx的值在0 ~ 1023，dy的值在0 ~ 255
rol表示列，row表示行即excel中单元格坐标
1表示开始位子
2表示结束位子