首先要引用 NPOI.dll   （可在网上下载！）

//导入

public void OnSubmit()

        {

            string path = Server.MapPath("/upload/201410/27/201410271103461051.xls");

            FileStream fs = File.Open(path, FileMode.Open);

            System.Data.DataTable dt = ConvertToDataTable(fs);

            

            foreach (DataRow row in dt.Rows)

            {

                if (row["Mobile1"] != null)

                {

                    Response.Write(row["Mobile1"].ToString() + " " + row["Mobile2"].ToString() + "

");

                }

            }

            Response.End();

        }

//excel转DataTable

public static DataTable ConvertToDataTable(System.IO.Stream excelFileStream)

        {

            HSSFWorkbook HSSFWorkbook = new HSSFWorkbook(excelFileStream);

            DataTable dt = new DataTable();

            HSSFSheet sheet = (HSSFSheet)HSSFWorkbook.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            int n = 0;

            while (rows.MoveNext())

            {

                HSSFRow row = (HSSFRow)rows.Current;

                if (n == 0)

                {

                    for (int i = 0; i < row.LastCellNum; i++)

                    {

                        HSSFCell cell = (HSSFCell)row.GetCell(i);

                        if (cell == null)

                            continue;

                        DataColumn column = new DataColumn(cell.StringCellValue);

                        dt.Columns.Add(column);

                    }

                }

                else

                {

                    DataRow dtRow = dt.NewRow();

                    string rValue = "";

                    for (int i = 0, j = 0; i < row.LastCellNum; i++)

                    {

                        HSSFCell cell = (HSSFCell)row.GetCell(i);

                        if (cell == null)

                        {

                            dtRow[i] = "";

                        }

                        else

                        {

                            dtRow[j] = cell.ToString();

                            rValue = cell.ToString();

                            j++;

                        }

                    }

                    if (string.IsNullOrEmpty(rValue.Trim()))

                        break;

                    dt.Rows.Add(dtRow);

                }

                n++;

            }

            return dt;

        }

 

//导出

///

        /// 将明细表导出到Excel

        ///

        ///

要导入到Excel中的中文表头的Sql,将需要的字段通过sql as成中文名

///

/// 1 成功 -1失败

protected int ExportToExcel(string sql, HttpResponse response)

{

DataTable dt = CommOtherBLL.ExportToExcel(sql); //要导出的表

if (dt != null && dt.Rows.Count > 0)

{

StringBuilder strContent = new StringBuilder();

for (int i = 0; i < dt.Columns.Count; i++)

strContent.Append(dt.Columns[i] + "\t");

strContent.Append("\n");

for (int i = 0; i < dt.Rows.Count; i++)

{

for (int y = 0; y < dt.Columns.Count; y++)

strContent.Append(dt.Rows[i][y] + "\t");

strContent.Append("\n");

}

response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode("excel", System.Text.Encoding.UTF8) + ".xls");

response.ContentType = "application/ms-excel";

response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

response.Write(strContent.ToString());

response.End();

return 1;

}

else

return -1;

}