erp/SiteCore/ReportBaseX.cs

using System;
using System.IO;
using System.Web;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;

namespace SiteCore
{

    public class NpoiMemoryStream : MemoryStream
    {
        public NpoiMemoryStream()
        {
            AllowClose = true;
        }

        public bool AllowClose { get; set; }

        public override void Close()
        {
            if (AllowClose)
                base.Close();
        }
    }

    /// <summary>
    /// ReportBase 的摘要说明
    /// </summary>
    public class ReportBaseX : BasePage
    {
        public ReportBaseX()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        public string excelName = "";
        public int AIdx = 65;
        public SXSSFWorkbook xssfworkbook;
        public ISheet iSheet;

        #region custom_style
        ICellStyle headStyle = null;
        ICellStyle moneyStyle = null;
        private ICellStyle GetHeadStyle()
        {
            if (headStyle == null)
            {
                headStyle = xssfworkbook.CreateCellStyle();
                IFont font = xssfworkbook.CreateFont();
                font.FontName = "宋体";
                font.FontHeightInPoints = 9;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                headStyle.Alignment = HorizontalAlignment.Center;
                headStyle.VerticalAlignment = VerticalAlignment.Center;
            }
            return headStyle;
        }

        private ICellStyle GetMoneyStyle()
        {
            if (moneyStyle == null)
            {
                moneyStyle = xssfworkbook.CreateCellStyle();
                IDataFormat format = xssfworkbook.CreateDataFormat();
                //moneyStyle.DataFormat = format.GetFormat("_(* #,##0.00_);_(* (#,##0.00);_(* \" - \"??_);_(@_)");
                moneyStyle.DataFormat = format.GetFormat("#,##0.0##");
            }
            return moneyStyle;
        }
        #endregion


        #region Excel自定义方法

        public string getExcelChar(int idx)
        {
            string perv = "";
            if (idx > 26)
            {
                idx = idx - 26;
                perv = "A";
            }
            return perv + Convert.ToChar(idx + AIdx).ToString();
        }

        //创建标题头
        public void createHeadCell(IRow row, int cIdx, string key)
        {
            ICell cell = row.CreateCell(cIdx);
            cell.CellStyle = GetHeadStyle();
            cell.SetCellValue(key);
        }

        //竖着求和
        public void setVSumCell(int rIdx, int cIdx, int s, int e)
        {
            string c = Convert.ToChar(cIdx + AIdx).ToString();
            if (iSheet.GetRow(rIdx).GetCell(cIdx) == null)
                iSheet.GetRow(rIdx).CreateCell(cIdx);
            setFormulaCell(rIdx, cIdx, string.Format("SUM({0}{1}:{0}{2})", c, s, e));
        }
        //横着求和
        public void setHSumCell(int rIdx, int cIdx, int s, int e)
        {
            string sc = Convert.ToChar((s - 1) + AIdx).ToString();
            string ec = Convert.ToChar((e - 1) + AIdx).ToString();
            setFormulaCell(rIdx, cIdx, string.Format("SUM({0}{1}:{2}{1})", sc, rIdx + 1, ec));
        }
        //竖着相乘
        public void setVMultCell(int rIdx, int cIdx, int s, int e)
        {
            string c = Convert.ToChar(cIdx + AIdx).ToString();
            setFormulaCell(rIdx, cIdx, string.Format("{0}{1}*{0}{2}", c, s, e));
        }
        //横着相乘
        public void setHMultCell(int rIdx, int cIdx, int s, int e)
        {
            string sc = Convert.ToChar((s - 1) + AIdx).ToString();
            string ec = Convert.ToChar((e - 1) + AIdx).ToString();
            setFormulaCell(rIdx, cIdx, string.Format("{0}{1}*{2}{1}", sc, rIdx + 1, ec));
        }
        public void setFormulaCell(int rIdx, int cIdx, string v)
        {
            iSheet.GetRow(rIdx).GetCell(cIdx).SetCellFormula(v);
        }
        public void setStringCell(int rIdx, int cIdx, object v)
        {
            if (v == null) return;
            if (iSheet.GetRow(rIdx).GetCell(cIdx) == null)
                iSheet.GetRow(rIdx).CreateCell(cIdx).SetCellValue(v.ToString());
            else
                iSheet.GetRow(rIdx).GetCell(cIdx).SetCellValue(v.ToString());
        }
        public void setMoneyCell(int rIdx, int cIdx, object v)
        {
            ICell cell = iSheet.GetRow(rIdx).GetCell(cIdx);
            cell.CellStyle = GetMoneyStyle();
            cell.SetCellValue(Convert.ToDouble(v));
        }
        public void createMoneyCell(IRow row, int cIdx, object v)
        {
            ICell cell = row.CreateCell(cIdx);
            cell.CellStyle = GetMoneyStyle();
            cell.SetCellValue(Convert.ToDouble(v));
        }
        public void setMoneyCell(IRow row, int cIdx, object v)
        {
            ICell cell = row.GetCell(cIdx);
            cell.CellStyle = GetMoneyStyle();
            cell.SetCellValue(Convert.ToDouble(v));
        }
        public void setRichTextCell(int rIdx, int cIdx, IRichTextString v)
        {
            iSheet.GetRow(rIdx).GetCell(cIdx).SetCellValue(v);
        }
        public void setDoubleCell(IRow row, int cIdx, object v)
        {
            if (v.ToString() == "" || v.ToString() == "0") return;
            row.GetCell(cIdx).SetCellValue(Convert.ToDouble(v));
        }
        public void createDoublCell(IRow row, int cIdx, object v)
        {
            if (v.ToString() == "" || v.ToString() == "0") return;
            row.CreateCell(cIdx).SetCellValue(Convert.ToDouble(v));
        }
        public void setDoubleCell(int rIdx, int cIdx, object v)
        {
            if (v.ToString() == "" || v.ToString() == "0") return;
            if (iSheet.GetRow(rIdx).GetCell(cIdx) == null)
                iSheet.GetRow(rIdx).CreateCell(cIdx).SetCellValue(Convert.ToDouble(v));
            else
                iSheet.GetRow(rIdx).GetCell(cIdx).SetCellValue(Convert.ToDouble(v));
        }
        public void setDoubleCellWithZero(int rIdx, int cIdx, object v)
        {
            if (v.ToString() == "") return;
            iSheet.GetRow(rIdx).GetCell(cIdx).SetCellValue(Convert.ToDouble(v));
        }
        public string getStringCell(int rIdx, int cIdx)
        {
            return iSheet.GetRow(rIdx).GetCell(cIdx).StringCellValue;
        }
        public IRichTextString GetRichStringCell(int rIdx, int cIdx)
        {
            return iSheet.GetRow(rIdx).GetCell(cIdx).RichStringCellValue;
        }
        public double getDoubleCell(int rIdx, int cIdx)
        {
            return iSheet.GetRow(rIdx).GetCell(cIdx).NumericCellValue;
        }

        //复制行
        public void CopyRowCells(int lastIdx, int sIdx)
        {
            for (int i = 0; i < lastIdx; i++)
            {
                iSheet.CopyRow(i, sIdx++);
                IRow row = iSheet.GetRow(i);
                if (row != null)
                {
                    IRow copyRow = iSheet.GetRow(sIdx - 1);
                    if (copyRow != null) copyRow.Height = row.Height;
                }
            }
        }
        //删除行
        public void removeRow(int rowIndex)
        {
            int lastRowNum = iSheet.LastRowNum;
            if (rowIndex >= 0 && rowIndex < lastRowNum)
            {
                iSheet.ShiftRows(rowIndex + 1, lastRowNum, -1);
            }
            if (rowIndex == lastRowNum)
            {
                IRow removingRow = iSheet.GetRow(rowIndex);
                if (removingRow != null)
                {
                    iSheet.RemoveRow(removingRow);
                }
            }
        }

        public void insertNoStyleRow(ISheet sheet, int rowIdx, int rowNum, IRow sourceRow)
        {
            //批量移动行
            sheet.ShiftRows(rowIdx, sheet.LastRowNum, rowNum, true, false);
            //对批量移动后空出的空行插，创建相应的行，并以插入行的上一行为格式源
            for (int i = rowIdx; i < rowIdx + rowNum - 1; i++)
            {
                IRow targetRow = null;
                ICell sourceCell = null;
                ICell targetCell = null;
                targetRow = sheet.CreateRow(i + 1);
                for (int m = sourceRow.FirstCellNum; m < sourceRow.LastCellNum; m++)
                {
                    sourceCell = sourceRow.GetCell(m);
                    if (sourceCell == null)
                        continue;
                    targetCell = targetRow.CreateCell(m);
                    //targetCell.Encoding = sourceCell.Encoding;
                    //targetCell.CellStyle = sourceCell.CellStyle;
                    //targetCell.SetCellType(sourceCell.CellType);
                }
                //CopyRow(sourceRow, targetRow);
                //Util.CopyRow(sheet, sourceRow, targetRow);
            }
            IRow firstTargetRow = sheet.GetRow(rowIdx);
            ICell firstSourceCell = null;
            ICell firstTargetCell = null;
            for (int m = sourceRow.FirstCellNum; m < sourceRow.LastCellNum; m++)
            {
                firstSourceCell = sourceRow.GetCell(m);
                if (firstSourceCell == null)
                    continue;
                firstTargetCell = firstTargetRow.CreateCell(m);
                //firstTargetCell.Encoding = firstSourceCell.Encoding;
                //firstTargetCell.CellStyle = firstSourceCell.CellStyle;
                //firstTargetCell.SetCellType(firstSourceCell.CellType);
            }
        }

        public void insertSimpleRow(ISheet sheet, int rowIdx, int rowNum, IRow sourceRow)
        {
            //批量移动行
            sheet.ShiftRows(rowIdx, sheet.LastRowNum, rowNum, true, false);
        }

        //插入行
        public void insertRow(ISheet sheet, int rowIdx, int rowNum, IRow sourceRow)
        {
            //批量移动行
            sheet.ShiftRows(rowIdx, sheet.LastRowNum, rowNum, true, false);
            //对批量移动后空出的空行插，创建相应的行，并以插入行的上一行为格式源
            for (int i = rowIdx; i < rowIdx + rowNum; i++)
            {
                IRow targetRow = null;
                ICell sourceCell = null;
                ICell targetCell = null;
                targetRow = sheet.CreateRow(i);
                targetRow.Height = sourceRow.Height;
                //targetRow.RowStyle = sourceRow.RowStyle;
                int c = sourceRow.Cells.Count;
                for (int m = sourceRow.FirstCellNum; m < sourceRow.LastCellNum; m++)
                {
                    sourceCell = sourceRow.GetCell(m);
                    if (sourceCell == null)
                        continue;
                    targetCell = targetRow.CreateCell(m);

                    //targetCell.Encoding = sourceCell.Encoding;
                    targetCell.CellStyle = sourceCell.CellStyle;
                    targetCell.SetCellType(sourceCell.CellType);
                    //targetCell.CellFormula = sourceCell.CellFormula;
                }
            }
        }
        ////复制行
        public void CopyRange(int fromRowIndex, int fromColIndex, int toRowIndex, int toColIndex, bool onlyData, bool copyComment)
        {

            IRow sourceRow = xssfworkbook.GetSheetAt(xssfworkbook.ActiveSheetIndex).GetRow(fromRowIndex);
            ICell sourceCell = sourceRow.GetCell(fromColIndex);
            if (sourceRow != null && sourceCell != null)
            {
                IRow changingRow = null;
                ICell changingCell = null;
                changingRow = xssfworkbook.GetSheetAt(xssfworkbook.ActiveSheetIndex).GetRow(toRowIndex);
                if (changingRow == null)
                    changingRow = xssfworkbook.GetSheetAt(xssfworkbook.ActiveSheetIndex).CreateRow(toRowIndex);
                changingCell = changingRow.GetCell(toColIndex);
                if (changingCell == null)
                    changingCell = changingRow.CreateCell(toColIndex);

                if (onlyData)//仅数据
                {
                    //对单元格的值赋值
                    changingCell.SetCellValue(sourceCell.StringCellValue);
                }
                else         //非仅数据
                {
                    //单元格的编码
                    //changingCell.Encoding = sourceCell.Encoding;
                    //单元格的格式
                    changingCell.CellStyle = sourceCell.CellStyle;
                    //单元格的公式
                    //if (sourceCell.CellFormula == "")
                    //    changingCell.SetCellValue(sourceCell.StringCellValue);
                    //else
                    //    changingCell.SetCellFormula(sourceCell.CellFormula);
                    //对单元格的批注赋值
                    if (copyComment)
                    {
                        if (sourceCell.CellComment != null)
                        {
                            IDrawing patr = xssfworkbook.GetSheetAt(xssfworkbook.ActiveSheetIndex).CreateDrawingPatriarch();
                            IClientAnchor ica = new HSSFClientAnchor(0, 0, 0, 0, toColIndex, toRowIndex, toColIndex + 1, toRowIndex + 1);
                            IComment comment = patr.CreateCellComment(ica);

                            comment.String = new HSSFRichTextString(sourceCell.CellComment.String.ToString());
                            comment.Author = sourceCell.CellComment.Author;

                            changingCell.CellComment = comment;
                        }
                    }
                }
            }
        }
        #endregion

        #region excel操作

        public void createExcel()
        {
            //FileStream file = new FileStream(fname, FileMode.Open, FileAccess.Read);
           
            xssfworkbook = new SXSSFWorkbook();
            //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            //dsi.Company = SiteName;
            //xssfworkbook.DocumentSummaryInformation = dsi;

            //SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            //si.Subject = excelName;
            //xssfworkbook.SummaryInformation = si;

            xssfworkbook.CreateSheet("Sheet1");
        }



        public void DownLoadFile()
        {
            using (MemoryStream ms = new MemoryStream())
            {
                xssfworkbook.Write(ms);
                Response.Clear();
                Response.Buffer = true;
                //Response.Charset = "GB2312";
                Response.Charset = "UTF-8";
                //Response.AppendHeader("Content-Disposition", "attachment;filename=" + excelName + ".xls");
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(excelName + ".xlsx", System.Text.Encoding.UTF8));
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
                Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
                ms.WriteTo(Response.OutputStream);
                xssfworkbook = null;
                iSheet = null;
            }
            //Response.Write(ExportTable(data, columns));
            //Response.End();

        }

        public void DownLoadXLSXFile()
        {
            using (MemoryStream ms = new MemoryStream())
            {
                xssfworkbook.Write(ms);
                Response.Clear();
                Response.Buffer = true;
                //Response.Charset = "GB2312";
                Response.Charset = "UTF-8";
                //Response.AppendHeader("Content-Disposition", "attachment;filename=" + excelName + ".xls");
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(excelName + ".xlsx", System.Text.Encoding.UTF8));
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
                Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
                ms.WriteTo(Response.OutputStream);
                xssfworkbook = null;
                iSheet = null;
            }
            //Response.Write(ExportTable(data, columns));
            //Response.End();

        }



        public void WriteToFile(string exFile)
        {
            //Write the stream data of workbook to the root directory
            if (exFile == "") return;
            using (FileStream file = new FileStream(exFile, FileMode.OpenOrCreate))
            {
                xssfworkbook.Write(file);
                xssfworkbook = null;
                iSheet = null;
            }
        }

        #endregion
    }
}