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 的常用构造,和读取,相关介绍就到这里,以后若碰见额外特别的需求,在做进一步记录.

 

posted @ 2020-09-30 21:50  郎中令  阅读(333)  评论(0编辑  收藏  举报