OTS/OTSCleanReportApp/OTSTemplateDesigner/Export_NPOI_Excel.cs


//nopi 相关引用
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OTSIncAReportGraph.Class;
//using OTSINTERFACE;
using System.Drawing;
using System.IO;

namespace OTSIncAReportApp.OTSTemplateDesigner
{
    class Export_NPOI_Excel
    {
        #region 全局变量
        public OTSReport_Export m_otsreport_export;
        IWorkbook workbook = new XSSFWorkbook();            //工作薄，用于创建.xlsx office2007开始以后的
        ISheet sheet;
        public string m_kzm = ".xlsx";
        public int m_Excel_Row_CurrentNumber = 1;       //当前插入到的位置
        #endregion

        #region 构造函数
        public Export_NPOI_Excel(OTSReport_Export in_export)
        {
            m_otsreport_export = in_export;
        }
        #endregion

        #region 创建保存
        /// <summary>
        /// 创建Excel文件
        /// </summary>
        /// <param name="dir"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public bool CreateExcelFile(string filePath)
        {
            //创建word文件
            FileStream fs = File.Create(filePath);
            fs.Close();

            return true;
        }

        public bool SaveExcelFile(string filepath)
        {
            //打开前，先保存word
            FileStream file = new FileStream(filepath, FileMode.Create);
            workbook.Write(file);
            file.Close();
            return true;
        }
        #endregion

        #region Excel相关操作
        /// <summary>
        /// 向Excel中插入工作表，sheet
        /// </summary>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        public bool InseretSheet(string sheetname)
        {
            sheet = workbook.CreateSheet(sheetname);//创建工作表
            return true;
        }

        /// <summary>
        /// 向Excel中插入表格的名称
        /// </summary>
        /// <param name="in_tablename"></param>
        private void InsertExcelTableName(string in_tablename, int spancell)
        {
            ICellStyle cellstyle = workbook.CreateCellStyle();
            cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellstyle.VerticalAlignment = VerticalAlignment.Center;

            IFont font = workbook.CreateFont();//设置字体大小和颜色
            font.FontName = "黑体";
            font.FontHeightInPoints = 13;
            cellstyle.SetFont(font);

            ICell sheettitle = sheet.CreateRow(m_Excel_Row_CurrentNumber).CreateCell(0);
            sheettitle.SetCellValue(in_tablename);
            sheettitle.CellStyle = cellstyle;
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(m_Excel_Row_CurrentNumber, m_Excel_Row_CurrentNumber, 0, spancell));

            m_Excel_Row_CurrentNumber = m_Excel_Row_CurrentNumber + 1;//插入完表名后，全局增量增长值+1
        }

        /// <summary>
        /// 获取表格基础样式
        /// </summary>
        /// <returns></returns>
        private ICellStyle GetTableStyle()
        {
            //创建表格边框样式风格
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;

            //设置上4边
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

            //设置字符大小和颜色
            IFont font = workbook.CreateFont();
            font.FontName = "黑体";
            font.FontHeightInPoints = 10;
            cellStyle.SetFont(font);

            return cellStyle;
        }

        /// <summary>
        /// 在Excel指定的单元格内，插入图片
        /// </summary>
        /// <param name="in_bp"></param>
        /// <param name="cell"></param>
        /// <param name="row"></param>
        private void AddPictureInExcel(Bitmap in_bp, int cell, int row)
        {
            byte[] bytes = DrawFuncation.ImageConvertToBytes(in_bp);

            //第二步，将图片添加到workbook中，指定图片的格式，返回图片所在workbook->paicture数组中的索引的地址，从1开始
            int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);

            //第三步，在sheet中创建画布
            IDrawing patriarch = sheet.GetRow(m_Excel_Row_CurrentNumber + row).GetCell(cell).Sheet.CreateDrawingPatriarch();

            //第四步，设置锚点，（在起始单元格的X坐标0-1023，Y的坐标0-255，在终止单元格的X坐标0-1023，Y的坐标0-255，起始单元格行数，列数，终止单元格行数，列数）
            IClientAnchor anchor = patriarch.CreateAnchor(1000, 200, 1000, 200, cell, m_Excel_Row_CurrentNumber + row, cell + 1, m_Excel_Row_CurrentNumber + row + 1);//终止比开始位置大1,会自动缩放到一个单元格内的

            //第五步，创建图片
            IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
        }


        /// <summary>
        /// 向指定的位置插入图片,为颗粒列表使用
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="row"></param>
        private void AddPictureInExcel_ForParticleList(Bitmap in_bp, int cell, int row)
        {
            //先对图片同比缩放
            Bitmap ls_bp = new Bitmap(64, 20);
            Graphics g = Graphics.FromImage(ls_bp);
            g.Clear(Color.FromArgb(230, 230, 230));
            g.DrawImage(in_bp, new Rectangle(17, 0, 30, 17), new Rectangle(0, 0, in_bp.Width, in_bp.Height), GraphicsUnit.Pixel);

            byte[] bytes = DrawFuncation.ImageConvertToBytes(ls_bp);

            //第二步，将图片添加到workbook中，指定图片的格式，返回图片所在workbook->paicture数组中的索引的地址，从1开始
            int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);

            //第三步，在sheet中创建画布
            IDrawing patriarch = sheet.GetRow(m_Excel_Row_CurrentNumber + row).GetCell(cell).Sheet.CreateDrawingPatriarch();

            //第四步，设置锚点，（在起始单元格的X坐标0-1023，Y的坐标0-255，在终止单元格的X坐标0-1023，Y的坐标0-255，起始单元格行数，列数，终止单元格行数，列数）
            //IClientAnchor anchor = patriarch.CreateAnchor(1000, 200, 1000, 200, cell, m_Excel_Row_CurrentNumber + row, cell + 1, m_Excel_Row_CurrentNumber + row + 1);//终止比开始位置大1,会自动缩放到一个单元格内的

            IClientAnchor anchor = sheet.GetRow(m_Excel_Row_CurrentNumber + row).GetCell(cell).Sheet.Workbook.GetCreationHelper().CreateClientAnchor();

            anchor.AnchorType = (int)AnchorType.MoveDontResize;
            int dx1 = 200;
            int dy1 = 50;
            //在单元格上的位置，不能居中就要这样弄了
            anchor.Col1 = cell;
            anchor.Row1 = m_Excel_Row_CurrentNumber + row;
            anchor.Col2 = cell + 1;
            anchor.Row2 = m_Excel_Row_CurrentNumber + row + 1;
            anchor.Dx1 = dx1;
            anchor.Dy1 = dy1;

            //第五步，创建图片
            IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);

            //不对图片进行拉伸
            pict.Resize();

            in_bp.Dispose();
            ls_bp.Dispose();

        }

        /// <summary>
        /// 向Excel中添加页眉页脚
        /// </summary>
        public void AddPageHeaderFooter()
        {
            //添加页眉页脚文字
            sheet.RepeatingRows = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 5);
            sheet.Header.Center = "Opton OTS";
            sheet.Footer.Center = "Opton OTS";
        }

        /// <summary>
        /// 根据系统中的颜色列表，获取NPOI中的颜色索引值
        /// </summary>
        /// <param name="in_c"></param>
        /// <returns></returns>
        private short GetNPOIColorIndexBySystemColor(Color in_c)
        {
            if (in_c == Color.Azure)
                return NPOI.HSSF.Util.HSSFColor.LightTurquoise.Index;
            if (in_c == Color.Beige)
                return NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
            if (in_c == Color.PaleGreen)
                return NPOI.HSSF.Util.HSSFColor.Lime.Index;
            if (in_c == Color.FloralWhite)
                return NPOI.HSSF.Util.HSSFColor.Automatic.Index;
            if (in_c == Color.Gainsboro)
                return NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;

            if (in_c == Color.LightCyan)
                return NPOI.HSSF.Util.HSSFColor.LightTurquoise.Index;
            if (in_c == Color.PowderBlue)
                return NPOI.HSSF.Util.HSSFColor.Aqua.Index;

            return 9;
        }
        #endregion

    }


}