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

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

阅读量：6715 次

发布时间：2019-06-25

本文共 13094 字，大约阅读时间需要 43 分钟。

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;//addusing 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互转    ///     /// 传入ds直接生成excel文件    /// 
    /// DataSet    /// 文件路径    /// 文件名    /// execl表头    /// 
         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 = NPOI.SS.UserModel.BorderStyle.THIN;        style2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;        style2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;        //style2.WrapText = true;        //设置大标题行        int RowCount = 0;        int arrFlag = 0;        string TileName1 = "";        string TileName2 = "";        string s = ReportHeader;        string[] sArray = s.Split('|');        if (ReportHeader != "")        {            foreach (string i in sArray)            {                string str1 = i.ToString();                string[] subArray = str1.Split('@');                foreach (string k in subArray)                {                    Console.WriteLine(k.ToString());                    if (arrFlag == 0)                    {                        TileName1 = k.ToString();                    }                    else                    {                        TileName2 = k.ToString();                    }                    arrFlag = arrFlag + 1;                }                HSSFRow row0 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题  8列                row0.CreateCell(0).SetCellValue(TileName1);                row0.CreateCell(1).SetCellValue(TileName2);                RowCount = RowCount + 1;                arrFlag = 0;            }        }        //设置全局列宽和行高        sheet.DefaultColumnWidth = 14;//全局列宽        sheet.DefaultRowHeightInPoints = 15;//全局行高        //设置标题行数据        int a = 0;        string mColumnName = "";        HSSFRow row1 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题  8列        for (int k = 0; k < ds.Tables[0].Columns.Count; k++)        {            mColumnName = ds.Tables[0].Columns[k].ColumnName.ToString();            row1.CreateCell(a).SetCellValue(mColumnName);            row1.Cells[a].CellStyle = style2;            a++;        }        //填写ds数据进excel        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)//写6行数据        {            HSSFRow row2 = (HSSFRow)sheet.CreateRow(i + RowCount + 1);            int b = 0;            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)            {                string DgvValue = "";                DgvValue = ds.Tables[0].Rows[i][j].ToString(); ;                row2.CreateCell(b).SetCellValue(DgvValue);                b++;            }        }        //获取用户选择路径        string ReportPath = HttpContext.Current.Server.MapPath(strPath + strFileName);        //创建excel        System.IO.FileStream file3 = new FileStream(ReportPath, FileMode.Create);        hssfworkbook2.Write(file3);        file3.Close();        return true;    }    ///     /// 用NPOI直接读取excel返回DataTable    /// 
    /// 文件流    /// Sheet序号    /// 开始行号    /// 
         public static DataTable ReadExcelToDataTable(Stream ExcelFileStream, int SheetIndex, int StartRowIndex)    {        HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);        HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(SheetIndex);        DataTable table = new DataTable();        HSSFRow headerRow = (HSSFRow)sheet.GetRow(StartRowIndex);        int cellCount = headerRow.LastCellNum;        for (int i = headerRow.FirstCellNum; i < cellCount; i++)        {            DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);            table.Columns.Add(column);        }        int rowCount = sheet.LastRowNum;        for (int i = (StartRowIndex + 1); i <= sheet.LastRowNum; i++)        {            HSSFRow row = (HSSFRow)sheet.GetRow(i);            DataRow dataRow = table.NewRow();            for (int j = row.FirstCellNum; j < cellCount; j++)            {                if (row.GetCell(j) != null)                    dataRow[j] = row.GetCell(j).ToString();            }            table.Rows.Add(dataRow);        }        ExcelFileStream.Close();        workbook = null;        sheet = null;        return table;    }    ///     /// 用NPOI直接读取excel返回DataTable    /// 
    /// 文件路径    /// Sheet序号    /// 开始行号    /// 
         public static DataTable ReadExcelToDataTable(string FilePath, int SheetIndex, int StartRowIndex)    {        DataSet ds = new DataSet();        FileStream fs = File.Open(HttpContext.Current.Server.MapPath(FilePath), FileMode.Open);        DataTable dt = ReadExcelToDataTable(fs, SheetIndex, StartRowIndex);        return dt;    }    #endregion }

#region DataSet与Execl互转        ///             /// 传入ds直接生成excel文件            /// 
            /// DataSet            /// 文件路径            /// execl表头            /// 
                 public static bool ExportExcelByDataSet(DataSet ds, string strPath, string ReportHeader = "")        {            //NPOI                 HSSFWorkbook hssfworkbook2 = new HSSFWorkbook();            #region 循环开始            for (int p = 0; p < ds.Tables.Count; p++)            {                int t = p + 1;                HSSFSheet sheet = (HSSFSheet)hssfworkbook2.CreateSheet("page" + t);                //定义字体 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 = NPOI.SS.UserModel.BorderStyle.THIN;                style2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;                style2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;                //style2.WrapText = true;                    //设置大标题行                    int RowCount = 0;                int arrFlag = 0;                string TileName1 = "";                string TileName2 = "";                string s = ReportHeader;                string[] sArray = s.Split('|');                if (ReportHeader != "")                {                    foreach (string i in sArray)                    {                        string str1 = i.ToString();                        string[] subArray = str1.Split('@');                        foreach (string k in subArray)                        {                            Console.WriteLine(k.ToString());                            if (arrFlag == 0)                            {                                TileName1 = k.ToString();                            }                            else                            {                                TileName2 = k.ToString();                            }                            arrFlag = arrFlag + 1;                        }                        HSSFRow row0 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题  8列                            row0.CreateCell(0).SetCellValue(TileName1);                        row0.CreateCell(1).SetCellValue(TileName2);                        RowCount = RowCount + 1;                        arrFlag = 0;                    }                }                //设置全局列宽和行高                    sheet.DefaultColumnWidth = 14;//全局列宽                    sheet.DefaultRowHeightInPoints = 15;//全局行高                    //设置标题行数据                    int a = 0;                string mColumnName = "";                HSSFRow row1 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题  8列                    for (int k = 0; k < ds.Tables[p].Columns.Count; k++)                {                    mColumnName = ds.Tables[p].Columns[k].ColumnName.ToString();                    row1.CreateCell(a).SetCellValue(mColumnName);                    row1.Cells[a].CellStyle = style2;                    a++;                }                //填写ds数据进excel                    for (int i = 0; i < ds.Tables[p].Rows.Count; i++)//写6行数据                    {                    HSSFRow row2 = (HSSFRow)sheet.CreateRow(i + RowCount + 1);                    int b = 0;                    for (int j = 0; j < ds.Tables[p].Columns.Count; j++)                    {                        string DgvValue = "";                        DgvValue = ds.Tables[p].Rows[i][j].ToString(); ;                        row2.CreateCell(b).SetCellValue(DgvValue);                        b++;                    }                }            }            #endregion            //获取用户选择路径                string ReportPath = (strPath);            //创建excel                System.IO.FileStream file3 = new FileStream(ReportPath, FileMode.Create);            hssfworkbook2.Write(file3);            file3.Close();            return true;        }        ///         /// 用NPOI直接读取excel返回DataSet        /// 
        /// FileStream fs = File.Open(dlg.FileName, FileMode.Open);        /// sheet数量        /// 
             public static DataSet ReadExcelToDataSet(Stream ExcelFileStream,int SheetCount)        {            HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);            DataSet ds = new DataSet();            for (int k = 0; k < SheetCount; k++)            {                HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(k);                DataTable table = new DataTable("table" + k);                HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);                int cellCount = headerRow.LastCellNum;                for (int i = headerRow.FirstCellNum; i < cellCount; i++)                {                    string columnName = headerRow.GetCell(i).StringCellValue;                    DataColumn column = new DataColumn(columnName);                    if (!table.Columns.Contains(columnName))                    {                        table.Columns.Add(column);                    }                }                int rowCount = sheet.LastRowNum;                for (int i = (0 + 1); i <= sheet.LastRowNum; i++)                {                    HSSFRow row = (HSSFRow)sheet.GetRow(i);                    DataRow dataRow = table.NewRow();                    for (int j = row.FirstCellNum; j < cellCount; j++)                    {                        if (row.GetCell(j) != null)                            dataRow[j] = row.GetCell(j);                    }                    table.Rows.Add(dataRow);                }                sheet = null;                ds.Tables.Add(table);            }            workbook = null;            ExcelFileStream.Close();            return ds;        }        #endregion