Excel基础
•整个Excel表格叫工作表：Workbook；工作表包含的叫页：Sheet；行：Row；单元格：Cell。
•Excel中的电话号码问题，看起来像数字的字符串以半角单引号开头就没问题了。
•使用区域（UsedRange）：用Excel表的时候不一定是从最左上角的单元格开始用，为了减小文件尺寸，有使用区域的概念，Excel只存储使用区域。
程序处理Excel的技术
•OLE Automation：程序启动一个Excel进程，然后和Excel进程进行通讯来进行Excel的操作。优点：强大，能够使用Excel的所有功能，要求装Excel，微软最推荐这种用法，因为可以促进Excel的销量。会启动Excel进程，不适合于服务器（比如Asp.Net网站，安全性、效率）。参考资料http://www.cnblogs.com/denylau/archive/2010/04/30/1725172.html
–演示一下，引用Excel.Interop，代码在备注
ApplicationClass c = new ApplicationClass();
c.Visible = false;
Workbook wb = c.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//wb.SaveAs("c:/1.xls", null, null, null, null, XlSaveAsAccessMode.xlNoChange, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
•(*)把Excel当成数据库，使用Microsoft.Jet.OleDb访问访问Excel ，参考资料 http://tieba.baidu.com/f?kz=331569890 只适合于完全二维结构，功能最弱，很少用。不用装Excel。
•(*) OpenXML，微软提供的读写Excel的技术，优点和NPOI差不多，不过只能处理xlsx格式文件。docx、pptx
•NPOI、MyXls等，NPOI能够分析Excel文件的格式，能够进行常用Excel操作，不依赖于Excel，节省资源，没有安全性、性能的问题，在ASP.net中用最合适。只能处理xls格式文件、不能处理xlsx这样的新版本Excel文件格式。
NPOI组件的引入
•我们平时调用的类是已经添加到引用的，如果想调用系统内置的没有添加引用的其他dll（*严格的说是在GAC中的Assembly）就要添加引用，在【.Net】选项卡中选择，对于另外一些第三方dll（*严格说是Assembly）则需要点击【浏览】选项卡选择对应的dll文件。
•将NPOI包解压到硬盘中，然后在项目中添加引用，浏览，将解压目录下的dll全部添加进来
•(*)POI是Apache的一个Java开源项目，NPOI是POI在.net下的移植版本，很多.Net的移植版本开源项目都是在原来的Java版本名称前加上N，比如NHibernate、NDoc、NUnit、NAnt。
NPOI起步
1、读取
using (FileStream stream = new FileStream(@"c:\客户资料.xls", FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(stream);
MessageBox.Show(workbook.GetSheetName(0));
}
2、遇到错误别慌，仔细看错误信息。可能遇到的问题：文件被其他进程占用。
3、NPOI处理WPS生成的XLS有问题。
读取Excel
private void button1_Click(object sender, EventArgs e)
{
//using (FileStream stream = new FileStream(@"c:\Book1.xls", FileMode.Open, FileAccess.Read))
//{
// HSSFWorkbook workbook = new HSSFWorkbook(stream);
// MessageBox.Show(workbook.GetSheetName(0));
//}
//using (FileStream stream = new FileStream(@"c:\Book1.xls", FileMode.Open, FileAccess.Read))
//{
// HSSFWorkbook workbook = new HSSFWorkbook(stream);
// HSSFSheet sheet = workbook.GetSheetAt(0);
// HSSFRow row = sheet.GetRow(0);
// HSSFCell cell1 = row.GetCell(1);
// HSSFCell cell0 = row.GetCell(0);
// MessageBox.Show(cell1.NumericCellValue.ToString());
// MessageBox.Show(cell0.StringCellValue.ToString());
//}
//using (FileStream stream = new FileStream(@"c:\Book1.xls", FileMode.Open, FileAccess.Read))
//{
// HSSFWorkbook workbook = new HSSFWorkbook(stream);
// HSSFSheet sheet = workbook.GetSheetAt(0);
// HSSFRow row = sheet.GetRow(0);
// HSSFCell cell1 = row.GetCell(1);
// if (cell1.CellType == HSSFCell.CELL_TYPE_STRING)
// {
// MessageBox.Show("字符串");
// }
// else if (cell1.CellType == HSSFCell.CELL_TYPE_NUMERIC)
// {
// MessageBox.Show("数字");
// }
//}
using (FileStream stream = new FileStream(@"c:\Book1.xls", FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(stream);
HSSFSheet sheet = workbook.GetSheetAt(0);
for (int i = 0; i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
HSSFCell cell0 = row.GetCell(0);
HSSFCell cell1 = row.GetCell(1);
MessageBox.Show(string.Format("{0}的年龄是：{1}",cell0.StringCellValue,cell1.NumericCellValue));
}
//MessageBox.Show(sheet.LastRowNum.ToString());
}
}
•读取字符串类型数据MessageBox.Show(sheet.GetRow(3).GetCell(4).StringCellValue); 读取数字类型数据NumericCellValue。
•判断单元格数据类型：读取GetCell(4).CellType，与HSSFCell类中定义的常量比较即可。
•判断使用区域：结束行号：LastRowNum
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using NPOI.HSSF.UserModel;
using CallCenterClient.DAL;
using System.Data.SqlClient;
namespace CallCenterClient.Customers
{
public partial class FormCustMgr : Form
{
public FormCustMgr()
{
InitializeComponent();
}
private void btnImportExcel_Click(object sender, EventArgs e)
{
//string filename = @"c:\khzl.xls";
string filename = txtFileName.Text;//界面上敲的是什么就是什么，没有转义，转义是给编译器看的
try
{
using (FileStream stream = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(stream);
HSSFSheet sheet = workbook.GetSheetAt(0);
for (int i = 1; i <= sheet.LastRowNum; i++)//注意从第1行开始读，第一行是表头
{
HSSFRow row = sheet.GetRow(i);
HSSFCell cellName = row.GetCell(0);
HSSFCell cellTel = row.GetCell(1);
HSSFCell cellBuyDate = row.GetCell(2);
HSSFCell cellCarNo = row.GetCell(3);
HSSFCell cellBrNo = row.GetCell(4);
//SqlHelper.ExecuteNonQuery("Insert into T_Customers(Id,Name,TelPhone,BuyDate,CarNum,BracketNum) values(newid(),@Name,@TelPhone,@BuyDate,@CarNum,@BracketNum)",
// new SqlParameter("Name", cellName.StringCellValue),
// new SqlParameter("TelPhone", cellTel.StringCellValue),
// new SqlParameter("BuyDate", cellBuyDate.DateCellValue),
// new SqlParameter("CarNum", cellCarNo.StringCellValue),
// new SqlParameter("BracketNum", cellBrNo.StringCellValue));//newid()写在SQL语句中，在C#看来就是普通字符串，把SQL扔给SQLServer运行，SQLServer会识别这个函数
//SqlHelper.ExecuteNonQuery("Insert into T_Customers(Id,Name,TelPhone,BuyDate,CarNum,BracketNum) values(" + newid() + ",@Name,@TelPhone,@BuyDate,@CarNum,@BracketNum)");//错误，注意区别。
SqlHelper.ExecuteNonQuery("Insert into T_Customers(Id,Name,TelPhone,BuyDate,CarNum,BracketNum) values(newid(),@Name,@TelPhone,@BuyDate,@CarNum,@BracketNum)",
new SqlParameter("Name", cellName.StringCellValue),//易错：别写成new SqlParameter("Name", cellName)
new SqlParameter("TelPhone", cellTel.StringCellValue),
new SqlParameter("BuyDate", cellBuyDate.DateCellValue),
new SqlParameter("CarNum", cellCarNo.StringCellValue),
new SqlParameter("BracketNum", cellBrNo.StringCellValue));
}
}
MessageBox.Show("导入成功！");
}
catch (IOException ex)//抓住IOException类型的异常（文件正在被打开等异常）
{
MessageBox.Show(ex.Message);
}
}
private void btnImportExcel2_Click(object sender, EventArgs e)
{
string filename = txtFileName.Text;//界面上敲的是什么就是什么，没有转义，转义是给编译器看的
try
{
using (FileStream stream = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(stream);
HSSFSheet sheet = workbook.GetSheetAt(0);
for (int i = 1; i <= sheet.LastRowNum; i++)//注意从第1行开始读，第一行是表头
{
HSSFRow row = sheet.GetRow(i);
HSSFCell cellName = row.GetCell(0);
HSSFCell cellTel = row.GetCell(1);
HSSFCell cellBuyDate = row.GetCell(2);
HSSFCell cellCarNo = row.GetCell(3);
HSSFCell cellBrNo = row.GetCell(4);
//正确写法
int count = (int)SqlHelper.ExecuteScalar("Select count(*) from T_Customers where CarNum=@CarNum or BracketNum=@BracketNum", new SqlParameter("CarNum", cellCarNo.StringCellValue),
new SqlParameter("BracketNum", cellBrNo.StringCellValue));
//是否数据库中有这辆车
if (count > 0)
{
continue;
}
DateTime buyDate;
if (cellBuyDate.CellType == HSSFCell.CELL_TYPE_NUMERIC)//日期类型
{
buyDate = cellBuyDate.DateCellValue;
}
else if (cellBuyDate.CellType == HSSFCell.CELL_TYPE_STRING)//2008年9月8号
{
string strBuyDate = cellBuyDate.StringCellValue;
strBuyDate = strBuyDate.Replace("号", "日");
buyDate = Convert.ToDateTime(strBuyDate);
}
else
{
throw new Exception("未知的购买日期单元格类型");
}
SqlHelper.ExecuteNonQuery("Insert into T_Customers(Id,Name,TelPhone,BuyDate,CarNum,BracketNum) values(newid(),@Name,@TelPhone,@BuyDate,@CarNum,@BracketNum)",
new SqlParameter("Name", cellName.StringCellValue),//易错：别写成new SqlParameter("Name", cellName)
new SqlParameter("TelPhone", cellTel.StringCellValue),
new SqlParameter("BuyDate", buyDate),
new SqlParameter("CarNum", cellCarNo.StringCellValue),
new SqlParameter("BracketNum", cellBrNo.StringCellValue));
//错误写法：一个SqlParameter只能被一个Command使用，只能添加一次
//SqlParameter pCarNum = new SqlParameter("CarNum", cellCarNo.StringCellValue);
//int count = (int)SqlHelper.ExecuteScalar("Select count(*) from T_Customers where CarNum=@CarNum or BracketNum=@BracketNum", pCarNum,
// new SqlParameter("BracketNum", cellBrNo.StringCellValue));
////是否数据库中有这辆车
//if (count > 0)
//{
// continue;
//}
//SqlHelper.ExecuteNonQuery("Insert into T_Customers(Id,Name,TelPhone,BuyDate,CarNum,BracketNum) values(newid(),@Name,@TelPhone,@BuyDate,@CarNum,@BracketNum)",
// new SqlParameter("Name", cellName.StringCellValue),//易错：别写成new SqlParameter("Name", cellName)
// new SqlParameter("TelPhone", cellTel.StringCellValue),
// new SqlParameter("BuyDate", cellBuyDate.DateCellValue),
// pCarNum,
// new SqlParameter("BracketNum", cellBrNo.StringCellValue));
//影响的行数指的是修改了(update、Delete等)的数据的行数，而不是select结果集的行数
//int count =SqlHelper.ExecuteNonQuery("Select * from T_Customers where CarNum=@CarNum or BracketNum=@BracketNum",new SqlParameter("CarNum",cellCarNo.StringCellValue),
// new SqlParameter("BracketNum", cellBrNo.StringCellValue));
////是否数据库中有这辆车
//if (count > 0)
//{
// continue;
//}
//SqlHelper.ExecuteNonQuery("Insert into T_Customers(Id,Name,TelPhone,BuyDate,CarNum,BracketNum) values(newid(),@Name,@TelPhone,@BuyDate,@CarNum,@BracketNum)",
// new SqlParameter("Name", cellName.StringCellValue),//易错：别写成new SqlParameter("Name", cellName)
// new SqlParameter("TelPhone", cellTel.StringCellValue),
// new SqlParameter("BuyDate", cellBuyDate.DateCellValue),
// new SqlParameter("CarNum", cellCarNo.StringCellValue),
// new SqlParameter("BracketNum", cellBrNo.StringCellValue));
}
}
MessageBox.Show("导入成功！");
}
catch (IOException ex)//抓住IOException类型的异常（文件正在被打开等异常）
{
MessageBox.Show(ex.Message);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
ImportExcel3();
MessageBox.Show("导入成功！");
}
catch (IOException ex)//抓住IOException类型的异常（文件正在被打开等异常）
{
MessageBox.Show(ex.Message);
}
}
private void ImportExcel3()
{
string filename = txtFileName.Text;//界面上敲的是什么就是什么，没有转义，转义是给编译器看的
using (FileStream stream = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(stream);
HSSFSheet sheet = workbook.GetSheetAt(0);
using (SqlConnection conn = new SqlConnection(SqlHelper.connstr))
{
conn.Open();
for (int i = 1; i <= sheet.LastRowNum; i++)//注意从第1行开始读，第一行是表头
{
HSSFRow row = sheet.GetRow(i);
HSSFCell cellName = row.GetCell(0);
HSSFCell cellTel = row.GetCell(1);
HSSFCell cellBuyDate = row.GetCell(2);
HSSFCell cellCarNo = row.GetCell(3);
HSSFCell cellBrNo = row.GetCell(4);
//正确写法
int count = (int)SqlHelper.ExecuteScalar(conn,"Select count(*) from T_Customers where CarNum=@CarNum or BracketNum=@BracketNum", new SqlParameter("CarNum", cellCarNo.StringCellValue),
new SqlParameter("BracketNum", cellBrNo.StringCellValue));
//是否数据库中有这辆车
if (count > 0)
{
continue;
}
DateTime buyDate;
if (cellBuyDate.CellType == HSSFCell.CELL_TYPE_NUMERIC)//日期类型
{
buyDate = cellBuyDate.DateCellValue;
}
else if (cellBuyDate.CellType == HSSFCell.CELL_TYPE_STRING)//2008年9月8号
{
string strBuyDate = cellBuyDate.StringCellValue;
strBuyDate = strBuyDate.Replace("号", "日");
buyDate = Convert.ToDateTime(strBuyDate);
}
else
{
throw new Exception("未知的购买日期单元格类型");
}
SqlHelper.ExecuteNonQuery(conn, "Insert into T_Customers(Id,Name,TelPhone,BuyDate,CarNum,BracketNum) values(newid(),@Name,@TelPhone,@BuyDate,@CarNum,@BracketNum)",
new SqlParameter("Name", cellName.StringCellValue),//易错：别写成new SqlParameter("Name", cellName)
new SqlParameter("TelPhone", cellTel.StringCellValue),
new SqlParameter("BuyDate", buyDate),
new SqlParameter("CarNum", cellCarNo.StringCellValue),
new SqlParameter("BracketNum", cellBrNo.StringCellValue));
}
}
}
}
private void btnExportToExcel1_Click(object sender, EventArgs e)
{
if (saveFileDlg.ShowDialog() != DialogResult.OK)
{
return;
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet();
//姓名 电话 购买日期 车号 车架号
//别用DataSet，因为数据量可能会非常大，如果用DataSet会非常占内存。
using (SqlConnection conn = new SqlConnection(SqlHelper.connstr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from T_Customers";
using (SqlDataReader reader = cmd.ExecuteReader())
{ //TODO:加表头。
int rownum = 0;//行号
while (reader.Read())
{
string name = reader.GetString(reader.GetOrdinal("Name"));
string telphone = reader.GetString(reader.GetOrdinal("TelPhone"));
DateTime buydate = reader.GetDateTime(reader.GetOrdinal("BuyDate"));
string carNum = reader.GetString(reader.GetOrdinal("CarNum"));
string brNum = reader.GetString(reader.GetOrdinal("BracketNum"));
HSSFRow row = sheet.CreateRow(rownum);//创建第rownum行
row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue(name);
row.CreateCell(1, HSSFCell.CELL_TYPE_STRING).SetCellValue(telphone);
row.CreateCell(2, HSSFCell.CELL_TYPE_STRING).SetCellValue(buydate.ToShortDateString());
row.CreateCell(3, HSSFCell.CELL_TYPE_STRING).SetCellValue(carNum);
row.CreateCell(4, HSSFCell.CELL_TYPE_STRING).SetCellValue(brNum);
rownum++;
}
}
}
}
using (FileStream stream = new FileStream(saveFileDlg.FileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(stream);
}
MessageBox.Show("保存成功！");
}
private void FormCustMgr_Load(object sender, EventArgs e)
{
this.t_CustomersTableAdapter1.Fill(this.dataSetCustomers1.T_Customers);
}
private void btnAddNew_Click(object sender, EventArgs e)
{
DataRowView drv = bsCustomers.AddNew() as DataRowView;
CallCenterClient.DAL.DataSetCustomers.T_CustomersRow custRow = drv.Row as CallCenterClient.DAL.DataSetCustomers.T_CustomersRow;
custRow.Id = Guid.NewGuid();
}
private void btnSave_Click(object sender, EventArgs e)
{
t_CustomersTableAdapter1.Update(this.dataSetCustomers1.T_Customers);
}
}
} mmmm写入Excel
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet();
HSSFRow row = sheet.CreateRow(0);
row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue("Hello");
row.CreateCell(1, HSSFCell.CELL_TYPE_NUMERIC).SetCellValue(3.14);
using (FileStream stream = new FileStream(@"c:\1.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(stream);
}
浙公网安备 33010602011771号