Reading Excel From C#

Reading Excel From C#

• Joe H

  "To prevent possible loss of data, Microsoft Office Excel cannot shift nonblank cells off the worksheet.

  Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used.

  Or, you can move the data to a new location and try again."
  

  This usually happens when Excel thinks that there is information in the very last row. If you hit Ctrl+End, you may find that it shoots all the way down to row 65536 or something like that. Clear the contents of that entire row or delete it altogether. You may need to be persistent (this is an Office application, after all). I had a user with this issue earlier today and I had to clear each cell one at a time and save the document before it would take effect. Once excel stops believing that there is something in the last rows, you'll be able to insert rows wherever you need.
  

  
  

• ST W

  Hello There

  I'm Using the Same Way but i'm Facing a problem and i hope to find a solution here

  objWorkbook = objExcel.Workbooks.Open(MapPath("\" & MyFilePath ))

  objWorkbook.Unprotect()

  objSheets = objWorkbook.Sheets()

  objRange = objSheets.Application.Cells

  and i tried to add a row using these tow lines once for the first and the second time i used the second one ..

  'objSheets.Application.Cells.Insert(Excel.XlDirection.xlDown, 1)

  'objRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown)

  but a new EXCEPTION appear which says

  "To prevent possible loss of data, Microsoft Office Excel cannot shift nonblank cells off the worksheet.

  Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used.

  Or, you can move the data to a new location and try again."

  Any one Know how to solve this

  Please note i'm using a predisigned Excel "Formatted File "

  
  
  

• sanjeevm

  His Doss, Create a reference in your project to Excel Objects Library.  The excel object library can be added in the COM tab of adding reference dialog. I hope the following code in your menu click event method will help you a lot to achieve your need.
  
    this.openFileDialog1.FileName = "*.xls";
    if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
     {
        Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(
           openFileDialog1.FileName, 0, true, 5,
            "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,
            0, true); 
       Excel.Sheets sheets = theWorkbook.Worksheets;
       Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
       for (int i = 1; i <= 10; i++)
       {
       Excel.Range range = worksheet.get_Range("A"+i.ToString(), "J" + i.ToString());
       System.Array myvalues = (System.Array)range.Cells.Value;
       string[] strArray = ConvertToStringArray(myvalues);
       }
  }
  
  Cheers,
  Daya Anand, PSPIndia

  
  
  

• Martin Kulov - MVP

  I would guess: open Excel from your program, and have Excel read the data.
  
  Search for Visual Studio Tools for Office (VSTO) for the details.
  
  

• AlistairSKing

  If you are using a dynamically generated excel file then you can use the following:
  
  

  Excel.Sheets sheets = m_Excel.Worksheets; Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1); System.Array myvalues; Excel.Range range = worksheet.get_Range("A1", "E1".ToString()); myvalues = (System.Array)range.Cells.Value;

  
  Thanks
  

  
  

• FourBlades

  Found a reference, worded slightly differently so it wasn't in the original search results. (http://support.microsoft.com/kb/305568)

  You probably have formatting the entire length of a column/row, such as a cell border. If you go to the edge of the used area, select the rest of the sheet, and remove the border, you should eliminate the problem.

  It's an instant problem caused by selecting a row/column and adding an edge border to it - after that point, you can't insert additional rows more that one at a time.

  Microsoft - Please fix the problem. Excel sees this formatting as content, but it is not. (Excel even adds the border back if the row is deleted!)

  
  

• caution812

  When I try to do this, I keep getting "Old format or invalid type library"
  I added "Microsoft Excel 10.0" as reference and use the following code
  
  Excel.ApplicationClass excel = new Excel.ApplicationClass();
  
  Excel.Workbook workbook = excl.Workbooks.Open(openFileDialog.FileName.toString(), null, null, null, null, null, null, null, null, null, null, null, null, null, null).
  I also don't know what all these parameters should be.

  
  

• desny

  If you don't want to use the Excel COM objects, you can use OleDb. It takes a little setup in your Excel document. Basically, you need to define "named objects" in Excel that are synonymous to tables in a database. The first row of the named object are the column headers. To set up a named object, first select the range of cells (your "table," with the first row being the column headers), then go to menu Insert->Names->Define. Name your object and press "Add." Now you have an object which can be read by ADO.NET.
  
  Now for the C# (this example assumes I have an Excel file at C:\Book1.xls and a named object in this workbook called "MyObject"):
  

  using System.Data; using System.Data.OleDb; ... OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0"); OleDbDataAdapter da = new OleDbDataAdapter("select * from MyObject", con); DataTable dt = new DataTable(); da.Fill(dt);

  
  You can use SQL to query the data in your named object.
  

• Eli Lopian

  I have the same problem, and I'm not using code. I insert one line, and it does fine. When I insert a second line, I get the same message you listed in a modal box ("To prevent possible loss of data, Microsoft Office Excel cannot shift nonblank cells off the worksheet."). If I save the file, I can then insert the additional line, but then I get the same message again when I try to insert another line. My sheet is large, but not that large; the last line is around 60k, leaving several thousand empty lines.

  Looks like Excel is trying to be too smart, and messing up in the process. I have Excel 2003 (with all updates), so it's not something a newer version would fix. Maybe an older version will fix it...

  There are no references to the message, that I could find, in Excel documentation or the KB.

  
  

• Brucec

  I just started working on an application which performs this action. Feel free to use the following code.

   

  //Start up excel to read the excel file in question

  Microsoft.Office.Interop.Excel.Application ExcelObj = null;

  ExcelObj = new Microsoft.Office.Interop.Excel.Application();

  if (ExcelObj == null)

  {

   MessageBox.Show("ERROR: EXCEL couldn't be started!");

   System.Windows.Forms.Application.Exit();

  }

  Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(openFileDialog.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

  Type.Missing, Type.Missing, Type.Missing, Type.Missing,

  Type.Missing, Type.Missing, Type.Missing, Type.Missing,

  Type.Missing, Type.Missing);

  Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

  Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

  for(int x = 1; x <= 29; x++)

  {

  Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A"+x.ToString(), "I" + x.ToString());

  System.Array myvalues = (System.Array)range.Cells.get_Value(range.);

  string[] strArray = ConvertToStringArray(myvalues);

  }

  Sorry about the bad formatting, I don't know how to use this forum that well yet.

  
  

• dddon

  can any body give any solution for the above query please

  Thanks-Aparna

  
  

• noyesar

  Hi
  
  Can anyone help me how to read values in Excel in C# . So that Once I read I can send them to DataBase. My excel file Test.xls in ("C:\")
  
  Thanks
  Doss
  
  

• nielsvanvliet

  Hi There,

  I also facing the same problem but it bit of different . I wanted to read the data in Excel Sheet Eg: If Data is there for columns A,B,C,D ..

  wanted to read the data 'A' and 'B' 'A' and 'C'

  after that I wanted to generate the Charts ..

  any suggestions or solutions.

  Thanks-Aparna

  
  

• Raja Pratap

  Hellow,

  I'used these things a couple of months ago for an application I made. While trying to adapt the application now I cannot open the Excel file anymore. Does anybody hase any notice of changes in the Excel object

  public ExcelDB()
  {
  try
  {
  excel.Open(Application.StartupPath+"\\MAILING.XLS");
  excel.Range();
  string file = Application.StartupPath+"\\MAILING.XLS";
  sbConn.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+file);
  sbConn.Append(";Extended Properties=");
  sbConn.Append(Convert.ToChar(34));
  sbConn.Append("Excel 8.0;HDR=NO;IMEX=2");
  sbConn.Append(Convert.ToChar(34));
  cnExcel = new OleDbConnection(sbConn.ToString());
  cmdExcel = new OleDbCommand("Select * From Clientlist",cnExcel);
  }
  catch
  {
  MessageBox.Show("Ernstige fout, klantenbestand niet gevonden!","Error 03",System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Stop);
  }
  }
  public void FillList()
  {
  try
  {
  Cursor.Current = Cursors.WaitCursor;
  cnExcel.Open();
  drExcel = cmdExcel.ExecuteReader();
  while(drExcel.Read())
  {
  if(drExcel["Name"].ToString() != "")
  {
  LogicControl.form.clientList.Items.Add(drExcel["Name"].ToString());
  string[] listArray = {drExcel["Name"].ToString(),
  drExcel["Address"].ToString(),
  drExcel["City"].ToString(),
  drExcel["Phone"].ToString(),
  drExcel["Fax"].ToString(),
  drExcel["VAT"].ToString()
  };
  lvi = new ListViewItem(listArray);
  LogicControl.form.klantenList.Items.Add(lvi);
  }
  }
  drExcel.Close();
  cnExcel.Close();
  Cursor.Current = Cursors.Arrow;
  }
  catch
  {
  excel.Quit();
  MessageBox.Show("Ernstige fout, foutief klantenbestand!","Error 05",System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Stop);
  }
  }

  
  
• Reading Excel From C#