NPOI操作Excel简单示例

     根据网上的资料，学习了一下NPOI操作Excel的基本方法：标题行合并居中、设置列宽、写入列标题及数据。

    public class ExportToExcel : IHttpHandler //一般处理程序ExportToExcel.ashx
    {
        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "application/x-excel";
            string fileName = HttpUtility.UrlEncode("备份.xls");
            context.Response.AddHeader("Content-Disposition", "attachment; fileName=" + fileName); 
            HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个xls；
            HSSFSheet sheet = workbook.CreateSheet("用户信息"); //创建一个Sheet页
            sheet.SetColumnWidth(3, 50 * 256);  //设置列宽，50个字符宽度。宽度参数为1/256，故乘以256
            string connectString = @"server=.\sqlexpress;database=BookShop; uid=sa; pwd=123456";
            SqlConnection connection = new SqlConnection(connectString);
            connection.Open();
            /******************写入标题行，合并居中*********************/
            HSSFRow row = sheet.CreateRow(0);
            HSSFCell cell = row.CreateCell(0);
            cell.SetCellValue("用户信息");
            HSSFCellStyle style = workbook.CreateCellStyle();
            style.Alignment = HSSFCellStyle.ALIGN_CENTER;
            HSSFFont font = workbook.CreateFont();
            font.FontHeight = 20 * 20;
            style.SetFont(font);
            cell.CellStyle = style;
            sheet.AddMergedRegion(new Region(0,0,0,4));
            /******************写入列标题*********************/
            int rowsNum = 1;  //行号
            row = sheet.CreateRow(rowsNum);
            row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue("用户名");
            row.CreateCell(1, HSSFCell.CELL_TYPE_STRING).SetCellValue("密码");
            row.CreateCell(2, HSSFCell.CELL_TYPE_STRING).SetCellValue("姓名");
            row.CreateCell(3, HSSFCell.CELL_TYPE_STRING).SetCellValue("电子邮件");
            row.CreateCell(4, HSSFCell.CELL_TYPE_STRING).SetCellValue("用户组编号");
            using (IDbCommand cmd = connection.CreateCommand()) ;
            {
                cmd.CommandText = "select * from Users";
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    rowsNum = 2;  //行号
                    while (reader.Read())
                    {
                        //根据字段名找出ID
                        string LoginId = reader.GetString(reader.GetOrdinal("LoginId"));
                        string LoginPwd = reader.GetString(reader.GetOrdinal("LoginPwd"));
                        string Name = reader.GetString(reader.GetOrdinal("Name"));
                        string Mail = reader.GetString(reader.GetOrdinal("Mail"));
                        int  userRoleId= reader.GetInt32(reader.GetOrdinal("UserRoleId"));
                        /******************写入字段值*********************/
                        row = sheet.CreateRow(rowsNum);
                        row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue(LoginId);
                        row.CreateCell(1, HSSFCell.CELL_TYPE_STRING).SetCellValue(LoginPwd);
                        row.CreateCell(2, HSSFCell.CELL_TYPE_STRING).SetCellValue(Name);
                        row.CreateCell(3, HSSFCell.CELL_TYPE_STRING).SetCellValue(Mail);
                        row.CreateCell(4, HSSFCell.CELL_TYPE_STRING).SetCellValue(userRoleId);           //整型数据          
                        rowsNum++;
                    }
                }
            }

            workbook.Write(context.Response.OutputStream);  //输出到流中

        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }

在其他页面调用：

<a href="ExportToExcel.ashx">导出到Excel</a>