通过NPOI 导出Excel 操作1

最新推荐文章于 2023-12-22 21:16:59 发布

原创最新推荐文章于 2023-12-22 21:16:59 发布 · 1.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #string #null #exception #header #class

Asp.net/C# 同时被 2 个专栏收录

3 篇文章

订阅专栏

C# Excel

1 篇文章

订阅专栏

本文介绍了一种使用NPOI框架封装的类，用于简化Excel文件的导出过程。通过配置XML文件，可以灵活设置列名、列宽、样式等属性，实现数据的高效导出。包括表头、标题、正文、小计和合计的个性化样式定制。

封装NPOI操作类,此类是通过NPOIHelper扩展来的

using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.IO;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using System.Data;
using Test.Objectx;

namespace Test.Utils
{
    public class NPOIEXCEL
    {
        //记录当前行数
        int rowCount = 0;
        //记录sheet数
        int sheetCount = 1;      
        //配置列表
        DataTable dtcolumn = null;
        /// <summary>
        /// 导出列名
        /// </summary>
        public SortedList ListColumnsName;
        HSSFWorkbook excelWorkbook = null;
        HSSFSheet newsheet = null;
        //单元格样式
        Hashtable NPOIStyle = null;
        //模块属性
        ModuleStyle modulestyle = null;
       
        public NPOIEXCEL()
        {}
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="_dtcolumn"></param>
        /// <param name="_sheetrows"></param>
        /// <param name="_pathxml"></param>
        /// <param name="_titlestyle"></param>
        public NPOIEXCEL(DataTable _dtcolumn, ModuleStyle _modulestyle)
        {
            modulestyle = _modulestyle;
            dtcolumn = _dtcolumn;            
        }
        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        public void SaveExcelFile(string filePath)
        {
            FileStream file = null;
            try
            {
                file = new FileStream(filePath, FileMode.Create);
                excelWorkbook.Write(file);
            }
            finally
            {
                if (file != null)
                {
                    file.Close();
                }
            }
        }
        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        protected void SaveExcelFile(HSSFWorkbook excelWorkBook, Stream excelStream)
        {
            try
            {
                excelWorkBook.Write(excelStream);
            }
            finally
            {

            }
        }
        /// <summary>
        /// 填充表头标题
        /// </summary>
        /// <param name="dtcolumn"></param>
        /// <returns></returns>
        protected SortedList CreateListColumnName()
        {
            SortedList sortedlist = null;
            if (dtcolumn != null && dtcolumn.Rows.Count > 0)
            {
                sortedlist = new SortedList(new NoSort());
                for (int i = 0; i < dtcolumn.Rows.Count; i++)
                {
                    DataRow drcol = dtcolumn.Rows[i];
                    if (drcol["isvisible"].ToString() == "True")
                    {
                        sortedlist.Add(drcol["columncname"].ToString(), drcol["columncname"].ToString());
                    }
                }
            }
            return sortedlist;
        }
        /// <summary>
        /// 创建Excel文件
        /// </summary>
        /// <param name="filePath"></param>
        public void CreateExcelFile()
        {            
            //填充表头标题
            ListColumnsName = CreateListColumnName();
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列名！"));
            excelWorkbook = new HSSFWorkbook();
            newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
            newsheet.IsPrintGridlines = modulestyle.IsGridlinesPrinted;
            //设置列宽
            SetColumnWidths();
            CreateStyle();
        }
        /// <summary>
        /// 创建Excel文件
        /// </summary>
        /// <param name="filePath"></param>
        public void CreateExcelFile(DataTable _dtcolumn, ModuleStyle _modulestyle)
        {
            modulestyle = _modulestyle;           
            dtcolumn = _dtcolumn;
            //填充表头标题
            ListColumnsName = CreateListColumnName();
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列名！"));
            excelWorkbook = new HSSFWorkbook();
            newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
            newsheet.IsPrintGridlines = modulestyle.IsGridlinesPrinted;
            //设置列宽
            SetColumnWidths();
            CreateStyle();
        }
        /// <summary>
        /// 创建样式
        /// </summary>
        public void CreateStyle()
        {
            NPOIStyle = new Hashtable();
            if (string.IsNullOrEmpty(modulestyle.PathXml))
            {
                HSSFCellStyle style1 = excelWorkbook.CreateCellStyle();
                style1.WrapText = true;
                style1.Alignment = HSSFCellStyle.ALIGN_CENTER;
                HSSFFont font1 = excelWorkbook.CreateFont();
                font1.FontHeight = 14 * 20;
                font1.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
                style1.SetFont(font1);
                NPOIStyle.Add("style1", style1);

                HSSFCellStyle style2 = excelWorkbook.CreateCellStyle();
                style2.Alignment = HSSFCellStyle.ALIGN_CENTER;
                style2.WrapText = true;
                HSSFFont font2 = excelWorkbook.CreateFont();
                font2.FontHeight = 12 * 20;
                font2.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
                style2.SetFont(font2);
                NPOIStyle.Add("style2", style2);

                HSSFCellStyle style3 = excelWorkbook.CreateCellStyle();
                style3.Alignment = HSSFCellStyle.ALIGN_CENTER;
                style3.WrapText = true;
                HSSFFont font3 = excelWorkbook.CreateFont();
                font3.FontHeight = 10 * 20;
                font3.Color = HSSFFont.COLOR_RED;
                style3.SetFont(font3);
                NPOIStyle.Add("style3", style3);
            }
            else
            {
                HSSFCellStyle style = null;
                HSSFFont font = null;
                CellStyle cellstyle = null;
                //动态加载样式
                OA.ZCommon.BLL.ColumnsConfig_BLL ccbll = new OA.ZCommon.BLL.ColumnsConfig_BLL();
                Hashtable htcellstyle = ccbll.GetModuleExcelStyleConfig(modulestyle.PathXml);
                foreach (DictionaryEntry de in htcellstyle)
                {
                    style = excelWorkbook.CreateCellStyle();
                    cellstyle = (CellStyle)de.Value;
                    switch(cellstyle.CellAlignment.ToUpper())
                    {
                        case "LEFT":                    
                           style.Alignment = HSSFCellStyle.ALIGN_LEFT;
                           break;
                        case "CENTER":
                           style.Alignment = HSSFCellStyle.ALIGN_CENTER;
                           break;
                        case "RIGHT":
                           style.Alignment = HSSFCellStyle.ALIGN_RIGHT;
                           break;
                        default:
                           style.Alignment = HSSFCellStyle.ALIGN_LEFT;
                           break;
                    }
                    //是否自动换行
                    if (cellstyle.WrapText)
                    {
                        style.WrapText = true;
                    }
                    font = excelWorkbook.CreateFont();
                    font.FontHeight = Convert.ToInt16(cellstyle.FontHeight*20);
                    if (cellstyle.Boldweight.ToUpper() == "BOLD")
                    {
                        font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
                    }
                    switch (cellstyle.FontColor.ToUpper())
                    {
                        case "RED":
                            font.Color = HSSFColor.RED.index;
                            break;
                        case "GREEN":
                            font.Color = HSSFColor.GREEN.index;
                            break;
                        case "BLUE":
                            font.Color = HSSFColor.BLUE.index;
                            break;
                        default:
                            font.Color = HSSFColor.BLACK.index;
                            break;
                    }
                    if (cellstyle.FontIsItalic)
                    {
                        font.IsItalic = true;
                    }
                    switch (cellstyle.FontUnderline.ToUpper())
                    {
                        case "U_SINGLE":
                            font.Underline = HSSFFont.U_SINGLE;
                            break;
                        case "U_DOUBLE":
                            font.Underline = HSSFFont.U_DOUBLE;
                            break;
                        case "U_SINGLE_ACCOUNTING":
                            font.Underline = HSSFFont.U_SINGLE_ACCOUNTING;
                            break;
                        case "U_DOUBLE_ACCOUNTING":
                            font.Underline = HSSFFont.U_DOUBLE_ACCOUNTING;
                            break;
                        default:
                            font.Underline = HSSFFont.U_NONE;
                            break;
                    }
                    style.SetFont(font);
                    NPOIStyle.Add(de.Key, style);
                }            
            }
        }
        
        /// <summary>
        /// 写入行
        /// </summary>
        /// <param name="data"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        /// <param name="cellstylename">样式名称</param>
        /// <param name="isrownum">是否行加1</param>
        public void WriteRowCell(string data, int row, int column,string cellstylename,bool isrownum)
        {
            data = data.Replace("&", "&");
            data = data.Replace(">", ">");
            data = data.Replace("<", "<");

            HSSFRow newRow = newsheet.CreateRow(row);
            HSSFCell newCell = newRow.CreateCell(column);
            newCell.SetCellValue(data);
            if(!string.IsNullOrEmpty(cellstylename))
            {
                if (NPOIStyle[cellstylename] != null)
                {
                    newCell.CellStyle = (HSSFCellStyle)NPOIStyle[cellstylename];
                }
            }
            if (isrownum)
            {
                rowCount++;
            }
        }
        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="FirstRow">区域中第一个单元格的行号</param>
        /// <param name="FirstColumn">区域中第一个单元格的列号</param>
        /// <param name="LastRow">区域中最后一个单元格的行号</param>
        /// <param name="LastColumn">区域中最后一个单元格的列号</param>
        public void MergedRegion(int FirstRow, int FirstColumn, int LastRow, int LastColumn)
        {
            newsheet.AddMergedRegion(new Region(FirstRow, FirstColumn, LastRow, LastColumn));            
        }
        /// <summary>
        /// 单个设置列宽
        /// </summary>
        /// <param name="column"></param>
        /// <param name="width"></param>
        public void SetColumnWidth(int column, int width)
        {
            newsheet.SetColumnWidth(column, width);
        }
        /// <summary>
        /// 批量设置列宽
        /// </summary>
        /// <param name="dtcolumn"></param>
        protected void SetColumnWidths()
        {
            if (dtcolumn != null && dtcolumn.Rows.Count > 0)
            {
                for (int i = 0; i < dtcolumn.Rows.Count; i++)
                {
                    DataRow drcol = dtcolumn.Rows[i];
                    if (drcol["isvisible"].ToString() == "True")
                    {
                        newsheet.SetColumnWidth(i, Convert.ToInt32(drcol["excelwidth"]) * 256);                      
                    }
                }                
            }            
        }
        /// <summary>
        /// 创建excel表头
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="excelSheet"></param>
        public void CreateHeader()
        {
            int cellIndex = 0;
            //循环导出列            
            rowCount++;
            HSSFCellStyle cellstyle = null;
            if (NPOIStyle[modulestyle.title] != null && !string.IsNullOrEmpty(NPOIStyle[modulestyle.title].ToString()))
            {
                cellstyle = (HSSFCellStyle)NPOIStyle[modulestyle.title];
            }
            foreach (DictionaryEntry de in ListColumnsName)
            {
                HSSFRow newRow = newsheet.CreateRow(rowCount);
                HSSFCell newCell = newRow.CreateCell(cellIndex);
                newCell.SetCellValue(de.Value.ToString());
                if (cellstyle != null)
                {
                    newCell.CellStyle = cellstyle;
                }
                cellIndex++;
            }
        }
        /// <summary>
        /// 插入数据行
        /// </summary>
        public void InsertRow(DataTable dtSource, string cellstylename,bool istitle)
        {
            if (excelWorkbook != null)
            {
                HSSFCellStyle cellstyle = null;
                if(!string.IsNullOrEmpty(cellstylename))
                {
                    cellstyle = (HSSFCellStyle)NPOIStyle[cellstylename];
                }
                //循环数据源导出数据集               
                foreach (DataRow dr in dtSource.Rows)
                {
                    rowCount++;
                    //超出指定条数据 创建新的工作簿
                    if (rowCount == modulestyle.ExcelRows)
                    {
                        rowCount = 0;
                        sheetCount++;
                        newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
                        newsheet.IsPrintGridlines = modulestyle.IsGridlinesPrinted;
                        if (istitle)
                        {
                            rowCount--;
                            CreateHeader();
                            rowCount++;
                        }
                        SetColumnWidths();
                    }
                    HSSFRow newRow = newsheet.CreateRow(rowCount);
                    InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook, cellstyle);
                }
            }
        }
        /// <summary>
        /// 导出数据行
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="drSource"></param>
        /// <param name="currentExcelRow"></param>
        /// <param name="excelSheet"></param>
        /// <param name="excelWorkBook"></param>
        protected void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook, HSSFCellStyle cellstyle)
        {
            for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
            {
                //列名称
                string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
                HSSFCell newCell = null;
                System.Type rowType = drSource[columnsName].GetType();
                string drValue = drSource[columnsName].ToString().Trim();
                switch (rowType.ToString())
                {
                    case "System.String"://字符串类型
                        drValue = drValue.Replace("&", "&");
                        drValue = drValue.Replace(">", ">");
                        drValue = drValue.Replace("<", "<");
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(drValue);
                        break;
                    case "System.DateTime"://日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(dateV);

                        //格式化显示
                        HSSFCellStyle cellStyle = excelWorkBook.CreateCellStyle();
                        HSSFDataFormat format = excelWorkBook.CreateDataFormat();
                        cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
                        newCell.CellStyle = cellStyle;

                        break;
                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(intV.ToString());
                        break;
                    case "System.Decimal"://浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull"://空值处理
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue("");
                        break;
                    default:
                        throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
                }
                if (cellstyle != null)
                {
                    newCell.CellStyle = cellstyle;
                }
            }
        }
    }
    //排序实现接口 不进行排序 根据添加顺序导出
    public class NoSort : System.Collections.IComparer
    {
        public int Compare(object x, object y)
        {
            return -1;
        }
    }

}

操作过程:

将要导出的列名,列宽,样式,和一些属性写到xml配置文件中,如下:

<?xml version="1.0" encoding="utf-8"?>
<root>
  <Columns>
    <column columnename="testdate" columncname="日期" isvisible="True" width="30" excelwidth="10" sort="">
    </column>
    <column columnename="testname" columncname="名称" isvisible="True" width="30" excelwidth="8" sort="">
    </column>        
  </Columns>
  <Styles>
    <Style name="style1">
      <!--单元格对齐方式LEFT,CENTER,RIGHT-->
      <CellAlignment>CENTER</CellAlignment>
      <!--是否自动换行-->
      <WrapText>true</WrapText>
      <FontHeight>14</FontHeight>
      <!--BOLD-->
      <Boldweight>BOLD</Boldweight>
      <!--RED,GREEN,BLUE,BLACK-->
      <FontColor></FontColor>
      <FontIsItalic>true</FontIsItalic>
      <!--U_SINGLE,U_DOUBLE,U_SINGLE_ACCOUNTING,U_DOUBLE_ACCOUNTING-->
      <FontUnderline></FontUnderline>
    </Style>
    <Style name="style2">
      <!--LEFT,CENTER,RIGHT-->
      <CellAlignment>LEFT</CellAlignment>
      <!--是否自动换行-->
      <WrapText>true</WrapText>
      <FontHeight>12</FontHeight>
      <Boldweight>BOLD</Boldweight>
      <!--RED,GREEN,BLUE,BLACK-->
      <FontColor></FontColor>
      <FontIsItalic></FontIsItalic>
      <!--U_SINGLE,U_DOUBLE,U_SINGLE_ACCOUNTING,U_DOUBLE_ACCOUNTING-->
      <FontUnderline></FontUnderline>
    </Style>
    <Style name="style3">
      <!--LEFT,CENTER,RIGHT-->
      <CellAlignment>LEFT</CellAlignment>
      <!--是否自动换行-->
      <WrapText>true</WrapText>
      <FontHeight>10</FontHeight>
      <Boldweight>BOLD</Boldweight>
      <!--RED,GREEN,BLUE,BLACK-->
      <FontColor>RED</FontColor>
      <FontIsItalic>true</FontIsItalic>
      <!--U_SINGLE,U_DOUBLE,U_SINGLE_ACCOUNTING,U_DOUBLE_ACCOUNTING-->
      <FontUnderline>U_SINGLE</FontUnderline>
    </Style>
    <Style name="style4">
      <!--LEFT,CENTER,RIGHT-->
      <CellAlignment>LEFT</CellAlignment>
      <!--是否自动换行-->
      <WrapText>true</WrapText>
      <FontHeight>10</FontHeight>
      <Boldweight></Boldweight>
      <!--RED,GREEN,BLUE,BLACK-->
      <FontColor></FontColor>
      <FontIsItalic></FontIsItalic>
      <!--U_SINGLE,U_DOUBLE,U_SINGLE_ACCOUNTING,U_DOUBLE_ACCOUNTING-->
      <FontUnderline></FontUnderline>
    </Style>
  </Styles>
  <ModuleStyle>
     <!--表头-->
     <header>style1</header>
     <!--标题-->
     <title>style2</title>
     <!--正文-->
     <info>style4</info>
     <!--小计-->
     <subtotal>style3</subtotal>
     <!--合计-->
     <total>style3</total>
     <!--标题设置格式 1 表示只有第一个sheet上有标题 2 每个sheet的最开始处有标题 3 每个客户的上面有标题-->
     <issheettitle>2</issheettitle>
     <!--是否需要小计 true 需要 false 不需要-->
     <issubtotal>true</issubtotal>
     <!--是否需要合计 true 需要 false 不需要-->
     <istotal>true</istotal>
     <!--打印时是否需要显示网格 true 需要 false 不需要-->
     <Isgridlinesprinted>true</Isgridlinesprinted>
  </ModuleStyle>
</root>

在导出excel时将分为表头,标题,正文,小计,合计五个部分,可以自动在配置文件中新增样式(这里可操作的样式比较简单,有需要的自行扩展),分别为五个部分指定不同样式,

读取xml配置文件到对象ModuleStyle中

下面是样式类和模块配置类

public class CellStyle
    {
        private string _CellAlignment = "";

        public string CellAlignment
        {
            get { return _CellAlignment; }
            set { _CellAlignment = value; }
        }

        private bool _WrapText = false;
        public bool WrapText
        {
            get { return _WrapText; }
            set { _WrapText = value; }
        }
        private short _FontHeight = 10;

        public short FontHeight
        {
            get { return _FontHeight; }
            set { _FontHeight = value; }
        }

        private string _Boldweight = "";
        public string Boldweight
        {
            get { return _Boldweight; }
            set { _Boldweight = value; }
        }

        private string _FontColor = "BLACK";

        public string FontColor
        {
            get { return _FontColor; }
            set { _FontColor = value; }
        }
        private bool _FontIsItalic = false;
        public bool FontIsItalic
        {
            get { return _FontIsItalic; }
            set { _FontIsItalic = value; }
        }
        private string _FontUnderline = "";
        public string FontUnderline
        {
            get { return _FontUnderline; }
            set { _FontUnderline = value; }
        }
    }
    public class ModuleStyle
    {


        private string _header = "";
        public string header
        {
            get { return _header; }
            set { _header = value; }
        }

        private string _title = "style2";

        public string title
        {
            get { return _title; }
            set { _title = value; }
        }

        private string _info = "";
        public string info
        {
            get { return _info; }
            set { _info = value; }
        }
        private string _subtotal = "";
        public string subtotal
        {
            get { return _subtotal; }
            set { _subtotal = value; }
        }
        private string _total = "";
        public string total
        {
            get { return _total; }
            set { _total = value; }
        }

        private string _issheettitle = "1";
        public string issheettitle
        {
            get { return _issheettitle; }
            set { _issheettitle = value; }
        }

        private bool _issubtotal = false;
        public bool issubtotal
        {
            get { return _issubtotal; }
            set { _issubtotal = value; }
        }

        private bool _istotal = false;
        public bool istotal
        {
            get { return _istotal; }
            set { _istotal = value; }
        }

        private bool _IsGridlinesPrinted = false;
        public bool IsGridlinesPrinted
        {
            get { return _IsGridlinesPrinted; }
            set { _IsGridlinesPrinted = value; }
        }

        private int _ExcelRows = 60000;
        public int ExcelRows
        {
            get { return _ExcelRows; }
            set { _ExcelRows = value; }
        }

        private string _pathxml = "";
        public string PathXml
        {
            get { return _pathxml; }
            set { _pathxml = value; }
        }
    }

未完,待续......

NPOI DLL SOURCE下载