reading excel file to print in c# windows form based on a condition

Question

if i have the following data in my excel file,note that it may or may not have data from A-Z columns

  A     B      C
1 1     97     testing
2 5     102    test
3 8     42     tessst

on a c# windows form there is a single test box that prompts the user for a row number,if they enter a row number of 1 then it must bring back only that single row.

what i attempted

private void btnGetRow_Click(object sender, EventArgs e)
{
    var RowNumber =  txtRowNumber.Text;
    var path = "C:\\Book1.xlsx;";          

    if (txtRowNumber.Text == string.Empty || Convert.ToInt32(RowNumber) == 0)
    {
        MessageBox.Show("Please enter a digit > 0 ");
    }
    else
    {
        string con = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
        using (OleDbConnection connection = new OleDbConnection(con))
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand("select * from [Product$] where F1=Rownumber ", connection); // hear it must filter from the textbox,which it doesnt do so,it gives an error 
           using (OleDbDataReader dr = command.ExecuteReader())
            {
                while (dr.Read())
               {
                  //here im not sure how to get the columns from A-Z
                  //send data to the printer
                }
            }

        }

        Console.WriteLine(RowNumber);
    }
}

score 0 · Answer 1 · edited May 23 '17 at 11:43

Consider to work with the OpenXML SDK instead. With this accessing XLSX Sheets is pretty easy: With this simple Code Snipped you can get a Spreadsheets Row and get it's cells values:

    private void GetValues()
    {
        OpenFileDialog fd = new OpenFileDialog();
        fd.ShowDialog();
        var x = fd.FileName;

        using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(x, true))
        {
            WorkbookPart workbookPart = myDoc.WorkbookPart;
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
            SheetData sheetData =
            worksheetPart.Worksheet.Elements<SheetData>().First();
            var row = sheetData.Elements<Row>().ToArray()[1];

            foreach (Cell c in row.Elements<Cell>())
            {
               MessageBox.Show(c.CellValue.Text);
            }
        }
    }

Edit: If your cells contain non-numeric values you have to get the value from the SharedStringTable have a look at this answer. Then your code has to look like this:

    private void GetNonNumericValues()
    {
        OpenFileDialog fd = new OpenFileDialog();
        fd.ShowDialog();
        var x = fd.FileName;

        using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(x, true))
        {
            SharedStringTable sharedStringTable = myDoc.WorkbookPart.SharedStringTablePart.SharedStringTable;
            WorkbookPart workbookPart = myDoc.WorkbookPart;
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
            SheetData sheetData =
            worksheetPart.Worksheet.Elements<SheetData>().First();
            var row = sheetData.Elements<Row>().ToArray()[0];

            foreach (Cell c in row.Elements<Cell>())
            {
                if (c.DataType == CellValues.SharedString)
                {
                    var cellValue = c.InnerText;
                    MessageBox.Show(sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText);
                }
            }
        }
    }

reading excel file to print in c# windows form based on a condition

1 Answers1