导出数据生成excel

最新推荐文章于 2022-10-25 11:35:25 发布

weixin_34355715

最新推荐文章于 2022-10-25 11:35:25 发布

阅读量72

点赞数

原文链接：http://www.cnblogs.com/shanshuiYiCheng/p/9629291.html

版权

本文介绍了一个ASP.NET应用程序中的经费使用总结表导出功能实现方法。通过点击按钮触发经费报表的导出流程，从数据库获取指定状态的数据，并将其转换为Excel文件。此过程涉及到了数据集操作、条件查询及Excel文件生成等关键步骤。

摘要由CSDN通过智能技术生成

前台：

<asp:Button ID="btnMoney" runat="server" Text="经费使用总结表" CssClass="admin_cx marginleft" Height="25" OnClick="btnMoney_Click"  />

后台：

        protected void btnMoney_Click(object sender, EventArgs e)
        {
            if (this.ArticleId.Value == "")
            {
                this.Alert("请点击下拉选择您想操作的文章");
                return;
            }
            int id = Convert.ToInt32(this.ArticleId.Value);
            string sqlwhere = " ArticleId = " + id + " and State=" + (int)CedureEnum.CapitalDeclareState.Complete;
            DataSet ds = IBLL.Factory.infoCapitalDeclareCreate().GetPageList(sqlwhere, ref oPageInfo, "a.CreateDate desc");
            DataTable dt = ds.Tables[0];
            LeadExcel(dt, "汇总表", "three", id);
        }

        #region  生成xls文件
        //按采购人统计
        protected void LeadExcel(DataTable dt, string opetes, string state, int ArticleId=0)
        {
            string filename = opetes + DateTime.Now.ToString("yyyyMMdd") + ".xls";
            MemoryStream ms = null;
            try
            {
                ms = new MemoryStream();//
                IWorkbook xssfworkbook = null;
                if (filename.IndexOf(".xlsx") > -1)
                {
                    xssfworkbook = new XSSFWorkbook();
                }
                else
                    xssfworkbook = new HSSFWorkbook();

                ISheet sheet = xssfworkbook.CreateSheet("Sheet1");
                ICellStyle cellstyle = xssfworkbook.CreateCellStyle();//设置垂直居中格式
                cellstyle.Alignment = HorizontalAlignment.CENTER;

                SetCellRangeAddress(sheet, 0, 0, 0, 7);
                sheet.DefaultColumnWidth = 28;

                //string title = "车辆信息";//startdate.ToString("yyyy-MM-dd") + "至" + enddate.ToString("yyyy-MM-dd") + "";
                //sheet.CreateRow(0).CreateCell(0).SetCellValue("车辆信息");
                //sheet.GetRow(0).GetCell(0).CellStyle = cellstyle;

                //SetCellRangeAddress(sheet, 0, 0, 8, 11);
                //sheet.CreateRow(0).CreateCell(8).SetCellValue("审核结果");
                //sheet.GetRow(0).GetCell(8).CellStyle = cellstyle;
               
                    sheet.CreateRow(1).CreateCell(0).SetCellValue("公司名称");
                    sheet.GetRow(1).CreateCell(1).SetCellValue("项目名称");
                    sheet.GetRow(1).CreateCell(2).SetCellValue("申请人");
                    sheet.GetRow(1).CreateCell(3).SetCellValue("联系人");
                    sheet.GetRow(1).CreateCell(4).SetCellValue("联系电话");
                    sheet.GetRow(1).CreateCell(5).SetCellValue("填报日期"); ;
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        int row = 2;//行
                        int endrow = 0;//结束行
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            sheet.CreateRow(row).CreateCell(0).SetCellValue(dt.Rows[i]["Company"] + "");
                            sheet.GetRow(row).CreateCell(1).SetCellValue(dt.Rows[i]["ProjectName"] + "");
                            sheet.GetRow(row).CreateCell(2).SetCellValue(dt.Rows[i]["Proposer"] + "");
                            sheet.GetRow(row).CreateCell(3).SetCellValue(dt.Rows[i]["Contacts"] + "");
                            sheet.GetRow(row).CreateCell(4).SetCellValue(dt.Rows[i]["Mobile"] + "");
                            sheet.GetRow(row).CreateCell(5).SetCellValue(dt.Rows[i]["CreateDate"] + "");
                            row++;
                        }
                    }
                xssfworkbook.Write(ms);
                Response.Clear();
                Response.Charset = "utf-8";
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.AddHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(filename));
                Response.ContentType = "application/octet-stream";
                Response.BinaryWrite(ms.ToArray());
                Response.End();//
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ms.Dispose();
            }
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            sheet.AddMergedRegion(cellRangeAddress);
        }

        #endregion