SOLVED - Export data from DataGridView to MS Excel Pivot Table and Pivot Chart.
Hi,
This code may help others.
After retrieving data from MS SQL Server, and populating it to a DataGridView, this code exports the data to an MS Excel 11.0 or 12.0 worksheet
Regards,
Aldo.
Note: "dg1" is the DataGridView.
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core; // Office 11.0
using System.Threading;
public virtual event EventHandler WorkStart;
public virtual event EventHandler WorkFinished;
// Events
public void OnWorkStart(object sender, EventArgs e)
{
if (WorkStart != null) { WorkStart(sender, e); }
}
public void OnWorkFinished(object sender, EventArgs e)
{
if (WorkFinished != null) { WorkFinished(sender, e); }
}
private void ExportToExcel()
{
Thread t1 = new Thread
(
delegate()
{
OnWorkStart(dg1, new EventArgs());
// Declare missing object.
Object oMissing = System.Reflection.Missing.Value;
// Change current thread culture to ("en-US").
// System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
// Create a new Excel instance.
Excel.Application oExcel = new Excel.Application();
// Set Excel workbook to open with only 1 worsheet.
oExcel.SheetsInNewWorkbook = 1;
// Set the UserControl property so Excel won't shut down.
oExcel.UserControl = true;
// Add a workbook.
Excel.Workbook oBook = oExcel.Workbooks.Add(oMissing);
// Get worksheets collection
Excel.Sheets oSheetsColl = oExcel.Worksheets;
// Get Worksheet number 1
Excel.Worksheet oSheet = (Excel.Worksheet)oSheetsColl.get_Item(1);
oSheet.Name = "Details Data";
// Export Data to Excel worksheet to pivot table.
int colIndex = 0;
foreach (DataGridViewColumn column in dg1.Columns)
{
// Export all columns.
oSheet.Cells[1, colIndex + 1] = column.HeaderText;
for (int row = 1; row < 1 + dg1.Rows.Count - 1; row++)
{ oSheet.Cells[row + 1, colIndex + 1] = dg1[colIndex, row - 1].Value; }
colIndex++;
}
// Get the range of the cells containing the exported data.
int lastCol = oSheet.UsedRange.Columns.Count;
int lastRow = oSheet.UsedRange.Rows.Count;
// Create the Range.
Excel.Range oSourceData = (Excel.Range)oSheet.get_Range(oSheet.Cells[1, 1], oSheet.Cells[lastRow, lastCol]);
// Create Pivot Table.
Excel.PivotTable table1 = oSheet.PivotTableWizard(
Excel.XlPivotTableSourceType.xlDatabase, oSourceData,
oMissing, "PivotTable1", true, true, true, false, oMissing,
oMissing, false, false, Excel.XlOrder.xlDownThenOver, 5,
oMissing, oMissing);
// Show / Hide Pivot Fields Table
oBook.ShowPivotTableFieldList = false;
// Set table format.
table1.Format(Excel.XlPivotFormatType.xlTable4);
// Page Fileds
Excel.PivotField oPivotField1 = (Excel.PivotField)table1.PivotFields("Items.ITEMNAME");
oPivotField1.Caption = "Items.ITEMNAME";
oPivotField1.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlPageField;
oPivotField1.Position = 1;
oPivotField1.LayoutForm = Microsoft.Office.Interop.Excel.XlLayoutFormType.xlTabular;
// Row Fields
Excel.PivotField oPivotField6 = (Excel.PivotField)table1.PivotFields("Accounts.FullName");
oPivotField6.Caption = "Accounts.FullName ";
oPivotField6.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;
oPivotField6.Position = 1;
oPivotField6.LayoutForm = Microsoft.Office.Interop.Excel.XlLayoutFormType.xlTabular;
// Data Fields
Excel.PivotField oPivotField14 = (Excel.PivotField)table1.PivotFields("WorkOrders");
oPivotField14.Caption = @" WorkOrders ";
oPivotField14.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField;
oPivotField14.Position = 1;
oPivotField14.NumberFormat = "0";
// Remove Subtotals
for (int index = 1; index < 13; index++) { try { oPivotField1.set_Subtotals(index, false); } catch (Exception ex) { } }
// Remove blank lines.
try { oPivotField1.LayoutBlankLine = false; } catch (Exception ex) { }
// Set Name and format to current worksheet.
Excel.Worksheet oDetailsPTSheet = (Excel.Worksheet)oExcel.ActiveSheet;
oDetailsPTSheet.Name = "Details PT";
oDetailsPTSheet.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
oDetailsPTSheet.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
oDetailsPTSheet.Cells.ShrinkToFit = true;
oDetailsPTSheet.Cells.Font.Bold = false;
oDetailsPTSheet.Cells.Font.Italic = false;
// Show / Hide GridLines.
oExcel.ActiveWindow.DisplayGridlines = false;
// Freeze Window
Excel.Range oFreezePosit = (Excel.Range)oDetailsPTSheet.get_Range(oDetailsPTSheet.Cells[8, 4], oDetailsPTSheet.Cells[8, 4]);
oFreezePosit.Select();
oExcel.ActiveWindow.FreezePanes = true;
// Set Columns width.
Excel.Range oCol1 = (Excel.Range)oDetailsPTSheet.get_Range(oDetailsPTSheet.Cells[1, 1], oDetailsPTSheet.Cells[1, 1]); oCol1.ColumnWidth = 30;
Excel.Range oCol2 = (Excel.Range)oDetailsPTSheet.get_Range(oDetailsPTSheet.Cells[1, 2], oDetailsPTSheet.Cells[1, 2]); oCol2.ColumnWidth = 10;
Excel.Range oCol3 = (Excel.Range)oDetailsPTSheet.get_Range(oDetailsPTSheet.Cells[1, 3], oDetailsPTSheet.Cells[1, 3]); oCol3.ColumnWidth = 9;
// Print Setup.
try { oDetailsPTSheet.PageSetup.PrintTitleRows = "$7:$7"; }catch { }
try { oDetailsPTSheet.PageSetup.LeftHeader = "&9" + ": &D-&T" + "\r" + " &P &N"; }catch { }
try { oDetailsPTSheet.PageSetup.CenterHeader = "&" + "8" + ": &\"Arial,Bold Italic\"&F"; }catch { }
try { oDetailsPTSheet.PageSetup.RightHeader = "&" + "8" + " "; }catch { }
try { oDetailsPTSheet.PageSetup.LeftFooter = "&" + "8" + "Confidential - " + "" + "&" + "6" + "\r" + "Path: " + "&6&Z&F"; }catch { }
try { oDetailsPTSheet.PageSetup.CenterFooter = "&" + "8" + " " + Environment.UserName + "\r" + ": " + Environment.MachineName; }catch { }
try { oDetailsPTSheet.PageSetup.RightFooter = "&" + "8" + ": &\"Arial,Bold Italic\"&A"; }catch { }
try { oDetailsPTSheet.PageSetup.LeftMargin = oExcel.Application.InchesToPoints(0.25); }catch { }
try { oDetailsPTSheet.PageSetup.RightMargin = oExcel.Application.InchesToPoints(0.25); }catch { }
try { oDetailsPTSheet.PageSetup.TopMargin = oExcel.Application.InchesToPoints(0.72); }catch { }
try { oDetailsPTSheet.PageSetup.BottomMargin = oExcel.Application.InchesToPoints(0.72); }catch { }
try { oDetailsPTSheet.PageSetup.HeaderMargin = oExcel.Application.InchesToPoints(0.17); }catch { }
try { oDetailsPTSheet.PageSetup.FooterMargin = oExcel.Application.InchesToPoints(0.25); }catch { }
try { oDetailsPTSheet.PageSetup.PrintHeadings = false; }catch { }
try { oDetailsPTSheet.PageSetup.PrintGridlines = false; }catch { }
try { oDetailsPTSheet.PageSetup.PrintComments = Microsoft.Office.Interop.Excel.XlPrintLocation.xlPrintNoComments; }catch { }
//try { oDetailsPTSheet.PageSetup.PrintQuality = 600; }catch { }
try { oDetailsPTSheet.PageSetup.CenterHorizontally = false; }catch { }
try { oDetailsPTSheet.PageSetup.CenterVertically = false; }catch { }
try { oDetailsPTSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape; }catch { }
try { oDetailsPTSheet.PageSetup.Draft = false; }catch { }
try { oDetailsPTSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4; }catch { }
//try { oDetailsPTSheet.PageSetup.FirstPageNumber = xlAutomatic; }catch { }
try { oDetailsPTSheet.PageSetup.Order = Microsoft.Office.Interop.Excel.XlOrder.xlDownThenOver; }catch { }
try { oDetailsPTSheet.PageSetup.BlackAndWhite = false; }catch { }
try { oDetailsPTSheet.PageSetup.Zoom = false; }catch { }
try { oDetailsPTSheet.PageSetup.FitToPagesWide = 1; }catch { }
try { oDetailsPTSheet.PageSetup.FitToPagesTall = 500; }catch { }
try { oDetailsPTSheet.PageSetup.PrintErrors = Microsoft.Office.Interop.Excel.XlPrintErrors.xlPrintErrorsDisplayed; }catch { }
// Wrap titles
Excel.Range oTitleRange = (Excel.Range)oDetailsPTSheet.get_Range(oDetailsPTSheet.Cells[7, 1], oDetailsPTSheet.Cells[7, 3]);
oTitleRange.Cells.WrapText = true;
oTitleRange.Cells.RowHeight = 62;
oTitleRange.Interior.ColorIndex = 4;
oTitleRange.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;
// Hide WorkSheets.
oSheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;
// XXXXXXX Segunda parte
// Insert new WorkSheet
oBook.Sheets.Add(oMissing, oMissing, oMissing, oMissing);
Excel.Worksheet oOTDDataSheet = (Excel.Worksheet)oExcel.ActiveSheet;
oOTDDataSheet.Name = "OTD Data";
//// XXXXXXXXXXXXXXXXx Tercera parte
// Create the Range.
// Get the range of the cells containing the exported data.
lastCol = oOTDPTSheet.UsedRange.Columns.Count;
lastRow = oOTDPTSheet.UsedRange.Rows.Count;
Excel.Range oOTDPCSourceData = (Excel.Range)oOTDPTSheet.get_Range(oOTDPTSheet.Cells[1, 1], oOTDPTSheet.Cells[lastRow, lastCol]);
Excel.Chart oOTDPChart = (Excel.Chart)oBook.Charts.Add(oMissing, oMissing, oMissing, oMissing);
try
{
oOTDPChart.ChartWizard(oOTDPCSourceData, oMissing
, oMissing, Microsoft.Office.Interop.Excel.XlRowCol.xlColumns, oMissing, oMissing
, oMissing, oMissing, oMissing, oMissing, oMissing);
}
catch { }
oOTDPChart.Name = "OTD Chart";
// Format Chart Title.
oOTDPChart.HasTitle = true;
Microsoft.Office.Interop.Excel.ChartTitle oOTDPChartTitle;
oOTDPChartTitle = (Microsoft.Office.Interop.Excel.ChartTitle)oOTDPChart.ChartTitle;
oOTDPChartTitle.Text =
"OTD"
+ "\r"
+ ": "
+ dtFirstDate.Value.ToString("dd/MM/yyyy")
+ "" + dtLastDate.Value.ToString("dd/MM/yyyy");
oOTDPChartTitle.Font.Size = 16;
oOTDPChartTitle.Font.Italic = true;
oOTDPChartTitle.Font.Underline = true;
oOTDPChartTitle.Orientation = Microsoft.Office.Interop.Excel.XlOrientation.xlHorizontal;
oOTDPChartTitle.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
oOTDPChartTitle.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
oOTDPChartTitle.ReadingOrder = (int)Microsoft.Office.Interop.Excel.Constants.xlContext;
// Format Plot area.
Microsoft.Office.Interop.Excel.PlotArea oOTDPCPlotArea;
oOTDPCPlotArea = (Microsoft.Office.Interop.Excel.PlotArea)oOTDPChart.PlotArea;
oOTDPCPlotArea.Fill.TwoColorGradient(Microsoft.Office.Core.MsoGradientStyle.msoGradientHorizontal, 4);
oOTDPCPlotArea.Fill.Visible = MsoTriState.msoTrue;
oOTDPCPlotArea.Fill.ForeColor.SchemeColor = 4;
oOTDPCPlotArea.Fill.BackColor.SchemeColor = 2;
// Format TickLabels (Category).
Microsoft.Office.Interop.Excel.Axis oOTDPCAxisX;
oOTDPCAxisX = (Microsoft.Office.Interop.Excel.Axis)oOTDPChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
oOTDPCAxisX.TickLabels.Orientation = Microsoft.Office.Interop.Excel.XlTickLabelOrientation.xlTickLabelOrientationUpward;
oOTDPCAxisX.TickLabels.Orientation = (Microsoft.Office.Interop.Excel.XlTickLabelOrientation)45;
oOTDPCAxisX.TickLabels.Offset = 2;
oOTDPCAxisX.TickLabels.Font.Name = "Arial";
oOTDPCAxisX.TickLabels.Font.Size = 9;
// Format the title of the axis.
oOTDPCAxisX.HasTitle = true;
Microsoft.Office.Interop.Excel.AxisTitle oOTDPCAxisXTitle;
oOTDPCAxisXTitle = (Microsoft.Office.Interop.Excel.AxisTitle)oOTDPCAxisX.AxisTitle;
oOTDPCAxisXTitle.Text = "x";
oOTDPCAxisXTitle.Font.Size = 14;
oOTDPCAxisXTitle.Font.Italic = true;
oOTDPCAxisXTitle.Font.Underline = true;
oOTDPCAxisXTitle.Orientation = Microsoft.Office.Interop.Excel.XlOrientation.xlHorizontal;
// Format TickLabels (Value).
Microsoft.Office.Interop.Excel.Axis oOTDPCAxisY;
oOTDPCAxisY = (Microsoft.Office.Interop.Excel.Axis)oOTDPChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
oOTDPCAxisY.HasTitle = true;
//oOTDPCAxisY.TickLabels.Orientation = (Microsoft.Office.Interop.Excel.XlTickLabelOrientation)0;
oOTDPCAxisY.TickLabels.Orientation = Microsoft.Office.Interop.Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;
oOTDPCAxisY.TickLabels.Offset = 2;
oOTDPCAxisY.TickLabels.Font.Name = "Arial";
oOTDPCAxisY.TickLabels.Font.Size = 9;
oOTDPCAxisY.TickLabels.NumberFormat = "0%";
// Format the title of the axis.
oOTDPCAxisY.HasTitle = true;
Microsoft.Office.Interop.Excel.AxisTitle oOTDPCAxisYTitle;
oOTDPCAxisYTitle = (Microsoft.Office.Interop.Excel.AxisTitle)oOTDPCAxisY.AxisTitle;
oOTDPCAxisYTitle.Text = "y";
oOTDPCAxisYTitle.Font.Size = 14;
oOTDPCAxisYTitle.Font.Italic = true;
oOTDPCAxisYTitle.Font.Underline = true;
oOTDPCAxisYTitle.Orientation = Microsoft.Office.Interop.Excel.XlOrientation.xlHorizontal;
// Format Legend
oOTDPChart.Legend.Delete();
// Format Series Collection
Microsoft.Office.Interop.Excel.Series oOTDSerie1;
oOTDSerie1 = (Microsoft.Office.Interop.Excel.Series)oOTDPChart.SeriesCollection(1);
oOTDSerie1.Fill.TwoColorGradient(Microsoft.Office.Core.MsoGradientStyle.msoGradientHorizontal, 4);
oOTDSerie1.Fill.Visible = MsoTriState.msoTrue;
oOTDSerie1.Fill.ForeColor.SchemeColor = 6;
oOTDSerie1.Fill.BackColor.SchemeColor = 3;
// Print Setup. (as before…)
// Make Excel visible to the user.
oExcel.Visible = true;
// Release the variables.
//oBook.Close(false, oMissing, oMissing);
oBook = null;
//oExcel.Quit();
oExcel = null;
// Collect garbage.
GC.Collect();
OnWorkFinished(dg1, new EventArgs());
}
);
t1.Start();
}