NPOI读取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展现为
二，
首先我们要准备一个用于打开文件流的函数InitializeWorkbook，由于文件读完后就没用了，所以这里直接用using（养成好习惯，呵呵）。
HSSFWorkbookhssfworkbook;
void InitializeWorkbook(string path)
{
//read the template via FileStream, it is suggested to use FileAccess.Readto prevent file lock.
//book1.xlsis an Excel-2007-generated file, so some new unknown BIFF records are added.
using (FileStream file =new FileStream(path, FileMode.Open,FileAccess.Read))
{
hssfworkbook = newHSSFWorkbook(file);
}
}
接下来我们要开始写最重要的函数ConvertToDataTable，即把HSSF的数据放到一个DataTable中。
HSSFSheetsheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while(rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
//TODO::Create DataTable row
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = row.GetCell(i);
//TODO::set cell value to the cell of DataTables
}
上面的结构大家都应该能看懂吧，无非就是先遍历行，再遍历行中的每一列。这里引出了一个难点，由于Excel的单元格有好几种类型，类型不同显示的东西就不同，具体的类型有布尔型、数值型、文本型、公式型、空白、错误。
publicenum HSSFCellType
{
Unknown = -1,
NUMERIC = 0,
STRING = 1,
FORMULA = 2,
BLANK = 3,
BOOLEAN = 4,
ERROR = 5,
}
这里的HSSFCellType描述了所有的类型，但细心的朋友可能已经发现了，这里没有日期型，这是为什么呢？这是因为Excel底层并没有一定日期型，而是通过数值型来替代，至于如何区分日期和数字，都是由文本显示的样式决定的，在NPOI中则是由HSSFDataFormat来处理。为了能够方便的获得所需要的类型所对应的文本，我们可以使用HSSFCell.ToString()来处理。
于是刚才的代码则变成了这样：
HSSFSheetsheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < 5;j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A')+j).ToString());
}
while(rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = row.GetCell(i);
if (cell ==
null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
是不是很简单，呵呵！
当然，如果你要对某个特定的单元格类型做特殊处理，可以通过判HSSFCell.CellType来解决，比如下面的代码：
switch(cell.CellType)
{
case HSSFCellType.BLANK:
dr[i] = "[null]";
break;
case HSSFCellType.BOOLEAN:
dr[i] =cell.BooleanCellValue;
break;
case HSSFCellType.NUMERIC:
dr[i] =cell.ToString(); //This
is a trick to get the correct value of the cell.NumericCellValue will
return a numeric value no matter the cell value is a dateor a number.
break;
case HSSFCellType.STRING:
dr[i] =cell.StringCellValue;
break;
case HSSFCellType.ERROR:
dr[i] = cell.ErrorCellValue;
break;
case HSSFCellType.FORMULA:
default:
dr[i] = "="+cell.CellFormula;
break;
}
这里只是举个简单的例子。
完整代码下载：https://files.cnblogs.com/tonyqus/ImportXlsToDataTable.zip
