“NPOI” 实现“Excel”导入
“NPOI” 可以实现“Excel”文件的导出和导入。这里主要指office2003的“.XSL”文件，而不是office2007的“.XSLX”文件的导入，如果可以兼容office2007了话，还请指教。
一、前台页面文件格式判断
function checkType() {
//得到上传文件的值
var fileName = document.getElementById("ful_ImportIn").value;
//返回String对象中子字符串最后出现的位置.
var seat = fileName.lastIndexOf(".");
//返回位于String对象中指定位置的子字符串并转换为小写.
var extension = fileName.substring(seat).toLowerCase();
//判断允许上传的文件格式
//var allowed=[".jpg",".gif",".png",".bmp",".jpeg"];
var allowed = [".xls"];
for (var i = 0; i < allowed.length; i++) {
if (!(allowed[i] != extension)) {
return true;
}
}
alert("不支持" + extension + "格式");
return false;
}
<asp:FileUpload ID="ful_ImportIn" runat="server" Width="181px" /> <asp:Button ID="Bt_ImportIn" runat="server" Text="确定" onclick="Bt_ImportIn_Click" OnClientClick="checkType()" />
二、后台代码
if (ful_ImportIn.HasFile)
{
try
{
//string path=ful_ImportIn.F
using (Stream file = ful_ImportIn.FileContent)
{
hssfworkbook = new HSSFWorkbook(file);
}
ConvertToDataTable();
}
catch (Exception ex)
{
//JScript.Alert(ex.Message);
//JScript.Alert("Excel表格格式有误！");
}
}
else
{
}
}
void ConvertToDataTable()
{
ISheet sheet = 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())
{
IRow row = (HSSFRow)rows.Current;
//DataRow dr = dt.NewRow();
string datime = "";
string davalue = "";
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (i == 0)
{
if (cell != null)
{
datime = cell.ToString();
}
}
else
{
if (i == 1)
{
if (cell != null)
{
davalue = cell.ToString();
}
}
}
}
simulationListManager.Add_ResualtList(P_ID, datime, davalue);
//dt.Rows.Add(dr);
}
//dataSet1.Tables.Add(dt);
}
三、“Winform”代码示例，将Excel读到“DataTable”，显示到“DataGrid”下
namespace ImportXlsToDataTable
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
HSSFWorkbook hssfworkbook;
void InitializeWorkbook(string path)
{
//read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
//book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added.
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
void ConvertToDataTable()
{
ISheet sheet = 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())
{
IRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
dataSet1.Tables.Add(dt);
}
private void btnImport_Click(object sender, EventArgs e)
{
InitializeWorkbook(@"xls\Book1.xls");
ConvertToDataTable();
dataGridView1.DataSource = dataSet1.Tables[0];
}
//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 date or 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;
//}
}
}
