none
how to excport one excel column from datagridview in C# RRS feed

  • Question


  • I have excel data as follow:

    Project | person | Time | Date

    support | A |50 | 2019-10-10

    IT | B |1,20 |2019-10-10

    debugg | A |30 |2019-10-11

    support | c |20 |2019-10-11

    support | A |30 |2019-10-12

    IT | B | 1.20 |2019-10-12

    In my code I can export all excel data from datagridview, how I do to export one column only. I want to export column Project and do sort for every type of project and sum e.g the exported new excel file should be as follow:

    2019-10-10 to 2019-10-12

    Project | Sum

    support | 1.40

    IT | 2.40

    debugg | 30

    Here is my export code. can you help me please.

    Thank you for hand.

     private void copyAlltoClipboard()
        {
            dataGridView1.SelectAll();
            DataObject dataObj = dataGridView1.GetClipboardContent();
            if (dataObj != null)
                Clipboard.SetDataObject(dataObj);
        }
    
        private void Button4_Click(object sender, EventArgs e)
        {
            copyAlltoClipboard();
            Microsoft.Office.Interop.Excel.Application xlexcel;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            xlexcel = new Excel.Application();
            xlexcel.Visible = true;
            xlWorkBook = xlexcel.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
            CR.Select();
            xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);


    • Edited by sara87 Friday, December 6, 2019 8:36 AM
    • Moved by CoolDadTx Friday, December 6, 2019 2:47 PM Winforms related
    Friday, December 6, 2019 8:35 AM

Answers

  • Hi sara87,

    Thank you for posting here.

    For your question, you want to statistics the data in excel and output it to a new file.

    You can try the following code to get it.

     class Program
        {
            static void Main(string[] args)
            {
                DataTable dt = ReadExcelFile("sheet1", @"d:\test\test.xlsx");
                double sum = 0;
                var data =
                      from r in dt.AsEnumerable()
                      group r by r.Field<string>("Project") into ProGroup
                      let SumTime = ProGroup.Sum(r => r.Field<double>("Time"))
    
                      select new
                      {
                          Project = ProGroup.Key,
                          Sum = SumTime
                      };
                DateTime dateTime1 = dt.AsEnumerable().Select(t => t.Field<DateTime>("Date")).Max();
                DateTime dateTime2 = dt.AsEnumerable().Select(t => t.Field<DateTime>("Date")).Min();
    
                var a = data.ToList();
    
                DataTable dt1 = new DataTable();
                dt1.Columns.Add("Project", typeof(string));
                dt1.Columns.Add("Sum", typeof(double));
    
                foreach (var item in a)
                {
                    dt1.Rows.Add(item.Project,item.Sum);
                }
    
                ExportToExcel(dt1,@"d:\test\result.xlsx",dateTime2+" - "+dateTime1);
    
    
                Console.WriteLine();
            }
            private static void ExportToExcel(DataTable tbl, string excelFilePath,string time)
            {
                try
                {
                    if (tbl == null || tbl.Columns.Count == 0)
                        throw new Exception("ExportToExcel: Null or empty input table!\n");
    
                    // load excel, and create a new workbook
                    var excelApp = new Excel.Application();
                    excelApp.Workbooks.Add();
    
                    // single worksheet
                    Excel._Worksheet workSheet = excelApp.ActiveSheet;
    
                    // column headings
                    for (var i = 0; i < tbl.Columns.Count; i++)
                    {
                        workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;
                    }
    
                    // rows
                    for (var i = 0; i < tbl.Rows.Count; i++)
                    {
                        // to do: format datetime values before printing
                        for (var j = 0; j < tbl.Columns.Count; j++)
                        {
                            workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];
                            if (j == tbl.Columns.Count-1 )
                            {
                                workSheet.Cells[2, j + 2] = time;
                            }
                        }
                    }
    
                    // check file path
                    if (!string.IsNullOrEmpty(excelFilePath))
                    {
                        try
                        {
                            workSheet.SaveAs(excelFilePath);
                            excelApp.Quit();
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                                                + ex.Message);
                        }
                    }
                    else
                    { // no file path is given
                        excelApp.Visible = true;
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception("ExportToExcel: \n" + ex.Message);
                }
            }
    
            private static DataTable ReadExcelFile(string sheetName, string path)
            {
    
                using (OleDbConnection conn = new OleDbConnection())
                {
                    DataTable dt = new DataTable();
                    string Import_FileName = path;
                    string fileExtension = Path.GetExtension(Import_FileName);
                    if (fileExtension == ".xls")
                        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
                    if (fileExtension == ".xlsx")
                        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        comm.CommandText = "Select * from [" + sheetName + "$]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
        }

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by sara87 Friday, December 6, 2019 11:54 PM
    Friday, December 6, 2019 10:06 AM

All replies

  • Hi sara87,

    Thank you for posting here.

    For your question, you want to statistics the data in excel and output it to a new file.

    You can try the following code to get it.

     class Program
        {
            static void Main(string[] args)
            {
                DataTable dt = ReadExcelFile("sheet1", @"d:\test\test.xlsx");
                double sum = 0;
                var data =
                      from r in dt.AsEnumerable()
                      group r by r.Field<string>("Project") into ProGroup
                      let SumTime = ProGroup.Sum(r => r.Field<double>("Time"))
    
                      select new
                      {
                          Project = ProGroup.Key,
                          Sum = SumTime
                      };
                DateTime dateTime1 = dt.AsEnumerable().Select(t => t.Field<DateTime>("Date")).Max();
                DateTime dateTime2 = dt.AsEnumerable().Select(t => t.Field<DateTime>("Date")).Min();
    
                var a = data.ToList();
    
                DataTable dt1 = new DataTable();
                dt1.Columns.Add("Project", typeof(string));
                dt1.Columns.Add("Sum", typeof(double));
    
                foreach (var item in a)
                {
                    dt1.Rows.Add(item.Project,item.Sum);
                }
    
                ExportToExcel(dt1,@"d:\test\result.xlsx",dateTime2+" - "+dateTime1);
    
    
                Console.WriteLine();
            }
            private static void ExportToExcel(DataTable tbl, string excelFilePath,string time)
            {
                try
                {
                    if (tbl == null || tbl.Columns.Count == 0)
                        throw new Exception("ExportToExcel: Null or empty input table!\n");
    
                    // load excel, and create a new workbook
                    var excelApp = new Excel.Application();
                    excelApp.Workbooks.Add();
    
                    // single worksheet
                    Excel._Worksheet workSheet = excelApp.ActiveSheet;
    
                    // column headings
                    for (var i = 0; i < tbl.Columns.Count; i++)
                    {
                        workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;
                    }
    
                    // rows
                    for (var i = 0; i < tbl.Rows.Count; i++)
                    {
                        // to do: format datetime values before printing
                        for (var j = 0; j < tbl.Columns.Count; j++)
                        {
                            workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];
                            if (j == tbl.Columns.Count-1 )
                            {
                                workSheet.Cells[2, j + 2] = time;
                            }
                        }
                    }
    
                    // check file path
                    if (!string.IsNullOrEmpty(excelFilePath))
                    {
                        try
                        {
                            workSheet.SaveAs(excelFilePath);
                            excelApp.Quit();
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                                                + ex.Message);
                        }
                    }
                    else
                    { // no file path is given
                        excelApp.Visible = true;
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception("ExportToExcel: \n" + ex.Message);
                }
            }
    
            private static DataTable ReadExcelFile(string sheetName, string path)
            {
    
                using (OleDbConnection conn = new OleDbConnection())
                {
                    DataTable dt = new DataTable();
                    string Import_FileName = path;
                    string fileExtension = Path.GetExtension(Import_FileName);
                    if (fileExtension == ".xls")
                        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
                    if (fileExtension == ".xlsx")
                        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        comm.CommandText = "Select * from [" + sheetName + "$]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
        }

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by sara87 Friday, December 6, 2019 11:54 PM
    Friday, December 6, 2019 10:06 AM
  • Thank you for your answer

    Friday, December 6, 2019 11:54 PM