Export datagrid to a excel window which is opening c#

Question

I'm begining in WPF and i'm working with datagrid

I have multi datagrids. If i click a button ("Export" button) first time, it will create a new excel window and export data to first sheet. Then, i change to another datagrid and click a button ("Export" button) second time. so, it will create a new sheet in excel window which created before. Can you help me change my code ?

Thank you very much!!

 public void Export(DataTable dt, string sheetName, string title)
    {

        Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbooks oBooks;
        Microsoft.Office.Interop.Excel.Sheets oSheets;
        Microsoft.Office.Interop.Excel.Workbook oBook;
        Microsoft.Office.Interop.Excel.Worksheet oSheet;
        Excel.Range _range = null;


        oExcel.DisplayAlerts = false;
        oExcel.Application.SheetsInNewWorkbook = 1;
        oBooks = oExcel.Workbooks;

        oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
        oSheets = oBook.Worksheets;
        oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
        oSheet.Name = sheetName;


        Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "C1");
        head.MergeCells = true;
        head.Value2 = title;
        head.Font.Bold = true;
        head.Font.Name = "Tahoma";
        head.Font.Size = "18";
        head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;


        List<object> objHeaders = new List<object>();
        for (int n = 0; n <= dt.Rows.Count; n++)
        {
            objHeaders.Add(dt.Columns[n].ColumnName);
        }

        var headerToAdd = objHeaders.ToArray();


        _range = oSheet.get_Range("A3", Type.Missing);
        _range = _range.get_Resize(dt.Rows.Count, dt.Columns.Count);
        _range.ColumnWidth = 30;
        _range.set_Value(Type.Missing, headerToAdd);


        Excel.Range rowHead = oSheet.get_Range("A3", "C"+dt.Columns.Count);
        rowHead.Font.Bold = true;

        rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;

        rowHead.Interior.ColorIndex = 15;
        rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;



        int row = dt.Rows.Count;
        int col = dt.Columns.Count;
        object[,] arr = new object[row, col];


        for (int r = 0; r < dt.Rows.Count; r++)
        {
            DataRow dr = dt.Rows[r];
            for (int c = 0; c < dt.Columns.Count; c++)
            {
                arr[r, c] = dr[c];
            }
        }


        int rowStart = 4;
        int columnStart = 1;

        int rowEnd = rowStart + dt.Rows.Count - 1;
        int columnEnd = dt.Columns.Count;


        Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];

        Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];

        Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);


        range.Value2 = arr;


        range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;

        Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];
        Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);
        oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

        oExcel.Visible = true;
    }      
}

Do you want to export the focused datagrid data to an excel sheet? What is the problem with your code? — terry, Apr 18 '14 at 07:28
my code do not have problem, but i need to change this code for save another datagrid to the same excel window which opened before, but in the other sheet — Dat Lieu, Apr 18 '14 at 12:16

score 0 · Accepted Answer · edited May 23 '17 at 10:26

0

You need to use field Microsoft.Office.Interop.Excel.Application oExcel in your class to store its value. First time it will be null, but on second - it will be opened excel. But you must be carefull with such a behavior, user can close excel, before 2nd export. So you need implement Closed event handler and clear your field (similar problem)

edited May 23 '17 at 10:26

Community

1
1

answered Apr 18 '14 at 09:17

NIBERIUM

76
6

i try to do it before, but it opened a different excel window has the sheet before. So, do i need to change something different ? – Dat Lieu Apr 18 '14 at 12:18
i got it, it ran. But when i click on second an excel window lose the sheet before and create two new sheet. Which of one has new data, another one blank. I use this case oBook.Sheets.Add(oBook.Sheets[numberSheet-1], Type.Missing, Type.Missing, Type.Missing); oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(numberSheet++); – Dat Lieu Apr 18 '14 at 13:28

Export datagrid to a excel window which is opening c#

1 Answers1