如何打开和读取Excel文件? [英] how to open and read an excel file ?!!

问题描述

我想制作程序以便打开一个excel文件，阅读并使用"oledb"将其保存为数据库，我尝试了许多代码，并在Google上检查了解决方案，并尝试从头开始构建代码，但我什么也没得到，所以我想要帮助???

i want to make program so as to open an excel file , read it and save it as a database using ''oledb'' i tried many codes and i check for solution on google and try to built a code from scratch but i get nothing ,so i want help ???

推荐答案

如果您不想使用Excel COM对象，则可以使用OleDb.这需要在Excel文档中进行一些设置.基本上，您需要在Excel中定义与数据库表同义的命名对象".命名对象的第一行是列标题.要设置命名对象，请首先选择单元格范围(您的表格"，第一行是列标题)，然后转到菜单插入"->名称"->定义".为您的对象命名，然后按添加".现在，您有了一个可以由ADO.NET读取的对象.

现在，对于C#(此示例假定我在C:\ Book1.xls处有一个Excel文件，并且在此工作簿中有一个名为"MyObject"的命名对象):

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);

现在您可以将此数据表保存到DB中.

要将excel的内容转换为数组:

Now you can save this data table into DB.

To transform the contents of excel into array:

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);
    }

现在您可以遍历array [].

代码格式

Now you can iterate through the array[].

Code formatted

这篇关于如何打开和读取Excel文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！