How to reduce excel file size having pivot tables

[Saeed]

Hi,
I am developing a C# application to create Excel Pivot tables. I have
developed the same by using methods in the Microsoft.Office.Interop.Excel.
But the size of the output excel files is too large. Is there any option to
reduce the file size. Given below the code used to generate Excel File .


private string CreateReport(Excel.Application xlApp, string queryReports,
string reportPath)
{
try
{
bool IsErrorInWorkBook = false;
string strGrandTotal = null;
FileInfo fpPubReports = new
FileInfo(System.IO.Path.Combine(reportPath, "Report.xls"));

Excel.Workbook xlBook = null;
Excel.Worksheet xlDataSheet = null;
Excel.PivotTables xlPivotTables = null;
Excel.PivotTable xlPivotTable = null;
Excel.PivotCache xlPivotCache = null;
Excel.PivotField xlPivotField = null;

try
{
xlBook =
xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

xlPivotCache =
xlBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal,
Type.Missing);
xlPivotCache.Connection =
@"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data
Source=PC012338\SQLEXPRESS;Initial Catalog=metrics_dm";
xlPivotCache.MaintainConnection = true;
xlPivotCache.CommandText = queryReports;
xlPivotCacheCommandType = Excel.XlCmdType.xlCmdSql;

xlDataSheet = (Excel.Worksheet)xlBook.Worksheets[1];
xlPivotTables =
(Excel.PivotTables)xlDataSheet.PivotTables(Type.Missing);
xlPivotTable = xlPivotTables.Add(xlPivotCache,
xlDataSheet.get_Range("A2", Type.Missing), "PTable1", Type.Missing,
Type.Missing);

xlPivotField =
(Excel.PivotField)xlPivotTable.PivotFields("Range_Group_Name");
xlPivotField.Name = "Article Age";
xlPivotField.Orientation =
Excel.XlPivotFieldOrientation.xlRowField;
xlPivotField.Position = 1;

xlPivotField =
(Excel.PivotField)xlPivotTable.PivotFields("List_Name");
xlPivotField.Name = "Publication Name";

xlPivotField.Orientation =
Excel.XlPivotFieldOrientation.xlColumnField;
xlPivotField.Position = 1;

xlPivotField =
(Excel.PivotField)xlPivotTable.PivotFields("Usage");
xlPivotField.Orientation =
Excel.XlPivotFieldOrientation.xlDataField;
xlPivotField.Name = "Sum of Usage";
xlPivotField.Function =
Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;

xlRange = xlPivotTable.GetPivotData("Usage",
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, 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);
strGrandTotal = Convert.ToString(xlRange.Value2);

xlPivotField.Calculation =
Excel.XlPivotFieldCalculation.xlPercentOfColumn;
xlPivotField.NumberFormat = "0.00%";

// Save the newly created Excel file.
xlBook.SaveAs(fpPubReports.FullName,
Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);

return strGrandTotal;
}
catch
{
IsErrorInWorkBook = true;
throw;
}
finally
{
ReleaseExcelRef(xlPivotField);
ReleaseExcelRef(xlPivotTable);
ReleaseExcelRef(xlPivotTables);
ReleaseExcelRef(xlPivotCache);
ReleaseExcelRef(xlDataSheet);
if (xlBook != null && !IsErrorInWorkBook)
{
xlBook.Close(true, System.Type.Missing,
System.Type.Missing);
}
ReleaseExcelRef(xlBook);

GC.Collect();
GC.WaitForPendingFinalizers();

}
}
catch
{
throw;
}
}