C#利用NPOI导出Excel
第一篇文章
View Code
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 /// <summary> 14 /// Excel 操作类 杨海峰 2011-07-27 15 /// 不允许继承该类 16 /// </summary> 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 /// <summary> 29 /// 导出EXCLE时用到的缓存SQL语句 30 /// </summary> 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 /// <summary> 44 /// 缓存记录的参数 45 /// </summary> 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 /// <summary> 59 /// 记录导出的脚本类型 60 /// </summary> 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 /// <summary> 74 /// 数据结果集 可以在配置文件中设置 IsDataResultsXML 为是否生成XML数据源 75 /// </summary> 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 else 103 { 104 HttpContext.Current.Session["ExcelPageUrlDataResultsXML"] = value; 105 } 106 } 107 } 108 109 #endregion 110 111 #region 导出图片文件路径 112 113 private string _PicPath = string.Empty; 114 /// <summary> 115 /// 导出图片文件路径 116 /// </summary> 117 [Description("导出图片文件路径")] 118 public string PicPath 119 { 120 get { return _PicPath; } 121 set { _PicPath = value; } 122 } 123 124 #endregion 125 126 #region 反射参数列表 127 128 private object[] _AssemblyList = null; 129 /// <summary> 130 /// 反射参数列表 131 /// </summary> 132 [Description("反射参数列表")] 133 public object[] AssemblyList 134 { 135 get { return _AssemblyList; } 136 set { _AssemblyList = value; } 137 } 138 139 #endregion 140 141 #endregion 142 143 #region 程序集属性internal 144 145 #region Url 地址 146 147 private string _PageUrl = string.Empty; 148 /// <summary> 149 /// Url 地址 150 /// </summary> 151 [Description("Url 地址")] 152 internal string PageUrl 153 { 154 get { return _PageUrl; } 155 set { _PageUrl = value; } 156 } 157 158 #endregion 159 160 #region 工作薄名称 161 162 private string _SheetName = "数据"; 163 /// <summary> 164 /// 工作薄名称 165 /// </summary> 166 [Description("工作薄名称")] 167 internal string SheetName 168 { 169 get { return _SheetName; } 170 set { _SheetName = value; } 171 } 172 173 #endregion 174 175 #region 导出表名 176 177 private string _ExcelName = "数据列表"; 178 /// <summary> 179 /// 导出表名 180 /// </summary> 181 [Description("导出表名")] 182 internal string ExcelName 183 { 184 get { return _ExcelName; } 185 set { _ExcelName = value; } 186 } 187 188 #endregion 189 190 #region 导出字段配置类列表 191 192 private DataTable _ExcelColumnNameList = null; 193 /// <summary> 194 /// 导出字段配置类列表 195 /// </summary> 196 [Description("导出字段配置类列表")] 197 internal DataTable ExcelColumnNameList 198 { 199 get { return _ExcelColumnNameList; } 200 set { _ExcelColumnNameList = value; } 201 } 202 203 #endregion 204 205 #region Excel 多表头配置类列表 206 207 private DataTable _ExcelManyTableHeadcsList = null; 208 /// <summary> 209 /// Excel 多表头配置类列表 210 /// </summary> 211 [Description("Excel 多表头配置类列表")] 212 internal DataTable ExcelManyTableHeadcsList 213 { 214 get { return _ExcelManyTableHeadcsList; } 215 set { _ExcelManyTableHeadcsList = value; } 216 } 217 218 #endregion 219 220 #region 连接字符串 221 222 private string _ConnectionStrings = string.Empty; 223 /// <summary> 224 /// 连接字符串 225 /// </summary> 226 [Description("连接字符串")] 227 internal string ConnectionStrings 228 { 229 get { return _ConnectionStrings; } 230 set { _ConnectionStrings = value; } 231 } 232 233 #endregion 234 235 #region 是否冻结窗口 236 237 private bool _IsCongelationWindow = false; 238 /// <summary> 239 /// 是否冻结窗口 240 /// </summary> 241 [Description("是否冻结窗口")] 242 internal bool IsCongelationWindow 243 { 244 get { return _IsCongelationWindow; } 245 set { _IsCongelationWindow = value; } 246 } 247 248 #endregion 249 250 #region 是否调用dll方法 251 252 private string _IsAssembly = string.Empty; 253 /// <summary> 254 /// 是否调用dll方法 255 /// </summary> 256 [Description("是否调用dll方法")] 257 internal string IsAssembly 258 { 259 get { return _IsAssembly; } 260 set { _IsAssembly = value; } 261 } 262 263 #endregion 264 265 #region 调用dll地址 266 267 private string _AssemblyPath = string.Empty; 268 /// <summary> 269 /// 调用dll地址 270 /// </summary> 271 [Description("调用dll地址")] 272 internal string AssemblyPath 273 { 274 get { return _AssemblyPath; } 275 set { _AssemblyPath = value; } 276 } 277 278 #endregion 279 280 #region 调用类名 281 282 private string _ClassName = string.Empty; 283 /// <summary> 284 /// 调用类名 285 /// </summary> 286 [Description("调用类名")] 287 internal string ClassName 288 { 289 get { return _ClassName; } 290 set { _ClassName = value; } 291 } 292 293 #endregion 294 295 #region 调用方法名 296 297 private string _Mehtod = string.Empty; 298 /// <summary> 299 /// 调用方法名 300 /// </summary> 301 [Description("调用方法名")] 302 internal string Mehtod 303 { 304 get { return _Mehtod; } 305 set { _Mehtod = value; } 306 } 307 308 #endregion 309 310 #region 生成类型 311 312 private string _BornType = string.Empty; 313 /// <summary> 314 /// 生成类型 315 /// </summary> 316 [Description("生成类型")] 317 internal string BornType 318 { 319 get { return _BornType; } 320 set { _BornType = value; } 321 } 322 323 #endregion 324 325 #region 模板名称 326 327 private string _TemplateName = string.Empty; 328 /// <summary> 329 /// 模板名称 330 /// </summary> 331 [Description("模板名称")] 332 internal string TemplateName 333 { 334 get { return _TemplateName; } 335 set { _TemplateName = value; } 336 } 337 338 #endregion 339 340 #region 模板开始行索引 341 342 private int _BegRow = 0; 343 /// <summary> 344 /// 模板开始行索引 345 /// </summary> 346 [Description("模板开始行索引")] 347 internal int BegRow 348 { 349 get { return _BegRow; } 350 set { _BegRow = value; } 351 } 352 353 #endregion 354 355 #region 模板开始列索引 356 357 private int _BegColumn = 0; 358 /// <summary> 359 /// 模板开始列索引 360 /// </summary> 361 [Description("模板开始列索引")] 362 internal int BegColumn 363 { 364 get { return _BegColumn; } 365 set { _BegColumn = value; } 366 } 367 368 #endregion 369 370 #region 每页显示的行数 371 372 private int _RowsNumber = 0; 373 /// <summary> 374 /// 每页显示的行数 375 /// </summary> 376 [Description("每页显示的行数")] 377 internal int RowsNumber 378 { 379 get { return _RowsNumber; } 380 set { _RowsNumber = value; } 381 } 382 383 #endregion 384 385 #region 是否存储数据位XML形式 386 387 /// <summary> 388 /// 是否存储数据位XML形式 389 /// </summary> 390 internal bool IsDataResultsXML 391 { 392 get 393 { 394 bool isDataResultsXML = false; 395 object obj = System.Configuration.ConfigurationSettings.AppSettings["IsDataResultsXML"]; 396 try 397 { 398 isDataResultsXML = bool.Parse(obj.ToString()); 399 } 400 catch (Exception) 401 { 402 isDataResultsXML = false; 403 } 404 return isDataResultsXML; 405 } 406 } 407 408 #endregion 409 410 #endregion 411 412 } 413 }
导出
View Code
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 /// <summary> 20 /// 生成Excel文件类库 21 /// 不允许继承该类 22 /// </summary> 23 public sealed class LeadExcel 24 { 25 26 #region Xml路径 27 28 /// <summary> 29 /// Xml路径 30 /// </summary> 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 /// <summary> 45 /// Url路径 46 /// </summary> 47 [Description("Url路径")] 48 private static string PageUrl = string.Empty; 49 50 #endregion 51 52 #region 将缓存的数据导出到 EXCEL 中 53 54 /// <summary> 55 /// 将缓存的数据导出到 EXCEL 中 56 /// </summary> 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("<font color='Red'>无法找到缓存的数据，请重新执行查询后，再导出！</font>"); 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("<font color='Red'>请检查是否配置了导出Url地址，请配置后，再导出！</font>"); 73 return; 74 } 75 76 excelPageUrl = (ExcelPageUrl)HttpContext.Current.Session[PageUrl]; 77 if (excelPageUrl == null) 78 { 79 System.Web.HttpContext.Current.Response.Write("<font color='Red'>请检查是否配置了导出相关数据，请配置后，再导出！</font>"); 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("<font color='Red'>无法找到缓存的数据结果集，请重新执行查询后，再导出！</font>"); 100 return; 101 } 102 } 103 //查询 104 else 105 { 106 if (excelPageUrl.Sql == null || excelPageUrl.Sql == "") 107 { 108 HttpContext.Current.Response.Write("<font color='Red'>请检查是否配置的SQL语句，请配置后，再导出！</font>"); 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("<font color='Red'>请检查是否已上传模板文件，请上传后，再导出！</font>"); 184 return; 185 } 186 } 187 else 188 { 189 hssfworkbook = new HSSFWorkbook(); 190 } 191 if (hssfworkbook == null) 192 { 193 HttpContext.Current.Response.Write("<font color='Red'>创建对象失败，请联系管理员！</font>"); 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 /// <summary> 222 /// 导出图片数据到表格 223 /// </summary> 224 /// <param name="excelPageUrl"></param> 225 /// <param name="hssfworkbook"></param> 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 /// <summary> 263 /// 调用递归生成数据 264 /// </summary> 265 /// <param name="excelPageUrl">配置参数</param> 266 /// <param name="dt">数据源</param> 267 /// <param name="count">暂时无用的参数传入0即可</param> 268 /// <param name="hssfworkbook">Excel参数</param> 269 /// <param name="RowsNumber">每页的行数</param> 270 /// <param name="Number">工作薄的编号</param> 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("<font color='Red'>请检查模板文件是否已配置工作薄，请配置后，再导出！</font>"); 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 /// <summary> 633 /// 对齐方式 634 /// </summary> 635 /// <param name="style"></param> 636 /// <param name="AlignWay"></param> 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 /// <summary> 670 /// 反射获取数据 671 /// </summary> 672 /// <param name="item"></param> 673 /// <param name="dr"></param> 674 /// <returns></returns> 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 /// <summary> 734 /// 格式化数据 735 /// </summary> 736 /// <param name="FormatString">格式化类型</param> 737 /// <param name="FormatValue">格式化数据</param> 738 /// <returns></returns> 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 /// <summary> 829 /// 读取配置文件信息 830 /// </summary> 831 /// <returns></returns> 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 && 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 /// <summary> 940 /// 获取多表头数据 2011-07-29 杨海峰 941 /// </summary> 942 /// <param name="xmlNodeList"></param> 943 /// <returns></returns> 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 && 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 /// <summary> 994 /// 获取导出的数据 2011-07-29 杨海峰 995 /// </summary> 996 /// <param name="xmlNodeList"></param> 997 /// <returns></returns> 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 #endregion 1032 1033 #region 获取数据 1034 1035 for (int i = 0; i < xmlNodeList.Count; i++) 1036 { 1037 if (xmlNodeList[i].OuterXml.IndexOf("<!--") == 0 && xmlNodeList[i].OuterXml.IndexOf("-->") > 0) 1038 { 1039 continue; 1040 } 1041 XmlElement xmlExcelColumnName = (XmlElement)xmlNodeList[i]; 1042 1043 #region MyRegion 1044 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 #endregion 1075 1076 } 1077 1078 #endregion 1079 1080 DataView dv = dtExcelColumnName.DefaultView; 1081 dv.Sort = "LeadIndex"; 1082 return dv.ToTable(); 1083 } 1084 1085 #endregion 1086 1087 #endregion 1088 1089 #region 下载文件 1090 1091 /// <summary> 1092 /// 下载文件 1093 /// </summary> 1094 /// <param name="path">文件路径</param> 1095 /// <param name="ExcelName">导出名称</param> 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 else 1113 { 1114 HttpContext.Current.Response.Write("无法生成数据，请重新执行查询后，再导出！"); 1115 return; 1116 } 1117 } 1118 1119 #endregion 1120 1121 #region 反射获取数据 1122 1123 /// <summary> 1124 /// 反射获取数据 1125 /// </summary> 1126 /// <param name="excelPageUrl"></param> 1127 /// <returns></returns> 1128 private static DataSet GetAssemblyGlint(ExcelPageUrl excelPageUrl) 1129 { 1130 DataSet set = new DataSet(); 1131 try 1132 { 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 #endregion 1153 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 #endregion 1174 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 #endregion 1195 1196 } 1197 catch (Exception) 1198 { 1199 } 1200 return set; 1201 } 1202 1203 #endregion 1204 1205 } 1206 1207 }
配置文件如
View Code
1 <?xml version="1.0" encoding="utf-8"?> 2 <ExcelPageUrlList> 3 <ExcelPageUrl PKID="791d5f72-53b0-41a2-abe5-9eb1497e34e3" PageName="请假申请" PageUrl="Demo.aspx" SheetName="请假申请" ExcelName="请假申请" ConnectionStrings="ConnectionString" Sql="" CommandType="" Parameter="" IsCongelationWindow="True" IsAssembly="Glint" AssemblyPath="DB_AttendanceManagement.Data.dll" ClassName="DB_AttendanceManagement.Data.DAL.DAT_Bas_LeaveApply" Mehtod="GetTaskList" BornType="Afresh" TemplateName="" BegRow="2" BegColumn="0" RowsNumber=""> 4 <ExcelColumnNameList> 5 <ExcelColumnName PKID="eb49313c-25aa-4088-9921-70a38eaa608d" OriginalityColumnName="UserName" NewColumnName="请假申请人" DataFormatString="无" LeadIndex="1" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 6 <!--<ExcelColumnName PKID="4c444e5d-76aa-4b50-a72d-d07d45d767d6" OriginalityColumnName="LeaveTypeName" NewColumnName="请假类别" DataFormatString="无" LeadIndex="2" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" />--> 7 <ExcelColumnName PKID="e25ceb49-61c0-40ff-8407-06b817b13764" OriginalityColumnName="BeginTime" NewColumnName="请假开始时间" DataFormatString="yyyy年MM月dd日 HH时mm分ss秒" LeadIndex="3" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 8 <ExcelColumnName PKID="e40a0904-a785-4f69-a0b3-3ebefee79e46" OriginalityColumnName="EndTime" NewColumnName="请假截止时间" DataFormatString="yyyy年MM月dd日 HH时mm分ss秒" LeadIndex="4" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 9 <ExcelColumnName PKID="ab96e6c2-2c5b-4e34-8878-54f3814809d8" OriginalityColumnName="TotalTime" NewColumnName="请假时长" DataFormatString="f2" LeadIndex="5" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 10 <ExcelColumnName PKID="cb9e8822-80c1-4523-b499-d1000689b742" OriginalityColumnName="IsReplaceHolidayText" NewColumnName="是否抵用倒休" DataFormatString="无" LeadIndex="6" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 11 <ExcelColumnName PKID="bd3c146f-0c40-4330-84f1-a8b51b071a3d" OriginalityColumnName="ReplaceTime" NewColumnName="抵用时长" DataFormatString="f2" LeadIndex="7" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 12 <ExcelColumnName PKID="c5c19e0e-6172-4971-85fa-244ef5d5e904" OriginalityColumnName="LeaveReason" NewColumnName="请假事由" DataFormatString="无" LeadIndex="8" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 13 <ExcelColumnName PKID="3632e82d-34ca-420b-8ff6-7c09c70e9d0e" OriginalityColumnName="CurrentNodeName" NewColumnName="当前节点" DataFormatString="无" LeadIndex="9" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 14 <ExcelColumnName PKID="1b290547-5e39-48b6-b713-a95bbbd2c53e" OriginalityColumnName="CurrentStateName" NewColumnName="审批状态" DataFormatString="无" LeadIndex="10" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 15 </ExcelColumnNameList> 16 </ExcelPageUrl> 17 <ExcelPageUrl PKID="82b745f6-ba70-41be-bd78-ff7cb4a268f2" PageName="加班申请" PageUrl="T_Bas_OverWorkApply_DefaultList.aspx" SheetName="加班申请" ExcelName="加班申请" ConnectionStrings="ConnectionString" Sql="" CommandType="" Parameter="" IsCongelationWindow="True" IsAssembly="Glint" AssemblyPath="DB_AttendanceManagement.Data.dll" ClassName="DB_AttendanceManagement.Data.DAL.DAT_Bas_OverWorkApply" Mehtod="GetTaskList" BornType="Afresh" TemplateName="" BegRow="" BegColumn="" RowsNumber=""> 18 <ExcelColumnNameList> 19 <ExcelColumnName PKID="74008533-c651-4635-b2c3-19f04b270d08" OriginalityColumnName="UserName" NewColumnName="加班申请人" DataFormatString="无" LeadIndex="1" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 20 <ExcelColumnName PKID="e28b8aa0-4891-4418-b4dd-664a40dce8b9" OriginalityColumnName="OWTypeName" NewColumnName="加班类型" DataFormatString="无" LeadIndex="2" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 21 <ExcelColumnName PKID="43c50382-09ef-4cdc-a67d-14de219ca81b" OriginalityColumnName="BeginTime" NewColumnName="加班开始时间" DataFormatString="yyyy年MM月dd日 HH时mm分ss秒" LeadIndex="3" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 22 <ExcelColumnName PKID="719e96fe-1f7b-4177-956f-139faa80c3c8" OriginalityColumnName="EndTime" NewColumnName="加班结束时间" DataFormatString="yyyy年MM月dd日 HH时mm分ss秒" LeadIndex="4" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 23 <ExcelColumnName PKID="635b633d-f412-45f8-98e4-0282cc62dc5a" OriginalityColumnName="TotalTime" NewColumnName="加班时长" DataFormatString="f2" LeadIndex="5" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 24 <ExcelColumnName PKID="23c9847f-37e4-4658-afcb-74663e0e2e6e" OriginalityColumnName="CurrentNodeName" NewColumnName="当前节点" DataFormatString="无" LeadIndex="6" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 25 <ExcelColumnName PKID="7d69844c-fc2f-4ce6-bb50-9b700b4a2d04" OriginalityColumnName="CurrentStateName" NewColumnName="审批状态" DataFormatString="无" LeadIndex="7" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 26 <ExcelColumnName PKID="c9699aa8-2108-4e34-b0bf-b768f2afc345" OriginalityColumnName="WorkTask" NewColumnName="工作任务" DataFormatString="无" LeadIndex="8" IsLead="True" AssemblyPath="" ClassName="" Mehtod="" IsAssembly="False" UnitSpaceType="无" /> 27 </ExcelColumnNameList> 28 </ExcelPageUrl> 29 </ExcelPageUrlList>
调用模式配置
View Code
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地址')"
View Code
1 Ths.Expand.ExcelProcessing.LeadExcel.DataToExcel();
2579
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
