NET导出Excel并打印

using System.Collections;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.Data;
using NPOI.SS.Util;
using System.Collections.Generic;
using System.Web;
using System.Linq;
using System;

namespace XX
{
    public class ExcelExportHelper
    {
        private HSSFWorkbook hssfworkbook;
        private List<ExcelModel> list;
        private int cellCount = 0;
        /// <summary>
        /// 初始化参数
        /// </summary>
        public ExcelExportHelper()
        {
            list = new List<ExcelModel>();
            hssfworkbook = new HSSFWorkbook();
        }

        /// <summary>
        /// 动态表头数据导出
        /// </summary>
        public void ExportCustomedExcel(string savedPath, string exlName, List<ExcelModel1> data)
        {
            ISheet sheet = hssfworkbook.CreateSheet();
            foreach (ExcelModel1 value in data)
            {
                IRow row = sheet.GetRow(value.RowIndex);
                if (row == null)
                {
                    row = sheet.CreateRow(value.RowIndex);
                }
                ICell cell = row.GetCell(value.ColIndex);
                if (cell == null)
                {
                    cell = row.CreateCell(value.ColIndex);
                }

                cell.SetCellValue(value.CellValue);

                //合并行、列算法
                int firstrow = 0, lastrow = 0, firstcol = 0, lastcol = 0;
                if (value.RowSpan > 0)
                {
                    firstrow = value.RowIndex;
                    lastrow = firstrow + (value.RowSpan - 1);
                    firstcol = value.ColIndex;
                    lastcol = value.ColIndex;
                }

                if (value.ColSpan > 0)
                {
                    firstcol = value.ColIndex;
                    lastcol = firstcol + (value.ColSpan - 1);
                }
                sheet.AddMergedRegion(new CellRangeAddress(firstrow, lastrow, firstcol, lastcol));

                //设置样式
                ICellStyle style = hssfworkbook.CreateCellStyle();
                IFont font = hssfworkbook.CreateFont();
                if (!string.IsNullOrEmpty(value.Align))
                {
                    switch (value.Align)
                    {
                        case "left": style.Alignment = HorizontalAlignment.Left; break;
                        case "center": style.Alignment = HorizontalAlignment.Center; break;
                        case "right": style.Alignment = HorizontalAlignment.Right; break;
                    }
                }
                if (!string.IsNullOrEmpty(value.vAlign))
                {
                    switch (value.vAlign)
                    {
                        case "top": style.VerticalAlignment = VerticalAlignment.Top; break;
                        case "center": style.VerticalAlignment = VerticalAlignment.Center; break;
                        case "bottom": style.VerticalAlignment = VerticalAlignment.Bottom; break;
                    }
                }
                if (!string.IsNullOrEmpty(value.Boldweight))
                {
                    switch (value.Boldweight)
                    {
                        case "bold": font.Boldweight = (short)FontBoldWeight.Bold; break;
                        case "normal": font.Boldweight = (short)FontBoldWeight.Normal; break;
                    }
                }
                if (value.FontHeight > 0)
                {
                    font.FontHeight = value.FontHeight;
                }
                style.SetFont(font);
                cell.CellStyle = style;
            }

            using (FileStream file = new FileStream(savedPath + "\\" + exlName, FileMode.OpenOrCreate))
            {
                hssfworkbook.Write(file);
            }
        }

        /// <summary>
        /// 导出datatable到excel模板
        /// </summary>
        /// <param name="sheetName">页签的名字</param>
        /// <param name="Mbmc">读取excel模板的名称</param>
        /// <param name="dt">数据源，需要循环取的数据</param>
        /// <param name="hs">直接覆盖的数据，只要是title和页脚</param>
        /// <param name="headerIndex">excel表头的最后一行的索引</param>
        public void ExprotDynamicExcel1(string filename, string Mbmc, DataTable dt, DataSet ds, int headerIndex)
        {
            InitializeWorkbook(Mbmc);//预置模板
            RenderListFromExcel(headerIndex, 0);// 读取模板内容
            List<ExcelModel> le = SetBodyModel(dt, 0);
            SetBodyCellValue(ds.Tables[0], le, 0);//填充单元格数据
            //SetCellValue1(hs);
            WriteToFile(filename, "");//生成文件(下载文件)
        }

        /// <summary>
        /// 导出datatable到excel模板
        /// </summary>
        /// <param name="sheetName">页签的名字</param>
        /// <param name="Mbmc">读取excel模板的名称</param>
        /// <param name="dt">数据源，需要循环取的数据</param>
        /// <param name="hs">直接覆盖的数据，只要是title和页脚</param>
        /// <param name="headerIndex">excel表头的最后一行的索引</param>
        public void ExprotDynamicExcel(string filename, string Mbmc, DataTable dt, DataSet ds, int headerIndex)
        {
            InitializeWorkbook(Mbmc);//预置模板
            RenderListFromExcel(headerIndex, 0);// 读取模板内容
            List<ExcelModel> le = SetBodyModel(dt, 0);
            SetBodyCellValue(ds.Tables[0], le, 0);//填充单元格数据

            RenderListFromExcel(headerIndex, 1);// 读取模板内容
            List<ExcelModel> le1 = SetBodyModel(dt, 1);
            SetBodyCellValue(ds.Tables[1], le1, 1);//填充单元格数据


            RenderListFromExcel(headerIndex, 2);// 读取模板内容
            List<ExcelModel> le2 = SetBodyModel(dt, 2);
            SetBodyCellValue(ds.Tables[2], le2, 2);//填充单元格数据
            //SetCellValue1(hs);
            WriteToFile(filename, "");//生成文件(下载文件)
        }
        /// <summary>
        /// 导出datatable到excel模板
        /// </summary>
        /// <param name="sheetName">页签的名字</param>
        /// <param name="Mbmc">读取excel模板的名称</param>
        /// <param name="dt">数据源，需要循环取的数据</param>
        /// <param name="hs">直接覆盖的数据，只要是title和页脚</param>
        /// <param name="headerIndex">excel表头的最后一行的索引</param>
        public void ExprotExcel(string sheetName, string Mbmc, DataTable dt, Hashtable hs, int headerIndex)
        {
            InitializeWorkbook(Mbmc);//预置模板
            RenderDataTableFromExcel(headerIndex);// 读取模板内容
            SetCellValue(dt);//填充单元格数据
            SetCellValue1(hs);
            WriteToFile(sheetName);//生成文件(下载文件)
        }

        /// <summary>
        /// 导出datatable到excel文件，打印用20160921
        /// </summary>
        /// <param name="sheetName">页签的名字</param>
        /// <param name="Mbmc">读取excel模板的名称</param>
        /// <param name="dt">数据源，需要循环取的数据</param>
        /// <param name="hs">直接覆盖的数据，只要是title和页脚</param>
        /// <param name="headerIndex">excel表头的最后一行的索引</param>
        public void ExprotExcelNotDownload(string sheetName, string Mbmc, DataTable dt, Hashtable hs, int headerIndex)
        {
            InitializeWorkbook(Mbmc);//预置模板
            RenderDataTableFromExcel(headerIndex);// 读取模板内容
            SetCellValue(dt);//填充单元格数据
            SetCellValue1(hs);
            WriteToFilePrint(sheetName);//生成文件(下载文件)
        }
        public void ExprotExcel(string sheetName, string Mbmc, DataTable dt, Hashtable hs, Hashtable hs1, int headerIndex, DataTable dt1, int headerindex1, int headerindex2)
        {
            InitializeWorkbook(Mbmc);//预置模板

            RenderDataTableFromExcel1(headerIndex);// 读取模板内容
            SetCellValue(dt);//填充单元格数据 
            SetCellValue1(hs);
            RenderDataTableFromExcel3(headerindex2);// 读取模板内容
            SetCellValue1(hs1);
            RenderDataTableFromExcel2(headerindex1);// 读取模板内容
            SetCellValue(dt1);//填充单元格数据

            WriteToFile(sheetName);//生成文件(下载文件)
        }
        public void ExprotExcel(string sheetName, string Mbmc, int headerIndex, Hashtable hs, DataTable dt, DataTable dt1, DataTable dt2)
        {
            InitializeWorkbook(Mbmc);//预置模板
            RenderDataTableFromExcel1(headerIndex);// 读取模板内容 
            SetCellValue1(hs);
            RenderDataTableFromExcel1(4, 0, 3);// 读取模板内容
            SetCellValue(dt1);//填充单元格数据
            RenderDataTableFromExcel1(4, 5, 8);// 读取模板内容
            SetCellValue(dt2);//填充单元格数据
            int cotextRownum = dt1.Rows.Count > dt2.Rows.Count ? 5 + dt1.Rows.Count : 5 + dt2.Rows.Count;
            RenderDataTableFromExcel1(cotextRownum, 0, 10);// 读取模板内容
            SetCellValue(dt);//填充单元格数据 
            WriteToFile(sheetName);//生成文件(下载文件)
        }
        public void ExprotExcel(string sheetName, string Mbmc, DataTable dt, Hashtable hs, Hashtable hs1, int headerIndex, DataTable dt1, int headerindex1, int headerindex2, DataSet ds3, DataSet ds5)
        {
            InitializeWorkbook(Mbmc);//预置模板

            RenderDataTableFromExcel1(headerIndex);// 读取模板内容
            SetCellValue(dt);//填充单元格数据 
            SetCellValue1(hs);
            RenderDataTableFromExcel3(headerindex2);// 读取模板内容
            SetCellValue1(hs1);
            RenderDataTableFromExcel2(headerindex1);// 读取模板内容
            SetCellValue(dt1);//填充单元格数据
            int i = 1;
            foreach (DataRow dr in ds5.Tables[0].Select("depth=1"))
            {
                if (i < 8)
                {
                    list = new List<ExcelModel>();
                    System.Collections.Hashtable ht = new System.Collections.Hashtable();
                    ht.Add("TITLE", dr["PROJECTNAME"]);
                    RenderDataTableFromExcel(i, "");// 读取模板内容
                    SetCellValue(ds3.Tables[0].Select("fid='" + dr["ID"].ToString() + "'"), i, ds3.Tables[0]);//填充单元格数据
                    SetCellValue1(ht, i);
                    ISheet sheet = hssfworkbook.GetSheetAt(i);
                    sheet.Workbook.SetSheetName(i, dr["PROJECTNAME"].ToString());
                    sheet.SetActive(true);
                    i++;
                }
            }
            WriteToFile(sheetName);//生成文件(下载文件)
        }
        /// <summary>
        /// 导出方法
        /// </summary>
        /// <param name="sheetName">客户端保存的导出文件名</param>
        /// <param name="Mbmc">模板名称</param>
        /// <param name="dt">数据源</param>
        /// <param name="hs">需要特殊正理的数据列放在Hashtable</param>
        /// <param name="headerIndex">导出设备工作表名称</param>
        public bool ExprotExcel(string sheetName, string Mbmc, DataTable dt, Hashtable hs, string sheetname)
        {
            int sheetIndex = 0;
            bool isexists_sheet = false;//是否存在sheet
            InitializeWorkbook(Mbmc);//预置模板
            for (int index = 0; index < hssfworkbook.NumberOfSheets; index++)
            {
                HSSFSheet curr_sheet = (HSSFSheet)hssfworkbook.GetSheetAt(index);
                if (curr_sheet.SheetName.IndexOf(sheetname) > 0 || curr_sheet.SheetName.Contains(sheetname))
                {
                    sheetIndex = index;
                    //sheetname = curr_sheet.SheetName;
                    isexists_sheet = true;
                    break;
                }
            }
            if (isexists_sheet)
            {
                RenderDataTableFromExcel(sheetIndex, "");// 读取模板内容
                SetCellValue(dt, sheetIndex);//填充单元格数据
                SetCellValue1(hs, sheetIndex);
                ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);
                sheet.SetActive(true);
                WriteToFile(sheetName, "");//生成文件(下载文件)
            }
            return isexists_sheet;
        }

        /// <summary>
        /// 河南省城乡住房建设行业 
        /// </summary>
        /// <param name="FileName">生成导出文件名称</param>
        /// <param name="Mbmc">模板名称</param>
        /// <param name="dt">填充数据集</param>
        /// <param name="hs">哈希页头和页脚</param>
        /// <param name="sheetname">sheet名称</param>
        /// <param name="rowHeight">行高</param>
        /// <param name="headerRowIndex">页头索引</param>
        /// <param name="footRow">页脚行数</param>
        /// <returns></returns>
        public bool ExprotExcel(string FileName, string Mbmc, Dictionary<string, DataTable> dic, Hashtable hs, short rowHeight, int headerRowIndex, int footRow)
        {
            int sheetIndex = 0;
            bool isexists_sheet = false;//是否存在sheet
            InitializeWorkbook(Mbmc);//预置模板
            foreach (var key in dic)
            {
                for (int index = 0; index < hssfworkbook.NumberOfSheets; index++)
                {
                    HSSFSheet curr_sheet = (HSSFSheet)hssfworkbook.GetSheetAt(index);
                    if (curr_sheet.SheetName.IndexOf(key.Key) > 0 || curr_sheet.SheetName.Contains(key.Key))
                    {
                        sheetIndex = index;
                        isexists_sheet = true;
                        break;
                    }
                    //else
                    //{
                    //    curr_sheet.(false);
                    //    //hssfworkbook.SetSheetHidden(index, true);
                    //}
                }
                if (isexists_sheet)
                {
                    RenderDataTableFromExcel(sheetIndex, headerRowIndex);// 读取模板内容.

                    SetCellValue1(hs, sheetIndex);
                    SetCellValue(key.Value, sheetIndex, rowHeight, footRow);//填充单元格数据

                    ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);
                    sheet.SetActive(true);

                    //WriteToFile(FileName);
                    WriteToFile(FileName, "");
                }
            }
            DownLoadFile(FileName);//下载文件

            return isexists_sheet;
        }

        /// <summary>
        /// 保障处excel导出
        /// </summary>
        public bool ExprotExcel(string sheetName, string Mbmc, DataSet ds, Hashtable hs, int headerIndex)
        {
            try
            {
                InitializeWorkbook(Mbmc);//预置模板
                RenderDataTableFromExcel(headerIndex, "");// 读取模板内容 
                SetCellValue1(hs, headerIndex);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    RenderDataTableFromExcel1(8, 0, 21);// 读取模板内容
                    SetCellValue(ds.Tables[0]);//填充单元格数据
                }

                if (ds.Tables[1].Rows.Count > 0)
                {
                    RenderDataTableFromExcel1((ds.Tables[0].Rows.Count == 0 ? 1 : ds.Tables[0].Rows.Count) + 9, 0, 21);// 读取模板内容
                    SetCellValue(ds.Tables[1]);//填充单元格数据
                }

                if (ds.Tables[2].Rows.Count > 0)
                {
                    RenderDataTableFromExcel1((ds.Tables[0].Rows.Count == 0 ? 1 : ds.Tables[0].Rows.Count) + 9 + (ds.Tables[1].Rows.Count == 0 ? 1 : ds.Tables[1].Rows.Count) + 1, 0, 21);// 读取模板内容
                    SetCellValue(ds.Tables[2]);//填充单元格数据 
                }
                //先删除
                FileStream file = new FileStream(sheetName, FileMode.Create);
                hssfworkbook.Write(file);
                file.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
                throw;
            }
            return false;
        }

        public void ExprotExcel(string sheetName, string Mbmc, DataTable dt, Hashtable hs, int headerIndex, string newexport)
        {
            InitializeWorkbook(Mbmc);//预置模板
            RenderDataTableFromExcel(headerIndex);// 读取模板内容
            SetCellValue(dt);//填充单元格数据
            SetCellValue1(hs);
            WriteToFile(sheetName, newexport);//生成文件(下载文件)
        }

        /// <summary>
        /// 预置模板
        /// </summary>
        /// <param name="DocumentName"></param>
        private void InitializeWorkbook(string DocumentName)
        {
            //读取模板通过编辑，这是建议使用fileaccess读防止文件锁定;
            FileStream file = new FileStream(DocumentName, FileMode.Open, FileAccess.Read);
            hssfworkbook = new HSSFWorkbook(file);
            //创建一个条目的文档概要信息
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "TES";
            hssfworkbook.DocumentSummaryInformation = dsi;
            //创建一个条目的概要信息
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "TES NPOI Example";
            hssfworkbook.SummaryInformation = si;

        }
        /// <summary>
        ///  读取模板内容
        /// </summary>
        /// <param name="HeaderRowIndex">表头</param>
        /// <param name="lastRowNum"></param>
        /// <param name="firstCellIndex"></param>
        /// <param name="lastCellIndex"></param>
        /// <returns></returns>
        private List<ExcelModel> RenderDataTableFromExcel1(int ContextRowNum, int firstCellIndex, int lastCellIndex)
        {
            list = new List<ExcelModel>();
            ISheet sheet = hssfworkbook.GetSheetAt(0);

            IRow headerRow = sheet.GetRow(ContextRowNum);
            cellCount = headerRow.LastCellNum;
            for (int j = firstCellIndex; j <= lastCellIndex; j++)
            {
                ExcelModel model = new ExcelModel();
                if (headerRow.GetCell(j) != null)
                {
                    string value = headerRow.GetCell(j).StringCellValue.Trim();
                    if (string.IsNullOrEmpty(value)) continue;
                    if (value.StartsWith("#"))
                    {
                        model.ColumnName = value.Substring(value.IndexOf('#') + 1, value.Length - 2);
                        model.ColumnType = headerRow.GetCell(j).CellType;
                        model.ColumnIndex = headerRow.GetCell(j).ColumnIndex;
                        model.ColumnStyle = headerRow.GetCell(j).CellStyle;
                        model.RowIndex = headerRow.RowNum;
                        if (value.EndsWith("$"))//行循环
                        {
                            model.SetValueType = 1;
                        }
                        else if (value.EndsWith("~"))//列循环
                        {
                            model.SetValueType = 2;
                        }
                        else if (value.EndsWith("#"))
                        {
                            model.SetValueType = 0;
                        }
                        list.Add(model);
                    }
                }
            }
            return list;
        }


        private List<ExcelModel> RenderDataTableFromExcel1(int HeaderRowIndex)
        {
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            IRow headerRow = sheet.GetRow(HeaderRowIndex);
            cellCount = headerRow.LastCellNum;
            //这种取法暂时有问题，未做调整，实际应该取的是包含列最多的行
            int rowCount = sheet.LastRowNum;
            for (int i = sheet.FirstRowNum; i <= HeaderRowIndex + 1; i++)
            {
                if (i == HeaderRowIndex)
                {
                    continue;
                }
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ExcelModel model = new ExcelModel();
                    if (row.GetCell(j) != null)
                    {
                        string value = row.GetCell(j).StringCellValue.Trim();
                        if (string.IsNullOrEmpty(value)) continue;
                        if (value.StartsWith("#"))
                        {
                            model.ColumnName = value.Substring(value.IndexOf('#') + 1, value.Length - 2);
                            model.ColumnType = row.GetCell(j).CellType;
                            model.ColumnIndex = row.GetCell(j).ColumnIndex;
                            model.ColumnStyle = row.GetCell(j).CellStyle;
                            model.RowIndex = row.RowNum;
                            if (value.EndsWith("$"))//行循环
                            {
                                model.SetValueType = 1;
                            }
                            else if (value.EndsWith("~"))//列循环
                            {
                                model.SetValueType = 2;
                            }
                            else if (value.EndsWith("#"))
                            {
                                model.SetValueType = 0;
                            }
                            list.Add(model);
                        }
                    }

                }
            }
            return list;
        }
        private List<ExcelModel> RenderDataTableFromExcel2(int HeaderRowIndex)
        {
            list = new List<ExcelModel>();
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            IRow headerRow = sheet.GetRow(HeaderRowIndex);
            cellCount = headerRow.LastCellNum;
            //这种取法暂时有问题，未做调整，实际应该取的是包含列最多的行
            int rowCount = sheet.LastRowNum;
            for (int i = HeaderRowIndex; i <= sheet.LastRowNum; i++)
            {
                if (i == HeaderRowIndex)
                {
                    continue;
                }
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ExcelModel model = new ExcelModel();
                    if (row.GetCell(j) != null)
                    {
                        string value = row.GetCell(j).StringCellValue.Trim();
                        if (string.IsNullOrEmpty(value)) continue;
                        if (value.StartsWith("#"))
                        {
                            model.ColumnName = value.Substring(value.IndexOf('#') + 1, value.Length - 2);
                            model.ColumnType = row.GetCell(j).CellType;
                            model.ColumnIndex = row.GetCell(j).ColumnIndex;
                            model.ColumnStyle = row.GetCell(j).CellStyle;
                            model.RowIndex = row.RowNum;
                            if (value.EndsWith("$"))//行循环
                            {
                                model.SetValueType = 1;
                            }
                            else if (value.EndsWith("~"))//列循环
                            {
                                model.SetValueType = 2;
                            }
                            else if (value.EndsWith("#"))
                            {
                                model.SetValueType = 0;
                            }
                            list.Add(model);
                        }
                    }

                }
            }
            return list;
        }
        private List<ExcelModel> RenderDataTableFromExcel3(int HeaderRowIndex)
        {
            list = new List<ExcelModel>();
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            IRow headerRow = sheet.GetRow(HeaderRowIndex);
            cellCount = headerRow.LastCellNum;
            //这种取法暂时有问题，未做调整，实际应该取的是包含列最多的行
            int rowCount = sheet.LastRowNum;
            for (int i = HeaderRowIndex; i <= sheet.LastRowNum - 5; i++)
            {
                if (i == HeaderRowIndex)
                {
                    continue;
                }
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ExcelModel model = new ExcelModel();
                    if (row.GetCell(j) != null)
                    {
                        string value = row.GetCell(j).StringCellValue.Trim();
                        if (string.IsNullOrEmpty(value)) continue;
                        if (value.StartsWith("#"))
                        {
                            model.ColumnName = value.Substring(value.IndexOf('#') + 1, value.Length - 2);
                            model.ColumnType = row.GetCell(j).CellType;
                            model.ColumnIndex = row.GetCell(j).ColumnIndex;
                            model.ColumnStyle = row.GetCell(j).CellStyle;
                            model.RowIndex = row.RowNum;
                            if (value.EndsWith("$"))//行循环
                            {
                                model.SetValueType = 1;
                            }
                            else if (value.EndsWith("~"))//列循环
                            {
                                model.SetValueType = 2;
                            }
                            else if (value.EndsWith("#"))
                            {
                                model.SetValueType = 0;
                            }
                            list.Add(model);
                        }
                    }

                }
            }
            return list;
        }
        #region  new 动态设置表头 csm by 20140113

        /// <summary>
        /// 读取动态excel的表头
        /// </summary>
        /// <param name="HeaderRowIndex">表头索引</param>
        /// <param name="sheetIndex">页签索引</param>
        /// <returns>集合</returns>
        private List<ExcelModel> RenderListFromExcel(int HeaderRowIndex, int sheetIndex)
        {
            ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);
            IRow headerRow = sheet.GetRow(HeaderRowIndex);
            cellCount = headerRow.LastCellNum;
            int rowCount = sheet.LastRowNum;//读取最后一行 
            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ExcelModel model = new ExcelModel();
                    if (row.GetCell(j) != null)
                    {
                        string value = row.GetCell(j).StringCellValue.Trim();
                        if (string.IsNullOrEmpty(value)) continue;
                        if (value.StartsWith("#"))
                        {
                            model.ColumnName = value.Substring(value.IndexOf('#') + 1, value.Length - 2);
                            model.ColumnType = row.GetCell(j).CellType;
                            model.ColumnIndex = row.GetCell(j).ColumnIndex;
                            model.ColumnStyle = row.GetCell(j).CellStyle;
                            model.RowIndex = row.RowNum;
                            if (value.EndsWith("$"))//行循环
                            {
                                model.SetValueType = 1;
                            }
                            else if (value.EndsWith("~"))//列循环
                            {
                                model.SetValueType = 2;
                            }
                            else if (value.EndsWith("#"))
                            {
                                model.SetValueType = 0;
                            }
                            list.Add(model);
                        }
                    }

                }
            }
            return list;
        }
        /// <summary>
        /// 根据表头绘制表头
        /// </summary>
        /// <param name="hs">绘制表头的datatable</param>
        /// <param name="sheetIndex">读取模板的页签</param>
        /// <returns>集合</returns>
        private List<ExcelModel> SetBodyModel(DataTable dt, int sheetIndex)
        {
            List<ExcelModel> le = new List<ExcelModel>();
            if (dt.Rows.Count == 0)
            {
                return le;
            }


            ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
            foreach (ExcelModel m in list)
            {
                if (m.SetValueType == 2)
                {
                    // le.Add(new ExcelModel() { RowIndex = m.RowIndex + 1, ColumnIndex = m.ColumnIndex, ColumnName = "xm", SetValueType = 1, ColumnStyle = m.ColumnStyle });
                    sheet1.CreateRow(m.RowIndex + 1);
                    for (int j = 0; j < dt.Rows.Count; j++)//创建单元格
                    {
                        sheet1.GetRow(m.RowIndex).CreateCell(j);
                        sheet1.GetRow(m.RowIndex + 1).CreateCell(j);
                    }
                    for (int i = m.ColumnIndex; i < dt.Rows.Count; i++)
                    {
                        sheet1.GetRow(m.RowIndex).GetCell(i).SetCellValue(dt.Rows[i]["Name"].ToString());
                        sheet1.GetRow(m.RowIndex).GetCell(i).CellStyle = m.ColumnStyle;
                        sheet1.GetRow(m.RowIndex).GetCell(i).SetCellType(m.ColumnType);
                        //同时给下面一行添加需要赋值的列名
                        ExcelModel em = new ExcelModel() { RowIndex = m.RowIndex + 1, ColumnIndex = i, ColumnName = "cell" + i, SetValueType = 1, ColumnStyle = m.ColumnStyle };
                        le.Add(em);
                    }
                    // le.Add(new ExcelModel() { RowIndex = m.RowIndex + 1, ColumnIndex = m.ColumnIndex, ColumnName = "cell" + dt.Rows.Count, SetValueType = 1, ColumnStyle = m.ColumnStyle });
                    //le.Add(new ExcelModel() { RowIndex = m.RowIndex + 1, ColumnIndex = m.ColumnIndex, ColumnName = "cell" + (dt.Rows.Count + 1), SetValueType = 1, ColumnStyle = m.ColumnStyle });


                }
            }
            return le;
        }

        /// <summary>
        ///  根据生成的表头列表绘制数据行
        /// </summary>
        /// <param name="dt"></param>
        private void SetBodyCellValue(DataTable dt, List<ExcelModel> le, int sheetIndex)
        {
            if (dt.Rows.Count == 0)
            {
                return;
            }
            ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
            int a = 0;
            cellCount = le.Max(d => d.ColumnIndex);
            int rowNum = le.Max(d => d.RowIndex);
            int count = dt.Rows.Count;
            int LastRowNum = sheet1.LastRowNum;
            //需要把循环以下的行向下移动dt.Rows.Count;
            int i = le.Where(d => d.SetValueType == 1).FirstOrDefault().RowIndex + 1;
            sheet1.ShiftRows(i, LastRowNum + 1, count - 1, true, true);
            foreach (DataRow dr in dt.Rows)
            {
                if (rowNum != rowNum + a)
                {
                    sheet1.CreateRow(rowNum + a);
                    for (int j = 0; j <= cellCount; j++)//创建单元格
                    {
                        sheet1.GetRow(rowNum + a).CreateCell(j);

                    }
                }
                foreach (ExcelModel m in le)
                {
                    if (m.SetValueType == 1)
                    {

                        if (dt.Columns.Contains(m.ColumnName))
                        {
                            sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellValue(dr[m.ColumnName].ToString());
                        }
                        else
                        {
                            sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellValue("");
                        }
                        sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).CellStyle = m.ColumnStyle;
                        //sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellType(m.ColumnType);
                    }
                }
                a++;
            }

        }

        #endregion

        /// <summary>
        /// 读取excel模板，初始化excel数据
        /// </summary>
        /// <param name="HeaderRowIndex"></param>
        /// <returns></returns>
        private List<ExcelModel> RenderDataTableFromExcel(int HeaderRowIndex)
        {
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            IRow headerRow = sheet.GetRow(HeaderRowIndex);
            cellCount = headerRow.LastCellNum;
            //这种取法暂时有问题，未做调整，实际应该取的是包含列最多的行
            int rowCount = sheet.LastRowNum;
            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
            {
                if (i == HeaderRowIndex)
                {
                    continue;
                }
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ExcelModel model = new ExcelModel();
                    if (row.GetCell(j) != null)
                    {
                        string value = row.GetCell(j).StringCellValue.Trim();
                        if (string.IsNullOrEmpty(value)) continue;
                        if (value.StartsWith("#"))
                        {
                            model.ColumnName = value.Substring(value.IndexOf('#') + 1, value.Length - 2);
                            model.ColumnType = row.GetCell(j).CellType;
                            model.ColumnIndex = row.GetCell(j).ColumnIndex;
                            model.ColumnStyle = row.GetCell(j).CellStyle;
                            model.RowIndex = row.RowNum;
                            if (value.EndsWith("$"))//行循环
                            {
                                model.SetValueType = 1;
                            }
                            else if (value.EndsWith("~"))//列循环
                            {
                                model.SetValueType = 2;
                            }
                            else if (value.EndsWith("#"))
                            {
                                model.SetValueType = 0;
                            }
                            list.Add(model);
                        }
                    }

                }
            }
            return list;
        }
        private List<ExcelModel> RenderDataTableFromExcel(int sheetIndex, int HeaderRowIndex)
        {
            list = new List<ExcelModel>();
            ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);
            IRow headerRow = sheet.GetRow(HeaderRowIndex);
            cellCount = headerRow.LastCellNum;
            //这种取法暂时有问题，未做调整，实际应该取的是包含列最多的行
            int rowCount = sheet.LastRowNum;
            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
            {
                if (i == HeaderRowIndex)
                {
                    continue;
                }
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }

                try
                {
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ExcelModel model = new ExcelModel();
                        if (row.GetCell(j) != null)
                        {
                            //20160824  报数字numberc转string 导致 row.GetCell(j)
                            //为null 错误所以 直接先设置类型 不知可否
                           
                            row.GetCell(j).SetCellType(CellType.String);


                            string value = row.GetCell(j).StringCellValue.Trim();
                           
                            if (string.IsNullOrEmpty(value)) continue;
                            if (value.StartsWith("#"))
                            {

                                model.ColumnName = value.Substring(value.IndexOf('#') + 1, value.Length - 2);
                                model.ColumnType = row.GetCell(j).CellType;
                                model.ColumnIndex = row.GetCell(j).ColumnIndex;
                                model.ColumnStyle = row.GetCell(j).CellStyle;
                                model.RowIndex = row.RowNum;
                                if (value.EndsWith("$"))//行循环
                                {
                                    model.SetValueType = 1;
                                }
                                else if (value.EndsWith("~"))//列循环
                                {
                                    model.SetValueType = 2;
                                }
                                else if (value.EndsWith("#"))
                                {
                                    model.SetValueType = 0;
                                }
                                list.Add(model);

                            }
                        }

                    }
                }
                catch (Exception)
                {

                    throw;
                }

            }
            return list;
        }
        private List<ExcelModel> RenderDataTableFromExcel(int sheetIndex, string a)
        {
            ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);
            hssfworkbook.SetActiveSheet(sheetIndex);
            sheet.IsSelected = true;
            IRow headerRow = sheet.GetRow(2);
            cellCount = headerRow.LastCellNum;
            //这种取法暂时有问题，未做调整，实际应该取的是包含列最多的行
            for (int n = 0; n < sheet.LastRowNum; n++)
            {
                headerRow = sheet.GetRow(n);
                if (headerRow != null)
                {
                    if (headerRow.LastCellNum > cellCount)
                    {
                        cellCount = headerRow.LastCellNum;
                    }
                }
            }


            int rowCount = sheet.LastRowNum;
            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ExcelModel model = new ExcelModel();
                    if (row.GetCell(j) != null)
                    {
                        string value = "";
                        if (row.GetCell(j).CellType == CellType.Numeric)
                        {
                            value = row.GetCell(j).NumericCellValue.ToString().Trim();
                        }
                        else
                        {
                            value = row.GetCell(j).StringCellValue.Trim();
                        }
                        if (string.IsNullOrEmpty(value)) continue;
                        if (value.StartsWith("#"))
                        {
                            model.ColumnName = value.Substring(value.IndexOf('#') + 1, value.Length - 2);
                            model.ColumnType = row.GetCell(j).CellType;
                            model.ColumnIndex = row.GetCell(j).ColumnIndex;
                            model.ColumnStyle = row.GetCell(j).CellStyle;
                            model.RowIndex = row.RowNum;
                            if (value.EndsWith("$"))//行循环
                            {
                                model.SetValueType = 1;
                            }
                            else if (value.EndsWith("~"))//列循环
                            {
                                model.SetValueType = 2;
                            }
                            else if (value.EndsWith("#"))
                            {
                                model.SetValueType = 0;
                            }
                            list.Add(model);
                        }
                    }

                }
            }
            return list;
        }
        /// <summary>
        ///  填充单元格数据
        /// </summary>
        /// <param name="dt"></param>
        private void SetCellValue(DataTable dt)
        {
            if (dt.Rows.Count == 0)
            {
                return;
            }
            ISheet sheet1 = hssfworkbook.GetSheetAt(0);
            int a = 0;
            int rowNum = list.Max(d => d.RowIndex);
            int count = dt.Rows.Count;
            int LastRowNum = sheet1.LastRowNum;
            //需要把循环以下的行向下移动dt.Rows.Count;
            int i = list.Where(d => d.SetValueType == 1).FirstOrDefault().RowIndex + 1;
            sheet1.ShiftRows(i, LastRowNum + 1, count - 1, true, true);
            foreach (DataRow dr in dt.Rows)
            {
                if (rowNum != rowNum + a)
                {
                    sheet1.CreateRow(rowNum + a);
                    for (int j = 0; j < cellCount; j++)//创建单元格
                    {
                        sheet1.GetRow(rowNum + a).CreateCell(j);

                    }
                }
                foreach (ExcelModel m in list)
                {
                    if (m.SetValueType == 1)
                    {

                        if (dt.Columns.Contains(m.ColumnName))
                        {
                            sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellValue(dr[m.ColumnName].ToString());
                        }
                        else
                        {
                            sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellValue("");
                        }
                        sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).CellStyle = m.ColumnStyle;
                        sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellType(m.ColumnType);
                    }
                }
                a++;
            }

        }
        /// <summary>
        /// 全省住房城乡建设系统项目汇总表 专用
        /// </summary>
        /// <param name="dt">循环列表</param>
        /// <param name="sheetIndex">sheet索引</param>
        /// <param name="rowHeight">行高</param>
        ///  <param name="footRow">页脚行数</param>
        private void SetCellValue(DataTable dt, int sheetIndex, short rowHeight, int footRow)
        {
            if (dt == null)
            {
                ISheet sheet0 = hssfworkbook.GetSheetAt(sheetIndex);
                int rowNum0 = list.Max(d => d.RowIndex) - footRow;
                SetCellNullValue(sheet0, rowNum0);
                return;
            }
            if (dt.Rows.Count == 0)
            {
                return;
            }
            ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
            int a = 0;
            int rowNum = list.Max(d => d.RowIndex) - footRow;
            // cellCount = list.Max(d => d.ColumnIndex);//-2014 01 10-
            int count = dt.Rows.Count;
            int LastRowNum = sheet1.LastRowNum;
            //需要把循环以下的行向下移动dt.Rows.Count;
            int i = list.Where(d => d.SetValueType == 1).FirstOrDefault().RowIndex + 1;
            sheet1.ShiftRows(i, LastRowNum + 1, count - 1, true, true);
            try
            {
                foreach (DataRow dr in dt.Rows)
                {
                    if ((rowNum != rowNum + a) || a == 0)
                    {
                        //设置行高
                        if (rowHeight == 0)
                        {
                            sheet1.CreateRow(rowNum + a);
                        }
                        else
                        {
                            IRow row = sheet1.CreateRow(rowNum + a);
                            row.Height = rowHeight;
                        }


                        for (int j = 0; j <= cellCount; j++)//创建单元格 //-2014 01 10 j < cellCount;
                        {

                            sheet1.GetRow(rowNum + a).CreateCell(j);

                        }
                    }
                    //设置自动换行
                    HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
                    style.WrapText = true;
                    style.BorderBottom =BorderStyle.Thin;//下边框为细线边框
                    style.BorderLeft = BorderStyle.Thin;//左边框
                    style.BorderRight = BorderStyle.Thin;//上边框
                    style.BorderTop = BorderStyle.Thin;//右边框

                    foreach (ExcelModel m in list)
                    {
                        if (m.SetValueType == 1)
                        {
                            //if (sheet1.GetRow(m.RowIndex + a) == null)
                            //{
                            //    return;
                            //}
                            //if (sheet1.GetRow(m.RowIndex + a).Cells.Count == 0)
                            //{
                            //    return;
                            //}

                            if (dt.Columns.Contains(m.ColumnName))
                            {
                                sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellValue(dr[m.ColumnName].ToString());
                            }
                            else
                            {
                                sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellValue("");
                            }



                            sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).CellStyle = style;
                            sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellType(m.ColumnType);
                        }
                    }
                    a++;
                }
            }
            catch (Exception)
            {

                throw;
            }

        }
        /// <summary>
        /// 赋空值
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="footRow"></param>
        /// <returns></returns>
        private void SetCellNullValue(ISheet sheet1, int rowNum)
        {
            sheet1.CreateRow(rowNum);
            for (int j = 0; j <= cellCount; j++)//创建单元格 //-2014 01 10 j < cellCount;
            {
                sheet1.GetRow(rowNum).CreateCell(j);
            }
            //设置自动换行
            HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
            style.WrapText = true;
            foreach (ExcelModel m in list)
            {
                if (m.SetValueType == 1)
                {
                    sheet1.GetRow(m.RowIndex).GetCell(m.ColumnIndex).SetCellValue("");
                    sheet1.GetRow(m.RowIndex).GetCell(m.ColumnIndex).CellStyle = style;
                    sheet1.GetRow(m.RowIndex).GetCell(m.ColumnIndex).SetCellType(m.ColumnType);
                }
            }
        }
        private void SetCellValue(DataTable dt, int sheetIndex)
        {
            if (dt.Rows.Count == 0)
            {
                return;
            }
            ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
            int a = 0;
            int rowNum = list.Max(d => d.RowIndex);
            // cellCount = list.Max(d => d.ColumnIndex);//-2014 01 10-
            int count = dt.Rows.Count;
            int LastRowNum = sheet1.LastRowNum;
            //需要把循环以下的行向下移动dt.Rows.Count;
            int i = list.Where(d => d.SetValueType == 1).FirstOrDefault().RowIndex + 1;
            sheet1.ShiftRows(i, LastRowNum + 1, count - 1, true, true);
            foreach (DataRow dr in dt.Rows)
            {
                if (rowNum != rowNum + a)
                {
                    sheet1.CreateRow(rowNum + a);

                    for (int j = 0; j <= cellCount; j++)//创建单元格 //-2014 01 10 j < cellCount;
                    {
                        sheet1.GetRow(rowNum + a).CreateCell(j);

                    }
                }
                foreach (ExcelModel m in list)
                {
                    if (m.SetValueType == 1)
                    {

                        if (dt.Columns.Contains(m.ColumnName))
                        {
                            sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellValue(dr[m.ColumnName].ToString());
                        }
                        else
                        {
                            sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellValue("");
                        }
                        sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).CellStyle = m.ColumnStyle;
                        sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellType(m.ColumnType);
                    }
                }
                a++;
            }

        }
        private void SetCellValue(DataRow[] dr1, int sheetIndex, DataTable dt)
        {
            if (dr1.Length == 0)
            {
                return;
            }
            ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
            int a = 0;
            int rowNum = list.Max(d => d.RowIndex);

            int count = dr1.Length;
            int LastRowNum = sheet1.LastRowNum;
            //需要把循环以下的行向下移动dt.Rows.Count;
            int i = list.Where(d => d.SetValueType == 1).FirstOrDefault().RowIndex + 1;
            sheet1.ShiftRows(i, LastRowNum + 1, count - 1, true, true);
            foreach (DataRow dr in dr1)
            {
                if (rowNum != rowNum + a)
                {
                    sheet1.CreateRow(rowNum + a);
                    for (int j = 0; j < cellCount; j++)//创建单元格
                    {
                        sheet1.GetRow(rowNum + a).CreateCell(j);

                    }
                }
                foreach (ExcelModel m in list)
                {
                    if (m.SetValueType == 1)
                    {

                        if (dt.Columns.Contains(m.ColumnName))
                        {
                            sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellValue(dr[m.ColumnName].ToString());
                        }
                        else
                        {
                            sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellValue("");
                        }
                        sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).CellStyle = m.ColumnStyle;
                        sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellType(m.ColumnType);
                    }
                }
                a++;
            }

        }
        /// <summary>
        /// 直接替换赋值
        /// </summary>
        /// <param name="hs"></param>
        private void SetCellValue1(Hashtable hs)
        {
            if (hs.Count == 0)
            {
                return;
            }
            ISheet sheet1 = hssfworkbook.GetSheetAt(0);
            foreach (ExcelModel m in list)
            {
                if (m.SetValueType == 0)
                {
                    if (hs.ContainsKey(m.ColumnName))
                    {
                        sheet1.GetRow(m.RowIndex).GetCell(m.ColumnIndex).SetCellValue(hs[m.ColumnName].ToString());
                        //设定单元格的格式 日期，货币
                    }
                    else
                    {
                        sheet1.GetRow(m.RowIndex).GetCell(m.ColumnIndex).SetCellValue("");
                    }
                }
            }
        }
        private void SetCellValue1(Hashtable hs, int sheetIndex)
        {
            if (hs.Count == 0)
            {
                return;
            }
            ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
            foreach (ExcelModel m in list)
            {
                if (m.SetValueType == 0)
                {
                    if (hs.ContainsKey(m.ColumnName))
                    {
                        sheet1.GetRow(m.RowIndex).GetCell(m.ColumnIndex).SetCellValue(hs[m.ColumnName].ToString());
                        //设定单元格的格式 日期，货币
                    }
                    else
                    {
                        sheet1.GetRow(m.RowIndex).GetCell(m.ColumnIndex).SetCellValue("");
                    }
                }
            }
        }
        /// <summary>
        /// 判断指定的单元格是否是合并单元格  
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        private bool MergedRegionNum(ISheet sheet, int rownum)
        {
            HSSFRow row = (HSSFRow)sheet.GetRow(rownum);
            if (row == null)
            {
                return false;
            }
            foreach (ICell c in row)
            {
                if (c.IsMergedCell)
                {
                    return true;
                }
            }
            return false;
        }
        /// <summary>
        ///  生成文件
        /// </summary>
        /// <param name="createFileName"></param>
        private void WriteToFile(string createFileName)
        {
            //string strpath = CreateDirectory();
            //写数据流的工作簿的根目录
            var path = HttpContext.Current.Server.MapPath("~/upload");
            FileStream file = new FileStream(path + @"/" + createFileName + ".xls", FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
            DownLoadFile(createFileName);//下载文件
        }

        /// <summary>
        ///  生成文件打印时用的
        /// </summary>
        /// <param name="createFileName"></param>
        private void WriteToFilePrint(string createFileName)
        {
            //写数据流的工作簿的根目录
            FileStream file = new FileStream(createFileName, FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();           
        }
        private void WriteToFile(string createFileName, string newexpoert)
        {
            //string strpath = CreateDirectory();
            //写数据流的工作簿的根目录
            var path = HttpContext.Current.Server.MapPath("~/upload");
            FileStream file = new FileStream(path + @"/" + createFileName + ".xls", FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
            //DownLoadFile(createFileName);//下载文件
            //DownLoadFile(createFileName);//下载文件
        }

        /// <summary>
        /// 文件下载
        /// </summary>
        /// <param name="strfileName"></param>
        private void DownLoadFile(string strfileName)
        {
            //文件下载
            string fileName = strfileName;//客户端保存的文件名
            string filePath = HttpContext.Current.Server.MapPath("~/upload/" + strfileName + ".xls");//服务端存储路径
            //以字符流的形式下载文件
            FileStream fs = new FileStream(filePath, FileMode.Open);
            byte[] bytes = new byte[(int)fs.Length];
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();

            HttpContext.Current.Response.Charset = "utf-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            HttpContext.Current.Response.ContentType = "application/octet-stream";
            //通知浏览器下载文件而不是打开
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;   filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls");
            HttpContext.Current.Response.BinaryWrite(bytes);
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
        }

        /// 创建文件夹
        /// </summary>
        /// <returns></returns>
        private string CreateDirectory()
        {
            string path = "";
            string physicsPath = HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath); //将当前虚拟根路径转为实际物理路径
            string toFindDirectoryName = "upload"; //要查找的文件夹名
            FindDirectory(physicsPath + "\\", toFindDirectoryName, out path);//用递归的方式去查找文件夹
            if (!string.IsNullOrEmpty(path)) //如果存在，返回该文件夹所在的物理路径
            {
                //将该物理路径转为虚拟路径
                GetVirtualPath(path, HttpContext.Current.Request.ApplicationPath);
            }
            else
            {
                //没有找到路径,创建新文件夹
                path = physicsPath + "\\" + toFindDirectoryName;
                Directory.CreateDirectory(path);
            }
            return path;
        }
        /// <summary>
        /// 在指定目录下递归查找子文件夹
        /// </summary>
        /// <param name="bootPath">根文件夹路径</param>
        /// <param name="directoryName">要查找的文件夹名</param>
        private void FindDirectory(string bootPath, string directoryName, out string filePath)
        {
            //在指定目录下递归查找子文件夹
            DirectoryInfo dir = new DirectoryInfo(bootPath);
            filePath = "";
            try
            {
                foreach (DirectoryInfo d in dir.GetDirectories()) //查找子文件夹
                {
                    if (d.Name == directoryName) //找到,返回文件夹路径
                    {
                        filePath = d.FullName;
                        break;
                    }
                    FindDirectory(bootPath + d.Name + "\\", directoryName, out filePath); //否则继续查找
                }
            }
            catch (Exception e)
            {
                HttpContext.Current.Response.Write(e.Message);
            }
        }
        /// <summary>
        /// 将物理路径转为虚拟路径
        /// </summary>
        /// <param name="physicsPath">物理路径</param>
        /// <param name="virtualRootPath">虚拟根路径</param>
        /// <returns></returns>
        private string GetVirtualPath(string physicsPath, string virtualRootPath)
        {
            int index = physicsPath.IndexOf(virtualRootPath.Substring(1));
            return "/" + physicsPath.Substring(index).Replace("\\", "/");
        }

        /// <summary>
        /// 判断指定的单元格是否是合并单元格  
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        private List<int> MergedRegionNum(ISheet sheet, int row, int column, out bool isflag)
        {
            List<int> MergedRegionNum = new List<int>();
            int sheetMergeCount = sheet.NumMergedRegions;
            for (int i = 0; i < sheetMergeCount; i++)
            {
                CellRangeAddress ca = sheet.GetMergedRegion(i);
                int firstColumn = ca.FirstColumn;
                int lastColumn = ca.LastColumn;
                int firstRow = ca.FirstRow;
                int lastRow = ca.LastRow;
                if (row >= firstRow && row <= lastRow)
                {
                    if (column >= firstColumn && column <= lastColumn)
                    {
                        isflag = true;
                        MergedRegionNum.Add(firstRow);
                        MergedRegionNum.Add(firstColumn);
                        MergedRegionNum.Add(lastRow);
                        MergedRegionNum.Add(lastColumn);
                        return MergedRegionNum;
                    }
                }
            }
            isflag = false;
            return null;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.SS.UserModel;

namespace XX
{
    public class ExcelModel
    {
        /// <summary>
        /// 行索引
        /// </summary>
        public int RowIndex { get; set; }
        /// <summary>
        /// 对应数据源的列名
        /// </summary>
        public string ColumnName { get; set; }
        /// <summary>
        /// 对应excel中的列索引
        /// </summary>
        public int ColumnIndex { get; set; }
        /// <summary>
        /// 对应excel中的列类型
        /// </summary>
        public CellType ColumnType { get; set; }
        /// <summary>
        /// 对应excel中的列样式
        /// </summary>
        public ICellStyle ColumnStyle { get; set; }
        /// <summary>
        /// 对应excel中的列的背景色
        /// </summary>
        public string BgColor { get; set; }
        /// <summary>
        /// 对应excel中的列的字体样式
        /// </summary>
        public string FontSize { get; set; }
        /// <summary>
        /// 设置列值的类型，0：直接替换，1：行循环替换，2：列循环
        /// </summary>
        public int SetValueType { get; set; }
        /// <summary>
        /// excel模板中原有的值
        /// </summary>
        public string OldValue { get; set; }
    }

    public class ExcelModel1
    {
        /// <summary>
        /// 行索引
        /// </summary>
        public int RowIndex { get; set; }
        /// <summary>
        /// 列索引
        /// </summary>
        public int ColIndex { get; set; }
        /// <summary>
        /// 行跨数
        /// </summary>
        public int RowSpan { get; set; }
        /// <summary>
        /// 列跨数
        /// </summary>
        public int ColSpan { get; set; }
        /// <summary>
        /// 单元格值
        /// </summary>
        public string CellValue { get; set; }
        /// <summary>
        /// 单元格样式: left center right 
        /// </summary>
        public string Align { get; set; }
        /// <summary>
        /// 单元格样式: top center bottom 
        /// </summary>
        public string vAlign { get; set; }
        /// <summary>
        /// 单元格样式: bold normal
        /// </summary>
        public string Boldweight { get; set; }
        /// <summary>
        /// 单元格样式: 12*12 15*15
        /// </summary>
        public short FontHeight { get; set; }
    }
}

一：上面两个是excel操作基础类。（直接粘贴过来的，有些多余代码可根据实际进行删减）

二：程序用到的打印是在导出基础上做的。

三：用的excel的dll下载地址 http://www.cr173.com/soft/46612.html#address

         DataTable dt = GetData_DataSource();
                if (dt != null && dt.Rows.Count > 0)
                {
                    try
                    {
                        ExcelExportHelper exelhelper = new ExcelExportHelper();
                        string xdfileName = "自然人LP信息" + "_" + siteAdminInfo.user_name + "_" + DateTime.Now.ToString("yyyyddmmhhss") + ".xls";
                        string fileName = Server.MapPath("~/temp/" + xdfileName);//文件名

                        if (!Directory.Exists(Server.MapPath("~/temp")))
                        //如果不存在就创建file文件夹
                        {
                            Directory.CreateDirectory(Server.MapPath("~/temp"));
                        }

                        //先删除之前导出存在的文件
                        Utils.DeleteFile(xdfileName);
                        string mbfileName = Server.MapPath("~/template/自然人LP信息.xls");
                        //导出要打印内容到filenname临时文件
                        exelhelper.ExprotExcelNotDownload(fileName, mbfileName, dt, new System.Collections.Hashtable(), 0);

                        try
                        {
                            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                            //不可或缺  
                            excelApp.Visible = true;

                            Microsoft.Office.Interop.Excel.Workbook wb = excelApp.Workbooks.Open(fileName, Type.Missing, Type.Missing,
                                                                                                        Type.Missing, Type.Missing,
                                                                                                        Type.Missing, Type.Missing,
                                                                                                        Type.Missing, Type.Missing,
                                                                                                        Type.Missing, Type.Missing,
                                                                                                        Type.Missing, Type.Missing);
                            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
                            bool userDidntCancel = excelApp.Dialogs[Microsoft.Office.Interop.Excel.XlBuiltInDialog.xlDialogPrintPreview].Show(
                                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);




                            GC.Collect();
                            GC.WaitForPendingFinalizers();
                            wb.Close(false, Type.Missing, Type.Missing);
                            excelApp.Quit();

                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }    

 

判断 Office 版本

office97 : 8.0

office2000 : 9.0

officeXP(2002) : 10.0

office2003 : 11.0

office2007: 12.0

office2010 : 14.0

office2013: 15.0

 

excel版本下载 http://download.csdn.net/detail/believeys/9636996

npoi的dll下载 （NPOI.2.1.3.1.zip）http://download.csdn.net/detail/believeys/9637012