NPOI 2.0版本的使用

最新推荐文章于 2021-06-24 05:17:39 发布

weixin_34357267

最新推荐文章于 2021-06-24 05:17:39 发布

阅读量150

点赞数

文章标签： ui javascript ViewUI

原文链接：http://www.cnblogs.com/vichin/p/6209805.html

版权

详细教程： http://blog.csdn.net/xxs77ch/article/details/50216033

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 < 1)
                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(0);//创建一个对象，该对象表示着第一行（在createRow方法中，参数0能够得到体现）。
            ICell dataCell = dataRow.CreateCell(0);//创建一个对象，该对象表示着第一的第一列（在createCell方法中，参数0能够得到体现）。

            #region 头部
            dataCell.SetCellValue("头部信息");
            IFont font = book.CreateFont();//创建字体样式。这里只是创建了一个字体样式，
            ICellStyle style = book.CreateCellStyle();//创建一个样式

            font.FontName = "宋体";
            font.Boldweight = short.MaxValue;
            font.FontHeightInPoints = 28;

            font.Color = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
            style.SetFont(font);//将字体样式加到样式对象中去。
            dataCell.CellStyle = style;//将样式作用于dataCell对象，这个对象前面有提到过，指的是第一行第一列
            sheet.SetColumnWidth(0, 18 * 256);//设置第0行的列宽是18*256（256指的是字符）
            sheet.SetColumnWidth(1, 50 * 256);//设置第1行列宽是50*256
            sheet.SetColumnWidth(2, 28 * 256);
            sheet.SetColumnWidth(3, 18 * 256);

            ICellStyle style2 = book.CreateCellStyle();
            IFont font2 = book.CreateFont();
            font2.FontHeightInPoints = 20;
            font2.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;

            style2.SetFont(font2);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1, 3));//合并单元格，前两位表示起始行，结束行。后两个表示起始列，结束列。
            dataCell = dataRow.CreateCell(1);
            style2.Alignment = HorizontalAlignment.Center;
            style2.VerticalAlignment = VerticalAlignment.Center;
            dataCell.SetCellValue("设置值");
            dataCell.CellStyle = style2;


            ICellStyle style3 = book.CreateCellStyle();
            dataRow = sheet.CreateRow(1);
            dataCell = dataRow.CreateCell(0);
            dataCell.SetCellValue("设置值");
            sheet.AddMergedRegion(new CellRangeAddress(1, 3, 0, 0));
            style3.Alignment = HorizontalAlignment.Center;
            style3.VerticalAlignment = VerticalAlignment.Center;
            dataCell.CellStyle = style3;
            ///设置机种名称
            sheet.AddMergedRegion(new CellRangeAddress(1, 3, 1, 1));
            dataCell = dataRow.CreateCell(1);
            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(1,3,0,0));
            dataCell = dataRow.CreateCell(2);
            dataCell.SetCellValue("设置值");
            ICellStyle styleMachineType0 = book.CreateCellStyle();
            styleMachineType0.Alignment = HorizontalAlignment.Left;
            dataCell.CellStyle = styleMachineType0;

            dataRow = sheet.CreateRow(2);
            dataCell = dataRow.CreateCell(2);
            dataCell.SetCellValue("4H.15D36.A01");
            ICellStyle styleMachineType = book.CreateCellStyle();
            styleMachineType.Alignment = HorizontalAlignment.Left;
            dataCell.CellStyle = styleMachineType;

            //dataRow = sheet.CreateRow(2);
            dataCell = dataRow.CreateCell(3);
            dataCell.SetCellValue("设置值");

            dataRow = sheet.CreateRow(3);
            dataCell = dataRow.CreateCell(2);
            dataCell.SetCellValue("B156HAN");
            ICellStyle styleMachineType3 = book.CreateCellStyle();
            styleMachineType3.Alignment = HorizontalAlignment.Left;
            dataCell.CellStyle = styleMachineType3;
            ///设置机种名称
           

            #endregion

            #region 中间
            dataRow = sheet.CreateRow(4);
            ICellStyle style4 = book.CreateCellStyle();
            IFont font3 = book.CreateFont();
            font3.FontHeightInPoints = 18;
            style4.SetFont(font3);
            style4.Alignment = HorizontalAlignment.Center;
            style4.VerticalAlignment = VerticalAlignment.Center;

            string strColumns = "第一列，第二列，第三列";
            string[] strArry = strColumns.Split(',');
            for (int i = 0; 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 = 0; i < dt.Rows.Count; i++)
            {
                dataRow = sheet.CreateRow(i + 5);
                for (int j = 0; j < 3; 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 == 2) && (Value != "OK"))
                            {
                                dataRow.CreateCell(j).SetCellValue("OK");
                                //dataRow.CreateCell(j + 1).CellStyle = bodyStyle;
                                //dataRow.CreateCell(j + 1).SetCellValue(Value);
                                dataCell = dataRow.CreateCell(j + 1);
                                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 = 0;
                            int.TryParse(Value, out intV);
                            dataRow.CreateCell(j).SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            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 != 1)
                        dataRow.GetCell(j).CellStyle = bodyStyle;
                    //dataRow.GetCell(j).CellStyle = style;

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

            #region 底部
            dataRow = sheet.CreateRow(5 + dt.Rows.Count);
            dataCell = dataRow.CreateCell(0);
            dataCell.SetCellValue("注：");
            ICellStyle styleFoot = book.CreateCellStyle();
            styleFoot.Alignment = HorizontalAlignment.Right;
            styleFoot.VerticalAlignment = VerticalAlignment.Top;
            IFont fontFoot = book.CreateFont();
            fontFoot.FontHeightInPoints = 20;
            styleFoot.SetFont(fontFoot);
            dataCell.CellStyle = styleFoot;
            sheet.AddMergedRegion(new CellRangeAddress(5 + dt.Rows.Count, 5 + dt.Rows.Count + 4, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(6 + dt.Rows.Count, 6 + dt.Rows.Count, 1, 2));
            sheet.AddMergedRegion(new CellRangeAddress(7 + dt.Rows.Count, 7 + dt.Rows.Count, 1, 2));
            sheet.AddMergedRegion(new CellRangeAddress(8 + dt.Rows.Count, 8 + dt.Rows.Count, 1, 2));
            sheet.AddMergedRegion(new CellRangeAddress(5 + dt.Rows.Count, 5 + dt.Rows.Count, 1, 2));
            dataRow = sheet.CreateRow(6 + dt.Rows.Count);
            dataCell = dataRow.CreateCell(1);
            dataCell.SetCellValue("设置值");
            dataRow = sheet.CreateRow(7 + dt.Rows.Count);
            dataCell = dataRow.CreateCell(1);
            dataCell.SetCellValue("设置值");
            dataRow = sheet.CreateRow(8 + dt.Rows.Count);
            dataCell = dataRow.CreateCell(1);
            dataCell.SetCellValue("设置值");
            dataRow = sheet.CreateRow(9 + dt.Rows.Count);
            dataCell = dataRow.CreateCell(1);
            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(0, 0, 1023, 0, 0, 0, 1, 3);//设置图片的尺寸及大小
        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();
        }
    }
}