protected void btnSearch_Click(object sender, EventArgs e)
    {

        string reportFile = GenerateJobSheet(BizUnit.GetBarCode());
        //ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "", String.Format("window.navigate('DownloadFile.aspx?path={0}');", Server.UrlEncode(reportFile)), true);


    }
    public string GenerateJobSheet(DataTable tb)
    {
        string reportFile = ReportHelper.CreateFileFromTemplate("~/Reports/JobSheetTemplate.xls", "~/Reports/JobSheet_" + DateTime.Now.Year.ToString() + ".xls");

        HSSFWorkbook workbook;
        using (FileStream fs = new FileStream(reportFile, FileMode.Open, FileAccess.Read))
        {
            workbook = new HSSFWorkbook(fs);
        }

        HSSFSheet worksheet = workbook.GetSheetAt(0);
        //这是循环插入的关键方法
        FillDataXBraCode(worksheet,tb,1,workbook);
        using (FileStream fs = new FileStream(reportFile, FileMode.Create))
        {
            workbook.Write(fs);
        }

        return reportFile;
    }


    ////这是循环插入的方法
    private void FillDataXBraCode(HSSFSheet worksheet, System.Data.DataTable dt, int beginRow, HSSFWorkbook workbook)
    {
        int rowCount = dt.Rows.Count;
        int colCount = dt.Columns.Count;
        string[,] dataArray = new string[rowCount, colCount];
        for (int i = 0; i < rowCount; i++)
        {
            HSSFRow row = null;
            if (beginRow == 2)
                row = worksheet.CreateRow(i + 2);
            else
                row = worksheet.CreateRow(i + 1);

            for (int j = 0; j < colCount; j++)
            {
                dataArray[i, j] = dt.Rows[i][j] == null ? string.Empty : dt.Rows[i][j].ToString();
                HSSFCell cell = row.CreateCell(j);
                //
                if (j == 1)
                {
                    HSSFPatriarch patriarch = worksheet.CreateDrawingPatriarch();
                    string unitNumber = (new BarCodeGenerator()).CreateBarcode("*" + dataArray[i,j] + "*", 20);
                    byte[] bytes1 = System.IO.File.ReadAllBytes(unitNumber);
                    int pictureIdx1 = workbook.AddPicture(bytes1, PictureType.JPEG);

                    HSSFClientAnchor anchor1 = new HSSFClientAnchor(3, 3, 1020, 255, j, i, j, i);
                    HSSFPicture pict1 = patriarch.CreatePicture(anchor1, pictureIdx1);

                    //pict1.Resize();
                    File.Delete(unitNumber);
                }
                else
                {
                    //
                    cell.SetCellValue(dataArray[i, j]);
                }
            }
        }
    }