导出带图片的Excel

导出excel，并添加图片

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.IO;using System.Drawing.Imaging;using System.Drawing;using NPOI;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using System.Text;using NPOI.SS.Util;using System.Collections;using System.Text.RegularExpressions;using NPOI.HPSF;using Gma.QrCodeNet.Encoding;using Gma.QrCodeNet.Encoding.Windows.Render;public partial class DemoExport : System.Web.UI.Page{ private static readonly string imageBasePath = "D:\\PathConfig\\"; private static readonly string excelBasePath = "D:\\PathConfig\\"; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Bind(); } } protected void Bind() { //string jsonobjstr = HttpHelper.QueryString("json", ""); List<PrintCard> cards = new List<PrintCard>() { new PrintCard{ CardCode="0230010900010336",Password= "123456"}, new PrintCard{ CardCode="0230010900010339",Password="123456"}, new PrintCard{ CardCode="0230010900010340",Password= "123456"}, new PrintCard{ CardCode="0230000900010340",Password= "123456"}, new PrintCard{ CardCode="0230020900010349",Password="123456"} }; try { // cards = JsonHelper.JsonDeserialize<List<Card>>(jsonobjstr); Session["ListPrintCard"] = cards; gvCardList.DataSource = cards; gvCardList.DataBind(); } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('" ex.Message.ToString() "');</script>"); return; } } public class PrintCard { public string CardCode { get; set; } public string Password { get; set; } } /// <summary> /// 生成二维码 /// </summary> /// <param name="QrCodeStr">二维码字符串</param> /// <returns></returns> public string GetQrCode(string QrCodeStr) { string FileName = imageBasePath Guid.NewGuid().ToString() ".png"; try { QrEncoder qrEncoder = new QrEncoder(ErrorCorrectionLevel.L); QrCode qrCode = new QrCode(); qrEncoder.TryEncode(QrCodeStr, out qrCode); GraphicsRenderer renderer = new GraphicsRenderer(new FixedModuleSize(5, QuietZoneModules.Two), Brushes.Black, Brushes.White); using (FileStream stream = new FileStream(FileName, FileMode.Create)) { renderer.WriteToStream(qrCode.Matrix, ImageFormat.Png, stream); } } catch (Exception ex) { FileName = ""; throw ex; } return FileName; } protected void btnGenerateCode_Click(object sender, EventArgs e) { //GenerateQrCodeNet(); AddPicture(); } /// <summary> /// 二维码导出到Excel /// </summary> protected void AddPicture() { try { if (!Directory.Exists(imageBasePath)) { Directory.CreateDirectory(imageBasePath); } if (!Directory.Exists(excelBasePath)) { Directory.CreateDirectory(excelBasePath); } //创建工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); //create sheet HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1"); string FileName = excelBasePath DateTime.Now.ToString("yyyyMMddhh24mss") ".xls"; #region 右击文件 属性信息 //{ // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "http://....../"; // workbook.DocumentSummaryInformation = dsi; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // if (HttpContext.Current.Session["realname"] != null) // { // si.Author = HttpContext.Current.Session["realname"].ToString(); // } // else // { // if (HttpContext.Current.Session["username"] != null) // { // si.Author = HttpContext.Current.Session["username"].ToString(); // } // } //填加xls文件作者信息 // si.ApplicationName = "NPOI"; //填加xls文件创建程序信息 // si.LastAuthor = "OA系统"; //填加xls文件最后保存者信息 // si.Comments = "OA系统自动创建文件"; //填加xls文件作者信息 // si.Title = "ddd"; //填加xls文件标题信息 // si.Subject = "ddd"; //填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.SummaryInformation = si; //} #endregion string strQrCodePath = ""; //填充列标题以及样式 int rowsNum = 0; //行号 HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowsNum); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = (HorizontalAlignment)HorizontalAlignment.Center; headerRow.HeightInPoints = 30; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 13; font.Boldweight = 700; headStyle.SetFont(font); //headerRow.CreateCell(0, CellType.String).SetCellValue("卡二维码"); //CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4); //sheet.AddMergedRegion(cellRangeAddress); //headerRow.CreateCell(5, CellType.String).SetCellValue("卡号"); //CellRangeAddress cellRangeAddress1 = new CellRangeAddress(0, 0, 5, 8); //sheet.AddMergedRegion(cellRangeAddress1); //headerRow.CreateCell(9, CellType.String).SetCellValue("密码"); //CellRangeAddress cellRangeAddress2 = new CellRangeAddress(0, 0, 9, 12); //sheet.AddMergedRegion(cellRangeAddress2); for (int i = 0; i < 12; i ) { if (i == 0) { headerRow.CreateCell(i, CellType.String).SetCellValue("卡二维码"); } else if (i == 5) { headerRow.CreateCell(i, CellType.String).SetCellValue("卡号"); } else if (i == 9) { headerRow.CreateCell(i, CellType.String).SetCellValue("密码"); } else { headerRow.CreateCell(i, CellType.String).SetCellValue(""); } } //合并列头单元格 CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4); sheet.AddMergedRegion(cellRangeAddress); CellRangeAddress cellRangeAddress1 = new CellRangeAddress(0, 0, 5, 8); sheet.AddMergedRegion(cellRangeAddress1); CellRangeAddress cellRangeAddress2 = new CellRangeAddress(0, 0, 9, 12); sheet.AddMergedRegion(cellRangeAddress2); //设置列的宽度 for (int columnindex = 0; columnindex < 3; columnindex ) { headerRow.GetCell(columnindex).CellStyle = headStyle; sheet.SetColumnWidth(columnindex, 5000); } //填充数据行 HSSFRow row = null; rowsNum = 1; //行号，从第2行开始 List<PrintCard> list = (List<PrintCard>)Session["ListPrintCard"]; foreach (PrintCard p in list) { //写入字段值 row = (HSSFRow)sheet.CreateRow(rowsNum); HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); cellStyle.Alignment = (HorizontalAlignment)HorizontalAlignment.Center; row.HeightInPoints = 120; HSSFFont cellfont = (HSSFFont)workbook.CreateFont(); cellfont.FontHeightInPoints = 10; cellStyle.SetFont(cellfont); row.CreateCell(0, CellType.String).SetCellValue(""); row.CreateCell(1, CellType.String).SetCellValue(p.CardCode); row.CreateCell(2, CellType.Blank).SetCellValue(p.Password); //合并单元格 //CellRangeAddress rowCellRangeAddress = new CellRangeAddress(rowsNum, rowsNum, 2, 4); //sheet.AddMergedRegion(rowCellRangeAddress); strQrCodePath = GetQrCode(p.CardCode); byte[] bytes = System.IO.File.ReadAllBytes(strQrCodePath); int pictureIdx = workbook.AddPicture(bytes, PictureType.PNG); // Create the drawing patriarch. This is the top level container for all shapes. HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); //add a picture HSSFClientAnchor anchor = new HSSFClientAnchor(0, 10, 1023, 0, 0, rowsNum, 0, rowsNum); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); //设置行的高度 for (int rowindex = 0; rowindex < 3; rowindex ) { row.GetCell(rowindex).CellStyle = cellStyle; } rowsNum ; //删除图片文件 if (File.Exists(strQrCodePath)) { File.Delete(strQrCodePath); } } //供浏览器下载Excel if (HttpContext.Current.Request.Browser.Browser == "IE") FileName = HttpUtility.UrlEncode(FileName); using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" HttpUtility.UrlEncode(FileName, Encoding.UTF8)); curContext.Response.BinaryWrite(ms.GetBuffer()); ms.Close(); ms.Dispose(); curContext.Response.End(); } } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('" ex.Message.ToString() "');</script>"); } } protected void gvCardList_RowCommand(object sender, GridViewCommandEventArgs e) { string cardCode = e.CommandArgument.ToString(); }}