近期一直在做如何将数据库的数据导出到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 = };
person.PersonName = "张三";
person.PersonSex = "男";
person.PersonIdCard = ;
Person person1 = new Person
{
PersonId = ,
PersonName = "李四",
PersonSex = "女",
PersonIdCard =
};
Person person2 = new Person
{
PersonId = ,
PersonName = "王二",
PersonSex = "男",
PersonIdCard =
};
Person person3 = new Person
{
PersonId = ,
PersonName = "麻子",
PersonSex = "女",
PersonIdCard =
}; 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();
for (int i = ; i < ds.Tables[].Columns.Count; i++)
{
cell = (HSSFCell)row.CreateCell(i);
cell.SetCellValue(String.Format(ds.Tables[].Columns[i].ColumnName));
}
//写记录
for (int rowIndex = ; rowIndex < ds.Tables[].Rows.Count; rowIndex++)
{
row = (HSSFRow)sheet1.CreateRow(rowIndex + );
for (int colIndex = ; colIndex < ds.Tables[].Columns.Count; colIndex++)
{
cell = (HSSFCell)row.CreateCell(colIndex);
cell.SetCellValue(String.Format(ds.Tables[].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", ));
}
/// <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 = ; 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 + ; 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进行遍历即可。然后将数据插入到数据库中。如果在其中遇到什么问题，可以留言。将会尽力解答你们所遇到的问题，希望相互学习，相互提高。