public static void FormulaTransform(Aspose.Cells.Cell src, Aspose.Cells.Cell dest)
{
src.R1C1Formula = "IF(F6>$C$6,\"NG\",\"OK\")";
var for1 = src.R1C1Formula;
dest.R1C1Formula = for1;
}
protected override List <Cell> GetCells(int rowIndex, int maxColEnd)
{
List <Cell> result = new List <Cell>();
Aspose.Cells.Row row = worksheet.Cells.Rows[rowIndex];
Aspose.Cells.Cell firstCell = row.FirstCell;
Aspose.Cells.Cell lastCell = row.LastCell;
if (lastCell == null)
{
return(result);
}
for (int i = 0; i <= lastCell.Column; i++)
{
if (i >= maxColEnd)
{
break;
}
Aspose.Cells.Cell cell = row.GetCellOrNull(i);
result.Add(new AsposeExcelCell(cell, worksheet));
if (cell != null && cell.IsMerged && cell.GetMergedRange().ColumnCount > 1)
{
i += cell.GetMergedRange().ColumnCount - 1;
}
}
return(result);
}
public void test() // 测试结果, 暂时无法在 Xamarin.Android 中授权, 能够读取Excel文件内容
{
//string LData = "PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz4NCjxMaWNlbnNlPg0KICAgIDxEYXRhPg0KICAgICAgICA8TGljZW5zZWRUbz5pckRldmVsb3BlcnMuY29tPC9MaWNlbnNlZFRvPg0KICAgICAgICA8RW1haWxUbz5pbmZvQGlyRGV2ZWxvcGVycy5jb208L0VtYWlsVG8+DQogICAgICAgIDxMaWNlbnNlVHlwZT5EZXZlbG9wZXIgT0VNPC9MaWNlbnNlVHlwZT4NCiAgICAgICAgPExpY2Vuc2VOb3RlPkxpbWl0ZWQgdG8gMTAwMCBkZXZlbG9wZXIsIHVubGltaXRlZCBwaHlzaWNhbCBsb2NhdGlvbnM8L0xpY2Vuc2VOb3RlPg0KICAgICAgICA8T3JkZXJJRD43ODQzMzY0Nzc4NTwvT3JkZXJJRD4NCiAgICAgICAgPFVzZXJJRD4xMTk0NDkyNDM3OTwvVXNlcklEPg0KICAgICAgICA8T0VNPlRoaXMgaXMgYSByZWRpc3RyaWJ1dGFibGUgbGljZW5zZTwvT0VNPg0KICAgICAgICA8UHJvZHVjdHM+DQogICAgICAgICAgICA8UHJvZHVjdD5Bc3Bvc2UuVG90YWwgUHJvZHVjdCBGYW1pbHk8L1Byb2R1Y3Q+DQogICAgICAgIDwvUHJvZHVjdHM+DQogICAgICAgIDxFZGl0aW9uVHlwZT5FbnRlcnByaXNlPC9FZGl0aW9uVHlwZT4NCiAgICAgICAgPFNlcmlhbE51bWJlcj57RjJCOTcwNDUtMUIyOS00QjNGLUJENTMtNjAxRUZGQTE1QUE5fTwvU2VyaWFsTnVtYmVyPg0KICAgICAgICA8U3Vic2NyaXB0aW9uRXhwaXJ5PjIwOTkxMjMxPC9TdWJzY3JpcHRpb25FeHBpcnk+DQogICAgICAgIDxMaWNlbnNlVmVyc2lvbj4zLjA8L0xpY2Vuc2VWZXJzaW9uPg0KICAgIDwvRGF0YT4NCiAgICA8U2lnbmF0dXJlPlFYTndiM05sTGxSdmRHRnNMb1B5YjJSMVkzUWdSbUZ0YVd4NTwvU2lnbmF0dXJlPg0KPC9MaWNlbnNlPg==";
//System.IO.Stream stream = new System.IO.MemoryStream(Convert.FromBase64String(LData));
//stream.Seek(0, System.IO.SeekOrigin.Begin);
//Aspose.Cells.License license = new Aspose.Cells.License();
//license.SetLicense(stream);
try
{
string path = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Aspose.xlsx");
StringBuilder sb = new StringBuilder();
Aspose.Cells.Workbook wb = null;
if (System.IO.File.Exists(path) == false)
{
wb = new Aspose.Cells.Workbook();
}
else
{
wb = new Aspose.Cells.Workbook(path);
}
wb = new Aspose.Cells.Workbook();
if (wb != null)
{
bool isLicensed = wb.IsLicensed;
if (isLicensed == false)
{
sb.AppendLine("Run Time {0} : isLicensed = false;".FormatWith(0));
}
if (wb.Worksheets.Count > 1)
{
sb.AppendLine("Run Time {0} : Worksheets Count = {1};".FormatWith(0, wb.Worksheets.Count));
}
var ws = wb.Worksheets[0];
Aspose.Cells.Cell cell0 = ws.Cells[0, 0];
string msg = "{0}".FormatWith(cell0.Value);
System.Diagnostics.Debug.WriteLine(msg);
cell0.Value = "A1 hello";
// 保存
string savePath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Aspose_Save.xlsx");
wb.Save(savePath);
}
}
catch (Exception ex)
{
string msg = "{0}".FormatWith(ex.GetInfo());
System.Diagnostics.Debug.WriteLine(msg);
}
}
private Aspose.Cells.Cell GetDown(Aspose.Cells.Worksheet sheet, Aspose.Cells.Cell cell)
{
var row = cell.Row;
var col = cell.Column;
Aspose.Cells.Cell c = null;
while ((c = sheet.Cells[++row, col]) == null)
{
;
}
return(c);
}
private void FormatCell(Aspose.Cells.Cell cell)
{
cell.Style.Borders[Aspose.Cells.BorderType.BottomBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.LeftBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.RightBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.TopBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.IsTextWrapped = true;
}
private void FormatCellInGreen(Aspose.Cells.Cell cell)
{
cell.Style.Borders[Aspose.Cells.BorderType.BottomBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.LeftBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.RightBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.TopBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
cell.Style.Pattern = Aspose.Cells.BackgroundType.Solid;
}
private void FormatDateCell(Aspose.Cells.Cell cell)
{
cell.Style.Borders[Aspose.Cells.BorderType.BottomBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.LeftBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.RightBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.TopBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
// TODO: add date formatting information
cell.Style.Custom = "yy 年 mm 月 dd 日 hh : mm : ss";
}
private void FormatNumberCell(Aspose.Cells.Cell cell)
{
cell.Style.Borders[Aspose.Cells.BorderType.BottomBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.LeftBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.RightBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.TopBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.IsTextWrapped = true;
cell.Style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
}
public override List <Cell> GetCells(int rowIndex, int maxColEnd = IAdapter.MaxColumnsCount)
{
List <Cell> result = new List <Cell>();
Aspose.Cells.Row row = worksheet.Cells.Rows[rowIndex];
Aspose.Cells.Cell firstCell = row.FirstCell;
Aspose.Cells.Cell lastCell = row.LastCell;
if (lastCell == null)
{
return(result);
}
for (int i = 0; i <= lastCell.Column; i++)
{
if (i >= maxColEnd)
{
break;
}
Aspose.Cells.Cell cell = row.GetCellOrNull(i);
result.Add(new AsposeExcelCell(cell, worksheet));
if (cell != null && cell.IsMerged && cell.GetMergedRange().ColumnCount > 1)
{
i += cell.GetMergedRange().ColumnCount - 1;
}
}
/*
* IEnumerator enumerator = worksheet.Cells.Rows[rowIndex].GetEnumerator();
* int range_end = -1;
* while (enumerator.MoveNext())
* {
* Aspose.Cells.Cell cell = (Aspose.Cells.Cell)enumerator.Current;
* if (cell.Column < range_end)
* {
* index++;
* continue;
* }
*
* result.Add(new AsposeExcelCell(cell));
*
* if (cell.IsMerged)
* {
* int first = cell.GetMergedRange().FirstColumn;
* int count = cell.GetMergedRange().ColumnCount;
* range_end = first + count;
* }
* index++;
* }
*/
return(result);
}
private void FormatCellInBlue(Aspose.Cells.Cell cell)
{
//cell.Style.Borders = .SetStyle(Aspose.Cells.CellBorderType.Thin);
// cell.Style.Borders.SetColor(System.Drawing.Color.Black);
cell.Style.Borders[Aspose.Cells.BorderType.BottomBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.LeftBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.RightBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.Borders[Aspose.Cells.BorderType.TopBorder].Color = System.Drawing.Color.Black;
cell.Style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
cell.Style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 255);
cell.Style.Pattern = Aspose.Cells.BackgroundType.Solid;
}
public AsposeExcelCell(Aspose.Cells.Cell cell, Aspose.Cells.Worksheet worksheet)
{
if (cell == null)
{
return;
}
IsEmpty = cell.Type == Aspose.Cells.CellValueType.IsNull;
// nobody wants to know how excel represents numbers inside itself
// for "size_raw"
Text = cell.GetStringValue(Aspose.Cells.CellValueFormatStrategy.DisplayStyle);
if (Text == "###")
{
Text = cell.StringValue;
}
IsMerged = cell.IsMerged;
if (IsMerged)
{
FirstMergedRow = cell.GetMergedRange().FirstRow;
MergedRowsCount = cell.GetMergedRange().RowCount;
MergedColsCount = cell.GetMergedRange().ColumnCount;
}
else
{
MergedColsCount = 1;
MergedRowsCount = 1;
FirstMergedRow = cell.Row;
}
Row = cell.Row;
Col = cell.Column;
CellWidth = 0;
for (int i = 0; i < MergedColsCount; i++)
{
//test File17207: GetColumnWidthPixel returns 45, GetColumnWidth returns 0 for the same cell
CellWidth += (int)worksheet.Cells.GetColumnWidthPixel(cell.Column + i);
}
}
/// <summary>
/// 开始处理文件
/// </summary>
private DataLayer.Model.FileReplace_File DoIt(DataLayer.Model.FileReplace_File model)
{
Stopwatch sw = new Stopwatch();
sw.Start();
int replaceCount = 0;
Regex reg = null;
List <string> strRemark = new List <string>();
model.IsDone = true;
Aspose.Cells.Cell currentCell = null;
Aspose.Cells.CellValueType currentCellType;
string cellValue = string.Empty;
bool tempBool;
int tempInt;
double tempDouble;
DateTime tempDateTime;
bool isCellReplaced = false;
int cellMatchCount;
bool isDefaultExt = defaultExt.Contains(model.ExtensionName);
bool isExcelExt = excelExt.Contains(model.ExtensionName);
bool isDocExt = docExt.Contains(model.ExtensionName);
//bool isPPTExt = pptExt.Contains(model.ExtensionName);
//bool isPdfExt = pdfExt.Contains(model.ExtensionName);
bool isTxtFile = XCLNetTools.FileHandler.ComFile.IsTextFile(model.Path);
bool isNeedCopy = !string.IsNullOrEmpty(this.txtOutPutPath.Text);
string realPath = model.Path; //被操作的文件实际路径，如果没有指定输出目录，则为原路径，如果指定了输出目录，则为copy到输出目录中后的路径
string filetitle = XCLNetTools.FileHandler.ComFile.GetFileName(model.Path, false); //文件名，不含扩展名
string newFileTitle = string.Empty;
if (!System.IO.File.Exists(model.Path))
{
model.Remark = "文件不存在！";
model.ProcessState = (int)DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.无需处理;
return(model);
}
if (string.IsNullOrEmpty(model.ExtensionName))
{
model.Remark = "无法确认文件类型！";
model.ProcessState = (int)DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.无需处理;
return(model);
}
try
{
#region 先处理替换文件名
for (int ruleIndex = 0; ruleIndex < this.dataGridRuleConfig.Rows.Count; ruleIndex++)
{
var ruleModel = XCLNetTools.Generic.ListHelper.DataRowToModel <DataLayer.Model.v_FileReplace_RuleConfig>(((DataRowView)dataGridRuleConfig.Rows[ruleIndex].DataBoundItem).Row);
if (null == ruleModel || !ruleModel.IsFileName)
{
continue;
}
#region 是否启用正则替换
if (ruleModel.IsRegex)
{
reg = ruleModel.IsIgnoreCase ? new Regex(ruleModel.OldContent, RegexOptions.IgnoreCase) : new Regex(ruleModel.OldContent);
}
else
{
string newExpStr = ruleModel.IsWholeMatch ? string.Format(@"\b{0}\b", Regex.Escape(ruleModel.OldContent)) : Regex.Escape(ruleModel.OldContent);
reg = ruleModel.IsIgnoreCase ? new Regex(newExpStr, RegexOptions.IgnoreCase) : new Regex(newExpStr);
}
#endregion 是否启用正则替换
#region 判断是否替换文件名
replaceCount = reg.Matches(filetitle).Count;
strRemark.Add(string.Format("规则【{0}】文件名替换【{1}】处；", ruleModel.Name, replaceCount));
filetitle = reg.Replace(filetitle, ruleModel.NewContent);
model.ProcessBlockCount += replaceCount;
#endregion 判断是否替换文件名
}
filetitle = string.Format("{0}{1}{2}", this.txtFileFirstName.Text, filetitle, this.txtFileLastName.Text);
bool isFileNameChanged = !string.Equals(XCLNetTools.FileHandler.ComFile.GetFileName(model.Path, false), filetitle, StringComparison.OrdinalIgnoreCase);
if (isNeedCopy)
{
if (isFileNameChanged)
{
realPath = XCLNetTools.FileHandler.ComFile.GetFileFolderPath(model.Path) + "\\" + filetitle + "." + model.ExtensionName;
}
realPath = realPath.Replace(this.openFileFolderPath.TrimEnd('\\'), this.txtOutPutPath.Text.TrimEnd('\\'));
XCLNetTools.FileHandler.ComFile.CopyFile(model.Path, realPath);
if (!System.IO.File.Exists(realPath))
{
model.Remark = "复制到输出目录执行失败！";
model.ProcessState = (int)DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.处理失败;
return(model);
}
}
else
{
if (isFileNameChanged)
{
newFileTitle = filetitle + "." + model.ExtensionName;
this.pc.FileSystem.RenameFile(model.Path, newFileTitle);
realPath = XCLNetTools.FileHandler.ComFile.ChangePathByFileName(realPath, newFileTitle);
}
}
#endregion 先处理替换文件名
#region 替换文件内容
Aspose.Cells.Workbook wb = null;
Aspose.Words.Document wordDocument = null;
string textContent = null;
for (int ruleIndex = 0; ruleIndex < this.dataGridRuleConfig.Rows.Count; ruleIndex++)
{
var ruleModel = XCLNetTools.Generic.ListHelper.DataRowToModel <DataLayer.Model.v_FileReplace_RuleConfig>(((DataRowView)dataGridRuleConfig.Rows[ruleIndex].DataBoundItem).Row);
if (null == ruleModel || !ruleModel.IsFileContent)
{
continue;
}
#region 验证扩展名及是否为文本文件
if (!isDefaultExt && !isTxtFile && ruleModel.IsFileContent)
{
//非aspose能处理的文件，且非文本文件，则不能替换内容！
strRemark.Add(string.Format("规则【{0}】不支持替换该文件的内容！", ruleModel.Name));
continue;
}
#endregion 验证扩展名及是否为文本文件
#region 是否启用正则替换
if (ruleModel.IsRegex)
{
reg = ruleModel.IsIgnoreCase ? new Regex(ruleModel.OldContent, RegexOptions.IgnoreCase) : new Regex(ruleModel.OldContent);
}
else
{
string newExpStr = ruleModel.IsWholeMatch ? string.Format(@"\b{0}\b", Regex.Escape(ruleModel.OldContent)) : Regex.Escape(ruleModel.OldContent);
reg = ruleModel.IsIgnoreCase ? new Regex(newExpStr, RegexOptions.IgnoreCase) : new Regex(newExpStr);
}
#endregion 是否启用正则替换
#region 开始替换文件内容
if (isDefaultExt)
{
if (isExcelExt)
{
#region 处理excel文件
if (null == wb)
{
wb = new Aspose.Cells.Workbook(realPath);
}
for (int i = 0; i < wb.Worksheets.Count; i++)
{
Aspose.Cells.Cells sheetCells = wb.Worksheets[i].Cells;
for (int cellsRowIndex = 0; cellsRowIndex < sheetCells.MaxDataRow + 1; cellsRowIndex++)
{
for (int cellsColumn = 0; cellsColumn < sheetCells.MaxDataColumn + 1; cellsColumn++)
{
currentCell = sheetCells[cellsRowIndex, cellsColumn];
if (currentCell.IsFormula && this.ckExcelOptionIsKeepFormula.Checked)
{
continue;
}
cellValue = Convert.ToString(currentCell.Value);
if (string.IsNullOrEmpty(cellValue))
{
continue;
}
cellMatchCount = reg.Matches(cellValue).Count;
if (cellMatchCount == 0)
{
continue;
}
var cellCharacters = currentCell.GetCharacters();
cellValue = reg.Replace(cellValue, ruleModel.NewContent);
currentCellType = this.ckExcelOptionIsKeepDataFormat.Checked ? currentCell.Type : Aspose.Cells.CellValueType.IsString;
switch (currentCellType)
{
case Aspose.Cells.CellValueType.IsBool:
if (bool.TryParse(cellValue, out tempBool))
{
currentCell.PutValue(tempBool);
isCellReplaced = true;
}
break;
case Aspose.Cells.CellValueType.IsDateTime:
if (DateTime.TryParse(cellValue, out tempDateTime))
{
currentCell.PutValue(tempDateTime);
isCellReplaced = true;
}
break;
case Aspose.Cells.CellValueType.IsNumeric:
if (int.TryParse(cellValue, out tempInt))
{
currentCell.PutValue(tempInt);
isCellReplaced = true;
}
else
{
if (double.TryParse(cellValue, out tempDouble))
{
currentCell.PutValue(tempDouble);
isCellReplaced = true;
}
}
break;
default:
currentCell.PutValue(cellValue);
isCellReplaced = true;
break;
}
if (isCellReplaced)
{
if (null != cellCharacters && cellCharacters.Length > 0)
{
currentCell.SetCharacters(cellCharacters);
}
replaceCount += cellMatchCount;
}
}
}
}
#endregion 处理excel文件
}
else if (isDocExt)
{
#region 处理word
if (null == wordDocument)
{
wordDocument = new Aspose.Words.Document(realPath);
}
var finder = new FindReplaceOptions();
replaceCount = wordDocument.Range.Replace(reg, ruleModel.NewContent, finder);
#endregion 处理word
}
//else if (isPPTExt)
//{
// #region 处理PPT
// Aspose.Slides.Pptx.PresentationEx pptPres = new Aspose.Slides.Pptx.PresentationEx(realPath);
// #endregion
//}
//else if (isPdfExt)
//{
// #region 处理pdf文件
// Aspose.Pdf.Kit.PdfContentEditor pdfEditor = new Aspose.Pdf.Kit.PdfContentEditor();
// pdfEditor.BindPdf(realPath);
// pdfEditor.ReplaceText(this.txtOldValue.Text, this.txtNew.Text);
// pdfEditor.Save(realPath);
// #endregion
//}
}
else
{
#region 处理文本文件
if (null == textContent)
{
textContent = System.IO.File.ReadAllText(realPath, System.Text.Encoding.Default) ?? "";
}
replaceCount = reg.Matches(textContent).Count;
textContent = reg.Replace(textContent, ruleModel.NewContent);
#endregion 处理文本文件
}
strRemark.Add(string.Format("规则【{0}】文件内容替换【{1}】处；", ruleModel.Name, replaceCount));
#endregion 开始替换文件内容
this.SetTextLogValue(string.Format("正在处理文件【{0}】，应用规则【{1}】", model.FileName, ruleModel.Name));
model.ProcessBlockCount += replaceCount;
}
if (null != wb)
{
if (this.ckExcelOptionIsKeepFormula.Checked)
{
wb.CalculateFormula();
}
wb.Save(realPath);
}
if (null != wordDocument)
{
wordDocument.Save(realPath);
}
if (null != textContent)
{
System.IO.File.WriteAllText(realPath, textContent, System.Text.Encoding.Default);
}
#endregion 替换文件内容
if (strRemark.Count > 0)
{
model.Remark = string.Join("；", strRemark.ToArray());
}
model.ProcessState = (int)DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.处理成功;
}
catch (Exception e)
{
model.ProcessState = (int)DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.处理失败;
model.Remark = e.Message;
}
finally
{
sw.Stop();
model.ProcessDuration = (int)sw.Elapsed.TotalSeconds;
}
if (string.IsNullOrWhiteSpace(model.Remark))
{
this.SetTextLogValue(string.Format("文件【{0}】处理完毕", model.FileName));
}
else
{
this.SetTextLogValue(string.Format("文件【{0}】处理完毕（{1}）", model.FileName, model.Remark));
}
return(model);
}
public override Cell GetCell(int row, int column)
{
Aspose.Cells.Cell cell = worksheet.Cells.GetCell(row, column);
return(new AsposeExcelCell(cell, worksheet));
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
public ActionResult ExportData(string queryJson)
{
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 100000000;
pagination.p_kid = "a.Id";
pagination.p_fields = "c.itemname as applystatename,b.itemname as worktype,workplace,workcontent,to_char(workstarttime,'yyyy-mm-dd hh24:mi') || ' - '||to_char(workendtime,'yyyy-mm-dd hh24:mi'),applyusername,applydeptname,to_char(a.createdate,'yyyy-mm-dd hh24:mi')";
pagination.p_tablename = " bis_highriskapply a left join base_dataitemdetail b on a.worktype=b.itemvalue and b.itemid =(select itemid from base_dataitem where itemcode='WorkType') left join base_dataitemdetail c on a.applystate=c.itemvalue and c.itemid =(select itemid from base_dataitem where itemcode='WorkStatus')";
pagination.conditionJson = "1=1";
pagination.sidx = "a.createdate";
pagination.sord = "desc";
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (!user.IsSystem)
{
string authType = new AuthorizeBLL().GetOperAuthorzeType(user, HttpContext.Request.Cookies["currentmoduleId"].Value, "search");
if (!string.IsNullOrEmpty(authType))
{
switch (authType)
{
case "1":
pagination.conditionJson += " and applyuserid='" + user.UserId + "'";
break;
case "2":
pagination.conditionJson += " and ApplyDeptCode='" + user.DeptCode + "'";
break;
case "3": //本子部门
pagination.conditionJson += string.Format(" and ApplyDeptCode in(select encode from base_department where encode like '{0}%' or senddeptid='{1}')", user.DeptCode, user.DeptId);
break;
case "4":
pagination.conditionJson += " and a.createuserorgcode='" + user.OrganizeCode + "'";
break;
}
}
else
{
pagination.conditionJson += " and 0=1";
}
}
DataTable exportTable = highriskapplybll.GetPageDataTable(pagination, queryJson);
exportTable.Columns.Remove("id");
exportTable.Columns.Remove("r");
// 确定导出文件名
string fileName = "高风险作业许可申请信息";
HttpResponse resp = System.Web.HttpContext.Current.Response;
// 详细列表内容
string fielname = fileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/高风险作业许可申请信息.xlsx"));
Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
Aspose.Cells.Cell cell = sheet.Cells[1, 1];
sheet.Cells.ImportDataTable(exportTable, false, 1, 0);
wb.Save(Server.UrlEncode(fielname), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp);
return(Success("导出成功!"));
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
public ActionResult ExportData(string queryJson)
{
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 100000000;
pagination.p_kid = "a.Id as workid";
pagination.p_fields = "supervisestate,taskworktype,handtype,to_char(TaskWorkStartTime,'yyyy-mm-dd hh24:mi') || ' - ' || to_char(TaskWorkStartTime,'yyyy-mm-dd hh24:mi'),'' as taskdept,'' as taskworkplace,b.fullname,taskusername,'-' as timelong";
pagination.p_tablename = "bis_supervisetask a left join Base_Department b on a.steamid=b.departmentid";
pagination.conditionJson = "1=1";
pagination.sidx = "supervisestate asc,a.createdate";
pagination.sord = "desc";
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (!user.IsSystem)
{
string authType = new AuthorizeBLL().GetOperAuthorzeType(user, HttpContext.Request.Cookies["currentmoduleId"].Value, "search");
if (!string.IsNullOrEmpty(authType))
{
switch (authType)
{
case "1":
pagination.conditionJson += " and a.createuserid='" + user.UserId + "'";
break;
case "2":
pagination.conditionJson += " and a.createuserdeptcode='" + user.DeptCode + "'";
break;
case "3": //本子部门
pagination.conditionJson += string.Format(" and ((a.steamid in(select departmentid from base_department where encode like '{0}%' or senddeptid='{1}') and supervisestate!='1') or a.createuserid='{2}')", user.DeptCode, user.DeptId, user.UserId);
break;
case "4":
pagination.conditionJson += " and ((a.createuserorgcode='" + user.OrganizeCode + "' and supervisestate!='1') or(a.createuserid='" + user.UserId + "'))";
break;
}
}
else
{
pagination.conditionJson += " and 0=1";
}
}
DataTable exportTable = supervisetaskbll.GetPageDataTable(pagination, queryJson);
foreach (DataRow item in exportTable.Rows)
{
if (item["supervisestate"].ToString() == "1")
{
item["supervisestate"] = "创建监督";
}
else if (item["supervisestate"].ToString() == "2")
{
item["supervisestate"] = "未监督";
}
else
{
item["supervisestate"] = "已监督";
}
var type = "";
if (!string.IsNullOrEmpty(item["taskworktype"].ToString()) && !string.IsNullOrEmpty(item["handtype"].ToString()))
{
type = item["taskworktype"].ToString() + "," + item["handtype"].ToString();
}
else
{
if (!string.IsNullOrEmpty(item["taskworktype"].ToString()))
{
type = item["taskworktype"].ToString();
}
if (!string.IsNullOrEmpty(item["handtype"].ToString()))
{
type = item["handtype"].ToString();
}
}
item["taskworktype"] = type;
var workdata = superviseworkinfobll.GetList(item["workid"].ToString());
string place = "", deptname = "";
foreach (var work in workdata)
{
place += work.WorkPlace + ",";
deptname += work.WorkDeptName + ",";
}
if (!string.IsNullOrEmpty(place))
{
item["taskworkplace"] = place.TrimEnd(',');
}
if (!string.IsNullOrEmpty(deptname))
{
item["taskdept"] = deptname.TrimEnd(',');
}
}
exportTable.Columns.Remove("workid");
exportTable.Columns.Remove("handtype");
exportTable.Columns.Remove("r");
// 确定导出文件名
string fileName = "高风险作业旁站监督信息";
HttpResponse resp = System.Web.HttpContext.Current.Response;
// 详细列表内容
string fielname = fileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/高风险作业旁站监督信息.xlsx"));
Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
Aspose.Cells.Cell cell = sheet.Cells[1, 1];
sheet.Cells.ImportDataTable(exportTable, false, 1, 0);
wb.Save(Server.UrlEncode(fielname), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp);
return(Success("导出成功!"));
}
///<Summary>
/// SearchQuery
///</Summary>
public void SearchQuery(string fileName, string folderName, string query, string outPath)
{
var fn = Config.Configuration.WorkingDirectory + folderName + "/" + fileName;
// Load the input Excel file.
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(fn);
// Specify the find options.
Aspose.Cells.FindOptions opts = new Aspose.Cells.FindOptions();
opts.LookAtType = Aspose.Cells.LookAtType.Contains;
opts.LookInType = Aspose.Cells.LookInType.Values;
string findText = query;
System.Text.StringBuilder found = new System.Text.StringBuilder();
found.AppendLine("Searched Text: " + query);
found.AppendLine("===========================================");
found.AppendLine();
//Check if found nothing
this.m_FoundNothing = true;
Aspose.Cells.Cell cell = null;
for (int i = 0; i < wb.Worksheets.Count; i++)
{
Aspose.Cells.Worksheet ws = wb.Worksheets[i];
found.AppendLine("Sheet Name: " + ws.Name);
found.AppendLine();
do
{
cell = ws.Cells.Find(findText, cell);
if (cell == null)
{
break;
}
this.m_FoundNothing = false;
found.AppendLine("Cell Name: " + cell.Name);
found.AppendLine("Cell Value: " + cell.StringValue);
found.AppendLine();
} while (true);
found.AppendLine("===========================================");
found.AppendLine();
}
if (this.m_FoundNothing)
{
string dn = System.IO.Path.GetDirectoryName(outPath);
System.IO.Directory.Delete(dn);
return;
}
string strFound = found.ToString();
System.IO.File.WriteAllText(outPath, strFound);
}
