ASP.NET MVC 导出Word报表

最近要做MVC导出Word报表功能。查了查资料发现一个好用的插件就是Aspose.Word。这个插件也很有名气,也很好用。

1.首先就是引用该插件

 

 

2.填充Word模版

3.后台操作

 private List<double> QuaterAirPM10AvgVolReport(string stns, DateTime start, DateTime end, Aspose.Words.DocumentBuilder builder, out DataTable dt, out List<double> widthList,string isMax)
        {
            dt = QuaterPM10AvgVol (stns, start, end,isMax);
            widthList = new List<double>();
            double[] colWidth = new double[] { 50, 118, 117, 50, 118, 117 };
            string[] colName = new string[] { "排序", "城市", start.Year + "年" + start.Month + "～"+end.Month+"月浓度(μg/m3)", "排序", "城市", "较" + start.AddYears(-1).Year + "年同期增幅" };
            builder.MoveToBookmark("table3");
            Aspose.Words.Tables.Table table = builder.StartTable();//开始画Table 
            builder.InsertCell();
            builder.CellFormat.Borders.LineStyle = LineStyle.Single;
            builder.CellFormat.Borders.Color = System.Drawing.Color.Black;
            builder.CellFormat.VerticalMerge = CellMerge.First;
        
            builder.CellFormat.Width = 285;
            builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
            //  builder.CellFormat.VerticalAlignment = Aspose.Words.Tables.CellVerticalAlignment.Center;
            builder.Write("按平均浓度排序");

            builder.InsertCell();
            builder.CellFormat.Borders.LineStyle = LineStyle.Single;
            builder.CellFormat.Borders.Color = System.Drawing.Color.Black;
            builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
            // builder.CellFormat.VerticalAlignment = Aspose.Words.Tables.CellVerticalAlignment.Center;
            builder.CellFormat.VerticalMerge = CellMerge.None;
            builder.CellFormat.Width = 285;
         
            builder.Write("按" + start.AddYears(-1).Year + "年同期增幅排序");
            builder.EndRow();
            AsposeCreateCell(builder, colWidth[0], colName[0]);
            AsposeCreateCell(builder, colWidth[1], colName[1]);
            AsposeCreateCell(builder, colWidth[2], colName[2]);
            AsposeCreateCell(builder, colWidth[3], colName[3]);
            AsposeCreateCell(builder, colWidth[4], colName[4]);
            AsposeCreateCell(builder, colWidth[5], colName[5]);
            builder.EndRow();
            //开始添加值
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i]["CityName"] == "12个考核地市" || dt.Rows[i]["CityName"] == "全省")
                {
                    builder.InsertCell();
                    builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                    builder.CellFormat.Borders.Color = System.Drawing.Color.Black;
                    builder.CellFormat.VerticalMerge = CellMerge.First;

                    builder.CellFormat.Width = 168;
                    builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;

                    builder.Write(dt.Rows[i]["CityName"].ToString());


                    builder.InsertCell();
                    builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                    builder.CellFormat.Borders.Color = System.Drawing.Color.Black;
                    builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
                    builder.CellFormat.VerticalMerge = CellMerge.None;
                    builder.CellFormat.Width = 117;
                    builder.Write(dt.Rows[i]["PM10ATI"].ToString());

                    builder.InsertCell();
                    builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                    builder.CellFormat.Borders.Color = System.Drawing.Color.Black;
                    builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
                    builder.CellFormat.VerticalMerge = CellMerge.None;
                    builder.CellFormat.Width = 168;
                    builder.Write(dt.Rows[i]["qnCityName"].ToString());

                    builder.InsertCell();
                    builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                    builder.CellFormat.Borders.Color = System.Drawing.Color.Black;
                    builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
                    builder.CellFormat.VerticalMerge = CellMerge.None;
                    builder.CellFormat.Width = 117;
                    builder.Write(dt.Rows[i]["tqbh"].ToString() + "%");


                }
                else
                {
                    AsposeCreateCell(builder, colWidth[0], dt.Rows[i]["Sort"].ToString());
                    AsposeCreateCell(builder, colWidth[1], dt.Rows[i]["CityName"].ToString());
                    AsposeCreateCell(builder, colWidth[2], dt.Rows[i]["PM10ATI"].ToString());


                    AsposeCreateCell(builder, colWidth[3], dt.Rows[i]["qnSort"].ToString());
                    AsposeCreateCell(builder, colWidth[4], dt.Rows[i]["qnCityName"].ToString());
                    AsposeCreateCell(builder, colWidth[5], dt.Rows[i]["tqbh"].ToString() + "%");

                }
                builder.EndRow();

            }
            builder.EndTable();
            return widthList;
        }

其中有几个注意的地方 builder.CellFormat.VerticalMerge = CellMerge.None;CellMerge是枚举类型，经常用到画复杂的表格，或者是合并单元格。还有First和Previous。先要得到DataTable数据，最后对数据进行操作就行了。

 

4.输出文档

 public JsonResult QuaterResponse()
        {
            bool result;
            string quarter = Request["quarter"].ToString();
            string stns = Request["stns"].ToString();
            string isMax = Request["ismax"].ToString();
            DateTime startTime = Convert.ToDateTime(Request["startdate"]);
            DateTime endTime = Convert.ToDateTime(Request["enddate"]);
            string tmppath = Server.MapPath("~/Document/Model/QuaterReport.docx");


            string path = Server.MapPath("~/Document/Export/QuaterReport.doc");
            Aspose.Words.Document doc = new Document(tmppath);
            Aspose.Words.DocumentBuilder builder = new DocumentBuilder(doc);
            doc.Range.Bookmarks["title"].Text = startTime.Year+"年"+quarter+"湖北省环境空气质量监测情况综述";
            doc.Range.Bookmarks["title1"].Text = "表1 "+quarter+"空气质量等级";
            doc.Range.Bookmarks["title2"].Text = "表2"  +quarter+"优良天数达标率情况表";
            doc.Range.Bookmarks["title3"].Text = "表3 "+quarter+"空气可吸入颗粒物（PM10）平均浓度情况表";
            doc.Range.Bookmarks["title4"].Text = "表4 "+quarter+"空气可吸入颗粒物（PM2.5）平均浓度情况表";
            doc.Range.Bookmarks["title5"].Text = "表5"+quarter+" 境空气气态污染物平均浓度情况表";
            doc.Range.Bookmarks["title6"].Text = "表6  "+quarter+"环境空气质量综合指数情况表";
            DataTable dt;
            List<double> widthList;
            try
            {

                doc.Range.Bookmarks["table1"].Text = "";    // 清掉标示 
                QuaterAirPerencetReport( stns, startTime, endTime, builder, out dt, out widthList,isMax);

                   doc.Range.Bookmarks["table2"].Text = "";
                QuaterAirYldblReport(stns, startTime, endTime, builder,quarter, out dt, out widthList,isMax);

                doc.Range.Bookmarks["table3"].Text = "";
                QuaterAirPM10AvgVolReport(stns, startTime, endTime, builder, out dt, out widthList,isMax);
                doc.Range.Bookmarks["table4"].Text = "";
                QuaterAirPM25AvgVolReport(stns, startTime, endTime, builder, out dt, out widthList,isMax);
                doc.Range.Bookmarks["table5"].Text = "";
                QuaterOtherAvgVolReport(stns, startTime, endTime, builder, out dt, out widthList,isMax);
                doc.Range.Bookmarks["table6"].Text = "";
                QuaterZHIndexReport(stns, startTime, endTime, builder, out dt, out widthList,isMax);

                doc.Save(path, Aspose.Words.SaveFormat.Doc);


             //    System.Diagnostics.Process.Start(path);//打开文档

                  // return View("QuaterReport");
                result = true;
            }
            catch (Exception)
            {
                result = false;

            }
            return Json(result);

        }