NPOI新建和读取EXCEL
//基本NPOI 1.2.5.0 static void Main(string[] args) { string path = string.Format("E:\\export{0}.xls", DateTime.Now.ToString("yyyyMMddhhmmss")); WriteAExcel(path); ReadAExcel(path); Console.ReadKey(); }
/// <summary> /// 创建测试 /// </summary> /// <param name="path">路径</param> static void WriteAExcel(string path) { //创建工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个名称为"排班表"的表 ISheet sheet = workbook.CreateSheet("排班表"); int rowCount = 0; int colCount = 6; //创建一行, 此行为标题行 IRow title = sheet.CreateRow(rowCount); title.CreateCell(0).SetCellValue(string.Format("{0}({1})", "消化内科", "珠海市人民医院")); //合并单元格 CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount, 0, colCount - 1); sheet.AddMergedRegion(cellRangeAddress); rowCount++; //创建一行, 空行 sheet.CreateRow(rowCount); rowCount++; //创建一行，此行为第二行 IRow headerRow = sheet.CreateRow(rowCount); rowCount++; //固定区域, 用于header sheet.CreateFreezePane(0, 1); string[] headerArray = new[] { "医生", "日期", "时间", "预约数", "挂号费", "状态" }; //表头行 for (int i = 0; i < headerArray.Length; i++) { headerRow.CreateCell(i).SetCellValue(headerArray[i]); } List<MyDataItem> dataList = new List<MyDataItem>(); #region 测试数据 dataList.Add(new MyDataItem() { ID = 1, DoctorName = "张某", Date = DateTime.Now, Time = "上午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 2, DoctorName = "李某", Date = DateTime.Now, Time = "下午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 3, DoctorName = "赵某", Date = DateTime.Now, Time = "中午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 4, DoctorName = "杨某", Date = DateTime.Now, Time = "上午", Place = 3, Fee = 10, Status = "停诊" }); dataList.Add(new MyDataItem() { ID = 5, DoctorName = "黎某", Date = DateTime.Now, Time = "中午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 6, DoctorName = "张某", Date = DateTime.Now, Time = "上午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 7, DoctorName = "李某", Date = DateTime.Now, Time = "下午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 8, DoctorName = "赵某", Date = DateTime.Now, Time = "中午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 9, DoctorName = "杨某", Date = DateTime.Now, Time = "上午", Place = 3, Fee = 10, Status = "停诊" }); dataList.Add(new MyDataItem() { ID = 10, DoctorName = "黎某", Date = DateTime.Now, Time = "中午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 11, DoctorName = "张某", Date = DateTime.Now, Time = "上午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 12, DoctorName = "李某", Date = DateTime.Now, Time = "下午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 13, DoctorName = "赵某", Date = DateTime.Now, Time = "中午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 14, DoctorName = "杨某", Date = DateTime.Now, Time = "上午", Place = 3, Fee = 10, Status = "停诊" }); dataList.Add(new MyDataItem() { ID = 15, DoctorName = "黎某", Date = DateTime.Now, Time = "中午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 16, DoctorName = "张某", Date = DateTime.Now, Time = "上午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 17, DoctorName = "李某", Date = DateTime.Now, Time = "下午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 18, DoctorName = "赵某", Date = DateTime.Now, Time = "中午", Place = 3, Fee = 10, Status = "排班" }); dataList.Add(new MyDataItem() { ID = 19, DoctorName = "杨某", Date = DateTime.Now, Time = "上午", Place = 3, Fee = 10, Status = "停诊" }); dataList.Add(new MyDataItem() { ID = 20, DoctorName = "黎某", Date = DateTime.Now, Time = "中午", Place = 3, Fee = 10, Status = "排班" }); #endregion //添加下拉选项(序列) AddDropdownList1(sheet, rowCount); //添加下拉选项(指定数据) AddDropdownList2(workbook, sheet, rowCount); //插入数据 for (int i = 0; i < dataList.Count; i++) { MyDataItem item = dataList[i]; IRow dataRow = sheet.CreateRow(rowCount); dataRow.CreateCell(0).SetCellValue(string.Format("{0}({1})", item.DoctorName, item.ID)); dataRow.CreateCell(1).SetCellValue(item.Date.ToString("yyyy/MM/dd")); dataRow.CreateCell(2).SetCellValue(item.Time); dataRow.CreateCell(3).SetCellValue(item.Place); dataRow.CreateCell(4).SetCellValue(item.Fee.ToString("N2")); dataRow.CreateCell(5);//.SetCellValue(item.Status); rowCount++; } //写入文件 using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); using (FileStream fs = new FileStream(path, FileMode.Create)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Count()); } } }
//读测试 static void ReadAExcel(string path) { using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = new HSSFWorkbook(file); ISheet sheet = workbook.GetSheet("排班表"); IRow headerRow = sheet.GetRow(2); //一行最后一个方格的编号 即总的列数 int cellCount = headerRow.LastCellNum; //有多少列 int rowCount = sheet.LastRowNum; //读 for (int i = 2; i < rowCount; i++) { IRow row = sheet.GetRow(i); for (int j = row.FirstCellNum; j < cellCount; j++) { Console.Write(" " + row.GetCell(j).ToString()); } Console.WriteLine(); } } }
/// <summary> /// 添加下拉框(序列) /// </summary> /// <param name="sheet"></param> /// <param name="start"></param> static void AddDropdownList1(ISheet sheet, int start) { CellRangeAddressList regions = new CellRangeAddressList(start, 65535, 5, 5); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "就诊", "停诊" }); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(dataValidate); } /// <summary> /// 添加下拉框(单元格) /// </summary> /// <param name="workbook"></param> /// <param name="sheet"></param> /// <param name="start"></param> static void AddDropdownList2(HSSFWorkbook workbook, ISheet sheet, int start) { ISheet sheet2 = workbook.CreateSheet("a"); sheet2.CreateRow(0).CreateCell(0).SetCellValue("上午"); sheet2.CreateRow(1).CreateCell(0).SetCellValue("中午"); sheet2.CreateRow(2).CreateCell(0).SetCellValue("下午"); sheet2.CreateRow(3).CreateCell(0).SetCellValue("晚上"); IName range = workbook.CreateName(); range.RefersToFormula = "a!$A$1:$A$4"; range.NameName = "timeDic"; CellRangeAddressList regions = new CellRangeAddressList(start, 65535, 2, 2); DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("timeDic"); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); //添加约束警告 dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。"); sheet.AddValidationData(dataValidate); }
/// <summary> /// 测试数据类型 /// </summary> class MyDataItem { public int ID { get; set; } public string DoctorName { get; set; } public DateTime Date { get; set; } public string Time { get; set; } public int Place { get; set; } public decimal Fee { get; set; } public string Status { get; set; } }
备忘:
//2014/10/08 //加粗,15字 IFont Bold15Font = workbook.CreateFont(); Bold15Font.Boldweight = (short)FontBoldWeight.BOLD; Bold15Font.FontHeightInPoints = 15; //黄色底,横向居中,纵向居中,加粗,11字, 边框(单个) ICellStyle YellowCenterBold11Cell = workbook.CreateCellStyle(); YellowCenterBold11Cell.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index; YellowCenterBold11Cell.FillPattern = FillPatternType.SOLID_FOREGROUND; YellowCenterBold11Cell.Alignment = HorizontalAlignment.CENTER; YellowCenterBold11Cell.VerticalAlignment = VerticalAlignment.CENTER; YellowCenterBold11Cell.SetFont(Bold11Font); YellowCenterBold11Cell.BorderBottom = BorderStyle.THIN; YellowCenterBold11Cell.BorderLeft = BorderStyle.THIN; YellowCenterBold11Cell.BorderRight = BorderStyle.THIN; YellowCenterBold11Cell.BorderTop = BorderStyle.THIN; //给合并单元格加边框 CellRangeAddress cellRangeAddress0 = new CellRangeAddress(0, 0, 0, 3); sheet.AddMergedRegion(cellRangeAddress0); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellRangeAddress0, BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index); //单元格宽 sheet.SetColumnWidth(0, 10 * 256); //行高 row0.HeightInPoints = Height25;
//2014/10/09
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); IRow row1 = sheet1.CreateRow(0); ICell cel1 = row1.CreateCell(0); ICell cel2 = row1.CreateCell(1); ICellStyle unlocked = hssfworkbook.CreateCellStyle(); unlocked.IsLocked = false; ICellStyle locked = hssfworkbook.CreateCellStyle(); locked.IsLocked = true;//确定当前单元格被设置保护 cel1.SetCellValue("没被锁定"); cel1.CellStyle = unlocked; cel2.SetCellValue("被锁定"); cel2.CellStyle = locked; sheet1.ProtectSheet("password");//设置密码保护
sheet1.AutoSizeColumn(i);//自动宽度(不支持中文)
自动宽度( 支持中文)
/// <summary> /// 自动宽度支持中文 /// </summary> public static void AutoSizeColumnExtension(this ISheet sheet, int maxColumn) { for (int i = 0; i < maxColumn; i++) { sheet.AutoSizeColumn(i); } //获取当前列的宽度，然后对比本列的长度，取最大值 for (int columnNum = 0; columnNum < maxColumn; columnNum++) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256; for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++) { IRow currentRow; //当前行未被使用过 if (sheet.GetRow(rowNum) == null) { currentRow = sheet.CreateRow(rowNum); } else { currentRow = sheet.GetRow(rowNum); } if (currentRow.GetCell(columnNum) != null) { ICell currentCell = currentRow.GetCell(columnNum); int length = Encoding.Default.GetBytes(currentCell.ToString()).Length; if (columnWidth < length) { columnWidth = length; } } } sheet.SetColumnWidth(columnNum, (columnWidth > 255 ? 255 : columnWidth) * 256); } }
更多可以查看官方手册:http://tonyqus.sinaapp.com/
