NPOI使用教程附Helper

最新推荐文章于 2024-03-26 11:39:06 发布

banyongzhan5405

最新推荐文章于 2024-03-26 11:39:06 发布

阅读量1.0k

点赞数

文章标签： java

原文链接：http://www.cnblogs.com/wyy1234/p/10039767.html

版权

1 NPOI简介

1.1 NPOI是什么

　　NPOI是POI的.NET版本,POI是一套用Java写成的库，我们在开发中经常用到导入导出表格、文档的情况，NPOI能够帮助我们在没有安装微软Office的情况下读写Office文件，如xls, doc, ppt等。NPOI采用的是Apache 2.0许可证（poi也是采用这个许可证），这意味着它可以被用于任何商业或非商业项目，我们不用担心因为使用它而必须开放你自己的源代码，所以它对于很多从事业务系统开发的公司来说绝对是很不错的选择。

1.2 NPOI简单使用

　　NPOI的API十分人性化，使用起来非常容易上手。首先创建一个控制台应用程序，通过nuget获取NPOI，这里使用的是最新版本2.4.0，如下：

　　项目引入NPOI后就可以直接上手使用了，代码如下

        static void Main(string[] args)
        {
            //创建workbook，说白了就是在内存中创建一个Excel文件
            IWorkbook workbook = new HSSFWorkbook();
            //要添加至少一个sheet,没有sheet的excel是打不开的
            ISheet sheet1 = workbook.CreateSheet("sheet1");
            ISheet sheet2 = workbook.CreateSheet("sheet2");

            IRow row1 = sheet1.CreateRow(0);//添加第1行,注意行列的索引都是从0开始的
            ICell cell1 = row1.CreateCell(0);//给第1行添加第1个单元格
            cell1.SetCellValue("hello npoi!");//给单元格赋值
            //上边3个步骤合在一起：sheet1.CreateRow(0).CreateCell(0).SetCellValue("hello npoi");

            //获取第一行第一列的string值
            Console.WriteLine(sheet1.GetRow(0).GetCell(0).StringCellValue); //输出：hello npoi

            //写入文件
            using (FileStream file = new FileStream(@"D:/TestFiles/test.xls", FileMode.Create))
            {
                workbook.Write(file);
            }
        }

运行代码，结果如下，同时也在D盘的TestFiles文件夹下创建了test.xls文件

2 NPOI使用详解

2.1 单元格数据格式

　　用过Excel的人都知道，单元格是Excel最有意义的东西，我们做任何操作恐怕都要和单元格打交道。在Excel中我们经常要设置格式，比如说日期格式（2018-1-1）、小数点格式（1.20）、货币格式（$2000）、百分比格式（99.99%）等等，这些东西在过去我们恐怕只能在服务器端生成好，不但增加了服务器端的代码量，还造成了不必要的字符串替换操作，如今NPOI将让服务器从这种完全没有必要的操作中解放出来，一切都将由Excel在客户端处理。

　　使用NPOI时要注意，所有的格式都是通过 CellStyle.DataFormat 赋给单元格的，而不是直接赋给单元格，设置单元格数据展示的格式有两种方法。

1.设置excel中内置的格式：

excel内部设置了很多格式，如下图所示：

如果我们想使用这些内置的格式，可用通过 DataFormat.GetBuildinFormat('格式') 来使用，使用实例如下：

cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");  //两位小数，内置格式

2.设置自定义格式

　　有时候内置的格式不能满足我们的要求，这时候就可以使用 workbook.CreateDataFormat().GetFormat("格式") 自定义格式了，使用语法如下：

cellStyle.DataFormat= workbook.CreateDataFormat().GetFormat("yyyy年m月d日 hh时mm分ss秒");//显示中文日期和时间，自定义格式

下边的栗子展示了几种常用的数据格式用法：

       static void Main(string[] args)
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("new sheet");
            //设置日期格式，2018年5月5日格式
            ICell cell = sheet.CreateRow(0).CreateCell(0);
            cell.SetCellValue(new DateTime(2018, 5, 5, 11, 31, 56));
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy年m月d日 hh时mm分ss秒");
            //cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/mm/dd hh:mm:ss"); 
            cell.CellStyle = cellStyle;

            //保留2位小数
            ICell cell2 = sheet.CreateRow(1).CreateCell(0);
            cell2.SetCellValue(12.346666);
            ICellStyle cellStyle2 = workbook.CreateCellStyle();
            cellStyle2.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
            cell2.CellStyle = cellStyle2;

            //货币格式
            ICell cell3 = sheet.CreateRow(2).CreateCell(0);
            cell3.SetCellValue(1234.66666);
            ICellStyle cellStyle3 = workbook.CreateCellStyle();
            cellStyle3.DataFormat = workbook.CreateDataFormat().GetFormat("￥#,##0.00");//美元的话格式为 $#,##0.00，其中#，##0表示千分号
            cell3.CellStyle = cellStyle3;

            //百分比
            ICell cell4 = sheet.CreateRow(3).CreateCell(0);
            cell4.SetCellValue(1.236666);
            ICellStyle cellStyle4 = workbook.CreateCellStyle();
            //cellStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");//保留两位小数
            cellStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("0%");
            cell4.CellStyle = cellStyle4;

            //中文大写数字
            ICell cell5 = sheet.CreateRow(4).CreateCell(0);
            cell5.SetCellValue(111);
            ICellStyle cellStyle5 = workbook.CreateCellStyle();
            cellStyle5.DataFormat = workbook.CreateDataFormat().GetFormat("[DbNum2][$-804]0.00");//不保留小数： [DbNum2][$-804]0
            cell5.CellStyle = cellStyle5;


            //科学计数法
            ICell cell6 = sheet.CreateRow(5).CreateCell(0);
            cell6.SetCellValue(1234.6666);
            ICellStyle cellStyle6 = workbook.CreateCellStyle();
            cellStyle6.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
            cell6.CellStyle = cellStyle6;

            using (FileStream file = new FileStream(@"D:/TestFiles/test.xls", FileMode.Create))
            {
                workbook.Write(file);
            }
        }

运行程序，生成的excel文件如下所示：

2.2 单元格合并及设置风格

　　单元格的合并和风格这部分内容比较多，但是Api十分的简单，为了看起来清晰，我们分成两个栗子来演示这部分的功能，代码中的注释很详细，这里就直接上代码了

1.第一个栗子演示单元格合并，内容停靠，字体风格颜色大小等

       static void Main(string[] args)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet1");
            
            //------------------单元格合并
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 5));//合并单元格,4个参数依次为为startRow,endRow,startCol,endCol
            ICell cell = sheet.CreateRow(1).CreateCell(1);
            cell.SetCellValue("合并的区域");
            cell.Row.HeightInPoints = 40;//行高
            ICellStyle cellstyle = workbook.CreateCellStyle();

            //----------------设置单元格常用风格
            cellstyle.Alignment = HorizontalAlignment.Left;//水平居左，可选Right,Center
            cellstyle.VerticalAlignment = VerticalAlignment.Bottom;//垂直居中,可选Top,Center
            cellstyle.WrapText = true;//自动换行

            cellstyle.Indention = 4;//缩进4个字节
            //cellstyle.Rotation = 90;//字体旋转90度，取值范围是[-90,90]

            //---------------字体，字体大小、颜色
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 24;//字号为24，可以用font.FontHeight = 24 * 20;FontHeight的单位是1/20点

            font.FontName = "楷体";//字体
            font.Boldweight = 700;//加粗
            font.Color = HSSFColor.Blue.Index;//字体颜色
            cellstyle.SetFont(font);
            cell.CellStyle = cellstyle;
            using (FileStream file = new FileStream(@"D:/TestFiles/test.xls", FileMode.Create))
            {
                workbook.Write(file);
            }
        }

生成的excel文件如下图所示：

2.第二个栗子演示单元格的宽高、背景色、边框风格等

       static void Main(string[] args)
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet1");

            ICell cell = sheet.CreateRow(1).CreateCell(1);
            cell.SetCellValue("hello");
            ICellStyle cellstyle = workbook.CreateCellStyle();
            //设置宽度，把第2列设成20字节宽；参数1指定列的索引；参数2指定宽度，单位是1/256个字符宽度
            sheet.SetColumnWidth(1, 20 * 256);
            //高度设成40，单位是点；也可以用cell2.Row.Height = 40 * 20;Height的单位是1/20点
            cell.Row.HeightInPoints = 40;
            //右边框设置成点画线
            cellstyle.BorderRight = BorderStyle.MediumDashDot;
            //右边框设成粉色
            cellstyle.RightBorderColor = HSSFColor.Pink.Index;
            //前景色填充模式，实心
            cellstyle.FillPattern = FillPattern.SolidForeground;
            //前景色填充颜色，黄色
            cellstyle.FillForegroundColor = HSSFColor.Yellow.Index;
            //背景色
            //cellstyle2.FillBackgroundColor = HSSFColor.Green.Index;//背景色设成green
            cell.CellStyle = cellstyle;

            using (FileStream file = new FileStream(@"D:/TestFiles/test.xls", FileMode.Create))
            {
                workbook.Write(file);
            }
        }

运行程序，生成的excel文件如下：

　　其实在NPOI中除了操作单元格的内容和风格外，还提供了添加excel文件摘要，添加批注，画图，使用公式等功能，但是这些功能我们用的比较少，所以就不一一展示了，NPOI的公式我自己很少去使用，一般通过程序直接计算结果，个人感觉在程序中计算更加灵活，当excel的结构发生变化时修改起来也更方便些。

3.封装一个NPOI的helper类

　　我们先看一下这个helper的使用吧，先看从DataTable导出到excel，

　　首先使用Nuget添加几个包：NPOI，log4net(其他的日志记录组件也可以)，System.Text.Encoding.CodePages(为了支持gb2312)，具体代码如下：

  class Program
    {
        static void Main(string[] args)
        {
            log4net.Config.XmlConfigurator.Configure();
　　　　　　　//用于支持gb2312         
　　　　　　　Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            //测试dataTable数据源 
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("id", typeof(int));
            dataTable.Columns.Add("uname", typeof(string));
            dataTable.Columns.Add("sex", typeof(string));
            dataTable.Columns.Add("age", typeof(int));
            dataTable.Columns.Add("pwd", typeof(string));
            dataTable.Columns.Add("email", typeof(string));
            dataTable.Columns.Add("address", typeof(string));
            Random r = new Random();
            for (int i = 0; i < 1000; i++)
            {
                DataRow row = dataTable.NewRow();
                row["id"] = i;
                row["uname"] = "hellox" + i.ToString();
                row["sex"] = r.Next(2) % 2 == 0 ? "男" : "女";
                row["age"] = r.Next(40) + 18;
                row["pwd"] = "pwd" + r.Next(5000).ToString();
                row["email"] = r.Next(100000) + "@qq.com";
                row["address"] = $"北京市，西{r.Next(4) + 1}环，xx路{r.Next(100)}号";
                dataTable.Rows.Add(row);
            }
            //DataTable的列名和excel的列名对应字典，因为excel的列名一般是中文的，DataTable的列名是英文的，字典主要是存储excel和DataTable列明的对应关系，当然我们也可以把这个对应关系存在配置文件或者其他地方
            Dictionary<string, string> dir = new Dictionary<string, string>();
            dir.Add("id", "编号");
            dir.Add("uname", "用户");
            dir.Add("sex", "性别");
            dir.Add("age", "年龄");
            dir.Add("pwd", "密码");
            dir.Add("email", "邮箱");
            dir.Add("address", "住址");

            //使用helper类导出DataTable数据到excel表格中,参数依次是 （DataTable数据源;  excel表名;  excel存放位置的绝对路径; 列名对应字典; 是否清空以前的数据，设置为false，表示内容追加; 每个sheet放的数据条数,如果超过该条数就会新建一个sheet存储）
            NPOIHelper.ExportDTtoExcel(dataTable, "考勤信息表", @"C:\Users\ZDZN\Desktop/Hello.xlsx", dir, false, 400);
           
        }
    }

　　运行程序，当一个sheet的数目满了后会新生产一个sheet1来存储，效果如下：

接下来测试读取excel中的数据到datatable中，我们就读取刚才生成的excel中的数据到DataTable中去：

 static void Main(string[] args)
        {
            log4net.Config.XmlConfigurator.Configure();
        　　//用于支持gb2312    
　　　　　　　Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            //DataTable的列名和excel的列名对应字典
            Dictionary<string, string> dir = new Dictionary<string, string>();
            dir.Add("id", "编号");
            dir.Add("uname", "用户");
            dir.Add("sex", "性别");
            dir.Add("age", "年龄");
            dir.Add("pwd", "密码");
            dir.Add("email", "邮箱");
            dir.Add("address", "住址");
            
            //读取数据到DataTable，参数依此是（excel文件路径，列名对应字典，列名所在行，sheet索引）
            DataTable dt = NPOIHelper.ImportExceltoDt(@"C:\Users\ZDZN\Desktop/Hello11.xlsx", dir, 1, 0);
           //遍历DataTable---------------------------
            foreach (DataColumn item in dt.Columns)
            {
                //显示dataTable的列名
                Console.Write(item.ColumnName + new string('-', 10));
            }
            Console.WriteLine();
            //遍历DataTable中的数据
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    Console.Write(row[i].ToString() + new string(' ', 10));

                }
                Console.WriteLine();
            }

            Console.WriteLine("ok");
            Console.ReadKey();
        }

程序运行如下：

上边例子中的helper兼容了xls和xlsx两种格式，通过helper只需要一行代码就可以快速地进行excel和datatable间的数据转换，helper附件如下

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using System.Linq;
using NPOI.SS.Util;
using System.Text.RegularExpressions;
using NPOI.XSSF.UserModel;
using log4net;


public class NPOIHelper
{
    private static ILog loger = LogManager.GetLogger("myLogger");

    #region 从DataTable导出到excel文件中，支持xls和xlsx格式
    #region 导出为xls文件内部方法
    /// <summary>
    /// 从datatable 中导出到excel
    /// </summary>
    /// <param name="strFileName">excel文件名</param>
    /// <param name="dtSource">datatabe源数据</param>
    /// <param name="strHeaderText">表名</param>
    /// <param name="sheetnum">sheet的编号</param>
    /// <returns></returns>
    static MemoryStream ExportDT(String strFileName, DataTable dtSource, string strHeaderText, Dictionary<string, string> dir, int sheetnum)
    {
        //创建工作簿和sheet
        IWorkbook workbook = new HSSFWorkbook();
        using (Stream writefile = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read))
        {
            if (writefile.Length > 0 && sheetnum > 0)
            {
                workbook = WorkbookFactory.Create(writefile);
            }
        }

        ISheet sheet = null;
        ICellStyle dateStyle = workbook.CreateCellStyle();
        IDataFormat format = workbook.CreateDataFormat();
        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
        int[] arrColWidth = new int[dtSource.Columns.Count];
        foreach (DataColumn item in dtSource.Columns)
        {
            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
        }
        for (int i = 0; i < dtSource.Rows.Count; i++)
        {
            for (int j = 0; j < dtSource.Columns.Count; j++)
            {
                int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                if (intTemp > arrColWidth[j])
                {
                    arrColWidth[j] = intTemp;
                }
            }
        }
        int rowIndex = 0;
        foreach (DataRow row in dtSource.Rows)
        {
            #region 新建表，填充表头，填充列头，样式
            if (rowIndex == 0)
            {
                string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
                if (workbook.GetSheetIndex(sheetName) >= 0)
                {
                    workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
                }
                sheet = workbook.CreateSheet(sheetName);
                #region 表头及样式
                {
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    IRow headerRow = sheet.CreateRow(0);
                    headerRow.HeightInPoints = 25;
                    headerRow.CreateCell(0).SetCellValue(strHeaderText);
                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 20;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    headerRow.GetCell(0).CellStyle = headStyle;

                    rowIndex = 1;
                }
                #endregion

                #region 列头及样式

                if (rowIndex == 1)
                {
                    IRow headerRow = sheet.CreateRow(1);//第二行设置列名
                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    //写入列标题
                    foreach (DataColumn column in dtSource.Columns)
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                        //设置列宽
                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
                    }
                    rowIndex = 2;
                }
                #endregion
            }
            #endregion

            #region 填充内容

            IRow dataRow = sheet.CreateRow(rowIndex);
            foreach (DataColumn column in dtSource.Columns)
            {
                ICell newCell = dataRow.CreateCell(column.Ordinal);
                string drValue = row[column].ToString();
                switch (column.DataType.ToString())
                {
                    case "System.String": //字符串类型
                        double result;
                        if (isNumeric(drValue, out result))
                        {
                            //数字字符串
                            double.TryParse(drValue, out result);
                            newCell.SetCellValue(result);
                            break;
                        }
                        else
                        {
                            newCell.SetCellValue(drValue);
                            break;
                        }

                    case "System.DateTime": //日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell.SetCellValue(dateV);

                        newCell.CellStyle = dateStyle; //格式化显示
                        break;
                    case "System.Boolean": //布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        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.SetCellValue(intV);
                        break;
                    case "System.Decimal": //浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull": //空值处理
                        newCell.SetCellValue("");
                        break;
                    default:
                        newCell.SetCellValue(drValue.ToString());
                        break;
                }

            }
            #endregion
            rowIndex++;
        }
       
        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return ms;
        }

    }
    #endregion

    #region 导出为xlsx文件内部方法
    /// <summary>
    /// 从datatable 中导出到excel
    /// </summary>
    /// <param name="dtSource">datatable数据源</param>
    /// <param name="strHeaderText">表名</param>
    /// <param name="fs">文件流</param>
    /// <param name="readfs">内存流</param>
    /// <param name="sheetnum">sheet索引</param>
    static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs, MemoryStream readfs, Dictionary<string, string> dir, int sheetnum)
    {

        IWorkbook workbook = new XSSFWorkbook();
        if (readfs.Length > 0 && sheetnum > 0)
        {
            workbook = WorkbookFactory.Create(readfs);
        }
        ISheet sheet = null;
        ICellStyle dateStyle = workbook.CreateCellStyle();
        IDataFormat format = workbook.CreateDataFormat();
        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

        //取得列宽
        int[] arrColWidth = new int[dtSource.Columns.Count];
        foreach (DataColumn item in dtSource.Columns)
        {
            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
        }
        for (int i = 0; i < dtSource.Rows.Count; i++)
        {
            for (int j = 0; j < dtSource.Columns.Count; j++)
            {
                int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                if (intTemp > arrColWidth[j])
                {
                    arrColWidth[j] = intTemp;
                }
            }
        }
        int rowIndex = 0;

        foreach (DataRow row in dtSource.Rows)
        {
            #region 新建表，填充表头，填充列头，样式

            if (rowIndex == 0)
            {
                #region 表头及样式
                {
                    string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
                    if (workbook.GetSheetIndex(sheetName) >= 0)
                    {
                        workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
                    }
                    sheet = workbook.CreateSheet(sheetName);
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    IRow headerRow = sheet.CreateRow(0);
                    headerRow.HeightInPoints = 25;
                    headerRow.CreateCell(0).SetCellValue(strHeaderText);

                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 20;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    headerRow.GetCell(0).CellStyle = headStyle;
                }
                #endregion

                #region 列头及样式
                {
                    IRow headerRow = sheet.CreateRow(1);
                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);


                    foreach (DataColumn column in dtSource.Columns)
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                        //设置列宽
                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
                    }
                }

                #endregion

                rowIndex = 2;
            }
            #endregion

            #region 填充内容
            IRow dataRow = sheet.CreateRow(rowIndex);
            foreach (DataColumn column in dtSource.Columns)
            {
                ICell newCell = dataRow.CreateCell(column.Ordinal);
                string drValue = row[column].ToString();
                switch (column.DataType.ToString())
                {
                    case "System.String": //字符串类型
                        double result;
                        if (isNumeric(drValue, out result))
                        {

                            double.TryParse(drValue, out result);
                            newCell.SetCellValue(result);
                            break;
                        }
                        else
                        {
                            newCell.SetCellValue(drValue);
                            break;
                        }
                    case "System.DateTime": //日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell.SetCellValue(dateV);

                        newCell.CellStyle = dateStyle; //格式化显示
                        break;
                    case "System.Boolean": //布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        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.SetCellValue(intV);
                        break;
                    case "System.Decimal": //浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull": //空值处理
                        newCell.SetCellValue("");
                        break;
                    default:
                        newCell.SetCellValue(drValue.ToString());
                        break;
                }
            }
            #endregion
            rowIndex++;
        }
        workbook.Write(fs);
        fs.Close();
    }
    #endregion

    #region 导出excel表格
    /// <summary>
    ///  DataTable导出到Excel文件，xls文件
    /// </summary>
    /// <param name="dtSource">数据源</param>
    /// <param name="strHeaderText">表名</param>
    /// <param name="strFileName">excel文件名</param>
    /// <param name="dir">datatable和excel列名对应字典</param>
    /// <param name="sheetRow">每个sheet存放的行数</param>
    public static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName, Dictionary<string, string> dir, bool isNew, int sheetRow = 50000)
    {
        int currentSheetCount = GetSheetNumber(strFileName);//现有的页数sheetnum
        if (sheetRow <= 0)
        {
            sheetRow = dtSource.Rows.Count;
        }
        string[] temp = strFileName.Split('.');
        string fileExtens = temp[temp.Length - 1];
        int sheetCount = (int)Math.Ceiling((double)dtSource.Rows.Count / sheetRow);//sheet数目
        if (temp[temp.Length - 1] == "xls" && dtSource.Columns.Count < 256 && sheetRow < 65536)
        {
            if (isNew)
            {
                currentSheetCount = 0;
            }
            for (int i = currentSheetCount; i < currentSheetCount + sheetCount; i++)
            {
                DataTable pageDataTable = dtSource.Clone();
                int hasRowCount = dtSource.Rows.Count - sheetRow * (i - currentSheetCount) < sheetRow ? dtSource.Rows.Count - sheetRow * (i - currentSheetCount) : sheetRow;
                for (int j = 0; j < hasRowCount; j++)
                {
                    pageDataTable.ImportRow(dtSource.Rows[(i - currentSheetCount) * sheetRow + j]);
                }

                using (MemoryStream ms = ExportDT(strFileName, pageDataTable, strHeaderText, dir, i))
                {
                    using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                    {

                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                }
            }
        }
        else
        {
            if (temp[temp.Length - 1] == "xls")
                strFileName = strFileName + "x";
            if (isNew)
            {
                currentSheetCount = 0;
            }
            for (int i = currentSheetCount; i < currentSheetCount + sheetCount; i++)
            {
                DataTable pageDataTable = dtSource.Clone();
                int hasRowCount = dtSource.Rows.Count - sheetRow * (i - currentSheetCount) < sheetRow ? dtSource.Rows.Count - sheetRow * (i - currentSheetCount) : sheetRow;
                for (int j = 0; j < hasRowCount; j++)
                {
                    pageDataTable.ImportRow(dtSource.Rows[(i - currentSheetCount) * sheetRow + j]);
                }
                FileStream readfs = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read);
                MemoryStream readfsm = new MemoryStream();
                readfs.CopyTo(readfsm);
                readfs.Close();
                using (FileStream writefs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {

                    ExportDTI(pageDataTable, strHeaderText, writefs, readfsm, dir, i);
                }
                readfsm.Close();
            }
        }
    }
    #endregion 
    #endregion

    #region 从excel文件中将数据导出到datatable/datatable
    /// <summary>
    /// 将制定sheet中的数据导出到datatable中
    /// </summary>
    /// <param name="sheet">需要导出的sheet</param>
    /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
    /// <param name="dir">excel列名和DataTable列名的对应字典</param>
    /// <returns></returns>
    static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, Dictionary<string, string> dir)
    {
        DataTable table = new DataTable();
        IRow headerRow;
        int cellCount;
        try
        {
            //没有标头或者不需要表头用excel列的序号（1,2,3..）作为DataTable的列名
            if (HeaderRowIndex < 0)
            {
                headerRow = sheet.GetRow(0);
                cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                {
                    DataColumn column = new DataColumn(Convert.ToString(i));
                    table.Columns.Add(column);
                }
            }
            //有表头，使用表头做为DataTable的列名
            else
            {
                headerRow = sheet.GetRow(HeaderRowIndex);
                cellCount = headerRow.LastCellNum;
                for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                {
                    //如果excel某一列列名不存在：以该列的序号作为Datatable的列名，如果DataTable中包含了这个序列为名的列，那么列名为重复列名+序号
                    if (headerRow.GetCell(i) == null)
                    {
                        if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                        {
                            DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                            table.Columns.Add(column);
                        }
                        else
                        {
                            DataColumn column = new DataColumn(Convert.ToString(i));
                            table.Columns.Add(column);
                        }

                    }
                    //excel中的某一列列名不为空，但是重复了：对应的Datatable列名为“重复列名+序号”
                    else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                    {
                        DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                        table.Columns.Add(column);
                    }
                    else
                    //正常情况，列名存在且不重复：用excel中的列名作为datatable中对应的列名
                    {
                        string colName = dir.Where(s => s.Value == headerRow.GetCell(i).ToString()).First().Key;
                        DataColumn column = new DataColumn(colName);
                        table.Columns.Add(column);
                    }
                }
            }
            int rowCount = sheet.LastRowNum;
            for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)//excel行遍历
            {
                try
                {
                    IRow row;
                    if (sheet.GetRow(i) == null)//如果excel有空行，则添加缺失的行
                    {
                        row = sheet.CreateRow(i);
                    }
                    else
                    {
                        row = sheet.GetRow(i);
                    }

                    DataRow dataRow = table.NewRow();

                    for (int j = row.FirstCellNum; j <= cellCount; j++)//excel列遍历
                    {
                        try
                        {
                            if (row.GetCell(j) != null)
                            {
                                switch (row.GetCell(j).CellType)
                                {
                                    case CellType.String://字符串
                                        string str = row.GetCell(j).StringCellValue;
                                        if (str != null && str.Length > 0)
                                        {
                                            dataRow[j] = str.ToString();
                                        }
                                        else
                                        {
                                            dataRow[j] = default(string);
                                        }
                                        break;
                                    case CellType.Numeric://数字
                                        if (DateUtil.IsCellDateFormatted(row.GetCell(j)))//时间戳数字
                                        {
                                            dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
                                        }
                                        else
                                        {
                                            dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
                                        }
                                        break;
                                    case CellType.Boolean:
                                        dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                        break;
                                    case CellType.Error:
                                        dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                        break;
                                    case CellType.Formula://公式
                                        switch (row.GetCell(j).CachedFormulaResultType)
                                        {
                                            case CellType.String:
                                                string strFORMULA = row.GetCell(j).StringCellValue;
                                                if (strFORMULA != null && strFORMULA.Length > 0)
                                                {
                                                    dataRow[j] = strFORMULA.ToString();
                                                }
                                                else
                                                {
                                                    dataRow[j] = null;
                                                }
                                                break;
                                            case CellType.Numeric:
                                                dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                                                break;
                                            case CellType.Boolean:
                                                dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                                break;
                                            case CellType.Error:
                                                dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                                break;
                                            default:
                                                dataRow[j] = "";
                                                break;
                                        }
                                        break;
                                    default:
                                        dataRow[j] = "";
                                        break;
                                }
                            }
                        }
                        catch (Exception exception)
                        {
                            loger.Error(exception.ToString());
                        }
                    }
                    table.Rows.Add(dataRow);
                }
                catch (Exception exception)
                {
                    loger.Error(exception.ToString());
                }
            }
        }
        catch (Exception exception)
        {
            loger.Error(exception.ToString());
        }
        return table;
    }

    /// <summary>
    /// 读取Excel文件特定名字sheet的内容到DataTable
    /// </summary>
    /// <param name="strFileName">excel文件路径</param>
    /// <param name="sheet">需要导出的sheet</param>
    /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
    /// <param name="dir">excel列名和DataTable列名的对应字典</param>
    /// <returns></returns>
    public static DataTable ImportExceltoDt(string strFileName, Dictionary<string, string> dir, string SheetName, int HeaderRowIndex = 1)
    {
        DataTable table = new DataTable();
        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
        {
            if (file.Length > 0)
            {
                IWorkbook wb = WorkbookFactory.Create(file);
                ISheet isheet = wb.GetSheet(SheetName);
                table = ImportDt(isheet, HeaderRowIndex, dir);
                isheet = null;
            }
        }
        return table;
    }

    /// <summary>
    /// 读取Excel文件某一索引sheet的内容到DataTable
    /// </summary>
    /// <param name="strFileName">excel文件路径</param>
    /// <param name="sheet">需要导出的sheet序号</param>
    /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
    /// <param name="dir">excel列名和DataTable列名的对应字典</param>
    /// <returns></returns>
    public static DataTable ImportExceltoDt(string strFileName, Dictionary<string, string> dir, int HeaderRowIndex = 1, int SheetIndex = 0)
    {
        DataTable table = new DataTable();
        using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
        {
            if (file.Length > 0)
            {
                IWorkbook wb = WorkbookFactory.Create(file);
                ISheet isheet = wb.GetSheetAt(SheetIndex);
                table = ImportDt(isheet, HeaderRowIndex, dir);
                isheet = null;
            }
        }
        return table;

    }
    #endregion



    /// <summary>
    /// 获取excel文件的sheet数目
    /// </summary>
    /// <param name="outputFile"></param>
    /// <returns></returns>
    public static int GetSheetNumber(string outputFile)
    {
        int number = 0;
        using (FileStream readfile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Read))
        {
            if (readfile.Length > 0)
            {
                IWorkbook wb = WorkbookFactory.Create(readfile);
                number = wb.NumberOfSheets;
            }
        }
        return number;
    }

    /// <summary>
    /// 判断内容是否是数字
    /// </summary>
    /// <param name="message"></param>
    /// <param name="result"></param>
    /// <returns></returns>
    public static bool isNumeric(String message, out double result)
    {
        Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
        result = -1;
        if (rex.IsMatch(message))
        {
            result = double.Parse(message);
            return true;
        }
        else
            return false;
    }

    /// <summary>
    /// 验证导入的Excel是否有数据
    /// </summary>
    /// <param name="excelFileStream"></param>
    /// <returns></returns>
    public static bool HasData(Stream excelFileStream)
    {
        using (excelFileStream)
        {
            IWorkbook workBook = new HSSFWorkbook(excelFileStream);
            if (workBook.NumberOfSheets > 0)
            {
                ISheet sheet = workBook.GetSheetAt(0);
                return sheet.PhysicalNumberOfRows > 0;
            }
        }
        return false;
    }
}

View Code

转载于:https://www.cnblogs.com/wyy1234/p/10039767.html

banyongzhan5405

关注

0
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
NPOI使用教程附Helper

1 NPOI简介1.1 NPOI是什么　　NPOI是POI的.NET版本,POI是一套用Java写成的库，我们在开发中经常用到导入导出表格、文档的情况，NPOI能够帮助我们在没有安装微软Office的情况下读写Office文件，如xls, doc, ppt等。NPOI采用的是Apache 2.0许可证（poi也是采用这个许可证），这意味着它可以被用于任何商业或非商业项目，我们不用...
复制链接

扫一扫