导出帮助类
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Web; /// <summary> /// ExcleHelper 的摘要说明 /// </summary> public class ExcleHelper { public ExcleHelper() { // // TODO: 在此处添加构造函数逻辑 // } /// <summary> /// 文件名称 /// </summary> public string fileName { get; set; } /// <summary> /// sheet,列,数据源 /// </summary> public List<ExcleSheetColumnData> SheetColumnList { get; set; } /// <summary> /// 导出excle /// </summary> public string Excle(string fileName, List<ExcleSheetColumnData> SheetColumnList) { //设置导出文件路径 string path = HttpContext.Current.Server.MapPath("Export/"); //设置新建文件路径及名称 var newFile = path + fileName + ".xls"; using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write)) { IWorkbook workbook = new HSSFWorkbook(); #region 设置内容单元格样式 (这边的单元格样式一定要放在循环外面,否则会造成office下部分样式丢失,wps下没问题) var style = (HSSFCellStyle)workbook.CreateCellStyle(); //创建样式 style.Alignment = HorizontalAlignment.Center; //水平居中 style.VerticalAlignment = VerticalAlignment.Center; //垂直居中 style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; IFont font = workbook.CreateFont(); font.FontHeight = 18 * 18; style.SetFont(font); #endregion foreach (var SheetColumnModel in SheetColumnList) { var sheet = workbook.CreateSheet(SheetColumnModel.sheetName); var ColumnList = SheetColumnModel.ColumnList; //列头行 var rowColumn = (HSSFRow)sheet.CreateRow(0); //列头 for (var i = 0; i < ColumnList.Count; i++) { var cell = rowColumn.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(ColumnList[i].ColumnValue); sheet.AutoSizeColumn(i); } int j = 0; foreach (DataRow it in SheetColumnModel.DataList.Tables[0].Rows) { j++; var row = sheet.CreateRow(j); for (var i = 0; i < ColumnList.Count; i++) { var cell = row.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(it[ColumnList[i].ColumnKey].ToString()); } } for (var i = 0; i < ColumnList.Count; i++) { SelfWidth(sheet,i); } } workbook.Write(fs); return newFile; } } /// <summary> /// 自适应宽度 /// </summary> /// <param name="sheet"></param> /// <param name="columnNum"></param> public void SelfWidth(ISheet sheet, int columnNum) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度 for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)//在这一 列上循环行 { IRow currentRow = sheet.GetRow(rowNum); ICell currentCell = currentRow.GetCell(columnNum); int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;// 获取当前单元格的内容宽度 if (columnWidth < length + 1) { columnWidth = length + 1; } } sheet.SetColumnWidth(columnNum, columnWidth * 256); } /// <summary> /// 自适应高度 /// </summary> /// <param name="sheet"></param> public void SelfHeight(ISheet sheet) { for (int rowNum = 2; rowNum < sheet.LastRowNum; rowNum++) { IRow currentRow = sheet.GetRow(rowNum); ICell currentCell = currentRow.GetCell(4); ICell currentCell2 = currentRow.GetCell(8); int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length; int length2 = Encoding.UTF8.GetBytes(currentCell2.ToString()).Length; currentRow.HeightInPoints= length > length2 ? 20 * (length / 60 + 2) : 20 * (length2 / 60 + 2); } } } /// <summary> /// 一个sheet和这个sheet的列 /// </summary> public class ExcleSheetColumnData { /// <summary> /// 展示得sheet /// </summary> public string sheetName { get; set; } /// <summary> /// 展示得列 /// </summary> public List<ExcleColumn> ColumnList { get; set; } /// <summary> /// 数据源 /// </summary> public DataSet DataList { get; set; } } /// <summary> /// 列 /// </summary> public class ExcleColumn { /// <summary> /// 列名对应得数据库字段 /// </summary> public string ColumnKey { get; set; } /// <summary> /// 列名 /// </summary> public string ColumnValue { get; set; } } /// <summary> /// 把导出的路径转换为http响应输出流 /// </summary> /// <param name="filePath"></param> public void OutputExcel(string filePath) { FileInfo fileInfo = new FileInfo(filePath); Response.Clear(); Response.ClearContent(); Response.ClearHeaders(); Response.AddHeader("Content-Disposition", "attachment;filename=" + fileInfo.Name); Response.AddHeader("Content-Length", fileInfo.Length.ToString()); Response.AddHeader("Content-Transfer-Encoding", "binary"); Response.ContentType = "application/octet-stream"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); Response.WriteFile(fileInfo.FullName); Response.Flush(); Response.End(); } 如何使用帮助类
ExcleHelper excle = new ExcleHelper(); List<ExcleSheetColumnData> sheetColumns = new List<ExcleSheetColumnData>(); //一个excleSheet代表一个sheet ExcleSheetColumnData excleSheet = new ExcleSheetColumnData(); excleSheet.sheetName = "第一个sheet1"; excleSheet.DataList = new AppInManager().GetDataSetAppInSum1(Bind());//当前这个sheet所展示的数据 所绑定的是一个DataSet List<ExcleColumn> columns = new List<ExcleColumn>(); //columns.Add(new ExcleColumn() { ColumnKey = "绑定的字段名称", ColumnValue = "展示的列名" }); columns.Add(new ExcleColumn() { ColumnKey = "Num", ColumnValue = "序号" }); columns.Add(new ExcleColumn() { ColumnKey = "AddressType", ColumnValue = "所在镇区" }); columns.Add(new ExcleColumn() { ColumnKey = "AppName", ColumnValue = "公司名称" }); columns.Add(new ExcleColumn() { ColumnKey = "AppAddress", ColumnValue = "地址" }); columns.Add(new ExcleColumn() { ColumnKey = "fa", ColumnValue = "发明数量" }); excleSheet.ColumnList = columns; sheetColumns.Add(excleSheet); //ExcleSheetColumnData excleSheet2 = new ExcleSheetColumnData(); //..... //.... //sheetColumns.Add(excleSheet2); string filePath = excle.Excle("有效专利汇总", sheetColumns); OutputExcel(filePath);//可以可无
本网页所有视频内容由 imoviebox边看边下-网页视频下载, iurlBox网页地址收藏管理器 下载并得到。
ImovieBox网页视频下载器 下载地址: ImovieBox网页视频下载器-最新版本下载
本文章由: imapbox邮箱云存储,邮箱网盘,ImageBox 图片批量下载器,网页图片批量下载专家,网页图片批量下载器,获取到文章图片,imoviebox网页视频批量下载器,下载视频内容,为您提供.
阅读和此文章类似的: 全球云计算
官方软件产品操作指南 (170)