C#利用NPOI导出Excel

第一篇文章

1 using System;  2 using System.Collections.Generic;  3 using System.Linq;  4 using System.Text;  5 using System.ComponentModel;  6 using System.Data.Common;  7 using System.Data;  8 using System.Web;  9  10 namespace Ths.Expand.ExcelProcessing 11 { 12  13     /// 
 14     /// Excel 操作类 杨海峰 2011-07-27 15     /// 不允许继承该类 16     /// 
 17     [Serializable] 18     public sealed class ExcelPageUrl 19     { 20         private string TempXmlPath = HttpContext.Current.Server.MapPath("~"); 21  22  23         #region 公用属性public 24  25         #region 导出EXCLE时用到的缓存SQL语句 26  27         private string _Sql = string.Empty; 28         /// 
 29         /// 导出EXCLE时用到的缓存SQL语句 30         /// 
 31         [Description("导出EXCLE时用到的缓存SQL语句")] 32         public string Sql 33         { 34             get { return _Sql; } 35             set { _Sql = value; } 36         } 37  38         #endregion 39  40         #region 缓存记录的参数 41  42         private DbParameter[] _Parameters = new DbParameter[0]; 43         /// 
 44         /// 缓存记录的参数 45         /// 
 46         [Description("缓存记录的参数")] 47         public DbParameter[] Parameters 48         { 49             get { return _Parameters; } 50             set { _Parameters = value; } 51         } 52  53         #endregion 54  55         #region 记录导出的脚本类型 56  57         private CommandType commandTypeName = CommandType.Text; 58         /// 
 59         /// 记录导出的脚本类型 60         /// 
 61         [Description("记录导出的脚本类型")] 62         public CommandType CommandTypeName 63         { 64             get { return commandTypeName; } 65             set { commandTypeName = value; } 66         } 67  68         #endregion 69  70         #region 数据结果集 71  72         private DataSet _DataResults = null; 73         /// 
 74         /// 数据结果集 可以在配置文件中设置 IsDataResultsXML 为是否生成XML数据源 75         /// 
 76         [Description("数据结果集")] 77         public DataSet DataResults 78         { 79             get 80             { 81                 if (IsDataResultsXML) 82                 { 83                     if (_DataResults == null) 84                     { 85                         _DataResults = new DataSet(); 86                     } 87                     _DataResults.ReadXml(TempXmlPath + "\\ExcelAllocation\\" + HttpContext.Current.Request["PageUrl"] + ".xml"); 88                 } 89                 else 90                 { 91                     _DataResults = HttpContext.Current.Session["ExcelPageUrlDataResultsXML"] as DataSet; 92                 } 93                 return _DataResults; 94             } 95             set 96             { 97                 if (IsDataResultsXML) 98                 { 99                     _DataResults = value;100                     _DataResults.WriteXml(TempXmlPath + "\\ExcelAllocation\\" + HttpContext.Current.Request["PageUrl"] + ".xml");101                 }102                 else103                 {104                     HttpContext.Current.Session["ExcelPageUrlDataResultsXML"] = value;105                 }106             }107         }108 109         #endregion110 111         #region 导出图片文件路径112 113         private string _PicPath = string.Empty;114         /// 
115         /// 导出图片文件路径116         /// 
117         [Description("导出图片文件路径")]118         public string PicPath119         {120             get { return _PicPath; }121             set { _PicPath = value; }122         }123 124         #endregion125 126         #region 反射参数列表127 128         private object[] _AssemblyList = null;129         /// 
130         /// 反射参数列表131         /// 
132         [Description("反射参数列表")]133         public object[] AssemblyList134         {135             get { return _AssemblyList; }136             set { _AssemblyList = value; }137         } 138 139         #endregion140 141         #endregion142 143         #region 程序集属性internal144 145         #region Url 地址146 147         private string _PageUrl = string.Empty;148         /// 
149         /// Url 地址150         /// 
151         [Description("Url 地址")]152         internal string PageUrl153         {154             get { return _PageUrl; }155             set { _PageUrl = value; }156         }157 158         #endregion159 160         #region 工作薄名称161 162         private string _SheetName = "数据";163         /// 
164         /// 工作薄名称165         /// 
166         [Description("工作薄名称")]167         internal string SheetName168         {169             get { return _SheetName; }170             set { _SheetName = value; }171         }172 173         #endregion174 175         #region 导出表名176 177         private string _ExcelName = "数据列表";178         /// 
179         /// 导出表名180         /// 
181         [Description("导出表名")]182         internal string ExcelName183         {184             get { return _ExcelName; }185             set { _ExcelName = value; }186         }187 188         #endregion189 190         #region 导出字段配置类列表191 192         private DataTable _ExcelColumnNameList = null;193         /// 
194         /// 导出字段配置类列表195         /// 
196         [Description("导出字段配置类列表")]197         internal DataTable ExcelColumnNameList198         {199             get { return _ExcelColumnNameList; }200             set { _ExcelColumnNameList = value; }201         }202 203         #endregion204 205         #region Excel 多表头配置类列表206 207         private DataTable _ExcelManyTableHeadcsList = null;208         /// 
209         /// Excel 多表头配置类列表210         /// 
211         [Description("Excel 多表头配置类列表")]212         internal DataTable ExcelManyTableHeadcsList213         {214             get { return _ExcelManyTableHeadcsList; }215             set { _ExcelManyTableHeadcsList = value; }216         }217 218         #endregion219 220         #region 连接字符串221 222         private string _ConnectionStrings = string.Empty;223         /// 
224         /// 连接字符串225         /// 
226         [Description("连接字符串")]227         internal string ConnectionStrings228         {229             get { return _ConnectionStrings; }230             set { _ConnectionStrings = value; }231         }232 233         #endregion234 235         #region 是否冻结窗口236 237         private bool _IsCongelationWindow = false;238         /// 
239         /// 是否冻结窗口240         /// 
241         [Description("是否冻结窗口")]242         internal bool IsCongelationWindow243         {244             get { return _IsCongelationWindow; }245             set { _IsCongelationWindow = value; }246         }247 248         #endregion249 250         #region 是否调用dll方法251 252         private string _IsAssembly = string.Empty;253         /// 
254         /// 是否调用dll方法255         /// 
256         [Description("是否调用dll方法")]257         internal string IsAssembly258         {259             get { return _IsAssembly; }260             set { _IsAssembly = value; }261         }262 263         #endregion264 265         #region 调用dll地址266 267         private string _AssemblyPath = string.Empty;268         /// 
269         /// 调用dll地址270         /// 
271         [Description("调用dll地址")]272         internal string AssemblyPath273         {274             get { return _AssemblyPath; }275             set { _AssemblyPath = value; }276         }277 278         #endregion279 280         #region 调用类名281 282         private string _ClassName = string.Empty;283         /// 
284         /// 调用类名285         /// 
286         [Description("调用类名")]287         internal string ClassName288         {289             get { return _ClassName; }290             set { _ClassName = value; }291         }292 293         #endregion294 295         #region 调用方法名296 297         private string _Mehtod = string.Empty;298         /// 
299         /// 调用方法名300         /// 
301         [Description("调用方法名")]302         internal string Mehtod303         {304             get { return _Mehtod; }305             set { _Mehtod = value; }306         }307 308         #endregion309 310         #region 生成类型311 312         private string _BornType = string.Empty;313         /// 
314         /// 生成类型315         /// 
316         [Description("生成类型")]317         internal string BornType318         {319             get { return _BornType; }320             set { _BornType = value; }321         }322 323         #endregion324 325         #region 模板名称326 327         private string _TemplateName = string.Empty;328         /// 
329         /// 模板名称330         /// 
331         [Description("模板名称")]332         internal string TemplateName333         {334             get { return _TemplateName; }335             set { _TemplateName = value; }336         }337 338         #endregion339 340         #region 模板开始行索引341 342         private int _BegRow = 0;343         /// 
344         /// 模板开始行索引345         /// 
346         [Description("模板开始行索引")]347         internal int BegRow348         {349             get { return _BegRow; }350             set { _BegRow = value; }351         }352 353         #endregion354 355         #region 模板开始列索引356 357         private int _BegColumn = 0;358         /// 
359         /// 模板开始列索引360         /// 
361         [Description("模板开始列索引")]362         internal int BegColumn363         {364             get { return _BegColumn; }365             set { _BegColumn = value; }366         }367 368         #endregion369 370         #region 每页显示的行数371 372         private int _RowsNumber = 0;373         /// 
374         /// 每页显示的行数375         /// 
376         [Description("每页显示的行数")]377         internal int RowsNumber378         {379             get { return _RowsNumber; }380             set { _RowsNumber = value; }381         }382 383         #endregion384 385         #region 是否存储数据位XML形式386 387         /// 
388         /// 是否存储数据位XML形式389         /// 
390         internal bool IsDataResultsXML391         {392             get393             {394                 bool isDataResultsXML = false;395                 object obj = System.Configuration.ConfigurationSettings.AppSettings["IsDataResultsXML"];396                 try397                 {398                     isDataResultsXML = bool.Parse(obj.ToString());399                 }400                 catch (Exception)401                 {402                     isDataResultsXML = false;403                 }404                 return isDataResultsXML;405             }406         }407 408         #endregion409 410         #endregion411 412     }413 }

导出

1 using System;   2 using System.Collections.Generic;   3 using System.Linq;   4 using System.Text;   5 using System.Web;   6 using System.Xml;   7 using System.Web.UI;   8 using System.ComponentModel;   9 using NPOI.HSSF.UserModel;  10 using System.Data.Common;  11 using System.Data;  12 using System.IO;  13 using System.Reflection;  14 using Ths.Expand.DALProcessing;  15   16 namespace Ths.Expand.ExcelProcessing  17 {  18   19     /// 
  20     /// 生成Excel文件类库  21     /// 不允许继承该类  22     /// 
  23     public sealed class LeadExcel  24     {  25   26         #region Xml路径  27   28         /// 
  29         /// Xml路径  30         /// 
  31         [Description("Xml路径")]  32         private static string XmlPath  33         {  34             get  35             {  36                 return HttpContext.Current.Server.MapPath("~") + "\\ExcelAllocation\\ExcelLeadAllocation.xml";  37             }  38         }  39   40         #endregion  41   42         #region Url路径  43   44         /// 
  45         /// Url路径  46         /// 
  47         [Description("Url路径")]  48         private static string PageUrl = string.Empty;  49   50         #endregion  51   52         #region 将缓存的数据导出到 EXCEL 中  53   54         /// 
  55         /// 将缓存的数据导出到 EXCEL 中  56         /// 
  57         public static void DataToExcel()  58         {  59             ExcelPageUrl excelPageUrl = null;  60   61             #region 无缓存的数据  62   63             if (HttpContext.Current.Request["PageUrl"] == null)  64             {  65                 HttpContext.Current.Response.Write("无法找到缓存的数据，请重新执行查询后，再导出！");  66                 return;  67             }  68   69             PageUrl = HttpContext.Current.Request["PageUrl"].ToString();  70             if (HttpContext.Current.Session[PageUrl] == null)  71             {  72                 System.Web.HttpContext.Current.Response.Write("请检查是否配置了导出Url地址，请配置后，再导出！");  73                 return;  74             }  75   76             excelPageUrl = (ExcelPageUrl)HttpContext.Current.Session[PageUrl];  77             if (excelPageUrl == null)  78             {  79                 System.Web.HttpContext.Current.Response.Write("请检查是否配置了导出相关数据，请配置后，再导出！");  80                 return;  81             }  82   83             ReadExcelLeadAllocation(ref excelPageUrl);  84   85             #endregion  86   87             #region 生成数据类型  88   89             //反射  90             if (excelPageUrl.IsAssembly == "Glint")  91             {  92   93             }  94             //数据结果集  95             else if (excelPageUrl.IsAssembly == "DataSet")  96             {  97                 if (excelPageUrl.DataResults == null)  98                 {  99                     HttpContext.Current.Response.Write("无法找到缓存的数据结果集，请重新执行查询后，再导出！"); 100                     return; 101                 } 102             } 103             //查询 104             else 105             { 106                 if (excelPageUrl.Sql == null || excelPageUrl.Sql == "") 107                 { 108                     HttpContext.Current.Response.Write("请检查是否配置的SQL语句，请配置后，再导出！"); 109                     return; 110                 } 111             } 112  113             #endregion 114  115             #region 读取数据 116  117             if (excelPageUrl.ConnectionStrings == string.Empty) 118             { 119                 HttpContext.Current.Response.Write("请检查是否已配置XML数据，请配置后，再导出！"); 120                 return; 121             } 122  123             DALOperation da = new DALOperation(excelPageUrl.ConnectionStrings); 124             DbParameter[] par = excelPageUrl.Parameters; 125             if (par == null) 126             { 127                 par = new DbParameter[0]; 128             } 129             DataSet dst = null; 130  131             #region 获取数据的方法 132             //反射 133             if (excelPageUrl.IsAssembly == "Glint") 134             { 135                 dst = GetAssemblyGlint(excelPageUrl); 136             } 137             //数据结果集 138             else if (excelPageUrl.IsAssembly == "DataSet") 139             { 140                 dst = excelPageUrl.DataResults; 141             } 142             //查询 143             else 144             { 145                 dst = da.GetDataSet(excelPageUrl.Sql, excelPageUrl.CommandTypeName, par); 146             } 147             #endregion 148  149             DataTable dt = dst.Tables[0].Copy(); 150  151             if (excelPageUrl.ExcelColumnNameList != null) 152             { 153                 for (int i = 0; i < excelPageUrl.ExcelColumnNameList.Rows.Count; i++) 154                 { 155                     if (!bool.Parse(excelPageUrl.ExcelColumnNameList.Rows[i]["IsLead"].ToString())) 156                     { 157                         if (!dt.Columns.Contains(excelPageUrl.ExcelColumnNameList.Rows[i]["OriginalityColumnName"].ToString())) 158                         { 159                             dt.Columns.Remove(excelPageUrl.ExcelColumnNameList.Rows[i]["OriginalityColumnName"].ToString()); 160                         } 161                     } 162                 } 163             } 164  165  166             #endregion 167  168             #region 检查数据 169  170             int count = 0; 171  172             HSSFWorkbook hssfworkbook = null; 173  174             if (excelPageUrl.BornType == "Template") 175             { 176                 if (System.IO.File.Exists(HttpContext.Current.Server.MapPath("~" + "\\ExcelAllocation\\" + excelPageUrl.TemplateName))) 177                 { 178                     FileStream fileTemplateName = new FileStream(HttpContext.Current.Server.MapPath("~" + "\\ExcelAllocation\\" + excelPageUrl.TemplateName), FileMode.Open, FileAccess.Read); 179                     hssfworkbook = new HSSFWorkbook(fileTemplateName); 180                 } 181                 else 182                 { 183                     HttpContext.Current.Response.Write("请检查是否已上传模板文件，请上传后，再导出！"); 184                     return; 185                 } 186             } 187             else 188             { 189                 hssfworkbook = new HSSFWorkbook(); 190             } 191             if (hssfworkbook == null) 192             { 193                 HttpContext.Current.Response.Write("创建对象失败，请联系管理员！"); 194                 return; 195             } 196  197             #endregion 198  199             GenerateExcelData(excelPageUrl, dt, count, hssfworkbook, 0, 0); 200  201             ExcelPic(excelPageUrl, hssfworkbook); 202  203             #region 导出 204  205             if (System.IO.Directory.Exists(System.Web.HttpContext.Current.Request.PhysicalApplicationPath + "\\temp") == false) 206             { 207                 System.IO.Directory.CreateDirectory(System.Web.HttpContext.Current.Request.PhysicalApplicationPath + "\\temp"); 208             } 209             string filename = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + "\\temp\\" + Guid.NewGuid().ToString() + ".xls"; 210             FileStream file = new FileStream(filename, FileMode.Create); 211             hssfworkbook.Write(file); 212             file.Close(); 213             DownLoadFile(filename, excelPageUrl.ExcelName + ".xls"); 214  215             #endregion 216  217         } 218  219         #region 导出图片数据到表格 220  221         /// 
 222         /// 导出图片数据到表格 223         /// 
 224         ///  225         ///  226         private static void ExcelPic(ExcelPageUrl excelPageUrl, HSSFWorkbook hssfworkbook) 227         { 228             if (excelPageUrl.PicPath != string.Empty) 229             { 230                 HSSFSheet sheet = hssfworkbook.CreateSheet("图表"); 231                 int cellIndex = 0; 232                 int rowIndex = 0; 233                 string[] PicPath = excelPageUrl.PicPath.Trim().Split(','); 234                 for (int i = 0; i < PicPath.Length; i++) 235                 { 236                     if (System.IO.File.Exists(PicPath[i])) 237                     { 238                         System.Drawing.Image image = System.Drawing.Image.FromFile(PicPath[i]); 239                         int ImgWidth = image.Width; 240                         int ImgHeight = image.Height; 241                         rowIndex = ImgWidth / 65; 242                         if (i > 0) 243                         { 244                             cellIndex = ImgHeight / 16 + 1; 245                         } 246                         byte[] bytes = System.IO.File.ReadAllBytes(PicPath[i]); 247                         int pictureIdx = hssfworkbook.AddPicture(bytes, HSSFWorkbook.PICTURE_TYPE_PNG); 248                         HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch(); 249                         HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, cellIndex, rowIndex, cellIndex + ImgHeight / 16); 250                         HSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx); 251                         //pict.Resize(); 252                     } 253                 } 254             } 255         } 256  257         #endregion 258  259  260         #region 调用递归生成数据 261  262         /// 
 263         /// 调用递归生成数据 264         /// 
 265         /// 配置参数 266         /// 数据源 267         /// 暂时无用的参数传入0即可 268         /// Excel参数 269         /// 每页的行数 270         /// 工作薄的编号 271         private static void GenerateExcelData(ExcelPageUrl excelPageUrl, DataTable dt, int count, HSSFWorkbook hssfworkbook, int RowsNumber, int Number) 272         { 273  274             #region 创建工作薄 275  276             HSSFSheet sheet = null; 277             if (excelPageUrl.BornType == "Template") 278             { 279                 sheet = hssfworkbook.GetSheet(excelPageUrl.SheetName); 280             } 281             else 282             { 283                 if (Number > 0) 284                 { 285                     sheet = hssfworkbook.GetSheet(excelPageUrl.SheetName + Number); 286                     if (sheet == null) 287                     { 288                         sheet = hssfworkbook.CreateSheet(excelPageUrl.SheetName + Number); 289                     } 290                     else 291                     { 292                         return; 293                     } 294                 } 295                 else 296                 { 297                     sheet = hssfworkbook.CreateSheet(excelPageUrl.SheetName); 298                 } 299             } 300             if (sheet == null) 301             { 302                 HttpContext.Current.Response.Write("请检查模板文件是否已配置工作薄，请配置后，再导出！"); 303                 return; 304             } 305  306             #endregion 307  308             #region 创建多表头样式 309  310             HSSFCellStyle ManystyleHear = hssfworkbook.CreateCellStyle(); 311             ManystyleHear.BorderBottom = HSSFCellStyle.BORDER_THIN; 312             ManystyleHear.BorderLeft = HSSFCellStyle.BORDER_THIN; 313             ManystyleHear.BorderRight = HSSFCellStyle.BORDER_THIN; 314             ManystyleHear.BorderTop = HSSFCellStyle.BORDER_THIN; 315             ManystyleHear.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER; 316             ManystyleHear.Alignment = HSSFCellStyle.ALIGN_CENTER; 317  318  319             ManystyleHear.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_CORNFLOWER_BLUE.index; 320             ManystyleHear.FillPattern = HSSFCellStyle.ALIGN_FILL; 321             ManystyleHear.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_CORNFLOWER_BLUE.index; 322             HSSFFont ManyfontHear = hssfworkbook.CreateFont(); 323             ManyfontHear.Boldweight = HSSFFont.BOLDWEIGHT_BOLD; 324             HSSFRow ManyhrHead = sheet.CreateRow(count); 325             ManyhrHead.Height = 400; 326             { 327                 int ManyHeadNumber = 0; 328  329                 #region 创建多表头 330  331                 if (excelPageUrl.ExcelManyTableHeadcsList != null && excelPageUrl.ExcelManyTableHeadcsList.Rows.Count > 0) 332                 { 333                     int BegColspan = 0; 334                     for (int i = 0; i < excelPageUrl.ExcelManyTableHeadcsList.Rows.Count; i++) 335                     { 336                         if (BegColspan == 0) 337                         { 338                             BegColspan = int.Parse(excelPageUrl.ExcelManyTableHeadcsList.Rows[i]["BegColspan"].ToString()) - 1; 339                         } 340                         else 341                         { 342                             BegColspan = ManyHeadNumber; 343                         } 344  345                         if (ManyHeadNumber == 0) 346                         { 347                             ManyHeadNumber = BegColspan; 348                         } 349  350                         HSSFCell cell = ManyhrHead.CreateCell(BegColspan); 351                         cell.SetCellValue(excelPageUrl.ExcelManyTableHeadcsList.Rows[i]["ColumnName"].ToString()); 352                         sheet.SetColumnWidth(ManyHeadNumber, 20 * 256); 353                         ManystyleHear.SetFont(ManyfontHear); 354                         cell.CellStyle = ManystyleHear; 355  356                         sheet.AddMergedRegion( 357                             new NPOI.HSSF.Util.Region( 358                                int.Parse(excelPageUrl.ExcelManyTableHeadcsList.Rows[i]["BegRowspan"].ToString()) - 1, 359                                BegColspan, 360                                int.Parse(excelPageUrl.ExcelManyTableHeadcsList.Rows[i]["JumpRow"].ToString()) - 1, 361                                ManyHeadNumber + int.Parse(excelPageUrl.ExcelManyTableHeadcsList.Rows[i]["JumpCols"].ToString()) - 1 362                            )); 363  364                         ManyHeadNumber += int.Parse(excelPageUrl.ExcelManyTableHeadcsList.Rows[i]["JumpCols"].ToString()); 365                         BegColspan = ManyHeadNumber + 1; 366                     } 367                     count = count + 1; 368                 } 369  370                 #endregion 371  372             } 373             #endregion 374  375             #region 表头样式 376  377             HSSFCellStyle styleHear = hssfworkbook.CreateCellStyle(); 378             styleHear.BorderBottom = HSSFCellStyle.BORDER_THIN; 379             styleHear.BorderLeft = HSSFCellStyle.BORDER_THIN; 380             styleHear.BorderRight = HSSFCellStyle.BORDER_THIN; 381             styleHear.BorderTop = HSSFCellStyle.BORDER_THIN; 382             styleHear.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER; 383             styleHear.Alignment = HSSFCellStyle.ALIGN_CENTER; 384  385             styleHear.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_CORNFLOWER_BLUE.index; 386             styleHear.FillPattern = HSSFCellStyle.ALIGN_FILL; 387             styleHear.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_CORNFLOWER_BLUE.index; 388             HSSFFont fontHear = hssfworkbook.CreateFont(); 389             fontHear.Boldweight = HSSFFont.BOLDWEIGHT_BOLD; 390             HSSFRow hrHead = sheet.CreateRow(count); 391             hrHead.Height = 400; 392  393             #endregion 394  395             #region 创建表头 396  397             int HeadNumber = 0; 398             if (excelPageUrl.BornType == "Template") 399             { 400                 count = excelPageUrl.BegRow - 1; 401             } 402             else 403             { 404                 #region 根据数据创建表 405  406                 if (excelPageUrl.ExcelColumnNameList != null && excelPageUrl.ExcelColumnNameList.Rows.Count > 0) 407                 { 408                     for (int i = 0; i < excelPageUrl.ExcelColumnNameList.Rows.Count; i++) 409                     { 410                         for (int n = 0; n < dt.Columns.Count; n++) 411                         { 412                             string ColumnName = dt.Columns[n].ColumnName; 413                             if (ColumnName.ToLower() == excelPageUrl.ExcelColumnNameList.Rows[i]["OriginalityColumnName"].ToString().ToLower()) 414                             { 415                                 HSSFCell cell = hrHead.CreateCell(HeadNumber); 416                                 cell.SetCellValue(excelPageUrl.ExcelColumnNameList.Rows[i]["NewColumnName"].ToString()); 417                                 sheet.SetColumnWidth(HeadNumber, 20 * 256); 418                                 styleHear.SetFont(fontHear); 419                                 cell.CellStyle = styleHear; 420  421                                 HeadNumber++; 422                                 break; 423                             } 424                         } 425                     } 426                 } 427                 else 428                 { 429                     for (int n = 0; n < dt.Columns.Count; n++) 430                     { 431                         string ColumnName = dt.Columns[n].ColumnName; 432                         HSSFCell cell = hrHead.CreateCell(HeadNumber); 433                         cell.SetCellValue(ColumnName); 434                         sheet.SetColumnWidth(HeadNumber, 20 * 256); 435                         styleHear.SetFont(fontHear); 436                         cell.CellStyle = styleHear; 437                         HeadNumber++; 438                     } 439                 } 440                 #endregion 441             } 442             #endregion 443  444             #region 是否冻结窗口 445  446             //是否冻结窗口 447             if (excelPageUrl.IsCongelationWindow) 448             { 449                 sheet.CreateFreezePane(0, count + 1, 0, count + 1); 450             } 451  452             #endregion 453  454             #region 数据样式 455  456             HSSFCellStyle style = hssfworkbook.CreateCellStyle(); 457             style.BorderBottom = HSSFCellStyle.BORDER_THIN; 458             style.BorderLeft = HSSFCellStyle.BORDER_THIN; 459             style.BorderRight = HSSFCellStyle.BORDER_THIN; 460             style.BorderTop = HSSFCellStyle.BORDER_THIN; 461             style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER; 462             style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_CORNFLOWER_BLUE.index; 463  464             #endregion 465  466             #region 生成数据 467  468             count = count + 1; 469             if (excelPageUrl.ExcelColumnNameList != null && excelPageUrl.ExcelColumnNameList.Rows.Count > 0) 470             { 471                 #region 根据配置生成数据 472  473                 int num = RowsNumber; 474                 if (num > 0) 475                 { 476                     num++; 477                 } 478                 for (; num < dt.Rows.Count; num++) 479                 { 480                     DataRow dr = dt.Rows[num]; 481  482                     HSSFRow hr = sheet.CreateRow(count); 483                     hr.Height = 400; 484                     if (excelPageUrl.BornType == "Template") 485                     { 486                         HeadNumber = excelPageUrl.BegColumn; 487                     } 488                     else 489                     { 490                         HeadNumber = 0; 491                     } 492  493                     #region 是否有替换数据 494  495                     for (int i = 0; i < excelPageUrl.ExcelColumnNameList.Rows.Count; i++) 496                     { 497                         for (int n = 0; n < dt.Columns.Count; n++) 498                         { 499                             string ColumnName = dt.Columns[n].ColumnName; 500                             if (ColumnName.ToLower() == excelPageUrl.ExcelColumnNameList.Rows[i]["OriginalityColumnName"].ToString().ToLower()) 501                             { 502                                 HSSFCell cell = hr.CreateCell(HeadNumber); 503                                 //是否反射 504                                 if (bool.Parse(excelPageUrl.ExcelColumnNameList.Rows[i]["IsAssembly"].ToString())) 505                                 { 506                                     cell.SetCellValue(GetAssemblyMethod(excelPageUrl.ExcelColumnNameList.Rows[i], dr)); 507                                 } 508                                 else 509                                 { 510                                     //是否格式化 511                                     if (excelPageUrl.ExcelColumnNameList.Rows[i]["DataFormatString"].ToString() != string.Empty) 512                                     { 513                                         cell.SetCellValue(BindDataFormatString(excelPageUrl.ExcelColumnNameList.Rows[i]["DataFormatString"].ToString(), 514                                             dr[excelPageUrl.ExcelColumnNameList.Rows[i]["OriginalityColumnName"].ToString()].ToString())); 515                                     } 516                                     else 517                                     { 518                                         cell.SetCellValue(dr[excelPageUrl.ExcelColumnNameList.Rows[i]["OriginalityColumnName"].ToString()].ToString()); 519                                     } 520                                 } 521                                 //是否单元格格式 522                                 if (excelPageUrl.ExcelColumnNameList.Rows[i]["UnitSpaceType"].ToString() != string.Empty) 523                                 { 524                                     HSSFDataFormat format = hssfworkbook.CreateDataFormat(); 525                                     style.DataFormat = format.GetFormat(excelPageUrl.ExcelColumnNameList.Rows[i]["UnitSpaceType"].ToString()); 526                                 } 527                                 cell.CellStyle = style; 528                                 HeadNumber++; 529                                 break; 530                             } 531                         } 532                     } 533  534                     #endregion 535  536                     count = count + 1; 537  538                     #region 循环调用递归数据 539  540                     if (excelPageUrl.BornType != "Template") 541                     { 542                         if (num - excelPageUrl.RowsNumber > 0) 543                         { 544                             if ((num + 1) % excelPageUrl.RowsNumber == 0 && (num + 1) / excelPageUrl.RowsNumber > 0) 545                             { 546                                 GenerateExcelData(excelPageUrl, dt, 0, hssfworkbook, num, ++Number); 547                                 return; 548                             } 549                         } 550                         else if (num + 1 == excelPageUrl.RowsNumber) 551                         { 552                             GenerateExcelData(excelPageUrl, dt, 0, hssfworkbook, num, ++Number); 553                             return; 554                         } 555                     } 556  557                     #endregion 558  559                 } 560  561                 #endregion 562             } 563             else 564             { 565                 #region 生成所有数据 566  567                 int num = RowsNumber; 568                 if (num > 0) 569                 { 570                     num++; 571                 } 572                 for (; num < dt.Rows.Count; num++) 573                 { 574                     HSSFRow hr = sheet.CreateRow(count); 575                     hr.Height = 400; 576                     if (excelPageUrl.BornType == "Template") 577                     { 578                         HeadNumber = excelPageUrl.BegColumn; 579                     } 580                     else 581                     { 582                         HeadNumber = 0; 583                     } 584                     for (int j = 0; j < dt.Columns.Count; j++) 585                     { 586  587                         HSSFCell cell = hr.CreateCell(HeadNumber); 588                         cell.SetCellValue(dt.Rows[num][j].ToString()); 589                         cell.CellStyle = style; 590                         HeadNumber++; 591                     } 592                     count = count + 1; 593  594                     #region 循环调用递归数据 595  596                     if (excelPageUrl.BornType != "Template") 597                     { 598                         if (num - excelPageUrl.RowsNumber > 0) 599                         { 600                             if ((num + 1) % excelPageUrl.RowsNumber == 0 && (num + 1) / excelPageUrl.RowsNumber > 0) 601                             { 602                                 GenerateExcelData(excelPageUrl, dt, 0, hssfworkbook, num, ++Number); 603                                 return; 604                             } 605                         } 606                         else if (num + 1 == excelPageUrl.RowsNumber) 607                         { 608                             GenerateExcelData(excelPageUrl, dt, 0, hssfworkbook, num, ++Number); 609                             return; 610                         } 611                     } 612  613                     #endregion 614  615                 } 616  617                 #endregion 618             } 619  620             #endregion 621  622             return; 623         } 624  625  626         #endregion 627  628         #endregion 629  630         #region 对齐方式 631  632         /// 
 633         /// 对齐方式 634         /// 
 635         ///  636         ///  637         private static void RefHSSFCellStyleAlignWay(ref  HSSFCellStyle style, string AlignWay) 638         { 639             switch (AlignWay) 640             { 641                 case "RecognizeTacitly": 642                     style.Alignment = HSSFCellStyle.ALIGN_LEFT; 643                     break; 644                 case "ALIGN_LEFT": 645                     style.Alignment = HSSFCellStyle.ALIGN_LEFT; 646                     break; 647                 case "ALIGN_CENTER": 648                     style.Alignment = HSSFCellStyle.ALIGN_CENTER; 649                     break; 650                 case "ALIGN_RIGHT": 651                     style.Alignment = HSSFCellStyle.ALIGN_RIGHT; 652                     break; 653                 case "ALIGN_JUSTIFY": 654                     style.Alignment = HSSFCellStyle.ALIGN_JUSTIFY; 655                     break; 656                 case "ALIGN_FILL": 657                     style.Alignment = HSSFCellStyle.ALIGN_FILL; 658                     break; 659                 default: 660                     style.Alignment = HSSFCellStyle.ALIGN_LEFT; 661                     break; 662             } 663         } 664  665         #endregion 666  667         #region 反射获取数据 668  669         /// 
 670         /// 反射获取数据 671         /// 
 672         ///  673         ///  674         /// 
       675         private static string GetAssemblyMethod(DataRow item, DataRow dr) 676         { 677             string obj1 = dr[item["OriginalityColumnName"].ToString()].ToString(); 678             if (item["AssemblyPath"].ToString() != string.Empty && item["ClassName"].ToString() != string.Empty && item["Mehtod"].ToString() != string.Empty) 679             { 680                 try 681                 { 682  683                     #region 绝对路径 684  685                     if (System.IO.File.Exists(item["AssemblyPath"].ToString())) 686                     { 687                         Assembly ass = Assembly.LoadFile(item["AssemblyPath"].ToString()); 688                         Type ty = ass.GetType(item["ClassName"].ToString()); 689                         object obj = System.Activator.CreateInstance(ty); 690                         MethodInfo mi = ty.GetMethod(item["Mehtod"].ToString()); 691                         obj1 = mi.Invoke(obj, new Object[] { dr[item["OriginalityColumnName"].ToString()].ToString() }).ToString(); 692                     } 693                     #endregion 694  695                     #region 发布后的相对路径 696  697                     else if (System.IO.File.Exists(HttpContext.Current.Server.MapPath("~") + "\\" + item["AssemblyPath"].ToString())) 698                     { 699                         Assembly ass = Assembly.LoadFile(HttpContext.Current.Server.MapPath("~") + "\\" + item["AssemblyPath"].ToString()); 700                         Type ty = ass.GetType(item["ClassName"].ToString()); 701                         object obj = System.Activator.CreateInstance(ty); 702                         MethodInfo mi = ty.GetMethod(item["Mehtod"].ToString()); 703                         obj1 = mi.Invoke(obj, new Object[] { dr[item["OriginalityColumnName"].ToString()].ToString() }).ToString(); 704                     } 705  706                     #endregion 707  708                     #region 程序员相对路径 709  710                     else if (System.IO.File.Exists(HttpContext.Current.Server.MapPath("~") + "\\Bin\\" + item["AssemblyPath"].ToString())) 711                     { 712                         Assembly ass = Assembly.LoadFile(HttpContext.Current.Server.MapPath("~") + "\\Bin\\" + item["AssemblyPath"].ToString()); 713                         Type ty = ass.GetType(item["ClassName"].ToString()); 714                         object obj = System.Activator.CreateInstance(ty); 715                         MethodInfo mi = ty.GetMethod(item["Mehtod"].ToString()); 716                         obj1 = mi.Invoke(obj, new Object[] { dr[item["OriginalityColumnName"].ToString()].ToString() }).ToString(); 717                     } 718  719                     #endregion 720  721                 } 722                 catch (Exception) 723                 { 724                 } 725             } 726             return obj1; 727         } 728  729         #endregion 730  731         #region 格式化数据 732  733         /// 
 734         /// 格式化数据 735         /// 
 736         /// 格式化类型 737         /// 格式化数据 738         /// 
       739         private static string BindDataFormatString(string FormatString, string FormatValue) 740         { 741             string ResultString = ""; 742  743             #region 格式化日期 744  745             if (FormatString.ToLower().IndexOf("y") > -1 || 746                    FormatString.ToLower().IndexOf("m") > -1 || 747                    FormatString.ToLower().IndexOf("d") > -1 || 748                    FormatString.ToLower().IndexOf("h") > -1 || 749                    FormatString.ToLower().IndexOf("s") > -1) 750             { 751                 try 752                 { 753                     ResultString = DateTime.Parse(FormatValue).ToString(FormatString); 754                 } 755                 catch (Exception) 756                 { 757  758                     ResultString = FormatValue; 759                 } 760             } 761  762             #endregion 763  764             #region 格式化为自动小数位 765  766             else if (FormatString.IndexOf("fAuto") > -1) 767             { 768                 if (FormatValue != "") 769                 { 770                     string str = FormatValue; 771                     string newstr = str; 772                     int nx = str.IndexOf("."); 773                     if (nx > -1) 774                     { 775                         for (int n = nx; n < str.Length; n++) 776                         { 777                             if (str[n] != '0') 778                             { 779                                 nx = n; 780                             } 781                         } 782                         newstr = str.Substring(0, nx + 1); 783                         if (newstr.Substring(newstr.Length - 1, 1) == ".") 784                         { 785                             newstr = newstr.Substring(0, newstr.Length - 1); 786                         } 787                         ResultString = newstr; 788                     } 789                     else 790                     { 791                         ResultString = FormatValue; 792                     } 793                 } 794             } 795  796             #endregion 797  798             #region 格式化小数 799  800             else if (FormatString.ToLower().IndexOf("f") > -1) 801             { 802                 try 803                 { 804                     ResultString = decimal.Parse(FormatValue).ToString(FormatString); 805                 } 806                 catch (Exception) 807                 { 808                     ResultString = FormatValue; 809                 } 810             } 811  812             #endregion 813  814             else 815             { 816                 ResultString = FormatValue; 817             } 818  819             return ResultString; 820         } 821  822         #endregion 823  824         #region 读取配置文件信息 825  826         #region 读取配置 827  828         /// 
 829         /// 读取配置文件信息 830         /// 
 831         /// 
       832         private static void ReadExcelLeadAllocation(ref ExcelPageUrl excelPageUrl) 833         { 834             XmlDocument xmldoc = new XmlDocument(); 835             if (System.IO.File.Exists(XmlPath)) 836             { 837                 xmldoc.Load(XmlPath); 838  839  840                 for (int i = 0; i < xmldoc.DocumentElement.ChildNodes.Count; i++) 841                 { 842                     if (xmldoc.DocumentElement.ChildNodes[i].OuterXml.IndexOf("
      ") > 0) 843                     { 844                         continue; 845                     } 846                     XmlElement birthday = (XmlElement)xmldoc.DocumentElement.ChildNodes[i]; 847  848                     if (birthday.Name == "ExcelPageUrl") 849                     { 850  851                         #region 判断是否有配置 852  853                         if (birthday.Attributes["PageUrl"].Value == PageUrl) 854                         { 855  856                             #region 读取导出页面基本属性 857  858                             //工作薄名称 859                             excelPageUrl.SheetName = birthday.Attributes["SheetName"].Value; 860  861                             //Excel名称 862                             excelPageUrl.ExcelName = birthday.Attributes["ExcelName"].Value; 863  864                             //连接字符串 865                             excelPageUrl.ConnectionStrings = birthday.Attributes["ConnectionStrings"] == null ? "ConnectionString" : 866                                 (birthday.Attributes["ConnectionStrings"].Value == string.Empty ? "ConnectionString" : birthday.Attributes["ConnectionStrings"].Value); 867  868                             //是否冻结窗口 869                             excelPageUrl.IsCongelationWindow = birthday.Attributes["IsCongelationWindow"] == null ? false : 870                                 (birthday.Attributes["IsCongelationWindow"].Value == string.Empty ? false : bool.Parse(birthday.Attributes["IsCongelationWindow"].Value)); 871  872                             //是否调用dll方法 873                             excelPageUrl.IsAssembly = birthday.Attributes["IsAssembly"] == null ? "Search" : 874                                 (birthday.Attributes["IsAssembly"].Value == string.Empty ? "Search" : birthday.Attributes["IsAssembly"].Value); 875  876                             //调用dll地址 877                             excelPageUrl.AssemblyPath = birthday.Attributes["AssemblyPath"] == null ? "" : 878                                 (birthday.Attributes["AssemblyPath"].Value == string.Empty ? "" : birthday.Attributes["AssemblyPath"].Value); 879  880                             //调用类名 881                             excelPageUrl.ClassName = birthday.Attributes["ClassName"] == null ? "" : 882                                 (birthday.Attributes["ClassName"].Value == string.Empty ? "" : birthday.Attributes["ClassName"].Value); 883  884                             //调用方法名 885                             excelPageUrl.Mehtod = birthday.Attributes["Mehtod"] == null ? "" : 886                                 (birthday.Attributes["Mehtod"].Value == string.Empty ? "" : birthday.Attributes["Mehtod"].Value); 887  888                             //生成类型 889                             excelPageUrl.BornType = birthday.Attributes["BornType"] == null ? "Afresh" : 890                                 (birthday.Attributes["BornType"].Value == string.Empty ? "Afresh" : birthday.Attributes["BornType"].Value); 891  892                             //模板名称 893                             excelPageUrl.TemplateName = birthday.Attributes["TemplateName"] == null ? "" : 894                                 (birthday.Attributes["TemplateName"].Value == string.Empty ? "" : birthday.Attributes["TemplateName"].Value); 895  896                             //模板开始行索引 897                             excelPageUrl.BegRow = birthday.Attributes["BegRow"] == null ? 0 : 898                                 (birthday.Attributes["BegRow"].Value == string.Empty ? 0 : int.Parse(birthday.Attributes["BegRow"].Value)); 899  900                             //模板开始列索引 901                             excelPageUrl.BegColumn = birthday.Attributes["BegColumn"] == null ? 0 : 902                                 (birthday.Attributes["BegColumn"].Value == string.Empty ? 0 : int.Parse(birthday.Attributes["BegColumn"].Value)); 903  904                             //每页显示的行数 905                             excelPageUrl.RowsNumber = birthday.Attributes["RowsNumber"] == null ? 0 : 906                                 (birthday.Attributes["RowsNumber"].Value == string.Empty ? 50000 : int.Parse(birthday.Attributes["RowsNumber"].Value)); 907  908                             #endregion 909  910                             #region 循环 911  912                             foreach (XmlElement xmlColumnHead in birthday.ChildNodes) 913                             { 914                                 if (xmlColumnHead.Name == "ExcelManyTableHeadList") 915                                 { 916                                     excelPageUrl.ExcelManyTableHeadcsList = ReadXmlElementByManyTableHeadList(xmlColumnHead.ChildNodes); 917                                 } 918                                 if (xmlColumnHead.Name == "ExcelColumnNameList") 919                                 { 920                                     excelPageUrl.ExcelColumnNameList = ReadXmlElementByExcelColumnName(xmlColumnHead.ChildNodes); 921                                 } 922                             } 923  924                             #endregion 925  926                         } 927  928                         #endregion 929  930                     } 931                 } 932             } 933         } 934  935         #endregion 936  937         #region 获取多表头数据 2011-07-29 杨海峰 938  939         /// 
 940         /// 获取多表头数据 2011-07-29 杨海峰 941         /// 
 942         ///  943         /// 
       944         private static DataTable ReadXmlElementByManyTableHeadList(XmlNodeList xmlNodeList) 945         { 946  947             #region 创建表 948  949             DataTable dtExcelColumnName = new DataTable(); 950             DataColumn dc1 = new DataColumn("BegColspan", typeof(int)); 951             DataColumn dc2 = new DataColumn("BegRowspan", typeof(int)); 952             DataColumn dc3 = new DataColumn("ColumnName", typeof(string)); 953             DataColumn dc4 = new DataColumn("Index", typeof(int)); 954             DataColumn dc5 = new DataColumn("JumpCols", typeof(int)); 955             DataColumn dc6 = new DataColumn("JumpRow", typeof(int)); 956  957             dtExcelColumnName.Columns.Add(dc1); 958             dtExcelColumnName.Columns.Add(dc2); 959             dtExcelColumnName.Columns.Add(dc3); 960             dtExcelColumnName.Columns.Add(dc4); 961             dtExcelColumnName.Columns.Add(dc5); 962             dtExcelColumnName.Columns.Add(dc6); 963  964             #endregion 965  966             for (int i = 0; i < xmlNodeList.Count; i++) 967             { 968                 if (xmlNodeList[i].OuterXml.IndexOf("
      ") > 0) 969                 { 970                     continue; 971                 } 972                 XmlElement xmlExcelManyTableHeadcList = (XmlElement)xmlNodeList[i]; 973  974                 DataRow rowExcelColumnName = dtExcelColumnName.NewRow(); 975                 rowExcelColumnName["BegColspan"] = xmlExcelManyTableHeadcList.Attributes["BegColspan"] == null ? 1 : int.Parse(xmlExcelManyTableHeadcList.Attributes["BegColspan"].Value); 976                 rowExcelColumnName["BegRowspan"] = xmlExcelManyTableHeadcList.Attributes["BegRowspan"] == null ? 1 : int.Parse(xmlExcelManyTableHeadcList.Attributes["BegRowspan"].Value); 977                 rowExcelColumnName["ColumnName"] = xmlExcelManyTableHeadcList.Attributes["ColumnName"] == null ? "" : xmlExcelManyTableHeadcList.Attributes["ColumnName"].Value; 978                 rowExcelColumnName["Index"] = xmlExcelManyTableHeadcList.Attributes["Index"] == null ? 1 : int.Parse(xmlExcelManyTableHeadcList.Attributes["Index"].Value); 979                 rowExcelColumnName["JumpCols"] = xmlExcelManyTableHeadcList.Attributes["JumpCols"] == null ? 1 : int.Parse(xmlExcelManyTableHeadcList.Attributes["JumpCols"].Value); 980                 rowExcelColumnName["JumpRow"] = xmlExcelManyTableHeadcList.Attributes["JumpRow"] == null ? 1 : int.Parse(xmlExcelManyTableHeadcList.Attributes["JumpRow"].Value); 981                 dtExcelColumnName.Rows.Add(rowExcelColumnName); 982             } 983  984             DataView dv = dtExcelColumnName.DefaultView; 985             dv.Sort = "Index"; 986             return dv.ToTable(); 987         } 988  989         #endregion 990  991         #region 获取导出的数据 2011-07-29 杨海峰 992  993         /// 
 994         /// 获取导出的数据 2011-07-29 杨海峰 995         /// 
 996         ///  997         /// 
       998         private static DataTable ReadXmlElementByExcelColumnName(XmlNodeList xmlNodeList) 999         {1000             #region 创建表1001 1002             DataTable dtExcelColumnName = new DataTable();1003             DataColumn dc1 = new DataColumn("OriginalityColumnName", typeof(string));//原来的列名1004             DataColumn dc2 = new DataColumn("NewColumnName", typeof(string));//新列名用于导出1005             DataColumn dc3 = new DataColumn("DataFormatString", typeof(string));//格式化1006             DataColumn dc4 = new DataColumn("LeadIndex", typeof(int));//导出顺序1007             DataColumn dc5 = new DataColumn("IsLead", typeof(string));//是否导出1008             DataColumn dc6 = new DataColumn("AssemblyPath", typeof(string));//反射地址1009             DataColumn dc7 = new DataColumn("ClassName", typeof(string));//反射类名1010             DataColumn dc8 = new DataColumn("Mehtod", typeof(string));//反射方法1011             DataColumn dc9 = new DataColumn("IsAssembly", typeof(string));//是否启用反射1012             DataColumn dc10 = new DataColumn("UnitSpaceType", typeof(string));//单元格格式化1013             DataColumn dc11 = new DataColumn("AlignWay", typeof(string));//对齐1014             DataColumn dc12 = new DataColumn("IsWrapText", typeof(string));//自动换行1015             DataColumn dc13 = new DataColumn("RevolveAngle", typeof(string));//文本旋转1016 1017             dtExcelColumnName.Columns.Add(dc1);1018             dtExcelColumnName.Columns.Add(dc2);1019             dtExcelColumnName.Columns.Add(dc3);1020             dtExcelColumnName.Columns.Add(dc4);1021             dtExcelColumnName.Columns.Add(dc5);1022             dtExcelColumnName.Columns.Add(dc6);1023             dtExcelColumnName.Columns.Add(dc7);1024             dtExcelColumnName.Columns.Add(dc8);1025             dtExcelColumnName.Columns.Add(dc9);1026             dtExcelColumnName.Columns.Add(dc10);1027             dtExcelColumnName.Columns.Add(dc11);1028             dtExcelColumnName.Columns.Add(dc12);1029             dtExcelColumnName.Columns.Add(dc13);1030 1031             #endregion1032 1033             #region 获取数据1034 1035             for (int i = 0; i < xmlNodeList.Count; i++)1036             {1037                 if (xmlNodeList[i].OuterXml.IndexOf("
      ") > 0)1038                 {1039                     continue;1040                 }1041                 XmlElement xmlExcelColumnName = (XmlElement)xmlNodeList[i];1042 1043                 #region MyRegion1044                 DataRow rowExcelColumnName = dtExcelColumnName.NewRow();1045 1046                 //读取格式化1047                 rowExcelColumnName["DataFormatString"] = xmlExcelColumnName.Attributes["DataFormatString"] == null ? "" : xmlExcelColumnName.Attributes["DataFormatString"].Value;1048                 //读取是否导出1049                 rowExcelColumnName["IsLead"] = xmlExcelColumnName.Attributes["IsLead"] == null ? true : Boolean.Parse(xmlExcelColumnName.Attributes["IsLead"].Value);1050                 //读取导出顺序1051                 rowExcelColumnName["LeadIndex"] = xmlExcelColumnName.Attributes["LeadIndex"] == null ? 0 : int.Parse(xmlExcelColumnName.Attributes["LeadIndex"].Value);1052                 //读取新列名1053                 rowExcelColumnName["NewColumnName"] = xmlExcelColumnName.Attributes["NewColumnName"] == null ? "" : xmlExcelColumnName.Attributes["NewColumnName"].Value;1054                 //读取原始列名1055                 rowExcelColumnName["OriginalityColumnName"] = xmlExcelColumnName.Attributes["OriginalityColumnName"] == null ? "" : xmlExcelColumnName.Attributes["OriginalityColumnName"].Value;1056                 //读取动态 DLL 地址1057                 rowExcelColumnName["AssemblyPath"] = xmlExcelColumnName.Attributes["AssemblyPath"] == null ? "" : xmlExcelColumnName.Attributes["AssemblyPath"].Value;1058                 //读取动态 类 地址1059                 rowExcelColumnName["ClassName"] = xmlExcelColumnName.Attributes["ClassName"] == null ? "" : xmlExcelColumnName.Attributes["ClassName"].Value;1060                 //读取动态 方法 地址1061                 rowExcelColumnName["Mehtod"] = xmlExcelColumnName.Attributes["Mehtod"] == null ? "" : xmlExcelColumnName.Attributes["Mehtod"].Value;1062                 //读取是否调用 动态DLL 的方法1063                 rowExcelColumnName["IsAssembly"] = xmlExcelColumnName.Attributes["IsAssembly"] == null ? false : Boolean.Parse(xmlExcelColumnName.Attributes["IsAssembly"].Value);1064                 //读取单元格类型1065                 rowExcelColumnName["UnitSpaceType"] = xmlExcelColumnName.Attributes["UnitSpaceType"] == null ? "" : xmlExcelColumnName.Attributes["UnitSpaceType"].Value;1066                 //对齐1067                 rowExcelColumnName["AlignWay"] = xmlExcelColumnName.Attributes["AlignWay"] == null ? "RecognizeTacitly" : xmlExcelColumnName.Attributes["AlignWay"].Value;1068                 //是否自动换行1069                 rowExcelColumnName["IsWrapText"] = xmlExcelColumnName.Attributes["IsWrapText"] == null ? "false" : xmlExcelColumnName.Attributes["IsWrapText"].Value;1070                 //文本旋转1071                 rowExcelColumnName["RevolveAngle"] = xmlExcelColumnName.Attributes["RevolveAngle"] == null ? "RecognizeTacitly" : xmlExcelColumnName.Attributes["RevolveAngle"].Value;1072 1073                 dtExcelColumnName.Rows.Add(rowExcelColumnName);1074                 #endregion1075 1076             }1077 1078             #endregion1079 1080             DataView dv = dtExcelColumnName.DefaultView;1081             dv.Sort = "LeadIndex";1082             return dv.ToTable();1083         }1084 1085         #endregion1086 1087         #endregion1088 1089         #region 下载文件1090 1091         /// 
1092         /// 下载文件1093         /// 
1094         /// 文件路径1095         /// 导出名称1096         private static void DownLoadFile(string path, string ExcelName)1097         {1098             HttpContext.Current.Response.ContentType = "application/ms-download";1099             FileInfo file = new FileInfo(path);1100             if (file.Exists)1101             {1102                 HttpContext.Current.Response.Clear();1103                 HttpContext.Current.Response.AddHeader("Content-Type", "application/octet-stream");1104                 HttpContext.Current.Response.Charset = "utf-8";1105                 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(ExcelName, System.Text.Encoding.UTF8));1106                 HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());1107                 HttpContext.Current.Response.WriteFile(file.FullName);1108                 HttpContext.Current.Response.Flush();1109                 HttpContext.Current.Response.Clear();1110                 HttpContext.Current.Response.End();1111             }1112             else1113             {1114                 HttpContext.Current.Response.Write("无法生成数据，请重新执行查询后，再导出！");1115                 return;1116             }1117         }1118 1119         #endregion1120 1121         #region 反射获取数据1122 1123         /// 
1124         /// 反射获取数据1125         /// 
1126         /// 1127         /// 
      1128         private static DataSet GetAssemblyGlint(ExcelPageUrl excelPageUrl)1129         {1130             DataSet set = new DataSet();1131             try1132             {1133 1134                 #region 绝对路径1135 1136                 if (System.IO.File.Exists(excelPageUrl.AssemblyPath))1137                 {1138                     Assembly ass = Assembly.LoadFile(excelPageUrl.AssemblyPath);1139                     Type ty = ass.GetType(excelPageUrl.ClassName);1140                     object obj = System.Activator.CreateInstance(ty);1141                     MethodInfo mi = ty.GetMethod(excelPageUrl.Mehtod);1142                     object objDataTable = mi.Invoke(obj, excelPageUrl.AssemblyList);1143                     if (objDataTable is DataTable)1144                     {1145                         set.Tables.Add(((DataTable)objDataTable).Copy());1146                     }1147                     else if (objDataTable is DataSet)1148                     {1149                         set = objDataTable as DataSet;1150                     }1151                 }1152                 #endregion1153 1154                 #region 发布后的相对路径1155 1156                 else if (System.IO.File.Exists(HttpContext.Current.Server.MapPath("~") + "\\" + excelPageUrl.AssemblyPath))1157                 {1158                     Assembly ass = Assembly.LoadFile(HttpContext.Current.Server.MapPath("~") + "\\" + excelPageUrl.AssemblyPath);1159                     Type ty = ass.GetType(excelPageUrl.ClassName);1160                     object obj = System.Activator.CreateInstance(ty);1161                     MethodInfo mi = ty.GetMethod(excelPageUrl.Mehtod);1162                     object objDataTable = mi.Invoke(obj, excelPageUrl.AssemblyList);1163                     if (objDataTable is DataTable)1164                     {1165                         set.Tables.Add(((DataTable)objDataTable).Copy());1166                     }1167                     else if (objDataTable is DataSet)1168                     {1169                         set = objDataTable as DataSet;1170                     }1171                 }1172 1173                 #endregion1174 1175                 #region 程序员相对路径1176 1177                 else if (System.IO.File.Exists(HttpContext.Current.Server.MapPath("~") + "\\Bin\\" + excelPageUrl.AssemblyPath))1178                 {1179                     Assembly ass = Assembly.LoadFile(HttpContext.Current.Server.MapPath("~") + "\\Bin\\" + excelPageUrl.AssemblyPath);1180                     Type ty = ass.GetType(excelPageUrl.ClassName);1181                     object obj = System.Activator.CreateInstance(ty);1182                     MethodInfo mi = ty.GetMethod(excelPageUrl.Mehtod);1183                     object objDataTable = mi.Invoke(obj, excelPageUrl.AssemblyList);1184                     if (objDataTable is DataTable)1185                     {1186                         set.Tables.Add(((DataTable)objDataTable).Copy());1187                     }1188                     else if (objDataTable is DataSet)1189                     {1190                         set = objDataTable as DataSet;1191                     }1192                 }1193 1194                 #endregion1195 1196             }1197             catch (Exception)1198             {1199             }1200             return set;1201         }1202 1203         #endregion1204 1205     }1206 1207 }

配置文件如

1 
       2 
      
        3   
       
         4     
        
          5       
          6       
          7       
          8       
          9       
         10       
         11       
         12       
         13       
         14       
         15     
        16   
       17   
       
        18     
        
         19       
         20       
         21       
         22       
         23       
         24       
         25       
         26       
         27     
        28   
       29 
      

调用模式配置

1 Ths.Expand.ExcelProcessing.ExcelPageUrl _excel = new Ths.Expand.ExcelProcessing.ExcelPageUrl();2             _excel.Sql = String.Format("select * from dbo.NGIDepartment");3             _excel.DataResults = this.DBFactory.GetDataSet("select * from dbo.NGIDepartment");4             _excel.PicPath = @"D:\公司项目\山西项目\DataCenterAnalysis\datacenteranalysis.web\TempImage\1201311043454964445.png,D:\公司项目\山西项目\DataCenterAnalysis\datacenteranalysis.web\TempImage\1010311713382187526.png";5             System.Web.HttpContext.Current.Session[System.IO.Path.GetFileName(System.Web.HttpContext.Current.Request["Url"].ToString())] = _excel;

调用页面调用方式

οnclick="window.open('../_framework/ToExcel.aspx?pageUrl=当前页面Url地址')"

1 Ths.Expand.ExcelProcessing.LeadExcel.DataToExcel();