导出Excel报表-优快云博客

本文链接：https://blog.youkuaiyun.com/linxianliang5201314/article/details/6912882

    private void ExportExcel(DataTable dtTmp)
    {
        string excelName = "";
        string fileName = @Server.MapPath("..\\File\\");

        if (dtTmp.Rows.Count <= 0)
        {
            this.lbInfo.Text = "<script>alert('导出失败！错误信息：\\n 没有任何数据!" + "')</script>";
            return;
        }

        Excel.Application xlApp = null;
        Excel.Workbooks workbooks = null;
        Excel.Workbook workbook = null;
        Excel.Sheets sheets = null;

try
{

#region 生成采购单Excel

            xlApp = new Excel.Application();
            if (xlApp == null)
            {
                this.lbInfo.Text = "<script>alert('无法创建Excel对象，可能您的电脑未安装Excel！')</script>";
                return;
            }

xlApp.Visible = false;
xlApp.DisplayAlerts = false;

            workbooks = xlApp.Workbooks;
            workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            sheets = workbook.Worksheets;

            if (excelName == "")
            {
                excelName = "出入库汇总报表_" + this.txtStartDate.Value.ToString() + "至" + this.txtEndDate.Value.ToString() + ".xls";
                fileName += excelName;
            }

#region 出入库汇总报表

            Excel.Worksheet worksheet1 = (Excel.Worksheet)sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            worksheet1 = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            worksheet1.Name = "汇总报表";

int hz_rowCount = 1;

            string orderCode = "机构未明确";
            try
            {
                orderCode = this.ddlBuyAgency.SelectedItem.Text.ToString();
            }
            catch
            {
                orderCode = "机构未明确";
            }

            string DeptName = "科室未明确";
            try
            {
                DeptName = this.txtDeptName.Text.ToString();
            }
            catch
            {
                DeptName = "科室未明确";
            }

int celCount = dtTmp.Columns.Count;
Excel.Range range = null;

            if (hz_rowCount == 1)
            {
                worksheet1.Cells[hz_rowCount, 1] = this.txtStartDate.Value.ToString() + "至" + this.txtEndDate.Value.ToString() + "汇总报表";
                range = worksheet1.get_Range(worksheet1.Cells[hz_rowCount, 1], worksheet1.Cells[hz_rowCount, celCount]);
                range.Merge(Type.Missing);//合并单元格
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                range.Font.Size = 15;
                range.Font.Bold = true;
            }

//hz_rowCount++;

            //worksheet1.Cells[hz_rowCount, 2] = orderCode;
            //worksheet1.Cells[hz_rowCount, celCount - 3] = supplierName;
            //range = worksheet1.get_Range(worksheet1.Cells[hz_rowCount, celCount - 3], worksheet1.Cells[hz_rowCount, celCount]);
            //range.Merge(Type.Missing);//合并单元格

hz_rowCount++;

            worksheet1.Cells[hz_rowCount, 1] = "采购机构：";
            range = worksheet1.get_Range(worksheet1.Cells[hz_rowCount, 1], worksheet1.Cells[hz_rowCount, 1]);
            range.Font.Bold = true;
            range.Interior.Color = System.Drawing.Color.FromArgb(230, 230, 250).ToArgb();

            worksheet1.Cells[hz_rowCount, 2] = orderCode;
            range = worksheet1.get_Range(worksheet1.Cells[hz_rowCount, 2], worksheet1.Cells[hz_rowCount, 2]);
            range.Interior.Color = System.Drawing.Color.FromArgb(230, 230, 250).ToArgb();

            worksheet1.Cells[hz_rowCount, 3] = "采购科室：";
            range = worksheet1.get_Range(worksheet1.Cells[hz_rowCount, 3], worksheet1.Cells[hz_rowCount, 3]);
            range.Font.Bold = true;
            range.Interior.Color = System.Drawing.Color.FromArgb(230, 230, 250).ToArgb();

            worksheet1.Cells[hz_rowCount, 4] = DeptName;
            range = worksheet1.get_Range(worksheet1.Cells[hz_rowCount, 4], worksheet1.Cells[hz_rowCount, celCount]);
            range.Interior.Color = System.Drawing.Color.FromArgb(230, 230, 250).ToArgb();

hz_rowCount++;

            worksheet1.Cells[hz_rowCount, 1] = "开始日期：";
            range = worksheet1.get_Range(worksheet1.Cells[hz_rowCount, 1], worksheet1.Cells[hz_rowCount, 1]);
            range.Font.Bold = true;
            range.Interior.Color = System.Drawing.Color.FromArgb(224, 255, 255).ToArgb();

            worksheet1.Cells[hz_rowCount, 2] = this.txtStartDate.Value.ToString();
            range = worksheet1.get_Range(worksheet1.Cells[hz_rowCount, 2], worksheet1.Cells[hz_rowCount, 2]);
            range.Interior.Color = System.Drawing.Color.FromArgb(224, 255, 255).ToArgb();

            worksheet1.Cells[hz_rowCount, 3] = "结束日期：";
            range = worksheet1.get_Range(worksheet1.Cells[hz_rowCount, 3], worksheet1.Cells[hz_rowCount, 3]);
            range.Font.Bold = true;
            range.Interior.Color = System.Drawing.Color.FromArgb(224, 255, 255).ToArgb();

            worksheet1.Cells[hz_rowCount, 4] = this.txtEndDate.Value.ToString();
            range = worksheet1.get_Range(worksheet1.Cells[hz_rowCount, 4], worksheet1.Cells[hz_rowCount, celCount]);
            range.Interior.Color = System.Drawing.Color.FromArgb(224, 255, 255).ToArgb();

            range = worksheet1.get_Range("H5", "H6");
            range.Select();
            xlApp.ActiveWindow.FreezePanes = true;
            hz_rowCount++;

            for (int i = 0; i < celCount; i++)
            {
                worksheet1.Cells[hz_rowCount, i + 1] = dtTmp.Columns[i].ColumnName.Trim();
            }

            range = (Excel.Range)worksheet1.get_Range(worksheet1.Cells[hz_rowCount, 1], worksheet1.Cells[hz_rowCount, celCount]);
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            range.Font.Bold = true;
            range.Interior.Color = System.Drawing.Color.FromArgb(230, 230, 250).ToArgb();

            foreach (DataRow dr in dtTmp.Rows)
            {
                hz_rowCount++;
                for (int j = 0; j < celCount; j++)
                {
                    worksheet1.Cells[hz_rowCount, j + 1] = dr[j].ToString().Trim();
                }
                range = worksheet1.get_Range(worksheet1.Cells[hz_rowCount, 1], worksheet1.Cells[hz_rowCount, celCount]);
                if (hz_rowCount % 2 == 1)
                {
                    range.Interior.Color = System.Drawing.Color.FromArgb(224, 255, 255).ToArgb();
                }
                else
                {
                    range.Interior.Color = System.Drawing.Color.FromArgb(230, 230, 250).ToArgb();
                }
            }

hz_rowCount += 3;

worksheet1.Columns.EntireColumn.AutoFit();//列宽自适应。
#endregion

workbook.SaveCopyAs(fileName);

System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);

workbook = null;

workbooks.Close();

System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);

workbooks = null;

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

GC.Collect();//强行销毁

#endregion

#region 下载采购单Excel

            Response.Clear();
            Response.ClearHeaders();
            Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(excelName));
            Response.ContentType = "application/ms-excel";
            Response.ContentEncoding = System.Text.Encoding.UTF8;

FileStream myFile = File.OpenRead(fileName); //读取文件进入FileStream

byte[] fileCont = new byte[myFile.Length];

myFile.Read(fileCont, 0, (int)myFile.Length); //将文件流中的内容转成byte数组

System.Web.HttpContext.Current.Response.BinaryWrite(fileCont);

System.Web.HttpContext.Current.Response.Flush();

System.Web.HttpContext.Current.Response.Clear();

System.Web.HttpContext.Current.Response.Close(); //关闭文件流

myFile.Close();

#endregion

        }
        catch (Exception ex)
        {
            if (workbook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            }

            workbook = null;
            if (workbooks != null)
            {
                workbooks.Close();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                workbooks = null;
            }
            if (xlApp != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
            }
            xlApp = null;

GC.Collect();

System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.ClearHeaders();

            string exMessage = ex.Message.ToString().Trim().Replace("'", "@");
            this.lbInfo.Text = "<script>alert('导出失败！错误信息：\\n " + exMessage + "')</script>";
            return;
        }
    }

导出报表代码