/// <summary>
/// 执行合并
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void OK_Click(object sender, RoutedEventArgs e)
{
///list<object> 转换成正确类型
IList <FileNameCustom> currencies = model.FileNameCustoms;
model.SetPaths(currencies);
int count = model.FileNameCustoms.Count;
for (int a = 0; a < count; a++)
{
FileNameCustom custom = model.FileNameCustoms[a];
if (!Utils.CheckFileExists(custom.FilePath))
{
model.FileNameCustoms.RemoveAt(a);
a--;
count--;
}
}
MyAction myAction = new MyAction(new Action(() =>
{
ExcelWrite.MergeExcel(model);
}), "合并表格", "完成");
CommHelper.FastTask(myAction);
SoftwareConfig.SaveRedis(ExcelMergeModel.RedisKey, model);
}
private void OpenFile_Click(object sender, RoutedEventArgs e)
{
FileNameCustom fileNameCustom = addFilePagerPage.GetTabGrid().CurrentItem as FileNameCustom;
if (fileNameCustom != null)
{
string path = fileNameCustom.FilePath;
FileUtils.OpenFile(path);
}
}
/// <summary>
/// 替换word中的内容
/// </summary>
/// <param name="replaceViewModels"></param>
/// <param name="fileNameCustom"></param>
public static void ReplaceText(IList <ReplaceViewModel> replaceViewModels, FileNameCustom fileNameCustom)
{
string path = fileNameCustom.FilePath;
if (Utils.CheckFileExists(path))
{
Document doc = new Document(path);
ReplaceText(replaceViewModels, doc);
doc.Save(path);
}
}
public void AddFile(IList <string> files)
{
if (!Utils.CheckListExists(files))
{
return;
}
for (int a = 0; a < files.Count; a++)
{
FileNameCustom custom = new FileNameCustom(files[a]);
model.FileNameCustoms.Add(custom);
}
}
/// <summary>
/// 手动添加文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void AddFile_Click(object sender, RoutedEventArgs e)
{
IList <string> paths = FileUtils.SelectFiles(Filter, "可以多选文件");
if (MyUtils.Utils.CheckListExists(paths))
{
IList <FileNameCustom> fs = new List <FileNameCustom>();
foreach (string path in paths)
{
if (MyUtils.Utils.CheckFileExists(path))
{
FileNameCustom fileNameCustom = new FileNameCustom(path);
fs.Add(fileNameCustom);
}
}
addFilePagerPage.AddObject(fs);
}
}
public virtual void Init(AddFileViewModel model, string Filter, bool isShowSelect = false, Visibility SelectDirIsShow = Visibility.Visible)
{
this.model = model;
this.DataContext = model;
addFilePagerPage.SetPagerPage(model.Files, FileNameCustom.GetFieldCustoms(), true, isShowSelect);
this.Filter = Filter;
pagerPageGrid.Children.Add(addFilePagerPage);
selectDirStackPanel.Visibility = SelectDirIsShow;
addFilePagerPage.HideFloot();
ContextMenu menu = new ContextMenu();
MenuItem taskAllocationMenu = new MenuItem();
taskAllocationMenu.Header = "打开文件";
taskAllocationMenu.Click += OpenFile_Click;
menu.Items.Add(taskAllocationMenu);
addFilePagerPage.ContextMenu = menu;
}
public void AddFile(IList <string> files2)
{
IList <FileNameCustom> fileNameCustoms = new List <FileNameCustom>();
if (!Utils.CheckListExists(files2))
{
return;
}
if (Utils.IsStrNull(Filter))
{
for (int a = 0; a < files2.Count; a++)
{
FileNameCustom custom = new FileNameCustom(files2[a]);
fileNameCustoms.Add(custom);
}
addFilePagerPage.AddObject(fileNameCustoms);
return;
}
IList <string> files = new List <string>();
int count = files2.Count;
string[] filters = FileterToEndWith();
for (int a = 0; a < count; a++)
{
string path = files2[a];
string ex = System.IO.Path.GetExtension(path);
if (path != null && Array.IndexOf(filters, ex) != -1)
{
files.Add(path);
}
}
for (int a = 0; a < files.Count; a++)
{
FileNameCustom custom = new FileNameCustom(files[a]);
fileNameCustoms.Add(custom);
}
addFilePagerPage.AddObject(fileNameCustoms);
}
/// <summary>
/// sheet合并到一个Excel中
/// </summary>
/// <param name="model"></param>
public static void MergeExcel(ExcelMergeModel model)
{
string saveName = model.SaveName;
if (saveName == null)
{
MessageBox.Show("保存路径没有填写！！！");
return;
}
Workbook newbook = new Workbook();
//newbook.SaveToFile("d:/123.xls", ExcelVersion.Version2013);
Spire.Xls.Workbook tempbook = null;
//创建一个新的workbook对象
newbook.Version = Spire.Xls.ExcelVersion.Version2013;
//删除文档中的工作表（新创建的文档默认包含3张工作表）
newbook.Worksheets.Clear();
if (newbook.Worksheets.Count == 0)
{
newbook.CreateEmptySheet();
}
newbook.SaveToFile(saveName, ExcelVersion.Version2013);
IList <FileNameCustom> fileNameCustoms = model.Files;
if (model.MergeModel == MergeSytle.MoreSheet)
{
tempbook = new Workbook();
foreach (FileNameCustom custom in fileNameCustoms)
{
tempbook.LoadFromFile(custom.FilePath);
//使用AddCopy方法，将文档中的所有工作表添加到新的workbook
foreach (Worksheet sheet in tempbook.Worksheets)
{
newbook.Worksheets.AddCopy(sheet);
}
}
newbook.SaveToFile(saveName, ExcelVersion.Version2013);
}
else if (model.MergeModel == MergeSytle.OneSheet)
{
//实例化一个Workbook类，加载Excel文档
for (int index = 0; index < fileNameCustoms.Count; index++)
{
FileNameCustom fileNameCustom = fileNameCustoms[index];
tempbook = new Workbook();
try
{
tempbook.LoadFromFile(fileNameCustom.FilePath);
}
catch (Exception e)
{
MessageBox.Show("文件有问题，可能有批注：" + fileNameCustom.FilePath);
continue;
}
for (int a = 0; a < tempbook.Worksheets.Count; a++)
{
//获取第1、2张工作表
Worksheet sheet1 = tempbook.Worksheets[a];
if (newbook.Worksheets.Count <= a)
{
newbook.CreateEmptySheet(sheet1.Name);
}
Worksheet newsheet = newbook.Worksheets[a];
//复制第2张工作表内容到第1张工作表的指定区1域中
CellRange range1 = sheet1.AllocatedRange;
if (range1.RowCount > model.ReduceStartRowCount + model.ReduceEndRowCount + 1)
{
//CellRange range = sheet1.Range[model.ReduceStartRowCount + 1, range1.CellsCount, range1.RowCount - model.ReduceEndRowCount, range1.CellsCount];
int cellCount = range1.CellsCount;
if (cellCount > 256)
{
cellCount = 254;
}
int rowCount = range1.RowCount;
if (rowCount > 20000)
{
rowCount = 20000;
}
CellRange range = sheet1.Range[model.ReduceStartRowCount + 1, 1, rowCount - model.ReduceEndRowCount, cellCount];
if (newsheet.LastRow == -1)
{
range.Copy(newsheet.Range[newsheet.LastRow + 2, 1]);
try
{
}
catch (Exception e)
{
MessageBox.Show("文件有问题：" + fileNameCustom.FilePath);
}
}
else
{
range.Copy(newsheet.Range[newsheet.LastRow + 1, 1]);
try
{
}
catch (Exception e)
{
MessageBox.Show("非常严重问题：" + fileNameCustoms[index - 1].FilePath);
return;
}
}
}
}
//break;
}
//保存并运行文档
newbook.SaveToFile(saveName, ExcelVersion.Version2013);
}
}
