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

 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;}}}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！