ASP.NET DataSet数据生成Excel文档导出下载

最新推荐文章于 2018-12-17 22:33:27 发布
落泪的只有我
最新推荐文章于 2018-12-17 22:33:27 发布
阅读量1.8k
点赞数 1
分类专栏： .NET C# 文章标签： ASP .NET C# DataSet Excel
本文链接：https://blog.csdn.net/luolei3344/article/details/68490148
版权
C# 同时被 2 个专栏收录
12 篇文章 0 订阅
订阅专栏
.NET
1 篇文章 0 订阅
订阅专栏
 public partial class am_ElectronicInvoinceExportExcel : BasePage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            ExcelFile excelFile = new ExcelFile();
            excelFile.Worksheets.Add("发票信息表");
            ExcelWorksheet xlsheet = excelFile.Worksheets[0];
            if (excelFile == null || xlsheet == null)
            {
                base.SetException("无法创建Excel对象，可能您的计算机未安装Excel。请先安装Excel");
                return;
            }

            DataSet ds = new DataSet();
            ds.Merge((DataSet)Session[WebConst.DOWNLOADDATA]);
            //Session[WebConst.DOWNLOADDATA] = null;
            if (OperateUI.HaveData(ds))
            {
                #region 列名
                int rowIndex = 0;
                xlsheet.Rows[rowIndex].Height = 400;
                xlsheet.Rows[rowIndex].Style.Font.Name = "宋体";
                xlsheet.Rows[rowIndex].Style.Font.Size = 9 * 20;
                xlsheet.Rows[rowIndex].Style.VerticalAlignment = VerticalAlignmentStyle.Center;
                xlsheet.Rows[rowIndex].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;

                CellRange cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 0, rowIndex, 0);
                cellRange.Merged = true;
                cellRange.Value = "发票代码";

                cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 1, rowIndex, 1);
                cellRange.Merged = true;
                cellRange.Value = "发票号码";

                cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 2, rowIndex, 2);
                cellRange.Merged = true;
                cellRange.Value = "提单号";

                cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 3, rowIndex, 3);
                cellRange.Merged = true;
                cellRange.Value = "进出口";

                cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 4, rowIndex, 4);
                cellRange.Merged = true;
                cellRange.Value = "币别";

                cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 5, rowIndex, 5);
                cellRange.Merged = true;
                cellRange.Value = "金额";

                cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 6, rowIndex, 6);
                cellRange.Merged = true;
                cellRange.Value = "人民币金额";

                cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 7, rowIndex, 7);
                cellRange.Merged = true;
                cellRange.Value = "开票日期";
                #endregion

                #region 明细数据
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    rowIndex++;
                    xlsheet.Rows[rowIndex].Height = 400;
                    xlsheet.Rows[rowIndex].Style.Font.Name = "宋体";
                    xlsheet.Rows[rowIndex].Style.Font.Size = 9 * 20;
                    xlsheet.Rows[rowIndex].Style.VerticalAlignment = VerticalAlignmentStyle.Center;
                    xlsheet.Rows[rowIndex].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;

                    cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 0, rowIndex, 0);
                    cellRange.Merged = true;
                    cellRange.Value = dr["ARIVCD"].ToString().Trim();

                    cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 1, rowIndex, 1);
                    cellRange.Merged = true;
                    cellRange.Value = dr["ARIVNO"].ToString().Trim();

                    cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 2, rowIndex, 2);
                    cellRange.Merged = true;
                    cellRange.Value = dr["INBLNO"].ToString().Trim();

                    cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 3, rowIndex, 3);
                    cellRange.Merged = true;
                    cellRange.Value = dr["IHIE"].ToString().Trim();

                    cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 4, rowIndex, 4);
                    cellRange.Merged = true;
                    cellRange.Value = dr["INCURR"].ToString().Trim();

                    cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 5, rowIndex, 5);
                    cellRange.Merged = true;
                    cellRange.Value = dr["INAMT"].ToString().Trim();

                    cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 6, rowIndex, 6);
                    cellRange.Merged = true;
                    cellRange.Value = dr["INCNY"].ToString().Trim();

                    cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 7, rowIndex, 7);
                    cellRange.Merged = true;
                    cellRange.Value = dr["INDATE"].ToString().Trim();
                }
                #endregion

                #region 总额统计
                rowIndex++;
                xlsheet.Rows[rowIndex].Height = 400;
                xlsheet.Rows[rowIndex].Style.Font.Name = "宋体";
                xlsheet.Rows[rowIndex].Style.Font.Size = 9 * 20;
                xlsheet.Rows[rowIndex].Style.VerticalAlignment = VerticalAlignmentStyle.Center;
                xlsheet.Rows[rowIndex].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;

                decimal ipamt = 0;
                decimal ipcny = 0;
                try
                {
                    ipamt = Decimal.Parse(ds.Tables[0].Compute("SUM(INAMT)", "").ToString());
                }
                catch { }
                try
                {
                    ipcny = Decimal.Parse(ds.Tables[0].Compute("SUM(INCNY)", "").ToString());
                }
                catch { }

                cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 5, rowIndex, 5);
                cellRange.Merged = true;
                cellRange.Value = string.Format("{0:N2}", ipamt);

                cellRange = xlsheet.Cells.GetSubrangeAbsolute(rowIndex, 6, rowIndex, 6);
                cellRange.Merged = true;
                cellRange.Value = string.Format("{0:N2}", ipcny);
                #endregion

                #region 文件导出
                string strFileName = Path.Combine(Request.MapPath(".") + "\\report\\", DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls");
                excelFile.SaveXls(strFileName);

                Response.ClearContent();
                Response.ClearHeaders();
                Response.BufferOutput = true;
                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", "attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls");
                Response.WriteFile(strFileName);
                Response.Flush();

                try
                {
                    System.IO.File.Delete(strFileName);
                }
                catch { }
                #endregion
            }
            else
            {
                base.SetException("浏览器缓存数据出现异常，请重启浏览器后重新操作");
                return;
            }
        }
    }