ExcelHelper

Excel帮助类
ExcelHelper
注:引用System.Drawing

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using Aspose.Cells;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace BD.Common
{
    /// <summary>
    /// Excel帮助类
    /// </summary>
    public class ExcelHelper
    {
        //**** Excel ****
        #region Excel组件：读取服务器端文件（配合导入用）
        /// <summary>
        /// 读取服务器端文件（配合导入用）
        /// </summary>
        /// <param url = "strSysUrl" ></ param >
        /// < param 目标excel的页数="pageInfo"></param>
        /// <returns></returns>
        public static DataTable GetExcelData(string strSysUrl, int pageInfo = 0)
        {
            try
            {
                //解析插入数据库
                Workbook book = new Workbook(strSysUrl);
                Worksheet sheet = book.Worksheets[pageInfo];
                Cells cells = sheet.Cells;

                //提取excel数据 转换为DataTable
                DataTable dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
                return dt;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        #endregion

        #region Excel组件：导出Excel（配合导出用）
        /// <summary>
        /// 导出EXCEL
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public MemoryStream Dt2Excel(DataTable dt)
        {
            HSSFWorkbook book = new HSSFWorkbook();

            ISheet sheet = book.CreateSheet("Sheet1");

            //生成头
            IRow Head = CreateHead(sheet, dt);
            //添加样式
            Row_Style(book, Head, HorizontalAlignment.Center, VerticalAlignment.Center, FontBoldWeight.Bold, 12);
            //生成体
            List<IRow> Body = CreateBody(sheet, dt);
            //添加样式
            Body.ForEach(row => Row_Style(book, row, HorizontalAlignment.Center, VerticalAlignment.Center, FontBoldWeight.Normal, 10));

            //自适应宽度

            AutoColumnWidth(sheet, dt);

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return ms;
        }

        /// <summary>
        /// 导出EXCEL
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public MemoryStream Dt2Excel(DataTable dt, bool needRowSpan)
        {
            HSSFWorkbook book = new HSSFWorkbook();

            ISheet sheet = book.CreateSheet("Sheet1");

            //生成头
            IRow Head = CreateHead(sheet, dt);
            //添加样式
            Row_Style(book, Head, HorizontalAlignment.Center, VerticalAlignment.Center, FontBoldWeight.Bold, 12);
            //生成体
            List<IRow> Body = CreateBody(sheet, dt, needRowSpan);
            //添加样式
            Body.ForEach(row => Row_Style(book, row, HorizontalAlignment.Center, VerticalAlignment.Center, FontBoldWeight.Normal, 10));
            //自适应宽度
            AutoColumnWidth(sheet, dt);

            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return ms;
        }
        /// <summary>
        /// 用于多选项的导出，可带参数
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="Type"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public MemoryStream Dt2Excel(DataTable dt, string Type, string[] args)
        {
            try
            {
                HSSFWorkbook book = new HSSFWorkbook();

                ISheet sheet = book.CreateSheet("Sheet1");

                //生成头
                IRow Head = CreateHead(sheet, dt);
                //添加样式
                Row_Style(book, Head, HorizontalAlignment.Center, VerticalAlignment.Center, FontBoldWeight.Bold, 12);

                List<IRow> Body = new List<IRow>();
                //生成体
                switch (Type)
                {
                    //需要单元格合并
                    case "RowSpan": Body = CreateBody(sheet, dt, true); break;
                    //需要行带有图片,调用时写法：com.Dt2Excel(dt, "RowImage", new string[] { "照片路径对应下标" });
                    case "RowImage": Body = CreateBodyForImage(sheet, book, dt, args[0]); break;
                }

                //添加样式
                Body.ForEach(row => Row_Style(book, row, HorizontalAlignment.Center, VerticalAlignment.Center, FontBoldWeight.Normal, 10));
                //自适应宽度
                AutoColumnWidth(sheet, dt);

                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                book.Write(ms);
                ms.Seek(0, SeekOrigin.Begin);
                return ms;
            }
            catch (Exception e)
            {
                //本地下载如果没路径可能导致报错
                return Dt2Excel(new DataTable());
            }
        }

        /// <summary>
        /// 多表导出
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="Type"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public MemoryStream Ds2Excel(DataSet ds, string Type, string[] args)
        {
            HSSFWorkbook book = new HSSFWorkbook();

            ISheet sheet;
            IRow Head;
            List<IRow> Body;
            foreach (DataTable dt in ds.Tables)
            {
                sheet = book.CreateSheet(dt.TableName);
                //生成头
                Head = CreateHead(sheet, dt);
                //添加样式
                Row_Style(book, Head, HorizontalAlignment.Center, VerticalAlignment.Center, FontBoldWeight.Bold, 12);

                Body = new List<IRow>();
                //生成体
                switch (Type)
                {
                    //需要单元格合并
                    case "RowSpan": Body = CreateBody(sheet, dt, true); break;
                    //需要行带有图片,调用时写法：com.Dt2Excel(dt, "RowImage", new string[] { "照片路径对应下标" });
                    case "RowImage": Body = CreateBodyForImage(sheet, book, dt, args[0]); break;
                    default: Body = CreateBody(sheet, dt); break;
                }

                //添加样式
                Body.ForEach(row => Row_Style(book, row, HorizontalAlignment.Center, VerticalAlignment.Center, FontBoldWeight.Normal, 10));
                //自适应宽度
                AutoColumnWidth(sheet, dt);
            }

            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return ms;
        }
        #endregion

        #region Excel组件：保存Excel到服务器
        public string SaveAsExcel(MemoryStream ms, string FilePath)
        {
            // 实例化一个文件流  
            FileStream streamFile = new FileStream(FilePath, FileMode.Create);

            // 获得字节数组  
            byte[] data = ms.ToArray();
            // 开始写入  
            streamFile.Write(data, 0, data.Length);

            // 清空缓冲区、关闭流  
            streamFile.Flush();
            streamFile.Close();

            ms.Close();
            ms.Dispose();

            return FilePath;
        }

        public string SaveAsExcel(MemoryStream ms, string FilePath, bool NeedServerPath)
        {
            // 实例化一个文件流  
            FileStream streamFile = new FileStream(FilePath, FileMode.Create);

            // 获得字节数组  
            byte[] data = ms.ToArray();
            // 开始写入  
            streamFile.Write(data, 0, data.Length);

            // 清空缓冲区、关闭流  
            streamFile.Flush();
            streamFile.Close();

            ms.Close();
            ms.Dispose();

            return FilePath;
        }
        #endregion

        #region Excel组件:头生成
        private IRow CreateHead(ISheet sheet, DataTable dt)
        {
            IRow row = sheet.CreateRow(0);

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
            }
            return row;
        }
        #endregion

        #region Excel组件:体生成
        private List<IRow> CreateBody(ISheet sheet, DataTable dt)
        {
            List<IRow> lst = new List<IRow>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                }
                lst.Add(row);
            }

            return lst;
        }


        private List<IRow> CreateBody(ISheet sheet, DataTable dt, bool hasRowSpan)
        {
            List<IRow> lst = new List<IRow>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString() == "" ? "空" : dt.Rows[i][j].ToString());
                }

                lst.Add(row);

                string RowSpan = dt.Rows[i]["指标父级"].ToString();
                //此处为新加代码 合并单元格
                if (RowSpan != "")
                {
                    int EndIndex = int.Parse(RowSpan.Split('_')[1]);

                    sheet.AddMergedRegion(new CellRangeAddress(i + 1, i + EndIndex, 1, 1));//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                }
            }
            return lst;
        }

        private List<IRow> CreateBodyForImage(ISheet sheet, HSSFWorkbook book, DataTable dt, string index)
        {
            List<IRow> lst = new List<IRow>();
            try
            {

                //前台传来的索引值
                int Index = Convert.ToInt32(index);

                string path = System.Web.HttpContext.Current.Server.MapPath(("~/Uploads/Enterprise/Staff/"));


                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //根据索引值查到image的文件名
                    string imgPath = dt.Rows[i][Index].ToString();

                    //转为image
                    Image img = null;
                    if (imgPath != "")
                    {
                        img = Image.FromFile(path + imgPath);
                    }

                    IRow row = sheet.CreateRow(i + 1);

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (j != Index)
                        {
                            row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                        }
                        if (imgPath != "")
                        {
                            row.Height = Convert.ToInt16(img.Height * 5);
                        }
                    }
                    lst.Add(row);
                    if (imgPath != "")
                    {
                        var test = img.Width;
                        //插入图片
                        AddPieChart(sheet, book, path + imgPath, (i + 1), Index);
                    }
                }
            }
            catch (Exception e)
            {

            }
            return lst;
        }
        #endregion

        #region Excel组件:单元格宽度自适应
        public void AutoColumnWidth(ISheet sheet, DataTable dt)
        {
            //获取当前列的宽度，然后对比本列的长度，取最大值
            for (int columnNum = 0; columnNum <= dt.Rows.Count; columnNum++)
            {
                int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
                for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
                {
                    IRow currentRow;
                    //当前行未被使用过
                    if (sheet.GetRow(rowNum) == null)
                    {
                        currentRow = sheet.CreateRow(rowNum);
                    }
                    else
                    {
                        currentRow = sheet.GetRow(rowNum);
                    }

                    if (currentRow.GetCell(columnNum) != null)
                    {
                        ICell currentCell = currentRow.GetCell(columnNum);
                        int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                        }
                    }
                }
                sheet.SetColumnWidth(columnNum, (columnWidth + 10) * 256);
            }
        }
        #endregion

        #region Excel组件:单元格样式
        public void Row_Style(HSSFWorkbook book, IRow Row, HorizontalAlignment Align, VerticalAlignment VerticalAlign, FontBoldWeight fontBold, short FontSize)
        {
            IFont font = book.CreateFont();
            ICellStyle style = book.CreateCellStyle();
            foreach (ICell item in Row.Cells)
            {
                //设置单元格的样式：水平对齐居中
                style.Alignment = Align;
                //垂直居中
                style.VerticalAlignment = VerticalAlign;
                //设置字体加粗样式
                font.Boldweight = (short)fontBold;
                //设置字体大小
                font.FontHeightInPoints = FontSize;//头的推荐大小为12,体的推荐为10
                                                   //使用SetFont方法将字体样式添加到单元格样式中 
                style.SetFont(font);
                //将新的样式赋给单元格
                item.CellStyle = style;
            }
        }
        #endregion

        #region Excel组件：给Sheet加入图片
        ///
        /// 向sheet插入图片
        ///
        public void AddPieChart(ISheet sheet, HSSFWorkbook workbook, string imagePath, int row, int col)
        {
            try
            {
                string FileName = imagePath;

                byte[] bytes = System.IO.File.ReadAllBytes(FileName);

                if (!string.IsNullOrEmpty(FileName))
                {
                    int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 140, 40, col, row, col + 1, row + 1);
                    //##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50

                    HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);

                    //pict.Resize();//这句话一定不要，这是用图片原始大小来显示
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion


    }
}