using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace CSR_Web.Common

{

    public   class NPOIExport

    {

        public static NPOI.HSSF.UserModel.HSSFWorkbook DoExport(System.Data.DataTable dt, string notile)

        {

            //创建工作簿

            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

            //创建表

            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(notile);

            //自适应列宽

          //  sheet.AutoSizeColumn(1, true);

            //标题行合并单元格

            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , dt.Columns.Count-));

            NPOI.SS.UserModel.IRow firstrow = sheet.CreateRow();

            NPOI.SS.UserModel.ICell firstcell = firstrow.CreateCell();

            //表名样式

            NPOI.SS.UserModel.ICellStyle styleHeader = book.CreateCellStyle();

            NPOI.SS.UserModel.IFont fontHeader = book.CreateFont();

            styleHeader.Alignment =NPOI.SS.UserModel.HorizontalAlignment.Center;

            styleHeader.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

            fontHeader.FontHeightInPoints =;

            styleHeader.SetFont(fontHeader);

            firstcell.CellStyle = styleHeader;

            firstcell.SetCellValue(notile);

            try

            {

                //列名样式

                NPOI.SS.UserModel.ICellStyle styleColName = book.CreateCellStyle();

                NPOI.SS.UserModel.IFont fontColName = book.CreateFont();

                styleColName.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

                styleColName.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

                fontColName.FontHeightInPoints = ;

                styleColName.SetFont(fontColName);

                //数据的样式、字体大小

                NPOI.SS.UserModel.ICellStyle styleBody = book.CreateCellStyle();

                NPOI.SS.UserModel.IFont fontBody = book.CreateFont();

                styleBody.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

                styleBody.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

                fontBody.FontHeightInPoints = ;

                styleBody.SetFont(fontBody);

                //创建具体单元格数据

                int rowCount = dt.Rows.Count;

                int colCount = dt.Columns.Count;

                NPOI.SS.UserModel.IRow colNameRow = sheet.CreateRow();

                for (int x = ; x < colCount; x++) { //将列名写入单元格

                    NPOI.SS.UserModel.ICell colNameCell = colNameRow.CreateCell(x);

                    colNameCell.SetCellValue(dt.Columns[x].ColumnName);

                    colNameCell.CellStyle = styleColName;

                }

                    for (int i = ; i < rowCount; i++)

                    {

                        NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + );//数据从第三行开始 第一行表名 第二行列名

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

                        {

                            //填充数据

                            NPOI.SS.UserModel.ICell cell = row.CreateCell(j);

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

                            {

                                cell.SetCellValue(dt.Rows[i][j].ToString());

                            }

                            else

                            {

                                cell.SetCellValue("");

                            }

                            cell.CellStyle = styleBody;

                        }

                    }

                //自适应列宽

                for (int x = ; x < colCount; x++)

                {

                    sheet.AutoSizeColumn(x, true);

   }

　　　　　　　　　　　　

//插入图片
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, 100, 50, 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();这句话一定不要，这是用图片原始大小来显示
}

//此处代码是将 xls文件发到页面通过浏览器直接下载到本地　　可以放到　界面调用的地方

                //System.IO.MemoryStream ms = new System.IO.MemoryStream();

                //book.Write(ms);

                //Response.AddHeader("Content-Disposition", string.Format("attachment; filename=绩效统计.xls"));

                //Response.BinaryWrite(ms.ToArray());

                //book = null;

                //ms.Close();

                //ms.Dispose();  

                return book;

            }

            catch {

            throw new Exception();

            }finally{

                book=null;

            }

        }

    }

}

  /// <summary>

        ///导出

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        protected void btnExport_Click(object sender, EventArgs e)

      {

            DataTable dt = cmbll.getdt();

            NPOI.HSSF.UserModel.HSSFWorkbook book = NPOIExport.DoExport(dt, "xxx报表");

            //写入客户端

            try

            {

                WriteClient(book);

            }

            catch

            {

            }

            finally

            {

                book = null;

            }

        }

        public void WriteClient(NPOI.HSSF.UserModel.HSSFWorkbook book)

        {

            System.IO.MemoryStream ms = new System.IO.MemoryStream();

            book.Write(ms);

            Response.AddHeader("Content-Disposition", string.Format("attachment; filename=客户资料"+DateTime.Now.ToString("yyyyMMddHHmmss")+".xls"));

            Response.BinaryWrite(ms.ToArray());

            book = null;

            ms.Close();

            ms.Dispose();

        }