C# 导出Excel并插入二维码图片

包

1. EPPlus.dll
2. ThoughtWorks.QRCode.dll

代码

/// <summary>
/// 申请单打印
/// Add by Kyle on 2021-11-10
/// </summary>
/// <param name="ctx"></param>
/// <returns></returns>
public static string print(HttpContext ctx)
{
    string RID = ctx.Request["RID"].ToString();
    string Number = ctx.Request["Number"].ToString();
    string Operator_ID = ctx.Request["Operator_ID"].ToString();
    string LanguageName = ctx.Request["LanguageName"].ToString();

    SqlParameter[] paras = new SqlParameter[] {
        new SqlParameter("@actiontype", "print")
        ,new SqlParameter("@RID", RID)
        ,new SqlParameter("@Number", Number)
        ,new SqlParameter("@Operator_ID", Operator_ID)
        ,new SqlParameter("@Language", LanguageName)
    };

    DataSet ds = SQlHelper.ExecuteToDataSet("p_Query", CommandType.StoredProcedure, paras);

    //模板
    string tempPath = ctx.Request.MapPath("../../") + "Template/IssueApplyPrintTemplate_CN.xlsx";

    string savePath = ctx.Request.MapPath("../../Excels/") + DateTime.Now.ToString("yyyyMM");
    string fileName = string.Format("申请单{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"));
    string filePath = Path.Combine(savePath, fileName);

    using (ExcelPackage pck = new ExcelPackage(new FileInfo(tempPath))) //必须
    {
        ExcelWorksheet sheet = pck.Workbook.Worksheets[1];
        ExcelWorksheet sheet2 = pck.Workbook.Worksheets[2];

        sheet.Cells[7, 3].Value = sheet.Cells[7, 3].GetValue<String>() + ds.Tables[0].Rows[0]["Apply_Dept"].ToString();
        sheet.Cells[7, 6].Value = sheet.Cells[7, 6].GetValue<String>() + ds.Tables[0].Rows[0]["Apply_No"].ToString();

        sheet2.Cells[7, 6].Value = sheet2.Cells[7, 6].GetValue<String>() + ds.Tables[0].Rows[0]["Apply_Dept"].ToString();
        sheet2.Cells[7, 9].Value = sheet2.Cells[7, 9].GetValue<String>() + ds.Tables[0].Rows[0]["Apply_Date"].ToString();
        sheet2.Cells[7, 11].Value = sheet2.Cells[7, 11].GetValue<String>() + ds.Tables[0].Rows[0]["Apply_No"].ToString();

        for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
        {
            DataRow dr = ds.Tables[1].Rows[i];
            sheet.Cells[9 + i, 1].Value = dr["No"].ToString();
            sheet.Cells[9 + i, 2].Value = dr["Customer"].ToString();
            sheet.Cells[9 + i, 3].Value = dr["Qty"];
            sheet.Cells[9 + i, 4].Value = "件";

            sheet2.Cells[9 + i, 1].Value = dr["No"].ToString();
            sheet2.Cells[9 + i, 2].Value = dr["Customer"].ToString();
            sheet2.Cells[9 + i, 3].Value = dr["Color"].ToString();
        }

        //合计
        sheet2.Cells[25, 6].Value = ds.Tables[0].Rows[0]["Total_Pcs"].ToString();

        //生成二维码
        //创建二维码生成类
        QRCodeEncoder qrCodeEncoder = new QRCodeEncoder();
        string encoding = "Byte";
        string errorCorrect = "H";
        int version = 2;
        int scale = 3;

        //设置编码模式
        if (encoding == "Byte")
        {
            qrCodeEncoder.QRCodeEncodeMode = QRCodeEncoder.ENCODE_MODE.BYTE;
        }
        else if (encoding == "AlphaNumeric")
        {
            qrCodeEncoder.QRCodeEncodeMode = QRCodeEncoder.ENCODE_MODE.ALPHA_NUMERIC;
        }
        else if (encoding == "Numeric")
        {
            qrCodeEncoder.QRCodeEncodeMode = QRCodeEncoder.ENCODE_MODE.NUMERIC;
        }
        //设置编码测量度
        try
        {
            //int scale = Convert.ToInt16(Request["Txtsize"].ToString());
            qrCodeEncoder.QRCodeScale = scale;
        }
        catch (Exception ex)
        {
            return null;
        }
        //设置编码版本
        try
        {
            //int version = Convert.ToInt16(Request["DDLVer"].ToString());
            qrCodeEncoder.QRCodeVersion = version;
        }
        catch (Exception ex)
        {
        }
        //设置编码错误纠正
        if (errorCorrect == "L")
            qrCodeEncoder.QRCodeErrorCorrect = QRCodeEncoder.ERROR_CORRECTION.L;
        else if (errorCorrect == "M")
            qrCodeEncoder.QRCodeErrorCorrect = QRCodeEncoder.ERROR_CORRECTION.M;
        else if (errorCorrect == "Q")
            qrCodeEncoder.QRCodeErrorCorrect = QRCodeEncoder.ERROR_CORRECTION.Q;
        else if (errorCorrect == "H")
            qrCodeEncoder.QRCodeErrorCorrect = QRCodeEncoder.ERROR_CORRECTION.H;
         qrCodeEncoder.QRCodeErrorCorrect = QRCodeEncoder.ERROR_CORRECTION.M;

        //生成二维码图片
        System.Drawing.Bitmap image = qrCodeEncoder.Encode(ds.Tables[0].Rows[0]["Apply_No"].ToString(), Encoding.UTF8);

        ExcelPicture picture = sheet.Drawings.AddPicture("qrCode", image);
        ExcelPicture picture2 = sheet2.Drawings.AddPicture("qrCode", image);
        //picture.SetSize(100, 100);//设置图片的大小
        picture.SetPosition(42, 848);//设置图片的位置
        picture2.SetPosition(30, 1049);//设置图片的位置

        //保存文件
        if (!Directory.Exists(savePath))
        {
            Directory.CreateDirectory(savePath);
        }

        pck.SaveAs(new FileInfo(filePath));
    }

    HttpContext context = HttpContext.Current;
    FileInfo fileInfo = new FileInfo(filePath);
    context.Response.Clear();
    context.Response.ClearContent();
    context.Response.ClearHeaders();
    context.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
    context.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
    context.Response.AddHeader("Content-Transfer-Encoding", "binary");
    context.Response.ContentType = "application/octet-stream";
    context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
    context.Response.WriteFile(fileInfo.FullName);
    context.Response.Flush();
    // File.Delete(filePath);//删除已下载文件

    context.Response.End();

    return JsonConvert.SerializeObject(new { fileName = fileName, msg = true });
}

window.location.href = IndexPager.apiUrl + 'action=print&Rid=' + Rid + '&Number=' + Number+ "&Operator_ID=" + LoginUserID + "&LanguageName=" + LanguageName;