NPOI Excel类

2024-01-02 11:50:04

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using System.IO;
using NPOI.SS.Util;

namespace ExcelPlay
{
    public class Excel
    {
        public HSSFWorkbook hssfwork; //工作簿

/// <summary>
        /// 初始化Excel
        /// </summary>
        public Excel()
        {
            hssfwork = new HSSFWorkbook();  //创建工作簿
        }

/// <summary>
        /// 初始化打开Excel
        /// </summary>
        /// <param name="path"></param>
        public Excel(string path)
        {
            FileStream file = new FileStream(path, FileMode.Open);
            hssfwork = new HSSFWorkbook(file);  //创建工作簿
        }

/// <summary>
        /// 创建工作表
        /// </summary>
        /// <param name="ShellName"></param>
        public NPOI.SS.UserModel.ISheet CreateShell(string ShellName)
        {
            NPOI.SS.UserModel.ISheet sheet; //工作表
            sheet = hssfwork.CreateSheet(ShellName);
           
            return sheet;
        }

/// <summary>
        /// 写主题公司信息
        /// </summary>
        /// <param name="Document"></param>
        /// <param name="Summary"></param>
        public void WriteInfomation(string Document, string Summary)
        {
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();  //创建summaryinformation文件  公司名
            dsi.Company = "mo";

SummaryInformation si = PropertySetFactory.CreateSummaryInformation();  //创建summaryinformation  主题
            si.Subject = "Example";

hssfwork.DocumentSummaryInformation = dsi;  //创建好的传递给工作簿
            hssfwork.SummaryInformation = si;
        }

/// <summary>
        /// 获取工作表
        /// </summary>
        /// <param name="LstName"></param>
        /// <returns></returns>
        public List<string> GetSheet(List<string> LstName)
        {
            for (int i = 0; i < hssfwork.NumberOfSheets; i++)
            {
                LstName.Add(hssfwork.GetSheetName(i));
            }
            return LstName;
        }

/// <summary>
        /// 储存工作簿
        /// </summary>
        /// <param name ="path"></param>
        public void SaveWorkbook(string path)
        {
            FileStream file = new FileStream(path, FileMode.Create);
            hssfwork.Write(file);
            file.Close();    // 写入输入流文件
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name ="Row"></param>
        /// <param name ="Column"></param>
        /// <param name ="data"></param>
        /// <param name ="sheet"></param>
        public void WriteData(int Row, int Column, string data, NPOI.SS.UserModel.ISheet sheet)
        {
            sheet.CreateRow(Row).CreateCell(Column).SetCellValue(data);
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="Column"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        public void WriteData(int Row, int Column, double data, NPOI.SS.UserModel.ISheet sheet)
        {
            sheet.CreateRow(Row).CreateCell(Column).SetCellValue(data);
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="Column"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        public void WriteData(int Row, int Column, bool data, NPOI.SS.UserModel.ISheet sheet)
        {
            sheet.CreateRow(Row).CreateCell(Column).SetCellValue(data);
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="Column"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        /// <param name="Flag"></param>
        public void WriteData(int Row, int Column, DateTime data, NPOI.SS.UserModel.ISheet sheet,string Flag)
        {
            sheet.CreateRow(Row).CreateCell(Column).SetCellValue(data);
            setFormat(Row, Column, Flag, sheet);
        }

/// <summary>
        /// 写入数据
       /// </summary>
       /// <param name="Row"></param>
       /// <param name="data"></param>
       /// <param name="sheet"></param>
        public void WriteData(int Row,string[] data,NPOI.SS.UserModel.ISheet sheet)
        {
            int Column = 0;
            foreach (string str in data)
            {
                if (Column == 0)
                {
                    sheet.CreateRow(Row).CreateCell(Column).SetCellValue(str);
                }
                else
                {
                    sheet.GetRow(Row).CreateCell(Column).SetCellValue(str);
                }
                Column++;
            }
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        public void WriteData(int Row, double[] data, NPOI.SS.UserModel.ISheet sheet)
        {
            int Column = 0;
            foreach (double str in data)
            {
                if (Column == 0)
                {
                    sheet.CreateRow(Row).CreateCell(Column).SetCellValue(str);
                }
                else
                {
                    sheet.GetRow(Row).CreateCell(Column).SetCellValue(str);
                }
                Column++;
            }
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        public void WriteData(int Row, bool[] data, NPOI.SS.UserModel.ISheet sheet)
        {
            int Column = 0;
            foreach (bool str in data)
            {
                if (Column == 0)
                {
                    sheet.CreateRow(Row).CreateCell(Column).SetCellValue(str);
                }
                else
                {
                    sheet.GetRow(Row).CreateCell(Column).SetCellValue(str);
                }
                Column++;
            }
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        /// <param name="Flag"></param>
        public void WriteData(int Row, DateTime[] data, NPOI.SS.UserModel.ISheet sheet,string Flag)
        {
            int Column = 0;
            foreach (DateTime str in data)
            {
                if (Column == 0)
                {
                    sheet.CreateRow(Row).CreateCell(Column).SetCellValue(str);
                }
                else
                {
                    sheet.GetRow(Row).CreateCell(Column).SetCellValue(str);
                }
                setFormat(Row, Column, Flag, sheet);
                Column++;
            }
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="diff"></param>
        /// <param name="Column"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        public void WriteData(bool diff,int Column, string[] data, NPOI.SS.UserModel.ISheet sheet)
        {
            int Row = 0;
            foreach (string str in data)
            {
                if (diff)
                {
                    sheet.CreateRow(Row).CreateCell(Column).SetCellValue(str);
                }
                else
                {
                    sheet.GetRow(Row).CreateCell(Column).SetCellValue(str);
                }
                Row++;
            }
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="diff"></param>
        /// <param name="Column"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        public void WriteData(bool diff,int Column, double[] data, NPOI.SS.UserModel.ISheet sheet)
        {
            int Row = 0;
            foreach (double str in data)
            {
                if (diff)
                {
                    sheet.CreateRow(Row).CreateCell(Column).SetCellValue(str);
                }
                else
                {
                    sheet.GetRow(Row).CreateCell(Column).SetCellValue(str);
                }
                Row++;
            }
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="diff"></param>
        /// <param name="Column"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        public void WriteData(bool diff, int Column, bool[] data, NPOI.SS.UserModel.ISheet sheet)
        {
            int Row = 0;
            foreach (bool str in data)
            {
                if (diff)
                {
                    sheet.CreateRow(Row).CreateCell(Column).SetCellValue(str);
                }
                else
                {
                    sheet.GetRow(Row).CreateCell(Column).SetCellValue(str);
                }
                Row++;
            }
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="diff"></param>
        /// <param name="Column"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        /// <param name="Flag"></param>
        public void WriteData(bool diff, int Column, DateTime[] data, NPOI.SS.UserModel.ISheet sheet,string Flag)
        {
            int Row = 0;
            foreach (DateTime str in data)
            {
                if (diff)
                {
                    sheet.CreateRow(Row).CreateCell(Column).SetCellValue(str);
                }
                else
                {
                    sheet.GetRow(Row).CreateCell(Column).SetCellValue(str);
                }
                Row++;
            }
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="Column"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        public void WriteData(int Row, int Column, string[,] data, NPOI.SS.UserModel.ISheet sheet)
        {
            int lengthRow = data.GetLength(0);
            int lengthColumn = data.GetLength(1);

for (int n = 0; n < lengthRow; n++)
            {
                for (int m = 0; m < lengthColumn; m++)
                {
                    if (m == 0)
                    {
                        sheet.CreateRow(Row + n).CreateCell(Column + m).SetCellValue(data[n, m]);
                    }
                    else
                    {
                        sheet.GetRow(Row + n).CreateCell(Column + m).SetCellValue(data[n, m]);
                    }

}
            }
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="Column"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        public void WriteData(int Row, int Column, double[,] data, NPOI.SS.UserModel.ISheet sheet)
        {
            int lengthRow = data.GetLength(0);
            int lengthColumn = data.GetLength(1);

for (int n = 0; n < lengthRow; n++)
            {
                for (int m = 0; m < lengthColumn; m++)
                {
                    if (m == 0)
                    {
                        sheet.CreateRow(Row + n).CreateCell(Column + m).SetCellValue(data[n, m]);
                    }
                    else
                    {
                        sheet.GetRow(Row + n).CreateCell(Column + m).SetCellValue(data[n, m]);
                    }
                }
            }
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="Column"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        public void WriteData(int Row, int Column, bool[,] data, NPOI.SS.UserModel.ISheet sheet)
        {
            int lengthRow = data.GetLength(0);
            int lengthColumn = data.GetLength(1);

for (int n = 0; n < lengthRow; n++)
            {
                for (int m = 0; m < lengthColumn; m++)
                {
                    if (m == 0)
                    {
                        sheet.CreateRow(Row + n).CreateCell(Column + m).SetCellValue(data[n, m]);
                    }
                    else
                    {
                        sheet.GetRow(Row + n).CreateCell(Column + m).SetCellValue(data[n, m]);
                    }
                }
            }
        }

/// <summary>
        /// 写入数据
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="Column"></param>
        /// <param name="data"></param>
        /// <param name="sheet"></param>
        /// <param name="Flag"></param>
        public void WriteData(int Row, int Column, DateTime[,] data, NPOI.SS.UserModel.ISheet sheet,string Flag)
        {
            int lengthRow = data.GetLength(0);
            int lengthColumn = data.GetLength(1);

for (int n = 0; n < lengthRow; n++)
            {
                for (int m = 0; m < lengthColumn; m++)
                {
                    if (m == 0)
                    {
                        sheet.CreateRow(Row + n).CreateCell(Column + m).SetCellValue(data[n, m]);
                    }
                    else
                    {
                        sheet.GetRow(Row + n).CreateCell(Column + m).SetCellValue(data[n, m]);
                    }
                    setFormat(Row, Column, Flag, sheet);
                }
            }
        }

/// <summary>
        /// 写入标签
        /// </summary>
        /// <param name="boll"></param>
        /// <param name="Row"></param>
        /// <param name="Column"></param>
        /// <param name="str"></param>
        /// <param name="author"></param>
        /// <param name="sheet"></param>
        /// <param name="dx1"></param>
        /// <param name="dy1"></param>
        /// <param name="dx2"></param>
        /// <param name="dy2"></param>
        /// <param name="row1"></param>
        /// <param name="col1"></param>
        /// <param name="row2"></param>
        /// <param name="col2"></param>
        public void WriteComment(bool boll,int Row, int Column, string str, string author, NPOI.SS.UserModel.ISheet sheet, int dx1, int dy1, int dx2, int dy2, int row1, int col1, int row2, int col2)
        {
            NPOI.SS.UserModel.IDrawing drawing = sheet.CreateDrawingPatriarch();
            NPOI.SS.UserModel.IComment comment = drawing.CreateCellComment(new HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2));

comment.String = new HSSFRichTextString(str);
            comment.Author = author;

if (boll)
            {
                comment.Visible = true;
            }

NPOI.SS.UserModel.ICell cell = sheet.CreateRow(Row).CreateCell(Column);
            cell.CellComment = comment;
        }

/// <summary>
        /// 设置格式
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="Column"></param>
        /// <param name="Format"></param>
        /// <param name="sheet"></param>
        public void setFormat(int Row, int Column, string Format, NPOI.SS.UserModel.ISheet sheet)
        {
            NPOI.SS.UserModel.ICell cell = sheet.GetRow(Row).GetCell(Column);
            NPOI.SS.UserModel.ICellStyle cellStyle = hssfwork.CreateCellStyle();
            NPOI.SS.UserModel.IDataFormat format = hssfwork.CreateDataFormat();
            cellStyle.DataFormat = format.GetFormat(Format);
            cell.CellStyle = cellStyle;
        }

/// <summary>
        /// 设置格式
        /// </summary>
        /// <param name="boll"></param>
        /// <param name="Row"></param>
        /// <param name="Column"></param>
        /// <param name="Format"></param>
        /// <param name="sheet"></param>
        public void setFormat(bool boll, int Row, int Column, string Format, NPOI.SS.UserModel.ISheet sheet)
        {
            NPOI.SS.UserModel.ICell cell = sheet.GetRow(Row).GetCell(Column);
            NPOI.SS.UserModel.ICellStyle cellStyle = hssfwork.CreateCellStyle();

cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(Format);  //内嵌写入
            cell.CellStyle = cellStyle;
        }

/// <summary>
        /// 合并区域
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="Column"></param>
        /// <param name="sheet"></param>
        /// <param name="FirstRow"></param>
        /// <param name="FirstColumn"></param>
        /// <param name="LastRow"></param>
        /// <param name="LastColumn"></param>
        public void MargeRegion(int Row,int Column ,NPOI.SS.UserModel.ISheet sheet,int FirstRow,int FirstColumn,int LastRow,int LastColumn)
        {
            NPOI.SS.UserModel.ICell cell = sheet.CreateRow(Row).CreateCell(Column);
            sheet.AddMergedRegion(new CellRangeAddress(FirstRow, LastRow, FirstColumn, LastColumn));
        }

public object ReadData(int Row,int Column,NPOI.SS.UserModel.ISheet sheet,int checkd)
        {
            object data = null;
            switch (checkd)
            {
                case 0:
                    data = sheet.GetRow(Row).GetCell(Column).StringCellValue;
                    break;
                case 1:
                    data = sheet.GetRow(Row).GetCell(Column).DateCellValue;
                    break;
                case 2:
                    data = sheet.GetRow(Row).GetCell(Column).NumericCellValue;
                    break;
                case 3:
                    data = sheet.GetRow(Row).GetCell(Column).BooleanCellValue;
                    break;
            }
            return data;
            //short num = sheet.GetRow(2).LastCellNum;
        }

public List<List<string>> ReadData(NPOI.SS.UserModel.ISheet sheet)
        {
            List<string> data = new List<string>();
            List<List<string>> listdata = new List<List<string>>();
            int Rownum = sheet.PhysicalNumberOfRows;
            for (int i = 0; i < Rownum; i++)
            {
                int Column = sheet.GetRow(i).PhysicalNumberOfCells;
                for (int m = 0; m < Column; m++)
                {
                    data.Add(sheet.GetRow(i).GetCell(m).StringCellValue);
                }
                listdata.Add(data);
                data = new List<string>();
            }
            return listdata;
        }
 
    }
}

上一篇：Python——Requests库的开发者接口


下一篇：Python Requests库简单入门