NPOI分層導出

  1 using NPOI.HSSF.UserModel;
  2 using NPOI.POIFS.FileSystem;
  3 using org.in2bits.MyXls;
  4 using System;
  5 using System.Collections.Generic;
  6 using System.Data;
  7 using System.IO;
  8 using System.Linq;
  9 using System.Text;
 10 using System.Web;
 11 using System.Web.UI;
 12 using System.Web.UI.WebControls;
 13 using Topevery.Basis.Config;
 14 using Topevery.Basis.Logic;
 15 using Topevery.DUM.Entity;
 16 
 17 namespace Topevery.DUM.Report.PublicCaseAnalysis
 18 {
 19     public partial class EvtSpread : BasePage
 20     {
 21         protected void Page_Load(object sender, EventArgs e)
 22         {
 23             if (!this.IsPostBack)
 24             {
 25                 //txtStartDate.Text = string.Format("{0}-{1}-1", DateTime.Today.Year, DateTime.Today.Month);
 26                 //txtEndDate.Text = string.Format("{0:yyyy-MM-dd}", DateTime.Today);
 27                 txtStartDate.Text = "2015-01-01";
 28                 txtEndDate.Text = "2016-08-01";
 29             }
 30 
 31             AjaxPro.Utility.RegisterTypeForAjax(typeof(EvtSpread));
 32         }
 33 
 34         /// <summary>
 35         /// 分佈統計查詢
 36         /// </summary>
 37         /// <param name="evtPara"></param>
 38         /// <returns></returns>
 39         [AjaxPro.AjaxMethod(AjaxPro.HttpSessionStateRequirement.Read)]
 40         public static EvtSpreadData GetZxEvtSpreadData(SearchPara evtPara)
 41         {
 42             EvtSpreadData result = new EvtSpreadData();
 43 
 44             //1.查出所有數據
 45             BaseParameter para = new BaseParameter();
 46 
 47             para.PageIndex = 1;
 48             para.PageSize = int.MaxValue;
 49 
 50 
 51             string title = "";
 52 
 53             //lblTitle.Text = string.Format("{0:yyyy-MM-01}至{1:MM-dd}{3}專項案件分佈統計", DateTime.Today);
 54             if (!string.IsNullOrEmpty(evtPara.DateBegin))
 55             {
 56                 DateTime dtStart = DateTime.Now;
 57                 if (DateTime.TryParse(evtPara.DateBegin, out dtStart))
 58                 {
 59                     title += string.Format("{0:yyyy-MM-dd}—", dtStart);
 60                     para.BeginDate = dtStart;
 61                 }
 62             }
 63 
 64             if (!string.IsNullOrEmpty(evtPara.DateEnd))
 65             {
 66                 DateTime dtEnd = DateTime.Now;
 67                 if (DateTime.TryParse(evtPara.DateEnd, out dtEnd))
 68                 {
 69                     title += string.Format("{0:yyyy-MM-dd}", dtEnd);
 70                     para.EndDate = dtEnd.AddDays(1).AddSeconds(-1);
 71                 }
 72             }
 73 
 74 
 75             title += "案件分佈統計";
 76 
 77             int recordCount = 0;
 78             DataTable dt = QmcgLogic.EvtSearchList(para);
 79             if (dt != null)
 80             {
 81                 recordCount = dt.Rows.Count;
 82             }
 83 
 84             StringBuilder StrbSprdHtm = new StringBuilder();
 85             string strTableClass = "TUI-grid-list";
 86             StrbSprdHtm.AppendFormat("<table class='{0}' ><colgroup ><col width='30px'><col width='120px' /><col/><col/></colgroup><tr><th>序號</th><th class='AeraName ui-th-column'>區域</th><th class='EvtCount ui-th-column'>案件數</th></tr>", strTableClass);
 87 
 88             //迴圈各個區域
 89             Topevery.Basis.Entity.SysMapTreeCollection mapTreeColl = SysMapLogic.GetDefaultTree();
 90             if (mapTreeColl != null && mapTreeColl.Count > 0)
 91             {
 92                 foreach (Topevery.Basis.Entity.SysMapTree mapCity in mapTreeColl)
 93                 {
 94                     int iDistCount = 0;
 95                     //市下麵的區迴圈
 96                     foreach (Topevery.Basis.Entity.SysMapTree mapDist in mapCity.Child)
 97                     {
 98                         iDistCount++;
 99                         string strDistTrStyle = "ui-widget-content jqgrow ui-row-ltr ui-priority-secondary";
100                         if (iDistCount % 2 == 1)
101                         {
102                             strDistTrStyle = "ui-widget-content jqgrow ui-row-ltr";
103                         }
104                         strDistTrStyle = string.Format("{0} {1}", Topevery.Basis.Config.MapTypeEnum.District, strDistTrStyle);
105                         if (dt.Rows.Count > 0)
106                         {
107                             //int prbInDistCount = 0;
108                             int evtInDistCount = GetEvtCountByMap(dt, mapDist.ID, Topevery.Basis.Config.MapTypeEnum.District);
109                             //
110                             StrbSprdHtm.AppendFormat("<tr class='{2}' mapId='{3}'><td>{4}</td><td class='AeraName'><a class='TUI-link-btn fl-left' href='javascript:void(0);' onclick='showOrHideStreet(this);'><span class='ui-icon ui-icon-minus'></span></a><a href='javascript:mapPositions({3})'>{0}</a></td><td class='EvtCount'><a href='javascript:void(0);' onclick='showDetail(this,{3},13002)'>{1}</a></td></tr>", mapDist.Name, evtInDistCount, strDistTrStyle, mapDist.ID, iDistCount);
111 
112 
113                             if (evtInDistCount > 0)
114                             {
115                                 StrbSprdHtm.AppendFormat("<tr class='{0}_Out'><td></td><td colspan='2' border='0'>", MapTypeEnum.Street);
116                                 StrbSprdHtm.AppendFormat("<table class='{0}'><colgroup ><col width='30px'><col width='120px' /><col/><col/></colgroup><tr><th>序號</th><th class='AeraName ui-th-column'>街道</th><th class='EvtCount ui-th-column'>案件數</th></tr>", strTableClass);
117                                 int iStreetCount = 0;
118                                 //迴圈區下麵的街道
119                                 foreach (Topevery.Basis.Entity.SysMapTree mapStreet in mapDist.Child)
120                                 {
121                                     string strStreetStyle = "ui-widget-content jqgrow ui-row-ltr ui-priority-secondary";
122                                     if (iStreetCount % 2 == 1)
123                                     {
124                                         strStreetStyle = "ui-widget-content jqgrow ui-row-ltr";
125                                     }
126                                     //int prbInStreetCount = 0;
127                                     int evtInStreetCount = GetEvtCountByMap(dt, mapStreet.ID, Topevery.Basis.Config.MapTypeEnum.Street);
128                                     if (evtInStreetCount > 0)
129                                     {
130                                         iStreetCount++;
131                                         StrbSprdHtm.AppendFormat("<tr class='{2}_Inner {5}' mapId='{3}'><td>{6}</td><td class='AeraName'><a class='TUI-link-btn fl-left' href='javascript:void(0);' onclick='showOrHideCmn(this);'><span class='ui-icon ui-icon-plus'></span></a><a href='javascript:mapPositions({3})'>{0}</a></td><td class='EvtCount'><a href='javascript:void(0);' onclick='showDetail(this,{3},13003)'>{1}</a></td></tr><tr class='{4}_Out' style='display:none'><td></td><td colspan='2'>", mapStreet.Name, evtInStreetCount, MapTypeEnum.Street, mapStreet.ID, MapTypeEnum.Community, strStreetStyle, iStreetCount);
132                                         StrbSprdHtm.AppendFormat("<table class='{0}'><colgroup><col width='30px'><col width='120px' /><col/><col/></colgroup><tr><th>序號</th><th class='AeraName ui-th-column'>社區</th><th class='EvtCount ui-th-column'>案件數</th></tr>", strTableClass);
133 
134                                         int iCmnCount = 0;
135                                         //迴圈街道下麵的社區
136                                         foreach (Topevery.Basis.Entity.SysMapTree mapComn in mapStreet.Child)
137                                         {
138                                             //int prbInCmnCount = 0;
139                                             int evtInCmnCount = GetEvtCountByMap(dt, mapComn.ID, Topevery.Basis.Config.MapTypeEnum.Community);
140                                             if (evtInCmnCount > 0)
141                                             {
142                                                 iCmnCount++;
143                                                 StrbSprdHtm.AppendFormat("<tr class='{2}_Inner' mapId='{3}'><td>{4}</td><td class='AeraName'><a href='javascript:mapPositions({3})'>{0}</a></td><td class='EvtCount'><a href='javascript:void(0);' onclick='showDetail(this,{3},13004)'>{1}</a></td></tr>", mapComn.Name, evtInCmnCount, MapTypeEnum.Community, mapComn.ID, iCmnCount);
144                                             }
145 
146                                         }
147                                         StrbSprdHtm.Append("</table></td></tr>");
148                                     }
149                                 }
150                                 StrbSprdHtm.Append("</table>");
151                             }
152                             else
153                             {
154                                 StrbSprdHtm.AppendFormat("<tr class='{0}_Out' style='display:none;'><td></td><td colspan='2' border='0'>", MapTypeEnum.Street);
155                             }
156                             StrbSprdHtm.Append("</td></tr>");
157                         }
158                     }
159 
160                     StrbSprdHtm.Append("</table>");
161 
162                     StrbSprdHtm.Append("</td></tr>");
163                 }
164             }
165 
166             StrbSprdHtm.Append("</table>");
167 
168             result.SprHtmlResult = StrbSprdHtm.ToString();
169             result.Title = title;
170             //ExportQueryService.SetQueryVal(ExportEnumKey, para);
171             foreach (DataRow dr in dt.Rows)
172             {
173                 try
174                 {
175                     Evt evt = new Evt();
176                     evt.EvtId = Convert.ToInt64(dr["c_id"]);
177 
178                     try
179                     {
180                         evt.AbsX = Convert.ToDouble(dr["c_abs_x"]);
181 
182                         evt.AbsY = Convert.ToDouble(dr["c_abs_y"]);
183                     }
184                     catch { }
185 
186                     try
187                     {
188                         evt.DistId = Convert.ToInt32(dr["c_map_district_id"]);
189                         evt.StreetId = Convert.ToInt32(dr["c_map_street_id"]);
190                         evt.CmnId = Convert.ToInt32(dr["c_map_community_id"]);
191                     }
192                     catch { }
193 
194                     evt.Title = Convert.ToString(dr["c_title"]);
195 
196                     //evt.Status = Convert.ToInt32(dr["c_status"]);
197 
198                     result.ListEvt.Add(evt);
199                 }
200                 catch { }
201             }
202             return result;
203 
204         }
205 
206 
207 
208         /// <summary>
209         /// 導出excel文件
210         /// </summary>
211         /// <param name="reportTitle">標題</param>
212         /// <param name="dt">數據源</param>
213         /// <param name="columns">列名</param>
214         /// <param name="filePath">文件路徑</param>
215         /// <param name="maxRow">一頁顯示最大記錄數</param>
216         public static void SingleExport(string reportTitle, string subTitle, DataTable dt, Dictionary<string, string> columns, string filePath, int maxRow)
217         {
218             int WorkSheetCount = Convert.ToInt32(Math.Ceiling(dt.Rows.Count * 1.0 / maxRow));
219             XlsDocument doc = new XlsDocument();
220             for (int i = 0; i < WorkSheetCount; i++)
221             {
222                 Worksheet sheet = doc.Workbook.Worksheets.Add(string.Format("Sheet{0}", i + 1));
223 
224                 int rowIndex = 0;
225                 int colIndex = 0;
226                 int currentMinRowIndex = i * maxRow;
227                 int currentMaxRowIndex = 0;
228                 if (((i + 1) * maxRow + 1) < dt.Rows.Count)
229                     currentMaxRowIndex = (i + 1) * maxRow;
230                 else
231                     currentMaxRowIndex = dt.Rows.Count;
232 
233                 //標題
234                 if (!string.IsNullOrEmpty(reportTitle))
235                 {
236                     rowIndex++;
237 
238                     XF cellXF = doc.NewXF();
239                     cellXF.VerticalAlignment = VerticalAlignments.Centered;
240                     cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
241                     cellXF.Font.Height = 24 * 12;
242                     cellXF.Font.Bold = true;
243                     //cellXF.Pattern = 1;//設定單元格填充風格。如果設定為0，則是純色填充
244                     //cellXF.PatternBackgroundColor = Colors.EgaYellow;//填充的底色
245                     //cellXF.PatternColor = Colors.Grey;//設定填充線條的顏色
246 
247                     sheet.Cells.Add(rowIndex, 1, reportTitle, cellXF);
248                     MergeArea area = new MergeArea(1, 1, 1, columns.Count);
249                     sheet.AddMergeArea(area);
250                 }
251                 //副標題
252                 if (!string.IsNullOrEmpty(subTitle))
253                 {
254                     rowIndex++;
255 
256                     XF cellXF = doc.NewXF();
257                     cellXF.VerticalAlignment = VerticalAlignments.Centered;
258                     cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
259 
260                     sheet.Cells.Add(rowIndex, 1, subTitle, cellXF);
261                     MergeArea area = new MergeArea(2, 2, 1, columns.Count);
262                     sheet.AddMergeArea(area);
263                 }
264 
265                 //列頭標題
266                 rowIndex++;
267                 foreach (string colTitle in columns.Values)
268                 {
269                     colIndex++;
270 
271                     XF cellXF = doc.NewXF();
272                     cellXF.VerticalAlignment = VerticalAlignments.Centered;
273                     cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
274                     cellXF.Font.Bold = true;
275                     cellXF.Pattern = 0;//設定單元格填充風格。如果設定為0，則是純色填充
276                     cellXF.PatternBackgroundColor = Colors.Grey;//填充的底色
277 
278                     cellXF.TopLineColor = Colors.Black;
279                     cellXF.TopLineStyle = 1;
280                     cellXF.BottomLineColor = Colors.Black;
281                     cellXF.BottomLineStyle = 1;
282                     cellXF.LeftLineColor = Colors.Black;
283                     cellXF.LeftLineStyle = 1;
284                     cellXF.RightLineColor = Colors.Black;
285                     cellXF.RightLineStyle = 1;
286 
287                     sheet.Cells.Add(rowIndex, colIndex, colTitle, cellXF);
288 
289                 }
290 
291                 //數據行
292                 for (int j = currentMinRowIndex; j < currentMaxRowIndex; j++)
293                 {
294                     rowIndex++;
295                     colIndex = 0;
296 
297                     XF cellXF = doc.NewXF();
298                     cellXF.VerticalAlignment = VerticalAlignments.Centered;
299                     cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
300 
301                     cellXF.TopLineColor = Colors.Black;
302                     cellXF.TopLineStyle = 1;
303                     cellXF.BottomLineColor = Colors.Black;
304                     cellXF.BottomLineStyle = 1;
305                     cellXF.LeftLineColor = Colors.Black;
306                     cellXF.LeftLineStyle = 1;
307                     cellXF.RightLineColor = Colors.Black;
308                     cellXF.RightLineStyle = 1;
309 
310                     foreach (string colName in columns.Keys)
311                     {
312                         colIndex++;
313                         object v = dt.Rows[j][colName];
314                         if (v is Int32 || v is Int16 || v is Int64 || v is Double || v is Single)
315                         {
316                             sheet.Cells.Add(rowIndex, colIndex, v == null || v == DBNull.Value ? "" : v, cellXF);
317                         }
318                         else
319                         {
320                             sheet.Cells.Add(rowIndex, colIndex, v == null || v == DBNull.Value ? "" : v.ToString(), cellXF);
321                         }
322                     }
323                 }
324             }
325 
326             doc.FileName = Guid.NewGuid().ToString() + ".xls";
327             if (!string.IsNullOrEmpty(filePath))
328             {
329                 doc.Save(filePath);
330             }
331             else
332             {
333 
334                 string folder = HttpContext.Current.Server.MapPath("~/Templates");
335 
336                 try
337                 {
338                     foreach (string file in System.IO.Directory.GetFiles(folder))
339                     {
340                         System.IO.FileInfo fi = new System.IO.FileInfo(file);
341                         if (DateTime.Now.Subtract(fi.CreationTime).TotalDays > 2)
342                         {
343                             System.IO.File.Delete(file);
344                         }
345                     }
346                 }
347                 catch { }
348                 doc.Save(folder);
349                 System.Web.HttpContext.Current.Response.WriteFile("~/Templates/" + doc.FileName);
350             }
351         }
352 
353         public const string ExportEnumKey = ExportKey.Regional;
354 
355         public static int GetEvtCountByMap(DataTable evtData, int mapId, Topevery.Basis.Config.MapTypeEnum mapType)
356         {
357             //prbCount = 0;
358             try
359             {
360                 string filter = "";
361                 switch (mapType)
362                 {
363                     case MapTypeEnum.District:
364                         filter = string.Format("[c_map_district_id]={0}", mapId);
365                         break;
366                     case MapTypeEnum.Street:
367                         filter = string.Format("[c_map_street_id]={0}", mapId);
368                         break;
369                     case MapTypeEnum.Community:
370                         filter = string.Format("[c_map_community_id]={0}", mapId);
371                         break;
372                 }
373 
374                 //object oPrbCount = evtData.Compute("sum([c_count])", filter);
375                 //try
376                 //{
377                 //    prbCount = Convert.ToInt32(oPrbCount);
378                 //}
379                 //catch { }
380                 return evtData.Select(filter).Length;
381             }
382             catch { }
383 
384             return 0;
385         }
386 
387         protected void Download_Click(object sender, EventArgs e)
388         {
389 
390             int recordCount = 0;
391             BaseParameter para = new BaseParameter();
392             string title = "";
393             if (!string.IsNullOrEmpty(txtStartDate.Text))
394             {
395                 DateTime dtStart = DateTime.Now;
396                 if (DateTime.TryParse(txtStartDate.Text, out dtStart))
397                 {
398                     title += string.Format("{0:yyyy-MM-dd}—", dtStart);
399                     para.BeginDate = dtStart;
400                 }
401             }
402             if (!string.IsNullOrEmpty(txtEndDate.Text))
403             {
404                 DateTime dtEnd = DateTime.Now;
405                 if (DateTime.TryParse(txtEndDate.Text, out dtEnd))
406                 {
407                     title += string.Format("{0:yyyy-MM-dd}", dtEnd);
408                     para.EndDate = dtEnd.AddDays(1).AddSeconds(-1);
409                 }
410             }
411             DataTable dt = QmcgLogic.EvtSearc