NPOI 导出Excel 模板 asp.net

NPOI.dll 下载地址

 https://files.cnblogs.com/MyLBlogs/NPOI.zip

 

1、npoi 导出是以一个模板 Excel 导出，asp.net 中以文件流的形式将模板中的Excel 从服务器上面下载下来。所以必须要有模板

 

2、npoi 导出

using (FileStream fs = new FileStream(HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath + "/" + SALARY_TEMPLET), FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                POIFSFileSystem poifs = new POIFSFileSystem(fs);
                HSSFWorkbook workbook = new HSSFWorkbook(poifs);
                ISheet sheet = workbook.GetSheetAt(0);
                var poi = new PoiUtils(workbook, sheet);

                #region 填充单元格

		//填充数据
		//通过poi 的setCellText 来添加表格内容。
		//12 第12 行，8：第8 列,"内容"：表格内容;   表格是以第一个单元格为第一行，第一列 即（1,1）
		poi.SetCellText(12, 8, "内容");
		
		//给 第一行,B列 赋值为 1; 	赋值可以为object 类型，显示的时候，可以控制模板的单元格样式，或自己手写样式添加到poi 中。
                poi.SetCellText(1, "B", 1);

		//获取单元格内容
                string cell1Text=  poi.GetCellText(1, 1);

		//添加新行
		poi.CreateRow();
		
		//在irow+2 行后面添加新行，新行的样式拷贝于 第21 行行样式，这个方法也可以自己写行样式。
                poi.ShiftRow(irow + 1, 21);

		//poi 的方法在  poiUtils 中方法太多，可以自己看，就不一一举例了。

		#endregion

		using (MemoryStream Stream = new MemoryStream())
                {
                    workbook.Write(Stream);
                    Response.ContentType = "application/x-msdownload";
                    Response.Charset = "";
                    Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("导出Excel_" + year + month + ".xls", System.Text.Encoding.UTF8));
                    Response.BinaryWrite(Stream.GetBuffer());
                    Response.End();
                }
            }

　　

 3、//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;
        }

        public PoiUtils(HSSFWorkbook workbook, ISheet sheet)
        {
            _workbook = workbook;
            _sheet = sheet;
        }

        /// <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, Utils.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, Utils.ToNumber(colName));
        }

        #region 设置单元格内容
        /// <summary>
        /// 设置单元格内容
        /// </summary>
        /// <param name="row"></param>
        /// <param name="colName"></param>
        public void SetCellText(int row, string colName, object value)
        {
            int col = Utils.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 = Utils.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 = Utils.ToNumber(colName);
            return GetCellText(row, col);
        }

        /// <summary>
        /// 取得单元格内容
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="row">行数，从1开始</param>
        /// <param name="col">列数，从1开始</param>
        /// <returns></returns>
        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>
        /// <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 IRow CreateRow()
        {
            return CreateRow(++CurrentRow);
        }

        /// <summary>
        /// 添加新行
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public IRow CreateRow(int row)
        {

            return CreateRow(row, null);
        }

        /// <summary>
        /// 在当前行插入一行（把当前行移到下一行）
        /// </summary>
        /// <returns></returns>
        public void ShiftRow()
        {
            _sheet.ShiftRows(CurrentRow - 1, _sheet.LastRowNum, 1);
            IRow row = _sheet.GetRow(CurrentRow - 1);

            for (int i = 0; i < ColsCount; i++)
            {
                row.CreateCell(i);
            }
        }

        /// <summary>
        /// 在当前行插入一行,并拷贝指定行的样式到新行上
        /// </summary>
        /// <param name="row"></param>
        /// <param name="copyRowStyleFrom"></param>
        /// <returns></returns>
        public IRow ShiftRow(int row, int copyRowStyleFrom) 
        {
            _sheet.ShiftRows(row, _sheet.LastRowNum, 1);
            IRow newrow = _sheet.GetRow(row);
            IRow hssfSourceRow = _sheet.GetRow(copyRowStyleFrom - 1);
            for (int i = 0; i < hssfSourceRow.Cells.Count; i++)
            {
                newrow.CreateCell(i);
            }


            for (int i = 1; i < hssfSourceRow.Cells.Count; i++)
            {
                newrow.GetCell(i).CellStyle = hssfSourceRow.GetCell(i).CellStyle;
            }
            return newrow;
        }

        /// <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 = 1; 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;
        }

        /// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="col"></param>
        /// <param name="width"></param>
        /// <remarks>poi设置后的列宽要比给定的width要小0.71，所以适当放大</remarks>
        public void SetColumnWidth(string col, int width)
        {
            SetColumnWidth(Utils.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);
        }

        /// <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>
        /// 
        /// </summary>
        /// <param name="title"></param>
        /// <param name="titleColNum">行号从1开始</param>
        /// <returns></returns>
        public string GetMergeCellText(string title, int titleColNum)
        {
            bool searchEnd = false;
            int mergeCellStartRow = 0;
            int mergeCellEndRow = 0;
            for (int i = _sheet.FirstRowNum; i <= _sheet.LastRowNum; i++)
            {
                string val = GetCellText(i, titleColNum);

                if (searchEnd)
                {
                    if (!string.IsNullOrEmpty(val) && !val.Equals(title))
                    {
                        mergeCellEndRow = i - 1;
                        break;
                    }
                }
                else
                {
                    if (val.Equals(title))
                    {
                        mergeCellStartRow = i + 1;
                        searchEnd = true;
                        continue;
                    }
                }

            }
            return GetCellTextMultiRow(mergeCellStartRow, mergeCellEndRow, titleColNum + 1);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="title"></param>
        /// <param name="titleColNum">行号从1开始</param>
        /// <param name="maxLength"></param>
        /// <returns></returns>
        public string GetMergeCellText(string title, int titleColNum, int maxLength)
        {
            return Utils.GetSubString(GetMergeCellText(title, titleColNum), maxLength);
        }

        /// <summary>
        /// 读取单列一排的数据
        /// </summary>
        /// <param name="fromRow"></param>
        /// <param name="toRow"></param>
        /// <param name="colNum"></param>
        /// <returns></returns>
        public string GetCellTextMultiRow(int fromRow, int toRow, int colNum)
        {
            string result = "";
            for (int i = fromRow; i <= toRow; i++)
            {
                result += GetCellText(i, colNum);
            }
            return result;
        }

        public string GetCellText(string title, int titleColNum)
        {
            for (int i = _sheet.FirstRowNum; i <= _sheet.LastRowNum; i++)
            {
                if (GetCellText(i, titleColNum).Equals(title))
                {
                    return GetCellText(i, titleColNum + 1);
                }

            }

            return "";
        }

        public string GetCellText(string title, int titleColNum, int maxLength)
        {
            return Utils.GetSubString(GetCellText(title, titleColNum), maxLength);
        }


        public string GetCellText(int row, int col, int maxLength)
        {
            return Utils.GetSubString(GetCellText(row, col), maxLength);
        }
    }