NOPI 基本读写
//获取cell的数据,并设置为对应的数据类型 public object GetCellValue(ICell cell) { object value = null; try { if (cell.CellType != CellType.Blank) { switch (cell.CellType) { case CellType.Numeric: // Date comes here if (DateUtil.IsCellDateFormatted(cell)) { value = cell.DateCellValue; } else { // Numeric type value = cell.NumericCellValue; } break; case CellType.Boolean: // Boolean type value = cell.BooleanCellValue; break; case CellType.Formula: value = cell.CellFormula; break; default: // String type value = cell.StringCellValue; break; } } } catch (Exception) { value = ""; } return value; } //npoi读excel方法 public void ReadFromExcelFile(string filePath) { IWorkbook wk = null; string extension = System.IO.Path.GetExtension(filePath); try { FileStream fs = File.OpenRead(filePath); if (extension.Equals(".xls")) { //把xls文件中的数据写入wk中 wk = new HSSFWorkbook(fs); } else { //把xlsx文件中的数据写入wk中 wk = new XSSFWorkbook(fs); } fs.Close(); //读取当前表数据 ISheet sheet = wk.GetSheetAt(0); IRow row; //LastRowNum 是当前表的总行数-1(注意) //LastCellNum 是当前行的总列数 for (int i = 0; i <= sheet.LastRowNum; i++) { row = sheet.GetRow(i); //读取当前行数据 if (row != null && i!=0) { DateTime value1 = DateTime.Parse( row.GetCell(0).ToString()); string value2 = row.GetCell(1).ToString(); string value3 = row.GetCell(2).ToString(); string value4 = row.GetCell(3).ToString(); string value5 = row.GetCell(4).ToString(); string value6 = row.GetCell(5).ToString(); string value7 = row.GetCell(6).ToString(); string value8 = row.GetCell(7).ToString(); Health.Model.PatientSchedule temp1 = new Health.Model.PatientSchedule(); temp1.BeginDate = value1; temp1.BeginTime = value2; temp1.EndTime = value3; temp1.Content = value4; temp1.Center = value5; temp1.DicDept = value6; temp1.Staves = value7; temp1.PlanTips = value8; temp1.ID = 0; temp1.InHosID = Datagrid1_selectedItem.InHosID; temp1.PatientID = Datagrid1_selectedItem.PatID; temp1.RegDatetime = DateTime.Now; temp1.RegUser = Health.Config.UserProfiles.UserName; temp1.State = 1; Datagrid2_ItemsSource.Add(temp1); } } } catch (Exception e) { //只在Debug模式下才输出 Console.WriteLine(e.Message); } } //根据数据类型设置不同类型的cell public static void SetCellValue(ICell cell, object obj) { if (obj.GetType() == typeof(int)) { cell.SetCellValue((int)obj); } else if (obj.GetType() == typeof(double)) { cell.SetCellValue((double)obj); } else if (obj.GetType() == typeof(IRichTextString)) { cell.SetCellValue((IRichTextString)obj); } else if (obj.GetType() == typeof(string)) { cell.SetCellValue(obj.ToString()); } else if (obj.GetType() == typeof(DateTime)) { cell.SetCellValue((DateTime)obj); } else if (obj.GetType() == typeof(bool)) { cell.SetCellValue((bool)obj); } else { cell.SetCellValue(obj.ToString()); } } //npoi写excel方法 public void WriteToExcel(string filePath) { //创建工作薄 IWorkbook wb; string extension = System.IO.Path.GetExtension(filePath); //根据指定的文件格式创建对应的类 if (extension.Equals(".xls")) { wb = new HSSFWorkbook(); } else { wb = new XSSFWorkbook(); } //创建一个表单 ISheet sheet = wb.CreateSheet("行程"); //行数和列数 int rowCount = Datagrid2_ItemsSource.Count(); IRow row; for (int i = 0; i < rowCount+1; i++) { row = sheet.CreateRow(i);//创建第i行 var cell1 = row.CreateCell(0); var cell2 = row.CreateCell(1); var cell3 = row.CreateCell(2); var cell4 = row.CreateCell(3); var cell5 = row.CreateCell(4); var cell6 = row.CreateCell(5); var cell7 = row.CreateCell(6); var cell8 = row.CreateCell(7); if (i==0) { SetCellValue(cell1, "日期"); SetCellValue(cell2, "开始时间"); SetCellValue(cell3, "结束时间"); SetCellValue(cell4, "日程内容(流程)"); SetCellValue(cell5, "执行中心"); SetCellValue(cell6, "执行科室"); SetCellValue(cell7, "相关人员"); SetCellValue(cell8, "备注"); } else { SetCellValue(cell1, Datagrid2_ItemsSource[i - 1].BeginDate.ToShortDateString()); SetCellValue(cell2, Datagrid2_ItemsSource[i - 1].BeginTime); SetCellValue(cell3, Datagrid2_ItemsSource[i - 1].EndTime); SetCellValue(cell4, Datagrid2_ItemsSource[i - 1].Content); SetCellValue(cell5, Datagrid2_ItemsSource[i - 1].Center); SetCellValue(cell6, Datagrid2_ItemsSource[i - 1].DicDept); SetCellValue(cell7, Datagrid2_ItemsSource[i - 1].Staves); SetCellValue(cell8, Datagrid2_ItemsSource[i - 1].PlanTips); } } try { FileStream fs = File.OpenWrite(filePath); wb.Write(fs);//向打开的这个Excel文件中写入表单并保存。 fs.Close(); } catch (Exception e) { Health.Toolkit.MessageBox.Show("保存失败:"+e.Message); } }
时期格式设置
ICellStyle style0 = wb.CreateCellStyle();
IDataFormat dataformat = wb.CreateDataFormat();
style0.DataFormat = dataformat.GetFormat("yyyy年MM月dd日 HH:mm");