NPOI将MSSQL数据与EXCEL数据进行导入导出
首先我们来讨论一下如何用ADO.NET处理Excel的技术
一、OLEAutomation
优点:强大,能够使用Excel的所有功能,要求装Excel。微软力推的用法,毕竟是一家子嘛,但!它的运行会启动Excel进程,不适合于服务器运行(ASP.NET网站(B\S),安全性,效率方面不适合),参考资料http://topic.csdn.net/t/20031204/09/2525334.html。
演示引用Excel.Interop
首页导入 office 的 12.0.0.0 版本的DLL(如果没有这个DLL,说明该系统的OFFICE版本不对)
引入命名空间后
missing = System.Reflection.Missing.Value;
ApplicationClass app = new ApplicationClass();
app.SheetsInNewWorkbook = 1;
//指定生成Excel的过程是否“可见”(会打开一个Excel)
//false表示后台自动创建好了
//true会打开一个Excel(可以看见的 )
app.Visible = false;
Workbook wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet sheet = (Worksheet)wb.Sheets[1];
sheet.get_Range("A1", "A1").Value2 = "hello,大家好!";
wb.SaveAs(@"c:\1.xls", XlFileFormat.xlExcel9795, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
app.Quit();
Console.WriteLine("ok");
Console.ReadKey();
object
//如果报“类型“Microsoft.Office.Interop.Excel.ApplicationClass”未定义构造函数
无法嵌入互操作类型“Microsoft.Office.Interop.Excel.ApplicationClass”。请改用适用的接口。 ”的错误,则:
将Microsoft.Office.Interop.Excel的属性(嵌入互操作类型),改为false。
二、把Excel当成数据库,使用Microsoft.Jet.OleDb访问访问Excel ,参考资料 http://tieba.baidu.com/f?kz=331569890 只适合于完全二维结构,功能最弱,很少用。不用装Excel。
三、OpenXML,微软提供的读写Excel的技术,优点和NPOI差不多,不过只能处理xlsx格式文件。docx、pptx。即Office 2007及以上版本。
四、NPOI、MyXls等,NPOI能够分析Excel文件的格式,能够进行常用Excel操作,不依赖于Excel,节省资源,没有安全性、性能的问题,在ASP.net中用最合适。只能处理xls格式文件、不能处理xlsx这样的新版本Excel文件格式。处理xlsx还要用OpenXML。
NPOI能提供Office 2003版本的导入导出,一般公司导入导出Excel 2003,也一般能够实现日常操作的一般功能,而且它学起来也比较简单。所以我今天主要是讲NPOI的使用。
NPOI是由两个程序员(一位是中国人,一位是土耳其人)编写的,他们把JAVA版本的POI移置成了.NET版本就成了NPOI(详见博客园:博主:Tony Qu)
好了,现在开始我们的NPOI将MSSQL数据与EXCEL数据进行导入导出
1、到网上下载NPOI的DLL文件
2、添加DLL的引用
3、引入命名空间
4、好了,一切准备就续,开始把MSSQL数据导出到EXCEL 2003
//数据导出
private void button1_Click(object sender, EventArgs e)
{
//得到reader对象
SqlDataReader reader = SqlHelper.ExecuteReader("select * from tblperson");
//判断是否有数据读出来
if (reader.HasRows)
{
//创建一个工作薄
using (Workbook wk = new HSSFWorkbook())
{
//创建一个工作表
using (Sheet sheet = wk.CreateSheet("name"))
{
//创建第一行,创建表头
Row row1 = sheet.CreateRow(0);
for (int i = 0; i < reader.FieldCount; i++)
{
//循环添加第一行数据
Cell cell = row1.CreateCell(i);
//添加表名到Cell的第一行的每个单元格中
cell.SetCellValue(reader.GetName(i)); //reader.GetName可以获得表名
}
//从索引为1的行开始添加数据库中的数据
int rowindex = 1;
while (reader.Read())
{
Row row = sheet.CreateRow(rowindex);
for (int i = 0; i < reader.FieldCount; i++) //reader.FieldCount可以获得表的行数
{
Cell cell = row.CreateCell(i);
//一定要先getvalue再tostring,如果得到的值为空的话,EXCEL中将是一个【空】,我把字换成一个“没有数据”
if (reader.GetValue(i).ToString() == "")
{
cell.SetCellValue("没有数据");//考虑到表中可能有空的数据
}
else
{
cell.SetCellValue(reader.GetValue(i).ToString());//SetCellValue可以把数据填到每一个单元格中
}
}
rowindex++;//这样就可以循环遍历每行了
}
//创建一个xls文档
using (FileStream fs = File.OpenWrite(@"e:\test.xls"))//创建一个EXCEL文件流
{
//将数据写到文件流中
wk.Write(fs);
}
}
}
MessageBox.Show("写入完毕");
}
else
{
MessageBox.Show("表中没有数据");
}
}
现在我们把Excel文件导入到数据库中, 反过来道理差不多,一些简单的就不再讲了
private void button2_Click(object sender, EventArgs e)
{
//把EXCEL文件读到文件流中
using (FileStream fs = File.OpenRead(@"E:\老师的课堂资料\20120511JsDom\05_三层项目\1\客户资料new.xls"))
{
using (Workbook wk = new HSSFWorkbook(fs))
{
using (Sheet sheet = wk.GetSheetAt(0))//读索引为0的工作表
{
string sql = "insert into t_customers(cc_customername,cc_cellphone,cc_landline,cc_carnum,cc_bracketnum,cc_buydate)values(@name,@phone,@line,@carnum,@bracketnum,@buydate)";
SqlParameter[] pms1 = new SqlParameter[]
{
//创建变量承载读出来的每个单元格的数据,有几个Cell就申明几个变量,注意,变量的数据类型要与数据库中的数据类型一致。
new SqlParameter("@name",SqlDbType.VarChar),
new SqlParameter("@phone",SqlDbType.VarChar),
new SqlParameter("@line",SqlDbType.VarChar),
new SqlParameter("@carnum",SqlDbType.VarChar),
new SqlParameter("@bracketnum",SqlDbType.VarChar),
new SqlParameter("@buydate",SqlDbType.DateTime)
};
//从索引为1的行读起
for (int i = 1; i < sheet.LastRowNum; i++)
{
Row row = sheet.GetRow(i);
for (int j = 0; j < row.LastCellNum; j++)
{
if (row.GetCell(j) == null)//如果EXCEL表中有空的单元格就往表里有一个‘空’,如果没这个判断的话,会出现末将对象引用到实例的错误
{
pms1[j].Value = "空";
}
else
{
pms1[j].Value = row.GetCell(j).ToString();
}
}
SqlHelper.ExecuteNonQuery(sql, pms1);//循环遍历每一行,加一行数据
}
//row end
}
//sheet end
}
//wk end
}
MessageBox.Show("数据导入成功");
//fs end
}
注意:如果在SqlHelper中出现这个错误是因为我们在上面循环遍历每一行,到第二操作的时候SqlParameter就不能为command对象再加一个SqlParameter对象。
那么我们就要把command对象里的parameter清空
int r = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return r;
学习心得:在学习第三方DLL文件 的引入时,如果有API的话,我们可以看API文档,但当我们不知道实现该接口的类到底有哪些成员变量和方法,一般情况下,它的成员和方法都不会很多,我们可以通过想实现的动作用平时的方法来“猜出来”,也可以‘点’着猜。