C#NPOI导出EXECL带上网络URL图片
1.NuGet安装NPOI
2.帮助类NPOIExportHelper
using CJ.Model; using NPOI.HSSF.UserModel; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Net; using System.Reflection; namespace CJ.Common { public class NPOIExportHelper { private static List<string> imgFieldStr = new List<string>() { "P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8" }; public static NPOI.HSSF.UserModel.HSSFWorkbook DoExport<T>(IEnumerable<T> models, string notile = "", IEnumerable<FieldList> fieldMap = null) where T : class { //创建工作簿 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //创建表 NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet"); //自适应列宽 // sheet.AutoSizeColumn(1, true); //标题行合并单元格 //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1)); NPOI.SS.UserModel.IRow firstrow = sheet.CreateRow(0); NPOI.SS.UserModel.ICell firstcell = firstrow.CreateCell(0); //表名样式 NPOI.SS.UserModel.ICellStyle styleHeader = book.CreateCellStyle(); NPOI.SS.UserModel.IFont fontHeader = book.CreateFont(); styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleHeader.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; fontHeader.FontHeightInPoints = 16; styleHeader.SetFont(fontHeader); firstcell.CellStyle = styleHeader; firstcell.SetCellValue(notile); try { //列名样式 NPOI.SS.UserModel.ICellStyle styleColName = book.CreateCellStyle(); NPOI.SS.UserModel.IFont fontColName = book.CreateFont(); styleColName.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleColName.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; fontColName.FontHeightInPoints = 12; styleColName.SetFont(fontColName); //数据的样式、字体大小 NPOI.SS.UserModel.ICellStyle styleBody = book.CreateCellStyle(); NPOI.SS.UserModel.IFont fontBody = book.CreateFont(); styleBody.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleBody.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; fontBody.FontHeightInPoints = 12; styleBody.SetFont(fontBody); NPOI.SS.UserModel.IRow colNameRow = sheet.CreateRow(0); #region 表头 Type type = typeof(T); PropertyInfo[] properties = type.GetProperties(); int iHead = 0; if (fieldMap == null) { foreach (PropertyInfo p in properties) { NPOI.SS.UserModel.ICell colNameCell = colNameRow.CreateCell(iHead); colNameCell.SetCellValue(p.Name.Replace("__", " ")); colNameCell.CellStyle = styleColName; iHead++; } } else { foreach (var c in fieldMap) { NPOI.SS.UserModel.ICell colNameCell = colNameRow.CreateCell(iHead); colNameCell.SetCellValue(c.FieldName); colNameCell.CellStyle = styleColName; iHead++; } } #endregion int i = 1; foreach (var m in models) { NPOI.SS.UserModel.IRow row = sheet.CreateRow(i); int jContent = 0; if (fieldMap != null && fieldMap.Count() > 0) { foreach (var c in fieldMap) { bool isImg = false; if (imgFieldStr.Contains(c.FieldValue) && c.FieldValue.Length == 2) { row.Height = 80 * 20; sheet.SetColumnWidth(jContent, 6400); isImg = true; } NPOI.SS.UserModel.ICell cell = row.CreateCell(jContent); PropertyInfo p = properties.SingleOrDefault(w => w.Name == c.FieldValue); if (p == null) throw new Exception("数据模型没有相应的属性：" + c.FieldValue); //是图片 if (isImg) {if (p.GetValue(m, null) != null && !string.IsNullOrEmpty(p.GetValue(m, null).ToString())) {
byte[] byteS = UrlToByte(p.GetValue(m, null).ToString()); int pictureIdx = book.AddPicture(byteS, NPOI.SS.UserModel.PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 100, 50, jContent, i, jContent + 1, i + 1); //##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50 HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); // pict.Resize();这句话一定不要，这是用图片原始大小来显示 } } else if (p.PropertyType.FullName.Contains("System.DateTime")) cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", p.GetValue(m, null))); else cell.SetCellValue(p.GetValue(m, null).ToString()); jContent++; } } else { foreach (PropertyInfo p in properties) { NPOI.SS.UserModel.ICell cell = row.CreateCell(jContent); if (p.PropertyType.FullName.Contains("System.DateTime")) cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", p.GetValue(m, null))); else cell.SetCellValue(p.GetValue(m, null).ToString()); jContent++; } } i++; } //自适应列宽 for (int x = 0; x < models.Count(); x++) { sheet.AutoSizeColumn(x, true); } return book; } catch (Exception ex) { throw ex; } } //根据图片url获取图片bytes public static byte[] UrlToByte(String filePath) { //第一步：读取图片到byte数组 HttpWebRequest request = (HttpWebRequest)WebRequest.Create(filePath); byte[] bytes; using (Stream stream = request.GetResponse().GetResponseStream()) { using (MemoryStream mstream = new MemoryStream()) { int count = 0; byte[] buffer = new byte[1024]; int readNum = 0; while ((readNum = stream.Read(buffer, 0, 1024)) > 0) { count = count + readNum; mstream.Write(buffer, 0, readNum); } mstream.Position = 0; using (BinaryReader br = new BinaryReader(mstream)) { bytes = br.ReadBytes(count); } } } return bytes; } } }
3.使用
using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { var t = NPOIExportHelper.DoExport<Class>(data, "", Init_ShipmentReportPackingExport()); t.Write(ms); //buffer = ms.GetBuffer(); ms.Close(); ms.Dispose(); HttpContext.Response.Headers.Add("Content-Disposition", string.Format("attachment; filename=绩效统计.xls")); HttpContext.Response.BodyWriter.WriteAsync(ms.ToArray()); }
4.结果：
参阅：
https://www.cnblogs.com/kgdjgd/p/5857563.html
https://www.csdn.net/tags/MtTaMg4sOTk5ODQyLWJsb2cO0O0O.html
http://t.zoukankan.com/guangfangxiaokeai-p-12518079.html
