private TaskCollectionData CombineDatas(List <TaskCollectionData> datas)
{
TaskCollectionData data = new TaskCollectionData();
data.Sheets = new List <DataSheet>();
foreach (TaskCollectionData item in datas)
{
foreach (var sheet in item.Sheets)
{
if (data.Sheets.Find(p => p.SheetName == sheet.SheetName) == null)
{
data.Sheets.Add(new DataSheet()
{
SheetName = sheet.SheetName, Rows = new List <DataRows>()
});
}
var dataSheet = data.Sheets.Find(p => p.SheetName == sheet.SheetName);
dataSheet.Rows.AddRange(sheet.Rows);
}
}
return(data);
}
/// <summary>
/// 读取上传的任务填报数据
/// 返回值 1 上传文件来源不正确 2 上传文件数据填写不正确 3上传成功
/// </summary>
/// <param name="businessId"></param>
/// <param name="fileName"></param>
/// <param name="stream"></param>
/// <param name="views"></param>
/// <param name="errorMessage"></param>
/// <returns></returns>
public int ReadTaskData(string businessId, string fileName, Stream stream, out TaskCollectionData views, out string errorMessage)
{
views = new TaskCollectionData();
errorMessage = "";
var itemTask = TemplateTaskOperator.Instance.GetModel(businessId);
var task = TemplateConfigInstanceOperator.Instance.GetModel(itemTask.TemplateConfigInstanceID);
ExcelEngine engine = new ExcelEngine();
Workbook databook = new Workbook(stream);
string templateID = engine.GetStringCustomProperty(databook.Worksheets[0], "TempleteID");
if (!templateID.Equals(task.TemplateID, StringComparison.CurrentCultureIgnoreCase))
{
errorMessage = "请使用系统下载的文件上传";
return(1);
}
var configs = TemplateConfigOperator.Instance.GetList(task.TemplateID, null).ToList();
var sheets = TemplateSheetOperator.Instance.GetList(task.TemplateID).ToList();
try
{
var data = TemplateTaskOperator.Instance.ReadTaskData(stream, configs, sheets);
views = data;
}
catch (Exception ex)
{
errorMessage = ex.Message;
return(2);
}
return(3);
}
public TaskCollectionData GetData()
{
TaskCollectionData data = new TaskCollectionData();
data.TaskName = this.GetTaskName();
_dataComprobantes.DataSetName = "tsa_CompbrobantesDataset";
data.Add(_dataComprobantes);
_dataComprobantesDetalleDeuda.DataSetName = "tsa_ComprobantesDetalleDeuda";
data.Add(_dataComprobantesDetalleDeuda);
return(data);
}
public IHttpActionResult Upload(string businessId)
{
Guid ret = Guid.Empty;
if (string.IsNullOrEmpty(businessId) || !Guid.TryParse(businessId, out ret))
{
throw new BizException("参数错误");
}
var tuple = AttachmentOperator.Instance.CommonSetting();
var model = AttachmentOperator.Instance.CommonUpload(businessId, tuple.Item1, tuple.Item2, tuple.Item3);
TaskCollectionData dcd = new TaskCollectionData();
using (TransactionScope scope = TransactionScopeFactory.Create())
{
string errorMessage = "";
var result = TemplateOperator.Instance.ReadTaskData(businessId, tuple.Item2, tuple.Item3, out dcd, out errorMessage);
if (result != 3)
{
return(BizResult(new
{
ResultType = result,
Message = errorMessage
}));
}
AttachmentOperator.Instance.AddModel(model);
scope.Complete();
}
var attachment = new Attachment();
attachment.ConvertEntity(model);
return(BizResult(new
{
ResultType = 3,
Attachment = attachment,
Message = "上传成功",
Sheets = dcd.Sheets.Select(x => new { SheetName = x.SheetName, SheetRowLength = x.Rows.Count }).ToList()
}));
}
public TaskCollectionData ReadTaskData(Stream stream, List <Model.TemplateConfig> configs, List <TemplateSheet> sheetConfigs)
{
LoginUserInfo userinfo = WebHelper.GetCurrentUser();;
Workbook dataBook = new Workbook(stream);
TaskCollectionData tcd = new TaskCollectionData();
tcd.Sheets = new List <DataSheet>();
StringBuilder sb = new StringBuilder();
sheetConfigs.ForEach(sheetconfig =>
{
DataSheet sheet = new DataSheet();
sheet.SheetName = sheetconfig.TemplateSheetName;
sheet.Rows = new List <DataRows>();
var firstRow = sheetconfig.RowNum;
var firstColumn = sheetconfig.ColumnNum;
var currentSheet = dataBook.Worksheets[sheet.SheetName];
if (currentSheet != null)
{
var currentConfigs = configs.FindAll(x => x.TemplateSheetID == sheetconfig.ID).OrderBy(x => x.SortIndex).ToList();
for (int i = 0; i <= currentSheet.Cells.MaxDataRow; i++)
{
if (i >= firstRow)
{
DataRows dr = new DataRows();
dr.Cells = new List <RowCells>();
//获取数据
for (int j = 0; j <= currentSheet.Cells.MaxDataColumn; j++)
{
if (j >= firstColumn - 1)
{
int cellIndex = j - (firstColumn - 1);
RowCells cell = new RowCells();
cell.Index = cellIndex;
if (currentConfigs.Count >= cellIndex + 1)
{
var config = currentConfigs[cellIndex];
var cellValue = GetCellValue(currentSheet, i, j, config);
cell.Type = config.FieldType;
cell.Formula = config.CellFormula;
cell.IsFormula = currentSheet.Cells[i, j].IsFormula;
cell.Value = cellValue;
dr.Cells.Add(cell);
}
}
}
if (dr.Cells.Count == 0 || !dr.Cells.Any(x => !string.IsNullOrEmpty(x.Value)))
{
goto BreakLoop;
}
var hasError = dr.Cells.FindAll(cell =>
{
var config = currentConfigs[cell.Index];
return(config != null && config.IsRequired == 1 && string.IsNullOrEmpty(cell.Value));
})
.Select(cell =>
{
var config = currentConfigs[cell.Index];
return(config);
})
.ToList();
if (hasError.Count > 0)
{
sb.AppendLine(string.Format("【{1}】数据列：【{0}】为必填列", string.Join(",", hasError.GroupBy(c => c.FieldName).Select(c => c.Key)), currentSheet.Name));
}
sheet.Rows.Add(dr);
}
continue;
BreakLoop:
{
break;
}
}
tcd.Sheets.Add(sheet);
}
});
if (sb.Length > 0)
{
sb.AppendLine("请仔细核对");
throw new Exception(sb.ToString());
}
var noDataSheets = tcd.Sheets.Where(t => !t.Rows.Any()).ToList();
if (noDataSheets.Any())
{
throw new Exception(string.Format("【{0}】未检测到有效数据行", string.Join("、", noDataSheets.Select(s => s.SheetName))));
}
return(tcd);
}
/// <summary>
/// 用于公式、二维表的需求
/// </summary>
/// <param name="c1"></param>
/// <param name="c2"></param>
/// <param name="c3"></param>
/// <param name="c4"></param>
/// <param name="fileExt"></param>
/// <returns></returns>
public Workbook BuildDataFormula(bool c1, bool c2, bool c3, bool c4, out string fileExt)
{
ExcelEngine engine = new ExcelEngine();
var templateConfigInstance = TemplateConfigInstanceOperator.Instance.GetModel(this._templateConfigInstanceID);
//string filePath = Path.Combine(ConstSet.TemplateBasePath, templateConfigInstance.TemplatePath);
var attachment = AttachmentOperator.Instance.GetModelByCode(templateConfigInstance.TemplatePathFileCode);
Workbook book = new Workbook(FileUploadHelper.DownLoadFileStream(templateConfigInstance.TemplatePathFileCode, attachment.IsUseV1).ToStream());
var ext = templateConfigInstance.TemplatePathFileExt;
if (ext == ".xlsx")
{
book.FileFormat = FileFormatType.Xlsx;
}
else
{
book.FileFormat = FileFormatType.Excel97To2003;
}
fileExt = ext;
book.FileName = templateConfigInstance.TemplateConfigInstanceName;
var tasks = TemplateTaskOperator.Instance.GetList(this._templateConfigInstanceID);
if (!string.IsNullOrEmpty(this._collectUserID))
{
tasks = tasks.FindAll(p => p.DataCollectUserID.ToLower() == this._collectUserID.ToLower());
}
tasks = tasks.FindAll(x => x.Status == ProcessStatus.Approved.GetHashCode());
//tasks = tasks.FindAll(p => p.Status == (int)ProcessStatus.Approved);
List <TaskCollectionData> datas = new List <TaskCollectionData>();
foreach (var item in tasks)
{
//TaskCollectionData data = JsonHelper.Deserialize<TaskCollectionData>(item.Content);
//if (!string.IsNullOrEmpty(item.Content) && !string.IsNullOrEmpty(item.AuthTimeString))
if (item.Status == Common.ProcessStatus.Approved.GetHashCode())
{
var json = JsonHelper.Deserialize <TaskCollectionData>(item.Content);
json.Sheets.ForEach(x =>
{
x.Rows.ForEach(r =>
{
r.LoginName = item.EmployeeLoginName;
r.OrgName = item.OrgName;
r.UserName = item.EmployeeName;
});
});
datas.Add(json);
}
}
TaskCollectionData data = CombineDatas(datas);
var sheetConfigs = TemplateSheetOperator.Instance.GetList(templateConfigInstance.TemplateID).ToList();
var configs = TemplateConfigOperator.Instance.GetList(templateConfigInstance.TemplateID, null).ToList();
#region 清空模板原有数据 可能会引起填报数据的颜色跟表头一致
//foreach (var sheet in data.Sheets)
//{
// var sheetConfig = sheetConfigs.Find(x => x.TemplateSheetName == sheet.SheetName);
// if (sheetConfig != null)
// {
// var firstRow = sheetConfig.RowNum;
// var workSheet = book.Worksheets[sheet.SheetName];
// for (int i = workSheet.Cells.MaxRow; i >= firstRow; i--)
// {
// workSheet.Cells.DeleteRow(i);
// }
// }
//}
#endregion
//表示是否是空列
var emptycolumn = new List <int>();
foreach (var sheet in data.Sheets)
{
var sheetConfig = sheetConfigs.Find(x => x.TemplateSheetName == sheet.SheetName);
if (sheetConfig != null)
{
var firstColumn = sheetConfig.ColumnNum;
var firstRow = sheetConfig.RowNum;
var workSheet = book.Worksheets[sheet.SheetName];
var firstColumnStyle = workSheet.Cells[firstRow - 1, firstColumn - 1].GetStyle();
var firstRowStyle = workSheet.Cells[firstRow, firstColumn].GetStyle();
// var startIdx = firstColumn - 1;
var style = workSheet.Cells[sheetConfig.RowNum, firstColumn - 1].GetStyle();
var currentConfigs = configs.FindAll(x => x.TemplateSheetID == sheetConfig.ID);
//获取没有列标题的列(主要考虑合并单元格的情况)
for (int i = 0; i <= workSheet.Cells.MaxDataColumn; i++)
{
var flag = true;
//主要考虑到合并单元格
for (int j = 1; j <= firstRow; j++)
{
if (workSheet.Cells[firstRow - j, i].StringValue.Trim() != "")
{
flag = false;
break;
}
}
if (flag)
{
emptycolumn.Add(i);
}
}
//没有示例的内容更汇总颜色
var styleDic = new Dictionary <int, Style>();
for (int i = 0; i <= workSheet.Cells.MaxColumn; i++)
{
var columnStyle = workSheet.Cells[firstRow, i].GetStyle();
if (workSheet.Cells[firstRow, i].StringValue == "")
{
columnStyle.ForegroundColor = Color.White;
columnStyle.Pattern = BackgroundType.Solid;
}
styleDic.Add(i, columnStyle);
}
int rowIndex = firstRow;
sheet.Rows.ForEach(row =>
{
var current = rowIndex;
workSheet.Cells.InsertRows(current, 1);
workSheet.Cells.CopyRow(workSheet.Cells, current + 1, current);
int cellIndex = 0;
currentConfigs.ForEach(x =>
{
if (cellIndex <= row.Cells.Count - 1)
{
var cell = row.Cells[cellIndex];
var excelCell =
workSheet.Cells[current, cellIndex + (firstColumn - 1)];
try
{
switch (cell.Type)
{
case "Text":
excelCell.PutValue(cell.Value);
break;
case "Number":
excelCell.PutValue(Double.Parse(cell.Value));
break;
default:
excelCell.PutValue(cell.Value);
break;
}
//将没有表头的列置空（排除第一列）
if (emptycolumn.Contains(cellIndex) && rowIndex > firstRow)
{
workSheet.Cells[rowIndex, cellIndex].PutValue("");
var s = workSheet.Cells[rowIndex, cellIndex].GetStyle();
s.ForegroundColor = Color.White;
s.Pattern = BackgroundType.Solid;
s.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
s.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
s.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
s.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
workSheet.Cells[rowIndex, cellIndex].SetStyle(s);
}
//设置内容颜色
else
{
//设置公式
if (x.IsFormula == 1)
{
if (cell.IsFormula)
{
excelCell.Formula = x.CellFormula.Replace("{R}", (1 + rowIndex).ToString());
styleDic[cellIndex].Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
styleDic[cellIndex].Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
styleDic[cellIndex].Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
styleDic[cellIndex].Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
workSheet.Cells[rowIndex, cellIndex].SetStyle(styleDic[cellIndex]);
}
else
{
//破坏掉公式后，亮色显示背景色
var noFormulaStyle = workSheet.Cells[rowIndex, cellIndex].GetStyle();
noFormulaStyle.ForegroundColor = Color.Yellow;
noFormulaStyle.Pattern = BackgroundType.Solid;
noFormulaStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
noFormulaStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
noFormulaStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
noFormulaStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
workSheet.Cells[rowIndex, cellIndex].SetStyle(noFormulaStyle);
}
}
//二维表高亮显示时，用值填充
if (cell.IsShowColorForUpdateData)
{
excelCell.PutValue(cell.Value);
var noFormulaStyle = workSheet.Cells[rowIndex, cellIndex].GetStyle();
noFormulaStyle.ForegroundColor = Color.Yellow;
noFormulaStyle.Pattern = BackgroundType.Solid;
noFormulaStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
noFormulaStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
noFormulaStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
noFormulaStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
workSheet.Cells[rowIndex, cellIndex].SetStyle(noFormulaStyle);
}
}
}
catch (Exception ex)
{
}
//}
}
cellIndex++;
});
rowIndex++;
});
var currentRow = workSheet.Cells[rowIndex];
var start = rowIndex;
while (currentRow != null)
{
var list = new List <Cell>();
for (int j = 0; j <= workSheet.Cells.MaxDataColumn; j++)
{
list.Add(workSheet.Cells[rowIndex, j]);
}
if (
//list.Any(x => !string.IsNullOrEmpty(x.StringValue))
//&&
list.Any(x => x.IsMerged)
||
list.All(x => string.IsNullOrEmpty(x.StringValue))
)
{
break;
}
rowIndex++;
currentRow = workSheet.Cells[rowIndex];
}
workSheet.Cells.DeleteRows(start, rowIndex - start);
//重新计算后插入数据库
book.CalculateFormula();
InsertColumn(workSheet, sheet, firstColumn, firstRow, c1, c2, c3, c4, firstColumnStyle, firstRowStyle);
}
emptycolumn = new List <int>();
}
//book.CalculateFormula();
return(book);
}
