/// <summary> /// 根据文件流读取excel配置信息 /// </summary> /// <param name="stream">文件流</param> /// <param name="tuple">配置信息集合tuple说明(item1:sheet名称 item2:首行 item3:首列)</param> /// <returns></returns> public Tuple <List <TemplateSheet>, List <Model.TemplateConfig> > ReadSheetDataFromStream(Stream stream, List <Tuple <string, int, int> > tuple) { List <TemplateSheet> sheets = new List <TemplateSheet>(); List <Model.TemplateConfig> configs = new List <Model.TemplateConfig>(); Workbook workbook = new Workbook(stream); var defaultColumn = 1; var defaultRow = 3; foreach (Worksheet wb in workbook.Worksheets) { if (!wb.IsVisible) { continue; } TemplateSheet sheet = new TemplateSheet(); var item = tuple.Find(x => x.Item1 == wb.Name); var column = defaultColumn; var row = defaultRow; if (item != null) { //column = item.Item3; row = item.Item2; } sheet.ColumnNum = column; sheet.ID = Guid.NewGuid().ToString(); sheet.RowNum = row - 1; sheet.Status = 0; sheet.TemplateSheetName = wb.Name; sheet.TemplateSheetRemark = ""; sheet.TemplateSheetTitle = wb.Cells[0, 0].StringValue; List <Model.TemplateConfig> items = new List <Model.TemplateConfig>(); for (int i = 0; i <= wb.Cells.MaxDataColumn; i++) { //if (i >= column - 1) if (i >= 0) { var columnModel = new Model.TemplateConfig(); columnModel.BGColor = "255,255,255"; columnModel.Digit = 0; columnModel.FieldType = "Text"; columnModel.FiledLength = 0; columnModel.HasSelectedValue = 0; columnModel.ID = Guid.NewGuid().ToString(); columnModel.IsRequired = 0; columnModel.SortIndex = i; columnModel.TemplateSheetID = sheet.ID; columnModel.TemplateSheetName = sheet.TemplateSheetName; var currentCell = wb.Cells[row - 1 - 1, i]; var cellValue = GetColumnSpanHeaderText(wb, currentCell); columnModel.FieldName = cellValue; configs.Add(columnModel); } } sheets.Add(sheet); } return(Tuple.Create(sheets, configs)); }
private string GetCellValue(Worksheet item, int row, int column, Model.TemplateConfig cConfig) { if (string.IsNullOrEmpty(item.Cells[row, column].StringValue)) { return(""); } string res = ""; try { var cell = item.Cells[row, column]; if (cell.IsMerged) { var range = cell.GetMergedRange(); cell = item.Cells[range.FirstRow, range.FirstColumn]; } switch (cConfig.FieldType) { case "Text": res = cell.StringValue; break; case "Number": int dot = cConfig.Digit; //string format = string.Format("{{0:F{0}}}", dot); res = cell.DoubleValue.ToString("F" + dot); break; case "DateTiem": res = cell.DateTimeValue.ToString("yyyy-MM-dd"); break; default: res = cell.StringValue; break; } } catch (Exception e) { return(""); } return(res); }
/// <summary> /// 根据提交的业务数据实体读取excel配置信息 /// </summary> /// <param name="template">模板实体</param> /// <param name="renderType">渲染类型 1在线编辑 2克隆 3本地模板</param> /// <param name="views">业务数据实体</param> /// <returns></returns> public Tuple <List <TemplateSheet>, List <Model.TemplateConfig>, List <TemplateConfigSelect> > ReadSheetDataFromBizModel(Template template, int renderType, List <ViewSheet> views) { views = views ?? new List <ViewSheet>(); var userInfo = WebHelper.GetCurrentUser();; List <TemplateSheet> sheets = new List <TemplateSheet>(); List <Model.TemplateConfig> configs = new List <Model.TemplateConfig>(); List <TemplateConfigSelect> selects = new List <TemplateConfigSelect>(); foreach (var item in views) { TemplateSheet sheet = new TemplateSheet(); sheet.ID = Guid.NewGuid().ToString(); sheet.TemplateSheetName = item.name; sheet.TemplateSheetTitle = item.title; sheet.TemplateSheetRemark = item.remark; sheet.RowNum = item.firstrow - 1; sheet.ColumnNum = item.firstcolumn; sheet.TemplateID = template.ID; sheet.TemplateName = template.TemplateName; int index = 1; item.columns = item.columns ?? new List <ViewColumn>(); foreach (var config in item.columns) { //如果是在线编辑或克隆模板 遇到第一个空列则视为后面没有其他列 这里需要在客户端做下验证 不可以跳格填写内容 if (string.IsNullOrEmpty(config.name) && (renderType == 0 || renderType == 1)) { continue; } Model.TemplateConfig tconfig = new Model.TemplateConfig(); tconfig.ID = Guid.NewGuid().ToString(); tconfig.FieldName = config.name; tconfig.FieldType = config.type; tconfig.FiledLength = 0; tconfig.Digit = config.digit; tconfig.BGColor = config.bgcolor; tconfig.HasSelectedValue = !string.IsNullOrEmpty(config.range) && config.range.Length > 0 ? 1 : 0; tconfig.IsDeleted = false; tconfig.IsRequired = config.required ? 1 : 0; tconfig.SortIndex = index; tconfig.TemplateSheetID = sheet.ID; tconfig.TemplateSheetName = sheet.TemplateSheetName; tconfig.TemplateID = template.ID; tconfig.TemplateName = template.TemplateName; //设置公式 tconfig.IsFormula = config.isformula ? 1 : 0; if (config.isformula) { tconfig.TempFormula = config.tempformula; tconfig.CellFormula = config.cellformula; } if (!string.IsNullOrEmpty(config.range)) { var rangeVal = config.range.Replace(',', ',').Split(','); int rindex = 0; foreach (var rVal in rangeVal) { TemplateConfigSelect configselect = new TemplateConfigSelect(); configselect.TemplateConfigID = tconfig.ID; configselect.SelectedValue = rVal; configselect.SortIndex = ++rindex; configselect.TemplateSheetID = sheet.ID; configselect.TemplateSheetName = sheet.TemplateSheetName; configselect.TemplateID = template.ID; configselect.TemplateName = template.TemplateName; selects.Add(configselect); } } configs.Add(tconfig); index++; } sheets.Add(sheet); } return(Tuple.Create(sheets, configs, selects)); }