C#导出图片到EXCEL|【NPOI】导出Excel带图片

使用C#语言开发导出EXCEL ,推荐使用 NPOI框架

1、添加程序包。选择管理NuGet程序包，浏览处搜索NPOI并安装。

2.导出方法

/// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;

            #region 右击文件 属性信息

            //{
            //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            //    dsi.Company = "";
            //    workbook.DocumentSummaryInformation = dsi;

            //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            //    si.Author = ""; //填加xls文件作者信息
            //    si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
            //    si.LastAuthor = "2"; //填加xls文件最后保存者信息
            //    si.Comments = "说明信息"; //填加xls文件作者信息
            //    si.Title = "NPOI测试"; //填加xls文件标题信息
            //    si.Subject = "NPOI测试Demo"; //填加文件主题信息
            //    si.CreateDateTime = DateTime.Now;
            //    workbook.SummaryInformation = si;
            //}

            #endregion

            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");

            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                arrColWidth[item.Ordinal] = arrColWidth[item.Ordinal] > 40 ? 40 : arrColWidth[item.Ordinal];
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                    arrColWidth[j] = arrColWidth[j] > 40 ? 40 : arrColWidth[j];
                }
            }
            int rowIndex = 0;

            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式

                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
                    }

                    #region 表头及样式

                    {
                        HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
                        headerRow.HeightInPoints = 40;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        headStyle.VerticalAlignment = VerticalAlignment.Center;
                        HSSFFont font = workbook.CreateFont() as HSSFFont;
                        font.FontHeightInPoints = 20;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;

                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                        //headerRow.Dispose();
                    }

                    #endregion


                    #region 列头及样式

                    {
                        HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;


                        HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        HSSFFont font = workbook.CreateFont() as HSSFFont;
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //边框
                        headStyle.BorderBottom = BorderStyle.Thin;
                        headStyle.BorderLeft = BorderStyle.Thin;
                        headStyle.BorderRight = BorderStyle.Thin;
                        headStyle.BorderTop = BorderStyle.Thin;


                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

                        }
                        //headerRow.Dispose();
                    }

                    #endregion

                    rowIndex = 2;
                }

                #endregion

                #region 填充内容

                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;

                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
                    //样式
                    //HSSFCellStyle cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                    边框
                    //cellStyle.BorderBottom = BorderStyle.THIN;
                    //cellStyle.BorderLeft = BorderStyle.THIN;
                    //cellStyle.BorderRight = BorderStyle.THIN;
                    //cellStyle.BorderTop = BorderStyle.THIN;
                    自动换行
                    //cellStyle.WrapText = true;
                    //cellStyle.VerticalAlignment = VerticalAlignment.Center;

                    newCell.CellStyle.VerticalAlignment = VerticalAlignment.Top;



                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                        case "System.String": //字符串类型
                            新增的四句话，设置CELL格式为文本格式   
                            //HSSFCellStyle cellStyle2 = workbook.CreateCellStyle() as HSSFCellStyle;
                            //HSSFDataFormat fmt = workbook.CreateDataFormat() as HSSFDataFormat;
                            //cellStyle2.DataFormat = fmt.GetFormat("@");
                            if (drValue.Contains(".png") || drValue.Contains(".PNG")|| drValue.Contains(".jpg")|| drValue.Contains(".JPG")|| drValue.Contains(".jpeg"))
                            {
                                AddCellPicture(sheet, workbook, "http://10.71.3.196/MIOS.Web/" + drValue, rowIndex, column.Ordinal);
                                break;
                            }
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime": //日期类型
                            DateTime dateV;
                            if (DateTime.TryParse(drValue, out dateV))
                            {
                                newCell.SetCellValue(dateV);
                                newCell.CellStyle = dateStyle; //格式化显示
                            }
                            break;
                        case "System.Boolean": //布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16": //整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal": //浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull": //空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue(drValue);
                            break;
                    }

                }

                #endregion

                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                //sheet.Dispose();
                //workbook.Dispose();

                return ms;
            }
        }

3.插入图片方法

 /// <summary>
        /// 向sheet插入图片
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="workbook">文档对象</param>
        /// <param name="imgPath">网络图片地址</param>
        /// <param name="row">哪行</param>
        /// <param name="col">哪列</param>
        private static void AddCellPicture(ISheet sheet, HSSFWorkbook workbook, string imgPath, int row, int col)
        {
            Uri uri = new Uri(imgPath); //imgPath :网络图片地址    
            WebRequest webRequest = WebRequest.Create(uri);

            using (WebResponse webResponse = webRequest.GetResponse())
            {
                //防止发生报错：GDI+中发生一般性错误的解决办法
                Bitmap bitmap = new Bitmap(webResponse.GetResponseStream()); //读取图片流 
                Bitmap OldImage = new Bitmap(bitmap);//将图片流复制到新的图片流中 
                bitmap.Dispose();   //将原来的图片流释放，将图片文件进行解锁。 

                using (MemoryStream ms = new MemoryStream())
                {
                    OldImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
                    byte[] bytes = ms.ToArray();
                    int pictureIdx = 0;
                    if (imgPath.Contains(".png")|| imgPath.Contains(".PNG"))
                    {
                        pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG);  //添加图片 
                    }
                    if (imgPath.Contains(".jpg")|| imgPath.Contains(".jpeg")|| imgPath.Contains(".JPG") )
                    {
                        pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);  //添加图片 
                    }
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);
                    //图片位置，图片左上角为（col, row） 
                    HSSFPicture pict = (HSSFPicture)sheet.CreateDrawingPatriarch().CreatePicture(anchor, pictureIdx);
                    //pict.Resize(); //用图片原始大小来显示   
                }
            }
        }

5.生成table,需要改造 个人的 此方法是根据配置(xml)文件自动 查询数据库生成

 public override void WriteToStream(Type type, object value, Stream writeStream, HttpContent content)
        {
            ODataPath path = Request.ODataProperties().Path;
            IEdmNavigationSource targetNavigationSource = path == null ? null : path.NavigationSource;

            var queryDatas = value.GetType().GetProperty("Query").GetValue(value) as IQueryable;
            var export_conf = HttpContext.Current.Request.QueryString["export_conf"];
            
            if (export_conf.IsEmpty()) export_conf = "Export_" + targetNavigationSource.Name;

            var export = XmlUtils.GetXml<ExportXml>(HttpContext.Current.Server.MapPath("~/ExportConfig/Excel/"
                + export_conf + ".xml"));
            if (export != null)
            {
                DataTable table = new DataTable();
                var dicSysDatas = new Dictionary<string, List<T_SYS_DATA_DETAILEntity>>();

                export.FileName = FormatReplaceSymbol(export.FileName);
                export.SheetName = FormatReplaceSymbol(export.SheetName);
                export.DisplayName = FormatReplaceSymbol(export.DisplayName);

                //添加列
                foreach (var field in export.Fields)
                {
                    table.Columns.Add(field.Name, Type.GetType(field.Type, true, true));
                }

                //添加行数据
                ExportApi exportApi = new ExportApi(export);
                var _formatFun = exportApi.GetType().GetMethod("OnFormat_" + export_conf);
                Type entityType = null;
                if (!string.IsNullOrWhiteSpace(export.TableName))
                {
                    var mdAssembly = typeof(Data.EFContextFactory).Assembly;
                    entityType = mdAssembly.GetType("MIOS.Data.EntityModel." + export.TableName);
                    if (entityType == null) throw new Exception("下载失败，未知的表实体名！！！");
                }
                // export.TableName

                foreach (var data in queryDatas)
                {
                    if (data == null) continue;

                    var row = table.NewRow();
                    var dItems = SelectExpandToData(data);

                    foreach (var field in export.Fields)
                    {
                        var pVal = GetValue(dItems, field.ID);

                        //字段为系统数据定义
                        if (!field.SysData.IsEmpty())
                        {
                            var zhi = (pVal ?? "").ToString();
                            if (!dicSysDatas.ContainsKey(field.SysData))
                            {
                                var sysdatas = Domain.DataCache.FindSysDataDetails(field.SysData, true)
                                    ?? new List<T_SYS_DATA_DETAILEntity>();
                                dicSysDatas.Add(field.SysData, sysdatas);
                            }
                            pVal = dicSysDatas[field.SysData].Where(p => p.DATA_VALUE == zhi)
                                .Select(p => p.DATA_DESC).FirstOrDefault();
                        }

                        if (field.ExistsFormatOptions)
                        {
                            var zhi = (pVal ?? "").ToString();
                            //存在格式化选项
                            var option = field.FormatOptions.Where(p => p.key != null && zhi == p.key).FirstOrDefault();
                            if (option != null) pVal = option.value;
                        }

                        if (!string.IsNullOrWhiteSpace(field.CustomFormat) && _formatFun != null)
                        {
                            // 存在自定义格式化
                            row[field.Name] = _formatFun.Invoke(exportApi, new object[] { field, dItems });
                        }
                        else
                        {
                            //添加行字段数据
                            row[field.Name] = (pVal == null ? DBNull.Value : pVal);
                        }
                    }

                    table.Rows.Add(row);
                }

                if (table.Columns.Count < 256 && table.Rows.Count < 65536)
                {
                    using (var exp = ExcelHelper.ExportDT(table, export.DisplayName))
                    {
                        var exporByt = exp.ToArray();
                        writeStream.Write(exporByt, 0, exporByt.Length);
                    }
                }
                else {
                    if (export.FileName.EndsWith(".xls")) export.FileName += "x";
                    using (var exp = ExcelHelper.ExportDT_X(table, export.DisplayName))
                    {
                        var exporByt = exp.ToArray();
                        writeStream.Write(exporByt, 0, exporByt.Length);
                    }
                }

                table.Dispose();
                HttpContext curContext = HttpContext.Current;

                // 设置编码和附件格式
                content.Headers.ContentType = new MediaTypeHeaderValue(MIME_TYPE);
                curContext.Response.ContentEncoding = Encoding.UTF8;
                curContext.Response.AppendHeader("Content-Disposition",
                    "attachment;filename=" + HttpUtility.UrlEncode(export.FileName, Encoding.UTF8));
            }
            
        }

6.配置文件截图