winfrom 使用NPOI导入导出Excel(xls/xlsx)数据到DataTable中
标签：value hit ring src plain info empty ogre windows
1.通过NUGET管理器下载nopi，在引入命令空间
1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using System.IO;
5 using NPOI.SS.UserModel; //NPOI
6 using NPOI.HSSF.Util; //NPOI
7 using NPOI.HSSF.UserModel; //NPOI
8 using NPOI.XSSF.UserModel; //NPOI
9 using System.Data.SqlClient;
10 using System.Data;
View Code
2.导入功能
//
/// Excel某sheet中内容导入到DataTable中
/// 区分xsl和xslx分别处理
///
/// Excel文件路径,含文件全名
/// 此Excel中sheet名
///
public DataTable ExcelSheetImportToDataTable(string filePath, string sheetName)
{
DataTable dt = new DataTable();
if (Path.GetExtension(filePath).ToLower() == ".xls".ToLower())
{//.xls
#region .xls文件处理:HSSFWorkbook
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
ISheet sheet = hssfworkbook.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
for (int j = 0; j =dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
{
break;
}
ICell cell = row.GetCell(i);
if ((i==0)&&(string.IsNullOrEmpty(cell.ToString())==true))//每行第一个cell为空,break
{
break;
}
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
#endregion
}
else
{//.xlsx
#region .xlsx文件处理:XSSFWorkbook
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
ISheet sheet = hssfworkbook.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
for (int j = 0; j = dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
{
break;
}
ICell cell = row.GetCell(i);
if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
{
break;
}
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
#endregion
}
return dt;
}
3.导出功能
///
/// NPOI导出Excel，不依赖本地是否装有Excel，导出速度快
///
/// 要导出的dataGridView控件
/// sheet表名
///
public static void ExportToExcel(DataGridView dataGridView1, string sheetName)
{
SaveFileDialog fileDialog = new SaveFileDialog();
fileDialog.Filter = "Excel(97-2003)|*.xls";
if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
//不允许dataGridView显示添加行，负责导出时会报最后一行未实例化错误
dataGridView1.AllowUserToAddRows = false;
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
IRow rowHead = sheet.CreateRow(0);
//填写表头
for (int i = 0; i
在winform中使用很方面的，特别是些服务程序的
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using NPOI.SS.UserModel; //NPOI
using NPOI.HSSF.Util; //NPOI
using NPOI.HSSF.UserModel; //NPOI
using NPOI.XSSF.UserModel; //NPOI
using System.Data.SqlClient;
using System.Data;
winfrom 使用NPOI导入导出Excel(xls/xlsx)数据到DataTable中
标签：value hit ring src plain info empty ogre windows
原文地址：http://www.cnblogs.com/louby/p/8045078.html
评论