关于NPOI导入导出
http://www.360doc.com/content/14/0110/16/432969_344152497.shtml
NPOI汇入Excel仅支持2007版本以内：
[HttpPost]
public ActionResult Upload(HttpPostedFileBase file)
{
if (Request.Files["file"].ContentLength > 0)
{
// 获取文件拓展名
string extension = Path.GetExtension(file.FileName);
if (extension == ".xls" || extension == ".xlsx")
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
// 验证文件是否存在,如果存在就删除
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
// 将文件上传到服务器
Request.Files["file"].SaveAs(fileLocation);
HSSFWorkbook excel;
using (FileStream files = new FileStream(fileLocation, FileMode.Open, FileAccess.Read))
{
excel = new HSSFWorkbook(files);
//
ISheet sheet = excel.GetSheetAt(0);
// 使用名称获取
ISheet sheetb = excel.GetSheet("Name");
for (int row = 0; row <= sheet.LastRowNum; row++)
{
string name = null;
int age = 0;
if (sheet.GetRow(row) != null)
{
name = sheet.GetRow(row).GetCell(0).StringCellValue;
age = Convert.ToInt32(sheet.GetRow(row).GetCell(1).NumericCellValue);
}
else
{
continue;
}
Users users = new Users();
users.Name = name;
users.Age = age;
db.users.Add(users);
}
db.SaveChanges();
}
}
}
return this.RedirectToAction("Index");
}
NPOI汇入Excel支持2007版本以上：（注意需要引入NPOI.dll,NPOI.OOXML.dll,NPOI.OpenXml4Net.dll,参照文件NPOI2.2.0.zip）
public ActionResult Upload(HttpPostedFileBase file)
{
if (Request.Files["file"].ContentLength > 0)
{
// 获取文件拓展名
string extension = Path.GetExtension(file.FileName);
if (extension == ".xls" || extension == ".xlsx")
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
// 验证文件是否存在,如果存在就删除
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
// 将文件上传到服务器
Request.Files["file"].SaveAs(fileLocation);
//HSSFWorkbook excel;
using (FileStream files = new FileStream(fileLocation, FileMode.Open, FileAccess.Read))
{
NPOI.SS.UserModel.IWorkbook userModel;
try
{
userModel = new NPOI.XSSF.UserModel.XSSFWorkbook(files); // 2007 格式
}
catch (Exception)
{
userModel = new NPOI.HSSF.UserModel.HSSFWorkbook(files); // 2003 格式
}
finally
{
files.Close();
}
//excel = new HSSFWorkbook(files);
// ISheet sheet = userModel.GetSheetAt(0);
// 使用名称获取
ISheet sheetb = userModel.GetSheet("Name");
for (int row = 0; row <= sheet.LastRowNum; row++)
{
string name = null;
int age = 0;
if (sheet.GetRow(row) != null)
{
name = sheet.GetRow(row).GetCell(0).StringCellValue;
age = Convert.ToInt32(sheet.GetRow(row).GetCell(1).NumericCellValue);
//for (int i = 0; i <= sheet.GetRow(row).LastCellNum; i++)
//{
// name = sheet.GetRow(row).GetCell(i).StringCellValue;
// age = Convert.ToInt32(sheet.GetRow(row).GetCell(i+1).NumericCellValue);
// //sheet.GetRow(row).GetCell(i).NumericCellValue; // 數值
// //sheet.GetRow(row).GetCell(i).StringCellValue; // 字串
// //sheet.GetRow(row).GetCell(i).BooleanCellValue; // 布林
// //sheet.GetRow(row).GetCell(i).DateCellValue; // 日期
//}
}
else
{
continue;
}
// 创建对象存入DB
Users users = new Users();
users.Name = name;
users.Age = age;
db.users.Add(users);
}
db.SaveChanges();
}
}
}
===================================================================================
NPOI汇出Excel：
/// <summary>
/// 将页面资料汇入指定 Excel 的多个 Sheet
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strFileName"></param>
/// <param name="lstNew"></param>
private void ExpMultiSheetToExcel<T>(string strFileName, List<T> lstNew)
{
try
{
if (strFileName != null && lstNew != null)
{
// 創建Excel文件的對象
HSSFWorkbook workbook = new HSSFWorkbook();
PropertyInfo[] newDataDest = lstNew[0].GetType().GetProperties();
int i = 1;
foreach (PropertyInfo newProperty in newDataDest)
{
dynamic newDic1 = newProperty.GetValue(lstNew[0]);
int newDataIndex1 = 0;
foreach (var newKey in newDic1.Keys)
{
if (newKey != null)
{
// 添加一個sheet
ISheet sheetEmp = workbook.CreateSheet(newKey);
dynamic lstNewDate = newDic1[newKey];
//將數據寫入sheetEmp的各個行
for (int intIndex = 0; intIndex < lstNewDate.Count; intIndex++)
{
// JKBaseInfo ALL Property
PropertyInfo[] propertiesDest = lstNewDate[intIndex].GetType().GetProperties();
IRow row0 = sheetEmp.CreateRow(0);
IRow rowtemp = sheetEmp.CreateRow(intIndex + 1);
int columnIndex = 0;
foreach (PropertyInfo property in propertiesDest)
{
Dictionary<string, string> dic = property.GetValue(lstNewDate[intIndex]) as Dictionary<string, string>;
foreach (var key in dic.Keys)
{
row0.CreateCell(columnIndex).SetCellValue(key);
// Value
if (row0.GetCell(columnIndex).ToString().Contains(key)) {
string obj = dic[key];
rowtemp.CreateCell(columnIndex).SetCellValue(obj);
}
}
columnIndex++;
}
}
newDataIndex1++;
}
}
}
// 寫入到客戶端
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
FileStream fs = new FileStream(strFileName + ".xls", FileMode.OpenOrCreate);
BinaryWriter w = new BinaryWriter(fs);
w.Write(ms.ToArray());
fs.Close();
workbook = null;
ms.Close();
ms.Dispose();
}
}
catch (Exception ex) { throw ex; } }
/// <summary>
/// 匯出个人健康档案數據到Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void toExcel()
{
try
{
// 創建Excel文件的對象
HSSFWorkbook workbook = new HSSFWorkbook();
// 添加一個sheet
ISheet sheetEmp = workbook.CreateSheet("sheet1");
// 獲得List數據 List<BaseInfo> lstInfo = lstBaseInfo;
// 給sheet添加第一行的頭部標題 IRow row1 = sheetEmp.CreateRow(0); row1.CreateCell(0).SetCellValue("与户主关系"); row1.CreateCell(1).SetCellValue("档案状态"); row1.CreateCell(2).SetCellValue("姓名"); row1.CreateCell(3).SetCellValue("性别"); row1.CreateCell(4).SetCellValue("证件编号"); row1.CreateCell(5).SetCellValue("出生日期"); row1.CreateCell(6).SetCellValue("本人电话"); row1.CreateCell(7).SetCellValue("工作单位"); row1.CreateCell(8).SetCellValue("联系人电话"); row1.CreateCell(9).SetCellValue("联系人姓名"); row1.CreateCell(10).SetCellValue("常住类型"); row1.CreateCell(11).SetCellValue("民族"); row1.CreateCell(12).SetCellValue("血型"); row1.CreateCell(13).SetCellValue("RH"); row1.CreateCell(14).SetCellValue("职业"); row1.CreateCell(15).SetCellValue("文化程度"); row1.CreateCell(16).SetCellValue("劳动程度"); row1.CreateCell(17).SetCellValue("婚姻状况");
row1.CreateCell(18).SetCellValue("医疗费用支付方式"); row1.CreateCell(19).SetCellValue("医疗保险号"); row1.CreateCell(20).SetCellValue("新农合号"); row1.CreateCell(21).SetCellValue("居住地址"); row1.CreateCell(22).SetCellValue("所属片区"); row1.CreateCell(23).SetCellValue("档案类别"); row1.CreateCell(24).SetCellValue("药物过敏史"); row1.CreateCell(25).SetCellValue("既往史"); row1.CreateCell(26).SetCellValue("家族史"); row1.CreateCell(27).SetCellValue("暴露史"); row1.CreateCell(28).SetCellValue("遗传病史"); row1.CreateCell(29).SetCellValue("残疾状况"); row1.CreateCell(30).SetCellValue("生活环境"); row1.CreateCell(31).SetCellValue("调查时间"); row1.CreateCell(32).SetCellValue("录入时间"); row1.CreateCell(33).SetCellValue("录入人"); row1.CreateCell(34).SetCellValue("最近更新时间"); row1.CreateCell(35).SetCellValue("最近修改人"); row1.CreateCell(36).SetCellValue("当前所属机构");
//將數據寫入sheetEmp的各個行 for (int i = 0; i < lstInfo.Count; i++) { IRow rowtemp = sheetEmp.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(lstInfo[i].huzhu); rowtemp.CreateCell(1).SetCellValue(lstInfo[i].daState); rowtemp.CreateCell(2).SetCellValue(lstInfo[i].Name); rowtemp.CreateCell(3).SetCellValue(lstInfo[i].Sex); rowtemp.CreateCell(4).SetCellValue(lstInfo[i].zjNum); rowtemp.CreateCell(5).SetCellValue(lstInfo[i].Birthday); rowtemp.CreateCell(6).SetCellValue(lstInfo[i].MobileTel); rowtemp.CreateCell(7).SetCellValue(lstInfo[i].WorkAddr); rowtemp.CreateCell(8).SetCellValue(lstInfo[i].HomeTel); rowtemp.CreateCell(9).SetCellValue(lstInfo[i].RelativeName); rowtemp.CreateCell(10).SetCellValue(lstInfo[i].changzhuType); rowtemp.CreateCell(11).SetCellValue(lstInfo[i].minzu); rowtemp.CreateCell(12).SetCellValue(lstInfo[i].xuexing); rowtemp.CreateCell(13).SetCellValue(lstInfo[i].RH); rowtemp.CreateCell(14).SetCellValue(lstInfo[i].job); rowtemp.CreateCell(15).SetCellValue(lstInfo[i].wenhuachengdu); rowtemp.CreateCell(16).SetCellValue(lstInfo[i].laodongchengdu); rowtemp.CreateCell(17).SetCellValue(lstInfo[i].marry);
rowtemp.CreateCell(18).SetCellValue(lstInfo[i].yiliaofeiyong); rowtemp.CreateCell(19).SetCellValue(lstInfo[i].yiliaobaoxianhao); rowtemp.CreateCell(20).SetCellValue(lstInfo[i].xinnonghehao); rowtemp.CreateCell(21).SetCellValue(lstInfo[i].juzhudizhi); rowtemp.CreateCell(22).SetCellValue(lstInfo[i].suoshupianqu); rowtemp.CreateCell(23).SetCellValue(lstInfo[i].danganleibie); rowtemp.CreateCell(24).SetCellValue(lstInfo[i].yaowuguominshi); rowtemp.CreateCell(25).SetCellValue("疾病：" + lstInfo[i].jibing + ";手术：" + lstInfo[i].shoushu + ";外伤：" + lstInfo[i].waishang + ";输血：" + lstInfo[i].shuxie); rowtemp.CreateCell(26).SetCellValue(lstInfo[i].jiazushi); rowtemp.CreateCell(27).SetCellValue(lstInfo[i].baolushi); rowtemp.CreateCell(28).SetCellValue(lstInfo[i].yichuanbingshi); rowtemp.CreateCell(29).SetCellValue(lstInfo[i].jibingzhuangkuang); rowtemp.CreateCell(30).SetCellValue("厨房排风设施：" + (lstInfo[i].chufang == null ? "无" : lstInfo[i].chufang) + "; 燃料类型：" + (lstInfo[i].ranliao == null ? "无" : lstInfo[i].ranliao) + ";饮水：" + (lstInfo[i].yinshui == null ? "无" : lstInfo[i].yinshui) + ";厕所：" + (lstInfo[i].cesuo == null ? "无" : lstInfo[i].cesuo) + ";禽畜栏：" + (lstInfo[i].qinchu == null ? "无" : lstInfo[i].qinchu)); rowtemp.CreateCell(31).SetCellValue(lstInfo[i].diaochashijian); rowtemp.CreateCell(32).SetCellValue(lstInfo[i].lurushijian); rowtemp.CreateCell(33).SetCellValue(lstInfo[i].lururen); rowtemp.CreateCell(34).SetCellValue(lstInfo[i].zuijingengxinshijian); rowtemp.CreateCell(35).SetCellValue(lstInfo[i].zuijinxiugairen); rowtemp.CreateCell(36).SetCellValue(lstInfo[i].dqsuoshujigou); }
// 寫入到客戶端 MemoryStream ms = new MemoryStream(); workbook.Write(ms); FileStream fs = new FileStream("个人基本信息.xls", FileMode.OpenOrCreate); BinaryWriter w = new BinaryWriter(fs); w.Write(ms.ToArray()); fs.Close(); workbook = null; ms.Close(); ms.Dispose(); } catch (Exception ex) { throw ex; } }
