根据网上的资料，学习了一下NPOI操作Excel的基本方法：
1、导出至Excel：标题行合并居中、设置列宽、写入列标题及数据。
public class ExportToExcel : IHttpHandler //一般处理程序ExportToExcel.ashx
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "application/x-excel";
string fileName = HttpUtility.UrlEncode("备份.xls");
context.Response.AddHeader("Content-Disposition", "attachment; fileName=" + fileName);
HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个xls；
HSSFSheet sheet = workbook.CreateSheet("用户信息"); //创建一个Sheet页
sheet.SetColumnWidth(3, 50 * 256); //设置列宽，50个字符宽度。宽度参数为1/256，故乘以256
string connectString = @"server=.\sqlexpress;database=BookShop; uid=sa; pwd=123456";
SqlConnection connection = new SqlConnection(connectString);
connection.Open();
/******************写入标题行，合并居中*********************/
HSSFRow row = sheet.CreateRow(0);
HSSFCell cell = row.CreateCell(0);
cell.SetCellValue("用户信息");
HSSFCellStyle style = workbook.CreateCellStyle();
style.Alignment = HSSFCellStyle.ALIGN_CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeight = 20 * 20;
style.SetFont(font);
cell.CellStyle = style;
sheet.AddMergedRegion(new Region(0,0,0,4));
/******************写入列标题*********************/
int rowsNum = 1; //行号
row = sheet.CreateRow(rowsNum);
row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue("用户名");
row.CreateCell(1, HSSFCell.CELL_TYPE_STRING).SetCellValue("密码");
row.CreateCell(2, HSSFCell.CELL_TYPE_STRING).SetCellValue("姓名");
row.CreateCell(3, HSSFCell.CELL_TYPE_STRING).SetCellValue("电子邮件");
row.CreateCell(4, HSSFCell.CELL_TYPE_STRING).SetCellValue("用户组编号");
using (IDbCommand cmd = connection.CreateCommand()) ;
{
cmd.CommandText = "select * from Users";
using (IDataReader reader = cmd.ExecuteReader())
{
rowsNum = 2; //行号
while (reader.Read())
{
//根据字段名找出ID
string LoginId = reader.GetString(reader.GetOrdinal("LoginId"));
string LoginPwd = reader.GetString(reader.GetOrdinal("LoginPwd"));
string Name = reader.GetString(reader.GetOrdinal("Name"));
string Mail = reader.GetString(reader.GetOrdinal("Mail"));
int userRoleId= reader.GetInt32(reader.GetOrdinal("UserRoleId"));
/******************写入字段值*********************/
row = sheet.CreateRow(rowsNum);
row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue(LoginId);
row.CreateCell(1, HSSFCell.CELL_TYPE_STRING).SetCellValue(LoginPwd);
row.CreateCell(2, HSSFCell.CELL_TYPE_STRING).SetCellValue(Name);
row.CreateCell(3, HSSFCell.CELL_TYPE_STRING).SetCellValue(Mail);
row.CreateCell(4, HSSFCell.CELL_TYPE_STRING).SetCellValue(userRoleId); //整型数据
rowsNum++;
}
}
}
workbook.Write(context.Response.OutputStream); //输出到流中
}
public bool IsReusable
{
get
{
return false;
}
}
}
在其他页面调用：
<a href="ExportToExcel.ashx">导出到Excel</a>
2、读取Excel表格数据至DataTable中
2.1 判断文件类型
<script type="text/javascript">
function checkType()
{
//得到上传文件的值
var fileName=document.getElementById("FileUpLoad1").value;
//返回String对象中子字符串最后出现的位置.
var seat=fileName.lastIndexOf(".");
//返回位于String对象中指定位置的子字符串并转换为小写.
var extension=fileName.substring(seat).toLowerCase();
//判断允许上传的文件格式
//var allowed=[".jpg",".gif",".png",".bmp",".jpeg"];
var allowed=[".xls"];
for(var i=0;i<allowed.length;i++){
if(!(allowed[i]!=extension)){
return true;
}
}
alert("不支持"+extension+"格式");
return false;
}
</script>
2.2 读取Excle表格，导入至DataTable
if (FileUpload1.HasFile)
{
//string fileName = Server.MapPath("~/Export/") + FileUpload1.FileName;
//FileUpload1.PostedFile.SaveAs(fileName);
try
{
//DataTable dt = ExcelHelper.Import(fileName, 0, 1); //Excel表格第0行为表头、第1行为列名。
DataTable dt = ExcelHelper.Import(FileUpload1.FileContent, 0, 1);
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (Exception ex)
{
JScript.Alert(ex.Message);
JScript.Alert("Excel表格格式有误！");
}
}