public static void ExportaParaExcelSubTotal(DataTable dt, string fileName, bool open, bool ajustarLarguraColunas, int pColunaAgrupar,
Microsoft.Office.Interop.Excel.XlConsolidationFunction pFuncaoAgrupadora)
{
SaveFileDialog saveDialog = new SaveFileDialog();
if (string.IsNullOrEmpty(fileName))
{
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Microsoft Excel (.xlsx)|*.xlsx|Todos Arquivos (*.*)|*.*";
saveDialog.AddExtension = true;
saveDialog.RestoreDirectory = true;
saveDialog.Title = "Onde você deseja salvar o arquivos?";
saveDialog.InitialDirectory = @"C:\Temp";
if (saveDialog.ShowDialog() == DialogResult.OK)
{
fileName = saveDialog.FileName;
}
}
if (!string.IsNullOrEmpty(fileName))
{
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range rng = null;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
wb = excel.Workbooks.Add();
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
for (int Idx = 0; Idx < dt.Columns.Count; Idx++)
{
ws.Range["A1"].Offset[0, Idx].Value = dt.Columns[Idx].ColumnName;
}
for (int Idx = 0; Idx < dt.Rows.Count; Idx++)
{
ws.Range["A2"].Offset[Idx].Resize[1, dt.Columns.Count].Value = dt.Rows[Idx].ItemArray;
}
if (ajustarLarguraColunas)
{
for (int Idx = 0; Idx < dt.Columns.Count; Idx++)
{
ws.Range["A1"].Offset[0, Idx].EntireColumn.AutoFit();
}
}
rng = ws.Range["A1", Type.Missing];
int[] colunas = new int[] { 2, 3 };
rng.Subtotal(pColunaAgrupar, pFuncaoAgrupadora, colunas, false, false, Microsoft.Office.Interop.Excel.XlSummaryRow.xlSummaryAbove);
excel.ActiveWorkbook.SaveCopyAs(fileName);
excel.DisplayAlerts = false;
excel.Quit();
if (open == true)
{
AbrirExcel(fileName);
}
}
catch (Exception ex)
{
MessageBox.Show("Error accessing Excel: " + ex.ToString());
}
}
}