.NET调用NPOI组件导入导出Excel**的操作类
** 此NPOI操作类的优点如下：
（1）支持web及winform从DataTable导出到Excel；
（2）生成速度很快；
（3）准确判断数据类型，不会出现身份证转数值等问题；
（4）如果单页条数大于65535时会新建工作表；
（5）列宽自适应；
NPOI操作类
public class NPOIHelper
{
///
/// DataTable导出到Excel文件
///
/// 源DataTable
/// 表头文本
/// 保存位置
public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
{
using (MemoryStream ms = Export(dtSource, strHeaderText))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
</span><span style="color: #808080;">///</span> <span style="color: #808080;"><summary></span>
<span style="color: #808080;">///</span><span style="color: #008000;"> DataTable导出到Excel的MemoryStream
</span><span style="color: #808080;">///</span> <span style="color: #808080;"></summary></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><param name="dtSource"></span><span style="color: #008000;">源DataTable</span><span style="color: #808080;"></param></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><param name="strHeaderText"></span><span style="color: #008000;">表头文本</span><span style="color: #808080;"></param></span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> MemoryStream Export(DataTable dtSource, <span style="color: #0000ff;">string</span><span style="color: #000000;"> strHeaderText)
{
HSSFWorkbook workbook </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> HSSFWorkbook();
HSSFSheet sheet </span>=<span style="color: #000000;"> workbook.CreateSheet();
</span><span style="color: #0000ff;">#region</span> 右击文件 属性信息<span style="color: #000000;">
{
DocumentSummaryInformation dsi </span>=<span style="color: #000000;"> PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company </span>= <span style="color: #800000;">"</span><span style="color: #800000;">NPOI</span><span style="color: #800000;">"</span><span style="color: #000000;">;
workbook.DocumentSummaryInformation </span>=<span style="color: #000000;"> dsi;
SummaryInformation si </span>=<span style="color: #000000;"> PropertySetFactory.CreateSummaryInformation();
si.Author </span>= <span style="color: #800000;">"</span><span style="color: #800000;">文件作者信息</span><span style="color: #800000;">"</span>; <span style="color: #008000;">//</span><span style="color: #008000;">填加xls文件作者信息</span>
si.ApplicationName = <span style="color: #800000;">"</span><span style="color: #800000;">创建程序信息</span><span style="color: #800000;">"</span>; <span style="color: #008000;">//</span><span style="color: #008000;">填加xls文件创建程序信息</span>
si.LastAuthor = <span style="color: #800000;">"</span><span style="color: #800000;">最后保存者信息</span><span style="color: #800000;">"</span>; <span style="color: #008000;">//</span><span style="color: #008000;">填加xls文件最后保存者信息</span>
si.Comments = <span style="color: #800000;">"</span><span style="color: #800000;">作者信息</span><span style="color: #800000;">"</span>; <span style="color: #008000;">//</span><span style="color: #008000;">填加xls文件作者信息</span>
si.Title = <span style="color: #800000;">"</span><span style="color: #800000;">标题信息</span><span style="color: #800000;">"</span>; <span style="color: #008000;">//</span><span style="color: #008000;">填加xls文件标题信息</span>
si.Subject = <span style="color: #800000;">"</span><span style="color: #800000;">主题信息</span><span style="color: #800000;">"</span>;<span style="color: #008000;">//</span><span style="color: #008000;">填加文件主题信息</span>
si.CreateDateTime =<span style="color: #000000;"> DateTime.Now;
workbook.SummaryInformation </span>=<span style="color: #000000;"> si;
}
</span><span style="color: #0000ff;">#endregion</span><span style="color: #000000;">
HSSFCellStyle dateStyle </span>=<span style="color: #000000;"> workbook.CreateCellStyle();
HSSFDataFormat format </span>=<span style="color: #000000;"> workbook.CreateDataFormat();
dateStyle.DataFormat </span>= format.GetFormat(<span style="color: #800000;">"</span><span style="color: #800000;">yyyy-mm-dd</span><span style="color: #800000;">"</span><span style="color: #000000;">);
</span><span style="color: #008000;">//</span><span style="color: #008000;">取得列宽</span>
<span style="color: #0000ff;">int</span>[] arrColWidth = <span style="color: #0000ff;">new</span> <span style="color: #0000ff;">int</span><span style="color: #000000;">[dtSource.Columns.Count];
</span><span style="color: #0000ff;">foreach</span> (DataColumn item <span style="color: #0000ff;">in</span><span style="color: #000000;"> dtSource.Columns)
{
arrColWidth[item.Ordinal] </span>= Encoding.GetEncoding(<span style="color: #800080;">936</span><span style="color: #000000;">).GetBytes(item.ColumnName.ToString()).Length;
}
</span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = <span style="color: #800080;">0</span>; i < dtSource.Rows.Count; i++<span style="color: #000000;">)
{
</span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> j = <span style="color: #800080;">0</span>; j < dtSource.Columns.Count; j++<span style="color: #000000;">)
{
</span><span style="color: #0000ff;">int</span> intTemp = Encoding.GetEncoding(<span style="color: #800080;">936</span><span style="color: #000000;">).GetBytes(dtSource.Rows[i][j].ToString()).Length;
</span><span style="color: #0000ff;">if</span> (intTemp ><span style="color: #000000;"> arrColWidth[j])
{
arrColWidth[j] </span>=<span style="color: #000000;"> intTemp;
}
}
}
</span><span style="color: #0000ff;">int</span> rowIndex = <span style="color: #800080;">0</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">foreach</span> (DataRow row <span style="color: #0000ff;">in</span><span style="color: #000000;"> dtSource.Rows)
{
</span><span style="color: #0000ff;">#region</span> 新建表，填充表头，填充列头，样式
<span style="color: #0000ff;">if</span> (rowIndex == <span style="color: #800080;">10000</span> || rowIndex == <span style="color: #800080;">0</span><span style="color: #000000;">)
{
</span><span style="color: #0000ff;">if</span> (rowIndex != <span style="color: #800080;">0</span><span style="color: #000000;">)
{
</span><span style="color: #0000ff;">if</span> (strHeaderText.Length > <span style="color: #800080;">0</span><span style="color: #000000;">)
sheet </span>=<span style="color: #000000;"> workbook.CreateSheet(strHeaderText);
</span><span style="color: #0000ff;">else</span><span style="color: #000000;">
sheet </span>=<span style="color: #000000;"> workbook.CreateSheet();
}
</span><span style="color: #0000ff;">#region</span> 表头及样式<span style="color: #000000;">
{
</span><span style="color: #0000ff;">if</span> (strHeaderText.Length > <span style="color: #800080;">0</span><span style="color: #000000;">)
{
HSSFRow headerRow </span>= sheet.CreateRow(<span style="color: #800080;">0</span><span style="color: #000000;">);
headerRow.HeightInPoints </span>= <span style="color: #800080;">25</span><span style="color: #000000;">;
headerRow.CreateCell(</span><span style="color: #800080;">0</span><span style="color: #000000;">).SetCellValue(strHeaderText);
HSSFCellStyle headStyle </span>=<span style="color: #000000;"> workbook.CreateCellStyle();
headStyle.Alignment </span>=<span style="color: #000000;"> NPOI.HSSF.UserModel.HSSFCellStyle.ALIGN_CENTER;
HSSFFont font </span>=<span style="color: #000000;"> workbook.CreateFont();
font.FontHeightInPoints </span>= <span style="color: #800080;">20</span><span style="color: #000000;">;
font.Boldweight </span>= <span style="color: #800080;">700</span><span style="color: #000000;">;
headStyle.SetFont(font);
headerRow.GetCell(</span><span style="color: #800080;">0</span>).CellStyle =<span style="color: #000000;"> headStyle;
sheet.AddMergedRegion(</span><span style="color: #0000ff;">new</span> Region(<span style="color: #800080;">0</span>, <span style="color: #800080;">0</span>, <span style="color: #800080;">0</span>, dtSource.Columns.Count - <span style="color: #800080;">1</span><span style="color: #000000;">));
}
</span><span style="color: #008000;">//</span><span style="color: #008000;">headerRow.Dispose();</span>
<span style="color: #000000;"> }
</span><span style="color: #0000ff;">#endregion</span>
<span style="color: #0000ff;">#region</span> 列头及样式<span style="color: #000000;">
{
HSSFRow headerRow </span>= sheet.CreateRow(<span style="color: #800080;">1</span><span style="color: #000000;">);
HSSFCellStyle headStyle </span>=<span style="color: #000000;"> workbook.CreateCellStyle();
headStyle.Alignment </span>=<span style="color: #000000;"> NPOI.HSSF.UserModel.HSSFCellStyle.ALIGN_CENTER;
HSSFFont font </span>=<span style="color: #000000;"> workbook.CreateFont();
font.FontHeightInPoints </span>= <span style="color: #800080;">10</span><span style="color: #000000;">;
font.Boldweight </span>= <span style="color: #800080;">700</span><span style="color: #000000;">;
headStyle.SetFont(font);
</span><span style="color: #0000ff;">foreach</span> (DataColumn column <span style="color: #0000ff;">in</span><span style="color: #000000;"> dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle </span>=<span style="color: #000000;"> headStyle;
</span><span style="color: #008000;">//</span><span style="color: #008000;">设置列宽</span>
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + <span style="color: #800080;">1</span>) * <span style="color: #800080;">256</span><span style="color: #000000;">);
}
</span><span style="color: #008000;">//</span><span style="color: #008000;"> headerRow.Dispose();</span>
<span style="color: #000000;"> }
</span><span style="color: #0000ff;">#endregion</span><span style="color: #000000;">
rowIndex </span>= <span style="color: #800080;">2</span><span style="color: #000000;">;
}
</span><span style="color: #0000ff;">#endregion</span>
<span style="color: #0000ff;">#region</span> 填充内容<span style="color: #000000;">
HSSFRow dataRow </span>=<span style="color: #000000;"> sheet.CreateRow(rowIndex);
</span><span style="color: #0000ff;">foreach</span> (DataColumn column <span style="color: #0000ff;">in</span><span style="color: #000000;"> dtSource.Columns)
{
HSSFCell newCell </span>=<span style="color: #000000;"> dataRow.CreateCell(column.Ordinal);
</span><span style="color: #0000ff;">string</span> drValue =<span style="color: #000000;"> row[column].ToString();
</span><span style="color: #0000ff;">switch</span><span style="color: #000000;"> (column.DataType.ToString())
{
</span><span style="color: #0000ff;">case</span> <span style="color: #800000;">"</span><span style="color: #800000;">System.String</span><span style="color: #800000;">"</span>:<span style="color: #008000;">//</span><span style="color: #008000;">字符串类型</span>
drValue = drValue.Replace(<span style="color: #800000;">"</span><span style="color: #800000;">&</span><span style="color: #800000;">"</span>, <span style="color: #800000;">"</span><span style="color: #800000;">&</span><span style="color: #800000;">"</span><span style="color: #000000;">);
drValue </span>= drValue.Replace(<span style="color: #800000;">"</span><span style="color: #800000;">></span><span style="color: #800000;">"</span>, <span style="color: #800000;">"</span><span style="color: #800000;">></span><span style="color: #800000;">"</span><span style="color: #000000;">);
drValue </span>= drValue.Replace(<span style="color: #800000;">"</span><span style="color: #800000;"><</span><span style="color: #800000;">"</span>, <span style="color: #800000;">"</span><span style="color: #800000;"><</span><span style="color: #800000;">"</span><span style="color: #000000;">);
newCell.SetCellValue(drValue);
</span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">case</span> <span style="color: #800000;">"</span><span style="color: #800000;">System.DateTime</span><span style="color: #800000;">"</span>:<span style="color: #008000;">//</span><span style="color: #008000;">日期类型</span>
<span style="color: #000000;"> DateTime dateV;
DateTime.TryParse(drValue, </span><span style="color: #0000ff;">out</span><span style="color: #000000;"> dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle </span>= dateStyle;<span style="color: #008000;">//</span><span style="color: #008000;">格式化显示</span>
<span style="color: #0000ff;">break</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">case</span> <span style="color: #800000;">"</span><span style="color: #800000;">System.Boolean</span><span style="color: #800000;">"</span>:<span style="color: #008000;">//</span><span style="color: #008000;">布尔型</span>
<span style="color: #0000ff;">bool</span> boolV = <span style="color: #0000ff;">false</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">bool</span>.TryParse(drValue, <span style="color: #0000ff;">out</span><span style="color: #000000;"> boolV);
newCell.SetCellValue(boolV);
</span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">case</span> <span style="color: #800000;">"</span><span style="color: #800000;">System.Int16</span><span style="color: #800000;">"</span>:<span style="color: #008000;">//</span><span style="color: #008000;">整型</span>
<span style="color: #0000ff;">case</span> <span style="color: #800000;">"</span><span style="color: #800000;">System.Int32</span><span style="color: #800000;">"</span>:<span style="color: #008000;">//</span><span style="color: #008000;">整型</span>
<span style="color: #0000ff;">case</span> <span style="color: #800000;">"</span><span style="color: #800000;">System.Int64</span><span style="color: #800000;">"</span>:<span style="color: #008000;">//</span><span style="color: #008000;">整型</span>
<span style="color: #0000ff;">case</span> <span style="color: #800000;">"</span><span style="color: #800000;">System.Int</span><span style="color: #800000;">"</span>:<span style="color: #008000;">//</span><span style="color: #008000;">整型</span>
<span style="color: #0000ff;">int</span> invV = <span style="color: #800080;">0</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">int</span>.TryParse(drValue, <span style="color: #0000ff;">out</span><span style="color: #000000;"> invV);
newCell.SetCellValue(invV);
</span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">case</span> <span style="color: #800000;">"</span><span style="color: #800000;">System.Byte</span><span style="color: #800000;">"</span><span style="color: #000000;">:
</span><span style="color: #0000ff;">int</span> intV = <span style="color: #800080;">0</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">int</span>.TryParse(drValue, <span style="color: #0000ff;">out</span><span style="color: #000000;"> intV);
newCell.SetCellValue(intV);
</span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">case</span> <span style="color: #800000;">"</span><span style="color: #800000;">System.Decimal</span><span style="color: #800000;">"</span>:<span style="color: #008000;">//</span><span style="color: #008000;">浮点型 </span>
<span style="color: #0000ff;">case</span> <span style="color: #800000;">"</span><span style="color: #800000;">System.Double</span><span style="color: #800000;">"</span><span style="color: #000000;">:
</span><span style="color: #0000ff;">double</span> doubV = <span style="color: #800080;">0</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">double</span>.TryParse(drValue, <span style="color: #0000ff;">out</span><span style="color: #000000;"> doubV);
newCell.SetCellValue(doubV);
</span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">case</span> <span style="color: #800000;">"</span><span style="color: #800000;">System.DBNull</span><span style="color: #800000;">"</span>:<span style="color: #008000;">//</span><span style="color: #008000;">空值处理</span>
newCell.SetCellValue(<span style="color: #800000;">""</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">default</span><span style="color: #000000;">:
newCell.SetCellValue(</span><span style="color: #800000;">""</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
}
}
</span><span style="color: #0000ff;">#endregion</span><span style="color: #000000;">
rowIndex</span>++<span style="color: #000000;">;
}
</span><span style="color: #0000ff;">using</span> (MemoryStream ms = <span style="color: #0000ff;">new</span><span style="color: #000000;"> MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position </span>= <span style="color: #800080;">0</span><span style="color: #000000;">;
</span><span style="color: #008000;">//</span><span style="color: #008000;">sheet.Dispose();
</span><span style="color: #008000;">//</span><span style="color: #008000;">workbook.Dispose();</span><span style="color: #008000;">//</span><span style="color: #008000;">一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet</span>
<span style="color: #0000ff;">return</span><span style="color: #000000;"> ms;
}
}
</span><span style="color: #808080;">///</span> <span style="color: #808080;"><summary></span>
<span style="color: #808080;">///</span><span style="color: #008000;"> 用于Web导出
</span><span style="color: #808080;">///</span> <span style="color: #808080;"></summary></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><param name="dtSource"></span><span style="color: #008000;">源DataTable</span><span style="color: #808080;"></param></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><param name="strHeaderText"></span><span style="color: #008000;">表头文本</span><span style="color: #808080;"></param></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><param name="strFileName"></span><span style="color: #008000;">文件名</span><span style="color: #808080;"></param></span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">void</span> ExportByWeb(DataTable dtSource, <span style="color: #0000ff;">string</span> strHeaderText, <span style="color: #0000ff;">string</span><span style="color: #000000;"> strFileName)
{
HttpContext curContext </span>=<span style="color: #000000;"> HttpContext.Current;
</span><span style="color: #008000;">//</span><span style="color: #008000;"> 设置编码和附件格式</span>
curContext.Response.ContentType = <span style="color: #800000;">"</span><span style="color: #800000;">application/vnd.ms-excel</span><span style="color: #800000;">"</span><span style="color: #000000;">;
curContext.Response.ContentEncoding </span>=<span style="color: #000000;"> Encoding.UTF8;
curContext.Response.Charset </span>= <span style="color: #800000;">""</span><span style="color: #000000;">;
curContext.Response.AppendHeader(</span><span style="color: #800000;">"</span><span style="color: #800000;">Content-Disposition</span><span style="color: #800000;">"</span><span style="color: #000000;">,
</span><span style="color: #800000;">"</span><span style="color: #800000;">attachment;filename=</span><span style="color: #800000;">"</span> +<span style="color: #000000;"> HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
curContext.Response.End();
}
</span><span style="color: #008000;">//</span><span style="color: #008000;">public static DataTable Import(string strFileName, int sheetindex)
</span><span style="color: #008000;">//</span><span style="color: #008000;">{
</span><span style="color: #008000;">//</span><span style="color: #008000;"> return Import(strFileName, sheetindex);
</span><span style="color: #008000;">//</span><span style="color: #008000;">}</span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><summary></span><span style="color: #008000;">读取excel
</span><span style="color: #808080;">///</span><span style="color: #008000;"> 默认第一行为标头
</span><span style="color: #808080;">///</span> <span style="color: #808080;"></summary></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><param name="strFileName"></span><span style="color: #008000;">excel文档路径</span><span style="color: #808080;"></param></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><returns></returns></span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> DataTable Import(<span style="color: #0000ff;">string</span> strFileName, <span style="color: #0000ff;">int</span> sheetindex, <span style="color: #0000ff;">int</span> recordcount, <span style="color: #0000ff;">int</span><span style="color: #000000;"> topcount)
{
DataTable dt </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> DataTable();
HSSFWorkbook hssfworkbook;
</span><span style="color: #0000ff;">using</span> (FileStream file = <span style="color: #0000ff;">new</span><span style="color: #000000;"> FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> HSSFWorkbook(file);
}
HSSFSheet sheet </span>=<span style="color: #000000;"> hssfworkbook.GetSheetAt(sheetindex);
System.Collections.IEnumerator rows </span>=<span style="color: #000000;"> sheet.GetRowEnumerator();
</span><span style="color: #0000ff;">if</span> (topcount == <span style="color: #800080;">0</span><span style="color: #000000;">)
topcount </span>= <span style="color: #800080;">1</span><span style="color: #000000;">;
HSSFRow headerRow </span>= sheet.GetRow(<span style="color: #800080;">0</span> + topcount - <span style="color: #800080;">1</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">int</span> cellCount =<span style="color: #000000;"> headerRow.LastCellNum;
</span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> j = <span style="color: #800080;">0</span>; j < cellCount; j++<span style="color: #000000;">)
{
HSSFCell cell </span>=<span style="color: #000000;"> headerRow.GetCell(j);
</span><span style="color: #0000ff;">if</span> (cell == <span style="color: #0000ff;">null</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">continue</span><span style="color: #000000;">;
dt.Columns.Add(cell.ToString());
}
</span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = (sheet.FirstRowNum + topcount); i <= sheet.LastRowNum; i++<span style="color: #000000;">)
{
</span><span style="color: #0000ff;">if</span> (recordcount > <span style="color: #800080;">0</span><span style="color: #000000;">)
{
</span><span style="color: #0000ff;">if</span> (i > (recordcount + <span style="color: #800080;">2</span><span style="color: #000000;">))
</span><span style="color: #0000ff;">break</span><span style="color: #000000;">;
}
HSSFRow row </span>=<span style="color: #000000;"> sheet.GetRow(i);
DataRow dataRow </span>=<span style="color: #000000;"> dt.NewRow();
</span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> j = row.FirstCellNum; j < cellCount; j++<span style="color: #000000;">)
{
</span><span style="color: #0000ff;">if</span> (row.GetCell(j) != <span style="color: #0000ff;">null</span><span style="color: #000000;">)
{
</span><span style="color: #0000ff;">if</span>(dt.Columns.Count><span style="color: #000000;">j)
dataRow[j] </span>=<span style="color: #000000;"> row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> dt;
}
</span><span style="color: #808080;">///</span> <span style="color: #808080;"><summary></span>
<span style="color: #808080;">///</span><span style="color: #008000;"> 获取Sheet列信息
</span><span style="color: #808080;">///</span> <span style="color: #808080;"></summary></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><param name="strFileName"></param></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><param name="sheetindex"></param></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><param name="topcount"></param></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><returns></returns></span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> System.Collections.Generic.List<<span style="color: #0000ff;">string</span>> GetSheetColumns(<span style="color: #0000ff;">string</span> strFileName, <span style="color: #0000ff;">int</span> sheetindex, <span style="color: #0000ff;">int</span><span style="color: #000000;"> topcount)
{
HSSFWorkbook hssfworkbook;
</span><span style="color: #0000ff;">using</span> (FileStream file = <span style="color: #0000ff;">new</span><span style="color: #000000;"> FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> HSSFWorkbook(file);
}
HSSFSheet sheet </span>=<span style="color: #000000;"> hssfworkbook.GetSheetAt(sheetindex);
System.Collections.IEnumerator rows </span>=<span style="color: #000000;"> sheet.GetRowEnumerator();
</span><span style="color: #0000ff;">if</span> (topcount < <span style="color: #800080;">1</span><span style="color: #000000;">)
topcount </span>= <span style="color: #800080;">1</span><span style="color: #000000;">;
HSSFRow headerRow </span>= sheet.GetRow(<span style="color: #800080;">0</span> + topcount - <span style="color: #800080;">1</span><span style="color: #000000;">);
</span><span style="color: #0000ff;">int</span> cellCount =<span style="color: #000000;"> headerRow.LastCellNum;
System.Collections.Generic.List</span><<span style="color: #0000ff;">string</span>> list = <span style="color: #0000ff;">new</span> System.Collections.Generic.List<<span style="color: #0000ff;">string</span>><span style="color: #000000;">();
</span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> j = <span style="color: #800080;">0</span>; j < cellCount; j++<span style="color: #000000;">)
{
HSSFCell cell </span>=<span style="color: #000000;"> headerRow.GetCell(j);
</span><span style="color: #0000ff;">if</span> (cell == <span style="color: #0000ff;">null</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">continue</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">if</span> (cell.ToString().Trim().Length > <span style="color: #800080;">0</span><span style="color: #000000;">)
{
list.Add(cell.ToString());
}
}
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> list;
}
</span><span style="color: #808080;">///</span> <span style="color: #808080;"><summary></span>
<span style="color: #808080;">///</span>
<span style="color: #808080;">///</span> <span style="color: #808080;"></summary></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><param name="strFileName"></param></span>
<span style="color: #808080;">///</span> <span style="color: #808080;"><returns></returns></span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> System.Collections.Generic.Dictionary<<span style="color: #0000ff;">int</span>, <span style="color: #0000ff;">string</span>> GetSheetList(<span style="color: #0000ff;">string</span><span style="color: #000000;"> strFileName)
{
HSSFWorkbook hssfworkbook;
</span><span style="color: #0000ff;">using</span> (FileStream file = <span style="color: #0000ff;">new</span><span style="color: #000000;"> FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> HSSFWorkbook(file);
}
System.Collections.Generic.Dictionary</span><<span style="color: #0000ff;">int</span>, <span style="color: #0000ff;">string</span>> lists = <span style="color: #0000ff;">new</span> System.Collections.Generic.Dictionary<<span style="color: #0000ff;">int</span>, <span style="color: #0000ff;">string</span>><span style="color: #000000;">();
</span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = <span style="color: #800080;">0</span>; i < hssfworkbook.NumberOfSheets; i++<span style="color: #000000;">)
{
lists.Add(i, hssfworkbook.GetSheetName(i));
}
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> lists;
}
}</span>