asp.net 读取excel

方法一:

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

方法二：

f 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.

方法三：

http://www.codeproject.com/KB/office/ExcelReader.aspx

上篇文章有一些错误，更正如下：

sample code:

WCF部分：
using ExcelLibrary.SpreadSheet;
using ExcelLibrary.CompoundDocumentFormat;
using ExcelLibrary.BinaryFileFormat;
[DataContract]
public class ExcelGridData
{
    public ExcelGridData()
    {
        m_Header = new List<string>();
        m_Data = new List<List<string>>();
    }
    [DataMember]
    public List<string> m_Header;
    [DataMember]
    public List<List<string>> m_Data;
}

 

 

        string filePath = HostingEnvironment.MapPath("~/upload/Book1.xls");
        //Stream fileStream = File.OpenRead(filePath);

        CompoundDocument doc = CompoundDocument.Read(filePath);
        if (doc == null) throw new Exception("Invalid Excel file");
        byte[] bookdata = doc.GetStreamData("Workbook");
        Workbook book = WorkbookDecoder.Decode(new MemoryStream(bookdata));
        Worksheet sheet = book.Worksheets[0];

        ExcelGridData rtval = new ExcelGridData();
        for (int rowIndex = sheet.Cells.FirstRowIndex; rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
        {
            Row row = sheet.Cells.GetRow(rowIndex);
            if (rowIndex == 0)
            {
                for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
                {
                    rtval.m_Header.Add("A[" + rowIndex.ToString() + "]");
                }
            }
            List<string> tmprow = new List<string>();
            for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
            {
                Cell cell = row.GetCell(colIndex);
                tmprow.Add(cell.StringValue);
            }
            rtval.m_Data.Add(tmprow);
        }
        return rtval;

 

Client 部分：

            List<ObservableCollection<string>> tmpdata = rt.m_Data.ToList();
            int iter = 0;
            foreach (ObservableCollection<string> tmprow in tmpdata)
            {
                BindableDataGrid.Data.DataRow dr = new BindableDataGrid.Data.DataRow();
                if (iter >100)
                {
                    break;
                }
                int i = 0;
                foreach (string tmpitem in tmprow.ToList())
                {
                    dr["col" + i.ToString()] = tmpitem;
                    i++;
                }
                dt.Rows.Add(dr);
                iter++;
            }
            DataSet ds = new DataSet("MyDataSet");
            ds.Tables.Add(dt);