How to reduce excel file size having pivot tables

S

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;
}
}
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top