最近一值在做报表的工作，写点小东西，总结一下。
采用十分火的NPOI插件来来读写Excel，NPOI能很方便的在没有装OFFICE的机器上读书xls、word、ppt。
便于代码利用，不重复造轮子我们先把NPOI封装一下看代码：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using NPOI.HSSF.UserModel; using System.IO; using NPOI.SS.UserModel; using System.Data; namespace _800Tele.eCRM.BLL.ProExcel { public interface IExcelNPOI { /// <summary> /// 获取WorkBook /// </summary> /// <param name="path">文件位置</param> /// <returns>IWorkbook</returns> IWorkbook GetWorkbook(string path); /// <summary> /// 获取WorkBook /// </summary> /// <param name="path">文件流</param> /// <returns>IWorkbook</returns> IWorkbook GetWorkbook(Stream fs, bool isXls); /// <summary> /// 转换excel数据到DataTable /// 默认表单索引0，系统指定列名行和标题行，数据行从0开始 /// </summary> /// <param name="path">文件位置</param> /// <returns>DataTable</returns> DataTable ConvertToTable(string path); /// <summary> /// 转换excel数据到DataTable /// </summary> /// <param name="path">文件位置</param> /// <param name="sheetIndex">表单索引号</param> /// <param name="titleRow">列名行号，-1则不指定，由系统指定</param> /// <param name="captionRow">标题行号，-1则不指定，由系统指定</param> /// <param name="dataFrom">数据起始行号，若为-1则从0行开始</param> /// <returns>DataTable</returns> DataTable ConvertToTable(string path, int sheetIndex, int titleRow, int captionRow, int dataFrom); /// <summary> /// 根据传入的Stream转换Excel为table /// </summary> /// <param name="fs">Stream</param> /// <param name="isXls">是否xls格式</param> /// <param name="sheetIndex">表单索引号</param> /// <param name="titleRow">列名行号，-1则不指定，由系统指定</param> /// <param name="captionRow">标题行号，-1则不指定，由系统指定</param> /// <param name="dataFrom">数据起始行号，若为-1则从0行开始</param> /// <returns>DataTable</returns> DataTable ConvertToTable(Stream fs, bool isXls, int sheetIndex, int titleRow, int captionRow, int dataFrom); /// <summary> /// 转换excel数据到DataTable /// 默认表单索引0，系统指定列名行和标题行，数据行从0开始 /// </summary> /// <param name="workBook">Workbook对象</param> /// <returns>DataTable</returns> DataTable ConvertToTable(IWorkbook workBook); /// <summary> /// 转换指定表单excel数据到DataTable /// 系统指定列名行和标题行，数据行从0开始 /// </summary> /// <param name="workBook">Workbook对象</param> /// <param name="sheetIndex">表单索引号</param> /// <returns>DataTable</returns> DataTable ConvertToTable(IWorkbook workBook, int sheetIndex); /// <summary> /// 转换excel数据到DataTable /// </summary> /// <param name="workBook">Workbook对象</param> /// <param name="sheetIndex">表单索引号</param> /// <param name="titleRow">列名行号，-1则不指定，由系统指定</param> /// <param name="captionRow">标题行号，-1则不指定，由系统指定</param> /// <param name="dataFrom">数据起始行号，若为-1则从0行开始</param> /// <returns>DataTable</returns> DataTable ConvertToTable(IWorkbook workBook, int sheetIndex, int titleRow, int captionRow, int dataFrom); /// <summary> /// 转换指定表单excel数据到DataTable /// 系统指定列名行和标题行，数据行从0开始 /// </summary> /// <param name="workBook">Workbook对象</param> /// <param name="sheetName">表单名称</param> /// <returns>DataTable</returns> DataTable ConvertToTable(IWorkbook workBook, string sheetName); /// <summary> /// 转换excel数据到DataTable /// </summary> /// <param name="workBook">Workbook对象</param> /// <param name="sheetName">表单名称</param> /// <param name="titleRow">列名行号，-1则不指定，由系统指定</param> /// <param name="captionRow">标题行号，-1则不指定，由系统指定</param> /// <param name="dataFrom">数据起始行号，若为-1则从0行开始</param> /// <returns>DataTable</returns> DataTable ConvertToTable(IWorkbook workBook, string sheetName, int titleRow, int captionRow, int dataFrom); /// <summary> /// 按给定格式转换Excel数据到DataTable /// </summary> /// <param name="workBook">Workbook对象</param> /// <param name="sheetIndex">表单索引号</param> /// <param name="tableSchema">给定格式DataTable</param> /// <param name="dataFrom">数据起始行号，若为-1则从0行开始</param> /// <returns>DataTable</returns> DataTable ConvertToTable(IWorkbook workBook, int sheetIndex, DataTable tableSchema, int dataFrom); /// <summary> /// 按给定格式转换Excel数据到DataTable /// </summary> /// <param name="workBook">Workbook对象</param> /// <param name="sheetName">表单名称</param> /// <param name="tableSchema">给定格式DataTable</param> /// <param name="dataFrom">数据起始行号，若为-1则从0行开始</param> /// <returns>DataTable</returns> DataTable ConvertToTable(IWorkbook workBook, string sheetName, DataTable tableSchema, int dataFrom); /// <summary> /// 数据处理 /// </summary> /// <param name="table">转换后的数据</param> /// <param name="s"></param> /// <returns>TRUE：处理成功，FALSE：处理失败</returns> bool ProcessTable(DataTable table, params object[] s); /// <summary> /// 行数据处理 /// </summary> /// <param name="row">数据行</param> /// <param name="s"></param> /// <returns>TRUE：处理成功，FALSE：处理失败</returns> bool ProcessRow(DataRow row, params object[] s); /// <summary> /// 多行数据处理 /// </summary> /// <param name="rows"></param> /// <param name="s"></param> /// <returns>TRUE：处理成功，FALSE：处理失败</returns> bool ProcessRows(List<DataRow> rows, params object[] s); } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace _800Tele.eCRM.BLL.ProExcel { public interface IProExcel { /// <summary> /// 上传商品EXCEL /// </summary> /// <param name="url"></param> void ImportExcel(string url,string from); void ExportExcel(List<string> Plus); } }
导入、导出的接口下面来实现接口;
using System; using System.Collections.Generic; using System.Linq; using System.Text; using NPOI.SS.UserModel; using System.IO; using System.Data; using NPOI.HSSF.UserModel; using NPOI.XSSF.UserModel; using System.Collections; namespace _800Tele.eCRM.BLL.ProExcel { public abstract class AbstractExcelNPOI : IExcelNPOI { #region<<接口实现>> /// <summary> /// 获取WorkBook /// </summary> /// <param name="path">文件位置</param> /// <returns>IWorkbook</returns> public IWorkbook GetWorkbook(string path) { try { using (Stream fs = new FileStream(path, FileMode.Open, FileAccess.Read)) { if (path.ToLower().EndsWith(".xls", StringComparison.OrdinalIgnoreCase)) { return new HSSFWorkbook(fs) as IWorkbook; } else if (path.ToLower().EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase)) { return new XSSFWorkbook(fs) as IWorkbook; } else return null; } } catch (Exception ex) { throw ex; } } /// <summary> /// 获取WorkBook /// </summary> /// <param name="path">文件流</param> /// <returns>IWorkbook</returns> public IWorkbook GetWorkbook(Stream fs, bool isXls) { try { if (isXls) return new HSSFWorkbook(fs) as IWorkbook; else return new XSSFWorkbook(fs) as IWorkbook; } catch (Exception ex) { throw ex; } } /// <summary> /// 转换excel数据到DataTable /// 默认表单索引0，系统指定列名行和标题行，数据行从0开始 /// </summary> /// <param name="path">文件位置</param> /// <returns>DataTable</returns> public DataTable ConvertToTable(string path) { try { IWorkbook workBook = GetWorkbook(path); return ConvertToTable(workBook); } catch (Exception ex) { throw ex; } } /// <summary> /// 转换excel数据到DataTable /// </summary> /// <param name="path">文件位置</param> /// <param name="sheetIndex">表单索引号</param> /// <param name="titleRow">列名行号，-1则不指定，由系统指定</param> /// <param name="captionRow">标题行号，-1则不指定，由系统指定</param> /// <param name="dataFrom">数据起始行号，若为-1则从0行开始</param> /// <returns>DataTable</returns> public DataTable ConvertToTable(string path, int sheetIndex, int titleRow, int captionRow, int dataFrom) { try { IWorkbook workBook = GetWorkbook(path); return ConvertToTable(workBook, sheetIndex, titleRow, captionRow, dataFrom); } catch (Exception ex) { throw ex; } } /// <summary> /// 根据传入的Stream转换Excel为table /// </summary> /// <param name="fs">Stream</param> /// <param name="isXls">是否xls格式</param> /// <param name="sheetIndex">表单索引号</param> /// <param name="titleRow">列名行号，-1则不指定，由系统指定</param> /// <param name="captionRow">标题行号，-1则不指定，由系统指定</param> /// <param name="dataFrom">数据起始行号，若为-1则从0行开始</param> /// <returns>DataTable</returns> public DataTable ConvertToTable(Stream fs, bool isXls, int sheetIndex, int titleRow, int captionRow, int dataFrom) { try { IWorkbook workBook = GetWorkbook(fs, isXls); return ConvertToTable(workBook, sheetIndex, titleRow, captionRow, dataFrom); } catch (Exception ex) { throw ex; } } /// <summary> /// 转换excel数据到DataTable /// 默认表单索引0，系统指定列名行和标题行，数据行从0开始 /// </summary> /// <param name="workBook">Workbook对象</param> /// <returns>DataTable</returns> public DataTable ConvertToTable(IWorkbook workBook) { return ConvertToTable(workBook, 0, -1, -1, 0); } /// <summary> /// 转换指定表单excel数据到DataTable /// 系统指定列名行和标题行，数据行从0开始 /// </summary> /// <param name="workBook">Workbook对象</param> /// <param name="sheetIndex">表单索引号</param> /// <returns>DataTable</returns> public DataTable ConvertToTable(IWorkbook workBook, int sheetIndex) { try { return ConvertToTable(workBook, sheetIndex, -1, -1, 0); } catch (Exception ex) { throw ex; } } /// <summary> /// 转换excel数据到DataTable /// </summary> /// <param name="workBook">Workbook对象</param> /// <param name="sheetIndex">表单索引号</param> /// <param name="titleRow">列名行号，-1则不指定，由系统指定</param> /// <param name="captionRow">标题行号，-1则不指定，由系统指定</param> /// <param name="dataFrom">数据起始行号，若为-1则从0行开始</param> /// <returns>DataTable</returns> public DataTable ConvertToTable(IWorkbook workBook, int sheetIndex, int titleRow, int captionRow, int dataFrom) { try { // 获取excel指定表单 ISheet sheet = workBook.GetSheetAt(sheetIndex); // 获取表单中所有行 IEnumerator rows = sheet.GetRowEnumerator(); // 最大列数 int maxCellNum = 0; while (rows.MoveNext()) { if (((IRow)rows.Current).LastCellNum > maxCellNum) maxCellNum = ((IRow)rows.Current).LastCellNum; } rows.Reset(); #region<<构建返回表结构>> DataTable table = new DataTable(); // 列名标题行 IRow rowTitle = null; if (titleRow >= 0) rowTitle = sheet.GetRow(titleRow); // caption IRow rowCaption = null; if (captionRow >= 0) rowCaption = sheet.GetRow(captionRow); for(int i = 0; i < maxCellNum; i++) { // 列名 DataColumn dc = new DataColumn(); string colName = string.Empty; if (rowTitle != null) { if (i >= rowTitle.LastCellNum) colName = Convert.ToChar(((int)'A') + i).ToString(); else { ICell cell = rowTitle.GetCell(i); if (cell == null) colName = Convert.ToChar(((int)'A') + i).ToString(); else colName = /*Convert.ToChar(((int)'A') + i).ToString() + "_" + */GetCellValue(cell).ToString(); } } else colName = Convert.ToChar(((int)'A') + i).ToString(); if (table.Columns.Contains(colName)) colName = colName + i.ToString(); dc.ColumnName = colName; // 标题 if (rowCaption != null) { if (i >= rowCaption.LastCellNum) dc.Caption = Convert.ToChar(((int)'A') + i).ToString(); else { ICell cell = rowCaption.GetCell(i); if (cell == null) dc.Caption = Convert.ToChar(((int)'A') + i).ToString(); else dc.Caption = /*Convert.ToChar(((int)'A') + i).ToString() + "_" + */GetCellValue(cell).ToString(); } } else dc.Caption = Convert.ToChar(((int)'A')+i).ToString(); table.Columns.Add(dc); } #endregion #region<<填充返回数据>> while (rows.MoveNext()) { IRow row = rows.Current as IRow; if (row.RowNum < dataFrom) continue; if (row.RowNum == titleRow || row.RowNum == captionRow) continue; // 填充行数据 DataRow dr = table.NewRow(); for (int i = 0; i < maxCellNum; i++) { if (i >= row.LastCellNum) continue; ICell cell = row.GetCell(i); if (cell == null) dr[i] = null; else dr[i] = GetCellValue(cell); } table.Rows.Add(dr); } #endregion return table; } catch (Exception ex) { throw ex; } } /// <summary> /// 转换指定表单excel数据到DataTable /// 系统指定列名行和标题行，数据行从0开始 /// </summary> /// <param name="workBook">Workbook对象</param> /// <param name="sheetName">表单名称</param> /// <returns>DataTable</returns> public DataTable ConvertToTable(IWorkbook workBook, string sheetName) { try { ISheet sheet = workBook.GetSheet(sheetName); return ConvertToTable(workBook, workBook.GetSheetIndex(sheet), -1, -1, 0); } catch (Exception ex) { throw ex; } } /// <summary> /// 转换excel数据到DataTable /// </summary> /// <param name="workBook">Workbook对象</param> /// <param name="sheetName">表单名称</param> /// <param name="titleRow">列名行号，-1则不指定，由系统指定</param> /// <param name="captionRow">标题行号，-1则不指定，由系统指定</param> /// <param name="dataFrom">数据起始行号，若为-1则从0行开始</param> /// <returns>DataTable</returns> public DataTable ConvertToTable(IWorkbook workBook, string sheetName, int titleRow, int captionRow, int dataFrom) { try { ISheet sheet = workBook.GetSheet(sheetName); return ConvertToTable(workBook, workBook.GetSheetIndex(sheet), titleRow, captionRow, dataFrom); } catch (Exception ex) { throw ex; } } /// <summary> /// 按给定格式转换Excel数据到DataTable /// </summary> /// <param name="workBook">Workbook对象</param> /// <param name="sheetIndex">表单索引号</param> /// <param name="tableSchema">给定格式DataTable</param> /// <param name="dataFrom">数据起始行号，若为-1则从0行开始</param> /// <returns>DataTable</returns> public DataTable ConvertToTable(IWorkbook workBook, int sheetIndex, DataTable tableSchema, int dataFrom) { try { int colNums = 0; if (tableSchema != null) colNums = tableSchema.Columns.Count; if (tableSchema == null || colNums <= 0) return ConvertToTable(workBook, sheetIndex, -1, -1, 0); if (dataFrom < 0) dataFrom = 0; ISheet sheet = workBook.GetSheetAt(sheetIndex); // 获取表单中所有行 IEnumerator rows = sheet.GetRowEnumerator(); while (rows.MoveNext()) { IRow row = rows.Current as IRow; if (row.RowNum < dataFrom) continue; // 填充行数据 DataRow dr = tableSchema.NewRow(); for (int i = 0; i < colNums; i++) { if (i >= row.LastCellNum) continue; ICell cell = row.GetCell(i); if (cell == null) dr[i] = null; else dr[i] = GetCellValue(cell); } tableSchema.Rows.Add(dr); } return tableSchema; } catch (Exception ex) { throw ex; } } /// <summary> /// 按给定格式转换Excel数据到DataTable /// </summary> /// <param name="workBook">Workbook对象</param> /// <param name="sheetName">表单名称</param> /// <param name="tableSchema">给定格式DataTable</param> /// <param name="dataFrom">数据起始行号，若为-1则从0行开始</param> /// <returns>DataTable</returns> public DataTable ConvertToTable(IWorkbook workBook, string sheetName, DataTable tableSchema, int dataFrom) { try { ISheet sheet = workBook.GetSheet(sheetName); return ConvertToTable(workBook, workBook.GetSheetIndex(sheet), tableSchema, dataFrom); } catch (Exception ex) { throw ex; } } #endregion #region<<抽象方法>> /// <summary> /// 数据处理 /// </summary> /// <param name="table">转换后的数据</param> /// <param name="s"></param> /// <returns>TRUE：处理成功，FALSE：处理失败</returns> public abstract bool ProcessTable(DataTable table, params object[] s); /// <summary> /// 行数据处理 /// </summary> /// <param name="row">数据行</param> /// <param name="s"></param> /// <returns>TRUE：处理成功，FALSE：处理失败</returns> public abstract bool ProcessRow(DataRow row, params object[] s); /// <summary> /// 多行数据处理 /// </summary> /// <param name="rows"></param> /// <param name="s"></param> /// <returns>TRUE：处理成功，FALSE：处理失败</returns> public abstract bool ProcessRows(List<DataRow> rows, params object[] s); #endregion #region<<用户函数>> /// <summary> /// 获取单元格数据 /// </summary> /// <param name="cell">单元格对象</param> /// <returns>数据object</returns> protected object GetCellValue(ICell cell) { switch (cell.CellType) { case CellType.BLANK: return "[null]"; case CellType.BOOLEAN: return cell.BooleanCellValue; case CellType.ERROR: return cell.ErrorCellValue; case CellType.NUMERIC: if (DateUtil.IsCellDateFormatted(cell)) return cell.DateCellValue; return cell.NumericCellValue; case CellType.STRING: return cell.StringCellValue; case CellType.Unknown: return cell.RichStringCellValue.String; default: return "=" + cell.CellFormula; } } /// <summary> /// 将dt里的数据导出为Excel到指定位置 /// </summary> /// <param name="dt">数据源</param> /// <param name="TitleType">标题的格式：(中文标题：Chinese,英文标题：English,中英文双标题：Both)</param> /// <param name="path">导出的数据文件要放目录</param> /// <param name="filename">文件名称。文件名称无效时自动生成一个文件名</param> /// <returns></returns> public string DataTabelExportToExcel(string ReportName, DataTable dt, string path, params object[] s) { HSSFWorkbook book = new HSSFWorkbook(); ISheet sheet = book.CreateSheet(ReportName); #region sheet赋值 //reportname IRow reportname = sheet.CreateRow(0); reportname.CreateCell(0).SetCellValue(ReportName); //初始化标题 IRow Title1 = sheet.CreateRow(1); foreach (DataColumn column in dt.Columns) { Title1.CreateCell(column.Ordinal).SetCellValue(column.Caption); } //导入数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow dataRow = new HSSFRow(); dataRow = sheet.CreateRow(i + 2); foreach (DataColumn column in dt.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(dt.Rows[column.Ordinal].ToString()); } } #endregion //处理文件路径 path = path.Trim(); if (path.Substring(path.Length - 1) != "/" && path.Substring(path.Length - 1) != @"\") { path = path + "/"; } if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } string filename = ""; //如果文件名无效，则自动生成一个文件名 if (s == null || s.Length < 1) { filename = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; } else { filename = s[0] as string; } //保存文件 FileStream file = new FileStream(path + filename, FileMode.Create); book.Write(file); file.Close(); return path + filename; } #endregion } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.OleDb; using System.IO; using System.Collections; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; namespace _800Tele.eCRM.BLL.ProExcel { public abstract class AbstractProExcel : IProExcel { public virtual void ImportExcel(string url, string from) { } public virtual void ExportExcel() { } public virtual void ExportExcel(List<string> Plus) { throw new NotImplementedException(); } /// <summary> /// 读取Excel文件 /// </summary> /// <param name="filePath">Excel文件的服务器屋里路径</param> /// <returns>DataSet数据集</returns> public DataTable ReadExcel(string filepath) { InitializeWorkbook(filepath); return ConvertToDataTable(); } IWorkbook hssfworkbook; void InitializeWorkbook(string path) { using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read)) { if (path.EndsWith(".xls", StringComparison.OrdinalIgnoreCase)) { hssfworkbook = new HSSFWorkbook(file); } else if (path.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase)) { hssfworkbook = (IWorkbook)new XSSFWorkbook(path); } else { hssfworkbook = (IWorkbook)new XSSFWorkbook(path); } } } private DataTable ConvertToDataTable() { ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); System.Collections.IEnumerator rows2 = sheet.GetRowEnumerator(); int LastLength = 0; while (rows2.MoveNext()) { IRow row = (IRow)rows2.Current; if (row.LastCellNum > LastLength) LastLength = row.LastCellNum; } DataTable dt = new DataTable(); for (int j = 0; j < LastLength; j++) { dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); } while (rows.MoveNext()) { IRow row = (IRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { //IDataFormat format = sheet.Workbook.CreateDataFormat(); //cell.CellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); if (cell.CellType == CellType.NUMERIC) { if (HSSFDateUtil.IsCellDateFormatted(cell)) { //double d = cell.NumericCellValue;// getNumericCellValue(); //DateTime date = HSSFDateUtil.GetJavaDate(d); //dr[i] = date; dr[i] = cell.DateCellValue; } else { dr[i] = cell.ToString(); } } else { dr[i] = cell.ToString(); } } } dt.Rows.Add(dr); } return dt; } } }
用抽象类来实现通用方法；
using System.Collections; using NPOI.HSSF.Util; using NPOI.SS.Util; namespace _800Tele.eCRM.BLL.ProExcel { public static class ExcelStyle { /// <summary> /// 获取字体样式 /// </summary> /// <param name="hssfworkbook">Excel操作类</param> /// <param name="fontname">字体名</param> /// <param name="fontcolor">字体颜色</param> /// <param name="fontsize">字体大小</param> /// <returns></returns> public static IFont GetFontStyle(HSSFWorkbook hssfworkbook, string fontfamily, HSSFColor fontcolor, int fontsize) { IFont font1 = hssfworkbook.CreateFont(); if (string.IsNullOrEmpty(fontfamily)) { font1.FontName = fontfamily; } if (fontcolor != null) { font1.Color = fontcolor.GetIndex(); } font1.IsItalic = true; font1.FontHeightInPoints = (short)fontsize; return font1; } /// <summary> /// 设置单元格样式 /// </summary> /// <param name="hssfworkbook">Excel操作类</param> /// <param name="font">单元格字体</param> /// <param name="fillForegroundColor">图案的颜色</param> /// <param name="fillPattern">图案样式</param> /// <param name="fillBackgroundColor">单元格背景</param> /// <param name="ha">垂直对齐方式</param> /// <param name="va">垂直对齐方式</param> /// <returns></returns> public static ICellStyle SetCellStyle(HSSFWorkbook hssfworkbook, IFont font, HSSFColor fillForegroundColor, FillPatternType fillPattern, HSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va) { ICellStyle cellstyle = hssfworkbook.CreateCellStyle(); cellstyle.FillPattern = fillPattern; cellstyle.Alignment = ha; cellstyle.VerticalAlignment = va; if (fillForegroundColor != null) { cellstyle.FillForegroundColor = fillForegroundColor.GetIndex(); } if (fillBackgroundColor != null) { cellstyle.FillBackgroundColor = fillBackgroundColor.GetIndex(); } if (font != null) { cellstyle.SetFont(font); } //有边框 cellstyle.BorderBottom = BorderStyle.THIN; cellstyle.BorderLeft = BorderStyle.THIN; cellstyle.BorderRight = BorderStyle.THIN; cellstyle.BorderTop = BorderStyle.THIN; return cellstyle; } /// <summary> /// 合并单元格 /// </summary> /// <param name="sheet">要合并单元格所在的sheet</param> /// <param name="rowstart">开始行的索引</param> /// <param name="rowend">结束行的索引</param> /// <param name="colstart">开始列的索引</param> /// <param name="colend">结束列的索引</param> public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend) { CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend); sheet.AddMergedRegion(cellRangeAddress); } } }
通用Excel样式设置类
using System; using System.Collections.Generic; using System.Linq; using System.Text; using NPOI.SS.UserModel; using System.IO; using System.Data; using NPOI.HSSF.UserModel; using NPOI.XSSF.UserModel; using System.Collections; using NPOI.HSSF.Util; namespace _800Tele.eCRM.BLL.ProExcel { public class ChangeGoodsDetail { /// <summary> /// 将dt里的数据导出为Excel到指定位置 /// </summary> /// <param name="dt">数据源</param> /// <param name="TitleType">标题的格式：(中文标题：Chinese,英文标题：English,中英文双标题：Both)</param> /// <param name="path">导出的数据文件要放目录</param> /// <param name="filename">文件名称。文件名称无效时自动生成一个文件名</param> /// <returns></returns> public string DataTabelExportToExcel(string ReportName, DataTable dt, string path, params object[] s) { HSSFWorkbook book = new HSSFWorkbook(); ISheet sheet = book.CreateSheet(ReportName); ICell cell; #region sheet赋值 //reportname IRow reportname = sheet.CreateRow(0); cell = reportname.CreateCell(1); //ICellStyle cellStype= cell.SetCellValue("原订单信息区"); cell.CellStyle = ExcelStyle.SetCellStyle(book, null, new HSSFColor.LIGHT_YELLOW(), FillPatternType.SOLID_FOREGROUND, new HSSFColor.LIGHT_YELLOW(), HorizontalAlignment.CENTER, VerticalAlignment.TOP); ExcelStyle.SetCellRangeAddress(sheet, 0, 0, 1, 9); cell = reportname.CreateCell(10); cell.SetCellValue("进退货信息区"); cell.CellStyle = ExcelStyle.SetCellStyle(book, null, new HSSFColor.YELLOW(), FillPatternType.SOLID_FOREGROUND, new HSSFColor.YELLOW(), HorizontalAlignment.CENTER, VerticalAlignment.TOP); ExcelStyle.SetCellRangeAddress(sheet, 0, 0, 10, 20); cell = reportname.CreateCell(21); cell.SetCellValue("重发信息区"); cell.CellStyle = ExcelStyle.SetCellStyle(book, null, new HSSFColor.AQUA(), FillPatternType.SOLID_FOREGROUND, new HSSFColor.AQUA(), HorizontalAlignment.CENTER, VerticalAlignment.TOP); //初始化标题 IRow Title1 = sheet.CreateRow(1); foreach (DataColumn column in dt.Columns) { cell = Title1.CreateCell(column.Ordinal); cell.SetCellValue(column.Caption); cell.CellStyle = ExcelStyle.SetCellStyle(book, null, new HSSFColor.PALE_BLUE(), FillPatternType.SOLID_FOREGROUND, new HSSFColor.PALE_BLUE(), HorizontalAlignment.CENTER, VerticalAlignment.TOP); } //导入数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow dataRow = new HSSFRow(); dataRow = sheet.CreateRow(i + 2); foreach (DataColumn column in dt.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(dt.Rows[i][column.Ordinal].ToString()); } } #endregion //处理文件路径 path = path.Trim(); if (path.Substring(path.Length - 1) != "/" && path.Substring(path.Length - 1) != @"\") { path = path + "/"; } if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } string filename = ""; //如果文件名无效，则自动生成一个文件名 if (s == null || s.Length < 1) { filename = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; } else { filename = s[0] as string; } //保存文件 FileStream file = new FileStream(path + filename, FileMode.Create); book.Write(file); file.Close(); return path + filename; } } }
在真实项目中不可能不设置表格样式，这个代码有很多要改的，我这里并没有继承抽象基类，大家可以自己实现；
/// <summary> /// 导出EXCEL /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnExport_Click(object sender, EventArgs e) { ExcelOrdersDetail MED = new ExcelOrdersDetail(); string urlStr = MED.DataTabelExportToExcel("订单明细", OrderExport(), Server.MapPath(@"..\..\UploadFile\OrderFile"), null); Response.ContentType = "application/x-zip-compressed"; Response.AddHeader("Content-Disposition", "attachment;filename=Orders.xls"); string filename = urlStr; Response.TransmitFile(filename); //删除服务器临时表 //if (System.IO.File.Exists(filename)) //{ // System.IO.File.Delete(filename); //} }
注意的是，导出Excel文件必须先存在服务器上，然后才能向client下载Excel表格;Asp.net下载文件有多种方式,上面代码只是一种用法，大家也可以用其它方式实现;
1184
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
