利用NPOI开源的读写Excel、WORD等微软OLE2组件读写execl，控制样式或单元格

最新推荐文章于 2023-05-18 12:17:54 发布

smartsmile2012

最新推荐文章于 2023-05-18 12:17:54 发布

阅读量8k

点赞数 3

分类专栏： Asp.net & C# Web

本文链接：https://blog.csdn.net/smartsmile2012/article/details/8111119

版权

本文介绍如何借助NPOI库进行Excel和Word文件的读写操作，包括设置单元格样式及内容控制。

摘要由CSDN通过智能技术生成

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