Expand|Select|Wrap|Line Numbers
- Row ID Order ID Order Date State
- 1 CA-2016-152156 8 November 2016 Kentucky
- 2 CA-2016-152156 8 November 2016 Kentucky
- 3 CA-2016-138688 12 June 2016 California
- 4 US-2015-108966 11 October 2015 Florida
- 5 US-2015-108966 11 October 2015 Florida
- 6 CA-2014-115812 9 June 2014 California
- 7 CA-2014-115812 9 June 2014 California
- 8 CA-2014-115812 9 June 2014 California
- 9 CA-2014-115812 9 June 2014 California
- 10 CA-2014-115812 9 June 2014 California
- 11 CA-2014-115812 9 June 2014 California
- 12 CA-2014-115812 9 June 2014 California
I've done:
Expand|Select|Wrap|Line Numbers
- using System;
- using System.Collections.Generic;
- using System.IO;
- using System.Linq;
- using System.Runtime.InteropServices;
- using System.Data;
- using System.Threading.Tasks;
- using Excel = Microsoft.Office.Interop.Excel;
- using System.Windows.Forms;
- namespace FinalExcel
- {
- /// <summary>
- /// Description of MainForm.
- /// </summary>
- public partial class MainForm : Form
- {
- public MainForm()
- {
- InitializeComponent();
- }
- private Excel.Range filteredRange=null;
- void ButtonShowClick(object sender, EventArgs e)
- {
- try {
- //Create COM Objects. Create a COM object for everything that is referenced
- Excel.Application xlApp = new Excel.Application();
- Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\DD21\tempdata.xlsx");
- Excel.Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[1];
- Excel.Range xlRange = xlWorksheet.UsedRange;
- xlRange.AutoFilter(4, "Florida", Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, true);
- filteredRange = xlRange.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Excel.XlSpecialCellsValue.xlTextValues);
- if (filteredRange != null)
- {
- DataTable dt = ConvertRangeToDataTable();
- if (dt != null) { dataGridView1.DataSource = dt; }
- }
- //cleanup
- GC.Collect();
- GC.WaitForPendingFinalizers();
- //release com objects to fully kill excel process from running in the background
- Marshal.ReleaseComObject(xlRange);
- Marshal.ReleaseComObject(xlWorksheet);
- //close and release
- xlWorkbook.Close();
- Marshal.ReleaseComObject(xlWorkbook);
- //quit and release
- xlApp.Quit();
- Marshal.ReleaseComObject(xlApp);
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.ToString());
- }
- }
- private DataTable ConvertRangeToDataTable() {
- try {
- DataTable dt = new DataTable();
- int ColCount = filteredRange.Columns.Count;
- int RowCount = filteredRange.Rows.Count;
- for (int i = 0; i < ColCount; i++) {
- DataColumn dc = new DataColumn();
- dt.Columns.Add(dc);
- }
- for (int i = 1; i <= RowCount; i++) {
- DataRow dr = dt.NewRow();
- for (int j = 1; j <= ColCount; j++) { dr[j - 1] = ((Excel.Range)filteredRange.Cells[i, j]).Value2; }
- dt.Rows.Add(dr);
- }
- return dt;
- }
- catch { return null; }
- }
- void ButtonExitClick(object sender, EventArgs e)
- {
- this.Close();
- }
- }
- }
Please help...
Further, I wish to show only few of the columns from the excel after the autofilter to the datagridview, how can I do that as well?
Thanks in advance