如何使用NPOI 导出到excel和导入excel到数据库

近期一直在做如何将数据库的数据导出到excel和导入excel到数据库。

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

我用的NPOI1.2.5稳定版。

使用这个网址http://tonyqus.sinaapp.com/可以查询你需要的一些资料。

我这次主要做的是数据库的数据导出到excel和导入excel到数据库。下来我说一下自己的思路。

   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; }
}

2.将person转换成list.

 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;
        }

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;
}

4.将Datatable转换成Excel

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;
        }

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

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;
}

}
}

 

里面的相关信息这里不做具体的介绍 详情请看http://tonyqus.sinaapp.com/tutorial 教程。

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进行遍历即可。然后将数据插入到数据库中。如果在其中遇到什么问题，可以留言。将会尽力解答你们所遇到的问题，希望相互学习，相互提高。