Reply to thread

Message: [QUOTE="Saeed, post: 6426168"] 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; } } [/QUOTE]

Verification