多张单据导出标准套打时拆分为多个工作表

Dictionary fileDict = new Dictionary(); //存储单据及文件名称

#region 校验信息

if (ids.Count() == 0)
{
view.ShowErrMessage("请选择要导出的数据!");
return;
}
if (!ExistExcelApp())
{
view.ShowErrMessage("无法创建Excel对象,服务器上未安装Excel软件.");
return;
}

if (fileName.Trim().IsNullOrEmptyOrWhiteSpace())
{
view.ShowErrMessage("请输入要导出的文件名!");
return;
}
else
{
if (overWrite)
{
//重构文件名 + 随机码
fileName = string.Format("{0}_{1}", fileName, DateTime.Now.ToString("yyyyMMddHHmmssff"));
}
if (fileType == ExportFileType.XLS)
{
fileName += ".XLS";
}
else if (fileType == ExportFileType.XLSX)
{
fileName += ".XLSX";
}

fileName = PathUtils.GetValidFileName(fileName);
}

#endregion

#region 查找套打模板

Dictionary sourceDict = GetBatchTemplateIDs(view, ids.ToArray());
List keyList =
(from q in sourceDict where q.Value.IsNullOrEmptyOrWhiteSpace() select q.Key).ToList();
if (keyList.Count > 0)
{
view.ShowErrMessage(
ResManager.LoadKDString("您选择的数据没有设置套打模板,请设置后再导出.", "002012030026868", SubSystemType.BOS,
new object[0]), "", MessageBoxType.Notice);
return;
}

#endregion

System.Type orRegister =
TypesContainer.GetOrRegister("Kingdee.BOS.Web.Printing.NoteExportService,Kingdee.BOS.Web");
object obj = System.Activator.CreateInstance(orRegister);
System.Reflection.MethodInfo method = obj.GetType().GetMethod("GetExportFiles");

#region 导出格式为PDF 使用标准

if (fileType == ExportFileType.PDF)
{
#region 下载PDF

string[] array = method.Invoke(obj, new object[]
{
view, sourceDict.Keys.ToArray(), sourceDict.Values.ToArray(), fileType, exportType
}) as string[];

if (array != null && array.Length > 0)
{
DownFile(view, array[0]);
}

#endregion

return;
}

#endregion

#region 导出格式为Excel 的进行文件分 sheet 操作

Excel.Application app = null;
Excel._Workbook workbook = null;
string physicalPath = "";
string outServicePath = "";

try
{

#region 调用标准生成单个模板文件

foreach (var source in sourceDict)
{
string filePath = (method.Invoke(obj, new object[]
{
view, new string[] {source.Key}, new string[] {source.Value}, fileType, exportType
}) as string[]).ToList().FirstOrDefault();

fileDict.Add(source.Key, filePath);
}

#endregion

#region 合并模板文件

app = new Excel.Application();
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;

object missing = System.Reflection.Missing.Value;
workbook = app.Workbooks.Add();

foreach (var dict in fileDict)
{
//中文文件名乱码
string path = PathUtils.UrlDecode(PathUtils.GetPhysicalPath(KeyConst.TEMPFILEPATH,
Path.GetFileName(dict.Value)));
if (!File.Exists(path))
{
continue;
}

Excel._Workbook wb1 = app.Workbooks.Open(Path.GetFullPath(path), missing, true, missing, missing,
missing, missing, missing, missing, true, missing, missing, missing, missing, true);

//打开excel
foreach (Excel._Worksheet each in wb1.Sheets)
{
each.Copy(workbook.Worksheets[1]);
workbook.Worksheets[1].Name = dict.Key;
}

wb1.Close();//释放excel资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb1);
}

int num = workbook.Worksheets.Count;
if (num > 1)
{
((Excel.Worksheet) workbook.Worksheets[num]).Delete();
}

#endregion

physicalPath = PathUtils.GetPhysicalPath(KeyConst.TEMPFILEPATH, fileName);
outServicePath = PathUtils.GetServerPath(KeyConst.TEMPFILEPATH, fileName);

workbook.SaveAs(physicalPath);//保存
DownFile(view, outServicePath); //下载文件

}
catch (Exception exception2)
{
view.ShowErrMessage(exception2.Message, "", MessageBoxType.Notice);
}
finally
{
#region 删除标准生成的文件

foreach (var dict in fileDict)
{
//中文文件名乱码
string path = PathUtils.UrlDecode(PathUtils.GetPhysicalPath(KeyConst.TEMPFILEPATH,
Path.GetFileName(dict.Value)));
if (System.IO.File.Exists(path))
{
DelFile(path); //删除文件
}
}

#endregion

#region 释放excel 资源&程序

if (workbook != null)
{
workbook.Close();//释放excel资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
}
if (app != null)
{
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.GC.Collect();
}

#endregion
}

#endregion

}

#endregion

#region 判断本机是否安装Excel

//判断本机是否安装Excel 方法
private static bool ExistExcelApp()
{
Type type = Type.GetTypeFromProgID("Excel.Application");
return type != null;
}

#endregion

#region 删除文件

private static void DelFile(string path)
{
FileInfo file = new FileInfo(path);
if (file.Exists)
{
file.Delete(); //删除单个文件
}
}

#endregion

#region 弹出下载路径窗口

public static void DownFile(IDynamicFormView view, string outServicePath)
{
//下载文件
DynamicFormShowParameter param = new DynamicFormShowParameter();
param.FormId = "BOS_FileDownLoad";
param.OpenStyle.ShowType = ShowType.Modal;
param.CustomParams.Add("IsExportData", "true");
param.CustomParams.Add("url", outServicePath);
view.ShowForm(param);
}

#endregion

#region 获取模板Ids

public static Dictionary GetBatchTemplateIDs(IDynamicFormView view, string[] arrbillIds)
{
Dictionary dict = new Dictionary();
BillTypeField billTypeField = view.BillBusinessInfo.GetBillTypeField();
BillStatusField billStatusField = view.BillBusinessInfo.GetBillStatusField();
Field billNoField = view.BillBusinessInfo.GetBillNoField();
Form form = view.Model.BillBusinessInfo.GetForm();
string pkFieldName = form.PkFieldName;
Dictionary dictionary = new Dictionary();
DynamicObjectCollection source = GetStatusBillTypeObjs(view, arrbillIds, billTypeField, billStatusField,
billNoField);

for (int i = 0; i < arrbillIds.Count(); i++)
{
Func func = null;
Predicate match = null;
string currentBillId = arrbillIds[i];
if (dict.Keys.Contains(currentBillId)) continue;
DynamicObject obj2 = null;
if (source != null)
{
if (func == null)
{
func = p => p[pkFieldName].ToString().Equals(currentBillId);
}
obj2 = source.Where(func).FirstOrDefault();
}
string billTypeId = string.Empty;
if ((billTypeField != null) && (obj2 != null))
{
billTypeId = obj2[billTypeField.Key].ToString();
}
string templateIdFromSetting = GetTemplateIdFromSetting(view, billTypeId);
if (!billTypeId.IsNullOrEmpty() && templateIdFromSetting.IsNullOrEmpty())
{
if (match == null)
{
match = p => p["Id"].ToString().Equals(billTypeId);
}
DynamicObject obj3 = billTypeField.BillTypeInfo.Find(match);
if (obj3 != null)
{
templateIdFromSetting = (obj3["DefPrintTemplate"] == null)
? string.Empty
: obj3["DefPrintTemplate"].ToString();
}
}
if (templateIdFromSetting.IsNullOrEmptyOrWhiteSpace())
{
templateIdFromSetting = view.BillBusinessInfo.GetForm().Note;
}
bool flag = false;
if (!templateIdFromSetting.IsNullOrEmptyOrWhiteSpace())
{
if (!dictionary.Keys.Contains(templateIdFromSetting))
{
flag = IsNoteTemplateExist(view, templateIdFromSetting);
dictionary.Add(templateIdFromSetting, flag);
}
else
{
dictionary.TryGetValue(templateIdFromSetting, out flag);
}
}
if (flag)
{
dict.Add(currentBillId, templateIdFromSetting);
}
else
{
dict.Add(currentBillId, string.Empty);
}
}
return dict;
}

private static DynamicObjectCollection GetStatusBillTypeObjs(IDynamicFormView view, string[] billIds,
BillTypeField billTypeField,
BillStatusField billStatusField, Field billNoField)
{
SqlParam param;
Form form = view.Model.BillBusinessInfo.GetForm();
string pkFieldName = form.PkFieldName;
if (pkFieldName.IsNullOrEmpty())
{
return null;
}
QueryBuilderParemeter para = new QueryBuilderParemeter
{
FormId = form.Id
};
para.SelectItems.Add(new SelectorItemInfo(pkFieldName));
if (billTypeField != null)
{
para.SelectItems.Add(new SelectorItemInfo(billTypeField.Key));
}
if (billStatusField != null)
{
para.SelectItems.Add(new SelectorItemInfo(billStatusField.Key));
}
if (billNoField != null)
{
para.SelectItems.Add(new SelectorItemInfo(billNoField.Key));
}
string str2 = string.Empty;
if (form.PkFieldType == EnumPkFieldType.STRING)
{
param = new SqlParam("@FID", KDDbType.udt_varchartable, billIds);
str2 = "2";
}
else
{
param = new SqlParam("@FID", KDDbType.udt_inttable, billIds);
str2 = "1";
}
ExtJoinTableDescription item = new ExtJoinTableDescription
{
FieldName = "FID",
JoinOption = QueryBuilderParemeter.JoinOption.InnerJoin,
ScourceKey = pkFieldName,
TableName = string.Format("table(fn_StrSplit(@FID,',',{0}))", str2),
TableNameAs = "TmpBillPk"
};
para.ExtJoinTables.Add(item);
para.SqlParams.Add(param);
return QueryServiceHelper.GetDynamicObjectCollection(view.Model.Context, para, null);
}

private static string GetTemplateIdFromSetting(IDynamicFormView view, string billTypeId)
{
string str = string.Empty;
JSONArray settingInfo = null;
string id = view.BillBusinessInfo.GetForm().Id;
string str2 = UserParamterServiceHelper.Load(view.Context,
"NotePrintSetup" + id.ToUpper().GetHashCode().ToString(), view.Context.UserId, "");
if (string.IsNullOrWhiteSpace(str2))
{
str2 = UserParamterServiceHelper.Load(view.Context,
"NotePrintSetup" + id.ToUpper().GetHashCode().ToString(), -1L, "");
}
if (string.IsNullOrWhiteSpace(str2))
{
str2 = UserParamterServiceHelper.Load(view.Context,
"NotePrintSetup" + id.GetHashCode().ToString(), view.Context.UserId, "");
}
str2 = str2.FixedOrcalXMLString();
if (!string.IsNullOrWhiteSpace(str2) && (str2 != ""))
{
settingInfo = new JSONArray(str2);
}

if (settingInfo != null)
{
if (billTypeId.IsNullOrEmptyOrWhiteSpace())
{
for (int j = 0; j < (settingInfo.Count - 1); j++)
{
Dictionary dictionary = settingInfo[j] as Dictionary;
if (((dictionary.ContainsKey("value") && !dictionary["value"].IsNullOrEmptyOrWhiteSpace()) &&
!dictionary["value"].Equals("empty")) &&
((dictionary["key"] == null) || string.IsNullOrWhiteSpace(dictionary["key"].ToString())))
{
return ((dictionary["value"] == null) ? string.Empty : dictionary["value"].ToString());
}
}
return str;
}
for (int i = 0; i < (settingInfo.Count - 1); i++)
{
Dictionary dictionary2 = settingInfo[i] as Dictionary;
if ((dictionary2["key"] != null) && dictionary2["key"].Equals(billTypeId))
{
return ((dictionary2["value"] == null) ? string.Empty : dictionary2["value"].ToString());
}
}
}
return str;
}

private static bool IsNoteTemplateExist(IDynamicFormView view, string templateId)
{
bool flag = false;
string strSQL = "select 1 from T_META_OBJECTTYPE WHERE FID=@FID";
SqlParam param = new SqlParam("@FID", KDDbType.AnsiString, templateId);
using (
IDataReader reader = DBServiceHelper.ExecuteReader(view.Context, strSQL, new List {param})
)
{
if (reader.Read())
{
flag = true;
}
}
return flag;
}

#endregion

}[/code]