/// <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));
}
/// <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));
}
