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

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//demo
using System.Data;
using System.Data.OracleClient;
using NPOI;
using NPOI.HSSF.UserModel;
using System.IO;

namespace Util
{
    /// <summary>
    /// 利用NPOI组件读写Execl 
    /// </summary>
    public class XmlExcelReport
    {
        /// <summary>
        ///传入ds直接生成excel在服务器目录上
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="strPath"></param>
        /// <param name="strFileName"></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 = NPOI.SS.UserModel.BorderStyle.THIN;
            style2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            style2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;

            //设置大标题行
            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 = 16;//全局列宽
            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 = strPath + strFileName;

            //创建excel
            System.IO.FileStream file3 = new FileStream(ReportPath, FileMode.Create);
            hssfworkbook2.Write(file3);
            file3.Close();

            return true; 
        }

        /// <summary>
        /// 用NPOI直接读取excel返回DataTable
        /// </summary>
        /// <param name="ExcelFileStream"></param>
        /// <param name="SheetIndex"></param>
        /// <param name="StartRowIndex"></param>
        /// <returns></returns>
        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;
        }
    }
}