asp.net怎样使用NPOI 导出到excel和从excel导入到数据库

首先进入官网进行下载NPOI插件（http://npoi.codeplex.com/）。

   DBToExcel

1.先建一个Model代码如下

public class person{
public string PersonId  { get; set; }
public string PersonName   { get; set; }
public string PersonSex  { get; set; }
public string PersonIdCard  { get; set; }
}

3.将list转换成dataset或者datatable

public static DataSet ConvertToDataSet<T>(List<T> list)
{
if (list == null || list.Count <= 0)
{
return null;
}
DataSet ds = new DataSet();
DataTable dt = new DataTable(typeof(T).Name);
DataColumn column;
DataRow row;
System.Reflection.PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
foreach (T t in list)
{
if (t == null)
{
continue;
}
row = dt.NewRow();
for (int i = 0, j = myPropertyInfo.Length; i < j; i++)
{
System.Reflection.PropertyInfo pi = myPropertyInfo[i];
string name = pi.Name;
if (dt.Columns[name] == null)
{
column = new DataColumn(name, pi.PropertyType);
dt.Columns.Add(column);
}
row[name] = pi.GetValue(t, null);
}
dt.Rows.Add(row);
}
ds.Tables.Add(dt);
return ds;
}

下面把我自己写测试类给大家看看，代码如下。

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

namespace Test
{
class Test
{
private static string sheetName = "测试";
private static string workName = "test2";
private static string path = "D:";

public static void Main(string[] args){

//ConvertToDataSet(GetData());
//前台调用
HSSFWorkbook hassfworkbook = DtToExcel(ConvertToDataSet(GetData()), sheetName, workName, path);
//a.Write(Response.OutputStream)
替换以下两句：
//hassfworkbook.Write(HttpContext.Current.Response.outputStrem);
FileStream file = new FileStream(path + "/" + workName + ".xls", FileMode.Create);
hassfworkbook.Write(file);
file.Close();
//DtToExcel(ConvertToDataSet(GetData()), sheetName, workName, path);
}

public static List<Person> GetData() {Person person = new Person {PersonId = 0};person.PersonName = "张三";person.PersonSex = "男";person.PersonIdCard = 1245896352145825841;Person person1 = new Person{PersonId = 1,PersonName = "李四",PersonSex = "女",PersonIdCard = 1245896352145825841};Person person2 = new Person{PersonId = 2,PersonName = "王二",PersonSex = "男",PersonIdCard = 1245896352145825841};Person person3 = new Person{PersonId = 3,PersonName = "麻子",PersonSex = "女",PersonIdCard = 1245896352145825841};

List<Person> list = new List<Person>();

list.Add(person);list.Add(person1);list.Add(person2);list.Add(person3);

return list;
}
public static DataSet ListToDataSet<T>(List<Person> list)
{
if (list.Count==0) return new DataSet();
var properties = list[0].GetType().GetProperties();
var cols = properties.Select(p => new DataColumn(p.Name));
var dt = new DataTable();
dt.Columns.AddRange(cols.ToArray());
//list.ForEach(x => dt.Rows.Add(properties.Select(p => p.GetValue(x)).ToArray()));
return new DataSet {Tables = {dt}};
}

public static DataSet ConvertToDataSet<T>(List<T> list){if (list == null || list.Count <= 0){return null;}DataSet ds = new DataSet();DataTable dt = new DataTable(typeof(T).Name);DataColumn column;DataRow row;System.Reflection.PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);foreach (T t in list){if (t == null){continue;}row = dt.NewRow();for (int i = 0, j = myPropertyInfo.Length; i < j; i++){System.Reflection.PropertyInfo pi = myPropertyInfo[i];string name = pi.Name;if (dt.Columns[name] == null){column = new DataColumn(name, pi.PropertyType);dt.Columns.Add(column);}row[name] = pi.GetValue(t, null);}dt.Rows.Add(row);}ds.Tables.Add(dt);return ds;}

public static HSSFWorkbook DtToExcel(DataSet ds, string sheetName, string workName, string path)
{
HSSFWorkbook hassfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hassfworkbook.DocumentSummaryInformation = dsi;

SummaryInformation si = PropertySetFactory.CreateSummaryInformation();si.Subject = "NPOI SDK Example";hassfworkbook.SummaryInformation = si;

HSSFSheet sheet1 = (HSSFSheet)hassfworkbook.CreateSheet(sheetName);
HSSFRow row;
HSSFCell cell;

 

//写字段名
row = (HSSFRow)sheet1.CreateRow(0);
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
cell = (HSSFCell)row.CreateCell(i);
cell.SetCellValue(String.Format(ds.Tables[0].Columns[i].ColumnName));
}
//写记录
for (int rowIndex = 0; rowIndex < ds.Tables[0].Rows.Count; rowIndex++)
{
row = (HSSFRow)sheet1.CreateRow(rowIndex + 1);
for (int colIndex = 0; colIndex < ds.Tables[0].Columns.Count; colIndex++)
{
cell = (HSSFCell)row.CreateCell(colIndex);
cell.SetCellValue(String.Format(ds.Tables[0].Rows[rowIndex][colIndex].ToString()));
}
}

//前台调用// HSSFWorkbook a= .DtToExcel(....)//a.Write(Response.OutputStream)替换以下两句：//hassfworkbook.Write(HttpContext.Current.Response.outputStrem);//FileStream file = new FileStream(path + "/" + workName + ".cls", FileMode.Create);//hassfworkbook.Write(file);//file.Close();return hassfworkbook;}

}
}

ExcelDoDatatable

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

namespace TestNopi
{
    public static class NPOIExcelToTable
    {
        private static string path = "D:\aaa.xls";
        static void Main(string[] args)
        {
            AcquireTwoArray(ImportExcelToTable(@"path", 0));
        }
        /// <summary>   
        /// 将Excel文件中的数据读出到DataTable中(xls)   
        /// </summary>   
        /// <param name="file"></param>   
        /// <returns></returns>   
        public static DataSet ImportExcelToTable(string path, int index)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            using (FileStream fs = new FileStream(@"D:\aaa.xls", FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);

                ISheet sheet = hssfworkbook.GetSheetAt(index);
                //表头   
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueTypeForXls(header.GetCell(i) as HSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据   
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueTypeForXls(sheet.GetRow(i).GetCell(j) as HSSFCell);
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
                ds.Tables.Add(dt);
            }
            return ds;
        }
        /// <summary>   
        /// 获取单元格类型(xls)   
        /// </summary>   
        /// <param name="cell"></param>   
        /// <returns></returns>   
        private static object GetValueTypeForXls(HSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.BLANK: //BLANK:   
                    return null;
                case CellType.BOOLEAN: //BOOLEAN:   
                    return cell.BooleanCellValue;
                case CellType.NUMERIC: //NUMERIC:   
                    return cell.NumericCellValue;
                case CellType.STRING: //STRING:             
                    return cell.StringCellValue;
                case CellType.ERROR: //ERROR:   
                    return cell.ErrorCellValue;
                case CellType.FORMULA: //FORMULA:   
                default:
                    return "=" + cell.CellFormula;
            }
        }
        public static void AcquireTwoArray(DataSet ds)
        {

            foreach (DataTable dt in ds.Tables)//遍历所有的DataTable
            {
                foreach (DataRow dr in dt.Rows)//遍历所有的行
                {
                    foreach (DataColumn dc in dt.Columns)//遍历所有的列
                    {
                        Console.Write("{0},{1},{2}", dt.TableName, dc.ColumnName, dr[dc].ToString());//表名，列名，单元格数据
                    }
                }

            }

        }

    }
}

导入主要做到把excel转换到datatable，将datatable进行遍历即可。然后将数据插入到数据库中。