Npoi to Excel 常用构造与读取
之前记录过如何判断 excel,word 的笔记,这里用到了就顺便写一下怎么构造与读取 excel ,毕竟在工作中用到的还是挺多的,导出文件,下载模板,读取文件都时挺常用的。话不多说,直接开始。
1. 构造 Excel 文件,从下载Npoi 的包开始,这个时候主要就是 Sheet表,Row行,Cell 列 ,以及 Style 等相关参数进行设置
//创建对象
HSSFWorkbook workbook = new HSSFWorkbook(); //创建电子表 ISheet sheet = workbook.CreateSheet(yearPeriodList[ii].YearPeriodName + ii);//创建首行 IRow rowA = sheet.CreateRow(0); //创建首行首列 ICell cell = rowA.CreateCell(0); //设置单元格样式 cell.CellStyle = ExcelHelper.SetFontAlign(workbook, "微软雅黑", 14); //设置单元格值 cell.SetCellValue(yearPeriodList[ii].YearPeriodName + ii); //合并单元格 首行 末行 首列 末列 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
#region 写入数据到客户端,供下载 MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); DateTime dt = DateTime.Now; string dateTime = dt.ToString("yyyyMMddHHmmssfff"); string fileName = "文档名称" + dateTime + ".xls"; return File(ms, "application/vnd.ms-excel", fileName); #endregion
2. 设置内容,样式等。有时候需要对单元格的字体,背景,以及下拉选项进行赋值
public class ExcelHelper {
//返回默认标题单元格样式 public static ICellStyle GetTitleCellStyle(HSSFWorkbook workbook) { ICellStyle style = workbook.CreateCellStyle(); IFont font = workbook.CreateFont(); //创建一个字体样式对象 font.FontName = "微软雅黑"; //和excel里面的字体对应 font.FontHeightInPoints = 14; //字体大小 style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //上框线 style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //下框线 style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //左框线 style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //右框线 style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 style.SetFont(font); return style; } /// <summary> /// 设置字体颜色 /// </summary> /// <param name="style"></param> /// <param name="color"></param> public static ICellStyle SetFontColor(HSSFWorkbook workbook,ICellStyle style,short color) { //创建一个字体颜色 IFont font = workbook.CreateFont(); //设置字体颜色 font.Color = color; font.FontHeightInPoints = 12; style.BorderTop = BorderStyle.Thin; style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.SetFont(font); return style; } //设置单元格对齐方式,字体 public static ICellStyle SetFontAlign(HSSFWorkbook workbook, string fontStyle = "Arial", short fontSize = 10, HorizontalAlignment align0 = HorizontalAlignment.Center, VerticalAlignment align1 = VerticalAlignment.Center) { ICellStyle style = workbook.CreateCellStyle(); IFont font = workbook.CreateFont(); //创建一个字体样式对象 font.FontName = fontStyle; //和excel里面的字体对应 font.FontHeightInPoints = fontSize; style.Alignment = align0; style.VerticalAlignment = align1; style.SetFont(font); style.IsLocked = true; return style; } //绑定下拉列表值--数据项较少 public static void SetCellDropdownList(ISheet sheet, int firstrow, int lastrow, int firstcol, int lastcol, string[] vals,bool bol = false) { if (bol) { for (int i = firstrow; i < lastrow; i++) { for (int j = firstcol; j < lastcol; j++) { sheet.CreateRow(i).CreateCell(j).CellStyle.IsLocked = false; } } } //设置生成下拉框的行和列 var cellRegions = new CellRangeAddressList(firstrow, lastrow, firstcol, lastcol); //设置 下拉框内容 DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(vals); //绑定下拉框和作用区域,并设置错误提示信息 HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。"); dataValidate.ShowPromptBox = true; sheet.AddValidationData(dataValidate); } //绑定下拉列表值--数据项较多 Edit by fanzhang in 20200920 public static void SetCellDropdownListMul(HSSFWorkbook workbook, ISheet sheet, string name, int firstrow, int lastrow, int firstcol, int lastcol, string[] vals, int sheetindex = 1) { //先创建一个Sheet专门用于存储下拉项的值 ISheet sheet2 = workbook.CreateSheet(name); //隐藏 workbook.SetSheetHidden(sheetindex, true); int index = 0; foreach (var item in vals) { sheet2.CreateRow(index).CreateCell(0).SetCellValue(item); index++; } //创建的下拉项的区域: var rangeName = name + "Range"; IName range = workbook.CreateName(); range.RefersToFormula = name + "!$A$1:$A$" + index; range.NameName = rangeName; CellRangeAddressList regions = new CellRangeAddressList(firstrow, lastrow, firstcol, lastcol); //设置约束 DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(rangeName); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。"); dataValidate.ShowPromptBox = true; sheet.AddValidationData(dataValidate); } }
设置单元格背景颜色,换行
//创建换行内容(一般是首行备注) var remark = new StringBuilder(); remark.Append("1.星期、课节、班级名称、科目名称不可编辑。\n"); remark.Append("2.单双周和走班多个科目使用英文“/”符分割。\n"); remark.Append("3.科目名称必须填写正确并保持完整,如 政治/地理 )。\n"); remark.Append("4.教师名称必须填写正确。\n"); remark.Append("5.禁止修改表结构 \n"); ISheet sheet = workbook.CreateSheet("我是sheet名称");//创建首行 IRow rowA = sheet.CreateRow(0); rowA.Height = 10 * 200; //创建首行首列 ICell cell = rowA.CreateCell(0); //设置单元格样式 cell.CellStyle = ExcelHelper.SetFontAlign(workbook, "微软雅黑", 14, HSSFColor.Yellow.Index, true, HorizontalAlignment.Left); //设置单元格值 cell.SetCellValue(remark.ToStr());
//设置单元格对齐方式,字体,背景颜色,多行 public static ICellStyle SetFontAlign(HSSFWorkbook workbook, string fontStyle = "Arial", short fontSize = 10, short color=HSSFColor.White.Index,bool bol=false) { ICellStyle style = workbook.CreateCellStyle(); IFont font = workbook.CreateFont(); //创建一个字体样式对象 font.FontName = fontStyle; //和excel里面的字体对应 font.FontHeightInPoints = 12; font.FontHeightInPoints = fontSize; style.SetFont(font); //设置背景颜色 style.FillForegroundColor = color; style.FillPattern = FillPattern.SolidForeground; //设置是否多行 style.WrapText = bol; return style; }
对于excel 的构造下载,以上可以满足常用需求,当然还有就是如何读取excel 数据, 逻辑是先将上传的文件保存到本地,然后对文件进行操作,当然也可以将文件直接放到内存中进行读取,如下
public JsonResult UpStuLoadFile(HttpPostedFileBase[] uploadFiles) { //验证上传的excel if (uploadFiles == null || uploadFiles.All(x => x == null)) { return Json(new { Code = "-1", Desc = "请上传excel格式文件" }); } string directoryPath = string.Format("/UploadFiles/Template/{0}/{1}/", DateTime.Now.ToString("yyyyMM"), "Kiaser"); var directoryServerPath = Server.MapPath(directoryPath); //创建文件路径
if (!Directory.Exists(directoryServerPath)){ Directory.CreateDirectory(directoryServerPath);} var errorSuffix = new[] { ".exe" };
//验证文件后缀 var fileFullName = uploadFiles[0].FileName; var fileNameArr = fileFullName.Split('.'); if (fileNameArr.Length != 2) { return Json(new { Code = "-1", Desc = "请上传excel格式文件" }); } var fileName = fileNameArr[0] + DateTime.Now.Ticks; var fileSuffix = string.Format(".{0}", fileNameArr[1]); if (errorSuffix.Contains(fileSuffix)) { return Json(new { Code = "-1", Desc = "请上传excel格式文件" }); } //构造excel完整路径 var filePath = string.Format("{0}/{1}{2}", directoryPath, fileName, fileSuffix); filePath = Server.MapPath(filePath); //将excel 保存到本地 uploadFiles[0].SaveAs(filePath); //加载读取 Excel数据 var dtStudent = ImportStuExcelFile(filePath, gosubjects); //....
解析Excel 文件数据
//解析Excel文件 public List<DataTable> ImportExcelFile(string filePath) { //构造表集合 List<DataTable> dtSubjests = new List<DataTable>(); IWorkbook workbook = null; try { using (FileStream fs = System.IO.File.OpenRead(filePath)) { workbook = WorkbookFactory.Create(fs); } //遍历工作表Sheet for (int i = 0; i < workbook.NumberOfSheets; i++) { #region 工作表相关数据 //当前工作表 var currSheet = workbook.GetSheetAt(i); //获取总行数 var allRowNum = currSheet.LastRowNum; //获取总列数 var allCellNum = currSheet.GetRow(2).LastCellNum; #endregion #region 构造表列 DataTable dt = new DataTable(); dt.Columns.Add("Week"); //周 dt.Columns.Add("Section"); //课节 dt.Columns.Add("ClassName"); //班级 dt.Columns.Add("ClassID"); //班级Id dt.Columns.Add("SubjectName"); //科目 #endregion
//遍历sheet表,读取行,列数据
.... //构造行 DataRow DRow = dt.NewRow();
//构造行中的列
DRow[0] = w.ToStr(); //周 DRow[1] = s.ToStr(); //课节 DRow[2] = ""; DRow[3] = ""; DRow[4] = "";
//表格添加列
dt.Rows.Add(DRow); } return dtSubjests; } catch (Exception e) { throw new Exception(e.Message); } }
关于Excel 的常用构造,和读取,相关介绍就到这里,以后若碰见额外特别的需求,在做进一步记录.
大家好,我是新来的小白,文未佳,却已创。转载请声明(博客园-郎中令)出处,谢谢
---市人皆大笑,举手揶揄之(手动链接博客园)