public string CommonGetPreviewLinkToken(string attachmentId)
{
return(null);
var attachment = AttachmentOperator.Instance.GetModel(attachmentId);
//如果当前文件是v1版并且当前使用的文件平台为v2版 则提交预览流
var stream = FileUploadHelper.DownLoadFileStream(attachment.AttachmentPath, attachment.IsUseV1).ToStream();
var accessToken = "";
var previewRelation = new PreviewRelation();
previewRelation.BusinessID = attachmentId;
previewRelation.CreateDate = DateTime.Now;
var currentUser = WebHelper.GetCurrentUser();;
previewRelation.CreatorLoginName = currentUser.LoginName;
previewRelation.CreatorName = currentUser.CNName;
previewRelation.ID = Guid.NewGuid().ToString();
previewRelation.DocumentByteLength = stream.Length;
previewRelation.DocumentFileCode = attachment.AttachmentPath;
previewRelation.DocumentFileName = attachment.Name;
var link = OWAHelper.Instance.GetDocumentLink(attachment.Name, previewRelation.ID, out accessToken);
previewRelation.AccessToken = accessToken;
previewRelation.DocumentLink = link;
PreviewRelationOperator.Instance.AddModel(previewRelation);
return(previewRelation.ID);
}
///// <summary>
///// 获取预览地址
///// </summary>
///// <param name="taskId"></param>
///// <returns></returns>
//[HttpGet]
//[Route("template/download/preview/{taskId}")]
//public IHttpActionResult PreviewTaskTemplate(string taskId)
//{
// FilesController filesController = new FilesController();
// string businessType = "TaskTemplatePreviewAttach";
// Lib.Model.Attachment attachment = null;
// var finder = AttachmentOperator.Instance.GetLastModel(businessType, taskId);
// if (finder != null)
// {
// attachment = finder;
// }
// else
// {
// var fileName = "";
// var stream = LoadTaskTemplateStream(taskId, out fileName);
// attachment = AttachmentOperator.Instance.CommonUpload(taskId, businessType, fileName, stream);
// }
// AttachmentOperator.Instance.AddModel(attachment);
// var result = filesController.GetPreviewLinkToken(attachment.ID);
// return result;
//}
private MemoryStream LoadTaskTemplateStream(string taskId, out string fileName)
{
var ret = Guid.Empty;
if (string.IsNullOrEmpty(taskId) || !Guid.TryParse(taskId, out ret))
{
throw new BizException("参数错误");
}
var task = TemplateTaskOperator.Instance.GetModel(taskId);
var tci = TemplateConfigInstanceOperator.Instance.GetModel(task.TemplateConfigInstanceID);
var attachment = AttachmentOperator.Instance.GetModelByCode(tci.TemplatePathFileCode);
System.IO.Stream stream = FileUploadHelper.DownLoadFileStream(tci.TemplatePathFileCode, attachment.IsUseV1).ToStream();
ExcelEngine engine = new ExcelEngine();
Workbook taskBook = new Workbook(stream);
#region 更新二维表时使用
var dcu = DataCollectUserOperator.Instance.GetModel(task.DataCollectUserID);
var templateSheets = TemplateSheetOperator.Instance.GetList(tci.TemplateID);
if (tci.TaskTemplateType == 2)
{
TemplateOperator.Instance.GetTemplateDateForUpdate(taskBook, templateSheets, dcu.UpdateArea, dcu.AreaValue);
}
#endregion
engine.SetCustomProperty(taskBook.Worksheets[0], "TempleteID", tci.TemplateID);
MemoryStream taskMemStream = new MemoryStream();
var format = TemplateOperator.Instance.GetFormatType(tci.TemplatePathFileExt);
taskBook.Save(taskMemStream, format);
fileName = string.Format("{0}-{1}（{2}）-{3}", tci.TemplateConfigInstanceName, task.EmployeeName, task.EmployeeLoginName, DateTime.Now.ToString("yyyyMMddHHmmss"));
fileName = string.Format("{0}{1}", fileName, tci.TemplatePathFileExt);
return(taskMemStream);
}
public TaskCollectionData ReadTaskDataForUpdateTask(Workbook attachmentWoorBook, Attachment attac, List <Model.TemplateConfig> configs, List <TemplateSheet> sheetConfigs)
{
LoginUserInfo userinfo = WebHelper.GetCurrentUser();;
var bytes = FileUploadHelper.DownLoadFileStream(attac.AttachmentPath, attac.IsUseV1);
Stream stream = bytes.ToStream();
stream.Seek(0, SeekOrigin.Begin);
return(ReadTaskDataForUpdateTask(attachmentWoorBook, stream, configs, sheetConfigs));
}
public IHttpActionResult Submit(string businessId, [FromBody] Todo todo)
{
Guid ret = Guid.Empty;
if (string.IsNullOrEmpty(businessId) || !Guid.TryParse(businessId, out ret))
{
throw new BizException("参数错误");
}
using (TransactionScope transaction = TransactionScopeFactory.Create(TransactionScopeOption.Required))
{
var tTask = TemplateTaskOperator.Instance.GetModel(businessId);
var tci = TemplateConfigInstanceOperator.Instance.GetModel(tTask.TemplateConfigInstanceID);
LoginUserInfo userinfo = WebHelper.GetCurrentUser();;
var configs = TemplateConfigOperator.Instance.GetList(tci.TemplateID, null).ToList();
var sheetConfigs = TemplateSheetOperator.Instance.GetList(tci.TemplateID).ToList();
var attac = AttachmentOperator.Instance.GetLastModel("UploadTaskData", businessId);
tTask.SubmitTime = DateTime.Now;
tTask.FileName = attac.Name;
tTask.FilePath = attac.AttachmentPath;
tTask.Status = (int)ProcessStatus.Inprocess;
tTask.Remark = todo.TaskReportRemark;
tTask.ModifierLoginName = userinfo.LoginName;
tTask.ModifierName = userinfo.CNName;
tTask.ModifyTime = DateTime.Now;
//TaskTemplateType=2时，更新二维表
if (tci.TaskTemplateType == 2)
{
//先获取模板数据
var attachment = AttachmentOperator.Instance.GetModelByCode(tci.TemplatePathFileCode);
var dcu = DataCollectUserOperator.Instance.GetModel(tTask.DataCollectUserID);
System.IO.Stream stream = FileUploadHelper.DownLoadFileStream(tci.TemplatePathFileCode, attachment.IsUseV1).ToStream();
ExcelEngine engine = new ExcelEngine();
Workbook templateBook = new Workbook(stream);
TemplateOperator.Instance.GetTemplateDateForUpdate(templateBook, sheetConfigs, dcu.UpdateArea, dcu.AreaValue);
var json = TemplateTaskOperator.Instance.ReadTaskDataForUpdateTask(templateBook, attac, configs, sheetConfigs);
tTask.Content = JsonHelper.Serialize(json);
}
else
{
var json = TemplateTaskOperator.Instance.ReadTaskData(attac, configs, sheetConfigs);
tTask.Content = JsonHelper.Serialize(json);
}
TemplateTaskOperator.Instance.UpdateModel(tTask);
//var result = TemplateTaskOperator.Instance.Approve(businessId, tTask.Remark, WebHelper.GetCurrentUser());
transaction.Complete();
}
return(BizResult(true));
}
public IHttpActionResult Load(string templateId)
{
Guid ret = Guid.Empty;
if (string.IsNullOrEmpty(templateId) || !Guid.TryParse(templateId, out ret))
{
throw new BizException("参数无效");
}
var template = TemplateOperator.Instance.GetViewList(new VTemplateFilter()
{
TemplateID = templateId
}).FirstOrDefault();
var attachment = AttachmentOperator.Instance.GetLastModel("UploadModelAttach", templateId);
if (attachment == null && template.IsImport == 0)
{
//为了适配老数据
ExcelTempleteBuilder builder = new ExcelTempleteBuilder(templateId);
var fileExt = "";
//初始化出excel对象
var wb = builder.InitExcelTemplete(out fileExt);
var format = TemplateOperator.Instance.GetFormatType(fileExt);
MemoryStream tmpStream = new MemoryStream();
wb.Save(tmpStream, format);
var attach = AttachmentOperator.Instance.CommonUpload(templateId, "UploadModelAttach", string.Format("{0}{1}", template.TemplateName, fileExt), tmpStream);
AttachmentOperator.Instance.AddModel(attach);
attachment = attach;
}
var fileFormat = TemplateOperator.Instance.GetFormatType(attachment.FileExt);
var stream = FileUploadHelper.DownLoadFileStream(attachment.AttachmentPath, attachment.IsUseV1).ToStream();
var sheets = TemplateSheetOperator.Instance.GetList(templateId).ToList();
var configs = TemplateConfigOperator.Instance.GetList(templateId, null).ToList();
var selects = TemplateConfigSelectOperator.Instance.GetList(templateId).ToList();
//使用配置和文件流生成业务数据便于前台展示
List <ViewSheet> list = TemplateOperator.Instance.ReadExcelData(stream, fileFormat, sheets, configs, selects);
Template model = new Template();
model.ConvertEntity(template);
model.sheets = list;
return(BizResult(new
{
Attachment = attachment,
Model = model
}));
}
public IHttpActionResult DownLoadStream(string attachmentId)
{
Guid ret = Guid.Empty;
if (string.IsNullOrEmpty(attachmentId) || !Guid.TryParse(attachmentId, out ret))
{
throw new BizException("参数错误");
}
var finder = AttachmentOperator.Instance.GetModel(attachmentId);
if (finder == null)
{
throw new BizException("附件查找失败");
}
var bytes = FileUploadHelper.DownLoadFileStream(finder.AttachmentPath, false);
return(new FileResult(finder.AttachmentFullName, bytes, Request));
}
public FileResult Get(string businessId, string access_token)
{
try
{
NLogTraceHelper.Instace.Info(string.Format("获取文件流：请求参数,businessId:{0},access_token", businessId, access_token));
var finder = PreviewRelationOperator.Instance.GetModel(businessId);
NLogTraceHelper.Instace.Info("找到记录，正在进行文件下载");
var attachment = AttachmentOperator.Instance.GetModel(finder.BusinessID);
var bytes = FileUploadHelper.DownLoadFileStream(finder.DocumentFileCode, attachment.IsUseV1);
return(new FileResult(finder.DocumentFileName, bytes, Request));
}
catch (Exception ex)
{
NLogTraceHelper.Instace.Error(ex, "错误信息：{0}", ex.Message);
throw new BizException("获取文件内容错误");
}
}
/// <summary>
/// 附件压缩成内存流
/// </summary>
/// <param name="atts"></param>
/// <param name="zipedFile"></param>
/// <param name="blockSize"></param>
/// <returns></returns>
private MemoryStream ZipFile(List <Lib.Model.Attachment> atts, string zipedFile, int blockSize)
{
string tmpFilePath = System.IO.Path.GetTempPath() + "\\" + Guid.NewGuid().ToString();
string tmpZipFilePath = tmpFilePath + "\\" + Guid.NewGuid().ToString() + ".zip";
//tmpZipFilePath = Path.ChangeExtension(tmpZipFilePath, "zip");
using (Ionic.Zip.ZipFile zipFile = new Ionic.Zip.ZipFile(Encoding.GetEncoding("GB2312")))
{
var t = atts.GroupBy(p => p.CreatorLoginName);
foreach (var itemKey in t)
{
var folderName = string.Format("{0}({1})", itemKey.FirstOrDefault().CreatorName, itemKey.Key);
List <string> files = new List <string>();
string newPath = Path.Combine(tmpFilePath, folderName);
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
zipFile.AddDirectory(newPath);
foreach (var item in itemKey)
{
string fileName = item.Name;
int index = 1;
while (files.Any(x => x.ToLower() == fileName.ToLower()))
{
var newFileName = BuilderNewFileName(fileName, index);
fileName = newFileName;
index++;
}
string filePath = Path.Combine(newPath, fileName);
File.WriteAllBytes(filePath, FileUploadHelper.DownLoadFileStream(item.AttachmentPath, item.IsUseV1));
zipFile.AddFile(filePath, folderName);
files.Add(fileName);
}
}
MemoryStream stream = new MemoryStream();
zipFile.Save(stream);
return(stream);
}
}
//---------------裴晓红-----------------------
/// <summary>
/// 源文件压缩成内存流
/// </summary>
/// <param name="atts"></param>
/// <param name="zipedFile"></param>
/// <param name="blockSize"></param>
/// <returns></returns>
private MemoryStream ZipFile(List <Lib.Model.TemplateTask> tasks, string zipedFile, int blockSize)
{
string tmpFilePath = System.IO.Path.GetTempPath() + "\\" + Guid.NewGuid().ToString();
string tmpZipFilePath = tmpFilePath + "\\" + Guid.NewGuid().ToString() + ".zip";
using (Ionic.Zip.ZipFile zipFile = new Ionic.Zip.ZipFile(Encoding.GetEncoding("GB2312")))
{
var folderName = zipedFile;
string newPath = Path.Combine(tmpFilePath, folderName);
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
zipFile.AddDirectory(newPath);
List <string> files = new List <string>();
foreach (var itemKey in tasks)
{
if (string.IsNullOrEmpty(itemKey.FileName))
{
continue;
}
string fileName = itemKey.FileName;
int index = 1;
while (files.Any(x => x.ToLower() == fileName.ToLower()))
{
var newFileName = BuilderNewFileName(fileName, index);
fileName = newFileName;
index++;
}
string filePath = Path.Combine(newPath, fileName);
File.WriteAllBytes(filePath, FileUploadHelper.DownLoadFileStream(itemKey.FilePath, false));
zipFile.AddFile(filePath, folderName);
files.Add(fileName);
}
MemoryStream stream = new MemoryStream();
zipFile.Save(stream);
return(stream);
}
}
public IHttpActionResult Download(string templateId)
{
Guid ret = Guid.Empty;
if (string.IsNullOrEmpty(templateId) || !Guid.TryParse(templateId, out ret))
{
throw new BizException("参数错误");
}
MemoryStream mockStream = new MemoryStream();
var templete = TemplateOperator.Instance.GetModel(templateId);
var fileCode = templete.TemplatePathFileCode;
if (string.IsNullOrEmpty(fileCode))
{
throw new BizException("模板数据存在问题，未找到有效远程文件");
}
var attachment = AttachmentOperator.Instance.GetModelByCode(fileCode);
ExcelEngine engine = new ExcelEngine();
var stream = FileUploadHelper.DownLoadFileStream(fileCode, attachment.IsUseV1).ToStream();
stream.Seek(0, SeekOrigin.Begin);
Workbook taskBook = new Workbook(stream);
var fileExt = templete.TemplatePathFileExt;
var format = TemplateOperator.Instance.GetFormatType(fileExt);
if (format == SaveFormat.Unknown)
{
throw new BizException("未知文件格式");
}
MemoryStream taskMemStream = new MemoryStream();
taskBook.Save(taskMemStream, format);
taskMemStream.Seek(0, SeekOrigin.Begin);
var fileName = string.Format("{0}{1}", templete.TemplateName, fileExt);
return(new FileResult(fileName, taskMemStream.ToArray(), Request));
}
/// <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);
}
public IHttpActionResult SetDataRange(string attachmentId, [FromBody] List <DataRangeSetting> ranges)
{
Guid ret = Guid.Empty;
if (string.IsNullOrEmpty(attachmentId) || !Guid.TryParse(attachmentId, out ret))
{
throw new BizException("参数无效");
}
var attachment = AttachmentOperator.Instance.GetModel(attachmentId);
if (attachment == null)
{
throw new BizException("文件查找失败");
}
var fileStream = FileUploadHelper.DownLoadFileStream(attachment.AttachmentPath, attachment.IsUseV1).ToStream();
var fileFormat = TemplateOperator.Instance.GetFormatType(attachment);
//by文件流生成配置信息（分析时因为还没有设置数据区域，所以默认第三行第一列）
var dataRangeList =
ranges.Select(x =>
{
return(Tuple.Create(x.name, x.row, x.column));
}).ToList();
if (dataRangeList.Any(x => string.IsNullOrEmpty(x.Item1)))
{
throw new BizException("工作表名称不可为空");
}
if (dataRangeList.Any(x => x.Item2 < 2 || x.Item3 < 1))
{
throw new BizException("数据区域存在错误");
}
if (dataRangeList.GroupBy(x => x.Item1).Any(x => x.Count() > 1))
{
throw new BizException("工作表名称不可重复");
}
var tuples = TemplateOperator.Instance.ReadSheetDataFromStream(fileStream, dataRangeList);
//使用配置和文件流生成业务数据便于前台展示
// List<ViewSheet> list = TemplateOperator.Instance.ReadExcelData(fileStream, fileFormat, tuples.Item1, tuples.Item2, new List<Lib.Model.TemplateConfigSelect>());
List <ViewSheet> list = TemplateOperator.Instance.ReadExcelData(fileStream, fileFormat, tuples.Item1, new List <Lib.Model.TemplateConfig>(), new List <Lib.Model.TemplateConfigSelect>());
list = list.Where(x => dataRangeList.Exists(r => r.Item1 == x.name)).ToList();
//如果没有输入列表，系统添加默认列，系统添加默认列
var flag = 1;
foreach (var viewSheet in list)
{
foreach (var column in viewSheet.columns)
{
if (column.name == "")
{
column.name = "".PadLeft(flag);
flag++;
}
}
}
return(BizResult(new
{
Attachment = attachment,
Data = list
}));
}
/// <summary>
/// 初始化excel
/// </summary>
/// <param name="isFile">是否为文件模式（对应是否为ismport）</param>
/// <param name="attachment"></param>
/// <param name="template"></param>
/// <param name="sheets"></param>
/// <param name="configs"></param>
/// <param name="selects"></param>
/// <returns></returns>
public Workbook InitExcel(bool isFile, Attachment attachment, Template template, List <TemplateSheet> sheets, List <TemplateConfig> configs, List <TemplateConfigSelect> selects)
{
ExcelEngine engine = new ExcelEngine();
Workbook wb = null;
if (!isFile)
{
wb = new Workbook();
wb.Worksheets.Clear();
}
else
{
var stream = FileUploadHelper.DownLoadFileStream(attachment.AttachmentPath, attachment.IsUseV1).ToStream();
stream.Seek(0, SeekOrigin.Begin);
wb = new Workbook(stream);
}
#region init style
StyleFlag styleFlag = new StyleFlag()
{
All = true, Borders = true
};
CellsColor bcolor = wb.CreateCellsColor();
bcolor.ColorIndex = 36;
CellsColor ccolor = wb.CreateCellsColor();
ccolor.ColorIndex = 36;
CellsColor tcolor = wb.CreateCellsColor();
tcolor.ColorIndex = 36;
Style style = wb.Styles[wb.Styles.Add()];
style.Font.Size = 18;
style.Font.Name = "微软雅黑";
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
style.VerticalAlignment = Aspose.Cells.TextAlignmentType.Fill;
style.ForegroundColor = System.Drawing.Color.White;
style.Pattern = BackgroundType.Solid;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;;
style.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;;
style.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
style.VerticalAlignment = TextAlignmentType.Center;
style.HorizontalAlignment = TextAlignmentType.Center;
Style markStyle = wb.Styles[wb.Styles.Add()];
markStyle.Font.Size = 15;
markStyle.Font.Name = "微软雅黑";
markStyle.IsTextWrapped = true;
markStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Right;
markStyle.VerticalAlignment = Aspose.Cells.TextAlignmentType.Fill;
markStyle.ForegroundColor = System.Drawing.Color.Yellow;
markStyle.Pattern = BackgroundType.Solid;
markStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.None;
markStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.None;
markStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.None;
markStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.None;
markStyle.VerticalAlignment = TextAlignmentType.Center;
markStyle.HorizontalAlignment = TextAlignmentType.Left;
Style contentStyle = wb.Styles[wb.Styles.Add()];
//contentStyle.IsLocked = false;
contentStyle.Font.Name = "Arial";
contentStyle.Font.Size = 10;
contentStyle.Pattern = BackgroundType.Solid;
//contentStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
// contentStyle.Borders[BorderType.TopBorder].Color = System.Drawing.Color.FromArgb(155, 194, 230);
contentStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;;
contentStyle.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.White;
contentStyle.IsTextWrapped = true;
contentStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;;
contentStyle.Borders[BorderType.LeftBorder].Color = bcolor.Color;
contentStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
contentStyle.Borders[BorderType.RightBorder].Color = bcolor.Color;
contentStyle.VerticalAlignment = TextAlignmentType.Center;
contentStyle.HorizontalAlignment = TextAlignmentType.Left;
Style contentStyle1 = wb.Styles[wb.Styles.Add()];
//contentStyle1.IsLocked = false;
contentStyle1.Font.Name = "Arial";
contentStyle1.Font.Size = 10;
//221, 235, 247
contentStyle1.ForegroundColor = ccolor.Color;
contentStyle1.Pattern = BackgroundType.Solid;
//contentStyle1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
//contentStyle1.Borders[BorderType.TopBorder].Color = System.Drawing.Color.FromArgb(155, 194, 230);
contentStyle1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
contentStyle1.Borders[BorderType.BottomBorder].Color = bcolor.Color;
contentStyle1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;;
contentStyle1.Borders[BorderType.LeftBorder].Color = bcolor.Color;
contentStyle1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
contentStyle1.Borders[BorderType.RightBorder].Color = bcolor.Color;
contentStyle1.VerticalAlignment = TextAlignmentType.Center;
contentStyle1.HorizontalAlignment = TextAlignmentType.Left;
Style titleStyle = wb.Styles[wb.Styles.Add()];
//contentStyle.IsLocked = false;
titleStyle.Font.Size = 12;
titleStyle.Font.Name = "微软雅黑";
titleStyle.ForegroundColor = tcolor.Color;
titleStyle.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Right;
titleStyle.VerticalAlignment = Aspose.Cells.TextAlignmentType.Fill;
titleStyle.Pattern = BackgroundType.Solid;
titleStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
titleStyle.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
titleStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;;
titleStyle.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
titleStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;;
titleStyle.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
titleStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
titleStyle.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
titleStyle.VerticalAlignment = TextAlignmentType.Center;
titleStyle.HorizontalAlignment = TextAlignmentType.Center;
#endregion
foreach (var sheet in sheets)
{
int beforeCount = wb.Worksheets.Count;
//重名异常，忽略
try
{
wb.Worksheets.Add(sheet.TemplateSheetName);
}
catch (Exception e)
{
int afterCount = wb.Worksheets.Count;
if (afterCount > beforeCount)
{
wb.Worksheets.RemoveAt(wb.Worksheets.Count - 1);
}
}
var itemConfigs = configs.Where(x => x.TemplateSheetID == sheet.ID).ToList();
//var configs = TemplateConfigOperator.Instance.GetList(sheet.TemplateID, sheet.ID);
itemConfigs = itemConfigs.OrderBy(p => p.SortIndex).ToList();
int startIndex = sheet.ColumnNum - 1;
if (!isFile)
{
Range r = wb.Worksheets[sheet.TemplateSheetName].Cells.CreateRange(0, startIndex, sheet.RowNum - 1, itemConfigs.Count);
r.Merge();
r[0, 0].PutValue(sheet.TemplateSheetTitle);
r.ColumnWidth = 13.88;
r.RowHeight = 48.75;
//r[0, 0].SetStyle(style);
if (sheet.TemplateSheetRemark != null && sheet.TemplateSheetRemark != "")
{
Range rr = wb.Worksheets[sheet.TemplateSheetName].Cells.CreateRange(11, startIndex, 5, itemConfigs.Count);
rr.Merge();
rr[0, 0].PutValue(sheet.TemplateSheetRemark);
rr[0, 0].SetStyle(markStyle);
}
}
if (itemConfigs.Count > 0)
{
Range dataRange = wb.Worksheets[sheet.TemplateSheetName].Cells.CreateRange(sheet.RowNum, sheet.ColumnNum - 1, 9, itemConfigs.Count);
dataRange.Name = "DataName" + sheet.TemplateSheetName;
}
foreach (var config in itemConfigs)
{
Style cStyle = new Style();
#region 格式枚举
//Value Type Format String
//0 General General
//1 Decimal 0
//2 Decimal 0.00
//3 Decimal #,##0
//4 Decimal #,##0.00
//5 Currency $#,##0;$-#,##0
//6 Currency $#,##0;[Red]$-#,##0
//7 Currency $#,##0.00;$-#,##0.00
//8 Currency $#,##0.00;[Red]$-#,##0.00
//9 Percentage 0 %
//10 Percentage 0.00 %
//11 Scientific 0.00E+00
//12 Fraction # ?/?
//13 Fraction # /
//14 Date m/ d / yy
//15 Date d-mmm - yy
//16 Date d-mmm
//17 Date mmm-yy
//18 Time h:mm AM/ PM
//19 Time h:mm: ss AM/ PM
//20 Time h:mm
//21 Time h:mm: ss
//22 Time m/ d / yy h: mm
//37 Currency #,##0;-#,##0
//38 Currency #,##0;[Red]-#,##0
//39 Currency #,##0.00;-#,##0.00
//40 Currency #,##0.00;[Red]-#,##0.00
//41 Accounting _ * #,##0_ ;_ * "_ ;_ @_
//42 Accounting _ $* #,##0_ ;_ $* "_ ;_ @_
//43 Accounting _ * #,##0.00_ ;_ * "??_ ;_ @_
//44 Accounting _ $* #,##0.00_ ;_ $* "??_ ;_ @_
//45 Time mm:ss
//46 Time h :mm: ss
//47 Time mm:ss.0
//48 Scientific ##0.0E+00
//49 Text @
#endregion
// Obtain the existing Validations collection.
ValidationCollection validations = wb.Worksheets[sheet.TemplateSheetName].Validations;
// Create a validation object adding to the collection list.
Validation validation = validations[validations.Add()];
CellArea area;
switch (config.FieldType)
{
case "Text":
cStyle.Number = 49;
break;
case "Number":
cStyle.Number = 1;
if (config.Digit != 0)
{
cStyle.Custom = "0.".PadRight(config.Digit + 2, '0');
}
// Set the validation type.
validation.Type = ValidationType.Decimal;
// Specify the operator.
validation.Operator = OperatorType.Between;
// Set the lower and upper limits.
validation.Formula1 = Decimal.MinValue.ToString();
validation.Formula2 = Decimal.MaxValue.ToString();
// Set the error message.
validation.ErrorMessage = "";
// Specify the validation area of cells.
area.StartRow = sheet.RowNum;
area.EndRow = 1048575;
area.StartColumn = startIndex;
area.EndColumn = startIndex;
// Add the area.
validation.AreaList.Add(area);
break;
case "DateTiem":
cStyle.Number = 15;
cStyle.Custom = "yyyy-m-d";
// Set the data validation type.
validation.Type = ValidationType.Date;
// Set the operator for the data validation
validation.Operator = OperatorType.Between;
// Set the value or expression associated with the data validation.
validation.Formula1 = "1970-1-1";
// The value or expression associated with the second part of the data validation.
validation.Formula2 = "2099-12-31";
// Enable the error.
validation.ShowError = true;
// Set the validation alert style.
validation.AlertStyle = ValidationAlertType.Stop;
// Set the title of the data-validation error dialog box
validation.ErrorTitle = "r";
// Set the data validation error message.
validation.ErrorMessage = "";
// Set and enable the data validation input message.
validation.InputMessage = "";
validation.IgnoreBlank = true;
validation.ShowInput = true;
// Set a collection of CellArea which contains the data validation settings.
area.StartRow = sheet.RowNum;
area.EndRow = 1048575;
area.StartColumn = startIndex;
area.EndColumn = startIndex;
validation.AreaList.Add(area);
break;
}
styleFlag = new StyleFlag()
{
NumberFormat = true, VerticalAlignment = true, HorizontalAlignment = true, FontSize = true, FontColor = true
};
var colors = config.BGColor.Split(',');
cStyle.ForegroundColor = System.Drawing.Color.FromArgb(int.Parse(colors[0]), int.Parse(colors[1]), int.Parse(colors[2]));
cStyle.Pattern = BackgroundType.Solid;
if (!isFile)
{
wb.Worksheets[sheet.TemplateSheetName].Cells.ApplyColumnStyle(startIndex, cStyle, styleFlag);
wb.Worksheets[sheet.TemplateSheetName].Cells[0, 0].SetStyle(style);
wb.Worksheets[sheet.TemplateSheetName].Cells[sheet.RowNum - 1, startIndex].PutValue(config.FieldName);
wb.Worksheets[sheet.TemplateSheetName].Cells[sheet.RowNum - 1, startIndex].SetStyle(titleStyle);
}
if (config.BGColor != "255,255,255")
{
wb.Worksheets[sheet.TemplateSheetName].Cells[sheet.RowNum - 1, startIndex].SetStyle(cStyle);
}
var selectedValues = selects.FindAll(x => x.TemplateConfigID == config.ID);// TemplateConfigSelectOperator.Instance.GetList(sheet.TemplateID, sheet.ID, config.ID);
if (selectedValues.Count > 0)
{
var hidSheet = wb.Worksheets["勿删除"];
if (hidSheet == null)
{
hidSheet = wb.Worksheets.Add("勿删除");
}
hidSheet.IsVisible = false;
Range range = hidSheet.Cells.CreateRange(50, itemConfigs.Count + 10 + startIndex, selectedValues.Count, 1);
range.Name = "MyRange" + sheet.TemplateSheetName + startIndex;
int i = 0;
foreach (var item in selectedValues)
{
range[i, 0].PutValue(item.SelectedValue);
i++;
}
validation = validations[validations.Add()];
validation.Type = Aspose.Cells.ValidationType.List;
validation.Operator = OperatorType.None;
validation.InCellDropDown = true;
validation.Formula1 = "=MyRange" + sheet.TemplateSheetName + startIndex;
validation.ShowError = true;
validation.AlertStyle = ValidationAlertType.Stop;
validation.ErrorTitle = "Error";
validation.ErrorMessage = "";
area.StartRow = sheet.RowNum;
area.EndRow = 1048575;
area.StartColumn = startIndex;
area.EndColumn = startIndex;
validation.AreaList.Add(area);
}
startIndex++;
}
if (!isFile)
{
wb.Worksheets[sheet.TemplateSheetName].AutoFilter.SetRange(sheet.RowNum - 1, sheet.ColumnNum - 1, itemConfigs.Count - 1);
}
}
wb.FileName = template.TemplateName;
if (wb.Worksheets.Count > 0 && wb.Worksheets[0].IsVisible)
{
wb.Worksheets.ActiveSheetIndex = 0;
}
return(wb);
}
