您现在的位置是:首页>博客详情

NPOI 1.2教程 - 6 项目实践

FreshMan2019年04月13日 23:58NPOI,Excel,Office425

简介还记得小学时候学的九九乘法表吗?这节我们一起学习利用NPOI通过C#代码生成一张Excel的九九乘法表。要生成九九乘法表,循环肯定是少不了的。另外,我们将综合NPOI的常用功能(包括创建和填充单元格、合并单元格、设置单元格样式和利用公式),做一个工资单的实例。

3.2 用NPOI操作EXCEL--生成九九乘法表

还记得小学时候学的九九乘法表吗?这节我们一起学习利用NPOI通过C#代码生成一张Excel的九九乘法表。要生成九九乘法表,循环肯定是少不了的,如下:

复制代码

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row;
HSSFCell cell;
for (int rowIndex = 0; rowIndex < 9; rowIndex++)
{
     row 
= sheet1.CreateRow(rowIndex);
     
for (int colIndex = 0; colIndex <= rowIndex; colIndex++)
     {
           cell 
= row.CreateCell(colIndex);
           cell.SetCellValue(String.Format(
"{0}*{1}={2}", rowIndex + 1, colIndex + 1, (rowIndex + 1* (colIndex + 1)));
     }
}

复制代码


      代码其实很简单,就是循环调用cell.SetCellValue(str)写入9行数据,每一行写的单元格数量随行数递增。执行完后生成的Excel样式如下:


完整的代码如下:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.HPSF;

namespace TimesTables
{
    
public class Program
    {
        
static HSSFWorkbook hssfworkbook;

        
static void Main(string[] args)
        {
            InitializeWorkbook();

            HSSFSheet sheet1 
= hssfworkbook.CreateSheet("Sheet1");
            HSSFRow row;
            HSSFCell cell;
            
for (int rowIndex = 0; rowIndex < 9; rowIndex++)
            {
                row 
= sheet1.CreateRow(rowIndex);
                
for (int colIndex = 0; colIndex <= rowIndex; colIndex++)
                {
                    cell 
= row.CreateCell(colIndex);
                    cell.SetCellValue(String.Format(
"{0}*{1}={2}", rowIndex + 1, colIndex + 1, (rowIndex + 1* (colIndex + 1)));
                }
            }

            WriteToFile();
        }

        
static void WriteToFile()
        {
            
//Write the stream data of workbook to the root directory
            FileStream file = new FileStream(@"test.xls", FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
        }

        
static void InitializeWorkbook()
        {
            hssfworkbook 
= new HSSFWorkbook();

            
//create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company 
= "NPOI Team";
            hssfworkbook.DocumentSummaryInformation 
= dsi;

            
//create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject 
= "NPOI SDK Example";
            hssfworkbook.SummaryInformation 
= si;
        }
    }
}


3.3 用NPOI操作EXCEL--生成一张工资单

 这一节,我们将综合NPOI的常用功能(包括创建和填充单元格、合并单元格、设置单元格样式和利用公式),做一个工资单的实例。先看创建标题行的代码:

复制代码

//写标题文本
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFCell cellTitle 
= sheet1.CreateRow(0).CreateCell(0);
cellTitle.SetCellValue(
"XXX公司2009年10月工资单");

//设置标题行样式
HSSFCellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment 
= HSSFCellStyle.ALIGN_CENTER;
HSSFFont font 
= hssfworkbook.CreateFont();
font.FontHeight 
= 20 * 20;
style.SetFont(font);

cellTitle.CellStyle 
= style;

//合并标题行
sheet1.AddMergedRegion(new Region(0016));

复制代码


其中用到了我们前面讲的设置单元格样式和合并单元格等内容。接下来我们循环创建公司每个员工的工资单:

复制代码


DataTable dt
=GetData();
HSSFRow row;
HSSFCell cell;
HSSFCellStyle celStyle
=getCellStyle();

HSSFPatriarch patriarch 
= sheet1.CreateDrawingPatriarch();
HSSFClientAnchor anchor;
HSSFSimpleShape line;
int rowIndex;
for (int i = 0; i < dt.Rows.Count; i++)
{
    
//表头数据
    rowIndex = 3 * (i + 1);
    row 
= sheet1.CreateRow(rowIndex);

    cell 
= row.CreateCell(0);
    cell.SetCellValue(
"姓名");
    cell.CellStyle 
= celStyle;

    cell 
= row.CreateCell(1);
    cell.SetCellValue(
"基本工资");
    cell.CellStyle 
= celStyle;

    cell 
= row.CreateCell(2);
    cell.SetCellValue(
"住房公积金");
    cell.CellStyle 
= celStyle;

    cell 
= row.CreateCell(3);
    cell.SetCellValue(
"绩效奖金");
    cell.CellStyle 
= celStyle;

    cell 
= row.CreateCell(4);
    cell.SetCellValue(
"社保扣款");
    cell.CellStyle 
= celStyle;

    cell 
= row.CreateCell(5);
    cell.SetCellValue(
"代扣个税");
    cell.CellStyle 
= celStyle;

    cell 
= row.CreateCell(6);
    cell.SetCellValue(
"实发工资");
    cell.CellStyle 
= celStyle;


    DataRow dr 
= dt.Rows[i];
    
//设置值和计算公式
    row = sheet1.CreateRow(rowIndex + 1);
    cell 
= row.CreateCell(0);
    cell.SetCellValue(dr[
"FName"].ToString());
    cell.CellStyle 
= celStyle;

    cell 
= row.CreateCell(1);
    cell.SetCellValue((
double)dr["FBasicSalary"]);
    cell.CellStyle 
= celStyle;

    cell 
= row.CreateCell(2);
    cell.SetCellValue((
double)dr["FAccumulationFund"]);
    cell.CellStyle 
= celStyle;

    cell 
= row.CreateCell(3);
    cell.SetCellValue((
double)dr["FBonus"]);
    cell.CellStyle 
= celStyle;

    cell 
= row.CreateCell(4);
    cell.SetCellFormula(String.Format(
"$B{0}*0.08",rowIndex+2));
    cell.CellStyle 
= celStyle;

    cell 
= row.CreateCell(5);
    cell.SetCellFormula(String.Format(
"SUM($B{0}:$D{0})*0.1",rowIndex+2));
    cell.CellStyle 
= celStyle;

    cell 
= row.CreateCell(6);
    cell.SetCellFormula(String.Format(
"SUM($B{0}:$D{0})-SUM($E{0}:$F{0})",rowIndex+2));
    cell.CellStyle 
= celStyle;


    
//绘制分隔线
    sheet1.AddMergedRegion(new Region(rowIndex+20, rowIndex+26));
    anchor 
= new HSSFClientAnchor(012510231250, rowIndex + 26, rowIndex + 2);
    line 
= patriarch.CreateSimpleShape(anchor);
    line.ShapeType 
= HSSFSimpleShape.OBJECT_TYPE_LINE;
    line.LineStyle 
= HSSFShape.LINESTYLE_DASHGEL;

}

复制代码

其中为了文件打印为单元格增加了黑色边框的样式(如果不设置边框样式,打印出来后是没有边框的)。另外,注意循环过程中excel中的行号随数据源中的行号变化处理。完整代码如下:


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

namespace Payroll
{
    
public class Program
    {
        
static HSSFWorkbook hssfworkbook;

        
static void Main(string[] args)
        {
            InitializeWorkbook();

            
//写标题文本
            HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            HSSFCell cellTitle 
= sheet1.CreateRow(0).CreateCell(0);
            cellTitle.SetCellValue(
"XXX公司2009年10月工资单");

            
//设置标题行样式
            HSSFCellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment 
= HSSFCellStyle.ALIGN_CENTER;
            HSSFFont font 
= hssfworkbook.CreateFont();
            font.FontHeight 
= 20 * 20;
            style.SetFont(font);

            cellTitle.CellStyle 
= style;

            
//合并标题行
            sheet1.AddMergedRegion(new Region(0016));

            DataTable dt
=GetData();
            HSSFRow row;
            HSSFCell cell;
            HSSFCellStyle celStyle
=getCellStyle();

            HSSFPatriarch patriarch 
= sheet1.CreateDrawingPatriarch();
            HSSFClientAnchor anchor;
            HSSFSimpleShape line;
            
int rowIndex;
            
for (int i = 0; i < dt.Rows.Count; i++)
            {
                
//表头数据
                rowIndex = 3 * (i + 1);
                row 
= sheet1.CreateRow(rowIndex);

                cell 
= row.CreateCell(0);
                cell.SetCellValue(
"姓名");
                cell.CellStyle 
= celStyle;

                cell 
= row.CreateCell(1);
                cell.SetCellValue(
"基本工资");
                cell.CellStyle 
= celStyle;

                cell 
= row.CreateCell(2);
                cell.SetCellValue(
"住房公积金");
                cell.CellStyle 
= celStyle;

                cell 
= row.CreateCell(3);
                cell.SetCellValue(
"绩效奖金");
                cell.CellStyle 
= celStyle;

                cell 
= row.CreateCell(4);
                cell.SetCellValue(
"社保扣款");
                cell.CellStyle 
= celStyle;

                cell 
= row.CreateCell(5);
                cell.SetCellValue(
"代扣个税");
                cell.CellStyle 
= celStyle;

                cell 
= row.CreateCell(6);
                cell.SetCellValue(
"实发工资");
                cell.CellStyle 
= celStyle;


                DataRow dr 
= dt.Rows[i];
                
//设置值和计算公式
                row = sheet1.CreateRow(rowIndex + 1);
                cell 
= row.CreateCell(0);
                cell.SetCellValue(dr[
"FName"].ToString());
                cell.CellStyle 
= celStyle;

                cell 
= row.CreateCell(1);
                cell.SetCellValue((
double)dr["FBasicSalary"]);
                cell.CellStyle 
= celStyle;

                cell 
= row.CreateCell(2);
                cell.SetCellValue((
double)dr["FAccumulationFund"]);
                cell.CellStyle 
= celStyle;

                cell 
= row.CreateCell(3);
                cell.SetCellValue((
double)dr["FBonus"]);
                cell.CellStyle 
= celStyle;

                cell 
= row.CreateCell(4);
                cell.SetCellFormula(String.Format(
"$B{0}*0.08",rowIndex+2));
                cell.CellStyle 
= celStyle;

                cell 
= row.CreateCell(5);
                cell.SetCellFormula(String.Format(
"SUM($B{0}:$D{0})*0.1",rowIndex+2));
                cell.CellStyle 
= celStyle;

                cell 
= row.CreateCell(6);
                cell.SetCellFormula(String.Format(
"SUM($B{0}:$D{0})-SUM($E{0}:$F{0})",rowIndex+2));
                cell.CellStyle 
= celStyle;


                
//绘制分隔线
                sheet1.AddMergedRegion(new Region(rowIndex+20, rowIndex+26));
                anchor 
= new HSSFClientAnchor(012510231250, rowIndex + 26, rowIndex + 2);
                line 
= patriarch.CreateSimpleShape(anchor);
                line.ShapeType 
= HSSFSimpleShape.OBJECT_TYPE_LINE;
                line.LineStyle 
= HSSFShape.LINESTYLE_DASHGEL;

            }

            WriteToFile();
        }

        
static DataTable GetData()
        {
            DataTable dt 
= new DataTable();
            dt.Columns.Add(
"FName",typeof(System.String));
            dt.Columns.Add(
"FBasicSalary",typeof(System.Double));
            dt.Columns.Add(
"FAccumulationFund"typeof(System.Double));
            dt.Columns.Add(
"FBonus"typeof(System.Double));

            dt.Rows.Add(
"令狐冲"600010002000);
            dt.Rows.Add(
"任盈盈"700010002500);
            dt.Rows.Add(
"林平之"500010001500);
            dt.Rows.Add(
"岳灵珊"40001000900);
            dt.Rows.Add(
"任我行"40001000800);
            dt.Rows.Add(
"风清扬"900050003000);

            
return dt;
        }

        
        
static HSSFCellStyle getCellStyle()
        {
            HSSFCellStyle cellStyle 
= hssfworkbook.CreateCellStyle();
            cellStyle.BorderBottom 
= HSSFCellStyle.BORDER_THIN;
            cellStyle.BorderLeft 
= HSSFCellStyle.BORDER_THIN;
            cellStyle.BorderRight 
= HSSFCellStyle.BORDER_THIN;
            cellStyle.BorderTop 
= HSSFCellStyle.BORDER_THIN;
            
return cellStyle;
        }

        
static void WriteToFile()
        {
            
//Write the stream data of workbook to the root directory
            FileStream file = new FileStream(@"test.xls", FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
        }

        
static void InitializeWorkbook()
        {
            hssfworkbook 
= new HSSFWorkbook();

            
//create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company 
= "NPOI Team";
            hssfworkbook.DocumentSummaryInformation 
= dsi;

            
//create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject 
= "NPOI SDK Example";
            hssfworkbook.SummaryInformation 
= si;
        }
    }
}

生成的Excel文件样式如下:


3.4 用NPOI操作EXCEL--从Excel中抽取文本

我们知道,搜索引擎最擅长处理的就是文本,而Excel中的内容并不是以文本方式存储的。那么如果想要搜索引擎爬虫能够抓取到Excel中的内容是比较困难的,除非搜索引擎爬虫对Excel格式进行专门的处理。那么有没有办法解决此问题呢?有,通过NPOI将Excel内容文本化!

  如下,有这样一张Excel,如果想让它被搜索引擎收录,常用的方式是以HTML形式展现,但将一个个这样的Excel手工做成HTML页面显然比较麻烦。接下来,我们将提供一种方案,自动将Excel中的内容以HTML形式展现。

  其实基本思想也很简单,就是通过NPOI读取每个Cell中的内容,然后以HTML的形式输出。但要保证输出的HTML页面布局与Excel中的一致,还有点小技巧。下面是构造Table的代码:

复制代码

private HSSFSheet sht;
protected String excelContent;

protected void Page_Load(object sender, EventArgs e)
{
    HSSFWorkbook wb 
= new HSSFWorkbook(new FileStream(Server.MapPath("App_Data/quotation.xls"), FileMode.Open));
    sht 
= wb.GetSheet("Sheet1");

    
//取行Excel的最大行数
    int rowsCount = sht.PhysicalNumberOfRows;
    
//为保证Table布局与Excel一样,这里应该取所有行中的最大列数(需要遍历整个Sheet)。
    
//为少一交全Excel遍历,提高性能,我们可以人为把第0行的列数调整至所有行中的最大列数。
    int colsCount = sht.GetRow(0).PhysicalNumberOfCells;

    
int colSpan;
    
int rowSpan;
    
bool isByRowMerged;

    StringBuilder table 
= new StringBuilder(rowsCount * 32);

    table.Append(
"<table border='1px'>");
    
for (int rowIndex = 0; rowIndex < rowsCount; rowIndex++)
    {
        table.Append(
"<tr>");
        
for (int colIndex = 0; colIndex < colsCount; colIndex++)
        {
            GetTdMergedInfo(rowIndex, colIndex, 
out colSpan, out rowSpan, out isByRowMerged);
            
//如果已经被行合并包含进去了就不输出TD了。
            
//注意被合并的行或列不输出的处理方式不一样,见下面一处的注释说明了列合并后不输出TD的处理方式。
            if (isByRowMerged)
            {
                
continue;
            }
            
            table.Append(
"<td");
            
if (colSpan > 1)
                table.Append(
string.Format(" colSpan={0}", colSpan));
            
if (rowSpan > 1)
                table.Append(
string.Format(" rowSpan={0}", rowSpan));
            table.Append(
">");

            table.Append(sht.GetRow(rowIndex).GetCell(colIndex));

            
//列被合并之后此行将少输出colSpan-1个TD。
            if (colSpan > 1)
                colIndex 
+= colSpan - 1;

            table.Append(
"</td>");

        }
        table.Append(
"</tr>");
    }
    table.Append(
"</table>");

    
this.excelContent = table.ToString();
}

复制代码

  其中用到的GetTdMergedInfo方法代码如下:

复制代码

/// <summary>
///  获取Table某个TD合并的列数和行数等信息。与Excel中对应Cell的合并行数和列数一致。
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="colIndex">列号</param>
/// <param name="colspan">TD中需要合并的行数</param>
/// <param name="rowspan">TD中需要合并的列数</param>
/// <param name="rowspan">此单元格是否被某个行合并包含在内。如果被包含在内,将不输出TD。</param>
/// <returns></returns>
private void GetTdMergedInfo(int rowIndex, int colIndex, out int colspan, out int rowspan, out bool isByRowMerged)
{
    colspan 
= 1;
    rowspan 
= 1;
    isByRowMerged 
= false;
    
int regionsCuont = sht.NumMergedRegions;
    Region region;
    
for (int i = 0; i < regionsCuont; i++)
    {
        region 
= sht.GetMergedRegionAt(i);
        
if (region.RowFrom == rowIndex && region.ColumnFrom == colIndex)
        {
            colspan 
= region.ColumnTo - region.ColumnFrom + 1;
            rowspan 
= region.RowTo - region.RowFrom + 1;

            
return;
        }
        
else if (rowIndex > region.RowFrom && rowIndex <= region.RowTo && colIndex>=region.ColumnFrom && colIndex<=region.ColumnTo)
        {
            isByRowMerged 
= true;
        }
    }
}

复制代码

最后在apsx页面中输出构建好的Table:

<%=excelContent %>

执行效果如下:

 

我们发现,与Excel中的布局完全一样(这里没有处理单元格的样式,只处理了内容,有兴趣的读者也可以将Excel中单元格的样式也应用在HTML中)。这里为保证布局一致,主要是将Excel中的Region信息解析成Table的colSpan和rowSpan属性,如果对这两个属性不太了解,可以结合以下代码和示例加以了解:

复制代码

<table width="300px" border="1px">
<tr>
    
<td colspan="2" rowspan="2">0,0</td>
    
<td>0,3</td>
</tr>
<tr>
    
<td>1,3</td>
</tr>
<tr>
    
<td rowspan="2">2,0</td>
    
<td colspan="2">2,1</td>
</tr>
<tr>
    
<td>3,1</td>
    
<td>3,2</td>
</tr>
</table>

复制代码

 以上HTML代码对应的Table展现为:


3.5 用NPOI操作EXCEL--巧妙使用Excel Chart

在NPOI中,本身并不支持Chart等高级对象的创建,但通过l模板的方式可以巧妙地利用Excel强大的透视和图表功能,请看以下例子。

首先建立模板文件,定义两列以及指向此区域的名称“sales”:

创建数据表,数据来源填入刚才定义的区域:

最后生成的数据透视表所在Sheet的样式如下:

至此,模板已经建好,另存为“D:\MyProject\NPOIDemo\Chart\Book2.xls”。我们发现,模板就相当于一个“空架子”,仅仅有操作方式没并没有任何数据。下一步,我们往这个“空架子”中填入数据。我们通过如下代码往这个“空架子”中写入数据:

复制代码

static void Main(string[] args)
{
    HSSFWorkbook wb 
= new HSSFWorkbook(new FileStream(@"D:\MyProject\NPOIDemo\Chart\Book2.xls", FileMode.Open));

    HSSFSheet sheet1 
= wb.GetSheet("Sheet1");

    HSSFRow row 
= sheet1.CreateRow(1);
    row.CreateCell(
0).SetCellValue("令狐冲");
    row.CreateCell(
1).SetCellValue(50000);

    row 
= sheet1.CreateRow(2);
    row.CreateCell(
0).SetCellValue("任盈盈");
    row.CreateCell(
1).SetCellValue(30000);

    row 
= sheet1.CreateRow(3);
    row.CreateCell(
0).SetCellValue("风清扬");
    row.CreateCell(
1).SetCellValue(80000);

    row 
= sheet1.CreateRow(4);
    row.CreateCell(
0).SetCellValue("任我行");
    row.CreateCell(
1).SetCellValue(20000);

    
//Write the stream data of workbook to the root directory
    FileStream file = new FileStream(@"test.xls", FileMode.Create);
    wb.Write(file);
    file.Close();
}

复制代码

打开生成的test.xls文件,发现数据已经被填进去了:

再看数据透视表,也有数据了:

 

总结:
  Excel有着强大的报表透视和图表功能,而且简单易用,利用NPOI,可以对其进行充分利用。在做图形报表、透视报表时将非常有用!