Excel表的导入导出运用三层架构的方法
UI层 BLL层 DAL层
核心代码如下:
//信息的导入
public void ReadExcel(string path)
{
using (FileStream fsRead = new FileStream(path, FileMode.Open, FileAccess.Read))
{
List<Information> list = new List<Information>();
//工作表
IWorkbook WorkBook = WorkbookFactory.Create(fsRead);
//获取页
ISheet sheet = WorkBook.GetSheetAt(0);
//遍历第一页中的行
for (int i = 0; i <= sheet.LastRowNum; i++)
{
Information information = new Information();
IRow row = sheet.GetRow(i);
//读表格的时候要特别注意判断excel表格的类型,类型不对就会出错
if (row.GetCell(0).CellType == CellType.String)
{
System.Globalization.DateTimeFormatInfo dtfi = new System.Globalization.CultureInfo("en-US", false).DateTimeFormat;
dtfi.ShortTimePattern = "t";
information.Time = DateTime.Parse(row.GetCell(0).StringCellValue, dtfi);
//information.Time = DateTime.Parse(row.GetCell(0).StringCellValue);
//关于DateTime.Parse 的使用將日期和時間的指定字串表示轉換為其對等的 DateTime。受 .NET Compact Framework 支援。上面获取的时间类型为字符串类型
}
else if (row.GetCell(0).CellType == CellType.Numeric)
{
information.Time = Convert.ToDateTime(row.GetCell(0).NumericCellValue);
}
else
{
information.Time = DateTime.Parse(row.GetCell(0).StringCellValue);
}
switch (row.GetCell(1).CellType)
{
case CellType.String: information.Sd = Convert.ToInt32(row.GetCell(1).StringCellValue);
break;
case CellType.Numeric: information.Sd = Convert.ToInt32(row.GetCell(1).NumericCellValue);
break;
case CellType.Formula: information.Sd = Convert.ToInt32(row.GetCell(1).CellFormula);
break;
case CellType.Boolean: information.Sd = Convert.ToInt32(row.GetCell(1).BooleanCellValue);
break;
case CellType.Error: information.Sd = Convert.ToInt32(row.GetCell(1).ErrorCellValue);
break;
case CellType.Blank: information.Sd = 0;
break;
default:information.Sd = Convert.ToInt32(row.GetCell(1).StringCellValue);
break;
}
//information.Time = DateTime.Parse(row.GetCell(0).StringCellValue);
if (row.GetCell(3).CellType == CellType.String)
{
information.Num = Convert.ToInt32(row.GetCell(3).StringCellValue);
}
else if (row.GetCell(3).CellType == CellType.Numeric)
{
information.Num = Convert.ToInt32(row.GetCell(3).NumericCellValue);
}
//information.Sd = Convert.ToInt32(row.GetCell(1).NumericCellValue);
if (row.GetCell(2).CellType == CellType.String)
{
information.Tem = Convert.ToInt32(row.GetCell(2).StringCellValue); ;
}
else if (row.GetCell(2).CellType == CellType.Numeric)
{
information.Tem = Convert.ToInt32(row.GetCell(2).NumericCellValue);
}
// string myDateTimeValue =row.GetCell(0).StringCellValue;
//DateTime myDateTime = DateTime.Parse(myDateTimeValue);
// information.Time = myDateTime;
information.Sname = "白粉虱";
information.DelFlag=0;
list.Add(information);//将数据插入到数据库中
}//end for
memdal.AddInfo(list);
}//end using
}
//信息的导出
public void WriteExcel(string path)
{
//文件流
using (FileStream fswrite = new FileStream(path, FileMode.Create, FileAccess.Write))
{
//创建文件
XSSFWorkbook work = new XSSFWorkbook();
//创建页
ISheet sheet = work.CreateSheet();
//获取信息
List<Information> list = memdal.GetAllMemberInfoByDelFlag(0);
for (int i = 0; i < list.Count; i++)
{
//创建行
IRow row = sheet.CreateRow(i);
row.CreateCell(0, CellType.String).SetCellValue(list[i].Sname );
row.CreateCell(1, CellType.Numeric).SetCellValue(list[i].Tem);
row.CreateCell(2, CellType.Numeric).SetCellValue(list[i].Sd);
row.CreateCell(3, CellType.String).SetCellValue(Convert.ToString(list[i].Time));
//这里感觉EXCEL表中单元格不是string就是numeric,开始时间类型不明白,忙活了几个小时,最后把时间类型转变成合适的字符串输出即可。
row.CreateCell(4,CellType.Numeric).SetCellValue(list[i].Num);
}
//写入文件中
work.Write(fswrite);
}
}
应该补一下属性和方法的知识。晚上来更