导出Excel方式汇总-- 第三种
来源：互联网 发布：c语言左移一位 编辑：程序博客网 时间：2024/06/07 04:00
第三种：利用 Npoi 插件，只需要在项目里引用DLL即可，无需在服务器安装任何插件。
注意：
1、NOPI4.0 支持excel2003和excel2007
2、03和07使用的对象不同，需要区分开来。代码需要写两套。
3、可以保存为文件，也可以直接输出。
public string ListToExcel(List<VmExceldgExcelOut> items, string fileName) { string url=""; int rows = items.Count;//不包括字段名 int cols = 0; int colIndex = 0; if (rows == 0) { return "没有数据!"; } fileName = "批量导出"+fileName + ".xlsx";// + DateTime.Now.ToString("yyyyMMddhhmmss") url = fileUrl + fileName;//返回下载路径 fileName = filepath + fileName; try { XSSFWorkbook xssfworkbook = new XSSFWorkbook(); ISheet sheet = xssfworkbook.CreateSheet("tempData"); ICellStyle cellStyle = xssfworkbook.CreateCellStyle(); cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; /*IFont font12 = xssfworkbook.CreateFont(); font12.FontHeightInPoints = 12; font12.FontName = "宋体";*/ //return url; #region 生成Excel //表头 IRow row = sheet.CreateRow(0); VmExceldgExcelOut temp = new VmExceldgExcelOut(); PropertyInfo[] propertys = temp.GetType().GetProperties(); foreach (PropertyInfo property in propertys) { string code = ((DescriptionAttribute)Attribute.GetCustomAttribute(property, typeof(DescriptionAttribute))).Description;// 属性值 if (string.IsNullOrEmpty(code)) continue; ICell cell = row.CreateCell(colIndex); cell.CellStyle = cellStyle; cell.SetCellValue(code); colIndex++; } cols = colIndex; //数据 for (int r = 0; r < rows; r++) { IRow row1 = sheet.CreateRow(r + 1); temp = items[r]; for (int c = 0; c < cols; c++) { propertys = temp.GetType().GetProperties(); object o = propertys[c].GetValue(temp, null); ICell cell = row1.CreateCell(c); cell.CellStyle = cellStyle; if (o == null) o = ""; cell.SetCellValue(o.ToString()); } } #endregion //转为字节数组 MemoryStream stream = new MemoryStream(); xssfworkbook.Write(stream); var buf = stream.ToArray(); /*HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmssfff"))); HttpContext.Current.Response.BinaryWrite(stream.ToArray()); xssfworkbook = null; stream.Close(); stream.Dispose();*/ //保存为Excel文件 using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } catch (Exception e) { return "导出Excel失败，错误信息：" + e.Message; } return url; } public string DataTableToExcel(DataTable dt, string fileName) { string url = ""; int rows = dt.Rows.Count; int cols = dt.Columns.Count; int colIndex = 0; if (rows == 0) { return "没有数据!"; } fileName = "导入异常原因" + fileName + ".xlsx";// + DateTime.Now.ToString("yyyyMMddhhmmss") url = fileUrl + fileName;//返回下载路径 fileName = filepath + fileName; try { XSSFWorkbook xssfworkbook = new XSSFWorkbook(); ISheet sheet = xssfworkbook.CreateSheet("tempData"); ICellStyle cellStyle = xssfworkbook.CreateCellStyle(); cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; #region 生成Excel //表头 IRow row = sheet.CreateRow(0); VmExceldgExcelOut temp = new VmExceldgExcelOut(); PropertyInfo[] propertys = temp.GetType().GetProperties(); foreach (PropertyInfo property in propertys) { string code = ((DescriptionAttribute)Attribute.GetCustomAttribute(property, typeof(DescriptionAttribute))).Description;// 属性值 if (string.IsNullOrEmpty(code)) continue; ICell cell = row.CreateCell(colIndex); cell.CellStyle = cellStyle; cell.SetCellValue(code); colIndex++; } //加入异常表头 ICell cell1 = row.CreateCell(colIndex); cell1.SetCellValue("异常原因"); //数据 for (int r = 0; r < rows; r++) { IRow row1 = sheet.CreateRow(r + 1); for (int c = 0; c < cols; c++) { ICell cell = row1.CreateCell(c); cell.CellStyle = cellStyle; cell.SetCellValue(dt.Rows[r][c].ToString()); } } #endregion //转为字节数组 MemoryStream stream = new MemoryStream(); xssfworkbook.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } catch (Exception e) { return "导出Excel失败，错误信息：" + e.Message; } return url; }如果是ajax调用后台输出，可先保存在缓存里，通过点击下载按钮下载缓存中的内容。
如果是通过缓存的方式，先把数据保存即可，下载时写入Excel直接已输出流的方式保存到客户端。
--以下为未测试代码：
http对象如果是页面的cs文件，可直接使用输出对象输出EXCEL，如果是ajax调用，需获取ajax传入的http对象，通过传入的对象进行输出
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmssfff"))); HttpContext.Current.Response.BinaryWrite(stream.ToArray()); xssfworkbook = null; stream.Close(); stream.Dispose();public static void GetOutPutExcelData(HttpContext context, string excelid){ if (!string.IsNullOrEmpty(excelid) && ExcelUtil.outputExcel.ContainsKey(excelid)) { OutPutExcel outputExcel = ExcelUtil.outputExcel[excelid]; ExcelUtil.outputExcel.Remove(excelid); context.Response.Clear(); OutPutExcel(outputExcel, context.Response); try { context.Response.End(); } catch (ThreadAbortException) { } }} private static MemoryStream OutPutExcelMOrH(OutPutExcel outputExcel, HttpResponse req){ if (outputExcel == null) { throw new Exception("在导出Excel时发生异常，系统检测到：导出Excel对象为空！"); } if (outputExcel.InputData != null) { if (outputExcel.CaptionList == null) { return null; } bool flag = false; IEnumerable<ExcelCaption> source = from c in outputExcel.CaptionList where c.ChildrenCount > 0 select c; if ((source != null) && (source.Count<ExcelCaption>() > 0)) { flag = true; } if (flag && (outputExcel.WorksheetName.IndexOf("mergetitle") < 0)) { outputExcel.WorksheetName = outputExcel.WorksheetName + "-mergetitle"; } DataTable newExcelTable = GetNewExcelTable(outputExcel.InputData, outputExcel.CaptionList); Workbook workbook = new Workbook(); workbook.Worksheets.Clear(); workbook.Worksheets.Add(outputExcel.WorksheetName); Worksheet worksheet = workbook.Worksheets[0]; Aspose.Cells.Cells cells = worksheet.Cells; worksheet.Cells.ImportDataTable(newExcelTable, true, "A1"); Style style = workbook.Styles[workbook.Styles.Add()]; style.HorizontalAlignment = TextAlignmentType.Center; style.Font.Size = 10; style.Font.IsBold = true; style.ForegroundColor = Color.FromArgb(0xff, 0x99, 0x99, 0xff); style.Borders.SetStyle(CellBorderType.Thin); style.Pattern = BackgroundType.Solid; style.Borders[BorderType.DiagonalDown].LineStyle = CellBorderType.None; style.Borders[BorderType.DiagonalUp].LineStyle = CellBorderType.None; Style style2 = workbook.Styles[workbook.Styles.Add()]; style2.Font.Size = 10; style2.IsTextWrapped = true; style2.Borders.SetStyle(CellBorderType.Thin); style2.Borders[BorderType.DiagonalDown].LineStyle = CellBorderType.None; style2.Borders[BorderType.DiagonalUp].LineStyle = CellBorderType.None; style2.Number = 0x31; int count = newExcelTable.Columns.Count; int rowNumber = newExcelTable.Rows.Count; Range range = worksheet.Cells.CreateRange(0, 0, 1, count); range.Style = style; range.RowHeight = 25.0; int firstRow = 1; if (flag) { worksheet.Cells.InsertRow(0); int num4 = 0; int num5 = 1; int firstColumn = 0; foreach (ExcelCaption caption in outputExcel.CaptionList) { if (source.Contains<ExcelCaption>(caption)) { cells.Merge(num4, firstColumn, 1, caption.ChildrenCount); cells[num4, firstColumn].PutValue(caption.DisplayName); range = worksheet.Cells.CreateRange(num4, firstColumn, 1, caption.ChildrenCount); range.Style = style; range.RowHeight = 25.0; foreach (ExcelCaption caption2 in caption.Childrens) { cells.Merge(num5, firstColumn, 1, 1); cells[num5, firstColumn].PutValue(caption2.DisplayName); range = worksheet.Cells.CreateRange(num5, firstColumn, 1, 1); range.Style = style; range.RowHeight = 25.0; firstColumn++; } } else if (caption.ParentName == string.Empty) { cells.Merge(num4, firstColumn, 2, 1); cells[num4, firstColumn].PutValue(caption.DisplayName); range = worksheet.Cells.CreateRange(num4, firstColumn, 2, 1); range.Style = style; range.RowHeight = 25.0; firstColumn++; } } firstRow = 2; } worksheet.Cells.CreateRange(firstRow, 0, rowNumber, count).Style = style2; worksheet.AutoFitRows(); if (req == null) { return workbook.SaveToStream(); } workbook.Save(outputExcel.FileName, FileFormatType.Default, SaveType.OpenInBrowser, req, Encoding.UTF8); } return null;} private static MemoryStream OutPutExcelMOrH(OutPutExcel outputExcel, HttpResponse req){ if (outputExcel == null) { throw new Exception("在导出Excel时发生异常，系统检测到：导出Excel对象为空！"); } if (outputExcel.InputData != null) { if (outputExcel.CaptionList == null) { return null; } bool flag = false; IEnumerable<ExcelCaption> source = from c in outputExcel.CaptionList where c.ChildrenCount > 0 select c; if ((source != null) && (source.Count<ExcelCaption>() > 0)) { flag = true; } if (flag && (outputExcel.WorksheetName.IndexOf("mergetitle") < 0)) { outputExcel.WorksheetName = outputExcel.WorksheetName + "-mergetitle"; } DataTable newExcelTable = GetNewExcelTable(outputExcel.InputData, outputExcel.CaptionList); Workbook workbook = new Workbook(); workbook.Worksheets.Clear(); workbook.Worksheets.Add(outputExcel.WorksheetName); Worksheet worksheet = workbook.Worksheets[0]; Aspose.Cells.Cells cells = worksheet.Cells; worksheet.Cells.ImportDataTable(newExcelTable, true, "A1"); Style style = workbook.Styles[workbook.Styles.Add()]; style.HorizontalAlignment = TextAlignmentType.Center; style.Font.Size = 10; style.Font.IsBold = true; style.ForegroundColor = Color.FromArgb(0xff, 0x99, 0x99, 0xff); style.Borders.SetStyle(CellBorderType.Thin); style.Pattern = BackgroundType.Solid; style.Borders[BorderType.DiagonalDown].LineStyle = CellBorderType.None; style.Borders[BorderType.DiagonalUp].LineStyle = CellBorderType.None; Style style2 = workbook.Styles[workbook.Styles.Add()]; style2.Font.Size = 10; style2.IsTextWrapped = true; style2.Borders.SetStyle(CellBorderType.Thin); style2.Borders[BorderType.DiagonalDown].LineStyle = CellBorderType.None; style2.Borders[BorderType.DiagonalUp].LineStyle = CellBorderType.None; style2.Number = 0x31; int count = newExcelTable.Columns.Count; int rowNumber = newExcelTable.Rows.Count; Range range = worksheet.Cells.CreateRange(0, 0, 1, count); range.Style = style; range.RowHeight = 25.0; int firstRow = 1; if (flag) { worksheet.Cells.InsertRow(0); int num4 = 0; int num5 = 1; int firstColumn = 0; foreach (ExcelCaption caption in outputExcel.CaptionList) { if (source.Contains<ExcelCaption>(caption)) { cells.Merge(num4, firstColumn, 1, caption.ChildrenCount); cells[num4, firstColumn].PutValue(caption.DisplayName); range = worksheet.Cells.CreateRange(num4, firstColumn, 1, caption.ChildrenCount); range.Style = style; range.RowHeight = 25.0; foreach (ExcelCaption caption2 in caption.Childrens) { cells.Merge(num5, firstColumn, 1, 1); cells[num5, firstColumn].PutValue(caption2.DisplayName); range = worksheet.Cells.CreateRange(num5, firstColumn, 1, 1); range.Style = style; range.RowHeight = 25.0; firstColumn++; } } else if (caption.ParentName == string.Empty) { cells.Merge(num4, firstColumn, 2, 1); cells[num4, firstColumn].PutValue(caption.DisplayName); range = worksheet.Cells.CreateRange(num4, firstColumn, 2, 1); range.Style = style; range.RowHeight = 25.0; firstColumn++; } } firstRow = 2; } worksheet.Cells.CreateRange(firstRow, 0, rowNumber, count).Style = style2; worksheet.AutoFitRows(); if (req == null) { return workbook.SaveToStream(); } workbook.Save(outputExcel.FileName, FileFormatType.Default, SaveType.OpenInBrowser, req, Encoding.UTF8); } return null;} 下面的跟导入导出无关，可以不看。本人只是为了方便记忆加入的代码。
---------------------------------------------------ashx文件--------------------------------------------------
public class VmExcelJsonService : JSONServeBase, IReadOnlySessionState { /// <summary> /// 请求输出到页面Json对象 /// 实现流程： /// 请求输出到页面Json对象 /// </summary> /// <param name="context">请求上下文</param> /// <returns>JSON字符串</returns> public override string ResponseJSON(HttpContext context) { if (null == context) { this.Logger.Debug("在请求 VmExcelJsonService 时出错，请求上下文不能为空！"); throw new ArgumentNullException("context"); } string strResponse = string.Empty; string controld = this.GetRequestQueryString("CommandControl"); string eventName = this.GetRequestQueryString("CommandEvent"); string commandName = this.GetRequestQueryString("CommandName"); string caller = this.BuildSwitchCaseID(controld, eventName, commandName); if (!string.IsNullOrEmpty(caller)) { // 依据请求命令执行方法 switch (caller.ToUpper()) { // 获取控件初始化数据 case "GETCONTROLDATA": string strControlID = this.GetRequestQueryString("ControlID"); strResponse = GetControlInitData(strControlID); break; case "BTNPUT_CLICK_ADD": strResponse = this.btnPut_Add(context); break; // 未定义的操作 default: ResultData result = new ResultData(); result.SetErrorStateMessage("未定义的操作!"); strResponse = ConvertEntityToDomainJSONString<ResultData>(result); break; } } else { ResultData unknown = new ResultData(); unknown.SetErrorStateMessage("无法处理的参数名称!"); strResponse = ConvertEntityToDomainJSONString<ResultData>(unknown); } return strResponse; } /// <summary> /// 构建分支语句的ID /// 实现流程： /// 构建分支语句的ID /// </summary> /// <param name="controlName">控件名称</param> /// <param name="eventName">事件名称</param> /// <param name="commandName">命令名称</param> /// <returns>string</returns> private string BuildSwitchCaseID(string controlName, string eventName, string commandName) { string caseID = string.Empty; if (!string.IsNullOrEmpty(controlName)) { caseID += string.Format("{0}_", controlName); } if (!string.IsNullOrEmpty(eventName)) { caseID += string.Format("{0}_", eventName); } caseID += string.Format("{0}", commandName); return caseID.ToUpper(); }如果是ajax调用后台输出，可先保存在缓存里，通过点击下载按钮下载缓存中的内容。
如果是通过缓存的方式，先把数据保存即可，下载时写入Excel直接已输出流的方式保存到客户端。
0 0
- 导出Excel方式汇总-- 第三种
- 导出Excel方式汇总-- 第二种
- 导出Excel方式汇总-- 第一种
- C#中将数据导出为EXCEL方式汇总
- C#导出Excel汇总
- 导出EXCEL方法汇总
- java导出excel文件------第三种方法
- 导出excel的两种方式
- 导出Excel的几种方式
- datagridview 导出excel的两种方式
- java 三种excel导出方式
- 导出excel的三种方式
- poi导出Excel的两种方式
- 导入Excel方式汇总--第二种
- poi 方式导出excel
- jxl 方式导出excel
- jxl方式excel导出
- C#导出到Excel汇总
- php中的对象引用和复制
- c# 给txt追加文本
- 生活杂记1
- Kaiwii
- JAVA 中BIO,NIO,AIO的理解
- 导出Excel方式汇总-- 第三种
- 杭电OJ2084-数塔
- VirtualBox安装CentOS 7（一）
- Android-用apktool实现多渠道自动打包
- Groovy入门教程
- python的函数参数传递
- jQuery UI使用总结
- 计算几何 欧拉定理应用
- Myeclipse环境下 Springmvc注解式与hibernate框架整合具体步骤！
