public void CopyWorksheet(int SourceWorksheetIndex, int DesWorksheetIndex)
{
try
{
Excel.Worksheet sheetSource = (Excel.Worksheet)xlsWb.Worksheets[SourceWorksheetIndex];
sheetSource.Select(Missing.Value);
Excel.Worksheet sheetDest = (Excel.Worksheet)xlsWb.Worksheets[DesWorksheetIndex];
sheetSource.Copy(Missing.Value, sheetDest);
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
/// <summary>
/// 多张报表合成一个EXCEL文件
/// </summary>
/// <param name="tempDir">临时文件存放目录</param>
/// <param name="filename">指定导出的文件名，不指定的情况下，弹出对话框让用户选择</param>
/// <param name="sheetNames">工作表名列表</param>
/// <param name="url">报表服务器路径</param>
/// <param name="path">报表路径</param>
/// <param name="parms">参数列表</param>
/// <param name="isVisible">是否显示EXCEL，若不显示EXCEL，则自动保存到filename</param>
/// <remarks>
/// 最后清除临时文件
/// </remarks>
public static void ExportToOneExcelSave(string tempDir, string filename, string[] sheetNames, string url, string[] path,
ReportParameter[][] parms, bool isVisible)
{
if (filename == string.Empty)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "EXCEL(*.xls)|*.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
filename = sfd.FileName;
}
if (sfd.FileName == string.Empty)
{
return;
}
}
string[] excels = new string[path.Length];
string dt = DateTime.Now.ToString("yyyyMMddHHmmssfff");
for (int j = 0; j < excels.Length; j++)
{
excels[j] = string.Format(@"{0}\{1}{2}.xls", tempDir, dt, j);
}
for (int i = 0; i < path.Length; i++)
{
Export("EXCEL", excels[i], string.Empty, url, path[i],
(parms == null ? null : parms[i]));
}
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook1 = excel.Workbooks.Open(excels[0], Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook1.Sheets[1];
worksheet1.Name = sheetNames[0];
worksheet1.PageSetup.CenterFooter = "第 &P 页，共 &N 页"; //加页码
for (int k = excels.Length - 1; k >= 1; k--)
{
Excel.Workbook workbook2 = excel.Workbooks.Open(excels[k], Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet worksheet2 = (Excel.Worksheet)workbook2.Sheets[1];
worksheet2.Name = sheetNames[k];
worksheet2.PageSetup.CenterFooter = "第 &P 页，共 &N 页"; //加页码
worksheet2.Copy(Type.Missing, worksheet1);
workbook2.Close(false, Type.Missing, Type.Missing);
}
if (!isVisible)
{
workbook1.Save();
Office.ExcelHelper.Kill(excel);
if (File.Exists(tempDir + filename))
{
File.Delete(tempDir + filename);
}
Thread.Sleep(100);
File.Move(excels[0], tempDir + filename);
}
for (int m = 1; m < excels.Length; m++)
{
File.Delete(excels[m]);
}
//excel.Visible = isVisible;
}