using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Configuration;

using System.Reflection;

using WSC.Common;

using System.IO;

using NPOI.SS.UserModel;

using NPOI.HSSF.UserModel;

using NPOI.SS.Util;

namespace WebApplication2

{

    public partial class _Default : System.Web.UI.Page

    {

        private string constr = ConfigurationManager.AppSettings["LocalConnectionString"];

        protected void Page_Load(object sender, EventArgs e)

        {

        }

        private DataTable getDT(string fid)

        {

            DataTable dt = new DataTable();

            if (string.IsNullOrEmpty(fid))

                Response.Write("");

            else

            {

                SQLHelper s = new SQLHelper(constr);

                string sql = @"";

                dt = s.Query(sql);

            }

            return dt;

        }

        protected void Button1_Click(object sender, EventArgs e)

        {

            string fid = "001";

            DataTable datas = getDT(fid);

            if (datas.Rows.Count < )

                Response.Write("<script type=\"text/javascript\">alert('无相关信息，请先维护！')</script>");

            else

            {

                ExportExcel(datas, "nameExcel", "sheetName");

            }

        }

        /// <summary>

        /// DataTable导出Excel

        /// </summary>

        /// <param name="dt">datatable数据源</param>

        /// <param name="strFileName">文件名</param>

        /// <param name="strSheetName">工作簿名</param>

        public void ExportExcel(DataTable dt, string strFileName, string strSheetName)

        {

            IWorkbook book = new HSSFWorkbook();

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

            IRow dataRow = sheet.CreateRow();//创建一个对象，该对象表示着第一行（在createRow方法中，参数0能够得到体现）。

            ICell dataCell = dataRow.CreateCell();//创建一个对象，该对象表示着第一的第一列（在createCell方法中，参数0能够得到体现）。

            #region 头部

            dataCell.SetCellValue("头部信息");

            IFont font = book.CreateFont();//创建字体样式。这里只是创建了一个字体样式，

            ICellStyle style = book.CreateCellStyle();//创建一个样式

            font.FontName = "宋体";

            font.Boldweight = short.MaxValue;

            font.FontHeightInPoints = ;

            font.Color = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;

            style.SetFont(font);//将字体样式加到样式对象中去。

            dataCell.CellStyle = style;//将样式作用于dataCell对象，这个对象前面有提到过，指的是第一行第一列

            sheet.SetColumnWidth(,  * );//设置第0行的列宽是18*256（256指的是字符）

            sheet.SetColumnWidth(,  * );//设置第1行列宽是50*256

            sheet.SetColumnWidth(,  * );

            sheet.SetColumnWidth(,  * );

            ICellStyle style2 = book.CreateCellStyle();

            IFont font2 = book.CreateFont();

            font2.FontHeightInPoints = ;

            font2.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;

            style2.SetFont(font2);

            sheet.AddMergedRegion(new CellRangeAddress(, , , ));//合并单元格，前两位表示起始行，结束行。后两个表示起始列，结束列。

            dataCell = dataRow.CreateCell();

            style2.Alignment = HorizontalAlignment.Center;

            style2.VerticalAlignment = VerticalAlignment.Center;

            dataCell.SetCellValue("设置值");

            dataCell.CellStyle = style2;

            ICellStyle style3 = book.CreateCellStyle();

            dataRow = sheet.CreateRow();

            dataCell = dataRow.CreateCell();

            dataCell.SetCellValue("设置值");

            sheet.AddMergedRegion(new CellRangeAddress(, , , ));

            style3.Alignment = HorizontalAlignment.Center;

            style3.VerticalAlignment = VerticalAlignment.Center;

            dataCell.CellStyle = style3;

            sheet.AddMergedRegion(new CellRangeAddress(, , , ));

            dataCell = dataRow.CreateCell();

            dataCell.SetCellValue("设置值");

            ICellStyle styleType = book.CreateCellStyle();

            styleType.Alignment = HorizontalAlignment.Right;

            styleType.VerticalAlignment = VerticalAlignment.Center;

            dataCell.CellStyle = styleType;

            ICellStyle styleMachineType1 = book.CreateCellStyle();

            styleMachineType1.VerticalAlignment = VerticalAlignment.Center;

            styleMachineType1.Alignment = HorizontalAlignment.Right;

            sheet.AddMergedRegion(new CellRangeAddress(,,,));

            dataCell = dataRow.CreateCell();

            dataCell.SetCellValue("设置值");

            ICellStyle styleMachineType0 = book.CreateCellStyle();

            styleMachineType0.Alignment = HorizontalAlignment.Left;

            dataCell.CellStyle = styleMachineType0;

            dataRow = sheet.CreateRow();

            dataCell = dataRow.CreateCell();

            dataCell.SetCellValue("4H.15D36.A01");

            ICellStyle styleMachineType = book.CreateCellStyle();

            styleMachineType.Alignment = HorizontalAlignment.Left;

            dataCell.CellStyle = styleMachineType;

            //dataRow = sheet.CreateRow(2);

            dataCell = dataRow.CreateCell();

            dataCell.SetCellValue("设置值");

            dataRow = sheet.CreateRow();

            dataCell = dataRow.CreateCell();

            dataCell.SetCellValue("B156HAN");

            ICellStyle styleMachineType3 = book.CreateCellStyle();

            styleMachineType3.Alignment = HorizontalAlignment.Left;

            dataCell.CellStyle = styleMachineType3;

            #endregion

            #region 中间

            dataRow = sheet.CreateRow();

            ICellStyle style4 = book.CreateCellStyle();

            IFont font3 = book.CreateFont();

            font3.FontHeightInPoints = ;

            style4.SetFont(font3);

            style4.Alignment = HorizontalAlignment.Center;

            style4.VerticalAlignment = VerticalAlignment.Center;

            string strColumns = "第一列，第二列，第三列";

            string[] strArry = strColumns.Split(',');

            for (int i = ; i < strArry.Length; i++)

            {

                dataRow.CreateCell(i).SetCellValue(strArry[i]);

                dataRow.GetCell(i).CellStyle = style4;//设置样式

            }

                    ICellStyle bodyStyle = book.CreateCellStyle();

                    bodyStyle.Alignment = HorizontalAlignment.Center;

            for (int i = ; i < dt.Rows.Count; i++)

            {

                dataRow = sheet.CreateRow(i + );

                for (int j = ; j < ; j++)

                {

                    string ValueType = "";

                    string Value = "";

                    if (dt.Rows[i][j].ToString() != null)

                    {

                        ValueType = dt.Rows[i][j].GetType().ToString();

                        Value = dt.Rows[i][j].ToString();

                    }

                    switch (ValueType)

                    {

                        case "System.String"://字符串类型

                            if ((j == ) && (Value != "OK"))

                            {

                                dataRow.CreateCell(j).SetCellValue("OK");

                                //dataRow.CreateCell(j + 1).CellStyle = bodyStyle;

                                //dataRow.CreateCell(j + 1).SetCellValue(Value);

                                dataCell = dataRow.CreateCell(j + );

                                dataCell.CellStyle = bodyStyle;

                                dataCell.SetCellValue(Value);

                            }

                            else

                            {

                                dataRow.CreateCell(j).SetCellValue(Value);

                            }

                            break;

                        case "System.DateTime"://日期类型

                            System.DateTime dateV;

                            System.DateTime.TryParse(Value, out dateV);

                            dataRow.CreateCell(j).SetCellValue(dateV);

                            break;

                        case "System.Boolean"://布尔型

                            bool boolV = false;

                            bool.TryParse(Value, out boolV);

                            dataRow.CreateCell(j).SetCellValue(boolV);

                            break;

                        case "System.Int16"://整型

                        case "System.Int32":

                        case "System.Int64":

                        case "System.Byte":

                            int intV = ;

                            int.TryParse(Value, out intV);

                            dataRow.CreateCell(j).SetCellValue(intV);

                            break;

                        case "System.Decimal"://浮点型

                        case "System.Double":

                            double doubV = ;

                            double.TryParse(Value, out doubV);

                            dataRow.CreateCell(j).SetCellValue(doubV);

                            break;

                        case "System.DBNull"://空值处理

                            dataRow.CreateCell(j).SetCellValue("");

                            break;

                        default:

                            dataRow.CreateCell(j).SetCellValue("");

                            break;

                    }

                    if (j != )

                        dataRow.GetCell(j).CellStyle = bodyStyle;

                    //dataRow.GetCell(j).CellStyle = style;

                    //sheet.SetColumnWidth(j, (Value.Length + 10) * 256);//设置宽度

                }

            }

            #endregion

            #region 底部

            dataRow = sheet.CreateRow( + dt.Rows.Count);

            dataCell = dataRow.CreateCell();

            dataCell.SetCellValue("注：");

            ICellStyle styleFoot = book.CreateCellStyle();

            styleFoot.Alignment = HorizontalAlignment.Right;

            styleFoot.VerticalAlignment = VerticalAlignment.Top;

            IFont fontFoot = book.CreateFont();

            fontFoot.FontHeightInPoints = ;

            styleFoot.SetFont(fontFoot);

            dataCell.CellStyle = styleFoot;

            sheet.AddMergedRegion(new CellRangeAddress( + dt.Rows.Count,  + dt.Rows.Count + , , ));

            sheet.AddMergedRegion(new CellRangeAddress( + dt.Rows.Count,  + dt.Rows.Count, , ));

            sheet.AddMergedRegion(new CellRangeAddress( + dt.Rows.Count,  + dt.Rows.Count, , ));

            sheet.AddMergedRegion(new CellRangeAddress( + dt.Rows.Count,  + dt.Rows.Count, , ));

            sheet.AddMergedRegion(new CellRangeAddress( + dt.Rows.Count,  + dt.Rows.Count, , ));

            dataRow = sheet.CreateRow( + dt.Rows.Count);

            dataCell = dataRow.CreateCell();

            dataCell.SetCellValue("设置值");

            dataRow = sheet.CreateRow( + dt.Rows.Count);

            dataCell = dataRow.CreateCell();

            dataCell.SetCellValue("设置值");

            dataRow = sheet.CreateRow( + dt.Rows.Count);

            dataCell = dataRow.CreateCell();

            dataCell.SetCellValue("设置值");

            dataRow = sheet.CreateRow( + dt.Rows.Count);

            dataCell = dataRow.CreateCell();

            dataCell.SetCellValue("设置值");

            #endregion

 　　　　#region 插入图片

        byte[] bytes = System.IO.File.ReadAllBytes(@"F:\gch.jpg");//写成流

        int pictureIdx = book.AddPicture(bytes, PictureType.PNG);//book是之前创建工作簿的名字

        HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();//sheet是之前创建的表单

 HSSFClientAnchor anchor = new HSSFClientAnchor(, , , , , , , );//设置图片的尺寸及大小

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

        pict.Resize();

        #endregion
//关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数，有必要在这里说明一下：
//dx1：起始单元格的x偏移量，如例子中的255表示直线起始位置距A1单元格左侧的距离；
//dy1：起始单元格的y偏移量，如例子中的125表示直线起始位置距A1单元格上侧的距离；
//dx2：终止单元格的x偏移量，如例子中的1023表示直线起始位置距C3单元格左侧的距离；
//dy2：终止单元格的y偏移量，如例子中的150表示直线起始位置距C3单元格上侧的距离；
//col1：起始单元格列序号，从0开始计算；
//row1：起始单元格行序号，从0开始计算，如例子中col1=0,row1=0就表示起始单元格为A1；
//col2：终止单元格列序号，从0开始计算；
//row2：终止单元格行序号，从0开始计算，如例子中col2=2,row2=2就表示起始单元格为C3；
一个有关NPOI的demo：http://blog.csdn.net/chinajiyong/article/details/9187485

            MemoryStream ms = new MemoryStream();

            book.Write(ms);

            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8)));

            Response.BinaryWrite(ms.ToArray());

            Response.End();

            book = null;

            ms.Close();

            ms.Dispose();

        }

    }

}