NPOI操作Execl

    using System;  
    using System.Collections.Generic;  
    using System.Linq;  
    using System.Web;  
    using System.Web.UI;  
    using System.Web.UI.WebControls;  
    //add  
    using System.Data;  
    using System.IO;  
    using NPOI;  
    using NPOI.HSSF.UserModel;  
      
      
    public partial class ExeclOperation : System.Web.UI.Page  
    {  
        #region 页面加载  
        protected void Page_Load(object sender, EventArgs e)  
        {  
            DataTable dt = ReadExcelToDataTable("~/xls/demo001.xls", 0, 0);  
            ViewState["dtview"] = dt;  
            GridView1.DataSource = dt;  
            GridView1.DataBind();  
        }  
        #endregion   
     
        #region DS直接生成Execl  
        protected void btnExport_Click(object sender, EventArgs e)  
        {  
            DataSet ds = new DataSet();  
            DataTable dt = ViewState["dtview"] as DataTable;  
            ds.Tables.Add(dt);  
            bool success = ExportExcelByDataSet(ds, "~/xls/", "demo.xls", "这是测试数据");  
            if (success)  
            {  
                ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "alert1", "alert('生成execl文件成功')", true);  
            }  
            else  
            {  
                ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "alert2", "alert('生成execl文件失败')", true);  
            }  
        }  
        #endregion   
     
        #region GridView自动列适应，不换行  
        protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)  
        {  
            if (e.Row.RowType == DataControlRowType.DataRow || e.Row.RowType == DataControlRowType.Header)  
            {  
                TableCellCollection cells1 = e.Row.Cells;  
                for (int i = 0; i < cells1.Count; i++)  
                {  
                    cells1[i].Wrap = false;  
                }  
            }  
        }   
        #endregion   
     
        #region DataSet与Execl互转  
        /// <summary>  
        /// 传入ds直接生成excel文件  
        /// </summary>  
        /// <param name="ds">DataSet</param>  
        /// <param name="strPath">文件路径</param>  
        /// <param name="strFileName">文件名</param>  
        /// <param name="ReportHeader">execl表头</param>  
        /// <returns></returns>  
        public static bool ExportExcelByDataSet(DataSet ds, string strPath, string strFileName, string ReportHeader = "")  
        {  
            //NPOI   
            HSSFWorkbook hssfworkbook2 = new HSSFWorkbook();  
            HSSFSheet sheet = (HSSFSheet)hssfworkbook2.CreateSheet("sheet1");  
            //定义字体 font   设置字体类型和大小  
            HSSFFont font = (HSSFFont)hssfworkbook2.CreateFont();  
            font.FontName = "宋体";  
            font.FontHeightInPoints = 11;  
      
            //定义单元格格式；单元格格式style1 为font的格式  
            HSSFCellStyle style1 = (HSSFCellStyle)hssfworkbook2.CreateCellStyle();  
            style1.SetFont(font);  
            style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;  
      
            HSSFCellStyle style2 = (HSSFCellStyle)hssfworkbook2.CreateCellStyle();  
            style2.SetFont(font);  
            style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;  
            style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;  
            style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;  
            style2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;  
            style2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;  
            //style2.WrapText = true;  
      
            //设置大标题行  
            int RowCount = 0;  
            int arrFlag = 0;  
            string TileName1 = "";  
            string TileName2 = "";  
      
            string s = ReportHeader;  
            string[] sArray = s.Split('|');  
            if (ReportHeader != "")  
            {  
                foreach (string i in sArray)  
                {  
                    string str1 = i.ToString();  
                    string[] subArray = str1.Split('@');  
                    foreach (string k in subArray)  
                    {  
                        Console.WriteLine(k.ToString());  
                        if (arrFlag == 0)  
                        {  
                            TileName1 = k.ToString();  
                        }  
                        else  
                        {  
                            TileName2 = k.ToString();  
                        }  
                        arrFlag = arrFlag + 1;  
                    }  
                    HSSFRow row0 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题  8列  
                    row0.CreateCell(0).SetCellValue(TileName1);  
                    row0.CreateCell(1).SetCellValue(TileName2);  
                    RowCount = RowCount + 1;  
                    arrFlag = 0;  
                }  
            }  
            //设置全局列宽和行高  
            sheet.DefaultColumnWidth = 14;//全局列宽  
            sheet.DefaultRowHeightInPoints = 15;//全局行高  
            //设置标题行数据  
            int a = 0;  
            string mColumnName = "";  
      
            HSSFRow row1 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题  8列  
            for (int k = 0; k < ds.Tables[0].Columns.Count; k++)  
            {  
      
                mColumnName = ds.Tables[0].Columns[k].ColumnName.ToString();  
                row1.CreateCell(a).SetCellValue(mColumnName);  
                row1.Cells[a].CellStyle = style2;  
                a++;  
      
            }  
            //填写ds数据进excel  
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)//写6行数据  
            {  
                HSSFRow row2 = (HSSFRow)sheet.CreateRow(i + RowCount + 1);  
                int b = 0;  
                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)  
                {  
                    string DgvValue = "";  
                    DgvValue = ds.Tables[0].Rows[i][j].ToString(); ;  
                    row2.CreateCell(b).SetCellValue(DgvValue);  
                    b++;  
                }  
            }  
            //获取用户选择路径  
            string ReportPath = HttpContext.Current.Server.MapPath(strPath + strFileName);  
      
            //创建excel  
            System.IO.FileStream file3 = new FileStream(ReportPath, FileMode.Create);  
            hssfworkbook2.Write(file3);  
            file3.Close();  
            return true;  
        }  
      
        /// <summary>  
        /// 用NPOI直接读取excel返回DataTable  
        /// </summary>  
        /// <param name="ExcelFileStream">文件流</param>  
        /// <param name="SheetIndex">Sheet序号</param>  
        /// <param name="StartRowIndex">开始行号</param>  
        /// <returns></returns>  
        public static DataTable ReadExcelToDataTable(Stream ExcelFileStream, int SheetIndex, int StartRowIndex)  
        {  
            HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);  
            HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(SheetIndex);  
      
            DataTable table = new DataTable();  
            HSSFRow headerRow = (HSSFRow)sheet.GetRow(StartRowIndex);  
            int cellCount = headerRow.LastCellNum;  
      
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)  
            {  
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);  
                table.Columns.Add(column);  
            }  
            int rowCount = sheet.LastRowNum;  
            for (int i = (StartRowIndex + 1); i <= sheet.LastRowNum; i++)  
            {  
                HSSFRow row = (HSSFRow)sheet.GetRow(i);  
                DataRow dataRow = table.NewRow();  
                for (int j = row.FirstCellNum; j < cellCount; j++)  
                {  
                    if (row.GetCell(j) != null)  
                        dataRow[j] = row.GetCell(j).ToString();  
                }  
                table.Rows.Add(dataRow);  
            }  
            ExcelFileStream.Close();  
            workbook = null;  
            sheet = null;  
            return table;  
        }  
      
        /// <summary>  
        /// 用NPOI直接读取excel返回DataTable  
        /// </summary>  
        /// <param name="FilePath">文件路径</param>  
        /// <param name="SheetIndex">Sheet序号</param>  
        /// <param name="StartRowIndex">开始行号</param>  
        /// <returns></returns>  
        public static DataTable ReadExcelToDataTable(string FilePath, int SheetIndex, int StartRowIndex)  
        {  
            DataSet ds = new DataSet();  
            FileStream fs = File.Open(HttpContext.Current.Server.MapPath(FilePath), FileMode.Open);  
            DataTable dt = ReadExcelToDataTable(fs, SheetIndex, StartRowIndex);  
            return dt;  
        }  
        #endregion   
    }  

    /// <summary>  
    ///传入ds直接生成excel在服务器目录上  
    /// </summary>  
    /// <param name="ds">ds包含多个DataTable</param>  
    /// <param name="strPath"></param>  
    /// <param name="strFileName"></param>  
    /// <returns></returns>  
    protected static bool ExportExcelByDataSet(DataSet ds, string strPath, string strFileName, string ReportHeader = "")  
    {  
        //NPOI   
        HSSFWorkbook hssfworkbook2 = new HSSFWorkbook();  
        #region 开始循环DS中的Table  
        for (int p = 0; p < ds.Tables.Count; p++)  
        {  
            #region 创建一个sheet  
            HSSFSheet sheet = (HSSFSheet)hssfworkbook2.CreateSheet("sheet" + (p + 1));  
            //定义字体 font   设置字体类型和大小  
            HSSFFont font = (HSSFFont)hssfworkbook2.CreateFont();  
            font.FontName = "宋体";  
            font.FontHeightInPoints = 11;  
      
            //定义单元格格式；单元格格式style1 为font的格式  
            HSSFCellStyle style1 = (HSSFCellStyle)hssfworkbook2.CreateCellStyle();  
            style1.SetFont(font);  
            style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;  
      
            HSSFCellStyle style2 = (HSSFCellStyle)hssfworkbook2.CreateCellStyle();  
            style2.SetFont(font);  
            style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;  
            style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;  
            style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;  
            style2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;  
            style2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;  
            //style2.WrapText = true;  
      
            //设置大标题行  
            int RowCount = 0;  
            int arrFlag = 0;  
            string TileName1 = "";  
            string TileName2 = "";  
      
            string s = ReportHeader;  
            string[] sArray = s.Split('|');  
            if (ReportHeader != "")  
            {  
                foreach (string i in sArray)  
                {  
                    string str1 = i.ToString();  
                    string[] subArray = str1.Split('@');  
                    foreach (string k in subArray)  
                    {  
                        Console.WriteLine(k.ToString());  
                        if (arrFlag == 0)  
                        {  
                            TileName1 = k.ToString();  
                        }  
                        else  
                        {  
                            TileName2 = k.ToString();  
                        }  
                        arrFlag = arrFlag + 1;  
                    }  
                    HSSFRow row0 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题  8列  
                    row0.CreateCell(0).SetCellValue(TileName1);  
                    row0.CreateCell(1).SetCellValue(TileName2);  
                    RowCount = RowCount + 1;  
                    arrFlag = 0;  
                }  
            }  
      
            //设置全局列宽和行高  
            sheet.DefaultColumnWidth = 14;//全局列宽  
            sheet.DefaultRowHeightInPoints = 15;//全局行高  
            //设置标题行数据  
            int a = 0;  
            string mColumnName = "";  
      
            HSSFRow row1 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题  8列  
            for (int k = 0; k < ds.Tables[p].Columns.Count; k++)  
            {  
                mColumnName = ds.Tables[p].Columns[k].ColumnName.ToString();  
                row1.CreateCell(a).SetCellValue(mColumnName);  
                row1.Cells[a].CellStyle = style2;  
                a++;  
            }  
      
            //填写ds数据进excel  
            //数据  
            for (int i = 0; i < ds.Tables[p].Rows.Count; i++)//写6行数据  
            {  
                HSSFRow row2 = (HSSFRow)sheet.CreateRow(i + RowCount + 1);  
                int b = 0;  
                for (int j = 0; j < ds.Tables[p].Columns.Count; j++)  
                {  
                    string DgvValue = "";  
                    DgvValue = ds.Tables[p].Rows[i][j].ToString(); ;  
                    row2.CreateCell(b).SetCellValue(DgvValue);  
                    b++;  
                }  
            }  
            #endregion  
        }  
        #endregion  
      
        //获取用户选择路径  
        string ReportPath = strPath + strFileName;  
        //创建excel  
        System.IO.FileStream file3 = new FileStream(ReportPath, FileMode.Create);  
        hssfworkbook2.Write(file3);  
        file3.Close();  
        return true;  
    }