ASP.NET读取EXCEL文件的三种经典方法 （其中，64位操作系统上使用第二种com组件方式）
最近研究了ASP.NET如何高效读取EXCEL文件，现总结如下：
1.方法一：采用OleDB读取EXCEL文件：
把EXCEL文件当做一个数据源来进行数据的读取操作，实例如下：
public DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel="select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds,"table1");
return ds;
}
对于EXCEL中的表即sheet([sheet1$])如果不是固定的可以使用下面的方法得到
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
string tableName=schemaTable.Rows[0][2].ToString().Trim();
另外：也可进行写入EXCEL文件，实例如下：
public void DSToExcel(string Path,DataSet oldds)
{
//先得到汇总EXCEL的DataSet 主要目的是获得EXCEL在DataSet中的结构
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+path1+";Extended Properties=Excel 8.0" ;
OleDbConnection myConn = new OleDbConnection(strCon) ;
string strCom="select * from [Sheet1$]";
myConn.Open ( ) ;
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom, myConn ) ;
ystem.Data.OleDb.OleDbCommandBuilder builder=new OleDbCommandBuilder(myCommand);
//QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。
builder.QuotePrefix="["; //获取insert语句中保留字符（起始位置）
builder.QuoteSuffix="]"; //获取insert语句中保留字符（结束位置）
DataSet newds=new DataSet();
myCommand.Fill(newds ,"Table1") ;
for(int i=0;i<oldds.Tables[0].Rows.Count;i++)
{
//在这里不能使用ImportRow方法将一行导入到news中，因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。
在使用ImportRow后newds内有值，但不能更新到Excel中因为所有导入行的DataRowState!=Added
DataRow nrow=aDataSet.Tables["Table1"].NewRow();
for(int j=0;j<newds.Tables[0].Columns.Count;j++)
{
nrow[j]=oldds.Tables[0].Rows[i][j];
}
newds.Tables["Table1"].Rows.Add(nrow);
}
myCommand.Update(newds,"Table1");
myConn.Close();
}
2.方法二：引用的com组件：Microsoft.Office.Interop.Excel.dll 读取EXCEL文件
首先是Excel.dll的获取， 再在项目中添加引用该dll文件.
//读取EXCEL的方法 (用范围区域读取数据)
p
rivate void OpenExcel(string strFileName)
{
object missing = System.Reflection.Missing.Value;
Application excel = new Application();//lauch excel application生吞结局
if (excel == null)
{
Response.Write("<script>alert('Can't access excel')</script>");
}
else
{
excel.Visible = false; excel.UserControl = true;
// 以只读的形式打开EXCEL文件
Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
//取得第一个工作薄
Worksheet ws = (Worksheet)_Item(1);
excel.Quit(); excel = null;
Process[] procs = Process.GetProcessesByName("excel");
foreach (Process pro in procs)
{
全国生猪价格pro.Kill();//没有更好的方法，只有杀掉进程
}
GC.Collect();
}
3.方法三：将EXCEL文件转化成CSV（逗号分隔）的文件，用文件流读取(等价就是读取一个txt文本文件)。
先引用命名空间:using System.Text；和using System.IO;
FileStream fs = new FileStream("d:\\Customer.csv", FileMode.Open, FileAccess.Read, FileShare.None);
StreamReader sr = new StreamReader(fs, System.Text.Encoding.GetEncoding(936));
string str = "";
string s = Console.ReadLine();
while (str != null)
{ str = sr.ReadLine();
string[] xu = new String[2];
xu = str.Split(',');
string ser = xu[0];
string dse = xu[1]; if (ser == s)
{ Console.WriteLine(dse);break;
}
} sr.Close();
另外也可以将数据库数据导入到一个txt文件，实例如下：
/
/txt文件名
string fn = DateTime.Now.ToString("yyyyMMddHHmmss") + "-" + "PO014" + ".txt";
OleDbConnection con = new OleDbConnection(conStr);
con.Open();
string sql = "select ITEM,REQD_DATE,QTY,PUR_FLG,PO_NUM from TSD_PO014";
//OleDbCommand mycom = new OleDbCommand("select * from TSD_PO014", mycon);
//OleDbDataReader myreader = mycom.ExecuteReader(); //也可以用Reader读取数据
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(sql, con);
oda.Fill(ds, "PO014");
DataTable dt = ds.Tables[0];
FileStream fs = new FileStream(Server.MapPath("download/" + fn), FileMode.Create, FileAccess.ReadWrite);
StreamWriter strmWriter = new StreamWriter(fs); //存入到文本文件中
//把标题写入.txt文件中
//for (int i = 0; i <dt.Columns.Count;i++)
//{
// strmWriter.Write(dt.Columns[i].ColumnName + "
");
//}
foreach (DataRow dr in dt.Rows)
{
string str0, str1, str2, str3;
string str = "|"; //数据用"|"分隔开
str0 = dr[0].ToString();
str1 = dr[1].ToString();
str2 = dr[2].ToString();
str3 = dr[3].ToString();
str4 = dr[4].ToString().Trim();
strmWriter.Write(str0);
strmWriter.Write(str);
strmWriter.Write(str1);
strmWriter.Write(str);
strmWriter.Write(str2);
strmWriter.Write(str);
strmWriter.Write(str3);
strmWriter.WriteLine(); //换行
}
strmWriter.Flush();
strmWriter.Close();
if (con.State == ConnectionState.Open)
{
con.Close();
}
4，方法4
public static DataTable ConvertToDataTable(System.IO.Stream excelFileStream)
{
using (HSSFWorkbook HSSFWorkbook = new HSSFWorkbook(excelFileStream))
{
DataTable dt = new DataTable();
Sheet sheet = 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++)
{
Cell cell = row.GetCell(i);
DataColumn column = new DataColumn(cell.StringCellValue);
dt.Columns.Add(column);
}
}
else
{
DataRow dtRow = dt.NewRow();
string rValue = string.Empty;
for (int i = 0; i < row.LastCellNum; i++)
{
Cell cell = row.GetCell(i);
if (cell == null)
{
dtRow[i] = "";
}
else
魔兽世界怎么改字体{
dtRow[i] = cell.ToString();
rValue += cell.ToString();
}
}
if (string.IsNullOrEmpty(rValue.Trim()))
break;
dt.Rows.Add(dtRow);
}
n++;
}
return dt;
}
}
5、方法
C# 读写Excel 的类
昨天公司一个部门要个小程序，要读写Excel，在网上搜了不少资料，结果程序做完了却在网上发现一个c#读写Excel的类，收藏了，呵呵
//1.
添加引用-〉com-〉microsoft excel 11.0
//2.若出现错误：命名空间“Microsoft.Office”中不存在类型或命名空间名称“Interop”(是缺少程序集引用吗?)
//解决方法：先删除引用中的Excel，然后到文件Microsoft.Office.Interop.Excel.dll，手动添加该文件的引用
using System;
using System.Data;
using System.Reflection;
using System.IO;
using Microsoft.Office.Core;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace Wage.Common
{
/// <summary>
/// 作者：李爱民
/
// 功能描述：对Excel报表进行操作
/// 创建时间：2006－01－17, 修改时间：２００７－１－１4
/// 说明：在工程中需要添加 Excel11.0对象库的引用（Office 2000为Excel9.0，Office XP为Excel10.0）；
/// 需要在Dcom中配置Excel应用程序的权限；
/// 服务器需要安装Office2003
/// </summary>
public class ExcelLib
{
//msdn.microsoft/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfexcelapplicationobject.asp
#region Variables
气压预报private Excel.Application excelApplication = null;
private Excel.Workbooks excelWorkBooks = null;
private Excel.Workbook excelWorkBook = null;
private Excel.Worksheet excelWorkSheet = null;
private Excel.Range excelRange = null;//Excel Range Object，多种用途
private Excel.Range excelCopySourceRange = null;//Excel Range Object
private int excelActiveWorkSheetIndex; //活动工作表索引
private string excelOpenFileName = ""; //操作Excel的路径
private string excelSaveFileName = ""; //保存Excel的路径
#endregion
#region Properties
public int ActiveSheetIndex
{
get
{
return excelActiveWorkSheetIndex;
}
set
{
excelActiveWorkSheetIndex = value;
}
}
public string OpenFileName
{
get
{
return excelOpenFileName;
}
set
{
抛竿excelOpenFileName = value;
}
}
public string SaveFileName
{
get
{
return excelSaveFileName;
}
set
{
excelSaveFileName = value;
}
}
#endregion
//
//--------------------------------------------------------------------------------------------------------
/// <summary
>
/// 构造函数；
/// </summary>
public ExcelLib()
{
excelApplication = null;//Excel Application Object
excelWorkBooks = null;//Workbooks
excelWorkBook = null;//Excel Workbook Object
excelWorkSheet = null;//Excel Worksheet Object
ActiveSheetIndex = 1; //默认值活动工作簿为第一个；设置活动工作簿请参阅SetActiveWorkSheet()
}
/// <summary>
/// 以excelOpenFileName为模板新建Excel文件
/// </summary>
public bool OpenExcelFile()
{
if (excelApplication != null) CloseExcelApplication();
//检查文件是否存在
if (excelOpenFileName == "")经典脑筋急转弯
{
throw new Exception("请选择文件！");
}
if (!File.Exists(excelOpenFileName))
{
throw new Exception(excelOpenFileName + "该文件不存在！");//该异常如何处理，由什么处理？？？？
}
try
{
excelApplication = new Excel.ApplicationClass();
excelWorkBooks = excelApplication.Workbooks;
excelWorkBook = ((Excel.Workbook)excelWorkBooks.Open(excelOpenFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets[excelActiveWorkSheetIndex];
excelApplication.Visible = false;
return true;
}
catch (Exception e)
{
CloseExcelApplication();
MessageBox.Show("(1)没有安装Excel 2003；(2)或没有安装Excel 2003 .NET 可编程性支持；/n详细信息："
+e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//throw new Exception(e.Message);
return false;
}
}
/// <summary>
/// 读取一个Cell的值
/// </summary>
/// <param name="CellRowID">要读取的Cell的行索引</param>
/// <param name="CellColumnID">要读取的Cell的列索引</param>
/// <returns>Cell的值</returns>
public string getOneCellValue(int CellRowID, int CellColumnID)
{
if (CellRowID <= 0)
{
throw new Exception("行索引超出范围！");
}
string sValue = "";
try
{
sValue = ((Excel.Ran
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论