NPOI.dll 下载地址：http://files.cnblogs.com/MyLBlogs/NPOI.zip
因最近做的两个项目里面都用到了NPOI 导出模板Excel ，不胜厌烦，所以在这里简略写一下有关 NPOI导出Excle
NPOI 导出模板Excel 好处:用模板来导出Excel 方便，快捷，易维护。
c# winform 与 asp.net 导出Excel 不同之处在于 导出文件的方法不一样，NPOI 写表格行 与列的时候，还是差不多的 ，关键在于手写PoiUtils。
winform 一下代码 写的是一个对于导出Excel 数据，计算较大的 或者说分组不同，计算也不同的导出Excel方式。
如下：
public class XiaoshouAllSheet { private PoiUtils _poi; private ISheet _sheet; private HSSFWorkbook hssfworkbook; private string templatepath; private string[] Account; private decimal _value1; private int _year; private string _month; private string _Title; private DataTable _CodeTable; private Xiaoshou[] _subaccasses; //本年 private Xiaoshou[] _beforeYearAccess; //上年 private DataTable _YuSuan; //本年本月预算 private Xiaoshou[] _branchSub; //分公司数据 #region 构造函数 public XiaoshouAllSheet(string[] accounts, string title) { this.templatepath = @"\Template\xiaoshouAll.xls"; this._Title = title; this.Account = accounts; _CodeTable = null; _subaccasses = null; _beforeYearAccess = null; } #endregion #region 创建Excel public void CreateReportXls() { #region Excel 表头 if (Global.Account_ERP == (int)EnmFactoryType.悦昌电器) { this._poi.SetCellText(1, 1, "单位名称:" + "悦昌（上海）电器有限公司"); } if (Global.Account_ERP == (int)EnmFactoryType.宏图电器) { this._poi.SetCellText(1, 1, "单位名称:" + "（上海）宏图电器有限公司"); } this._poi.SetCellText(2, 1, "年月:" + _year + "." + _month); #endregion #region 表格标题 _poi.SetCellText(6, 1, "项目"); _poi.SetCellText(6, 2, "本月实际"); _poi.SetCellText(6, 3, "本月预算"); _poi.SetCellText(6, 4, "差异"); _poi.SetCellText(6, 5, "%"); _poi.SetCellText(6, 6, "去年本月"); _poi.SetCellText(6, 7, "差异"); _poi.SetCellText(6, 8, "%"); DataTable _Branch = BalanceSheetDao.getBranch(); //分公司数据库 DataTable profitAndLoss = null; int branch_count = _Branch.Rows.Count; List<string> list_branch = new List<string>(); for (int i = 0; i < branch_count; i++) { this._poi.SetCellText(6, 9 + i, Utils.NvStr(_Branch.Rows[i]["cUnitAbbre"])); } _poi.SetCellText(6, 9 + branch_count, "累计实际"); _poi.SetCellText(6, 10 + branch_count, "累计预算"); _poi.SetCellText(6, 11 + branch_count, "累计差异"); _poi.SetCellText(6, 12 + branch_count, "%"); _poi.SetCellText(6, 13 + branch_count, "累计去年"); _poi.SetCellText(6, 14 + branch_count, "累计差异"); _poi.SetCellText(6, 15 + branch_count, "%"); #endregion #region 表格内容 if (_CodeTable != null && _CodeTable.Rows.Count > 0) { int rowcount = _CodeTable.Rows.Count; int j, i,num; for (i = 0; i < rowcount; i++) { this._poi.SetCellText(7 + i, 1, Utils.NvStr(_CodeTable.Rows[i]["ccode_name"])); //本月实际 this.Calc(Utils.NvStr(_CodeTable.Rows[i]["ccode"]), _subaccasses).SetText(7 + i, 2); //预算 string timeMonth = getTimeMonth(); for(j=0;i<_YuSuan.Rows.Count;j++) { //本月预算 if (Utils.NvStr(_YuSuan.Rows[i]["cid"]) == Utils.NvStr(_CodeTable.Rows[i]["ccode"]) && Utils.NvDecimal(_YuSuan.Rows[i][timeMonth]) != 0) this._poi.SetCellText(7 + i, 3, Utils.NvDecimal(_YuSuan.Rows[i][timeMonth])); } //差异 =本月实际-本月预算 isNull(7 + i, 4, Utils.NvDecimal(this._poi.GetCellText(7 + i, 2)) - Utils.NvDecimal(this._poi.GetCellText(7 + i, 3))); //% this._poi.SetCellText(7 + i, 5, Utils.NvDecimal(this._poi.GetCellText(7 + i, 4)) / Utils.NvDecimal(this._poi.GetCellText(7 + i, 3)) * 100); //去年本月 this.Calc(Utils.NvStr(_CodeTable.Rows[i]["ccode"]), _beforeYearAccess).SetText(7 + i, 6); //差异 isNull(7 + i, 7, Utils.NvDecimal(this._poi.GetCellText(7 + i, 2)) - Utils.NvDecimal(this._poi.GetCellText(7 + i, 6))); //% this._poi.SetCellText(7 + i, 8, Utils.NvDecimal(this._poi.GetCellText(7 + i, 7)) / Utils.NvDecimal(this._poi.GetCellText(7 + i, 6)) * 100); //分公司 for (j = 0; j < branch_count; i++) { list_branch.Clear(); list_branch.Add(Utils.NvStr(Utils.NvStr(_Branch.Rows[i]["cacc_id"])) + "_" + DbBusiness.DbYear); profitAndLoss = ProfitAndLossDao.GetProfitAndLossAll(list_branch.ToArray(), _year); if (profitAndLoss != null) { List<Xiaoshou> list = new List<Xiaoshou>(); for (num = 0; num < profitAndLoss.Rows.Count; num++) { Xiaoshou o = new Xiaoshou(); BeanHelper.DataRowToModel(profitAndLoss.Rows[num], o); list.Add(o); } this._branchSub = list.ToArray(); } //分公司 数据填充 this.Calc(Utils.NvStr(_CodeTable.Rows[i]["ccode"]), _branchSub).SetText(7 + i, 9 + j); } //累计实际 this.Calc2(Utils.NvStr(_CodeTable.Rows[i]["ccode"]), _subaccasses).SetText(7 + i, 10 + branch_count); int monthofday = System.DateTime.Now.Month; decimal total = 0M; //累计预算 for (j = 0; j < _YuSuan.Rows.Count; j++) { if (Utils.NvStr(_YuSuan.Rows[j]["cid"]) == "L01") { total = 0M; for (int k = 2; k < 2 + monthofday; j++) { total += Utils.NvDecimal(_YuSuan.Rows[j][k]); } if (total != 0) this._poi.SetCellText(7 + i, 11 + branch_count, total); } } //累计差异 isNull(7 + i, 12 + branch_count, Utils.NvDecimal(this._poi.GetCellText(7 + i, 10 + branch_count)) - Utils.NvDecimal(this._poi.GetCellText(7 + i, 10 + branch_count))); //% isNull(7 + i, 13 + branch_count, Utils.NvDecimal(this._poi.GetCellText(7 + i, 12 + branch_count)) * 100 / Utils.NvDecimal(this._poi.GetCellText(7 + i, 11 + branch_count))); //累计去年 this.Calc2(Utils.NvStr(_CodeTable.Rows[i]["ccode"]), _beforeYearAccess).SetText(7 + i, 14 + branch_count); //累计差异 isNull(7 + i, 15 + branch_count, Utils.NvDecimal(this._poi.GetCellText(7 + i, 10 + branch_count)) - Utils.NvDecimal(this._poi.GetCellText(7 + i, 14 + branch_count))); //% isNull(7 + i, 16 + branch_count, Utils.NvDecimal(this._poi.GetCellText(7 + i, 15 + branch_count)) * 100 / Utils.NvDecimal(this._poi.GetCellText(7 + i, 14 + branch_count))); } this._poi.SetCellText(7 + rowcount, 1, "合计"); #region 合计 for (i = 0; i < rowcount; i++) { this._poi.SetCellText(7 + rowcount, 2, ""); isNull(7 + rowcount, 2, Utils.NvDecimal(this._poi.GetCellText(7 + i, 2)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 2))); isNull(7 + rowcount, 3, Utils.NvDecimal(this._poi.GetCellText(7 + i, 3)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 3))); isNull(7 + rowcount, 4, Utils.NvDecimal(this._poi.GetCellText(7 + i, 4)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 4))); isNull(7 + rowcount, 5, Utils.NvDecimal(this._poi.GetCellText(7 + i, 5)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 5))); isNull(7 + rowcount, 6, Utils.NvDecimal(this._poi.GetCellText(7 + i, 6)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 6))); isNull(7 + rowcount, 7, Utils.NvDecimal(this._poi.GetCellText(7 + i, 7)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 7))); isNull(7 + rowcount, 8, Utils.NvDecimal(this._poi.GetCellText(7 + i, 8)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 8))); for (j = 0; j < branch_count; j++) { isNull(7 + rowcount, 8 + j, Utils.NvDecimal(this._poi.GetCellText(7 + i, 8 + j)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 8 + j))); } isNull(7 + rowcount, 10 + branch_count, Utils.NvDecimal(this._poi.GetCellText(7 + i, 10 + branch_count)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 10 + branch_count))); isNull(7 + rowcount, 11 + branch_count, Utils.NvDecimal(this._poi.GetCellText(7 + i, 11 + branch_count)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 11 + branch_count))); isNull(7 + rowcount, 12 + branch_count, Utils.NvDecimal(this._poi.GetCellText(7 + i, 12 + branch_count)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 12 + branch_count))); isNull(7 + rowcount, 13 + branch_count, Utils.NvDecimal(this._poi.GetCellText(7 + i, 13 + branch_count)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 13 + branch_count))); isNull(7 + rowcount, 14 + branch_count, Utils.NvDecimal(this._poi.GetCellText(7 + i, 14 + branch_count)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 14 + branch_count))); isNull(7 + rowcount, 15 + branch_count, Utils.NvDecimal(this._poi.GetCellText(7 + i, 15 + branch_count)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 15 + branch_count))); isNull(7 + rowcount, 16 + branch_count, Utils.NvDecimal(this._poi.GetCellText(7 + i, 16 + branch_count)) + Utils.NvDecimal(this._poi.GetCellText(7 + rowcount, 16 + branch_count))); } #endregion } #endregion } #endregion #region 初始化 public void InitData(int year, string month) { _year = year - 1; this._month = month; if (this.Account.Length > 0) { try { _CodeTable = BalanceSheetDao.getXiaoshouCode(); DataTable profitAndLoss = ProfitAndLossDao.GetProfitAndLossAll(this.Account, year); DataTable beforeYearProfitAndLoss = ProfitAndLossDao.GetProfitAndLossAll(this.Account, year-1); _YuSuan = ProfitAndLossDao.GetYuSuan(this.Account, year); if (profitAndLoss != null && beforeYearProfitAndLoss != null) { int num; List<Xiaoshou> list = new List<Xiaoshou>(); for (num = 0; num < profitAndLoss.Rows.Count; num++) { Xiaoshou o = new Xiaoshou(); BeanHelper.DataRowToModel(profitAndLoss.Rows[num], o); list.Add(o); } this._subaccasses = list.ToArray(); list.Clear(); for (num = 0; num < beforeYearProfitAndLoss.Rows.Count; num++) { Xiaoshou o = new Xiaoshou(); BeanHelper.DataRowToModel(beforeYearProfitAndLoss.Rows[num], o); list.Add(o); } this._beforeYearAccess = list.ToArray(); this.templatepath = Utils.GetStartupPath() + this.templatepath; string name = "销售费用(合并)"; FileStream s = new FileStream(this.templatepath, FileMode.Open, FileAccess.Read); this.hssfworkbook = new HSSFWorkbook(s); this._sheet = this.hssfworkbook.CloneSheet(this.hssfworkbook.GetSheetIndex(name)); this._poi = new PoiUtils(this.hssfworkbook, this._sheet, 8); //this.CreateReportXls(Utils.NvInt(month)); this.CreateReportXls(); this.hssfworkbook.RemoveSheetAt(this.hssfworkbook.GetSheetIndex(name)); for (num = 0; num < this.hssfworkbook.NumberOfSheets; num++) { this.hssfworkbook.GetSheetAt(num).ForceFormulaRecalculation = true; } s.Close(); } } catch (Exception exception) { Log.Error(exception); } } } #endregion #region 取一般数据（Calc） private XiaoshouAllSheet Calc(string code, Xiaoshou[] subaccess) { this._value1 = 0M; return this.Add(code, subaccess); } private XiaoshouAllSheet Add(string code, Xiaoshou[] subaccess) { return this.AddByRatio(code, 1, subaccess); } private XiaoshouAllSheet AddByRatio(string code, int ratio, Xiaoshou[] subaccess) { Func<Xiaoshou, decimal> func = x => x.bProperty ? (x.md) : (x.mc); Xiaoshou[] lossArray = (from x in subaccess where x.cCode.StartsWith(code) select x).ToArray<Xiaoshou>(); decimal num = ratio; int month =Utils.NvInt(this._month); this._value1 += Enumerable.Sum<Xiaoshou>(from x in lossArray where (x.iPeriod == month) select x, func) * num; return this; } #endregion #region 累计数据（Calc2） private XiaoshouAllSheet Calc2(string code, Xiaoshou[] subaccess) { this._value1 = 0M; return this.Add2(code, subaccess); } private XiaoshouAllSheet Add2(string code, Xiaoshou[] subaccess) { return this.AddByRatio2(code, 1, subaccess); } private XiaoshouAllSheet AddByRatio2(string code, int ratio, Xiaoshou[] subaccess) { Func<Xiaoshou, decimal> func = x => x.bProperty ? (x.md) : (x.mc); Xiaoshou[] lossArray = (from x in subaccess where x.cCode.StartsWith(code) select x).ToArray<Xiaoshou>(); decimal num = ratio; int month = Utils.NvInt(this._month); this._value1 += Enumerable.Sum<Xiaoshou>(from x in lossArray where (x.iPeriod <= month && x.iPeriod > 0) select x, func) * num; return this; } #endregion #region OnSaveToXls /// <summary> /// 保存到Excel /// </summary> public void OnSaveToXls(string filename) { string str; if (!Directory.Exists(@"D:\temp")) { Directory.CreateDirectory(@"D:\temp"); } str = @"D:\temp\" + filename + ".xls"; if (File.Exists(str)) { File.Delete(str); } FileStream stream = new FileStream(str, FileMode.Create); this.hssfworkbook.Write(stream); stream.Close(); System.Diagnostics.Process.Start(str); } #endregion #region SetText /// <summary> /// 设置单元格的数据 /// </summary> /// <param name="row"></param> /// <param name="col"></param> private void SetText(int row, int col) { if (this._value1 != 0) { this._poi.SetCellText(row, col, this._value1); } } private void SetText(int row, int col, decimal value) { if (this._value1 != 0) { this._poi.SetCellText(row, col, this._value1); } } #endregion #region getTimeMonth /// <summary> /// 获取本年本月对应的数据 /// </summary> /// <returns></returns> public string getTimeMonth() { int thisMonth = Utils.NvInt(this._month); if (thisMonth == (int)EnumMonth.Jan) { return "m1"; } if (thisMonth == (int)EnumMonth.Feb) { return "m2"; } if (thisMonth == (int)EnumMonth.Mar) { return "m3"; } if (thisMonth == (int)EnumMonth.Apr) { return "m4"; } if (thisMonth == (int)EnumMonth.May) { return "m5"; } if (thisMonth == (int)EnumMonth.Jan) { return "m6"; } if (thisMonth == (int)EnumMonth.July) { return "m7"; } if (thisMonth == (int)EnumMonth.Aug) { return "m8"; } if (thisMonth == (int)EnumMonth.Sept) { return "m9"; } if (thisMonth == (int)EnumMonth.Oct) { return "m10"; } if (thisMonth == (int)EnumMonth.Nov) { return "m11"; } if (thisMonth == (int)EnumMonth.Dec) { return "m12"; } return ""; } #endregion #region isNull public void isNull(int row, int colc, decimal value) { if (value != 0) { this._poi.SetCellText(row, colc, value); } } #endregion } internal class Xiaoshou { public bool bProperty { get; set; } public string cCode { get; set; } public int iPeriod { get; set; } public decimal mc { get; set; } public decimal md { get; set; } public string cCCCode { get; set; } }
NPOI 导出的内容可以自己定义。
在这里我要说一下，asp.net 导出Excel 是按照一个模板，导出的数据，可以从很多地方取数，方便了用繁杂的sql 一次导出， 而且还可以将一个页面上面的所有数据都导出来。导出的内容可以自己定义。
winform 导出来的内容侧重于计算，是将所有的数据以一个简单的sql 取出来，然后通过代码去将取出来的数据按照需求分开， 这里适合做导出复杂报表。 而且合计，小计，等公式也可以使用,可以自己写代码尝试。
//poiUtils NPOI 精华尽在 PoiUtils 中，可以自己看，不会没关系，直接调用就行。 public class PoiUtils { private ISheet _sheet; public ISheet WorkSheet { get { return _sheet; } set { _sheet = value; } } private HSSFWorkbook _workbook; public HSSFWorkbook Workbook { get { return _workbook; } set { _workbook = value; } } private int _currentrow; /// <summary> /// 当前行 /// </summary> public int CurrentRow { get { return _currentrow; } set { _currentrow = value; } } private int _currentcol; /// <summary> /// 当前列 /// </summary> public int CurrentCol { get { return _currentcol; } set { _currentcol = value; } } private HSSFFormulaEvaluator _evaluator; /// <summary> /// 列数 /// </summary> public int ColsCount { get; set; } public PoiUtils(HSSFWorkbook workbook, ISheet sheet, int colsCount) { _workbook = workbook; _sheet = sheet; ColsCount = colsCount; } /// <summary> /// 设置sheet的名称 /// </summary> /// <param name="sheetName"></param> public void SetSheetName(string sheetName) { _workbook.SetSheetName(_workbook.GetSheetIndex(_sheet), sheetName); } /// <summary> /// 隐藏列 /// </summary> /// <param name="col"></param> public void HideColumn(int col) { _sheet.SetColumnHidden(col - 1, true); } /// <summary> /// sheet克隆 /// </summary> /// <param name="sheetName"></param> /// <param name="source"></param> /// <returns></returns> public PoiUtils CloneSheet(string sheetName, PoiUtils source) { ISheet sheet = source.Workbook.CloneSheet(source.Workbook.GetSheetIndex(source.WorkSheet)); source.Workbook.SetSheetName(source.Workbook.GetSheetIndex(sheet), sheetName); return new PoiUtils(source.Workbook, sheet, source.ColsCount); } /// <summary> /// 创建 用于自定义样式 /// </summary> /// <returns></returns> public IDataFormat CreateDataFormat() { return _workbook.CreateDataFormat(); } /// <summary> /// 创建单元格样式 /// </summary> /// <returns></returns> public ICellStyle CreateCellStyle() { return _workbook.CreateCellStyle(); } /// <summary> /// 创建字体样式 /// </summary> /// <returns></returns> public IFont CreateFont() { return _workbook.CreateFont(); } /// <summary> /// 克隆单元格样式 /// </summary> /// <param name="source"></param> /// <returns></returns> public ICellStyle CloneCellStyle(ICellStyle source) { ICellStyle cellstyle = CreateCellStyle(); cellstyle.CloneStyleFrom(source); return cellstyle; } public ICellStyle GetCellStyle(int row, int col) { int iRow = CurrentRow; int iCol = CurrentCol; ICellStyle cellStyle = GetCell(row, col).CellStyle; CurrentRow = iRow; CurrentCol = iCol; return cellStyle; } public void SetCellStyle(ICellStyle fromCellStyle) { SetCellStyle(CurrentRow, CurrentCol, fromCellStyle); } public void SetCellStyle(int fromRow, int fromCol, int toRow, int toCol) { SetCellStyle(toRow, toCol, GetCellStyle(fromRow, fromCol)); } public void SetCellStyle(int toRow, int toCol, ICellStyle fromCellStyle) { GetCell(toRow, toCol).CellStyle = fromCellStyle; } //private void InitCellstyle() //{ // //百分比样式 // PercentCellStyle = _workbook.CreateCellStyle(); // PercentCellStyle.BorderBottom = CellBorderType.THIN; // PercentCellStyle.BorderLeft = CellBorderType.THIN; // PercentCellStyle.BorderRight = CellBorderType.THIN; // PercentCellStyle.BorderTop = CellBorderType.THIN; // PercentCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%"); // PercentCellStyle.Alignment = HorizontalAlignment.CENTER; // //小数样式 // DecimalCellStyle = _workbook.CreateCellStyle(); // DecimalCellStyle.BorderBottom = CellBorderType.THIN; // DecimalCellStyle.BorderLeft = CellBorderType.THIN; // DecimalCellStyle.BorderRight = CellBorderType.THIN; // DecimalCellStyle.BorderTop = CellBorderType.THIN; // DecimalCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); // DecimalCellStyle.Alignment = HorizontalAlignment.CENTER; // //标题样式（居中对齐，自动换行） // HeaderCellStyle = _workbook.CreateCellStyle(); // HeaderCellStyle.BorderBottom = CellBorderType.THIN; // HeaderCellStyle.BorderLeft = CellBorderType.THIN; // HeaderCellStyle.BorderRight = CellBorderType.THIN; // HeaderCellStyle.BorderTop = CellBorderType.THIN; // HeaderCellStyle.Alignment = HorizontalAlignment.CENTER; // HeaderCellStyle.VerticalAlignment = VerticalAlignment.CENTER; // HeaderCellStyle.WrapText = true; // //文本样式 // TextCellStyle = _workbook.CreateCellStyle(); // TextCellStyle.BorderBottom = CellBorderType.THIN; // TextCellStyle.BorderLeft = CellBorderType.THIN; // TextCellStyle.BorderRight = CellBorderType.THIN; // TextCellStyle.BorderTop = CellBorderType.THIN; // TextCellStyle.Alignment = HorizontalAlignment.LEFT; //} public void SetCellStyle(int row, string col, HSSFCellStyle cellstyle) { SetCellStyle(row, ToNumber(col), cellstyle); } /// <summary> /// 设置单元格样式 /// </summary> /// <param name="row"></param> /// <param name="col"></param> /// <param name="cellstyle"></param> public void SetCellStyle(int row, int col, HSSFCellStyle cellstyle) { GetCell(row, col).CellStyle = cellstyle; } /// <summary> /// 取得单元格 /// </summary> /// <param name="row"></param> /// <param name="col"></param> /// <returns></returns> public ICell GetCell(int row, int col) { CurrentRow = row; CurrentCol = col; IRow rowObj = _sheet.GetRow(row - 1); if (col > ColsCount) { ColsCount = col; } if (rowObj == null) { CreateRow(row); rowObj = _sheet.GetRow(row - 1); } ICell cellObj = rowObj.GetCell(col - 1); if (cellObj == null) { rowObj.CreateCell(col - 1); cellObj = rowObj.GetCell(col - 1); } return cellObj; } /// <summary> /// 取得单元格 /// </summary> /// <param name="row"></param> /// <param name="colName">列的字母名称</param> /// <returns></returns> public ICell GetCell(int row, string colName) { return GetCell(row, ToNumber(colName)); } #region 设置单元格内容 /// <summary> /// 设置单元格内容 /// </summary> /// <param name="row"></param> /// <param name="colName"></param> public void SetCellText(int row, string colName, object value) { int col = ToNumber(colName); SetCellText(row, col, value); } /// <summary> /// 设置单元格内容 /// </summary> /// <param name="row"></param> /// <param name="col"></param> /// <param name="value"></param> public void SetCellText(int row, int col, object value) { if (value == null) return; Type t = value.GetType(); ICell cell = GetCell(row, col); if (t == typeof(decimal) || t == typeof(double) || t == typeof(Int32) || t == typeof(Int64)) { cell.SetCellValue(Utils.NvDouble(value)); } else if (t == typeof(DateTime)) { cell.SetCellValue(Convert.ToDateTime(value)); } else { //cell.SetCellValue(Utils.NvStr(value)); cell.SetCellValue(Convert.ToString(value)); } } /// <summary> /// 设置单元格内容及样式 /// </summary> /// <param name="row"></param> /// <param name="col"></param> /// <param name="value"></param> /// <param name="cellStyle"></param> public void SetCellTextStyle(int row, int col, object value, HSSFCellStyle cellStyle) { SetCellText(row, col, value); SetCellStyle(row, col, cellStyle); } /// <summary> /// 设置单元格公式 /// </summary> /// <param name="row"></param> /// <param name="colName"></param> /// <param name="formula"></param> public void SetCellFormula(int row, string colName, string formula) { int col = ToNumber(colName); SetCellFormula(row, col, formula); } public void SetCellFormula(int row, int col, string formula) { GetCell(row, col).CellFormula = formula; } /// <summary> /// 设置单元格公式及样式 /// </summary> /// <param name="row"></param> /// <param name="col"></param> /// <param name="value"></param> /// <param name="cellStyle"></param> public void SetCellFormulaStyle(int row, int col, string formula, HSSFCellStyle cellStyle) { SetCellFormula(row, col, formula); SetCellStyle(row, col, cellStyle); } #endregion #region 取得单元格内容 /// <summary> /// 取得单元格内容 /// </summary> /// <param name="row">行号，从1开始</param> /// <param name="colName">列序号(A,B,C...)</param> /// <returns></returns> public string GetCellText(int row, string colName) { int col = ToNumber(colName); return GetCellText(row, col); } /// <summary> /// 取得单元格内容 /// </summary> /// <param name="row">行数，从1开始</param> /// <param name="col">列数，从1开始</param> /// <returns></returns> /// <remarks> /// 注意事项 /// 1，当Excel中这个单元格的文字格式是日期时，返回的将是一串数字， /// 如果是读取日期值的，请改用GetCellDateValue()方法 /// </remarks> public string GetCellText(int row, int col) { //用不惯POI啊。GetRow会返回null IRow hssfrow = _sheet.GetRow(row - 1); if (hssfrow == null) return ""; ICell cell = hssfrow.GetCell(col - 1); if (cell == null) return ""; if (cell.CellType == CellType.NUMERIC) { if (DateUtil.IsCellDateFormatted(cell)) { //日期型 return cell.DateCellValue.ToString("yyyy-MM-dd"); } else //数字型 return cell.NumericCellValue.ToString(); } else if (cell.CellType == CellType.STRING) { return cell.StringCellValue.Trim(); } else if (cell.CellType == CellType.BOOLEAN) { return cell.BooleanCellValue == true ? "true" : "false"; } else if (cell.CellType == CellType.FORMULA) { return GetCalcValue(row, col).ToString(); } return ""; } /// <summary> /// 取得单元格的日期值 /// </summary> /// <param name="row"></param> /// <param name="col"></param> public DateTime? GetCellDateValue(int row, int col) { IRow hssfrow = _sheet.GetRow(row - 1); if (hssfrow == null) return null; ICell cell = hssfrow.GetCell(col - 1); if (cell == null) return null; if (cell.CellType == CellType.NUMERIC) { return cell.DateCellValue; } return null; } /// <summary> /// 获取公式计算后的值 /// </summary> /// <param name="row"></param> /// <param name="col"></param> /// <returns></returns> /// <remarks>适用场景：用npoi生成Excel时，获取单元格经公式计算后的值时使用</remarks> public decimal GetCalcValue(int row, int col) { if (_evaluator == null) if (_workbook != null) _evaluator = new HSSFFormulaEvaluator(_workbook); if (_evaluator == null) return 0; ICell cell = GetCell(row, col); return Utils.NvDecimal(_evaluator.Evaluate(cell).NumberValue); } #endregion /// <summary> /// 合并单元格 /// </summary> /// <param name="startRow">起始行</param> /// <param name="startCol">起始列</param> /// <param name="endRow">结束行</param> /// <param name="endCol">结束列</param> public void MergedRegion(int startRow, int startCol, int endRow, int endCol) { _sheet.AddMergedRegion(new CellRangeAddress(startRow - 1, endRow - 1, startCol - 1, endCol - 1)); } /// <summary> /// 在当前行插入一行（也就是把当前行移到下一行） /// </summary> /// <returns></returns> public void InsertRow() { _sheet.ShiftRows(CurrentRow - 1, _sheet.LastRowNum, 1); IRow row = _sheet.GetRow(CurrentRow - 1); for (int i = 0; i < ColsCount; i++) { row.CreateCell(i); } } #region CreateRow 在当前行之后新增一行 /// <summary> /// 添加新行 /// </summary> /// <returns></returns> public IRow CreateRow() { return CreateRow(++CurrentRow); } /// <summary> /// 添加新行 /// </summary> /// <param name="row"></param> /// <returns></returns> public IRow CreateRow(int row) { return CreateRow(row, null); } /// <summary> /// 添加新行，并拷贝指定行的样式到新行上 /// </summary> /// <param name="row"></param> /// <param name="copyRowStyleFrom"></param> /// <returns></returns> public IRow CreateRow(int row, int copyRowStyleFrom) { IRow hssfRow = CreateRow(row); CurrentRow = row; IRow hssfSourceRow = _sheet.GetRow(copyRowStyleFrom - 1); for (int i = 0; i < ColsCount; i++) { hssfRow.GetCell(i).CellStyle = hssfSourceRow.GetCell(i).CellStyle; } return hssfRow; } public IRow CreateRow(int row, ICellStyle cellstyle) { IRow hssfRow = _sheet.CreateRow(row - 1); CurrentRow = row; for (int i = 0; i < ColsCount; i++) { hssfRow.CreateCell(i); if (cellstyle != null) { GetCell(row, i + 1).CellStyle = cellstyle; } } return hssfRow; } #endregion /// <summary> /// 设置列宽 /// </summary> /// <param name="col"></param> /// <param name="width"></param> /// <remarks>poi设置后的列宽要比给定的width要小0.71，所以适当放大</remarks> public void SetColumnWidth(string col, int width) { SetColumnWidth(ToNumber(col), width); } /// <summary> /// 设置列宽 /// </summary> /// <param name="col"></param> /// <param name="width"></param> public void SetColumnWidth(int col, int width) { //_sheet.SetColumnWidth(col - 1, width * 256); _sheet.SetColumnWidth(col - 1, width*256); } /// <summary> /// 设置行高 /// </summary> /// <param name="row"></param> /// <param name="height"></param> public void SetRowHeight(int row, int height) { _sheet.SetColumnWidth(row - 1, height * 256); } #region 静态方法 /// <summary> /// 取得某一列多个行的合计公式 /// </summary> /// <param name="rowList">参与合计的行的集合</param> /// <returns></returns> public static string GetTotalFormula(List<int> rowList) { string ret = ""; for (int i = 0; i < rowList.Count; i++) { if (ret.Length > 0) { ret += "+"; } //{0}是列的占位符 ret += "{0}" + rowList[i].ToString(); } return ret; } /// <summary> /// HSSFRow Copy Command /// /// Description: Inserts a existing row into a new row, will automatically push down /// any existing rows. Copy is done cell by cell and supports, and the /// command tries to copy all properties available (style, merged cells, values, etc...) /// </summary> /// <param name="poiTo">目标PoiUtils对象</param> /// <param name="poiFrom">源PoiUtils对象</param> /// <param name="sourceRowNum">源样式的行数</param> /// <param name="destinationRowNum">目标行</param> public static void CopyRow(PoiUtils poiTo, PoiUtils poiFrom, int sourceRowNum, int destinationRowNum) { // Get the source / new row IRow newRow = poiTo.WorkSheet.GetRow(destinationRowNum - 1); IRow sourceRow = poiFrom.WorkSheet.GetRow(sourceRowNum - 1); // Loop through source columns to add to new row for (int i = 0; i < sourceRow.LastCellNum; i++) { // Grab a copy of the old/new cell ICell oldCell = sourceRow.GetCell(i); //ICell newCell = newRow.CreateCell(i); ICell newCell = newRow.GetCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } //模板cell有，目标cell没有 if (newCell == null) { newCell = newRow.CreateCell(i); } //edit start by qiulc 2012/7/21 这个不适合生成内容较多的表单（poi只能生成4000个样式） // Copy style from old cell and apply to new cell //ICellStyle newCellStyle = poiTo.CreateCellStyle(); //newCellStyle.CloneStyleFrom(oldCell.CellStyle); ; //newCell.CellStyle = newCellStyle; newCell.CellStyle = oldCell.CellStyle; //edit end by qiulc 2012/7/21 // If there is a cell comment, copy if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment; // If there is a cell hyperlink, copy if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink; // Set the cell data type newCell.SetCellType(oldCell.CellType); } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < poiFrom.WorkSheet.NumMergedRegions; i++) { CellRangeAddress cellRangeAddress = poiFrom.WorkSheet.GetMergedRegion(i); if (cellRangeAddress.FirstRow == sourceRow.RowNum) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum, (newRow.RowNum + (cellRangeAddress.LastRow - cellRangeAddress.FirstRow)), cellRangeAddress.FirstColumn, cellRangeAddress.LastColumn); poiTo.WorkSheet.AddMergedRegion(newCellRangeAddress); } } } /// <summary> /// 用于excel表格中列号字转成数字，返回的列号索引从1开始 /// </summary> /// <param name="columnName"></param> /// <returns></returns> public static int ToNumber(string columnName) { if (!Regex.IsMatch(columnName.ToUpper(), @"[A-Z]+")) throw new Exception("Invalid parameter"); int index = 0; char[] chars = columnName.ToUpper().ToCharArray(); for (int i = 0; i < chars.Length; i++) { index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1); } return index; } #endregion }
180
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
